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