To log into the database:

mysql -uroot -proot

To create a database:

create database test

To list all databases:

show databases

To select and use a specific database:

use test

To view all tables in the database:

show tables

To delete a database:

drop database test

To create a table:

create table emp(ename varchar(10), hiredate date, sal decimal(10, 2), deptno int(2))

create table dept(deptno int(2), deptname varchar(10))

To describe the structure of a table:

desc emp

To view detailed table creation information:

show create table emp G

To drop a table:

drop table emp

To modify the fields of a table:

alter table emp modify ename varchar(20)

To add a new field to a table:

alter table emp add column age int(3)

To remove a field from a table:

alter table emp drop column age

To rename a field:

alter table emp change age age1 int(4)

To change the name of a table:

alter table emp rename emp1

To insert a record with specified fields:

insert into emp (ename, hiredate, sal, deptno) values ('Tom', '2023-01-01', '2000', 1)

To insert a record without specifying fields:

insert into emp values ('Jack', '2023-01-01', '2000', 1)

To perform bulk insert:

insert into dept values (1, 'dept1'), (2, 'dept2')

To update a record:

update emp set sal='4000', deptno=2 where ename='Tom'

To delete a specific record:

delete from emp where ename='Tom'

To retrieve all records:

select * from emp

To get unique records:

select distinct deptno from emp

For conditional queries:

select * from emp where deptno=1 and sal<3000

For sorting and limiting results:

select * from emp order by deptno desc limit 2

For pagination (starting from record 0 and limiting to 10):

select * from emp order by deptno desc limit 0, 10

To perform aggregation (counting departments with deptno greater than 1):

select deptno, count(1) from emp group by deptno having count(1) > 1

For joining tables:

select * from emp e left join dept d on e.deptno=d.deptno

For subqueries:

select * from emp where deptno in (select deptno from dept)

To combine records from multiple queries:

select deptno from emp union select deptno from dept

To grant permissions (select and insert) to a user:

grant select, insert on test.* to 'test'@'localhost' identified by '123'

To view user permissions:

show grants for 'test'@'localhost'

To revoke permissions:

revoke insert on test.* from 'test'@'localhost'

To grant all privileges on all databases:

grant all privileges on . to 'test'@'localhost'

To grant all permissions including grant option:

grant all privileges on . to 'test'@'localhost' with grant option

To grant system-level permissions:

grant super, process, file on . to 'test'@'localhost'

To only grant login permissions:

grant usage on . to 'test'@'localhost'

To delete a user account:

drop user 'test'@'localhost'

To change your own password:

set password = password('123')

For an administrator to change another user's password:

set password for 'test'@'localhost' = password('123')

To view current character set:

show variables like 'character%'

To specify character set while creating a database:

create database test2 character set utf8

To check the current time zone:

show variables like "%time_zone%"

To modify the global MySQL time zone:

set global time_zone = '+8:00';

To change the current session time zone:

set time_zone = '+8:00'

To apply changes immediately:

flush privileges