layout: post
title: MySQL数据库(一)SQL语法、字段类型、函数、约束、多表查询与事务
description: MySQL数据库(一)SQL语法、字段类型、函数、约束、多表查询与事务
tag: 数据库
MySQL数据库(一)SQL语法、字段类型、函数、约束、多表查询与事务
MySQL数据库
- 数据库连接
- 通过MySQL提供的命令行客户端
- 通过widows命令行
中括号内选填
mysql [-h 127.0.0.1] [-P 3306] -u root -p
- MySQL启动命令
net start mysql80
net stop mysql80
- MySQL的 数据模型
SQL
通用语法
- SQL语句可以单行或者多行书写,可随意换行,语句以分号‘;’结尾
- SQL语句可以使用空格/缩进增强可读性,空格多少不受限制
- MySQL的SQL语句不区分大小写,但建议关键字大写,以增强可读性
- 注释形式:
- 单行注释
--注释内容
或者#注释内容(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 数据库名;
- 数据库表操作
字段名与字段类型空格分开,字段之间逗号分开
--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 表名
- 建表
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 '员工信息表';
- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 字段说明]
ALTER TABLE employee_info ADD nickname VARCHAR(20) COMMENT '昵称';
- 修改数据类型
ALTER employee_info CHANGE nickname username VARCHAR(30) COMMENT '用户名';
- 删除字段
alter T drop
ALTER TABLE employee_info DROP username;
- 修改表名
ALTER TABLE employee_info RENAME TO employee;
- 删除表
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 '用户表';
外键约束
-- 添加外键
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;
外键的删除/更新功能
创建外键同时可以添加外键的删除与更新功能,即当外键关系的字段被删除和更新时会发生的行为。
使用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
顾名思义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
多表查询
多表关系
- 多对一
多个员工同属一个部门,一个员工只能对应一个部门。
为实现连表关系:
可以将部门表id主键设置为员工表的dept_id字段的外键。即
在多的一方建立外键,references 另一方的主键
- 多对多
学生与课程的关系,一个学生选修多门课程,而同时一个课程包含多个学生的选择。
为实现连表关系:
可以建立第三张中间表,中间表至少包含两个外键,分别关系两方的主键 。
构建第三章中间表即:双方的关系表,包含两个外键对应双方表的主键
- 一对一
用于单表拆分
在拆分后的两个表中,任选一方加入外键关联另一方的主键。
但这样做有点类似是多对一的做法,因此这里的外键要设置约束(unique)
,确保唯一性,这就就保证是一对一的关系。
多表查询概述
笛卡尔积
:集合A和集合B所有的组合(集合A的大小×集合B的大小)情况。(在多表查询时,需要消除 无效的笛卡尔积
)
select * from employee, dept where employee.dept_id = dept.id;
多表查询的分类
内连接、外连接、自连接
连接查询
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)右外连接:查询右表所有的数据,以及两张表交集部分的数据(显然很多右外连接都可以改为左外连接
)
--查询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;
- 自连接:当前表与自身的连接查询,自连接必须使用表别名。
例如下边的表结构:
-- 查询员工和它领导的姓名
select a.name, b.name from employee as a, employee as b where a.manager_id = b.id;
-- 查询所有员工的emp
自连接也可以使用外连接
-- 查询所有员工 employee表及其领导名字,如果没有领导也需要查询出来
select a.name, b.name from employee as a left join employee as b on a.manager_id = b.id;
联合查询
使用union , union all
关键字将多次查询结果合并。
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
union all
是直接将多次查询结果合并,其中可能有某个记录满足多次查询条件,从而重复出现。
union
则会将重复出现的记录剔除。
select * from employee where salary < 5000
union all
select * from employee where age > 50;
子查询
SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
标量子查询
子查询返回结果为单个数值(数字、字符串、日期等)
--查询销售部的所有员工信息
--步骤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 = '方东白');
列子查询
子查询返回的结果是一列(可以是多行,即子查询结果有多条数据),这种子查询称为列子查询
常见配合操作符: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 = '研发部') );
行子查询
子查询返回结果是一行(可以是多列,即子查询返回结果有多个字段数据),称为行子查询。常见的操作符为=、<>、IN、NOT IN
--1、查询与“张无忌”的薪资及直属领导相同的员工信息
--查询张无忌的薪资与直属领导
--select salary, managerid from emp where name = '张无忌';
select * where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');
采用括号(),填入字段,与条件结果对应
表子查询
子查询返回结果是一张表,即查询出一张临时表作为条件
--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、查询与鹿杖客和宋远桥职位和薪资相同的员工信息,满足与某一个人的条件相同即可,所以用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;
事务
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
事务操作
准备一张简单的账号表,包含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;
事务操作方式二,开启一个事务.
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;
事务的四大特性
- 原子性:事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性:事务完成时,必须使所有数据保持一致。
- 隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性:事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
并发事务问题
- 脏读:一个事务读取到另外一个事务还没有提交的数据。
- 不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称为不可重复读。
- 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在了,好像出现了“幻影”。
事务隔离级别
数据隔离级别越高,并发性能越差,MySQL的默认数据隔离级别为Repeatable Read.而Oracle的默认数据隔离级别为Read committed。
顾名思义,read uncommited 脏读问题都解决不了
read commited 可以解决脏读问题
repeatable read 可以解决不可重复读问题
serialize 可以解决幻读问题,即所有并发事务问题都可以解决了。
查看事务隔离级别:
select @@transaction_isolation;
设置事务隔离级别:
set session transaction isolation level read uncommitted;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;