记录一些工作过程中经常会使用的Mysql命令

DDL（Database Definition Language）

  1. Create Database

    1

    CREATE DATABASE dbname;

  2. Create user with password

    1
2

    -- 设置 host为 % 时，username可用于远程登录
CREATE user 'username'@'host' identified by 'password';

  3. Show Database

    1

    SHOW DATABASES;

  4. Use database

    1

    USE dbname;

  5. Drop Database

    1

    DROP DATABASE dbname;

  6. Create Table

    1
2
3
4
5
6
7
8

    CREATE TABLE IF NOT EXISTS `demo`(
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`password` VARCHAR(40) NOT NULL,
`create_time` TIMESTAMP NOT NULL DEFAULT NOW(),
`update_time` TIMESTAMP NOT NUll DEFAULT CURRENT_TIMESTAMP(),
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

  7. Change Table

    • Change name of column password to passwd

      1

      ALTER TABLE demo CHANGE password passwd;

  8. Drop Table

    1

    DROP TABLE demo;

  9. Change Remote root password

change password to 123456 (mysql8.0)

1

ALTER user 'root'@'%' identified with mysql_native_password by '123456';

DML

  1. add data into table demo

    1

    INSERT INTO demo(name,password) VALUES('name1','password1');

  2. Change data

    1

    UPDATE demo SET name='name2' where id = 1;

  3. delete data

    1

    DELETE FROM demo WHERE id = 1;

  4. select data

    • select data by time, accurate to the second

      1

      SELECT * FROM demo WHERE timestamp(create_date) BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 00:00:00';

    • select data by date, accurate to the day

      1

      SELECT * FROM demo WHERE date(create_date) BETWEEN '2023-01-01' AND '2023-01-31';

    • use case when to conversion some select column

      1

      SELECT CASE gender WHEN 0 THEN '男' WHEN 1 THEN '女' END AS '性别', CASE WHEN status IS NULL THEN '未提交' END AS '提交状态' FROM demo;

DCL

  1. Authorization to users

    1
2

    GRANT all privileges on dbname.* to username@'%' identified by password;
flush  privileges;

Others

backup

  1. backup database with mysqldump

    1

    mysqldump -u root -h host -p dbname > backup_file.sql;

restore

  1. restore database with mysqldump

    1

    mysqldump -u root -h host -m dbname < backup_file.sql;