安装
SQL规范
- 不区分大小写,但是建议大写
关键词
,小写表名
、列名
- 每条SQL建议分号结尾
- 每条SQL根据需要进行换行缩进
- 注释:
单行:# --
多行:/* */
类型
数值:
整型:
小数:
定点数
浮点数
字符:
短字符: char varcahr
长文本: text blob
日期:
date 2020-02-03
datetime 2020-02-02 02:02:02
timesiamp 1594279093389
time 02:02:02
year 2020
常用SQL
use test; -- 选中 数据库
show tables; -- 现实当前选中的库的所有表
show tables from mysql; # 查询mysql下的tables
SHOW INDEX FROM stuinfo; # 显示当前的索引
select database(); # 查看当前库
/* create table table1(
id int,
name varchar(24)
); */
desc table1; -- 查看表结构
select * from table1;
insert into table1 (id,name) values(1,'测试'); -- 插入
update table1 set name='我靠' where name='ces'; -- 修改
update table1 set id=0 where name='我靠'; -- 修改
delete from table1 where name='我靠'; -- 删除
常见函数
单行函数
处理
字符函数
SELECT LENGTH('我是谁'); -- 根据当前字符集 得到当前字节长度
SELECT CONCAT('我','是','谁呀'); -- 拼接字符串
SELECT UPPER('Abc'); -- 转换成大写字符
SELECT LOWER('Abc'); -- 转换成小写
SELECT SUBSTR('abc123一二三',4,3); -- 从4开始截取3个 包含4 索引从1开始
SELECT SUBSTRING('abc123一二三',4,3); -- 从4开始截取3个 包含4 索引从1开始
SELECT INSTR('01234556','234'); -- 查找字符串出现的位置 没找到就是0
SELECT TRIM(' A B C D '); -- 去除前后空格
SELECT TRIM('a' FROM 'aaaaA B CaaaDaaaa' ); -- 去除前后的a
SELECT LPAD('abc123一二三',20,'*'); -- 左填充/保留左边的
SELECT RPAD('abc123一二三',20,'*'); -- 右填充/保留左边的
数学函数
SELECT ROUND(0.4); -- 四舍五入
SELECT ROUND(0.5); -- 四舍五入
SELECT ROUND(-0.4); -- 四舍五入
SELECT ROUND(-0.5); -- 四舍五入
SELECT CEIL(0.2); -- 向上取整
SELECT FLOOR(0.9); -- 向下取整
SELECT RAND(); -- 随机数
SELECT TRUNCATE(0.2345,3); -- 保留多少位小数 不进行处理
SELECT MOD(10,3); -- 取余
日期函数
SELECT NOW(); -- 返回当前的日期时间
SELECT CURDATE(); -- 返回当前的日期
SELECT CURTIME(); -- 返回当前时间
SELECT YEAR(NOW()) as `year`, MONTH(NOW()) as `month`, DAY(NOW()) as date as `day`; -- 年/月/日
SELECT STR_TO_DATE('2020-03-23 22:32:12','%Y-%m-%d %H:%i:%s'); -- 将字符串解析成时间
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- 格式化时间
其他函数
SELECT VERSION(); -- 查看版本号
SELECT DATABASE(); -- 查看当前的库
SELECT USER(); -- 当前用户
流程控制函数
SELECT IF(10<5,'大','小'); -- if
SELECT `last_name`, IF(`commission_pct` IS NULL,TRUE,FALSE) AS isPct from `employees` ORDER BY `isPct` DESC; -- if 例子
# case
SELECT `salary`,`department_id`,
CASE department_id
WHEN 80 THEN
salary * 1.2
WHEN 40 THEN
salary * 1.9
ELSE
salary * 0
END AS newMoney
FROM `employees`
ORDER BY department_id DESC;
统计函数
统计
SELECT COUNT(*) FROM `employees`; -- 数量统计
SELECT SUM(`salary`) FROM `employees`; -- 相加和
SELECT AVG(`salary`) FROM `employees`; -- 平均值
SELECT MAX(`salary`) FROM `employees`; -- 最大值
SELECT MIN(`salary`) FROM `employees`; -- 最小值
SELECT COUNT(*) AS `count`, SUM(`salary`) AS `sum`, AVG(`salary`) AS `avg`, MAX(`salary`) as `max`, MIN(`salary`) as `min`
FROM `employees`;
# 注意
/*
*/
常见约束
一种限制,用于限制表中的数据,用来保证表中数据的准确和可靠性
分类:
六大约束:
NOT NULL: 非空,用于保证该字段的值不能为空
DEFAULT: 默认值
PRIMARY KEY: 主键,用于保证该字段具有唯一性(非空)
UNIQUE: 唯一(可空)
CHECK: 检查 (mysql 不支持)
FOREIGN KEY: 外键,用于限制两个表的关系,用于保证该字段必须来自关联表的主键
添加约束的时机:
1. 创建表
2. 修改表
约束的添加分类:
列级约束:
六大约束语法上都支持,外键约束无效
表级约束:
除了非空和默认其他都支持
主键和唯一的区别:
主键:唯一、非空、只能一个
唯一:唯一、可空、多个
外键:
1. 从表设置外键关系
2. 主从表类型一致/兼容
3. 主表关联键一般为主键或唯一
4. 必须对应主表数据,删除先删除从表再删除主表
DQL 数据查询语言
常量、表达式、函数
SELECT 1; -- 常量值
SELECT 10*20; -- 表达式
SELECT VERSION(); -- 函数
别名
SELECT 1+2 as number;
去重
SELECT DISTINCT
`name`
FROM
`table`
+号
SELECT 1+2; -- 数字相加
SELECT 1+'123'; -- 字符串会强转成数字非数字转为0
SELECT 1 + Null; -- 与Null返回Null
字符串连接 concat
SELECT CONCAT('a','b','c'); -- 字符串拼接
SELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees`; -- 拼接字段
条件查询
条件表达式
<
>
>=
<=
!=
<>
<=>
# 等于
SELECT
CONCAT(`first_name`,`last_name`) as `name`
FROM `employees`
WHERE
`first_name`='Bruce';
# 安全等于 可查 Null
SELECT
CONCAT(`first_name`,`last_name`) as `name`
FROM `employees`
WHERE
`first_name`<=>'Bruce';
# 大于
SELECT
*
FROM `employees`
WHERE
`department_id` > 60;
# 小于
SELECT
*
FROM `employees`
WHERE
`department_id` <= 60;
# 不等于
# != 不建议
SELECT
*
FROM `employees`
WHERE
`department_id` <> 60;
逻辑表达式
&&
||
!
AND
OR
NOT
# 且查询
# 不建议 &&
SELECT
CONCAT(`first_name`,`last_name`) as `name`
FROM `employees`
WHERE
`first_name`='Bruce'
AND
`last_name`='Ernst';
# 或
SELECT
CONCAT(`first_name`,`last_name`) as `name`
FROM `employees`
WHERE
`first_name`='Bruce'
OR
`last_name`='K_ing';
# 非
SELECT
CONCAT(`first_name`,`last_name`) as `name`
FROM `employees`
WHERE
NOT
`first_name`='Bruce'
模糊查询
like
模糊查询
%
:任意多个字符
_
: 任意单个字符
\
: 转义
# 包含 a
SELECT * FROM `employees`
WHERE
`first_name` like '%a%';
# 定义转义字符
SELECT * FROM `employees`
WHERE
`last_name` like '%$_%' ESCAPE '$';
between and
范围
在什么到什么之间,包含前后
# 查找100 到200 之间的数据
SELECT * FROM `employees`
WHERE
`employee_id` BETWEEN 100 AND 200;
in
属于
查询是否属于某些列表中的某一个
# 查询是否属于某些列表中的某一个
SELECT * FROM `employees`
WHERE
`job_id` IN ('SH_CLERK','AD_ASST','AD_VP');
is null
or is not null
是否为Null
# 查询字段为空的
SELECT * FROM `employees`
WHERE
`commission_pct` IS NULL;
# 查询不为空的
SELECT * FROM `employees`
WHERE
NOT `commission_pct` IS NULL;
SELECT * FROM `employees`
WHERE
`commission_pct` IS NOT NULL;
<=>
安全等于
既可以判断NULL 又可以判断数值
SELECT * FROM `employees`
WHERE
`commission_pct` <=> NULL;
order by
排序
ASC
升序 DESC
降序 默认 ASC
SELECT * FROM `employees` ORDER BY `salary` ASC; -- 升序
SELECT * FROM `employees` ORDER BY `salary` DESC; -- 降序
SELECT `salary` * IFNULL(`commission_pct`,0) + IFNULL(manager_id,0) as money,`salary` FROM `employees` ORDER BY `money`; -- 表达式别名降序
SELECT LENGTH(`last_name`) as len FROM `employees` ORDER BY len; -- 按函数
SELECT * FROM `employees` ORDER BY `salary` DESC, `employee_id` ASC; -- 多个排序条件
分组查询
# 每个工种的总工资
SELECT SUM(`salary`) AS `money`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `money`;
# 每个工种的最高工资
SELECT MAX(`salary`) as `max`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `max`;
# 查询邮箱包含a的工种的最高工资
SELECT MAX(`salary`) as `max`, `job_id`
FROM `employees`
WHERE email LIKE '%a%'
GROUP BY `job_id`
ORDER BY `max`;
# 查询邮箱包含a的工种的最高工资大于1万的 HAVING
SELECT MAX(`salary`) as `max`, `job_id`
FROM `employees`
WHERE email LIKE '%a%'
GROUP BY `job_id`
HAVING `max` > 10000
ORDER BY `max`;
# 查询名称长度一样的大于5个人的数据
SELECT LENGTH(`first_name`) AS `name`, COUNT(1) AS `count`
FROM `employees`
GROUP BY `name`
HAVING `count` > 5;
# 多字段分组
SELECT AVG(`salary`) AS `avg`,`department_id`,`job_id`
FROM `employees`
GROUP BY `department_id`,`job_id`
ORDER BY `department_id`,`job_id`
连接查询
分类:
按年代划分:
sql92标准:只支持内连接
sql99标准【推荐】:支持内连接+外来连接(左外、右外)+交叉连接
按功能划分:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接
sql92标准
# 等值连接
SELECT e.`first_name`,j.`job_title`,`j`.`job_id`
FROM `employees` as e,`jobs` as j
WHERE `e`.`job_id` = `j`.`job_id`;
# 非等值连接
# 自连接
SELECT e.last_name,m.employee_id,e.manager_id,m.last_name
FROM `employees` AS e, `employees` AS m
WHERE `e`.employee_id = `m`.manager_id
sql99标准
# 等值连接
SELECT `last_name`,`department_name`
FROM employees
INNER JOIN departments
ON employees.`department_id` = departments.`department_id`
# 复杂的等值连接
SELECT department_name,COUNT('*') AS count,MAX(`salary`) AS max,min(`salary`) AS min
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id
WHERE last_name LIKE '%o%'
GROUP BY department_name
HAVING `count` BETWEEN 2 AND 10
ORDER BY `count`
# 多表等值连接
SELECT last_name,department_name,job_title
FROM employees
INNER JOIN departments
ON departments.department_id =employees.department_id
INNER JOIN jobs
ON employees.job_id = jobs.job_id;
子查询
分类:
按出现位置:
select 后面:
只支持标量子查询
form 后面
表子查询
where 和having后面 *
标量子查询 *
列子查询 *
行子查询
exists后面
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集只有多列一行)
表子查询(结果集一般多行多列)
where 和having后面
特点: 1. 放在小括号内 2. 放在条件右侧 3. 标量子查询:配合单行操作符。列子查询:配合多行操作符。
# 查询工资比Abel高的人
SELECT * FROM employees
WHERE salary > (
SELECT salary FROM employees
WHERE last_name = 'Abel'
);
# job_id与141号员工一样且工资大于143号员工的员工
SELECT last_name,salary,employee_id FROM employees
WHERE job_id = (
SELECT job_id FROM employees WHERE employee_id = 141
) AND salary > (
SELECT salary FROM employees WHERE employee_id = 143
)
# 查询比50号部门最低工资高的部门的最低工资
SELECT department_id, COUNT(*),MIN(salary) AS minsalary FROM employees
GROUP BY department_id
HAVING minsalary > (SELECT MIN(salary) AS minsalary FROM employees WHERE department_id = 50)
ORDER BY minsalary DESC;
分页查询
SELECT *
FROM `employees`
LIMIT (page-1)*size,size;
SELECT *
FROM `employees`
LIMIT 10,10;
联合查询
# 无
DML 数据操作语言
插入
INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES('p_a','捡垃圾1',200,6000);
INSERT INTO jobs
VALUES('p_a1','捡垃圾1',200,6000),
('p_a2','捡垃圾2',200,6000);
INSERT INTO jobs SET job_id = 'ces',job_title="123"
INSERT INTO jobs SELECT 'ces1','444',200,6000
修改
# 简单修改
UPDATE jobs
SET job_title = '2222'
WHERE job_id = 'ces1'
# 修改多表
UPDATE jobs
INNER JOIN employees
ON employees.job_id = jobs.job_id
SET jobs.job_title = CONCAT(jobs.job_title,'22222'),employees.job_id = 'ces1'
WHERE jobs.job_title = 'Public Accountant12322222'
删除
# 单表删除
DELETE FROM jobs
WHERE job_id = 'ces'
# 清空整表
TRUNCATE TABLE ttt;
# 多表删除
DELETE employees
FROM employees
INNER JOIN jobs
ON jobs.job_id = employees.job_id
WHERE jobs.job_id = 'SA_MAN'
DDL 数据定义语言
1. 库的管理
创建\修改\删除
2. 表的管理
创建\修改\删除
创建: create
修改: alter
删除: drop
库管理
# 创建 IF NOT EXISTS 进行容错 CHARACTER SET 字符集
CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;
# 修改 不能该了
RENAME DATABASE books TO newbook;
# 更改字符集
ALTER DATABASE books CHARACTER SET gbk;
# 库的删除
DROP DATABASE IF EXISTS books;
表的管理
创建表
USE books;
# 表的创建
/*
CREATE TABLE 表名(
列名 列类型([长度]) [约束],
列名 列类型([长度]) [约束],
...
)
*/
CREATE TABLE book (
id INT,
b_name VARCHAR(20),
price DOUBLE,
author_id INT,
publish_date DATETIME
);
DESC book;
CREATE TABLE author(
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);
DESC author;
表的修改
/*
修改列:
ALTER TABLE 表名 [CHANGE|MODIFY|ADD|DROP] COLUMN 列名 类型|约束;
修改表名:
ALTER TABLE 表名 RENAME TO 新表名;
*/
# 修改列名
ALTER TABLE book CHANGE COLUMN publish_date publishDate DATETIME;
# 修改列类型约束
ALTER TABLE book MODIFY COLUMN publishDate TIMESTAMP;
# 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
# 删除
ALTER TABLE author DROP COLUMN annual;
# 修改表名
ALTER TABLE author RENAME TO authers;
表的删除
DROP TABLE IF EXISTS authers;
表的复制
# 复制表结构
CREATE TABLE copy LIKE author;
# 复制整个表
CREATE TABLE copy2
SELECT * FROM author;
约束
添加表时添加约束
# 列级约束
CREATE TABLE stuinfo(
id INT PRIMARY KEY, # 主键
stu_name VARCHAR(20) NOT NULL, # 非空
gender CHAR(1) DEFAULT '男', # 默认
seat INT UNIQUE # 唯一
);
# 表级约束
CREATE TABLE stuinfo(
id INT, # 主键
stu_name VARCHAR(20), # 非空
gender CHAR(1) , # 默认
seat INT, # 唯一
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),# 主键
CONSTRAINT uq UNIQUE(seat),# 唯一
CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);
# 通用建议约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT PRIMARY KEY, # 主键
stu_name VARCHAR(20) NOT NULL, # 非空
gender CHAR(1) DEFAULT '男', # 默认
seat INT UNIQUE, # 唯一
majorid INT,
-- 表级约束
CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);
修改表时的约束
# 修改列约束
ALTER TABLE stuinfo MODIFY COLUMN stu_name VARCHAR(20) NOT NULL;
# 表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
# 删除外键
# ALTER TABLE stuinfo DROP PRIMARY KEY;
标识列(自增长列)
AUTO_INCREMENT
CREATE TABLE stuinfo(
id INT PRIMARY KEY AUTO_INCREMENT, # 主键
stu_name VARCHAR(20) NOT NULL, # 非空
gender CHAR(1) DEFAULT '男', # 默认
seat INT UNIQUE, # 唯一
majorid INT,
-- 表级约束
CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);
TCL 事务控制语言
一个或一组sql语句组成一个执行单位,要么全部执行,要么全部失败。
ACID: A: 原子性 C: 一致性 I: 隔离性 D: 持久性
# 提交表示完成,回滚代表异常
set autocommit = 0; -- 关闭自动事务
START TRANSACTION; -- 开始事务
UPDATE stuinfo SET stu_name = '12232' WHERE id = 3;
SAVEPOINT a; -- 保存节点
UPDATE stuinfo SET stu_name = '12332' WHERE id = 5;
ROLLBACK; -- 回滚事务
ROLLBACK TO a; -- 回滚事务到指定节点
COMMIT; -- 提交事务
网友评论