SQL

发布日期: 2022-08-12

文章字数: 1.8k

阅读时长: 8 分

阅读次数:

SQL 常用命令

数据定义语言 DDL

创建数据库

--CREATE DATABASE 数据库名
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

创建表

/*
CREATE TABLE IF NOT EXISTS 表名 (
    `字段名` 数据类型 [约束1] [约束2] [约束3] [...] [注释],
    `字段名` 数据类型 [约束1] [约束2] [约束3] [...] [注释],
    PRIMARY KEY(主键) 
)ENGINE=引擎名, DEFAULT CHARSET=字符集编码
*/
CREATE TABLE `student1` (
  `id`  int(10) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(40) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` varchar(10) NOT NULL DEFAULT '123456' COMMENT '密码',
  `age` int NOT NULL COMMENT '年龄',
  `date` datetime NOT NULL COMMENT '注册日期',
  `email` varchar(20) NOT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

删除表

DROP TABLE IF EXISTS 表名

显示表的结构

desc '表名'

修改表

修改表名

--ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE `student1` RENAME AS `student2`

增加表的字段

--ALTER TABLE 旧表名 ADD 字段名 字段属性...
ALTER TABLE `student2` ADD `address` VARCHAR(1024) COMMENT '地址'

删除表的字段

ALTER TABLE `student2` DROP `address`

修改表的字段

--修改约束
ALTER TABLE `student2` MODIFY `address` VARCHAR(2048);

--修改字段名
ALTER TABLE `student2` CHANGE `address` `Address`  VARCHAR(2048);

/*
相同点：都是用来改变字段的属性，change和modify执行成功后都会这本次设置的属性替换字段原属性
不同点：重命名只能使用change
*/

添加外键

ALTER TABLE `student01` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

DML语言 (数据库管理语言)

增加记录 insert

--INSERT INTO 表名(字段名1,字段名2,字段名3...) VALUES (值1,值2,值3...)
INSERT INTO `student2`(`name`,`password`,`age`) VALUES ('小埋','123456',16)

更改记录 update

--UPDATE 表名 SET 字段名1=值1[,字段名2=值2,字段名3=值3...] [WHERE 条件1 AND 条件2 AND 条件3...]
UPDATE `student2` SET `name`='张三' WHERE `id` = 1;
UPDATE `student2` SET `age`=20, `email`='123456@qq.com' WHERE `name` = '张三' AND `id` = 1 ;

删除记录 delete

--DELETE FROM 表名 [WHERE 条件1 AND 条件2 AND 条件3...]
DELETE FROM `student2` WHERE `name` = '张三' 

--TRUNCATE TABLE 表名
TRUNCATE TABLE `student2`

/*
DELETE FROM 与 TRUNCATE TABLE 的区别
DELETE FROM 是一条一条删除记录，不改变自增计数器
DELETE FROM 是清空所有记录，自增计数器被重置
*/

DQL语言 (数据库查询语言)

select 字段名 from 表名

--查询全部字段
SELECT * FROM `student`
--查询指定字段
SELECT `studentno`, `studentname` FROM `student` AS 学生表

AS 关键字用于取别名

SELECT `studentno` AS 学号, `studentname` AS 姓名 FROM `student` AS 学生表

distinct 关键字用于去除重复数据

SELECT DISTINCT `studentno` AS 学号 FROM  `result`

select 表达式 from 表名

--数据库中的表达式：文本值，NULL，函数，字段名，算术表达式，系统变量

SELECT VERSION() --数据库版本 （函数）

SELECT 1+63*2 AS 计算结果 --（算术表达式）

SELECT @@auto_increment_increment --自增的步长（变量）

函数 concat

SELECT CONCAT('姓名: ',`studentname`) AS 姓名 FROM `student`

where 条件查询

SELECT `studentno` AS 学号, `studentresult` AS 成绩 FROM `result` 
WHERE `studentresult` > 80;

SELECT `studentno` AS 学号, `studentresult` AS 成绩 FROM `result` 
WHERE `studentresult` >= 80 AND `studentresult` <= 90;

SELECT `studentno` AS 学号, `studentresult` AS 成绩 FROM `result` 
WHERE `studentresult` BETWEEN 80 AND 90;

SELECT `studentno` AS 学号, `studentresult` AS 成绩 FROM `result` 
WHERE `studentresult` != 80;

SELECT `studentno` AS 学号, `studentresult` AS 成绩 FROM `result` 
WHERE NOT `studentresult` = 80;

模糊查询

like

-- % 代表零个或多个任意字符, _ 代表一个任意字符

SELECT `subjectname` AS 课程号,`subjectname` AS 课程名, `classhour` AS 课时 FROM `subject`
WHERE `subjectname` LIKE '%C语言%'

SELECT `subjectname` AS 课程号,`subjectname` AS 课程名, `classhour` AS 课时 FROM `subject`
WHERE `subjectname` LIKE '高等数学-_'

in

SELECT `subjectname` AS 课程号,`subjectname` AS 课程名, `classhour` AS 课时 FROM `subject`
WHERE `subjectname` IN ('高等数学-1','高等数学-2')

is null

SELECT `subjectname` AS 课程号,`subjectname` AS 课程名, `classhour` AS 课时 FROM `subject`
WHERE `subjectname` IS NULL

SELECT `subjectname` AS 课程号,`subjectname` AS 课程名, `classhour` AS 课时 FROM `subject`
WHERE `subjectname` IS NOT NULL

联表查询 join on

inner join

--TableA inner join TableB  从多个表中返回满足 JOIN 条件的所有行
SELECT s.`studentno` AS 学号,`studentname` AS 姓名, `subjectno` AS 课程号, `studentresult` AS 课程成绩
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`;

left join

--LEFT JOIN 即使右表中没有匹配，也从左表返回所有的行
--例：查询未参加考试的同学
SELECT s.`studentno` AS 学号,`studentname` AS 姓名, `subjectno` AS 课程号, `studentresult` AS 课程成绩
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE `studentresult` IS NULL

right join

--RIGHT JOIN 即使左表中没有匹配，也从右表返回所有的行
--例：查询参加考试的学生的所有课程信息及成绩
SELECT stu.`studentno` AS 学号,stu.`studentname` AS 姓名, res.`subjectno` AS 课程号,sub.`subjectname` AS 课程名, res.`studentresult` AS 课程成绩
FROM `student` AS stu
RIGHT JOIN `result` AS res 
ON stu.`studentno` = res.`studentno`
INNER JOIN `subject` AS sub
ON res.`subjectno` = sub.`subjectno`

natural join 自然连接

-- 消除了重复属性
select sID from
Student natural join Apply

自查询

SELECT father.`categoryid` AS '父栏目id',father.`categoryname` AS '父栏目名称', son.`categoryid` AS '子栏目id', son.`categoryname` AS '子栏目名称'
FROM `category` AS father, `category` AS son
WHERE father.`categoryid` = son.`pid`

排序 order by 字段名 asc/desc

SELECT * 
FROM `result`
ORDER BY `studentresult` ASC --升序

SELECT * 
FROM `result`
ORDER BY `studentresult` DESC --降序

分页 limit 起始记录索引,页面记录数

SELECT * 
FROM `subject`
ORDER BY `subjectno` ASC
LIMIT 0,5 --从第一条记录开始，显示5条记录

子查询

--在where里面可以嵌套select查询的结果
SELECT `studentname`,`studentno`
FROM `student` s
WHERE `studentno` IN (
	SELECT DISTINCT `studentno`
	FROM `result`
	WHERE `subjectno` = (
		SELECT `subjectno`
		FROM `subject`
		WHERE `subjectname` = '高等数学-1' 
	) AND `studentresult` > 60
)


-- exists 关键字
select cName, state
from College C1
where exists (
  select * from College C2 
  where C1.state = C2.state 
  and C1.cName < C2.cName 
)

分组及分组过滤

SELECT `subjectname` AS 课程名,AVG(`studentresult`) AS 平均分, SUM(`studentresult`) AS 总分
FROM `subject` s
INNER JOIN `result` r
ON s.`subjectno` = r.`subjectno`
GROUP BY `subjectname` --分组
HAVING 平均分 > 60 AND 总分 > 100 --过滤

集合操作

并集 union

-- union 之后自动排序
select cName as name from College
union
select sName as name from Student

-- union all 之后不排序
select cName as name from College
union all
select sName as name from Student

交集 intersect

select cName as name from College
intersect
select sName as name from Student

差集 except

select cName as name from College
except
select sName as name from Student

MySQL函数

常用函数 http://c.biancheng.net/mysql/function/

-- 时间
SELECT NOW() --当前时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT USER()
SELECT VERSION()

聚合函数 Aggregation

计数 count

count(*) --包括了所有的列，相当于行数，在统计结果的时候， 不会忽略列值为NULL  
count(1) --包括了忽略所有列，用1代表代码行，在统计结果的时候， 不会忽略列值为NULL  
count(列名) --只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数， 即某个字段值为NULL时，不统计。
/*
列名为主键，count(列名)会比count(1)快  
列名不为主键，count(1)会比count(列名)快  
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）  
如果有主键，则 select count（主键）的执行效率是最优的  
如果表只有一个字段，则 select count（*）最优。
*/

max,min,avg,sum

同理

加密函数

SELECT MD5('123456')

AkiiLucky

https://akiilucky.github.io/2022/08/12/sql-notes/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 AkiiLucky !

SQL MySql 数据库

动态规划的基本概念

2022-08-12 算法

DP 动态规划

写文章示例

这是你自定义的文章摘要内容，如果这个属性有值，文章卡片摘要就显示这段文字，否则程序会自动截取文章的部分内容作为摘要

2022-08-11 Markdown

Typora Markdown

SQL笔记

SQL 常用命令

数据定义语言 DDL

创建数据库

创建表

删除表

显示表的结构

修改表

修改表名

增加表的字段

删除表的字段

修改表的字段

添加外键

DML语言 (数据库管理语言)

增加记录 insert

更改记录 update

删除记录 delete

DQL语言 (数据库查询语言)

select 字段名 from 表名

AS 关键字用于取别名

distinct 关键字用于去除重复数据

select 表达式 from 表名

函数 concat

where 条件查询

模糊查询

like

in

is null

联表查询 join on

inner join

left join

right join

natural join 自然连接

自查询

排序 order by 字段名 asc/desc

分页 limit 起始记录索引,页面记录数

子查询

分组及分组过滤

集合操作

并集 union

交集 intersect

差集 except

MySQL函数

常用函数 http://c.biancheng.net/mysql/function/

聚合函数 Aggregation

计数 count

max,min,avg,sum

加密函数