工作——MySQL基础

MySQL数据库基础

包括基本的DML、DDL以及DCL，以及更复杂的子查询，函数，存储过程，视图等。

1. DML 数据操纵语言

概述：对表中的数据进行增删改查操作，重点是查询部分。

1. 给表中增加一条记录

# 格式
# insert into table_name(字段1, 字段2,...) values(字段1的值, 字段2的值,...);

# 首先创建一张表
create table student( # 给学生表增加两个字段
	sid int, # 学生id
    sname varchar(20), # 学生姓名
    sex varchar(10) default '男', # 学生的性别
    age int # 学生的年龄
);

# 增加一个学生，学号为 1，姓名为 Tom，性别为 男，年龄为18
# 方式1，给表中每个字段都赋值
insert into student values(1, 'Tom', '男', 18);
# 方式2，指定字段赋值，前提是未被赋值的字段允许为null或者自增
insert into student(sname, sex, age) values('Jack', '男', 19);

2. 给表中一次性增加多条记录

# 格式
# insert into table_name(字段1, 字段2,...)
# values(字段1的值1, 字段2的值1,...),(字段1的值2, 字段2的值2,...),(字段1的值3, 字段2的值3,...),...

# 同样必须有一张表，如student

# 增加三个学生信息，张三、李四、王五
INSERT INTO student(NAME, sex, age) 
VALUES
('张三', '男', 25),
('李四', '女', 17),
('王五', '男', 39);

3. 删除记录

# 格式
# 根据字段删除记录，如果不指定字段，则全部删除
# delete from table_name 
# 可选择筛选条件，如 where 字段名 = 字段值，举例如下：

# 删除 sid 为 1 的学生信息
delete from student where sid = 1;

4. 修改记录

# 格式
# update table_name set 字段1 = 字段1新值,字段2 = 字段2新值,...
# 筛选条件，如 where 字段1=字段旧值
# 如果不加筛选条件，则默认修改所有记录都为该值！

# 修改 sid 为 4 的学生年龄为 24
update student set age = 24 where sid=4;

5. 简单查询

#格式：
select 字段1,字段2,... from table_name
[筛选条件，如 where,group by,order by, limit等]

1. 最简单的查询，即查询所有

select * from student;

2. 带 where 筛选条件

# 格式
# where 子句可带的关键字

# 赋值 =
select * from student where sid=1;

# 比较运算：> < >= <= !=或<>(不相等) =(相等)
select * from student where id > 2;

# 范围查询：between 字段值1 and 字段值2，【包含边界】
# 查询 sid 在 2~4 的学生信息
select * from student where sid between 2 and 4;

# 范围查询 in(value1, value2,...)
# 查询 sid 在 2~4 的学生信息
select * from student where sid in(2, 3, 4);

# like 模糊查询
# % 表示所有字符匹配，如j%，能匹配Jack，jaack等
# _ 单个字符匹配，如j_ck，不能匹配 jaack，能匹配 jack等
# 查询 sname 以 j 开头的学生信息
select * from student where sname like 'j%'

# 非空判断：is (not) null
select * from student where sname is not null;

# 逻辑判断：and or not
# 查询sid 大于 1，小于 5的学生信息
select * from student where sid > 1 and sid < 5;

3. order by

# 格式
# select 字段1,字段2,... from table_name 
# order by 字段1 [desc],字段2 [desc],...
# desc 逆序；asc 顺序（默认），支持多字段排序

# 查询 sid 在 2~4 的学生信息，并按照 sid 逆序排列
select * from student 
where sid between 2 and 4 
order by sid desc;

4. 分组函数

​ 分组函数作用于一组数据，并对一组数据返回一个值。 分组函数常用的有以下几个：

• AVG()：对数值类型数据取平均值
• COUNT()：计算字段个数。count(*) 计算表中所有记录数；count(字段名) 返回该字段不为空的所有记录数。
• MAX()：最大值
• MIN()：最小值
• SUM()：对数值类型求和

#格式
# select 字段1,..., group function(字段名)
# from table_name
# [筛选条件]

5. 分组数据 group by

​ 可以使用GROUP BY子句将表中的数据分成若干组。

• SELECT的所有字段中，没有在组函数（AVG、COUNT等）中使用的字段，都应该包含在分组数据中；
• 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中；
• 不能在where子句中使用group by；
• 可以在 having 子句中使用 group by；

1. 单列分组

# 分组数据 group by
# select 字段1,... 组函数
# from table_name
# [where]
# group by 字段1,...
# [其他]

# 在员工表中，根据部门id进行分组，并统计分组后的平均薪资
select department_id,AVG(salary)
from employees
group by department_id;

2. 多列分组

# 进行多列分组
# 员工表中，对department_id和job_id进行分组，统计平均薪资
SELECT department_id,job_id, AVG(salary) avg_salary
FROM employees
GROUP BY department_id,job_id;

3. 查询后再进行分组

# 进行多列分组
# 员工表中，对部门id大于50的员工，针对department_id和job_id进行分组，统计平均薪资
SELECT department_id,job_id, AVG(salary) avg_salary
FROM employees
where department_id > 50
GROUP BY department_id,job_id;

6. 过滤分组 having

​ 使用 having 进行过滤分组，使用 having 需要满足以下2个条件：

• 已经使用 group by 进行分组；
• 使用了组函数；

1. having过滤分组语法格式

# having 格式
select 字段1,...,[group function]
from table_name
[where]
group by 分组字段1,...
having 分组过滤条件
[order by 排序字段]

2. having分组举例

# 员工表中，对department_id进行分组，统计平均薪资，只显示平均薪资大于5000
SELECT department_id,job_id, AVG(salary) avg_salary
FROM employees
GROUP BY department_id
HAVING avg_salary >5000;

6. 多表查询

​ 涉及到的两张表分别为 beauty 和 boys 表：

boys 表：

beauty 表：

1. 笛卡尔集

​ 没有正确使用多表查询，就会出现笛卡尔集的情况。

# 查询每个女生的男朋友
# 错误示范
SELECT NAME, boyName FROM beauty, boys;

​ 出现笛卡尔集的原因：省略连接条件；连接条件无效；所有表中的所有行互相连接

2. 连接

​ 为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。

select table1.column1, table2.column1
from table1, table2
where table1.id = table2.did # 用两个表的某个字段进行匹配，就是连接

• 连接时注意重复的列名的区分，一般使用表名.字段名或者对字段起别名的方式进行区分；
• 连接 n 张表至少需要 n-1 个连接条件；

3. MySQL连接方式

​ MySQL数据库的连接关键字为join，所以也可以称为join连接。MySQL连接有以下几种：

• 内连接。
• 左外连接。
• 右外连接。

# 内连接，即连接两张表，找到两张表的"交集"，不为空的所有数据。
select * from table1 inner join table2 on table1.id = table2.id;
# 左外连接，即连接两张表，以左表为主表，找到右表中有的数据进行连接。
select * from table1 left outer join table2 on table1.id = table2.id;
# 右外连接，连接两张表，以右表为主表，找到左表中有的数据进行连接。
select * from table1 right outer join table2 on table1.id = table2.id;

4. 内连接

# 内连接
SELECT * FROM beauty INNER JOIN boys 
ON beauty.boyfriend_id = boys.id;

5. 左外连接和右外连接

# 左外连接和右外连接
# 左外连接
SELECT * FROM beauty LEFT OUTER JOIN boys
ON beauty.boyfriend_id = boys.id

# 右外连接
SELECT * FROM beauty RIGHT OUTER JOIN boys
ON beauty.boyfriend_id = boys.id

7. 子查询

​ 出现在其他语句内部的select语句，称为子查询或内查询。同理，内部嵌套其他select语句的查询，称为外查询或主查询。子查询需要注意以下几点：

• 子查询要包含在括号内；
• 子查询放在比较条件的右侧；

1. 单行子查询

​ 单行子查询只返回一条记录。对于单行子查询使用单行操作符，如>、< 、= 等。

# 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT e.department_id, MIN(salary) min_salary
FROM employees e
GROUP BY e.department_id
HAVING min_salary > (
SELECT MIN(e.salary)
FROM employees e
GROUP BY e.department_id
HAVING e.department_id = 50
);

2. 多行子查询

​ 多行子查询返回多行记录，使用多行比较操作符，如 all、in、any 等。

1. all 所有

​ 如果大于，则必须大于其中的最大值；如果小于，则必须小于其中的最小值。

#返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、 job_id 以及salary
SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE salary > ALL (
	SELECT salary FROM employees
	WHERE job_id='IT_PROG'
)
AND job_id <> 'IT_PROG'

2. any 任一

​ 大于时，只要大于其中最小值；小于时，只要小于其中的最大值即可。

3. in/not in 在/不在

• in：查询出所有符合其中条件的记录；
• not in：查询所有不符合其中条件的所有记录；

# 返回location_id是1400或1700的部门中的所有员工姓名
SELECT first_name FROM employees
WHERE department_id IN (
	SELECT department_id FROM departments
	WHERE location_id IN (1400,1700)
)

8. 分页 limit

​ 所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。

# 格式：limit index,num
# index 从第几条记录开始
# num 每页显示的记录数

# 显示前10条记录
select * from employees
limit 0,10;

# 显示11-20条记录
select * from employees
limit 10,10;

​ 分页的公式：limit (当前页数 - 1) * 每页条数，每页条数

SELECT * FROM table 
LIMIT(PageNo - 1)*PageSize,PageSize;

注意：limit子句必须放在整个查询语句的最后！

2. DDL 数据定义语言

概述：对数据库表结构进行设计，主要有对表的增删改查操作，难点是修改表结构，如约束部分。

1. 创建数据库

create database db_name;

# 使用数据库
use db_name;

# 显示当前所有数据库
show databases;

2. 创建表结构

create table table_name(
	字段名 数据类型 [default 默认值] [comment 备注],
    字段名 数据类型 [default 默认值] [comment 备注],
    ...
)

​ 也可以用子查询来创建表：

create table table_name(字段1,字段2,...)
as 
子查询语句;

• 指定的列和子查询中的列要一一对应
• 通过列名和默认值定义列

3. 修改表结构

​ 使用关键字 alter table。可实现的功能有添加字段，删除字段，修改字段参数（如默认值，数据类型，索引等），重命名字段等。

1. 添加字段

alter table table_name add column column_name datatype [default 默认值];

2. 删除字段

alter table table_name drop column column_name;

3. 修改字段

# 重命名字段
alter table table_name change column column_name to new_name;

# 修改字段类型，默认值
alter table table_name modify column column_name new_datatype [default 新默认值];

4. 删除表结构

drop table table_name;

5. 清空表

truncate table table_name;

• 删除表中所有的数据

• 释放表的存储空间

• 和 delete from table_name 一样都是删除表中所有数据，但是其效率更高

• 使用 truncate 清空表不能回滚

6. 重命名表结构

# 使用rename可以重命名表、视图等结构
alter table table_name rename to new_name;

7. 约束

​ 为了保证数据的一致性和完整性， SQL规范以约束的方式对表数据进行额外的条件限制。约束是表之间的强制规定。

1. 约束的类型

​ 约束有以下6种：

• not null：非空约束，保障列的字段值不为空；
• unique：唯一约束，字段值唯一。允许多个空值。
• primary key：主键约束，非空且唯一
• foreign key：外键约束
• check：检查约束，MySQL中不可用，即使指定也不生效。
• default：默认值

​ 根据约束数据列的限制，约束又可以分为：

• 单列约束：即每个约束只约束一列；
• 多列约束：每个约束约束多列；

​ 根据约束的作用范围，约束可以分为：

• 列级约束：只作用在一个列上，跟在列的定义后面；
• 表级约束：可以定义在多列上，不与列一起，而是单独定义；

2. 创建约束

​ 可以在创建表时为字段指定约束，也可以在创建表后通过alter table 来创建约束。

1. 创建唯一约束

# 创建唯一约束
# 方式1，创建表时，新建约束
create table user (
    id int not null,
    name varchar(20),
    password varchar(16),
    # 使用表级约束语法
    constraint uk_name_pwd unique(name, password)
);
# 方式2
create table user (
	id int unique,
    name varchar(20),
    password varchar(16),
);
# 方式3，创建表后，新建约束
alter table user add constraint uk_name_pwd unique(name, password);

# 方式4，unique 可以使用modify进行指定
alter table user modify name varchar(20) unique;

2. 创建主键约束

​ 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。

# 方式一
create table user (
    id int primary key, # 定义字段时，指定主键
    name varchar(20),
    password varchar(16)
);

# 方式2
create table user (
    id int not null,
    name varchar(20),
    password varchar(16),
    # 使用表级约束语法
    constraint pk_name_pwd primary key(id)
);

# 方式3
create table user (
    id int not null,
    name varchar(20),
    password varchar(16)
);
alter table user add primary key(id);

# 方式4，通过modify修改
alter table user modify id int primary key;

​ 如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

create table user (
    id int not null,
    name varchar(20),
    password varchar(16),
    # 使用表级约束语法
    constraint pk_name_pwd primary key(id)
);

3. 创建外键约束

​ 外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

​ 从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。

注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列。

# 主表
create table dept(
	did int primary key,
    dname varchar(20)
);
# 从表，外键表
create table emp(
	eid int primary key,
    ename varchar(20),
    did int,
    # 表级约束
    constraint emp_dept_id foreign key(did) references dep(did)
);

create table emp(
	eid int primary key,
    ename varchar(20),
    did int
);
alter table emp add constraint emp_dept_id foreign key(did) references dept(did);

​ 创建多列外键组合，必须使用表级约束:

# 主表
create table classes(
	cid int,
    cname varchar(20),
    number int,
 	primary key(cname, number)
);
# 从表，外键表
create table student(
	id int primary key,
    cname varchar(20),
    cnumber int,
    # 表级约束
    constraint stu_cls foreign key(cname,cnumber) references classes(cname, number)
);

​ 外键约束的关键字：

• **FOREIGN KEY：**在表级指定子表中的列
• **REFERENCES：**标示在父表中的列
• **ON DELETE CASCADE(级联删除)：**当父表中的列被删除
  时，子表中相对应的列也被删除
• **ON DELETE SET NULL(级联置空)：**子表中相应的列置空

3. DCL 数据控制语言

概述：定义用户的访问权限和安全级别。很重要！

4. 常见函数

1. 字符串相关函数

1. 大小写控制函数

• lower(str)：对字符串str进行小写转换；
• upper(str)：对字符串进行大写转换；

2. 字符控制函数

• concat(str1,str2)：连接两个字符串；
• substr(str, begin, end)：截取字符串，begin是起始索引，end为终止索引；
• length(str)：求字符串长度；
• instr(str, index)：取得字符串中指定索引的某个字符；
• lpad(str, num, str1)：从左填充 str1 到 str 中，直到总字符数为num；
• rpad(str,num,str1)：从右填充 str1 到 str 中，直到总字符数为num；
• trim(str1 from str)：截取以 str1 开始返回的 str 字符串；
• replace(str1, str2, str3)：用str3替换 str1 中的 str2；

2. 数字函数

• round()：四舍五入
• truncate(number,cnt)：取几位小数
• mod(num, mod)：取余

3. 日期函数

• now()：获取当前时间
• str_to_date(‘日期字符串’,‘指定的格式’)：转换日期字符为指定格式的日期。
• date_format(‘日期’,‘指定格式’)：转换日期的格式为指定的格式。

5. 事务

​ 事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

1. 事务的四种特性（ACID）

1. 原子性（Atomicity）

​ 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）

​ 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性（Isolation）

​ 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性（Durability）

​ 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

2. 事务的使用

• 事务的开始：以第一个 DML 语句的执行作为开始
• 以下面的其中之一作为结束:
  • COMMIT 或 ROLLBACK 语句
  • DDL 或 DCL 语句（自动提交）
  • 用户会话正常结束
  • 系统异常终了

3. 事务的隔离级别

1. 并发问题

• 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还未提交的字段.之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
• 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.之后, T1再次读取同一个字段, 值就不同了。
• 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行。

2. 事务的隔离性和隔离级别

​ 数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响, 避免各种并发问题。
​ 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

3. 数据库提供的隔离级别

隔离级别	描述
READ UNCOMMITTED(读未提交数据)	允许事务读取未被其他事物提交的变更.脏读,不可重复读和幻读的问题都会出现
READ COMMITED(读已提交数据)	只允许事务读取已经被其它事务提交的变更.可以避免脏读,但不可重复读和幻读问题仍然可能出现
REPEATABLEREAD(可重复读)	确保事务可以多次从-一个字段中读取相同的值.在这个事务持续期间,禁止其他事物对这个字段进行更新.可以避免脏读和不可重复读,但幻读的问题仍然存在.
SERIALIZABLE(串行化)	确保事务可以从一个表中读取相同的行.在这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作.所有并发问题都可以避免,但性能十分低下.

• Oracle 支持的 2 种事务隔离级别： READ COMMITED,SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED

• Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ

4. 在MySQL中设置隔离级别

​ 每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。

• 查看当前的隔离级别: SELECT @@tx_isolation;
• 设置当前 mySQL 连接的隔离级别:
  • set transaction isolation level read committed;
• 设置数据库系统的全局的隔离级别:
  • set global transaction isolation level read committed;

6. 视图

​ MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的， 只保存了sql逻辑，不保存查询结果。

​ 使用视图的好处：

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

1. 创建视图

create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]

2. 修改视图

alter view view_name
As select_statement
[with|cascaded|local|check option]

​ 视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的：

• 包含以下关键字的sql语句：分组函数、 distinct、 group by、 having、 union 或者 union all
• 常量视图
• select 中包含子查询
• join
• from 一个不能更新的视图
• where 子句的子查询引用了 from 子句中的表

3. 删除视图

​ 删除视图必须拥有删除权限。

drop view view_name1,view_name2,... [restrict|cascade];

4. 查看视图定义

show create view view_name;

7. 存储过程和函数

​ 事先经过编译并存储在数据库中的一段sql语句的集合。使用好处：

• 简化应用开发人员的很多工作
• 减少数据在数据库和应用服务器之间的传输
• 提高了数据处理的效率

1. 创建存储过程或函数

# 1. 创建存储过程
create procedure 存储过程名 ([proc_parameter[,…]])
[characteristic…]routine_body

# 2. 创建函数
create function 函数名([func_parameter[,…]])
returns type
[characteristic…]routine_body

• proc_parameter：[in|out|inout] param_name type
• Func_paramter：param_name type
• Type：任何有效的mysql数据类型
• Characteristic：
  language sql(默认，且推荐)
  |[not] deterministic
  |{contains sql|no sql|reads sql data|modifies sql data}
  |sql security{definer|invoker}
  |comment ‘string’
• Rountine_body：有效的sql 过程语句

2. 调用存储过程和函数

# 1. 调用存储过程
call procedure_name(参数列表);

# 2. 调用函数
select function_name(参数列表);

3. 修改存储过程或函数

# 1. 修改存储过程：
alter procedure 存储过程名 [charactristic…];

# 2. 修改函数
alter function 函数名 [charactristic…]

• characteristic:
  {contains sql|no sql|reads sql data|modifies sql data}
  |sql security{definer|invoker}
  |comment ‘string’

4. 删除存储过程和函数

注：一次只能删除一个存储过程或函数。

# 1. 删除存储过程：
drop procedure [if exists] 存储过程名
# 2. 删除函数：
drop function [if exists] 函数名

5. 查看存储过程和函数

# 1.查看存储过程或函数的状态：
show {procedure|function} status like 存储过程或函数名
# 2.查看存储过程或函数的定义：
show create {procedure|function} 存储过程或函数名

|[not] deterministic
|{contains sql|no sql|reads sql data|modifies sql data}
|sql security{definer|invoker}
|comment ‘string’

• Rountine_body：有效的sql 过程语句

2. 调用存储过程和函数

# 1. 调用存储过程
call procedure_name(参数列表);

# 2. 调用函数
select function_name(参数列表);

3. 修改存储过程或函数

# 1. 修改存储过程：
alter procedure 存储过程名 [charactristic…];

# 2. 修改函数
alter function 函数名 [charactristic…]

• characteristic:
  {contains sql|no sql|reads sql data|modifies sql data}
  |sql security{definer|invoker}
  |comment ‘string’

4. 删除存储过程和函数

注：一次只能删除一个存储过程或函数。

# 1. 删除存储过程：
drop procedure [if exists] 存储过程名
# 2. 删除函数：
drop function [if exists] 函数名

5. 查看存储过程和函数

# 1.查看存储过程或函数的状态：
show {procedure|function} status like 存储过程或函数名
# 2.查看存储过程或函数的定义：
show create {procedure|function} 存储过程或函数名