Mysql 8 CheatSheet

Database Operation

show databases;

create database dbname;

drop database dbname;

use dbname;

Table Operation

create table tableName(
id INT AUTO_INCREMENT,
   first_name VARCHAR(100),
   last_name VARCHAR(100),
   email VARCHAR(50),
   password VARCHAR(20),
   location VARCHAR(100),
   phone VARCHAR(100),
   status TINYINT(1),
   create_date DATETIME,
   PRIMARY KEY(id)
);

drop table tableName;

desc tableName;

insert into tableName (col1,col2,col3) values (val1,val2,now());

select * from tableName;

select * from tableName where user_name like '%aa%';

select col1,col2 from tableName where col2=val2;

delete from tableName where col1=val1;

update tableName set col2=val2 where col1=val1;

alter table tableName add col4 varchar(255);

alter table tableName modify column col4 int(3);

alter table tableName modify column varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

User Operation

create user  'username'@'%' identified by 'password';

grant all privileges on dbname.* to 'username'@'%' with grant option;

alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'password';

drop user 'user'@'host';

Backup/Restore

mysqldump -hhost -uuser -p dbName > db_backup.sql

mysql -uuser -p dbname < db_backup.sql

Leave a Reply

Your email address will not be published. Required fields are marked *