【MySQL】1.基础语句知识

1.MySQL概述

• SQL登录

mysql [-h 127.0.0.1] [-P 3306] -u root -p        // 连接mysql

• SQL语句分类
  • DDL（Data Definition Language）数据定义语言，用来定义数据库对象(数据库，表，字段)
  • DML（Data Manipulation Language）数据操作语言，用来对数据库表中的数据进行增删改
  • DQL（Data Query Language）数据查询语言，用来查询数据库中表的记录
  • DCL（Data Control Language）数据控制语言，用来创建数据库用户、控制数据库的访问权限

2.SQL语言

2.1DDL 数据定义

2.1.1 数据库操作

# 查询所有数据库
SHOW DATABASES;
# 查询当前数据库
SELECT DATABASE();
# 创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
# 删除
DROP DATABASE [IF EXISTS] 数据库名;
# 使用（切换到该数据库）
USE 数据库名;

2.1.2 表操作

• 查询

# 查询当前数据库所有表
SHOW TABLES;
# 查询表结构
DESC 表名;
# 查询指定表的建表语句
SHOW CREATE TABLE 表名;

• 创建

CREATE TABLE 表名 (
    字段1 字段1类型 [COMMENT ‘字段1注释’],
    字段2 字段2类型 [COMMENT ’字段1注释‘],
    字段3 字段3类型 [COMMENT ’字段1注释‘]
) [COMMENT　’表注释‘];

• 数据类型

• 修改表

# 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [comment '注释'] [约束];
# 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度) [comment '注释'];
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [comment '注释'] [约束];
# 删除字段
ALTER TABLE 表名 DROP 字段名;
# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
# 删除表名
DROP TABLE [IF EXISTS] 表名;
# 删除指定表并重新创建
TRUNCATE TABLE 表名;

• 创建表案例

create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '华龄',
    idcard char(18) comment '身份证号',
    entrydate date comment '入职时间'
) comment '员工表';

2.2 DML 数据操作

• 数据添加

# 给指定字段添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES(值1, 值2, ...);
# 给全部字段添加数据
INSERT INTO 表名 values(值1, 值2, ...);
# 批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES(值1, 值2, ...) (值1, 值2, ...);
INSERT INTO 表名 (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

• 数据更新

UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ... [WHERE 条件];

• 数据删除

DELETE FROM 表名 [WHERE 条件];

2.3 DQL 数据查询

• DQL语句编写顺序如下，执行顺序为 from -> where -> group by -> having -> select -> ordered by -> limit

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 ordered by 排序字段列表 limit 分页参数;

• 基本查询（去重）

# 基本查询（查询多个字段）
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;
# 设置别名
SELECT 字段1 [AS 别名1] FROM 表名
# 去重查询
select distinct 字段 from 表名;

• 条件查询

# 条件 > = < !=
select * from 表名 where age >=15 and age<= 20;
select * from 表名 where age in(18, 20, 40);
# 查询空值
select * from 表名 where id is null;
select * from 表名 where id is not null;
# 模糊匹配
select * from 表名 where name like '__';    # 模糊匹配名字两个字符
select * from 表名 where id like '%X';    # 模糊匹配最后一个字符是X的ID

• 聚合函数

# 统计某一字段的总数（不统计null的数量）
select count(*) from 表名; 
select count(字段) from 表名;
# 统计平均值
select avg(字段) from 表名;
# 统计最大值
select max(字段) from 表名;
# 统计最小值
select min(字段) from 表名;
# 一个整合例子，统计某一字段的和
select sum(age) from emp where workaddress='成都';

• 分组查询
  • where：分组前进行过滤，不满足where条件不参与分组；where不能对聚合函数进行判断
  • having：分组后对结果进行过；having可以对聚合函数进行判断
  • 执行顺序：where -> 聚合函数 -> having

# 分组查询
select 字段列表 from 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后的过滤条件];
# 根据性别分组, 分别统计男性和女性员工数量
select gender, count(*) from emp group by gender;
# 根据性别分组, 统计男性和女性员工平均年龄
select gender, avg(age) from emp group by gender;
# 查询年龄小于45的员工, 并根据工作地址分组, 获取员工数量>=3的地址
select workaddress, count(*) from emp where age<=45 group by workaddress having count(*)>=3;

• 排序查询

# ASC 升序, DESC 降序, 按照第一个字段排序后, 再按第二个字段排序
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式 2;
# 根据年龄升序排序
select * from emp order by age asc;
# 年龄升序, 入职降序
select * from emp order by age asc, data desc;

• 分页查询

# 语法
select 字段列表 from 表名 limit 起始索引, 查询记录数;
# 查询第2页员工数据，每页展示10条
select * from emp limit 1, 10;

2.4 DCL 数据控制

• 用户控制（创建、修改、删除）

# 查询用户 （用户在mysql中的user表里）
use mysql;
select * from user;
# 创建用户（只能在本机访问/可以在任意地址访问）
create user 'username'@'localhost' identified by 'password';
create user 'username'@'%' identified by 'password';
# 修改密码
alter user 'username'@'localhost' identified with mysql_native_password by 'newpassword';
# 删除用户
drop user 'username'@'localhost';

• 权限控制

# 查询权限
show grants for 'username'@'host';
# 授予权限
grant 权限列表 on 数据库名.表名 to 'username'@'host';
# 撤销权限
revoke 权限列表 on 数据库名.表名 to 'username'@'host';

3.函数

• 字符串函数

# 字符串拼接
select concat('Hello', 'MySQL');
# 转小写
select lower('HELLO');
# 转大写
select upper('hello');
# 左侧填充（使用 '-' 在左侧填充 'Hi' 直到达到10个字符的长度）
select lpad('Hi', 10, '-');
# 右侧填充（使用 '-' 在右侧填充 'Hi' 直到达到10个字符的长度）
select rpad('Hi', 10, '-');
# 去除头部和尾部的空格（不包括中间）
select trim('  Hello  MySQL  ');
# 字符串截取（从第start位置开始，截取len个，index从1开始）
select substring('Hello MySQL', start, len);

# 例子：把ID统一为5位数，不足的补0
update emp set id = lpad(id, 5, '0');

• 数值函数

# 向上取整
select ceil(1.5);
# 向下取整
select floor(1.9);
# 模运算
select mod(6, 4);
# (0,1)随机数
select rand();
# 保留2位小数（四舍五入）
select rount(2.344, 2)

• 日期函数

# 当前日期
select curdata();
# 当前时间
select curtime();
# 当前日期和时间
select now();
# 年份
select year(now());
# 月份
select month(now());
# 日期
select day(now());
# 计算当前时间加70天后的时间
select data_add(now(), INTERVAL 70 DAY);
# 求两个时间差
select datadiff('2021-12-01', '2021-10-01');

• 流程函数

# 如果value为true，则返回返回 t，否则 f
select if(value, t, f);
# 如果value1不为空，则返回value1，否则返回value2
select ifnull(value1, value2);
# 如果val1为true，返回res1，否则返回默认值
select case when [val1] then [res1] when [val2] then [res2] else [default] end;
# 如果expr的值等于val1，返回res1，否则返回默认值
select case [expr] when [val1] then [res1] when [val2] then [res2] else [default] end;
# 使用范例
select 
    name,
    (case when math >=85 then '优秀' when math >= 60 then '及格‘ else '不及格' end) '数学成绩',
    (case when english >=85 then '优秀' when english >= 60 then '及格‘ else '不及格' end) '英语成绩'
from score;

4.约束

• 非空约束 NOT NULL
• 唯一约束 UNIQUE
• 主键约束 PRIMARY_KEY
• 默认约束 DEFAULT
• 检查约束 CHECK
• 外键约束 FOREIGN KEY（与另一个表主键关联）
  • NO_ACTION / RESTRICT 父表删除/更新记录时，检查该记录是否有对应外键，如果有则不允许删除/更新
  • CASCADE 父表删除/更新记录时，检查该记录是否有对应外键，如果有则删除/更新外键在子表中的记录
  • SET NULL 父表删除外键时，检查是否有对应外键，有则设置子表中的外键值为null（需要外键允许读取null）
  • SET DEFAULT 父表变更时，子表外键设置为一个默认值（Innodb不支持）

create table user(
    id int primary key auto_increment comment '主键, 且自动增长',
    name varchar(10) not null unique comment '非空, 且唯一',
    age int check (age > 0 && age <=120) comment '年龄介于0,120',
    status char(1) default '1' comment '状态, 默认为1'
) comment '用户表';

insert into user(name, age, status) values ('Tom', 19, '1');

# 创建时添加外键
create table 表名 (
    [constraint] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表别名)
};
# 修改添加外键
alter table 表名 add constraint foreign key (本张表外键的字段) references 主表 (对应另一张表的字段);

# 使用例子（注意关联的必须是主键）
alter table emp add constraint fk_emp_detp_id foreign key (dept_id) references dept(id);
alter table emp add constraint fk_emp_detp_id foreign key (dept_id) references dept(id) on update cascade on delete set null;
# 删除例子
alter table emp drop foreign key fk_emp_dept_id;

5.多表查询

• 内连接：两张表交集的部分
• 外连接
  • 左外连接：完全包含左表数据和两表交集的部分（两张表拼起来，如果右表有一部分是空的，只显示左表部分）
  • 右外连接：完全包含右表数据和两表交集的部分

• 自连接：把自己一张表当成两张表使用
  • 例如一张表中有员工id，员工姓名，领导id。可以通过内连接拼成两张表，然后使用内连接或外连接展示员工姓名-领导姓名
• 联合查询：把多次查询的结果合并形成一个新的结果集
• 子查询
  • 标量子查询：子查询返回的结果是单个值
  • 列子查询：子查询返回的值是一个列（IN、NOT IN、ANY、SOME任意满足一个、ALL全部满足）
  • 行子查询：子查询返回结果是一个行（=、!=、IN、NOT IN）
  • 表子查询：子查询返回结果多行多列（=）

# 多表查询 （笛卡尔积，将第一张表的每行和第二张表的每行排列组合）
select * from emp, dept;
# 隐式内连接
select * from emp, dept where emp.dept_id = dept.id;
# 显示内连接
select * from emp inner join dept on emp.dept_id = dept.id;
# 左外连接
select * from emp left outer join dept on emp.dept_id = dept.id;
# 右外连接
select * from emp right outer join dept on emp.dept_id = dept.id;
# 自连接（内连接，顶级领导的领导id为空）
select a.name, b.name from emp a, emp b where a.managerid = b.id;
# 自连接（外连接，外连接可以显示顶级领导，它的领导为空）
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;
# 联合查询（union all会直接合并两次结果集，单独union则会将重复项合并）
select * from emp where salary<5000
union [all]
select * from emp where age>50;

# 标量子查询（返回结果是单个值）
select * from emp where dept_id = (select id from dept where name = '销售部');
select * from emp where entrydata > (select entrydata from emp where name = 'xuan');
# 列子查询
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部';
select * from emp where salary > all (select salary from emp where dept_id = 3);
# 行子查询
select * from emp where (salary, managerid) = (select salary, mangerid from emp where name = 'xuan');
# 表子查询
select * from emp where (job, salary) in (select job, salary from emp where name = 'xuan' or name = 'yue');
select e.*, d.* from (select * from emp where entrydata > '2006-01-01') e left join dept d on e.dept_id = d.id;

6.事务

　　事务是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

　　例如转账：查询余额，余额足够，张三-1000的同时李四+1000

• 事务的四大特性
  • 原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败
  • 一致性（Consistency）：事务完成时，数据库必须从一个一致性状态转换到另一个一致性状态（A和B无论怎么转账和不变）
  • 隔离性（Isolation）：数据库系统提供的隔离机制，多个用户并发访问数据库时，多个并发事务相互隔离
  • 持久性（Durability）：事务一旦提交或回滚，他对数据库中的改变是永久的
• 并发事务问题
  • 脏读：一个事务读取到另一个事务还没有提交的事务（A进行数据更新后，B读取到A未提交的数据）

• • 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同（A读取数据，B更新并提交，A再读取数据，两次数据不同）

• • 幻读：一个事务按条件查询数据时，没有对应的数据行，但再插入数据时发现这行数据已经存在（事务A查询发现没有该数据，事务B进行插入，接下来事务A进行插入提示已有该数据插入失败，但是此时事务A再查询还是没有该数据，因为已经解决了不可重复读的问题，两次读取数据会相同）

• 事务隔离级别：Serializable隔离级别最高，可以解决全部问题，但同样性能最差

select @@transaction_isolation;    # 查看事务隔离级别
set [session|GLOBAL] transaction isolation level {read uncommitted | read committed | repeatable read | serializable } # 设置事务隔离级别

• 设置事务方式一：关闭事务自动提交，手动设置提交或回滚

select @@autocommint;    # 查看当前事务是否自动提交
set @@autocommint = 0;    # 将事务自动提交设置为否
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
commint;    # 如果执行成功应该调用commit提交事务
rollback;    # 如果执行失败应该调用rollback回滚事务

• 设置事务方式二：开启事务，不需要关闭事务自动提交

start transaction;    # 开启事务的方法1
begin;    # 开启事务的方法2
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
commint;    # 如果执行成功应该调用commit提交事务
rollback;    # 如果执行失败应该调用rollback回滚事务