MySQL常用命令和知识点

DDL(data definition language):用来定义数据库对象，数据库、表、字段 

#数据库
show databases;#查询所有数据库
create database [if not exists] name;#创建数据库
drop database [if exists] name;#删除数据库
use name;#切换数据库

#表
show tables;
desc table_name; #查询tablename表结构
show create table table_name;#查询创建tablename的语句
#表操作--创建
create table table_name{
    field_1 type,
    field_2 type,
    ...
};
#表操作--修改
alter table table_name add field_name field_type;#添加字段
alter table table_name modify field_name field_type;#修改字段类型
alter table table_name change field_oldname field_newname field_type;#修改字段名和字段类型
alter table table_name drop field_name;#删除字段
alter table table_old_name to table_new_name;#修改表名
#表操作--删除
drop table [if exists] table_name;#删除指定表
truncate table table_name; #删除指定表，并创建新表

DML（data manipulation language）:对数据库中表的数据记录进行增、删、改操作

#添加数据 
insert into table_name (field_1,field_2,...) values(value_1,value_2,...),(value_1,value_2,...),(value_1,value_2,...);
#修改数据
update table_name set field_1 = value_1,field_2 = value_2,... [where 条件];
#删除数据
delete from table_name [where 条件];

DQL(data query language):用来查询数据库中表的记录

#语法结构
select [distinct] 字段列表seq4 from 表名列表seq1 where 条件列表seq2 group by 分组字段列表seq3 
                      having 分组后条件列表seq5 order by 排序字段列表seq6 limit 分页参数seq7;


#条件 where
field between ... and ... #某个范围内
field in(...) #在in之后列表中的值，多选一
field like '__%' #模糊匹配，'_'单个字符，'%'匹配任意个字符
field is null 

#聚合函数
count,max,min,avg,sum

#排序方式
order by field_name asc/desc;

#分页查询
limit 起始索引,每页查询记录数; #起始索引 = （查询页码 - 1 ）* 每页显示记录数

DCL(data control language),用来管理数据库用户、控制数据库的访问权限

#创建用户
create user '用户名'@'主机名' identified by '密码';
#修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
#删除用户
alter user '用户名'@'主机名'


#权限控制--grants
all,all privileges,select,insert,update,delete,alter,drop,create
#查询权限
show grants for '用户名'@'主机名';
#授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
#撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

MySQL内置函数

#字符串函数
concat(s1,s2,...) #字符串拼接
lower(str) #转小写
upper(str) #转大写
lpad(str,n,pad) #左填充，使用pad对str左边填充达到n个字符
rpad(str,n,pad) #右填充
trim(str) #去掉字符串头部和尾部的空格
substring(str,start,len) #返回从字符串str从start位置起的len个长度的字符串

#数值函数
ceil(x) #向上取整
floor(x) #向下取整
mod(x,y) #返回x/y的模
rand() #返回0~1的随机数
round(x,y) #求参数x的四舍五入的值，保留y位小数

#日期函数
curdate() #返回当前日期
curtime() #返回当前时间
now() #返回当前日期和事件
year(date) 
month(date)
day(date)
date_add(date,interval expr type) #在日期/时间值上加上一个时间间隔expr后的时间值
datediff(date1,date2) #返回起始时间date1和结束时间date2之间的天数

#流程函数
if(value,t,f) #value为true则返回t，否则返回f
ifnull(value1,value2) #value1不为空返回value1，否则返回value2
case [field_name] 
    when value_1 
        then res_1
    when value_2 
        then res_2
    else
        default_res
end;

约束：作用于表中字段上的规则，限制存储在表中的数据，保证表中数据的正确、有效、完整。

not null #非空约束
unique #唯一约束
primary key #主键约束
default 值 #默认约束
check 条件 #保证字段满足某一个条件
foreign key #外键约束
auto_increment #自增

#添加外键--创建表
create table table_name(
    field_name1 type,
    ...
    [constraint] foreign_key_name foreign key (field_name1) references                 
                                  main_table_name(field_name2) on update 行为 on delete 行为
);

#添加外键--修改表
alter table table_name add constraint foreign_key_name foreign key(field_name1) 
                      references main_table_name(field_name2) on update 行为 on delete 行为;

#删除外键
alter table table_name foreign key foreign_key_name;

#行为
no action 、restrict #父表删除或更新记录时，检查是否有对应外键，有则不允许
cascade              #父表删除或更新记录时，检查是否有对应外键，有则也删除更新子表中的记录
set null             #父表删除记录时，检查是否有对应外键，有则设置子表外键值为null
set default          #父表变更时，检查是否有对应外键，有则设置子表外键值为默认值

多表查询

（1）一对多：在多的一方建立外键，指向一的一方的主键

（2）一对一：任意一方建立外键并设置外键为唯一unique，关联另一方的主键

（3）多对多：建立第三张中间表，中间表包含两个外键，分别关联两方主键

#连接查询
#内连接:查询A、B交集部分数据
#内连接--隐式
select 字段列表 from 表1，表2 where 条件 ...;
#内连接--显示
select 字段列表 from 表1 inner join 表2 on 条件 ...;
#外连接--左外连接：查询A表所有数据，以及两张表交集部分数据
select 字段列表 from 表1 left outer join 表2 on 条件 ...;
#外连接--右外连接：查询B表所有数据，以及两张表交集部分数据
select 字段列表 from 表1 right outer join 表2 on 条件 ...;
#自连接：当前表与自身的连接查询，自连接必须使用表别名
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;


#联合查询,多张表列数必须一致，字段类型也需要一致，union all不去重，union会合并之后的数据去重
select 字段列表 from A ...
union [all]
select 字段列表 from B ...;

#子查询
#标量子查询 子查询结果为单个值 = >= <= !=
select * from emp where entrydate > (select entrydate from emp where name = '张三');
#列子查询 子查询结果为一列
select * from emp where dept_id in (select id from dept where name = "销售部" or name = "市场部");
#行子查询 子查询结果为一行
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张三');
#表子查询 子查询结果为多行多列
select * from emp where (salary,managerid) in (select salary,managerid from emp where name = '张三' or name = '李四');