SQL学习-基础篇

大家好，欢迎来到IT知识分享网。

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

一、数据库

1. 数据库相关概念
(1)数据库-DB
(2)数据库管理系统-DBMS-操控数据库的软件
(3)sql-编程语言
2. mysql数据库（RDBMS）
是关系型数据库，建立在关系模型上，由二维表组成的数据库。
3. 数据模型
创建表

二、SQL

1，SQL通用语法

5，*代表所有数据

2. SQL分类

2.1-DDL-建库建表

SHOW DATABASES#查询所有数据库 SELECT DATABASE()#查询当前数据库 CREATE DATABASE[IF NOT EXISTS]#数据库名 [DEFAULT CHARSET]#字符集 [COLLATE]#排序规则,[]代表可有可无 CREATE DATABASE DUAN DEFAULT CHARSET utf8mb4#例子 DROP DATABASE[IF EXISTS]#删除 USE 数据库名#使用 

（1）DDL-表操作​，进入数据库后即USE

SHOW TABLES#查询数据库所有表 DESC#查表 SHOW CREATE TABLE#查询建表语句 CREATE TABLE 表名( 字段1 字段1类型[COMMENT字段1注释], 字段2 字段2类型[COMMENT字段2注释], ..... 字段n 字段n类型[COMMENT字段n注释] )[COMMENT 表注释]#建表，最后一个字段没有逗号，且为英文逗号 

建表例子，第一步先使用数据库

create table tb_user( id int comment '编号', name varchar(50) comment '性名', age int comment '年龄', gender varchar(1) comment '姓别' ) comment '用户表'; 

2.1.1-数据类型

数值类型
int-整
float-单精度浮点数
double-双精度浮点数

字符串类型
char-定长字符串-用不满会自动填比如char(50)
varchar-变长字符串-varchar(50)-用多少是多少
text-文本数据

日期类型
date-年月日-日期
time-时分秒-时间值或持续时间
year-年份
datetime-年月日+时分秒

2.1.2-DDL-表操作

ALTER TABLE 表名 ADD 字段名 类型(长度) comment '注释'#添加字段 ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)#修改数据类型 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)#修改字段名和字段类型 ALTER TABLE 表名 DROP 字段名#删除字段 ALTER TABLE 表名 RENAME TO 新表名#修改表名 DROP TABLE [IF EXISTS] 表名#删除表 TRUNCATE TABLE 表名#删除并重建（只有表结构没有数据） 

2.2-DML-操作数据

2.2.1-DML-添加数据

INSERT INTO 表名 (字段1,字段2,...) VALUES(值1,值2,...)#给指定字段添加数据 INSERT INTO 表名 VALUES (值1,值2,...)#给全部字段添加数据 INSERT INTO 表名 (字段1,字段2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)#批量添加数据 INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...)#给全部字段添加数据 

2.2.2-DML-修改数据

UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[where 条件] 

2.2.3-DML-删除数据

DELETE FROM 表名 [where 条件] 

2.3-DQL-数据查询-业务里用的最多

编写顺序

select 字段列表 from 表名 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数 

执行顺序

from 表名 where 条件列表 group by 分组字段列表 having 分组后条件列表 select 字段列表（设置别名等） order by 排序字段列表 limit 分页参数 

2.3.1-DQL-基本查询

1，查询多个字段

SELECT 字段1,字段2,字段3...FROM 表名 SELECT*FROM 表名#返回所有字段 

2，设置别名

SELECT 字段1 [AS] 别名1,字段2 [AS] 别名2...FROM表名#不加AS就隔个空格就行 

3，去除重复记录

SELECT DISTINCT 字段列表 FROM 表名#在这个字段下重复的归为一类，字段列表的意思同上，即多个字段。 

2.3.2-DQL-条件查询

可以这么写

where(A,B)=(1111,12222)代表A=1111，B等于12222 

SELECT 字段列表 FROM 表名 WHERE 条件列表 

逻辑：
<=,小于等于
！=或<>是不等于
is null没有，空值；is not null 有
and或者&&——且（多个条件成立）
or或者||——或（任一条件成立）
not或者！——非，不是

between 18 and 30;#取值18-30，闭区间 

in(…)多选一，例如

where age in(1,11,111);#年龄为1岁或11岁或111岁 

where name like '__';#两个下划线，即名字两个字 where idcard like '%X';#查询身份证号最后一位是X的 where idcard like '_________________X';#另一种写法17个下划线 where university like '%北京%';#大学的名称里有北京 select prod_name, prod_desc from Products where prod_desc like '%toy%carrots%' #先后出现toy和carrots 

2.3.3-DQL-聚合函数

1. count 统计数量
2. max
3. min
4. avg 平均值
5. sum
   sum是相加和值，count是统计这一列的个数
   

sum(if(qpd.result='right', 1, 0)) #正确返回1 ，不成立返回0 

 round(value,n) #value是数值，n代表保留几位小数 

3，语法

SELECT 聚合函数(字段名) FROM 表名 

2.3.4-DQL-分组查询（group by）

1，语法

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件] #where分组前过滤条件，不满足where条件，不参与分组；having分组后过滤条件。 

where不能对聚合函数判断，having可以。
示例1：根据性别分组，统计男性员工和女性员工数量（不涉及条件）

select gender,count(*) from 表名 group by gender; #因为想看到两列，所以写了gender,count(*)，返回两列 

示例2：根据性别分组，统计男性员工和女性员工平均年龄（不涉及条件）

select gender,avg(age) from 表名 group by gender; 

示例3：查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址。

select '地址' ,count(*) from 表名 where age<45 group by '地址' having count(*)=>3 

一般返回分组之后的字段和聚合函数。

2.3.5-DQL-排序查询(order by)

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2; select * from emp order by age adc,entrydata desc; 

2.3.5-DQL-分页查询(LIMIT)

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;#起始索引别忘了乘记录数 select * from 表名 order by age asc limit 5;#查询前五个员工并年龄升序 

• 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
• 不同的数据库有不同的实现，Mysql是limit
• 第一页可以简写为limit 10

2.4-DCL

2.4.1-DCL-管理用户

1，查询用户

USE mysql; select * from user; 

2，创建用户

create user '用户名'@'主机名' identified by '密码'; 

3，修改密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'; 

4，删除函数

drop user '用户名'@'主机名'; 

2.4.1-DCL-权限控制

show grants for '用户名'@'主机名';#查询权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';#授予权限 revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';#撤销权限 

三、函数

函数指一段可以直接被另一段程序调用的程序或代码。

3.1-函数-字符串函数

• concat()-字符串拼接
• lower()-全部转小写
• upper()-全部转大写
• lpad(a,n,c)-左填充
• rpad(a,n,c)-右填充
  例lpad(a,3,c)结果就是cca，rpad('o','3','6')结果就是066
  
• trim()-去除头尾空格
• substring(A,n,m)-从字符串A中，从n开始截取到m，第一个是从1开始
• substring_index()更好用
• left(column_name,size):取字符串左边 size个字符

使用：select 函数（）;

3.2-函数-数值函数

3.3-函数-日期函数

SELECT DATE_FORMAT(需要转的哪个时间字段,...) SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- 结果：2020-12-07 22:18:58 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'); -- 结果：2020-12-07 22:18 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H'); -- 结果：2020-12-07 22 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); -- 结果：2020-12-07 SELECT DATE_FORMAT(NOW(),'%H:%i:%s'); -- 结果：22:18:58 SELECT DATE_FORMAT(NOW(),'%H'); -- 结果：22 SELECT DATE_FORMAT(NOW(),'%Y%m%d'); -- 结果： 

3.4-函数-流程函数

select name, case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end as '工作地址' from 表名; #这里select后不能重复加workaddress 

四、约束

作用于表中字段上的规则，用于限制存储在表中的数据

1. 非空约束-not null
2. 唯一约束-unique-数据唯一不重复
3. 主键约束-primary key-一行数据的唯一标识，要求非空且唯一
4. 默认约束-default-未指定字段的值，采取默认值
5. 检查约束-check-保证字段值满足某个条件
6. 外键约束-foreign key-两个表的连接

用法：在建表时直接在字段数据类型后面加就行

有外键时在父表中不允许删除

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)# 添加外键 alter table 表名 drop foreign key 外键名称#删除外键 

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update 行为 on delete 行为 

五、多表查询（表连接）

5.1 内连接（查询的是两张表交集的部分）

select 字段列表 from 表1,表2 where 条件...;#隐式内连接 select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;#例 select 字段列表 from 表1 [inner] join 表2 on 连接条件...;#显式内连接，比隐式更快 

5.2 外连接

左连接会把相匹配的字段拿过来，不是光on相等的那个字段

select 字段列表 from 表1 left [outer] join 表2 on 条件...; #左外连接，查询左表，包含交集 select 字段列表 from 表1 right [outer] join 表2 on 条件...; #右外连接，同理 select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;#例 

5.3 自连接

select 字段列表 from 表A 别名A join 表A 别名B on 条件...；#自连接可以左可以右 select a.name,b.name from emp a,emp b where a.mangerid = b.id;#例 

5.4 联合查询

union[all]:把多次查询结果合并，形成新的查询结果集。

示例： select 字段列表 from 表A ... union[all] select 字段列表 from 表B ...;#省略号代表后续加条件等...all代表去重 

5.5 嵌套查询（子查询）

select * from t1 where column1 = (select column1 from t2) 

select可以换为insert/update/delete

• 标量子查询（结果是单个值）
• 列子查询
• 行子查询
• 表子查询（结果多行多列）

根据子查询位置，分为where之后，from之后，select之后。

5.5.1-标量子查询

select id from dept where name = '销售部'; select * from emp where dept_id = 4; select * from emp where dept_id = (select id from dept where name = '销售部'); #将第一个第二个嵌套 

5.5.2-列子查询

select id from dept where name = '财务部'; select salary from emp where dept_id = (select id from dept where name = '财务部')； select * from emp where salaly > all(select salary from emp where dept_id = (select id from dept where name = '财务部')); 

5.5.3-行子查询

返回一行

5.5.3-表子查询

返回多行多列

select job,salary from emp where name = 'A' or name = 'B'; select * from emp where (job,salary) in (select job,salary from emp where name = 'A' or name = 'B'); 

六、 窗口函数