MySQL数据库（一）

MySQL数据库（一）
layout: post
title: MySQL数据库（一）SQL语法、字段类型、函数、约束、多表查询与事务
description: MySQL数据库（一）SQL语法、字段类型、函数、约束、多表查询与事务
tag: 数据库
MySQL数据库（一）SQL语法、字段类型、函数、约束、多表查询与事务
MySQL数据库
- 数据库连接
1. 通过MySQL提供的命令行客户端
2. 通过widows命令行
  中括号内选填
mysql [-h 127.0.0.1] [-P 3306] -u root -p
- MySQL启动命令
```
net start mysql80
net stop mysql80
1
2
```
- MySQL的数据模型
SQL

通用语法
1. SQL语句可以单行或者多行书写，可随意换行，语句以分号‘;’结尾
2. SQL语句可以使用空格/缩进增强可读性，空格多少不受限制
3. MySQL的SQL语句不区分大小写，但建议关键字大写，以增强可读性
4. 注释形式：
  - 单行注释 --注释内容 或者 #注释内容（MySQL特有）
  - 多行注释
    /* */
SQL分类（DDL、DML、DQL、DCL）
- DDL(数据定义（definition）语言)
  操作数据库、表、字段
- DML（数据操作（manipulation）语言）
  主要是增删改等操作
- DQL（数据查询（query）语言）
  查询
- DCL（数据控制（control）语言）
  控制创建用户及其访问权限
数据类型

 数据库的字段类型

unsigned:
- 无符号整数
- 若使用，该数据不能定义为负
DDL
- 数据库操作
  下边[]为选填参数
```
--query all
SHOW DATABASES;
--query current database
SELECT DATABASE();
--create
CREATE DATABASE[IF NOT EXISTS] 数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
--delete
DROP DATABASE[IF EXISTS]数据库名;
--use
USE 数据库名;
1
2
3
4
5
6
7
8
9
10
```
- 数据库表操作
  字段名与字段类型空格分开，字段之间逗号分开
```
--qurey all
SHOW TABLES;
--create table
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
……
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释]
--query table DESC (description的缩写)
DESC 表名
--query the sql while create table
SHOW CREATE TABLE 表名
1
2
3
4
5
6
7
8
9
10
11
12
13
```
- 建表
```
CREATE TABLE employee_info(
	id INT[纯数字编码],
	work_id VARCHAR(10) COMMENT '工号',
	name VARCHAR(10) COMMENT '姓名',
	sex CHAR(1) COMMENT '性别',
	age INT(3) COMMENT '年龄',
	 identity_id CHAR(18) COMMENT '身份证号',
	regist_time DATA COMMENT '入职时间'
) COMMENT '员工信息表';
1
2
3
4
5
6
7
8
9
```
- 添加字段
  ALTER TABLE 表名 ADD 字段名类型(长度)[COMMENT 字段说明]
```
ALTER TABLE employee_info ADD nickname VARCHAR(20) COMMENT '昵称';
1
```
- 修改数据类型
```
ALTER employee_info CHANGE nickname username VARCHAR(30) COMMENT '用户名';
1
```
- 删除字段
  alter T drop
```
ALTER TABLE employee_info DROP username;
1
```
- 修改表名
```
ALTER TABLE employee_info RENAME TO employee;
1
```
- 删除表
  
  TRUNCATE删除表并重建，可以起到清空表数据的作用
DML

操纵manipulation包含增删改三种操作
增加：INSERT
单条：
insert T () value()
insert T value()
批量：
insert T () , () , () value (), () ()
inset T value () , () , ()
不指定字段默认给全部字符添加数据，此时的value顺序要保持与字段顺序对应一致

删除：DELETE
delete from T where
delete不可删除某个字段的值，若要删除某个字段可以用update设置为NONE

更改：UPDATE
update T = where

DQL

基本查询

下边用ZD代表字段，NK代表别名，T代表表名
1-查询多个字段
SELECT ZD1, ZD2, ZD3 FROM T
用通配符*查询所有
SELECT * FORM T
2-设置别名
SELECT ZD1 AS NK1, ZD2 AS NK2 ZD3 AS NK3 FROM T
3-去重
SELECT DISTINCT ZD1

条件查询

1、查询
select * from t where age = 80;
2、is null 取空
select * from t where idcard is null;
3、not null 取非空
select * from t where idcard is not null;
4、<> 与 != 都可以是不等于的意思
select * from t where age != 20;
select * from t where age <> 20;
5、多个条件逻辑连接 &&
select *from t where age >=15 && age <= 20;
6、在某个范围内，也可以用 between min and max
注：必须前边是min最小值后边是max最大值，是闭合区间
select * from t where age between 15 and 20;
7 and关键字
select * from t where gender = '女' and age < 25;
8、in关键字
select * from t where age = 18 or age = 20 or age = 40;
多个值可以用 in关键字，例如上边的18或20或40可以改为：
select * from t where age in (18,20,40);
9、模糊匹配 '_‘匹配单个字符，’%'匹配任意个字符
查询名字是两个字的
select * from t where name like '_ _'
查询身份证最后一位是X的员工
select * from t where idcard like '%X'

聚合函数

注：所有的null值不参与聚合函数的运算

1、统计员工数量
select count(*) from t
select count(id) from t
2、统计平均年龄
select avg(age) from t
3、统计最大年龄
select max(age) from t
4、统计最小年龄
select min(age) from t
5、统计西安地区员工年龄之和
select sum(age) from t where address = '西安'

分组查询

注：where与having的区别
- * 执行时机不同：where 是分组之前进行过滤，不满足where条件不参与分组；而having是分组之后对结果的进行过滤.
- * 执行条件不同，where不能对聚合函数进行判断，而having可以
1、根据性别分组，统计男性员工和女性员工的数量
select gender, count(*) from t group by sex;
2、根据性别分组，统计男性员工和女性员工的平均年龄
select gender, avg(age) from t group by sex;
3、查询年龄小于45的员工，并根据工作地址分组，获取员工数大于等于3的工作地址

select address, count(*) from t where age < 45 group by address having count(*) >= 3
可以为count(*)起别名
select address, count(*) as address_num from t where age < 45 group by address having address_num >= 3

排序查询
- ASC：升序（ascend）（默认是升序）
- DESE：降序（descend
select * from t order by zd1 asc , zd2 desc;

注：多个字段排序时，当第一个字段值相同时才会根据第二个字段进行排序。

1、根据年龄对公司的员工进行升序排序。
select * from t order by age asc;

由于默认是asc所以也可以省略

select * from t order by age;

2、根据入职时间，对员工进行降序排序。
select * from t order by regist_time desc;

3、根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序。
select * from t order by age asc, regist_time desc;

分页查询

语法：selet * from t limit 起始索引, 每页的记录数
注：
- 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页记录数
- 分页查询是数据库的方言，不同数据库有不同实现，MySQL中是LIMIT
- 如果查询的是第一页数据，起始索引可以省略
1、查询第一页员工数据，每页展示10条记录
select * from t limit 0 ,10;
查询第一页时，起始索引可以省略
select * from t limit 10;

2、查询第二页员工数据，每页展示10条记录
select * from t limit 10, 10;

DQL案例练习

1．查询年龄为20,21,22,23岁的员工信息。

select * from t where age in (20,21,22,23);
select * from t where age between 20 and 23;
select * from t where age >= 20 and <= 23;

2．查询性别为男，并且年龄在20-40岁(含)以内的姓名为三个字的员工。

select * from t where gender= '男' and age (between 20 and 40) and name like '_ _ _';

3．统计员工表中,年龄小于60岁的，男性员工和女性员工的人数。

select gender, count(*) from t where age < 60 group by gender;

4．查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。

select name, age from t where age <= 35 order by age asc, regist_time desc;

5．查询性别为男，且年龄在20-40岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

select * from t where sex = '男' and age between (20 and 40) order by age asc, regist_time desc limit 5;

注：limit 在最后；

DQL执行顺序

DQL在执行过程中：
1、先根据from确定是哪张表
2、根据where条件筛选字段
3、根据group将字段分组，根据having条件将分组后字段再过滤
4、选中字段并返回
5、字段排序
6、字段分页

 DCL

用户管理

MySQL数据库有一张默认生成的user表，记录了该数据库中的用户信息

1、查看用户表
use mysql;

select * from user;

2、创建 lewis用户，主机为本机，密码为123456
create user 'lewis' '@' 'localhost' identified by '123456';

3、修改用户lewis密码为‘admin’
alter user 'lewis' '@' 'localhost identified with mysql_native_password by 'admin'

4、删除用户
drop user 'lewis' '@' 'localhost';

注：可以看出用户名加主机地址共同构成一个用户账号，每次操作都是需要'lewis' '@' 'localhost'一起进行。

权限控制

 函数

函数是一段可以直接别另一段程序调用的代码，用于数据格式转换等。

字符串函数

 数值函数

 日期函数

 流程函数

注：''代表空字符串，不是null。

1、查询表t中员工姓名和工作地址，如果工作地址是北京/上海，返回一线城市，否则返回二线城市。
select name, (case address when '上海' then '一线城市' when '北京' then '一线城市' else '二线城市' end) as '工作地址' form t ;
2、统计班级各个学员的成绩，展示的规则如下:>=85，展示优秀； >=60，展示及格；否则，展示不及格

select name, (case grade when >= 85 then '优秀' when >= 60 then '及格' else '不及格' end) as '成绩' from table;

约束

 常见约束案例
```
create table user(
id int primary key auto_increment comment '主键',
name vachar(10) not null unique comment '姓名',
age in check(age > 0 && age <= 120) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
)comment '用户表';
1
2
3
4
5
6
7
```
外键约束
```
-- 添加外键
alter table employee add constraint fk_emp_dect_id foreign key (dept_id) references dept(id)

-- 删除外键
alter table employee drop foreign key fk_emp_dect_id;
1
2
3
4
5
```
外键的删除/更新功能

创建外键同时可以添加外键的删除与更新功能，即当外键关系的字段被删除和更新时会发生的行为。

使用cascade(级联)主表和子表，当删除或者主键同时为另外一个表的外键时，另一个表对应也改变
例如下边这条语句构成的外键，dept表中的字段id改变后，employee表中字段dept_id会同步改变或者删除。
```
-- 创建外键并添加外键的删除/更新行为
alter table employee add constraint fk_emp_dect_id foreign key (dept_id) references dept(id) on update cascade on delete cascade
1
2
```
顾名思义set null是指主表中主键字段更新或删除时将子表中外键字段置空
```
alter table employee add constraint fk_emp_dect_id foreign key (dept_id) references dept(id) on update set null on delete set null
1
```
多表查询

 多表关系
- 多对一
  多个员工同属一个部门，一个员工只能对应一个部门。
  为实现连表关系：
  可以将部门表id主键设置为员工表的dept_id字段的外键。即
  在多的一方建立外键，references 另一方的主键
- 多对多
  学生与课程的关系，一个学生选修多门课程，而同时一个课程包含多个学生的选择。
  为实现连表关系：
  可以建立第三张中间表，中间表至少包含两个外键，分别关系两方的主键。
  构建第三章中间表即：双方的关系表，包含两个外键对应双方表的主键
- 一对一
  用于单表拆分
  在拆分后的两个表中，任选一方加入外键关联另一方的主键。
  但这样做有点类似是多对一的做法，因此这里的外键要设置约束（unique），确保唯一性，这就就保证是一对一的关系。
多表查询概述

笛卡尔积：集合A和集合B所有的组合（集合A的大小×集合B的大小）情况。（在多表查询时，需要消除无效的笛卡尔积）
```
select * from employee, dept where employee.dept_id = dept.id;
1
```
多表查询的分类

内连接、外连接、自连接

连接查询
1. 内连接：相当于查询A、B交集部分的数据。
语法：
（1）隐式内连接 (2) 显示内连接
```
-- 隐式内连接
select employee.name, dept.name from employee, dept where employee.dept_id = dept.id;
-- 有时候表名比较长可以在select表时，给表起别名
select e.name, d.name from employee as e, dept as d where e.dept_id = d.id;
-- 显示内连接写法，加on关键字
select e.name, d.name from employee as e inner join dept as d on e.dept_id = d.id;
1
2
3
4
5
6
```
1. 外连接：
  （1）左外连接：查询左表所有的数据，以及两张表交集部分数据。
  （2）右外连接：查询右表所有的数据，以及两张表交集部分的数据（显然很多右外连接都可以改为左外连接）
```
--查询employee表所有数据以及对应部门的信息（左外连接），关键字 left outer join，其中outer可省略
select e.*, d.naem from employee as e left outer join dept as d on dept_id = d.id;

-- 查询dept表所有数据和对应的员工的信息（右外连接）
select d.*, e.* from employee as e right join dept as d on dept_id = d.id;
1
2
3
4
5
```
1. 自连接：当前表与自身的连接查询，自连接必须使用表别名。
  例如下边的表结构：
```
-- 查询员工和它领导的姓名
select a.name, b.name from employee as a, employee as b where a.manager_id = b.id;
-- 查询所有员工的emp
1
2
3
```
自连接也可以使用外连接
```
-- 查询所有员工 employee表及其领导名字，如果没有领导也需要查询出来
select a.name, b.name from employee as a left join employee as b on a.manager_id = b.id;
1
2
```
联合查询

使用union , union all 关键字将多次查询结果合并。
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
union all是直接将多次查询结果合并，其中可能有某个记录满足多次查询条件，从而重复出现。
union则会将重复出现的记录剔除。
```
select * from employee where salary < 5000
union all
select * from employee where age > 50;

1
2
3
4
```
子查询
```
SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
1
```
标量子查询

子查询返回结果为单个数值（数字、字符串、日期等）
```
--查询销售部的所有员工信息
--步骤1：查询销售部部门id
--步骤2：根据销售部部门id查询员工信息
select id from dept where name = '销售部';
select * from employee where dept_id = (select id from dept where name = '销售部');

--查询在“方东白”入职之后的员工信息
select * from emp where entrydata > (select entrydata from employee where name = '方东白');
1
2
3
4
5
6
7
8
```
列子查询

子查询返回的结果是一列（可以是多行，即子查询结果有多条数据），这种子查询称为列子查询
常见配合操作符：in（在所选列中）, notin（不在）, any（有1即可）, some（有一即可）, all（全部满足）
```
--1、查询“销售部”和“市场部”的所有员工信息
select * from employee where dept_id in (select id from dept where name = '销售部' or name = '市场部');

--2、查询比财务部所有人工资都高的员工的信息
--select id from dept where name = '财务部';
-- select salary from where dept_id = (select id from dept where name = '财务部');
select * from emp where salay > all(select salary from where dept_id = (select id from dept where name = '财务部') );

--3、查询比研发部任意一人工资高的员工信息
--
select * from emp where salary > any(select salary from where dept_id = (select id from dept where name = '研发部') );
1
2
3
4
5
6
7
8
9
10
11
```
行子查询

子查询返回结果是一行（可以是多列，即子查询返回结果有多个字段数据），称为行子查询。常见的操作符为=、<>、IN、NOT IN
```
--1、查询与“张无忌”的薪资及直属领导相同的员工信息
--查询张无忌的薪资与直属领导
--select salary, managerid from emp where name = '张无忌';
select * where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');
1
2
3
4
```
采用括号()，填入字段，与条件结果对应

 表子查询

子查询返回结果是一张表，即查询出一张临时表作为条件
```
--1、查询与鹿杖客和宋远桥职位和薪资相同的员工信息
--a、查询鹿杖客和宋远桥的职位和薪资
--select salary, level from emp where name = '鹿杖客' or name = '宋远桥';
select * from emp where (job, salary) in (select salary, level from emp where name = '鹿杖客' or name = '宋远桥');

--2、查询入职日期是“2006-01-01”之后的员工信息及其部门信息。
select * from emp where entrydate > '2006-01-01';
select * from (select * from emp where entrydate > '2006-01-01') as e left join dept as d on e.dept_id = d.dept_id;
1
2
3
4
5
6
7
8
```
1、查询与鹿杖客和宋远桥职位和薪资相同的员工信息，满足与某一个人的条件相同即可，所以用in
2、查询入职日期是“2006-01-01”之后的员工信息及其部门信息。
根据入职日期查询到满足条件的员工信息表，因为还需要部门信息，因此左外连接上部门表。

多表查询案例

准备三张表：emp（员工信息）、dept（部门）、salgrade（薪酬等级：给定字段：等级，该等级最低薪资，该等级最高薪资）
```
--1、查询员工的姓名、年龄、职位、部门信息
--隐式内连接写法
select e.name, e.age, e.job, d.name from emp as e, dept as d where e.dept_id = d.id;
--显式内连接写法
select e.name, e.age, e.job, d.name from emp as e inner join dept as d on e.dept_id = d.id;

--2、查询年龄小于30岁的员工的姓名、年龄、职位、部门信息
select e.name, e.age, e.job, d.name from emp as e, dept as d where e.dept_id = d.id and e.age < 30;
-- 3、查询拥有员工的部门id，部门名称
select distinct d.id, d.name from emp as e, dept as d where e.dept_id = d.id;

--4、查询年龄大于40岁的员工，及其归属部门名，如果员工没有分配部门也需要展示出来。
select e.*, d.name from emp as e left join dept as d on e.dept_id = d.id where e.age > 40;

--5、查询所有员工的工资等级
select e.* , s.grade from emp as e, salgrade as s where e.salary between s.lowsal and s.highsal;

--6、查询“研发部所有员工的信息及工资等级”
--select id from dept where name = '研发部';
--select * from emp where id = (select id from dept where name = '研发部');
--select e.* , s.grade from (select * from emp where id = (select id from dept where name = '研发部')) as e, salgrade as s where e.salary between s.lowsal and s.highsal;
select e.*, s.grade from emp as e, dept as d, salgrade as s where e.dept_id = d.id and (e.salary between s.lowsal and s.highsal);

--7、查询研发部员工的平均工资
select ave(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

--8、查询工资比灭绝高的员工信息
select * from emp where salary > (select salary from emp where name = '灭绝');

--9、查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp);

--10、查询低于本部门平均工资的员工信息
--a、查询指定部门平均薪资
select avg(e1.salary) from emp e1 where e1.dept_id = 1;

select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

--11、查询所有的部门信息，并统计部门的员工人数
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;

--12、查询所有学生的选课情况，展示出学生名称，学号，课程名称
-- 涉及学生表，课程表，学生选课表
select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.student_id and sc.course_id = c.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
```
事务

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

事务操作

准备一张简单的账号表，包含id，姓名，账户，余额四个字段
假设张三向李四转账1000，那么这个操作会使得张三的余额-1000，而李四的余额+1000，转账操作必须构建成一个事务，即-1000和+1000的操作要么都失败，要么都成功，否则就会产生异常！

事务操作方式一，修改默认自动提交sql变为手动，执行没有异常再提交sql，出现异常并不会真正影响数据表中的数据。
```
set @@autocommit = 0;
--1、查询张三账号余额
select * from account where name = '张三';

--2、张三账号余额-1000
update account set money = money - 1000 where name = '张三';

--3、李四账号余额+1000
update account set money = money + 1000 where name = '李四';

--如果正确执行，提交事务
commit;

--如果出错，回滚指令
rollback;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
```
事务操作方式二，开启一个事务.
```
set @@autocommit = 1;

start transaction;
--1、查询张三账号余额
select * from account where name = '张三';

--2、张三账号余额-1000
update account set money = money - 1000 where name = '张三';

--3、李四账号余额+1000
update account set money = money + 1000 where name = '李四';

--如果正确执行，提交事务
commit;

--如果出错，回滚指令
rollback;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
```
事务的四大特性
- 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性：事务完成时，必须使所有数据保持一致。
- 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
并发事务问题
- 脏读：一个事务读取到另外一个事务还没有提交的数据。
- 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称为不可重复读。
- 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了，好像出现了“幻影”。
事务隔离级别

数据隔离级别越高，并发性能越差，MySQL的默认数据隔离级别为Repeatable Read.而Oracle的默认数据隔离级别为Read committed。
顾名思义，read uncommited 脏读问题都解决不了
read commited 可以解决脏读问题
repeatable read 可以解决不可重复读问题
serialize 可以解决幻读问题，即所有并发事务问题都可以解决了。

查看事务隔离级别：
```
select @@transaction_isolation;
1
```
设置事务隔离级别：
```
set session transaction isolation level read uncommitted;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
1
2
3
```
相关阅读:
Vuex
Git 和 Github 的使用
 centos7 离线安装httpd
Linux中常见的命令(java后端版)
新学期，我的FLAG不能倒~
【Linux-ELK】安装部署文档
 Educational Codeforces Round 130 (Rated for Div. 2) A--C
FANUC机器人socket通讯硬件配置
 jira查询user详细信息
 C—数据的储存（下）
原文地址：https://blog.csdn.net/baiduwaimai/article/details/126573295

MySQL数据库（一）SQL语法、字段类型、函数、约束、多表查询与事务

MySQL数据库

SQL

通用语法

SQL分类（DDL、DML、DQL、DCL）

数据类型

数据库的字段类型

DDL

DML

DQL

基本查询

条件查询

聚合函数

分组查询

排序查询

分页查询

DQL案例练习

DQL执行顺序

DCL

用户管理

权限控制

函数

字符串函数

数值函数

日期函数

流程函数

约束

常见约束案例

外键约束

外键的删除/更新功能

多表查询

多表关系

多表查询概述

内连接、外连接、自连接

联合查询

子查询

标量子查询

列子查询

行子查询

表子查询

多表查询案例

事务

事务操作

事务的四大特性

并发事务问题

事务隔离级别