MySQL基础操作*

开启与关闭MySQL服务

net start mysql80  # 启动MySQL服务
net stop mysql80  # 关闭MySQL服务

登录连接MySQL

# 第一种
mysql -u user -p  # 然后输入密码
# 第二种
mysql -u[user] -p[password]

创建用户

CREATE USER Tom IDENTIFIED BY '密码';

数据类型

• 整型

  1. 不设置符号时, 默认有符号
  2. 设置无符号, 关键字 UNSIGNED
  3. 若插入数值超出范围, 会警报并且以临界值替代该数值
  4. 不设置显示的最大长度时, 会有默认长度, 搭配关键字 ZEROFILL 会在位数不够时, 会用0填充

  类型	字节数	数值范围
  Tinyint	1	有符号: -128-127() 无符号: 0-255()
  Smallint	2	有符号: -32768-32767() 无符号: 0-65535()
  Mediumint	3	有符号: -8388608-8388607() 无符号: 0-16777215()
  Int, Integer	4	有符号: -2147483648-2147483647() 无符号: 0-4294967295()
  Bigint	8	有符号: -9223372036854775808-9223372036854775807() 无符号: 0-(9223372036854775807 * 2 + 1)()

• 小数

  1. M: 整数部位数 + 小数部位数
  2. D: 小数部位数
  3. 超过时会以临界值替代
  4. M, D可省略
  5. DECIMAL默认 M = 10, D = 0
  6. 定点型精确度较高

  浮点数类型, 定点数类型	字节数
  float	4
  double	8
  DEC(M, D), DECIMAL(M, D)	M+2

• 字符

  类型	最多字符数	描述及存储需求
  char(M)	M(默认为1)	M是[0, 255]的整数, 固定长度
  varchar(M)	M(无默认值)	M是[0, 65535]的整数, 可变长度

• 日期, 时间

  类型	字节数	最小值	最大值
  date	4	1000-01-01	9999-12-31
  datetime(不受时区影响)	8	1000-01-01 00:00:00	9999-12-31 23:59:59
  timestamp(受时区影响)	4	19700101080001	2038年某时刻
  time	3	-838:59:59	838:59:59
  year	1	1901	2155

数据定义语言(DDL)

数据库操作

创建数据库

create database first;  # 创建的数据库名为first
create database second character set gbk;  # 使用指定字符集GBK, 创建名为second的数据库

• IF NOT EXISTS

  CREATE DATABASE IF NOT EXISTS books;
  

查看数据库

show databases;  # 查看所有数据库
show create database first;  # 查看指定的数据库创建信息

修改数据库

alter database first character set gbk;  # 修改数据库的字符集为GBK

删除数据库

drop database first;  # 删除指定数据库
DROP DATABASE IF EXISTS first;

切换数据库

use first;  # 切换到指定的数据库
select database();  # 查看当前所使用的数据库

表操作

创建表

create table IS NOT EXISTS t_score 
(
    `name` varchar(10), 
    age    int, 
    height double(5, 1)
);  # 创建时需要写出表每一行的标题(如name, age, height)

查看表

show tables;  # 查看数据库中所有的表
show create table t_score;  # 查看某表的建表信息
desc tables;  # 查看一张表的字段详情(表头详情)

删除表

DROP TABLE IF EXISTS t_score;  # 删除指定表

修改表

ALTER TABLE 表名
ADD|DROP|MODIFY|CHANGE COLUMN 列明 [类型 约束];

# 添加表的字段
alter table t_student
    add name varchar(10); 
# 删除表的字段
alter table t_score
    drop age;
# 将表中的字段改为指定类型
alter table t_score
    modify height int;
# 修改表中字段的名字以及类型
alter table t_score
    change height weight int;
# 修改表的字符集
alter table t_score
    character set gbk;
# 修改表的名字
alter table t_score rename t_user;

• 修改列名

  ALTER TABLE 表名
  CHANGE COLUMN 旧名, 新名 类型;
  

• 修改列的类型或约束

  ALTER TABLE 表名
  MODIFY COLUMN 列名 新类型或约束;
  

• 添加新列

  ALTER TABLE 表名
  ADD COLUMN 列名 类型 [FIRST|(AFTER 另一列)];
  

• 删除列

  ALTER TABLE表名
  DROP COLUMN 列明;
  

• 修改表名

  ALTER TABLE 表名
  RENAME TO 新表名;
  

复制表

• 仅赋值表的结构

  CREATE TABLE 新表名 LIKE 就表名;
  

• 完整(或部分)赋值表

  CREATE TABLE 新表名
  SELECT * 
  # SELECT 字段
  FROM 旧表
  # WHERE 字段 条件;
  

• 仅复制某些字段

  CREATE TABLE 新表名
  SELECT 字段
  FROM 旧表名
  WHERE 0;  # 或用1 = 2(任何恒为假的条件)
  

数据操作语言(DML)*

条件子句

# 关键字 where
# 作用: 提供一个条件用于数据操作的数据筛选
# = : 相当于Java的 ==
# !=, >, <, >=, <= : 同Java
# != 的另一种写法: <>
# between ... and ... : 判断是否在指定的范围之间
# in(set) : 判断是否在指定的集合中
# is null : 判断是否为null
# and, or, not : 逻辑关系: 与或非

增添数据

# 给表中每一个字段都赋值
insert into t_student
values ('7', 5, 1);  # 三个数据都要求int, 但是字符串中如果是纯数字也可以用字符串赋值
# 给指定的字段赋值
insert into t_student (age, height)
values (10, 1);
# 一次给表里添加多行数据
insert into t_student (age, height, score)
values (1,1,1), (1,1,1);
##################################################
INSERT INTO beauty 
SET     id = 19,
    `name` = '刘涛', 
     phone = 214234;
##########支持子查询################################
INSERT INTO beauty (id, `name`, phone)
SELECT 26, '宋茜', '2628746278';
INSERT INTO beauty (id, `name`, phone)
SELECT id, `name`, phone
FROM boys
WHERE id = 3;
SELECT id, `name`, phone
FROM boys
WHERE id < 3;  # 此时为插入多条

1. 插入的值的类型要与列的类型一致或兼容

   INSERT INTO beauty (id, `name`, sex)
   VALUES (13, '唐艺昕', '女');
   # 支持插入多行
   INSERT INTO beauty (id, `name`, sex)
   VALUES (13, '唐艺昕', '女'),
   (19, '成果', '女');
   

2. 不可以为NULL的列必须插入值

3. 列的顺序可以调换, 但是对应插入值也应对换

4. 列数和值的个数必须一致

5. 可省略列明, 默认所有列, 插入值需按照表中每列的顺序

删除数据

• 多表连接的删除

  # 删除张无忌女朋友的信息
  DELETE b
  FROM beauty b 
  JOIN boys bo
  ON b.boyfriend_id = bo.id
  WHERE bo.`name` = '张无忌'
  LIMIT 1;  # (搭配LIMIT可以实现部分删除)
  

# 方法(1) 删除表中全部数据 delete from 
delete 
from t_student;
# 方法(2) truncate (效率比delete from 高)
# 1. 记录当前表中的字段信息
# 2. 删除整张表
# 3. 按照记录的字段信息重新创建一张一样的表
truncate t_student;
# 删除指定数据
delete
from t_student
where name = 'xiaoming';
# 删除不在指定集合中的数据
delete
from t_student
where age not in (1, 2, 3);

• DELETE 和 TRUNCATE 区别
  1. DELETE可以搭配WHERE, TRUNCATE不行(意味着只能删除整张表)
  2. TRUNCATE删除效率高一点
  3. 如果删除表中有自增长列
     • DELETE删除后再插入数据, 自增长列的值会接着断点继续
     • TRUNCATE删除后再插入数据, 自增长列的值从1开始
  4. DELETE删除有返回值, TRUNCATE没有返回值
  5. TRUNCATE删除不能回滚, DELETE删除可以回滚

修改数据

# 关键字 update, 修改表中某些字段的值
# 修改一个字段
update t_student
set score = 20
where age = 3;
# 修改多个字段
update t_student
set age    = 19,
    height = 166,
    score  = 98
where name = 'Gogoing';

• 修改张无忌的女朋友的手机号

  UPDATE boys bo
  JOIN beauty b
  ON bo.id = b.boyfriend_id
  SET b.phone = 114
  WHERE bo.`name` = '张无忌';
  

• 修改没有朋友的女神的男朋友编号为2

  UPDATE boys bo
  RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
  SET b.boyfriend_id = 2
  WHERE bo.id IS NULL;
  # WHERE b.boyfriend_id IS NULL;
  

数据查询语言(DQL)*

基础查询

# 1. 基础查询 [select 字段(列名) from 表名]
# select的查询结果是一张虚拟表
# 查看指定列所有信息
select name
from t_student;
select name, height, score
from t_student;
# 查看所有列
select *
from t_student;

条件查询

# 2. 条件查询
# 查看满足条件的所有行信息(*表示查询所有字段)
select *
from t_student
where score < 1000 and height < 200;
select *
from t_student
where score between 60 and 100;
select *
from t_student
where height - score > 60 or score = 1;

模糊查询

# 3. 模糊查询 (关键字 like)
# _ : 通配符, 匹配一位任意字符
# % : 通配符, 匹配任意位任意字符
# 根据指定条件查询表中相似的数据
# 查询name以"Go"开头的行信息
select *
from t_student
where name like 'Go%';
# 查询score是一位数的行信息
select *
from t_student
where score like '_';

结果去重

# 使用distinct修饰的字段进行去重
# distinct只能紧跟在select后
# distinct后如果有多个字段, 则如果所有字段的值都相同才去重
select distinct age, name
from t_student;

字段别名

# 2. 给列起别名(查询结果的虚拟表的字段别名, 关键字as可省略)
select name as '姓', age as '年龄'
from t_student;

字段求和

# 3. 查询表中所有字段的值的总和
select *, age + height + score as 'sum'
from t_student;
# 列数据之间的相加, null和任何值相加都是null
# ifnull(字段, value)将判断某字段的值如果是null则设置为value
select *, age + height + ifnull(score, 0)
from t_student;

结果排序

# 4. 查询结果排序(关键字 order by)
# 在查询语句的最后方添加 order by 列(默认升序排序, 或者在最后添加asc)
select *, age + height + score as 'sum'
from t_student
order by score asc;
# 如果需要降序排序, 则在最后添加 desc
select *, age + height + score as 'sum'
from t_student
order by score desc;
# 如果根据多个字段排序(例如, 先按score, 再按sum)
select *, age + height + score as 'sum'
from t_student
order by score, sum asc;

聚合函数

# 5. 聚合函数(count(), sum(), avg(), max(), min())
# 聚合函数作用于列
# count()不会将null值计入
select count(name)
from t_student;
select count(name)
from t_student
where height > 0;
# sum()计算某一字段的值总和
select sum(score)
from t_student;
# avg()计算某一字段的值平均值
select avg(score)
from t_student;
# max(), min()
select max(score), min(score)
from t_student;

分组查询

# 6. 分组(将查询的结果, 按某一个字段进行分组, 当这个字段的值相同时视为一个分组)
# 先给表添加一个字段group(加反引号, 防止与关键字混淆)
alter table t_student
    add `group` int;
# 查询每个分组中有多少行数据(count()作用于每个分组)
select `group`, count(`name`)
from t_student
group by `group`;
# 查询每个分组某个字段的平均值/总和
select `group`, avg(height)
from t_student
group by `group`;
select `group`, sum(score)
from t_student
group by `group`;
# where必须放到分组之前(where过滤后才分组)
# 查询第二组有多少人
select `group`, count(`name`)
from t_student
where `group` = 1
group by `group`;
# 查询每个分组中有多少满足条件的行数据
select `group`, count(`name`)
from t_student
where height > 170
group by `group`;
# 7. 关键字 having(与where相似)
# (1) where写在分组前(先过滤再分组), having写在分组后(先分组再过滤)
# (2) where子句中不允许使用聚合函数, having可以
select `group`, avg(age)
from t_student
group by `group`
having avg(age) < 22;

分页查询

要显示的数据一页显示不全, 则需要分则提交SQL请求

1. LIMIT 语句放在查询语句最后

2. 公式

   SELECT 字段
   FROM 表
   LIMIT (start_index - 1) * size, size;
   

# 8.分页查询(关键字 limit)
# limit i, n 其中i代表从第i个开始查, n代表每一页显示几个
select *
from t_student
limit 0,2;
# 真分页(物理分页): 从数据库查询数据的时候, 将数据分段读取
# 假分页(逻辑分页): 直接从数据库中查询所有数据(在程序中进行逻辑处理)
# 假分页效率高, 但数据量过大时会内存溢出
SELECT 字段
FROM 表
[JOIN TYPE JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序的字段]
LIMIT [offset,] size;  # 起始索引, 显示数量

• 查询有奖金的员工信息并且工资高的前十名

  SELECT *
  FROM employees
  WHERE bouns IS NOT NULL
  ORDER BY salary DESC
  LIMIT 10;  #LIMIT 0, 10
  

• 查询语句关键字顺序

  SELECT 字段             (7)
  FROM 表                     (1)
  连接类型 JOIN 表2        (2)
  ON 连接条件                  (3)
  WHERE 筛选条件               (4)
  GROUP BY 分组字段        (5)
  HAVING 分组后的筛选条件       (6)
  ORDER BY 排序字段        (8)
  LIMIT 偏移量, 条目数;         (9)
  

联合查询

查询多个表, 表之间没有直接联系, 但查询信息一致

1. 多条查询语句的查询列数一致
2. 多条查询语句的每一列类型和顺序最好一致
3. UNION 关键字默认去重, UNION ALL 不去重

SELECT CN_id
FROM t_china
UNION
SELECT fo_id
FROM t_foreign;

数据完整性

# 创建表格式对字段添加约束，使这个字段对应的值保持一定的完整性，保证用户储存的数据正常存入数据库中

• 六大约束
  • 非空约束: NOT NULL 保证字段的值不能为空
  • 默认约束: DEFAULT 保证字段有默认值
  • 主键约束: PRIMARY KEY 保证字段的值唯一, 非空
  • 唯一约束: UNIQUE 保证字段的值唯一, 可为空
  • 检查约束: CHECK 保证字段的值在指定范围内(MySQL不支持)
  • 外键约束: FOREIGN KEY 限制两个表的关系, 保证字段的值必须引用自主表的某列的值

实体完整性

# 实体(Entity): 表中的一行数据称为一个实体
# 实体完整性: 确保一行的数据是唯一的不重复的

主键约束

# 主键(primary key): 作为表中一行数据唯一标志的字段(不允许null或重复)
# 添加主键
# 创建表的同时添加一个主键
create table t_person
(
    id     int primary key, # 使用id作为(主键)一行数据的唯一标志
    `name` varchar(10),
    age    int
);
# 创建表时添加组合主键(组合主键的所有字段都一样才算重复)
create table t_person
(
    id int,
    `name` varchar(10),
    age int,
    primary key (id, `name`)
);
# 表格创建后(没有主键), 追加主键
# 追加主键时, 该字段的值必须没有重复
alter table t_person
add primary key(id, `name`);

唯一约束

# 约束一个字段, 使得这个字段的值不允许重复
# 唯一约束不是主键
# (1)主键不允许为null, 而唯一约束可以为null
# 唯一约束(关键字 unique)
create table t_person
(
    id     int primary key,
    `name` varchar(10) unique,
    age    int
);

自增约束

AUTO_INCREMENT 标识字段

1. 自增约束(标识列)要求该字段是一个KEY
2. 一个表至多有一个标识列
3. 标识列类型只能是数值型
4. 可以通过 SET AUTO_INCREMENT = 步长 来设置自增步长

# 关键字 auto_increment
# 约束某个字段, 在没有赋值时, 该字段的值会根据上一个数值自动增长1
create table t_person
(
    id     int primary key auto_increment,
    `name` varchar(10) unique,
    age    int
);
# 当下面这个语句执行两次时, 第二次会报错(因为主键重复)
insert into t_person
values (1, 'Ming', 12);
# 再执行这个语句, 自动生成的自增约束不是2而是3(因为上面虽然没有生成两行数据但是第二次执行时有记录)
insert into t_person (`name`, age)
values ('Ning', 8);
# (id = 3时)执行删除语句, 再执行上一个语句, 自增约束是4
delete from t_person
where id = 3;
# 结论: 自增约束不受'添加失败', '删除(甚至删除所有数据)'等影响, 一直有记录
# 注意: 删除整个表时, 自增约束会重置(删除整个表和清空表的数据不一样)

• 修改表时设置, 删除标识列

  ALTER TABLE 表名
  MODIFY COLUMN 列名 字段类型 约束类型 AUTO_INCREMENT;
  # 删除
  ALTER TABLE 表名
  MODIFY COLUMN 列名 字段类型 [约束类型];
  

域完整性

# 对某列数据的约束

非空约束

# 关键字 not null 约束这一列的值不可以是null
create table t_person
(
    id     int,
    `name` varchar(10) not null,
    age    int
);

默认值约束

# 添加数据时没有给默认值约束的列赋值, 则会按设定的默认值赋值
create table t_person
(
    id     int,
    `name` varchar(10),
    age    int default 18
);

引用完整性

# 某张表的某字段的值只能从另一张表中获取
# 以下为例: 员工表中的d_id只能从部门表中的d_id中获取
# 部门有多个员工, 员工只能属于一个部门
# 1. 员工表(id, name, age, salary, 部门id)
# 2. 部门表(id, name,duty)
# 两张表依靠d_id关联起来
# "一"对"多"的关系(部门"一", 员工"多", 员工是x, 部门是f(x))
create table t_dept # 部门表
(
    d_id   int primary key,
    d_name varchar(10) not null unique,
    d_duty varchar(20) default '为人民服务'
);
create table t_employee
(
    e_id     int primary key,
    e_name   varchar(5) not null,
    e_age    int,
    e_salary int default 5000,
    d_id     int # 表示员工是哪个部门的
);
# 外键约束
# 将"一"对"多"中"多"的一方, 用来维系表之间关系的字段, 做成"外键"
# 外键约束的值, 只能从另一张表中关联的字段中获取
# 1. 建表时添加外键
create table t_employee
(
    e_id     int primary key,
    e_name   varchar(5) not null,
    e_age    int,
    e_salary int default 5000,
    d_id     int,
    constraint fk_employee_dept_d_id foreign key (d_id) references t_dept (d_id)
);
# 2. 追加外键(只适用于添加外键后该字段的值仍是合法的)
# 员工表中的外键d_id引用自部门表的d_id
# foreign key (d_id) references t_dept (d_id)
alter table t_employee
    add constraint fk_employee_dept_d_id foreign key (d_id) references t_dept (d_id);
# 添加外键注意事项
# 1. 数据库存储引擎
#    MyISAM: 存取效率极高, 但是不支持外键
#    InnoDB: 存取效率不没有上一个高, 但支持外键
# 2. 修改存储引擎
alter table t_employee engine = 'InnoDB';

• 修改表时追加约束

  # 列级约束写法(所有约束)
  ALTER TABLE 表名
  MODIFY COLUMN 字段名 字段类型 约束类型;
  # 表级约束写法(主键约束, 唯一约束)
  ALTER TABLE 表名
  ADD 约束类型 (字段名);
  

• 添加表级约束

  [CONSTRAINT 约束别名] 约束类型 (被约束字段名) REFERENCES 被引用表名 (被引用字段)

  可以不起别名

  CREATE TABLE stuinfo (
    id      INT,
      gender  VARCHAR(20),
      seat    INT,
      age     INT,
      majorid INT,
      CONSTRAINT pk PRIMARY KEY (id, seat),  # 可以用多个字段组合成主键
      CONSTRAINT uq UNIQUE (seat),
      [CONSTRAINT fk_stuinfo_major] FOREIGN KEY (majorid) REFERENCES majoi (id)
  );
  

• 修改表时删除约束

  ALTER TABLE 表名
  MODIFY COLUMN 字段名 字段类型;  # 也就是把原来有的约束省略
  # 删除主键约束
  ALTER TABLE 表名
  DROP PRIMARY KEY;
  # 删除唯一约束
  ALTER TABLE 表名
  DROP INDEX 唯一约束的字段名;
  # 删除外键
  ALTER TABLE 表名
  DROP FOREIGN KEY 外键别名;
  

• 主键约束和唯一约束的异同

  约束类型	唯一性	允许为空	在表中的数量	允许组合
  主键约束	√	×	至多一个	√, 不推荐
  唯一约束	√	√	无限制	√, 不推荐

• 外键

  1. 在从表设置外键关系

  2. 从表的外键字段和主表的关联字段类型要求一致或兼容

  3. 主表的关联字段必须是一个KEY(一般是主键或唯一键)

  4. 插入数据时, 先主表后从表

  5. 删除数据时, 先从表后主表

     # 方式一: 级联删除(DELETE CASCADE)
     # 删除主表数据时, 从表对应数据也会删除
     ALTER TABLE 从表
     ADD FOREIGN KEY (字段1)
     REFERENCES 主表 (字段2)
     ON DELETE CASCADE;
     # 方式二: 级联置空(DELETE SET NULL)
     ALTER TABLE 从表
     ADD FOREIGN KEY (字段1)
     REFERENCES 主表 (字段2)
     ON DELETE SET NULL;
     

多表

从多张表查询到我们要的数据

合并查询结果集

将多次查询的结果联合到一起

# 合并查询结果集时, 数据类型可以不同, 但是列数要一致
# union: 合并查询结果集, 去重
# union all: 合并查询结果集, 不去重
select e_name, e_salary
from t_employee
union  # union all 用法一样
select e_age, e_salary
from t_employee;

连接查询

# 将多张有关联的表连接起来, 从中查询数据
# 进行连接查询的多张表, 外键有无皆可
# 连接查询会有一个问题: 笛卡尔积
# 因此需要去笛卡尔积(where t_employee.d_id = t_dept.d_id)
# 此语句是MySQL的扩展
select e_id, e_name, e_salary, t_employee.d_id, d_name
from t_employee,
     t_dept
where t_employee.d_id = t_dept.d_id;
# 此语句是SQL的标准(join后用on进行去笛卡尔积)
# 如果连接不止两张表, 则在on的去笛卡尔积操作后再join...on.........................
select e_id, e_name, e_salary, d_name
from t_employee
         join t_dept on t_employee.d_id = t_dept.d_id;

内连接

# 查询多张表的交集(都出现的数据)
# 关键字 inner(但可省略)
# 筛选条件放在where后面, 连接条件放在on后面, 提高分离性
# 1. 等值连接
select *
from t_employee
         inner join t_dept on t_employee.d_id = t_dept.d_id;

外连接

# 查询在两张表中同时出现的数据, 和在其中一张表中出现的数据
# 用于查询一个表中有, 另一个表中没有的数据
# 外连接的查询结果为主表中的所有数据
    # 从表(副表)中如果匹配的到, 则显示匹配的值, 否则显示NULL
    # 外连接 = 内连接 + 主表有而从表无的数据
# outer可省略
    # 左外连接: 左是主表
    # 右外连接: 右是主表

• 左外连接

  select *
  from t_employee
           left outer join t_dept on t_employee.d_id = t_dept.d_id;
  

• 右外连接

  # 右外连接
  select *
  from t_employee
           right outer join t_dept on t_employee.d_id = t_dept.d_d;
  

• 全外连接

  # 全外连接 = 内连接 + 表1有表2无 + 表2有表1无
  # full join
  

• 交叉连接

  # 交叉连接不带WHERE子句
  # 返回被连接的两个表所有数据行的笛卡尔积
  # 结果行数 = 表1中符合条件的数据行数乘表2中符合条件的数据行数
  

• 双表查询总结

  • A (A - B + AB)

    select col_name
    from A
    left join B
    on A.`key` = B.`key`;
    

  • AB

    select col_name
    from A
    inner join B
    on A.`key` = B.`key`
    

  • B (B - A + AB)

    select col_name
    from A
    right join B
    where A.`key` = B.`key`;
    

  • A - B

    select col_name
    from A
    left join B
    on A.`key` = B.`key`
    where B.`key` is null;
    

  • B - A

    select col_name
    from A
    right join B
    on A.`key` = B.`key`
    where A.`key` is null;
    

  • A B

    select col_name
    from A
    full join B
    on A.`key` = B.`key`;
    

  • (A B) - AB

    select col_name
    from A
    full join B
    on A.`key` = B.`key`
    where A.`key` is null or B.`key` is null;
    

子查询

出现在其他语句中的select语句成为子查询(内查询)

• 子查询结果集行列数

  • 标量子查询(结果只有一行一列)
  • 列子查询(结果集只有一列多行)
  • 行子查询(结果集有一行多列)
  • 表子查询(结果集一般多行多列)

• 子查询出现的位置

  • select后面: 仅仅支持标量子查询
  • from后面: 表子查询, 子查询结果充当一张表, 并且必须起别名
  • where或having后面: 标量子查询, 列子查询, 行子查询
  • exists(相关子查询): 表子查询

• 特点

  • 子查询放在小括号内

  • 子查询一般放在条件的右侧

  • 标量子查询, 一般搭配着单行操作符使用

    > < >= <= = <>

  • 列子查询: 一般搭配着多行操作符使用

    in; any;some; all;
    

  • 子查询的执行优先于主查询, 主查询的条件用到了子查询的结果

• 标量子查询

  • 谁的工资比Abel高

    1. 查询Abel的工资

       select salary
       from employees
       where last_name = 'Abel';
       

    2. 查询满足salary > 上一步查询结果的员工信息

       select *
       from employees
       where salary > (
        select salary
           from employees
           where last_name = 'Abel'  # 子查询最后不要加分号
       );
       

  • 返回job_id与141号员工相同, salary比143号员工多的员工

    select *
    from employees
    where job_id = (
        select job_id
        from employees
        where employee_id = 141
    ) and salary > (
        select salary
        from employees
        where employee_id = 143
    );
    

  • 返回工资最少的员工的last_name, job_id, salary

    SELECT last_name, job_id, salary
    FROM employees
    WHERE salary = (
        SELECT MIN(salary)
        FROM employees
    );
    

  • 查询最低工资>50号部门最低工资的部门id及其最低工资

    SELECT department_id, MIN(salary) m
    FROM employees
    GROUP BY department_id
    HAVING m > (
        SELECT MIN(salary)
        FROM employees
        WHERE department_id = 50
    );
    

• 列子查询

  • 返回location_id=1400或1700的部门中所有员工姓名

  SELECT last_name
  FROM employees
  WHERE department_id IN (
  SELECT DISTINCT department_id
  FROM employees
  WHERE location_id IN (1400, 1700)
  );

  
  * 返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的工号, 姓名, job_id, salary
  
  ```mysql
  SELECT id, last_name, job_id, salary
  FROM employees
  WHERE salary < ANY(
      SELECT DISTINCT salary
      FROM employees
      WHERE job_id = 'IT_PROG'
  ) AND job_id <> 'IT_PROG';
  

  • 查询员工工号=102的部门名

    SELECT (
        SELECT department_name
        FROM departments d
        JOIN employees e
        ON d.d_id = e.d_id
        WHERE e.e_id = 102
    ) '部门名';
    

• 行子查询

  • 返回工号最小, 工资高的员工信息

    SELECT *
    FROM employees
    WHERE employee_id = (
        SELECT MIN(employees_id)
        FROM employee
    ) AND salary = (
        SELECT MAX(salary)
        FROM employees
    );
    

  • 查询每个部门员工个数

    SELECT department.*, (
        SELECT COUNT(*)
        FROM employees e
        WHERE e.department_id = department.department_id
    ) '员工个数'
    FROM department;
    

  • 查询每个部门平均工资的等级

    SELECT t_avg.*, g.grade_level
    FROM (
        SELECT d_id, AVG(salary) avg_s
        FROM employees
        GROUP BY d_id
    ) t_avg
    JOIN job_grades g
    ON t_avg.avg_s BETWEEN lowest_sal AND highest_sal;
    

• 相关子查询(exists后面)

  查询结果是bool值(0或1)

  # 查询有员工的部门名
  SELECT d_name
  FROM departments d
  WHERE EXISTS (
    SELECT *
      FROM employees e
      WHERE d.d_id = e.d_id
  );
  # 等价于
  SELECT d_name
  FROM departments d
  WHERE d.d_id IN (
    SELECT d_id
      FROM employees
  );
  

case-when语句

# 输出学生表的信息后, 再接上一列数据, 该列数据由case-when决定
select *,
       case
           when score < 60
               then '不及格'
           when score < 80
               then '良'
           else '优'
           end 'grade'
from t_student;
select *,
       case score
           when 90
               then '牛逼'
           when 80
               then '还行'
           else '嗯嗯'
           end 'grade'
from t_student;
# 行转列
select s_name,
       max(case s_subject
               when '语文' then score end) 'Chinese',
       max(case s_subject
               when '数学' then score end) 'Math',
       sum(score)
from t_subject
group by s_name;

事务控制语言(TCL)

Transaction Control Language

事务

1. 一个或一组SQL语句组成的执行单元
2. 单元的SQL语句要么全执行, 要么全不执行
3. 单元的所有SQL语句都是相互依赖的
4. 某一条出现错误, 整个单元会回滚(回到最初状态)

事务中的DELETE是可以回滚的, 但是TRUNCATE不能

ACID属性

1. Atomicity(原子性)

   事务不可分割, 事务中所有操作要么都执行, 要么都不执行

2. Consistency(一致性)

   事务必须是数据库从一个一致性状态变换到另一个一致性状态

   举例: 转账前后两用户资金和不变

3. Isolation(隔离性)

   一个事务的发生不会对并发执行的其它事务产生影响

4. Durability(持久性)

   一个事物一旦被提交, 它对数据库的改变是永久的(不可撤销)

创建事务

• 隐式事务: 没有明显的开启和结束标记

  比如INSERT, UPDATE, DELETE语句

• 显式事务: 有明显的开启和结束标记

  1. 先设置自动提交功能为禁止(每次开启显式事务都要设置)

     SET AUTOCOMMIT = 0;
     

  2. 开启显式事务

     START TRANSACTION;  # 这句是可选的
     

  3. 编写事务中的SQL语句(增删改查 INSERT, DELETE, UPDATE, SELECT)

  4. 结束显式事务

     COMMIT;  # 提交事务
     ROLLBACK;  # 回滚
     

数据库的隔离级别

• 并发问题

  • 脏读

    对于两个事务T1, T2, T1读取了已经被T2更新但还没有提交的数据, 若T2回滚, 则T1的读取的内容临时且无效

  • 不可重复读

    对于两个事务T1, T2, T1读取了一个字段, 然后T2更新了该字段, 之后T1再读取同个字段的时候, 值就不同了

  • 幻读

    对于两个事务T1, T2, T1从一个表中读取一个字段, 然后T2在该表中插入了新的行, 如果T1再读取同个表, 就会多出几行

• MySQL的4种事务隔离级别

  每启动一个MySQL程序, 就会获得一个单独的数据库连接

  每个数据库连接都会有一个全局变量 @@tx_isolation 表示当前事务隔离级别

  • READ UNCOMMITED(读取未提交数据)

    允许事务读取未被其他事务提交的变更

    该级别下, 三种并发问题都可能出现

  • READ COMMITTED(读取已提交数据)

    只允许事务读取已被其他事务提交的变更

    避免脏读, 其余两种并发问题仍存在

  • REPEATABLE READ(可重复读)

    MySQL的默认事务隔离级别

    确保事务可以多次从一个字段中读取相同值

    这个事务持续期间禁止其他事务对这个字段进行更新

    避免脏读和不可重复读, 仍存在幻读的可能

  • SERIALIZABLE(串行化)

    确保事务可以从一个表中读取相同的行

    在这个事务持续期间禁止其他事务对该表执行增删改

    避免所有并发问题, 但性能十分低下

• 查看当前隔离级别

  SELECT @@tx_isolation;
  

• 设置当前MySQL连接的隔离级别

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• 设置数据库管理系统的全局隔离级别

  SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• SAVEPOINT 保存点

  SET AUTOCOMMIT = 0;
  START TRANSACTION;
  SQL语句1
  SAVEPOINT a;  # 设置保存点
  SQL语句2
  ROLLBACK TO a;  # 回滚到保存点a
  

存储引擎

视图

MySQL5.1后出现的新特性

虚拟表, 和普通表一样使用, 通过表动态生成的数据, 行和列数据来自定义视图的查询中使用的表, 动态生成所以只保存了SQL逻辑不保存查询结果

应用场景

1. 多个地方用到用样的查询结果
2. 该查询使用的SQL语句比较复杂

创建

CREATE VIEW 视图名
AS
SQL语句;

修改

CREATE OR REPLACE VIEW 视图名 # 存在就替换, 否则创建
AS
SQL语句;
#######################################
ALTER VIEW 视图名
AS
SQL语句;

刪除

DROP VIEW 视图名, 视图名, ...;

查看

DESC 视图名;
SHOW CREATE VIEW 视图名;

更新

• 以下类型的视图不能更新

  • 包含以下关键字的SQL语句: 分组函数, DISTINCT, GROUP BY, HAVING, UNION, UNION ALL

  • 常量视图

    CREATE VIEW v
    AS
    SELECT '常量';
    

  • SELECT中包含子查询

  • JOIN(即多表查询)

  • FROM一个不能更新的视图

  • WHERE子句的子查询引用了FROM子句的表

    SELECT 字段1
    FROM 表1
    WHERE 字段2 IN (
        SELECT 字段3
        FROM 表1
        WHERE 字段3 条件;
    );
    

1. 插入

   INSERT INTO 视图名
   VALUES ('张飞', 'zf@qq.com');
   

2. 修改

   UPDATE 视图名
   SET 字段名 = 新值
   WHERE 字段名 = 旧值;
   

3. 删除

   DELETE FROM 视图名
   WHERE 字段名 = 某值;
   

案例

• 查询姓张的学生姓名和专业名称

  1. 普通写法

  SELECT stuName, majorName
  FROM students s
  JOIN majors m
  ON s.`major_id` = m.id
  WHERE stuName LIKE '张%';
  

  2. 视图写法

  CREATE VIEW v1
  AS
  SELECT stuName, majorName
  FROM students s
  JOIN majors m
  ON s.major_id = m.id;
  SELECT v1
  WHERE stuName LIKE '张%';
  

• 查询各部门的平均工资级别

  CREATE VIEW v
  AS
  SELECT AVG(salary) avg_s, grade_level
  FROM employees e
  JOIN job_grade g
  ON avg_s BETWEEN g.lowest AND g.highest
  GROUP BY e.d_id;
  

• 查询电话号码以"011"开头的员工姓名和工资, 邮箱

  CREATE OR REPLACE VIEW v
  AS
  SELECT e_name n, salary s, email m, phone p
  FROM employees;
  SELECT n, s, m
  FROM v
  WHERE p LIKE '011%';
  

• 查询部门最高工资高于12000的部门信息

  CREATE VIEW v
  AS
  SELECT MAX(salary) m, d_id
  FROM employees e
  GROUP BY d_id
  HAVING m > 12000;
  SELECT d.*
  FROM departments d
  JOIN v
  ON d.id = v.d_id;
  

优点

• 重用SQL语句
• 简化复杂的SQL操作
• 保护数据, 提高安全性

变量

系统变量

由系统提供, 属于服务器层面、

全局变量需要加 GLOBAL 关键字, 会话变量则 SESSION 可以省略

• 查看所有系统变量

  SHOW GLOBAL|[SESSION] VARIABLES;
  

• 查看满足条件的部分系统变量

  SHOW GLOBAL|[SESSION] VARIABLES 
  LIKE '%char%';
  

• 查看指定的某个系统变量的值

  SELECT @@GLOBAL|[SESSION].系统变量名;
  

• 为系统变量赋值

  SET GLOBAL|[SESSION] 系统变量名 = 值;
  SET @@GLOBAL|[SESSION].系统变量名 = 值;
  

• 全局变量

  作用域: 所有会话(连接),服务器每次启动将为所有的全局变量赋初始值

  重启MySQL服务会重置全局变量的值

• 会话变量

  作用域: 仅当前会话(连接)

自定义变量

• 用户变量

  作用域: 当前会话(同会话变量)

  赋值操作符: = 或 :=, 推荐':='

  应用在任何地方, BEGIN END 里面或外面

  1. 声明并初始化

     SET @用户变量名 = 值;
     SET @用户变量名 := 值;
     SELECT @用户变量名 := 值;
     

  2. 赋值(更新)

     # 方式一: 同声明初始化
     SET @用户变量名 = 值;
     SET @用户变量名 := 值;
     SELECT @用户变量名 := 值;
     # 方式二: 从表中查询一个值赋给变量
     SELECT 字段 INTO 变量名
     FROM 表名;
     

  3. 使用(查看)

     SELECT @用户变量名;
     

• 局部变量

  仅在定义它的 BEGIN END 中有效, 并且只能在BEGIN END中的首句

  1. 声明

     DECLARE 变量名 类型;
     DECLARE 变量名 类型 DEFAULT 值;
     

  2. 赋值

     SET 局部变量名 = 值;
     SET 局部变量名 := 值;
     SELECT @局部变量名 := 值;
     SELECT 字段 INTO 局部变量名
     FROM 表名;
     

  3. 使用(查看)

     SELECT 局部变量名;
     

  自定义变量	作用域	位置	语法
  用户变量	当前会话	会话中的任何地方	必须加@, 不用限定类型
  局部变量	BEGIN END 中	BEGIN END 中的首句	一般不用加@, 需要限定类型

存储过程

一组预先编译好的SQL语句的集合, 理解成批处理语句

类似于Java的方法

优点

1. 提高代码重用性
2. 简化操作
3. 提高效率(减少编译次数, 减少和数据库服务器的连接次数)

创建

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体  # 一组合法的SQL语句
END;

• 参数列表: 参数模式, 参数名, 参数类型

  • 参数模式
    • IN: 参数作为输入(需要调用方传入值)
    • OUT: 参数作为输出(作为返回值)
    • INOUT: 参数作为输入和输出(既需要传入值, 又能作为返回值)

• 如果存储过程体只有一个语句, 则BEGIN和END可省略

• 存储过程体中每条SQL语句结尾必须加分号

• 存储过程的结尾可以用 DELIMITER 重新设置

  DELIMITER 结束标记;
  

调用

CALL 存储过程名(实参列表);

删除

DROP PROCEDURE 存储过程名; # 一次只能删一个存储过程

查看

SHOW CREATE PROCEDURE 存储过程名;

案例

• 插入值

DELIMITER $
CREATE PROCEDURE P()
BEGIN
    INSERT INTO 表 (字段)
    VALUES (值);
END $
CALL p() $

• 根据变量查询另外的变量(IN参数模式)

CREATE PROCEDURE p(IN beautyName VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
    RIGHT JOIN beauty b 
    ON bo.id = b.boyfriend_id
    WHERE b.`name` = beautyName;
END $
CALL p('柳岩');

• 实现"用户是否登录成功"功能(IN参数模式)

CREATE PROCEDURE p(IN userName VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0;  # 声明并初始化
    SELECT COUNT(*) INTO result  # 赋值
    FROM admin
    WHERE admin.username = userName
    AND admin.`password` = `password`;
    SELECT IF(result > 0, '成功', '失败');  # 返回值-使用变量
END $

• 根据beauty名, 返回对应boy名(IN和OUT参数模式)

  OUT 类型的变量会自动返回

CREATE PROCEDURE p(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO boyName
    FROM boys bo
    RIGHT JOIN beauty b
    ON bo.id = b.boyfriend_id
    WHERE b.`name` = beautyName;
END $
# 调用
CALL p('小昭', @bName)$
SELECT @bName$

• 根据beauty名, 返回对应boy名和魅力值(IN和OUT参数模式)

CREATE PROCEDURE p(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
    SELECT bo.boyName, bo.userCP INTO boyName, userCP
    FROM boys bo
    RIGHT JOIN beauty b
    ON bo.id = b.boyfriend_id
    WHERE b.`name` = beautyName;
END $
# 调用
CALL p('小昭', @bName, @bUserCP)$
SELECT @bName$, @bUserCP$

• 传入a, b两个值, 将a, b翻倍返回

CREATE PROCEDURE p(INOUT a INT, INOUT b INT)
BEGIN
    SET a = a * 2;
    SET b = b * 2;
END$
# 调用
SET @m = 10$
SET @n = 20$
CALL p(@m, @n)$
SELECT @m, @n$

• 传入一个日期, 格式化为xx年x月x日并返回

CREATE PROCEDURE p(IN `date` DATETIME, OUT result VARCHAR(50))
BEGIN
    SELECT DATE_FORMAT(`date`, '%y年%m月%d日') INTO result;
END$
# 调用
CALL p(NOW(), @strDate)$
SELECT @strDate$

函数

类似于存储过程, 但是必须有且只有一个返回值

创建

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END$

• 参数列表: 参数名 参数类型

• 函数体: 必有 RETURN 语句, 没有会报错

  函数体只有一句时可以省略BEGIN END

• 使用DELIMITER语句设置结束标记

调用

SELECT 函数名(实参列表)

案例

• 返回公司员工个数

CREATE FUNCTION f() RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    SELECT COUNT(*) INTO result
    FROM employees;
    RETURN result;
END$

查看

SHOW CREATE FUNCTION f;

删除

DROP FUNCTION f;

流程控制结构

顺序结构: 程序从上而下执行

分支结构: 程序从多条路径中选一条执行

顺环结构: 程序在满足条件时重复执行一段代码

分支结构

• IF()函数

  # 如果表达式1成立, 返回表达式2的值, 否则返回表达式3的值
  IF(表达式1, 表达式2, 表达式3);
  

• CASE结构

  • 等值判断

  CASE 变量|表达式|字段
  WHEN 判断的值 THEN 返回值1或语句;
  WHEN 判断的值 THEN 返回值2或语句;
  ...
  ELSE 返回值n或语句;
  END CASE;
  

  • 区间判断

  CASE
  WHEN 判断条件 THEN 返回值1或语句
  WHEN 判断条件 THEN 返回值2或语句
  ...
  ELSE 返回值n或语句
  END CASE;
  

  • 特点
    • 可作为表达式(值)嵌套在其它语句中
    • 可作为独立的语句使用, 但只能放在BEGIN和END中间
    • 如果满足WHEN中的条件, 则执行对应的THEN后面的语句, 并结束CASE
    • 如果都不满足, 则执行ELSE中的语句
    • 如果省略ELSE且所有WHEN都不满足, 则返回NULL

• IF结构(应用在BEGIN和END中)

  IF 条件 THEN 语句;
  ELSEIF 条件 THEN 语句;
  ...
  ELSE 语句;
  END IF;
  

循环结构

• 分类: WHILE, LOOP, REPEAT

• 循环控制:

  • ITERATE: 类似于continue, 结束本轮循环, 进入下一轮循环
  • LEAVE: 类似于break, 跳出最近一层循环

• WHILE

  [标签(循环的别名): ]WHILE 循环条件 DO
    循环体;
  END WHILE [标签];
  

• LOOP(可用来模拟简单的死循环)

  [标签: ]LOOP
    循环体;
  END LOOP [标签];
  

• REPEAT

  [标签: ]REPEAT
    循环体;
  UNTIL 结束条件
  END REPEAT [标签];
  

• 三种循环都可以省略标签, 但如果添加了循环控制语句(LEAVE, ITERATE)则必须添加标签

案例

• 根据传入的成绩, 显示成绩等级

CREATE PROCEDURE grade_level(IN score INT)
BEGIN
    CASE
    WHEN score >= 90 THEN SELECT 'A';
    WHEN score >= 80 THEN SELECT 'B';
    WHEN score >= 70 THEN SELECT 'C';
    ELSE SELECT 'D';
    END CASE;
END$

CREATE FUNCTION grade_level(score INT) RETURNS CHAR
BEGIN
    IF score >= 90 THEN RETURN 'A';
    ELSEIF score >= 80 THEN RETURN 'B';
    ELSEIF score >= 70 THEN RETURN 'C';
    ELSE RETURN 'D';
    END IF;
END$

• 批量插入, 插入次数决定于参数, 且不超过20

CREATE PROCEDURE p(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    a: WHILE i <= insertCount DO
        INSERT INTO 表 (字段) 
        VALUES (值);
        IF i > 20 
            THEN LEAVE a;
        END IF
        SET i = i + 1;
    END WHILE a;
END$

• 已知表stringcontent中的字段, id 自增长, content VARCHAR(20), 向该表插入指定个数随机字符串

CREATE PROCEDURE p_rand_insert(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;  # 循环变量
    DECLARE str VARCHAR(20) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    DECLARE startIndex INT DEFAULT 1;  # 起始索引
    DECLARE len INT DEFAULT 1;  # 截取长度
    WHILE i <= insertCount DO
        SET startIndex = FLOOR(RAND() * 26 + 1);  # 产生一个1-26的随机索引
        SET len = FLOOR(RAND() * (20 - startIndex + 1) + 1)# 产生随机数 1 - (20 - startIndex)
        INSERT INTO stringcontent (content)
        VALUES (SUBSTR(str, startIndex, len));
        SET i = i + 1;  # 循环变量更新
    END WHILE;
END$