SQL学习-底子篇

提示：文章写完后，目录可以自动天生，怎样天生可参考右边的帮助文档
  
  
一、数据库



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


二、SQL

1，SQL通用语法



1，单行或多行，分号结尾
2，可用空格
3，不区分大小写，但关键字建议大写
4，解释
（1）单行解释：#或–
（2）多行解释：/* ,*/
5，*代表全部数据


2. SQL分类



1，DDL-界说语言-表、字段、数据库
2，DML-操作语言-对数据增删改
3，DQL-查询语言-查询表的记录
4，DCL-控制语言-创建、控制数据库的访问权限
2.1-DDL-建库建表

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

复制代码

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

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

复制代码

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

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

复制代码

2.1.1-数据范例

数值范例
int-整
float-单精度浮点数
double-双精度浮点数


字符串范例
char-定长字符串-用不满会自动填比如char(50)
varchar-变长字符串-varchar(50)-用多少是多少
text-文本数据
日期范例
date-年月日-日期
time-时分秒-时间值或持续时间
year-年份
datetime-年月日+时分秒
2.1.2-DDL-表操作

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

复制代码

2.2-DML-操作数据

添加数据-INSERT
修改数据-UPDATE
删除数据-DELETE
2.2.1-DML-添加数据

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

复制代码

2.2.2-DML-修改数据

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

复制代码

2.2.3-DML-删除数据

1. DELETE FROM 表名 [where 条件]

复制代码

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

编写次序

1. select 字段列表
2. from 表名
3. where 条件列表
4. group by 分组字段列表
5. having 分组后条件列表
6. order by 排序字段列表
7. limit 分页参数

复制代码

执行次序

1. from 表名
2. where 条件列表
3. group by 分组字段列表
4. having 分组后条件列表
5. select 字段列表（设置别名等）
6. order by 排序字段列表
7. limit 分页参数

复制代码

2.3.1-DQL-基本查询

1，查询多个字段

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

复制代码

2，设置别名

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

复制代码

3，去除重复记录

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

复制代码

2.3.2-DQL-条件查询

可以这么写

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

复制代码

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

复制代码

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

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

复制代码

in(…)多选一，比方

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

复制代码

like，'_‘单个字符，’%'多个字符，例
可以not like

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

复制代码

2.3.3-DQL-聚合函数

1，指的是将一列数据作为一个整体，举行纵向盘算。null不到场盘算。
2，常见聚合函数：

• count 统计数量
  
• max
  
• min
  
• avg 平均值
  
• sum
  sum是相加和值，count是统计这一列的个数
  

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

复制代码

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

复制代码

3，语法

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

复制代码

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

1，语法

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

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

一般返回分组之后的字段和聚合函数。
2.3.5-DQL-排序查询(order by)

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

复制代码

排序方式：
ASC：升序（默认）
DESC：降序
2.3.5-DQL-分页查询(LIMIT)

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

复制代码

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

2.4-DCL

2.4.1-DCL-管理用户

1，查询用户

1. USE mysql;
2. select * from user;

复制代码

2，创建用户

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

复制代码

3，修改密码

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

复制代码

4，删除函数

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

复制代码

2.4.1-DCL-权限控制

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

复制代码

三、函数

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

select concat(last_name, " ", first_name) from employees
拼接并且中间以空格隔开

• 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-函数-数值函数

ceil(x)——向上取整
floor(x)——向下取整
mod(x,y)——返回x/y的模（余数）
rand()——返回0-1内的随机数
round(x,y)——求参数x的四舍五入的值，保存y位小数
例select lpad(round(rand()*1000000,0),6,'0');#六位随机验证码
3.3-函数-日期函数

curdate()-返回当前日期
curtime()-返回当前时间
now()-返回当前时间和日期
year(date)-获取指定date的年份
month(date)-获取指定date的月份
day(date)-获取指定date的日期
date_add(date,interval expr type)-返回加上expr的时间/日期值
type是范比方年月日
datediff(date1,date2)-返回起始时间date1和date2之间的天数
timestampdiff(时间格式，开始时间，竣事时间)返回时间差，时间格式可以是年、月、天
date_format(date,format)
DATE_FORMAT将传来的Date范例数据转为本身必要的格式，如%Y-%m-%d %H:%i:%s会将传来的Time数据转为"yyyy-MM-dd HH:mm:ss"格式

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

复制代码

3.4-函数-流程函数

if(value,t,f)-若value为真(true)，则返回t，否则返回f
ifnull(value1,value2)-若value1不为空返回value1，否则返回value2
case [expr] when [val1] then [res1] … else [default] end
——若expr的值等于val1，返回res1，否则返回default

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

复制代码

四、约束

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

• 非空约束-not null
  
• 唯一约束-unique-数据唯一不重复
  
• 主键约束-primary key-一行数据的唯一标识，要求非空且唯一
  
• 默认约束-default-未指定字段的值，采取默认值
  
• 查抄约束-check-包管字段值满足某个条件
  
• 外键约束-foreign key-两个表的毗连
  

用法：在建表时直接在字段数据范例后面加就行
有外键时在父表中不答应删除

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

复制代码

外键约束删除或更新活动
cascade-有外键时，父表做删除或更新，子表也删除或更新
set null-有外键时，父表做删除，子表出null

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

复制代码

五、多表查询（表毗连）

一对多：在多的一方创建外键，指向一的一方的主键
多对一：创建第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一：随意一方加主键关联另一方主键，并设置为唯一的（UNIQUE）

from a left join b 查询a的全部行，并且将全部b里匹配的信息拿过来，若行数不同等或者出现缺失会出现null值
右链接反过来了，查询right join 后的全部行…
5.1 内毗连（查询的是两张表交集的部门）

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

复制代码

5.2 外毗连

左毗连会把相匹配的字段拿过来，不是光on相称的谁人字段

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

复制代码

5.3 自毗连

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

复制代码

5.4 联合查询

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

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

复制代码

多张表的列数要同等，字段范例要同等
union 会去重，union all 全部
5.5 嵌套查询（子查询）

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

复制代码

select可以换为insert/update/delete

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

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


5.5.1-标量子查询

返回单个值（数字，字符串，日期等）
例

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

复制代码

5.5.2-列子查询

返回的是一列
in-指定聚集多选一
not in-不在指定聚集
any-子查询返回列表恣意一个满足即可
some=any
all-必须全部满足
用法all()
例

1. select id from dept where name = '财务部';
2. select salary from emp where dept_id = (select id from dept where name = '财务部')；
3. 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-表子查询

返回多行多列

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

复制代码

六、 窗口函数

搬运
https://zhuanlan.zhihu.com/p/92654574
avg加分组可以直接分组求平均值
sum()over(partition by a)
根据a求和排序

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。