1.SQL
1.1 SQL 通用语法和分类
1.1.1 SQL 通用语法
1.1.2 SQL语句分类
1.2 SQL常用操作 DDL
1.2.1数据库操作
1.2.2表操作创建&查询
举例:建立当下数据库
解:
查看该表结构
1.2.3数据类型及案例
char比 varchar 性能更高
案例:
执行:
1.2.4 表操作修改&删除
解:
解:
解:
解:
1.3数据库常用操作 DML
1.3.1 插入
注意
:
1.3.2更新和删除
1.4 数据查询—DQL
1.4.1 DQL 基础查询
尽量不要使用select * from 表名;,哪怕使用所有字段也不要用,影响效率
1.4.2 DQL 条件查询
条件
between…and…必须从小到大写
举例:
-- 年龄等于30
select * from employee where age = 30;
-- 年龄小于30
select * from employee where age < 30;
-- 小于等于
select * from employee where age <= 30;
-- 没有身份证
select * from employee where idcard is null or idcard = '';
-- 有身份证
select * from employee where idcard;
select * from employee where idcard is not null;
-- 不等于
select * from employee where age != 30;
-- 年龄在20到30之间
select * from employee where age between 20 and 30;
select * from employee where age >= 20 and age <= 30;
-- 下面语句不报错,但查不到任何信息
select * from employee where age between 30 and 20;
-- 性别为女且年龄小于30
select * from employee where age < 30 and gender = '女';
-- 年龄等于25或30或35
select * from employee where age = 25 or age = 30 or age = 35;
select * from employee where age in (25, 30, 35);
-- 姓名为两个字
select * from employee where name like '__';
-- 身份证最后为X
select * from employee where idcard like '%X';
1.4.3 聚合函数
举例:
-- 统计地址为广东省的员工
select count(id) from employee where workaddress = "广东省";
-- 统计员工的最大年龄
select max(age) from emp
1.4.4 分组查询
-- 根据性别分组,统计男性和女性数量(只显示分组数量,
-- 不显示哪个是男哪个是女)
select count(*) from employee group by gender;
-- 根据性别分组,统计男性和女性数量
select gender, count(*) from employee group by gender;
-- 根据性别分组,统计男性和女性的平均年龄
select gender, avg(age) from employee group by gender;
-- 年龄小于45,并根据工作地址分组
select workaddress, count(*) from employee where
age < 45 group by workaddress;
-- 年龄小于45,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from employee where
age < 45 group by workaddress having address_count >= 3;
1.4.5 排序查询
举例:
-- 根据年龄升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 两字段排序,根据年龄升序排序,若年龄相同则按入职时间降序排序
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;
1.4.6 分页查询
-- 查询第一页数据,展示10条
SELECT * FROM employee LIMIT 0, 10;
-- 查询第二页
SELECT * FROM employee LIMIT 10, 10;
1.4.7 DQL 执行顺序
1.5 DCL 数据控制语言
1.5.1 DCL 管理用户
查询用户
USER mysql;
SELECT * FROM user;
创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码:
ALTER USER '用户名'@'主机名' IDENTIFIED WITH
mysql_native_password BY '新密码';
删除用户:
DROP USER '用户名'@'主机名';
举例:
-- 创建用户test,只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test,能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with
mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';
1.5.2 DCL 权限控制
常用权限
DCL - 权限控制
查询权限:
SHOW GRANTS FOR '用户名'@'主机名';
授予权限:
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限:
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
举例:
2.函数
2.1字符串函数
常用函数:
-- 拼接
SELECT CONCAT('Hello', 'World');
-- 小写
SELECT LOWER('Hello');
-- 大写
SELECT UPPER('Hello');
-- 左填充
SELECT LPAD('01', 5, '-');
-- 右填充
SELECT RPAD('01', 5, '-');
-- 去除空格
SELECT TRIM(' Hello World ');
-- 切片(起始索引为1)
SELECT SUBSTRING('Hello World', 1, 5);
2.2数值函数
常用数值函数
2.3日期函数
常见日期函数
案例:
2.4流程函数
案例1
案例2
3.约束
3.1 概述
分类:
案例:
解:
3.2 外键约束
添加外键
CREATE TABLE 表名(
字段名 字段类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
– 例子
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
3.3 外键删除更新行为
删除更新行为
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段)
REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;
4.多表查询
4.1多表关系
4.2 多表查询概述
合并查询(笛卡尔积,会展示所有组合结果)
select * from employee, dept;
消除无效笛卡尔积:使用相应的连接表
select * from employee, dept where employee.dept = dept.id;
4.3 内连接
内连接查询的是两张表交集的部分
隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显示内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;
显式性能比隐式高
4.4 外连接
左外连接:
查询左表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;
相当于查询表1的所有数据,包含表1和表2交集部分数据
右外连接:
查询右表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;
4.5 自连接
当前表与自身的连接查询,自连接必须使用表别名
语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
自连接查询,可以是内连接查询,也可以是外连接查询
举例:
4.6 联合查询 union,union all
语法:
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...
举例:
注意:
UNION ALL 会有重复结果,UNION 不会
联合查询比使用or效率高,不会使索引失效
4.7 子查询
4.7.1标量子查询
举例:
4.7.2 列子查询
举例:
4.7.3行子查询
举例:
4.7.4表子查询
举例1.
举例2.
5.事务
事务是一组操作的集合,事务会把所有操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
5.1提交事务
方式一:
方式二:
基本操作:
-- 1. 查询张三账户余额
select * from account where name = '张三';
-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';
-- 此语句出错后张三钱减少但是李四钱没有增加
模拟sql语句错误
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
-- *****************我是分界线*********************
-- 查看事务提交方式
SELECT @@AUTOCOMMIT;
-- 设置事务提交方式,1为自动提交,0为手动提交,该设置只对当前会话有效
SET @@AUTOCOMMIT = 0;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 设置手动提交后上面代码改为:
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
commit;
方式二:
-- 开启事务
start transaction;
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
-- 提交事务
commit;
-- 回滚事务
rollback;
5.2事务的四大特性ACID
- 原子性(Atomicity):事务是不可分割的最小操作但愿,要么全部成功,要么全部失败
- 一致性(Consistency):事务完成时,必须使所有数据都保持一致状态
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的(磁盘)
5.3并发事务问题
5.4并发事务演示及隔离级别
并发事务隔离级别
√表示在当前隔离级别下该问题会出现
- Serializable 性能最低;
- Read uncommitted 性能最高,数据安全性最差
- 隔离级别越高,性能越差,安全性越高
查看事务隔离级别:
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别:
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };
SESSION 是会话级别,表示只针对当前会话有效,GLOBAL 表示对所有会话有效