SQL 知识总结

基础

关系数据库：通过二维表来表示数据之间的联系，每一行表示一个记录，每一列表示一个字段。不同的表通过关联字段来建立联系。
语句不区分大小写

SQL语句分类：
DDL 数据定义语言 create drop alter truncate
DML 数据操作语言 insert update delete call
DQL 数据查询语言 select where
DCL 数据控制语言(访问权限，安全级别) crant revoke

数据类型：
1.数值类型：整数类型（占用空间tinyint,smallint,int,bigint，分别对应1-8个字节的存储空间）、浮点数类型(float,double：4、8个字节)
2. 时间和日期类型：date,time, datetime，timestamp:日期、时间、日期时间和时间戳
3. 字符串类型：char,varchar,text,blob:长字符串、变长字符串、文本、二进制数据
decimal(10,2) 长度为10保留两位小数的二进制数据
新增的json类型、空间类型

流程：创建数据库—》创建表—》查看表的结构（DESC describe的缩写）-----》alter语句修改表结构：属性的类型，长度不够 ----》数据查找与修改

常用的语句

数据库

（为方便小写表示，实际一般大写标注）
创建数据库：create database 数据库名；
展示数据库：show 数据库名;
删除数据库：drop database 数据库名;

表的创建

• 表的创建：
  create table 表名 (
  字段1 字段1类型 属性约束[可选]，
  字段2 字段2类型 属性约束[可选]，
  …
  );
  属性约束：默认值/not null/null(是否允许空值)/unique（字段唯一）/主键约束（每个表只能有一个）、外键约束（一个表的外键必须是另一个表的主键）

CREATE TABLE `student`(
	`student_id` INT auto_increment, 添加数据时自动补足
	`name` VARCHAR(20)  NOT NULL, 这个属性一定要有值
	`major` VARCHAR(20)  unique, 这个属性的值不能重复
	PRIMARY KEY(`student_id` ) 主键设置
	FOREIGN  KEY(`manager_id`) references `employee` (`emp_id`) on delete set NULL 设置外键，关联到其他表的哪个字段
)

on delete set NULL: 对应employee对应的emp_id对应的行数据删掉了，那么brach这张表对应的manager_id设置为NULL（对应不到设置为NULL）
on delete cascade：对应employee对应的emp_id对应的行数据删掉了，那么work_with这张表终对应的emp_id的行数据也同时删除（跟着一起删掉，对应不到就删除）

表的删除

表的删除：drop 表名;

• alter ：创建表对表结构进行修改
  • 修改属性类型：Alter table 表名 modify column 列名 新数据的类型;
  • 设置属性的默认值: Alter table 表名 modify 列名 数据类型 默认值；
  • 重命名属性：Alter table 表名 rename column 旧列名 to 新列名;
  • 添加新字段：Alter table 表名 add column 列名 数据类型;
  • 删除字段：Alter table 表名 drop column 列名;

modify,rename,add,drop

数据插入(insert语句)

插入数据：insert into 表名 (列名) values (数据); 默认是NULL值填充
（）括号必须有

数据查询(select语句)

select * from 表名 （where 判断条件）； *表示所有列名（后可接order by 、group by或者where\ 前聚合函数、distinct）

where

逻辑判断优先级：Not>AND>OR, 可以使用括号改变优先级

• 查找范围 where level in (1,3,5); IN 还会和子查询一起使用
• between…and… 来指定查找范围
  • Select * from 表名 where 列名 between 1 and 10; 包括1和10
  • Select * from 表名 where 列名 not between 1 and 10; 不在1到10范围内（not 可以加在任何一条条件语句前面）
• 模糊查询： like %:任意 _:一个匹配
  • Select * from 表名 where 列名 like 匹配表达式
  • 通用的匹配表达式：
    • **·**任意一个字符
    • **^**开头
    • **$**结尾
    • **[abc]**其中任意一个字符
    • **[a-z]**范围内的任意一个字符
    • A|B:A或者B
  • Select * from 表名 where 列名 regexp 匹配表达式；
    例如：查找王字开头的玩家
  • Select * from 表名 where name like ‘王%’; 查找姓王的
  • Select * from 表名 where name regexp ‘王’ ; 查找姓王的
  • Select * from 表名 where name regexp ‘^王.$’ ; 姓王名字且只有两个字
  • Select * from 表名 where name regexp ‘[王张]’ ; 姓王或者姓张的，匹配其中一个字符
  • Select * from 表名 where email is not NULL ; 邮箱非空
  • Select * from 表名 where email <=> null; 这种写法更直观 orcle更常用

注意：空字符串 和 NULL的判断：NULL需要用is null判断，空字符串用等号判断
Select * from 表名 where email is null or email =’’;
select * from 表名 过滤条件;
select 列名1,列名2 from 表名 过滤条件;

小结：
范围用in
模糊搜索是匹配表达式
具体值用 等于号

排序 order by

默认从小到大排序
Select * from 表名 order by 排序条件;
Eg:
Select * from 表名 order by 列名 (DESC/ASC); DESC降序排列,ASC 升序
Select * from 表名 order by 列名1 DESC, 列名2; 列名1降序排列,列名2升序排列
Select * from 表名 order by 列名位置(5:第5列);

聚合函数

Select count(* 或者 列名) from 表名
Count()计数、avg 求均值、min、max

分组 group by

Select sex,count(*) from 表名 group by sex;
Select level,count(level) from 表名 group by level;计算每个等级玩家有多少名

Group by 还经常和**having（筛选分组后的数据）**一起使用 ，对分组后的结果进行过滤
Select level,count(level) from 表名 group by level having count(level)>4； 保留数量大于4的等级

Group by 还和order by 一起使用，对返回结果进行排序
Select level,count(level) from 表名 group by level having count(level)>4 order by count(level DESC);
[热搜榜 或者排行榜]-----------只有 level 和count(level) 两列

Select * from 表名 limit 3;
Select * from 表名`
order by score DESC
limit 3; 得到分数前3高的资料

练习：1.统计每个姓氏玩家的数量并按照数量降序排序
Select substr(name,1,1),count(substr(name,1,1)) from 表名
group by substr(name,1,1)
having count(substr(name,1,1))>=5 order by count(substr(name,1,1)) DESC #此时返回K条数据
limit 3; 只返回前三名的姓氏（分页查询原理，网站分页效果）

Selct substr(name,1,1)
Substr(要截取的字符串，开始位置，截取长度)

limit:限制数量
limit k；前k名 limit 3,3; 第4名到第6名 第一个3表示偏移量表示从第4名开始 k+1, 第二个3：返回的数量

Distinct:去除重复记录

Select distinct 去重字段 from 表名

集合运算

Union 合并两个查询结果（并集）
查询结果1 union 查询结果2（union 查询结果3…）
[行拼接操作，所有字段一致]
Union 对合并结果去重
Union all :不会对合并结果去重

Intersect 交集
A except B 差集 满足A条件排除B条件

子查询

(用在update\delete\create\insert等等语句)

查找大于平均等级的玩家

Select * from 表名
	Where level > (
	Select avg(level) from 表名
);

等价于

Select level,round(Select avg(level) from 表名) as average,
level-round(Select avg(level) from 表名) as diff
from 表名;

利用子查询创建新表

1.把所有等级小于5的玩家数据插入到新的表中

Create table 新表名 s
elect * from 旧表名 where level<5;

Select * from 新表名;

2.等级6-10的玩家插入到新表中

Insert into 新表 
Select * from 旧表 where level between 6 and 10;

Exists 判断一个查询是否有结果
3.是否有等级大于100的玩家

Select Exists (Select * from 表名 where level>100);

表关联

一般使用表的主键和外键来关联【笛卡尔积+条件过滤】

1. Inter join内连接：只返回两个表中都有的数据
2. Left join左连接：返回左表中有的数据
3. Right join 右连接：返回和右表中匹配的数据，左表没有的数据null填充

Desc equip 装备表

Select * from player 
Inner join equip
On player.id = equip.player_id;

Left join 包含玩家所有信息，玩家有装备就显示装备信息，没有就NULL填充
Right join 包含装备表的所有信息，以及这些装备所对应的玩家的信息

除了用join…on 指定外可以用where 来指定

Select * from play ,equip where player.id = equip.player_id;

Select * from play p,equip e where p.id = e.player_id; 取别名

连接注意笛卡尔积的的问题（两个表的数据进行组合），如果连接没有指定条件或者条件不准确

索引-提高查询效率的结构

Create Index 创建索引（建表时创建或者建表后创建）

**Create [unique|fulltext|spatial]**索引关键字 Index 索引名字 on 表名 (列名,…, 可有)；

1. Unique:唯一索引
2. fulltext:全文索引
3. spatial：空间索引

对哪些字段创建索引，一般对表的主键或者经常查询的字段 创建索引（where 的查询字段）
Eg:
Create index email_index on fast(email); 必须有括号
Show index from 表名; 查看索引
Select * from 表名 where email like ‘abcd%’ order by id; 根据ID字段排序
Select * from fast where email like ‘abcd%’ order by id; 根据ID字段排序 （创建表名）

删除索引: drop index 索引名字 on 表名；

修改表结构时候创建索引：
Alter table 表名 add index 索引名称 (name); 必须有括号

视图

虚拟存在的表，本身并不包含数据，而是作为一个查询语句保存在数据字典中。根据查询语句的定义动态生成数据(数据是动态的)

创建视图：
Create view 视图名称
As
Select * from 表名;（想要查询的select语句）
或者
Select * from 表名 order by level desc
Limit 10; 查询玩家等级在前10名的数据

视图的使用和表是一致的，可以使用正常的select语句来查询视图：
Select * from 视图名称
修改表的数据，视图的数据也会随机发生变化
Update player
Set level = 10
Where id = 64;

修改视图：alter 视图名称 as 新的查询语句

数值更新 update

update 表名 set 属性=修改值 （where 判断条件）；

数据删除 delete

delete from 表名**（where 判断条件）**；

数据导入与导出

导出【大于号】：

mysqldump -u root -p 数据库名称 (表名称) > (导出的文件夹) 

-u指定用户名 -p 参数指定密码
导入【小于号】：

Mysql -u root -p< 文件名