MySQL核心SQL

约定不等于承诺〃 2024-02-05 23:50 31阅读 0赞

## 一.结构化查询语言 ##

SQL是结构化查询语言（Structure Query Language），它是关系型数据库的通用语言。

SQL 主要可以划分为以下  3  个类别：

*  DDL（Data Definition Languages）语句   
    数据定义语言，这些语句定义了不同的数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 **create****、****drop****、****alter**等。 
 *  DML（Data Manipulation Language）语句   
    数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字,主要包括 **insert****、****delete****、****update** **和****select** 等。 
 *  DCL（Data Control Language）语句   
    数据控制语句，用于控制不同的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户 的访问权限和安全级别。主要的语句关键字包括 **grant****、****revoke** 等。

## 二.库操作 ##

查询数据库

> show databases;

创建数据库

> create database db01;

删除数据库

> drop database db01;

选择数据库

> use db01;

## 三.表操作 ##

查看表

> show tables;

创建表

> create table user(  
> id int primary key auto\_increment comment '主键',  
> nickname varchar(20) not null comment '昵称',  
> age int unsigned not null default 18 comment '年龄',  
> sex enum('男','女') default '男' comment '性别'  
> )engine=innodb default charset=utf8;

修改表名

> alter table user rename to user1;

插入字段

> alter table user add password varchar(20);

删除字段

> alter table user drop password;

改变字段的类型

> alter table user modify password varchar(30);

查看表结构

> desc user

查看建表 sql

> show create table user;
> 
> 或者
> 
> show create table user\\G

删除表

> drop table user;

## 四.CRUD操作 ##

### 1.**insert****增加** ###

> INSERT INTO USER(nickname,age,sex) VALUES('张三',19,'男'),('李四',20,'女');

> INSERT INTO USER(nickname,age,sex) VALUES('王五',26,'男');

这两条语句的区别:一条SQL语句执行一次三次握手和四次挥手

多条语句执行多次

![bda4960dd9274dd582db41949e38fc8a.png][]

### **2.update****修改** ###

> UPDATE USER SET age=age+1;
> 
> UPDATE USER SET age=age+1 where id=1;

### **3.delete****删除** ###

> delete from user where id=1;
> 
> delete from user;
> 
> delete from user where age between 1 and 2;

### **4.select****查询**  ###

#### **1.简单select查询** ####

> select \* from user;
> 
> select id,nickname,age,sex from user;
> 
> select id,nickname,age,sex from user where sex='男';

#### **2.去重****distinct** ####

> select distinct age from user;

#### **3.空值查询** ####

> select \* from user where nickname is null;

#### **4.****union****合并查询** ####

SELECT expression1, expression2, ... expression\_n

FROM tables\[WHERE conditions\]

UNION \[ALL | DISTINCT\] \#  注意： union 默认去重，不用修饰 distinct ， all 表示显示所有重复值

SELECT expression1, expression2, ... expression\_n

FROM tables\[WHERE conditions\];

> select \* from user where age>=20 union all select \* from user where sex='男';
> 
> select \* from user where age>=20 union select \* from user where sex='男';

#### **5.带****in****子查询** ####

> select \* from user where age in(20,21);

#### **6.分页查询** ####

> select \* from user limit 3;
> 
> select \* from user limit 1,3;
> 
> select \* from user limit 3 offset 1;
> 
> select \* from user where age>=20 limit 2 offset 1;

我们都知道有索引字段的情况下查询的条数都是一条,但是没有使用会发生什么情况,使用limit会不会提高查询的效率呢?

可以使用explain查询select查询的条数

> EXPLAIN SELECT \* FROM USER WHERE age>=20 LIMIT 1;

![53048eba7f4743a28174e13536d1e153.png][] 可以看到还是要进行全表扫描的,但是实际执行过程中扫描到第一条符合条件的数据的时候就停止扫描了,在实际的环境中(对于大量的数据),使用limit查询的速度比不适用快很多

向t\_user表中插入2000000条数据的执行

delimiter $
 Create Procedure add_t_user (IN n INT)
 BEGIN
 DECLARE i INT;
 SET i=0;
 WHILE i<n DO
 INSERT INTO t_user VALUES(NULL,CONCAT(i+1,'@fixbug.com'),i+1);
 SET i=i+1;
 END WHILE;
 END$
 delimiter ;
 call add_t_user(2000000);

此时我们可以进行观察,速度明显是快很多的,自己可以尝试

因此当我们知道某个数据是唯一(或者需要查询执行数量的数据)时,并且字段没有建立索引,此时我们使用limit可以明显提高查询的效率.

实际生产项目中分页查询pagenum,pageno

> select \* from user limit (pageno-1)\*pagenum,pagenum;

这种可以进行查询,但是效率很低,因为他首先需要从0->offset条数据,再将之后的数据取出来,0->offset条数据的时间

优化后的sql语句(id为主键,具体表的主键为准),因为主键建立了索引,我们只需要花常量的时间就可以定位到需要查询的位置

> select \* from user where id>(上一页最后一条数据的id) limit pagenum;

#### **7.排序****order by** ####

> select \* from user order by age;(默认升序ASC)
> 
> select \* from user order by age DESC;
> 
> select \* from user order by age,nickname;

EXPLAIN SELECT \* FROM USER ORDER BY nickname;

使用的是外排序

![4bf2c10061d54d619933a4245da54b46.png][] EXPLAIN SELECT id,nickname FROM USER ORDER BY nickname;

使用的是索引

![7c8db8fec06345e696234b35f16c3c48.png][]是否使用的是索引与排序的字段是否添加索引和查询的字段是否有索引有关

#### **8.分组****group by**  ####

> select age,count(age) as num from user group by age;
> 
> select age from user group by age having age>20;  
> select age,sex from user group by age,sex;

explain select age from user group by age;

![a2fb8c8765c54af687cfc18a848f749a.png][]

查询出来的数据其实是经过排序的,因此会出现filesort,因此group by之后的字段加索引是十分必要的

#### 9.笔试实践题 ####

下表 bank\_bill 是某银行代缴话费的主流水表结构：

<table style="width:500px;"> 
 <tbody> 
 <tr> 
 <td> 
 <div> 
 字段名 
 </div> </td> 
 <td>描述</td> 
 </tr> 
 <tr> 
 <td>serno</td> 
 <td>流水号 </td> 
 </tr> 
 <tr> 
 <td>date</td> 
 <td>交易日期 </td> 
 </tr> 
 <tr> 
 <td>accno</td> 
 <td>账号 </td> 
 </tr> 
 <tr> 
 <td>name </td> 
 <td>姓名</td> 
 </tr> 
 <tr> 
 <td>amount</td> 
 <td>金额</td> 
 </tr> 
 <tr> 
 <td>brno</td> 
 <td>缴费网点 </td> 
 </tr> 
 </tbody> 
</table>

1 、统计表中缴费的总笔数和总金额

> select count( serno),sum( amount) from bank\_bill;

2 、给出一个 sql ，按网点和日期统计每个网点每天的营业额，并按照营业额进行倒序排序

> select brno,date,sum(amount) as sum\_account from bank\_bill group by brno,date order by sum\_account DESC;

### 5.连接查询 ###

连接查询主要分为以下的几个:

![1cd824a8876f4ec18c1beb016d92c5b3.png][]

先来创建三个表

> create table student(  
> uid int primary key auto\_increment,  
> name varchar(20) not null,  
> age int not null,  
> sex enum('男','女') default '男' not null  
> );
> 
> create table course(  
> cid int primary key auto\_increment,  
> cname varchar(20) not null,  
> credit int not null  
> );
> 
> create table exame(  
> uid int not null,  
> cid int not null,  
> time date not null,  
> score float not null,  
> primary key(uid,cid)  
> );

插入一些数据:

> insert into student(name,age,sex)  
> values('zhangsan',18,'男'),('gaoyang',20,'女'),('chenwei',22,'男') ,('linfeng',21,'女'),('liuxiang',19,'女');  
> insert into course(cname, credit)  
> values('c++基础课程',5),('c++高级课程',10),('c++项目开发',8),('c++算法课程',12);  
> insert into exame(uid,cid,time,score)  
> values(1,2,'2021-04-10',80.0),(2,2,'2021-04-10',90.0),  
> (2,3,'2021-04-12',85.0),(3,1,'2021-04-09',56.0) ,  
> (3,2,'2021-04-10',93.0),(3,3,'2021-04-12',89.0),(3,4,'2021-04-11',100.0),  
> (4,4,'2021-04-11',99.0),(5,2,'2021-04-10',59.0),  
> (5,3,'2021-04-12',94.0),(5,4,'2021-04-11',95.0);

#### 1.内连接查询 ####

> select t1.uid,t1.name,t1.age,t1.sex,t2.\`score\` from student t1 join exame t2 on t1.\`uid\`=t2.\`uid\`;

![2721f77310ac41d8b481d03f2759f1fa.png][]

**重点:on a.uid=c.uid区分大表和小表，按照数据量来区分，小表永远是整表扫描，然后去大表搜索从student小表中取出所有的a.uid，然后拿着这些uid去exame大表中搜索**

**对于inner join内连接，过滤条件写在where的后面和on连接条件里面，效果是一样的**

> select t1.uid,t1.name,t1.age,t1.sex,t2.\`score\`,t3.\`cid\`,t3.\`cname\`,t3.\`credit\` from student t1 join exame t2 on t1.\`uid\`=t2.\`uid\`  
> join course t3 on t3.\`cid\`=t2.\`cid\`;

![1ccbde12e6374fa7a8d455c46960168d.png][]

> select b.cid,b.cname,b.credit,count(\*) cnt  
> from exame c  
> inner join course b on c.cid=b.cid  
> where c.score>=90.0  
> group by c.cid  
> order by cnt;

![bdbd4237654f4cf2ba9111ed9105d38c.png][]

**内连接应用场景**,前面t\_user表可以通过id直接定位分页查询的位置,是因为加了索引,如果我们直接查询id,因为id加了索引,也可以减少查询的时间,但是我们需要的是查询全部的信息,怎么通过内连接可以减少查询的时间呢?

> select id from t\_user limit 100000,10

下面给出解决方案

> select a.id,a.email,a.password from t\_user a join (select id from t\_user limit 100000,10) b
> 
> on a.id=b.id;

通过产生的id临时表,可以直接定位到查询的位置,也是因为id加了索引.

#### 2.外连接查询 ####

学生表中插入一条新的数据

> insert into student(name,age,sex) values('weiwei',32,'男');

##### 1.左连接查询 #####

> select a.\*,b.\* from student a left join exame b on a.\`uid\`=b.\`uid\`;

![b6a88770fae947e28601a4a4eae46dbb.png][]把left这边的表所有的数据显示出来，在右表中不存在相应数据，则显示NULL

使用explain查看可知是先查左表

![a5df804a2bb3405c974ec9b82e162276.png][]

##### 2.右连接查询 #####

> select a.\*,b.\* from student a right join exame b on a.\`uid\`=b.\`uid\`;

把 right 这边的表所有的数据显示出来，在左表中不存在相应数据，则显示 NULL

使用explain查看可知是先查右表

![3ab849ea364c4fa2b209c5c1e743c357.png][]

不采用带in子查询的原因: 会产生一张中间表存储结果供外面的sql来查询,not in对于索引的命中并不高

再来分析一组例子

> select a.\*,b.\* from student a left join exame b on a.\`uid\`=b.\`uid\` where b.\`cid\`=2;
> 
> select a.\*,b.\* from student a join exame b on a.\`uid\`=b.\`uid\` where b.\`cid\`=2;

此时上面的两条sql语句一个是内连接一个是外连接,两者按理来说应该是不一样的,但是实际显示的结果都是一致的

![d18e59ec72fb48cebf9a257cad04bd87.png][]

使用explain查看

![3fd7c13ad89643e1b1c915e77591726a.png][]

可以看到都是先全表查询右表,然后再查询左表,这样与我们预期中的左连接查询结果是不一样的了,此时我们应该

> select a.\*,b.\* from student a left join exame b on a.\`uid\`=b.\`uid\` and b.\`cid\`=2;

我们把查询的条件写在on的后面,此时查询的结果是我们想的左连接查询所预期的

![2eaed11488be42faaf6b2f5006d4967a.png][]

使用explain查看也可以看到是先查询左表的.

![e646e92f893345ceaae17afbc9bb8d61.png][]

**外连接的连接条件不能像内连接一样写在on和where都行,如果想要产生符合预期的答案,应该要写在on后面**

[bda4960dd9274dd582db41949e38fc8a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/692c63f4f8984007a655da0a1883efd8.png
[53048eba7f4743a28174e13536d1e153.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/139c7ed6f6bb461dba9f25545d7f754f.png
[4bf2c10061d54d619933a4245da54b46.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/e3a2d6b1da7e40d0810917fffa1321b3.png
[7c8db8fec06345e696234b35f16c3c48.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/8ee620c4c5cb42a6ada0ef2b8795bea4.png
[a2fb8c8765c54af687cfc18a848f749a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/feebaea0613f41eeba516aaac5e91772.png
[1cd824a8876f4ec18c1beb016d92c5b3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/ceccbe87880848e1940c2c05252b5695.png
[2721f77310ac41d8b481d03f2759f1fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/101a9f24e0b3470cb15fc0a54b094421.png
[1ccbde12e6374fa7a8d455c46960168d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c5a63b59dd7c4288b2249304aa6e4ac4.png
[bdbd4237654f4cf2ba9111ed9105d38c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/414593aef98e40899227b01f5f5482f1.png
[b6a88770fae947e28601a4a4eae46dbb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/ee95104a0c2b4429b17604c98a67664e.png
[a5df804a2bb3405c974ec9b82e162276.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/61774f8bf05d49d9be1f0f844871e79a.png
[3ab849ea364c4fa2b209c5c1e743c357.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/46fde5bf46ed47db9ab930f4b0127c3b.png
[d18e59ec72fb48cebf9a257cad04bd87.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/e9e1748a58004a41a597c1287d3f99f4.png
[3fd7c13ad89643e1b1c915e77591726a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/ec0a8a5bf30f4db79e74c6f07ed82c05.png
[2eaed11488be42faaf6b2f5006d4967a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c8faae6d443e4a3597bcffabed551349.png
[e646e92f893345ceaae17afbc9bb8d61.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/64efc0ac266f481789719467e7bbc1a7.png