大家好,今天是陈熹专栏
SQL是一个存活近半个世纪的语言,如今仍有大量人在使用。它语法简单,对培养数据整理和提取的思维有很大帮助。我将我过去的笔记分享给大家,希望能为大家的学习提供参考,更希望有人因此能迈出学习SQL的第一步~
既然是笔记,就省去了很多介绍性定义,一切都在代码在注释中!这一万字符的笔记中包含了SQL中的全部常用操作,可以边看边敲也可以收藏当成速查手册随用随查!
连接Mysql
mysql -h$ip -P$port -u$user -p
net start mysql # 或 mysql.server start
mysql -u root -p
mysql -u user -p db_name # 直接进入指定数据库
导入导出
导出现有数据库数据:
mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据
mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 结构
导入现有数据库数据:
mysqldump -uroot -p密码 数据库名称
数据库操作
SELECT version(), current_date, now(); # 查看mysql版本号和日期、时间
SELECT version(); select now(); select current_date; # 分开写则表格分开
SOURCE c://test.sql # 用txt文件保存命令该后缀名 可直接执行文件内的命令
\c # 撤销先前输入
\G # 按行输出
SHOW DATABASES; # 查看所有数据库
SELECT DATABASE(); # 进入数据库后查看当前数据库
# 数据库名称组成除了三大项还可以含$,但不能是纯数字
CREATE DATABASE db_name; # 创建spiders数据库
CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
# 安装utf8规则排序
# utf8mb4支持墨迹表情
USE db_name; # 进入数据库
ALTER DATABASE db_name DEFAULT CHARSET SET utf8; # 修改数据库
DROP DATABASE db_name; # 删除数据库
DROP VARIABLES LIKE 'datadir'; # 查看数据库所在位置
SHOW TABLES; # 查看所有表
DESCRIBE db_name; # 查看表的描述,也可以用 DESC table;
SELECT * FROM db_name; # 查看表中所有数据
SELECT host,user FROM db_name; # 大小写不敏感
INSERT INTO person(name,birth) VALUES('A',1994-01-01); # VALUE也可
表操作:创建表
CREATE TABLE students(
nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, # 自增列必须是索引,最好是主键
name VARCHAR(20),
num INT NOT NULL DEFAULT 2)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
主键
一张表只能有一个主键,值是唯一的(或多列组合是唯一的),不能重复不能为空,一般情况下自增列设置为主键。常用nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY一张表可以多个唯一列
CREATE TABLE tb1(
nid INT NOT NULL,
num INT NOT NULL,
PRIMARY KEY(nid,num) # 两列组成一个主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
主键的作用:约束
索引,加速查找
外键
# 创建表时创建
CREATE TABLE color(
INT NOT NULL PRIMARY KEY,
name CHAR(16) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;;
CREATE TABLE fruit(
INT NOT NULL PRIMARY KEY,
smt CHAR(32) NOT NULL ,
color_id INT NOT NULL,
CONSTRAINT fk_fruit_color FOREIGN KEY (color_id) REFERENCES color(nid)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;;
# 表外创建
ALTER TABLE students ADD CONSTRAINT fk_tb2_tb1 FOREIGN KEY tb2(info) REFERENCES tb1(nid);
修改表属性
# 添加列:
ALTER TABLE 表名 ADD 列名 类型
# 删除列:
ALTER TABLE 表名 DROP COLUMN 列名
# 修改列:
ALTER TABLE 表名 MODIFY COLUMN 列名 类型; 一般只改类型
ALTER TABLE 表名 CHANGE 原列名 新列名 类型; 可改列名+类型
# 添加主键:
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
# 删除主键:
ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE 表名 MODIFY 列名 INT, DROP PRIMARY KEY;
# 添加外键:
ALTER TABLE 从表 ADD CONSTRAINT 外键名称(形如:FK_从表_主表)FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段);
# 删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
# 修改默认值:
ALTER TABLE tbl ALTER列名 SET DEFAULT 1000;
# 删除默认值:
ALTER TABLE tbl ALTER列名 DROP DEFAULT;
数据类型
bit(M):
二进制位,M为1~64,默认M=1
int:
nid int unsigned最大4GB (2**32 − 1)
整数类型中的m仅用于显示,对存储范围无限制
tinyint:有符号-128127,无符号0255,默认是signed
MySQL中无布尔值,使用tinyint(1)构造
bigint
smallint
decimal:
精确的小数,能够存储精确值的原因在于其内部按照字符串存储
num decimal(6,2) 6个有效数字(总长度),2位小数,最大是65和30
float,double: 不精确
char:
唯一定长,其他都是变长,数据不到该长度其他空间会闲置,查找速度快,浪费空间
varchar: 变长,相对于char效率低。查找下一列时不确定跳多少存储空间
字符最大是255
text:字符最大是65535 2**16-1
mediumtext:2**24-1
longtext:2**32-1
二进制数据:
TinyBlob、Blob、MediumBlob、LongBlob
上传文件,blob强制二进制方式。现在多用varchar保存路径,上传文件保存在硬盘
时间
DATE
YYYY-MM-DD(1000-01-01/9999-12-31)
TIME
HH:MM:SS('-838:59:59'/'838:59:59')
YEAR
YYYY(1901/2155)
DATETIME
YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59 Y)
TIMESTAMP
YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)
枚举 enum
支持65535个元素枚举
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
集合 set
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
表内容操作
# 写的顺序
SELECT... FROM...
WHERE...
GROUP BY... HAVING...
ORDER BY...
LIMIT...
# 执行顺序
FROM...
WHERE...
GROUP BY...
SELECT...
HAVING...
ORDER BY...
LIMIT...
表内容操作—增
INSERT INTO 表 (列名,列名...) VALUES(值,值,值...);
INSERT INTO 表 (列名,列名...) VALUES(值,值,值...),(值,值,值...); # 增加多条数据
INSERT INTO 表 (列名,列名...) SELECT 列名,列名 FROM 表; # 后面选择列不需要空格
# 如果数据可以转则允许互转
insert into students(name,age) SELECT caption,nid FROM tb2 WHERE nid>2;
表内容操作—删
DELETE FROM 表;
DELETE FROM 表 WHERE id=1 AND name='alex';
DELETE FROM 表 WHERE id > 1 OR name='alex';
# 删除和清空
DROP TABLE student; # 删除表
DELETE FROM student; # 清空表,有自增列则清空后自增记忆存在
TRUNCATE (TABLE) student;# 快速清空表,有自增列则清空后自增从1重新开始
表内容操作—改
UPDATE students SET name = 'alex' WHERE id>1;
UPDATE salary SET sex = IF(sex = 'm', 'f', 'm') # 条件判断更改
UPDATE salary SET sex = char(ascii('m') + ascii('f') - ascii(sex));
UPDATE salary
SET
sex = CASE sex
WHEN "m" THEN "f"
ELSE "m" END;
# UPDATE和JOIN
UPDATE A JOIN B ON A.URL = B.URL
SET member_id = '00012138'
WHERE LOGIN_time BETWEEN '2019' AND '2020'
AND B.class_id = 'TNT'; # BETWEEN 后可以再跟AND
表内容操作—查
SELECT * FROM 表;
# 要全部列可以把列名写一遍,效率比*高
SELECT * FROM 表 WHERE id <> 1; # 即!=
SELECT * FROM 表 WHERE id BETWEEN 3 AND 5; # 即 id>=3 AND id<=5
SELECT nid,name,gender FROM 表 WHERE id%2 = 1;
SELECT DISTINCE name FROM score WHERE num
# distinct 去重只保留各组一项 也可以使用group by
WHERE (class, name) NOT IN (SELECT class, name FROM B) # 多个字段限制
表内容操作—条件选择
SELECT * FROM 表 WHERE id BETWEEN 5 AND 16; # 也支持多个并列
SELECT * FROM 表 WHERE id IN(11,22,33)
SELECT * FROM 表 WHERE id NOT IN(11,22,33)
SELECT * FROM 表 WHERE id IN(select nid from 表)
表内容操作—通配符模糊匹配
SELECT * FROM 表 WHERE name LIKE 'ale%' # - ale开头的所有(多个字符串) where ... like ''
SELECT * FROM 表 WHERE name LIKE 'ale_' # - ale开头的所有(1个字符)
SELECT * FROM 表 WHERE name LIKE '_le%'
表内容操作—限制/分页
SELECT * FROM 表 LIMIT 5; # 前5行
SELECT * FROM 表 LIMIT 4,5; # 从第4行下一行开始的5行
SELECT * FROM 表 LIMIT 5 OFFSET 4 # 从第4行开始的5行,与上一条功能一样
表内容操作—排序
SELECT * FROM 表 ORDER BY 列 ASC # 根据 “列” 从小到大排列
SELECT * FROM 表 ORDER BY 列 DESC # 根据 “列” 从大到小排列
SELECT * FROM 表 ORDER BY 列1 DESC,列2 ASC # 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序,否则一列相同时其他列默认升序排序
表内容操作—分组+聚合
# group by获取各组第一行作为标识,其余行丢弃
SELECT num FROM 表 GROUP BY num
# 分组的原理默认是升序排序,也可以降序
SELECT num FROM 表 GROUP BY num DESC
SELECT count(1) FROM 表 GROUP BY num # 也可使用
SELECT count(name) FROM students GROUP BY classid
# count(1) 和 count(name)区别
# 如果name中有null则count(name)不记录,其余时候二者完全一样
SELECT count(distinct name) FROM students GROUP BY classid # 去重
# sum只能对int类型计算 否则是0
# 多列分组 多个均相同的分到一组
SELECT num,nid FROM 表 GROUP BY num,nid
SELECT num,nid FROM 表 WHERE nid > 10 GROUP BY num,nid ORDER BY nid DESC
SELECT num,nid,count(*) AS count,sum(score) AS sum,avg(score),max(score),min(score) FROM 表 GROUP BY num,nid # as 是自定义命名
# 如果要对聚合函数进行筛选需引入having 顺序在group by后
SELECT num FROM 表 GROUP BY num HAVING max(id) > 10
# 聚集函数
count sum max min avg group_concat(字符串拼接) # 都会去除null
# sum(1) 等于 count(1) 只能针对int类型
# group_concat
SELECT id,GROUP_CONCAT(name) FROM aa GROUP BY id;
SELECT id,GROUP_CONCAT(name SEPARETOR ';') FROM aa GROUP BY id;
SELECT id,GROUP_CONCAT(DISTINCT name) FROM aa GROUP BY id;
SELECT id,GROUP_CONCAT(name ORDER BY name DESC) FROM aa GROUP BY id;
# 语法如下
DISTINCT name ORDER BY id DESC SEPARETOR '-'
表内容操作—组合
# 纵向组合,并以第一个表字段为准
# 组合,自动处理重合
SELECT nickname FROM A UNION SELECT name FROM B;
# 组合,不处理重合
SELECT nickname FROM A UNION ALL SELECT name FROM B;
表内容操作—连表
# 需要多行之间满足一个需求就需要join
# 笛卡尔积
SELECT * FROM students,disc_info;
# 根据对应关系连表,实际等同于inner join
SELECT * FROM students,disc_info WHERE students.discipline = disc_info.nid;
SELECT students.name,disc_info.discipline FROM students,disc_info WHERE students.discipline = disc_info.nid;
# join 左右连接如果无对应关系显示NULL,join效率高
# 取交集,inner join,也可以理解成过滤掉含NULL数据行的左右连接
SELECT A.num, A.name, B.name FROM
A INNER JOIN B
ON A.nid = B.nid;
# 右连接,right join
SELECT A.num, A.name, B.name FROM
A RIGHT JOIN B
ON A.nid = B.nid
# 左连接,right join
SELECT A.num, A.name, B.name FROM
A LEFT JOIN B
ON A.nid = B.nid
表内容操作—CASE WHEN 判断
CASE WHEN 表达式
THEN 输出
WHEN 表达式 # 多少个WHEN都可以
THEN 输出
ELSE
输出
END
SELECT id,name,
(
CASE WHEN classid = 1 THEN 2
WHEN classid =2 THEN 1
ELSE classid END
) AS clid
FROM stu;
# 把tidydata转为正常数据
SELECT name,
MAX(CASE WHEN project = '基础' THEN score ELSE NULL END) as '基础',
MAX(CASE WHEN project = '爬虫' THEN score ELSE NULL END) as '爬虫',
MAX(CASE WHEN project = 'SQL' THEN score ELSE NULL END) as 'SQL'
FROM score;
# 查询两门及以上不及格同学信息
SELECT st.Name, AVG(score) as Score_n
FROM SC JOIN student st ON SC.SId = st.SId
GROUP BY SId
HAVING COUNT(CASE WHEN Score =2;
表内容操作—半连接
SELECT
id,NAME,classid
FROM
student tf
WHERE
id = (
SELECT
max(id)
FROM
student ts
WHERE
ts.classid = tf.classid
);
# 也可以用常规方法
SELECT
id,NAME,classid
FROM
(
SELECT
max(id)
FROM
student
GROUP BY
classid
);
表内容操作—索引
# 基于B+树
# 数据分的越开的列则建索引效果越好
# OR情况不能用索引
# 如果是联合索引 前部过滤条件可以做为索引
# 在SQL语句前加EXPLAIN就可以明确是否走索引
# 创建索引
CREATE INDEX name_index ON student(name);
# 聚簇索引叶子节点跟着数据,非聚簇索引叶子节点跟着主键(聚簇索引)
# 非聚簇走完多数会再走聚簇,除非SELECT内容均被包含于索引(全覆盖索引)
# MySQL中主键是聚簇索引,其他均为非聚簇索引
# 如果没有主键,内部会虚拟一个AUTO_INCREMENT的主键
表内容操作—视图
CREATE VIEW V1 AS # 反复利用某个临时表则可以创建视图
SELECT * FROM stu WHERE id > 10;
# 视图是一个动态表,会从物理表动态读出来。但无法直接对虚拟表即视图修改
# 修改视图
ALTER VIEW V1 AS SELECT * FROM stu WHERE id > 20;
# 删除视图
DROP VIEW V1;
表内容操作—触发器
DELIMITER // # 修改终止符
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT INTO tb1 FOR EACH ROW
BEGIN
INSERT INTO tb2(name) VALUES('chenx');
INSERT INTO tb2(name) VALUES(NEW.sname); # 指代新插入的一行,OLD可以用在DELETE和UPDATE
END //
DELIMITER ; # 修改回原终止符
# BEFORE可以换成AFTER,INSERT可以换成DELETE或者UPDATE
# 删除触发器
DROP TRIGGER tri_before_insert_tb1;
表内容操作—函数
# 内置函数
SELECT CURDATE(); # 执行函数的用法
SELECT DATE_FORMAT(ctime, "%Y-%m") FROM stu GROUP BY DATE_FORMAT(ctime, "%Y-%m") # 时间格式化
# 自定义函数
DELIMITER \\
CREATE FUNCTION f1(
i1 INT,
i2 INT)
RETURNS INT# 强类型语言
BEGIN
DECLARE num INT DEFAULT 0; # 声明变量
SET num = i1 + i2;
RETURN(num);
END \\
DELIMITER ;
看到这里,不要忘记点赞+收藏+转发三连,毕竟在看等于理解,转发等于学会拜拜~