439-MySQL(库操作,表操作,CRUD操作)

向右看齐 2022-09-05 12:59 246阅读 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** 等。

**管理方式：**  
![在这里插入图片描述][bf065dd38c5a499f84a9a150357cfb57.png]

## 库操作 ##

**查询数据库**

show databases;

**创建数据库**

create database ChatDB;

**删除数据库**

drop database ChatDB;

**选择数据库**

use ChatDB;

**演示示例：**  
![在这里插入图片描述][80cce822905448bdbbef8a5215c0b490.png]  
**前面4个是默认的库**  
**先选择数据库，再执行对这个库的操作。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]  
**创建数据库**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]  
**删除数据库（表，表里的数据都没了）**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]

## 表操作 ##

**查看表**

show tables;

**创建表**

create table user(id int unsigned primary key not null auto_increment, 
    name varchar(50) not null, 
    age tinyint not null, 
    sex enum('M','W') not null)engine=INNODB default charset=utf8;

**存储引擎，字符编码。**

**查看表结构**

desc user;

**查看建表sql**

show create table user\G

**删除表**

drop table user;

**演示示例：**

**我们先创建一个school库，进行演示。**  
![在这里插入图片描述][f1447df29b124639bd7b7792052c6ff7.png]  
**查看表为空**  
![在这里插入图片描述][7c4a9e08623c46529245b595046ed574.png]  
**创建1个user表**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]

**可以进行配置修改，就不用加上engine=INNODB default charset=utf8了。**  
![在这里插入图片描述][a43173f2911f47fba2a92c2e7f2f3133.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]  
**查看创建的表的结构**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]  
**删除表格user（连同表格都删了）**  
![在这里插入图片描述][0c4b47a7d4a347889565e5257c0e8c56.png]  
**我们重新创建user表格进行演示**  
**显示创建表的SQL语句**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]  
**我们的表在磁盘上存储有3个东西：1个是表的结构，1个是表的数据，1个是表创建的索引。**

## CRUD操作 ##

## insert增加 ##

insert into user(nickname, name, age, sex) values('fixbug', 'zhang san', 22, 'M'); 
    insert into user(nickname, name, age, sex) values('666', 'li si', 21, 'W'), ('888', 'gao yang', 20, 'M');

**如果书写插入的语句没有写字段名，values的参数就要按表的字段的顺序书写。建议书写插入的语句中书写字段名。**  
![在这里插入图片描述][0c3247d060f942e68cdb37d8b083943a.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]

**这两种写法对插入表的结果是一样的。两者有什么区别吗？**

**1条SQL语句的执行都会经过这样的一个阶段。首先，client和server进行3次握手，握手成功，建立通信链路，然后交互完成，释放通信链路。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]  
**第二步，client发送SQL到server上，接收并返回处理结果，最后server和client断开连接，tcp四次挥手。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]  
**如果我们是执行第一种方法，分别发送5句插入语句，就是做了5次上面的操作。**  
**如果我们是执行第二种方法，只发送1条SQL，就做了1次上面的操作（省了tcp协商和交互的次数，就是减少了tcp的握手和挥手）。**

## update修改 ##

update user set age=23 where name='zhang san'; 
    update user set age=age+1 where id=3;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]

## delete删除 ##

delete from user where age=23; 
    delete from user where age between 20 and 22; 
    delete from user;//表中数据全部删除

**删除 zhangsan**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]  
**如果我们再插入zhangsan，那么它的id是补上原来的1还是6呢？**  
![在这里插入图片描述][0895f4d628494e24b57ae80af9d411d4.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]  
**是6。所以说，id的自增减开弓没有回头箭，一直往后跑，复用不了，总有上限，达到上限后，再插入，就插入失败，id是主键，不可能有相同的id，我们定义成BIGINT，就是8个字节，能表示的范围就非常非常大了。**

**删除所有**  
![d984d2bbd0bf4b92abee5425e07b3262.png][]

## select查询 ##

select * from user; 
    select id,nickname,name,age,sex from user; 
    select id,name from user; 
    select id,nickname,name,age,sex from user where sex='M' and age>=20 and age<=25;
    select id,nickname,name,age,sex from user where sex='M' and age between 20 and 25; 
    select id,nickname,name,age,sex from user where sex='W' or age>=22;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]  
\*是通配符。但是后面表的结构一改，选择出来的数据可能和预期的不一样。  
**建议加上字段名。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]  
**回表**：SQL语句和索引相关的，和性能息息相关。看索引怎么设计，不回表最好。select后面的列名我们是要负责的。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]  
**逻辑与是条件都要满足，逻辑或是满足其中一个条件。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]  
![在这里插入图片描述][54e91ab3f4a147feb98ab8ef88bcae5c.png]  
**between and 是包含了起始，也包含了末尾**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 18]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 19]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 20]

## 去重distinct ##

**去掉重复的，只显示1个**

select distinct name from user;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 21]

## 空值查询 ##

is \[not\] null

select * from user where name is null;

## 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 country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 22]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 23]  
**相当于就是把两个select的结果合并起来**

## 带in子查询 ##

\[NOT\] IN(元素1，元素2，…，元素3)

select * from user where id in(10, 20, 30, 40, 50) 
    select * from user where id not in(10, 20, 30, 40, 50) 
    select * from user where id in(select stu_id from grade where average>=60.0)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 24]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 25]

## 补充 ##

：**逻辑或or是不会用到索引的，这是错误的理解**。单单看逻辑或，是不会用到索引，但是MySQL server对SQL是有优化的，or就是要么满足这个条件要么满足那个条件，MySQL server在合适的条件下会把这个逻辑或or转化成合并查询！这样的SQL没有出现or，就是完全可以用到索引了。in或者not in其实都会用到索引，针对MySQLserver 的优化。

[bf065dd38c5a499f84a9a150357cfb57.png]: /images/20220829/cd24260165be418b800090747335c0c4.png
[80cce822905448bdbbef8a5215c0b490.png]: /images/20220829/23c862a8045449bf8912ae9c19c79806.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/7d11c8f2de2a438cb6fba57776307720.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/dac7d8f036a740dc976a1e035bdcc05e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/a555367d0e3345759be61bd54dc6c4e1.png
[f1447df29b124639bd7b7792052c6ff7.png]: /images/20220829/4200ce7cf9f34960869d720a5ce57d63.png
[7c4a9e08623c46529245b595046ed574.png]: /images/20220829/80528a49728240cea5acd96193cb3c62.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/75a2a2380807416baa26e059a9fc9ed9.png
[a43173f2911f47fba2a92c2e7f2f3133.png]: /images/20220829/4d1a1861164140edb254fa894c86cb49.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]: /images/20220829/e3caf55a5a844a079264c3883eb574b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]: /images/20220829/24d9e198baba4ebb818517bb88aa0867.png
[0c4b47a7d4a347889565e5257c0e8c56.png]: /images/20220829/d823e1ae80a946d18be8f3b52f3d65dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]: /images/20220829/bdfef045ac3e49e78e31a59cd4c2ed39.png
[0c3247d060f942e68cdb37d8b083943a.png]: /images/20220829/3e8a9805950942febbc3e42e7b8dc1d5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]: /images/20220829/ab1649a99e434caa87103360ec253669.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]: /images/20220829/268034f7986f4ff7a7317d4c5b6eed5a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]: /images/20220829/7a3d551bc93940bca6260b4b1fd2b5fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]: /images/20220829/a9330ba2e73846358e27c919c443c5d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]: /images/20220829/cd5f8b01f8144a3cbe35c73b6e051352.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]: /images/20220829/7684f24ad06c4918821c6c3bbc5d187b.png
[0895f4d628494e24b57ae80af9d411d4.png]: /images/20220829/33572ebd5b6e41308f9a59eef84a12d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 13]: /images/20220829/3d260e27556341128b033e0f43924003.png
[d984d2bbd0bf4b92abee5425e07b3262.png]: /images/20220829/973f3ec1599e4e908b1f70b91b5243a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 14]: /images/20220829/cee02d52ca5248c09ca02c88cd3cbdbe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 15]: /images/20220829/c65f2be4de8c499fac93ebabe69bb259.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 16]: /images/20220829/5b6731285f004da1b6454dfe0b938836.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 17]: /images/20220829/40a40a0903004eaf81210ec8c1183f25.png
[54e91ab3f4a147feb98ab8ef88bcae5c.png]: /images/20220829/77daf0b426064545b53be6720f5c52c9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 18]: /images/20220829/aa3bc408730c49b7b71668058488621d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 19]: /images/20220829/23c6bbad4ac342fd9c0def6ce7a17175.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 20]: /images/20220829/7581469a6748464b9e9a814bbe37225c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 21]: /images/20220829/56c62f6bd4e94d779ba9964b31514ef7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 22]: /images/20220829/9a774df04aa043aa95baa87733420673.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 23]: /images/20220829/6ee4c46f2e8f41448f19e6ec4e0170fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 24]: /images/20220829/1946982e440949aaa5fc5a4638835b42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 25]: /images/20220829/b0c459f453f64c16999886749ba8585f.png