环境 Ubuntu 20.04 vmware

常用

select schema_name from information_schema.schemata;
select table_name from inforamtion_schema.tables where table_schema=database();
select column_name from information_schema.columns where table_schema=database();

安装

安装 mysql server

sudo apt-get update
sudo apt-get install mysql-server

第一次登录是无需输入密码

mysql -u root -p

设置 root 密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_pwd';

退出

quit

语法

DDL

创建新用户

CREATE USER 'jbn'@'localhost' IDENTIFIED BY 'your_pwd';

赋予权限

GRANT ALL PRIVILEGES ON *.* TO 'jbn'@'localhost' WITH GRANT OPTION;

查看所有 database

show databases;

创建新的 database

crearte database test_db;

查询创建 database 的语句

show create database test_db;

删除 database

drop database test_db;

使用 database

use test_db;

显示当前 database 所有 table

show tables;

显示某 table 的表结构

desc table_name;

查询某表的创建语句

show create table table_name;

创建 table

create table test_table(
    id int comment "num",
    name varchar(50) comment "name",
    age int comment "age"
) comment "user_table";

// id, name, age 为字段值

添加字段

alter table test_table add nickname varchar(20);

修改字段

alter table test_table change nickname username varchar(30) comment "username";

// alter table <table name> change <old name> <new name> <type> comment "<new name>";

删除字段

alter table test_table drop username;

// alter table <table name> drop <name>;

修改 table 名称

alter table test_table rename to rename_test_table;

// alter table <old table name> rename to <new table name>;

删除 table

drop table test_table;

// drop table <table name>;
// drop table if exist <table name>

删除 table 并重新创建（删除所有数据，但是不改变表结构）

truncate table test_table;

// truncate table <table name>;

DML

插入值

insert into test_table(id, name, age) values(1, "test", 18);

// insert into <table name>(<column 1, column 2, ...>) values(<value1, value2, ...>);

批量插入值

insert into test_table(id, name, age) values(2, "test2", 19), (3, "test3", 19);

// insert into <table name>(<column 1, column 2, ...>) values(<value1, value2, ...>), (<value1, value2, ...>), ...;

给全部字段插入值

insert into test_table values(2, "test2", 19);

// insert into <table name> values(<value1, value2, ...>);

批量字段插值

insert into test_table values(2, "test2", 19), (3, "test3", 19);

// insert into <table name> values(<value1, value2, ...>), (<value1, value2, ...>), ...;

修改数据

update test_table set name="test";
update test_table set name="test4" where id=1;

// update <table name> set <column_name>=<value> [where <condition>]

删除数据

delete from test_table where name="test";

// delete from <table name> [where <condition>]

DQL

查询

select * from test_table;

select id from test_table;

select id, name from test_table;

select id name from where id=3;

select id as 'num' from test_table;

select distinct name from test_table;

条件查询

select * from test_table where id = 1;
select * from test_table where id <= 3;
select * from test_table where age is null;

select * from test_table where id != 3;
select * from test_table where id <> 3;

select * from test_table where id < 3 and id > 1;
select * from test_table where id < 3 && id > 1;

select * from test_table where id = 1 or id = 2;
select * from test_table where id in(1, 2);

select * from test_table where name like "test1";
select * from test_table where name like "test_";
select * from test_table where name like "____";
select * from test_table where name like "%3";

聚合查询

select count(*) from test_table;
select avg(age) from test_table;
select max(age) from test_table;
select min(age) from test_table;
select sum(age) from test_table;

分组查询

select count(name) from test_table group by name;
select name, count(name) from test_table group by name;
select name, count(name) from test_table group by name having count(name) >= 2;
select name, count(name) num from test_table group by name having count(name) >= 2;
select name, count(name) num from test_table where id > 1 group by name having count(name) >= 2;

排序查询

select name from test_table order by age;
select name from test_table order by age asc;
select name from test_table order by age desc;

select * from test_table order by name, age;
select * from test_table order by name, age desc;

// 当 name 相同时按照 age 排序

分页查询

select * from test_table limit 1, 3;
select * from test_table limit 2, 3;
select * from test_table limit 4, 3;
select * from test_table limit 1, 6;

DCL

查询用户

use mysql;
select * from user;

创建用户

create user "test"@"localhost" identified by "test";

// create user "<username>"@"<host>" identified by "<password>";

// create user "test"@"%" identified by "test" 
// % 为各个主机都能操作 

修改用户密码

alter user "test"@"localhost" identified with mysql_native_password by "pass";

// alter user "<username>"@"<host>" identified with mysql_native_password by "<new pwd>"

删除用户

drop user "test"@"localhost";

// drop user "<username>"@"<host>"

权限控制

以普通用户 test 登录，查数据库 VS root 用户查数据库

show grants for 'test'@'localhost';

// show grants for "<username>"@"<host>";

授权

grant all on test_db.* to 'test'@'localhost';

// grant <privilage> on <database>.<table> to "<username>"@"<localhost>";

取消授权

revoke all on test_db.* from 'test'@'localhost';

// revoke <privilage> on <database>.<table> from '<username>'@'<host>';

Function

字符串函数

select concat("hello", "world");

select lower('TEST');
select upper('test');

select lpad('test', 10, '*');
select rpad('test', 10, '*');

// select [rpad | lpad]('<string>', <length>, 'string');

select trim('  hello world  ');

// 去除两头的空格

select substring('hello world', 3, 5);

// select substring('<string>', start, length);

数值函数

select ceil(4.5);

// 向上取整

select floor(4.5);

// 向下取整

select mod(5, 4);

// 取模

select rand();

// random(0, 1)

select round(4.98767890987, 5);

// 保留小数位

时间函数

select curdate();
select curtime();
select now();

select year("2022-11-13");
select month("2022-11-13");
select day("2022-11-13");

select date_add(now(), interval 70 day);
select date_add(now(), interval 70 year);
select date_add(now(), interval 70 month);

select datediff('2020-11-13', now());

流程控制

select if(1=1, "value1", "value2");
select if(1=2, "value1", "value2");
select ifnull("test", "default");
select ifnull("", "default");
select ifnull(null, "default");

// IF(value, true, false)
// IFNULL(value1, value2)

select id, (
    case name 
    when "test3" then "true" 
    when "test2" then "false" 
    else "null" end) 
 from test_table;

// select <column>, (case <column> when <"value"> then <"value"> else <"value"> end) from <table name>

Constraint

用于插入和修改字段时使用

创建表

create table user(
    id int primary key auto_increment comment "main key",       -- 主键，自增值
    name varchar(10) not null unique comment "username",        -- 不能为空，不重复
    age int check (age > 0 && age <=120) comment "user age",    -- 条件
    status char(1) default "1" comment "user status",           -- 默认值
    gender char(1) comment "user gender"                        --
) comment "user_table";

插入值

insert into user(name, age, status, gender) values('test1', 19, '1', 'm');
insert into users values("test2", 19, '1', 'w'),("test3", 21, '0', 'm');

进程已结束,退出代码0