常用SQL

2021-10-02 13:35:56

1.创建视图

# 视图跟索引是不一样的，
#	索引是为了加快引擎查询速度，是存储引擎用于快速找到记录的一种数据结构，涉及到数据结构和算法
#	而视图可以理解成一个封装了很复杂SELECT语句的快捷键

show DATABASES;
USE mybaties;
CREATE VIEW shitu AS SELECT * from student;

# 视图还可以被嵌套，一个视图中嵌套另一个视图

# 视图创建好后，相当于一张临时表，就可以执行原来复杂的逻辑了
SELECT DISTINCT * from shitu ORDER BY id DESC LIMIT 0,10;		#分页和降序排序

# 更新视图
CREATE OR REPLACE VIEW view_name AS SELECT 列名 FROM 表名 WHERE 条件

# 删除视图
DROP VIEW view_name

2.多个表联合查询

# 多个表联表查询
#   1.这几个表里的数据字段完全一样
#   2.这几个表里的数据字段不完全一样，比如说存在外键
#   3.不加筛选条件，只是单纯的将多表拼接合一   用 Union All

# UNION ALL 只要求两个表具有数量相同的列数即可，没有要求类型也得一致
# [21000][1222] The used SELECT statements have a different number of columns
SELECT * from student UNION ALL SELECT * FROM person ;

3.union all 的用法

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

# 示例
SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;

# 示例
SELECT country, name FROM Websites WHERE country='CN' UNION ALL SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country;

# union all 的用法
create table tt select * from student union all select * from person;

# union all代表不检查重复全部读取，不写all代表检查重复数据，如果数据中有相同数据则只录入一次。
create table ttt select * from student union select * from person;

4.join连接查询，返回符合条件的数据

Join 分为：

内连接(inner join) inner join可以省略
外连接(outer join)

其中外连接分为：

左外连接(left outer join)
右外连接(right outer join)
全外连接(full outer join)

内连接用于返回满足连接条件的记录；而外连接则是内连接的扩展，它不仅会满足连接条件的记录，而且还会返回不满足连接条件的记录。

5.SQL里设置变量和使用变量

SET a="student";

6.创建存储过程

7. MyBatis里写SQL语句

要注意字符串的拼接和转义

select * from username where 用户名 like '段_%'    -- 会查出来段煜 段玉

select * from username where 用户名 like '段\_%' escape '\'   -- 通过 \转义,只能查出来 段_煜

变量的替换${arg0},#{arg1}
和前端HTTP的编解码

8.SQL通配符

%	替代 0 个或多个字符
_	替代一个字符

9.SQL里的正则表达式

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

# 下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站：
SELECT * FROM Websites WHERE name REGEXP '^[GFs]';

# 下面的 SQL 语句选取 name 以 A 到 H 字母开头的网站：
SELECT * FROM Websites WHERE name REGEXP '^[A-H]';

# 下面的 SQL 语句选取 name 不以 A 到 H 字母开头的网站：
SELECT * FROM Websites WHERE name REGEXP '^[^A-H]';

10.SQL里的in操作符

# 下面的 SQL 语句选取 name 为 "Google" 或 "菜鸟教程" 的所有网站：
SELECT * FROM Websites WHERE name IN ('Google','菜鸟教程');

11.SQL起别名

SELECT w.name, w.url, a.count, a.date FROM Websites AS w, access_log AS a 
WHERE a.site_id=w.id and w.name="菜鸟教程";

在下面的情况下，使用别名很有用：

在查询中涉及超过一个表
在查询中使用了函数
列名称很长或者可读性差
需要把两个列或者多个列结合在一起

12.复制表数据

# 从一个表复制数据，插入到另一个新表中
CREATE TABLE 新表 AS SELECT * FROM 旧表 

# 从一个表中复制所有的列插入到另一个已存在的表中：
INSERT INTO table2 SELECT * FROM table1;

# 只复制部分列插入到另一个已存在的表中：
INSERT INTO 表名1 (列名1，列名2...) SELECT 列名1，列名2... FROM 表名2;

13.SQL约束

SQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

在 SQL 中，我们有如下约束：

NOT NULL - 指示某列不能存储 NULL 值。这意味着如果不向字段添加值，就无法插入新记录或者更新记录。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

14.关于Foreign Key

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

FOREIGN KEY 约束用于预防破坏表之间连接的行为。实现了一种参照完整性

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

15.关于Check约束

下面的SQL在"Persons" 表创建时在 “P_Id” 列上创建 CHECK 约束。CHECK 约束规定 “P_Id” 列必须只包含大于 0 的整数。

# 创建表时创建 CHECK 约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

# 命名 CHECK 约束，并定义多个列的 CHECK 约束
CREATE TABLE Persons
(
P_Id int NOT NULL,
Name varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

# 修改表的 CHECK 约束
ALTER TABLE Persons ADD CHECK (P_Id>0)

# 命名 CHECK 约束，并定义多个列的 CHECK 约束
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

# 撤销 CHECK 约束
ALTER TABLE Persons DROP CHECK chk_Person

16.关于DEFAULT 约束

# 通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值
CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT GETDATE()
)

# 修改 DEFAULT 约束
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'

ALTER TABLE Persons ALTER City DROP DEFAULT

17.数据库的索引 index

索引可以在不读取整个表的情况下，使数据库应用程序更快地查找数据。快表的命中，离不开指针和数据结构

一、索引的数据结构

# 任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。所有的根节点存放分区和指针，只有叶子节点才存放真实的数据，需要磁盘去IO,而根节点信息全部在内存中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8LHCdsCd-1644405975208)(笔记图片/1184802-20170912211249219-1576835998.png)]

# b+树的查找过程
# 如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。内存的大小对于数据库底层算法是有影响的，内存小的话，这个B+树就会很高，查询就会变慢

# b+树性质

# 1.索引字段要尽量的少占bit，即尽量的小：当然，一次索引的多个字段数量越少越好
#	比如索引一个 varchar(20) 比索引一个 varchar(50)的树要好得多，因为更矮，IO次数少
# 	因为通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

# 2.索引的最左匹配特性（即从左往右匹配）：使用索引的时候也有讲究
#	当b+树的数据项是复合的数据结构，比如一次索引了多个字段(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据进来的时候，b+树就不知道下一步该查哪个节点了，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

二、MySQL的索引分类

索引分类
1.普通索引index :加速查找
2.唯一索引
    主键索引：primary key ：加速查找+约束（不为空且唯一）
    唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
    -primary key(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用

三、加上索引

#1. 一定是为搜索条件的字段创建索引，比如select * from t1 where age > 5;就需要为age加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，插入删除更新都很慢，只有查询快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yFXkwuk0-1644405975210)(笔记图片/1184802-20170912214329375-1453229056.png)]

四、正确使用索引

一、覆盖索引
select * from s1 where id=123;
该sql命中了索引，但未覆盖索引。
利用id=123到索引的数据结构中定位到该id在硬盘中的位置，或者说再数据表中的位置。
但是我们select的字段为*，除了id以外还需要其他字段，这就意味着，我们通过索引结构取到id还不够，
还需要利用该id再去找到该id所在行的其他字段值，这是需要时间的，很明显，如果我们只select id，
就减去了这份苦恼，如下
select id from s1 where id=123;
这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了id在硬盘的地址，这样的速度就很快

二、联合索引（组合索引）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oZ4DIhK9-1644405975212)(笔记图片/1184802-20170912214652782-1000358771.png)]

三、索引合并
#索引合并：把多个单列索引用 and 合并使用

#分析：
组合索引能做到的事情，我们都可以用索引合并做，比如
create index ne on s1(name,email);#组合索引
我们完全可以单独为name和email创建索引

组合索引可以命中：
select * from s1 where name='egon' ;
select * from s1 where name='egon' and email='adf';

索引合并可以命中：
select * from s1 where name='egon' ;
select * from s1 where email='adf';
select * from s1 where name='egon' and email='adf';

乍一看好像索引合并更好了：可以命中更多的情况，但其实要分情况去看，如果是name='egon' and email='adf',那么组合索引的效率要高于索引合并，如果是单条件查，那么还是用索引合并比较合理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uYRrQ0SQ-1644405975213)(笔记图片/1184802-20170912215426922-1260754280.png)]

五、添加索引时，必须遵循以下原则:

#1.最左前缀匹配原则，非常重要的原则，
create index ix_name_email on s1(name,email,)
- 最左前缀匹配：必须按照从左到右的顺序匹配
select * from s1 where name='egon'; #可以
select * from s1 where name='egon' and email='asdf'; #可以
select * from s1 where email='alex@oldboy.com'; #不可以

#	因为mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就会停止匹配，
#	比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

#2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

#3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，
表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、
性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，
这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

#4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’
就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，
但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。
所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

注意：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

参考资料

# 在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name ON 表名 (列名)

# 在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值
CREATE UNIQUE INDEX index_name ON 表名 (列名)

# 创建不止一个列的索引
CREATE INDEX PIndex ON Persons (LastName, FirstName)

# 查看都建立了哪些索引
SHOW KEYS FROM XXX;

# 关闭索引
ALTER TABLE `test` DISABLE KEYS ;

# 开启索引
ALTER TABLE `test` ENABLE KEYS;

18.关于删除

# 仅删除表内的数据，但并不删除表本身
TRUNCATE TABLE 表名

# 1.drop的操作对象可以是数据库，也可以是数据库中的数据表，或者是视图表，整个给删掉
# 2.delete的操作对象只能是数据库中的数据表,而且只是删除内容

# 别乱删除东西

# 不小心drop数据库了，如果binlog日志还在的话，也是可以恢复的，不过要找到还原点，时间很长会很麻烦
# 但是，如果在文件夹下使用Linux命令 sudo rm/rf 的话，而且生产环境没设置回收站的话，就没了
# 因此需要一个脚本每日全量备份数据库

19.关于自增

# 让 AUTO_INCREMENT 序列以其他的值起始
ALTER TABLE Persons AUTO_INCREMENT=100

# 查看数据表的自增长信息
SHOW VARIABLES LIKE 'auto_inc%';

# 修改步长
SET @@auto_increment_increment=10;

20.SQL内置的函数

SQL 拥有很多可用于计数和计算的内建函数。

SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

有用的 Aggregate 函数：

AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

有用的 Scalar 函数：

UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式

21.MySQL 处理重复数据

有用的 Aggregate 函数：

AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

有用的 Scalar 函数：

UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式

码农公寓

1.创建视图

2.多个表联合查询

3.union all 的用法

4.join连接查询，返回符合条件的数据

5.SQL里设置变量和使用变量

6.创建存储过程

7. MyBatis里写SQL语句

8.SQL通配符

9.SQL里的正则表达式

10.SQL里的in操作符

11.SQL起别名

12.复制表数据

13.SQL约束

14.关于Foreign Key

15.关于Check约束

16.关于DEFAULT 约束

17.数据库的索引 index

18.关于删除

19.关于自增

20.SQL内置的函数

21.MySQL 处理重复数据

21.MySQL 处理重复数据

相关文章