[一起来学mysql]-----MySQl的增删改查

最新推荐文章于 2024-09-08 11:26:17 发布
最新推荐文章于 2024-09-08 11:26:17 发布
阅读量953 收藏
点赞数
分类专栏： Mysql 文章标签： mysql 增删改查
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_51574797/article/details/123494701
版权
本文详细介绍了MySQL的增删改查(CURD)操作，包括数据插入、多行插入、部分数据插入、时间插入，以及查询、排序、筛选、分页、更新和删除等技巧。涵盖了表设计、约束和数据库完整性概念，以及复杂的表间查询和自连接实例。
摘要由CSDN通过智能技术生成

[一起来学mysql]-----MySQl的增删改查

文章目录

CURD

C——create——新增

R——Retrieve——查询

U——updata——修改

D——delete——删除

对于现在，我们有了表了，那么就需要在表中加入数据。

对数据进行增加，修改数据，查询数据，删除数据

create数据

插入一行数据

在表中添加数据：

insert into 表名 values(参数1,参数2,参数3,...);
  1. into可以被省略
  2. 参数1,2,3的类型和个数必须和创建表的时候的字段相同

插入多行数据

insert into 表名 values(值1,值2,..),(值1,值2),...;

mysql支持一次插入多条数据.

在values的后面插入多个括号,每一条括号代表一行,括号之间使用逗号连接

mysql一次插入多条数据的效率比多次插入一条数据高,因为MySQL服务器对每条语句都会进行解析,执行,响应.所以,插入多条数据的效率会比较低.

插入部分数据

如果只是想要加入其中的某些数据,而不是全部数据,可以这样:

insert into 表名 (列名1,列名2) values(参数1,参数2);

在表名的后面指定了几个列名,对应的参数的个数就应该是多少个

mysql> insert into goods(goods_id,goods_name) values(1,'qq');
Query OK, 1 row affected (0.01 sec)

mysql> select * from goods;
+----------+------------+-----------+----------+----------+
| goods_id | goods_name | unitprice | category | provider |
+----------+------------+-----------+----------+----------+
|        1 | vegetable  |      12.5 | food     | local    |
|        2 | drink      |      11.0 | dd       | xx       |
|        1 | qq         |      NULL | NULL     | NULL     |
+----------+------------+-----------+----------+----------+

未指定值的地方就是NULL

插入时间

1.省略部分

只输入部分的时间年份,没有输入的部分就被默认成0

2.自己全部输入

就是显示自己输入的内容

3.使用now函数

mysql> insert into t values(1,'2022-3-6');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(2,'2022-3-6 23:08:21');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(3,now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+---------------------+
| id   | time1               |
+------+---------------------+
|    1 | 2022-03-06 00:00:00 |
|    2 | 2022-03-06 23:08:21 |
|    3 | 2022-03-06 23:09:18 |
+------+---------------------+

select查询数据

全列查询

下面这种sql语句就是select查询

select * from 表名;

其中的表名*代表的是全列,也就是全部列数.

所以这个*也叫做通配符

但是,千万不要小看这个select语句,它这个还是非常危险的.

当我们在客户端敲下一个select的时候,通过网络连接,将请求发送到服务器端

因为select是全列查询,所以服务器就会到硬盘中将所有的数据全部都读取出来.在真实的开发环境中,这个数据还是很大的,有可能有1T那么大,而硬盘的读取速度只有100M每秒.所以,硬盘的所有工作都会用来读取这个数据

并且将数据从硬盘中读取到内存中去的时候,会通过网卡来传输.网卡的传输也是非常的慢的.

所以select语句会非常的占用内存,导致其他的客户端向服务器发送请求的时候就会响应的非常慢或者相应不出来

并且我们打印出来的表仅仅是一个临时表,数据从硬盘中通过网卡到了内存中,在屏幕上打印完了之后,就会从内存中回收了

mysql> insert into foods values(1,'cola'),(2,'bread'),(3,'noodle');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from foods;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 吃的   |
|    1 | cola   |
|    2 | bread  |
|    3 | noodle |
+------+--------+
4 rows in set (0.00 sec)

指定列查询

就是将*替换成列名

select 列名,列名,列名... from 表名;

mysql> select id from foods;
+------+
| id   |
+------+
|    1 |
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.01 sec)

查询字段为表达式

可以将列名换位字段表达式

mysql> select id from foods;
+------+
| id   |
+------+
|    1 |
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.01 sec)

mysql> select id+10   from  foods;
+-------+
| id+10 |
+-------+
|    11 |
|    11 |
|    12 |
|    13 |
+-------+
4 rows in set (0.00 sec)

可以在指定列的时候使用一下表达式,这样就可以对表中的内容进行操作.

但是更改的只是表中的打印值,在内存中的值其实并没有改变

还可以列之间进行相加

mysql> select id ,chinese+math+english from exam_result;
+------+----------------------+
| id   | chinese+math+english |
+------+----------------------+
|    1 |                221.0 |
|    1 |                221.0 |
+------+----------------------+
2 rows in set (0.00 sec)

查询一个数字

如果直接在select的后面加上一个数字，那么会出现什么样的后果呢？

mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
|    6 | 孙权      |    70.0 | 73.0 |    78.5 |
|    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
+------+-----------+---------+------+---------+
7 rows in set (0.01 sec)

mysql> select 1 from exam_result;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+---+
7 rows in set (0.00 sec)

mysql> select 100 from exam_result;
+-----+
| 100 |
+-----+
| 100 |
| 100 |
| 100 |
| 100 |
| 100 |
| 100 |
| 100 |
+-----+
7 rows in set (0.00 sec)

我们看到出现了一个行数和原来表的行数相同的临时表,然后临时表中的每一个数字都是select后面的数字

将查询列名赋予别名

select 列名1,列名2 as 别名,... from 表名;

可以将某个列名赋予别名.用关键字as

mysql> select id ,chinese+math+english as total from exam_result;
+------+-------+
| id   | total |
+------+-------+
|    1 | 221.0 |
|    1 | 221.0 |
+------+-------+
2 rows in set (0.00 sec)

将查询的列中去重distinct

可以将要查询的列中相同值的数字只保留一个,达到去重的效果

select distinct 列名 from 表名;

使用distinct就可以达到去重的效果

mysql> select * from exam_result;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    1 | Luo  |    67.0 | 98.0 |    56.0 |
|    1 | x    |    67.0 | 98.0 |    56.0 |
+------+------+---------+------+---------+
mysql> select distinct chinese from exam_result;
+---------+
| chinese |
+---------+
|    67.0 |
+---------+

还可以同时对多个列名进行去重,但是必须是两个列的各自的数字都相同才可以

select distinct 列名1,列名2,列名3 from 表名;

mysql> select * from exam_result;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    1 | Luo  |    67.0 | 98.0 |    56.0 |
|    1 | x    |    67.0 | 98.0 |    56.0 |
+------+------+---------+------+---------+
mysql> select distinct chinese ,english,math from exam_result;
+---------+---------+------+
| chinese | english | math |
+---------+---------+------+
|    67.0 |    56.0 | 98.0 |
+---------+---------+------+
1 row in set (0.00 sec)

按照指定列进行排序order by

基本格式:

select 列名1 from 表名 order by 列名2 desc;

ase升序排序；desc降序排序

按照列名

mysql> select english from exam_result order by english desc;
+---------+
| english |
+---------+
|    90.0 |
|    78.5 |
|    77.0 |
|    67.0 |
|    56.0 |
|    56.0 |
|    56.0 |
|    45.0 |
|    30.0 |
+---------+
9 rows in set (0.00 sec)

当插入的内容是null的时候,升序的时候是最上面;降序的时候在最低下

mysql> insert into exam_result(id,name) values(9,'xixi');
Query OK, 1 row affected (0.01 sec)

mysql> select chinese+math+english as total from exam_result order by total;
+-------+
| total |
+-------+
|  NULL |
| 170.0 |
| 185.5 |
| 221.0 |
| 221.0 |
| 221.0 |
| 221.5 |
| 233.0 |
| 242.5 |
| 276.5 |
+-------+
10 rows in set (0.00 sec)

order by还可以排多个列的序，也就是第一个列相同的情况下，就会排第二个列的序

mysql> select * from exam_result order by chinese,math desc;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    8 | qj2  |    75.0 | 67.0 |    20.0 |
|    7 | qj   |    75.0 | 65.0 |    30.0 |

两个人的Chinese相同,但是8的math大,所以8在前

谁在前面就先按谁排

查找的时候进行某些条件选择where

写在前面:查询出来的只是以一个临时表,查找出来满足where语句的内容,然后进行打印,不代表内存中就只有这些数据

select * from 表名 where 条件;

找到英语小于60的

mysql> select * from exam_result where english<60;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    1 | Luo  |    67.0 | 98.0 |    56.0 |
|    1 | x    |    67.0 | 98.0 |    56.0 |
|    1 | qq   |    67.0 | 98.0 |    56.0 |
|    5 | qo   |    55.5 | 85.0 |    45.0 |
|    7 | qj   |    75.0 | 65.0 |    30.0 |
|    8 | qj2  |    75.0 | 67.0 |    20.0 |
+------+------+---------+------+---------+

where语句中不能使用别名

mysql> select id,name,chinese+math+english from exam_result where chinese+math+english>250;
+------+------+----------------------+
| id   | name | chinese+math+english |
+------+------+----------------------+
|    3 | qe   |                276.5 |
+------+------+----------------------+
1 row in set (0.00 sec)

mysql> select id,name,chinese+math+english as total from exam_result where total>250;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'

and的优先级大于or

mysql> select * from exam_result where english>70 or math>90 and chinese>80;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    2 | qw   |    87.5 | 78.0 |    77.0 |
|    3 | qe   |    88.0 | 98.5 |    90.0 |
|    6 | qi   |    70.0 | 73.0 |    78.5 |
+------+------+---------+------+---------+
3 rows in set (0.00 sec)

所以必要时可以加上括号来使用

between A and B-----[A,B]

找到属于A和B之间的数据,并且包括A和B是闭区间

mysql> select * from exam_result where chinese between 80 and 90;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    2 | qw   |    87.5 | 78.0 |    77.0 |
|    3 | qe   |    88.0 | 98.5 |    90.0 |
|    4 | qr   |    82.0 | 84.0 |    67.0 |
|   10 | qf   |    80.0 | 66.0 |    78.0 |
|   11 | qp   |    90.0 | 77.0 |    89.0 |
+------+------+---------+------+---------+
5 rows in set (0.00 sec)

也可以使用and达到上面的效果

in(A,B,C,D,…)

找到值为A,B,C,D的值

mysql> select * from exam_result where chinese in(80,90,88);
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    3 | qe   |    88.0 | 98.5 |    90.0 |
|   10 | qf   |    80.0 | 66.0 |    78.0 |
|   11 | qp   |    90.0 | 77.0 |    89.0 |
+------+------+---------+------+---------+
3 rows in set (0.01 sec)

当然了in也可以使用or来实现

模糊查询like

mysql> select * from exam_result where name like 'qj%';//所有以qj开头的字符
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    7 | qj   |    75.0 | 65.0 |    30.0 |
|    8 | qj2  |    75.0 | 67.0 |    20.0 |
+------+------+---------+------+---------+
2 rows in set (0.00 sec)

mysql> select * from exam_result where name like 'qj_';//字符是3位且以qj为开头的
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    8 | qj2  |    75.0 | 67.0 |    20.0 |
+------+------+---------+------+---------+
1 row in set (0.00 sec)

is null 和is not null

is null和<=>是相同的不是和=相同.

对于=来说,对null类型的不适配,<=>才适配

mysql> select * from exam_result where name is null;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|   14 | NULL |    NULL | NULL |    NULL |
+------+------+---------+------+---------+
1 row in set (0.00 sec)

mysql> select * from exam_result where name is not null;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    1 | Luo  |    67.0 | 98.0 |    56.0 |
|    1 | x    |    67.0 | 98.0 |    56.0 |
|    1 | qq   |    67.0 | 98.0 |    56.0 |
|    2 | qw   |    87.5 | 78.0 |    77.0 |
|    3 | qe   |    88.0 | 98.5 |    90.0 |
|    4 | qr   |    82.0 | 84.0 |    67.0 |
|    5 | qo   |    55.5 | 85.0 |    45.0 |
|    6 | qi   |    70.0 | 73.0 |    78.5 |
|    7 | qj   |    75.0 | 65.0 |    30.0 |
|    9 | xixi |    NULL | NULL |    NULL |
|    8 | qj2  |    75.0 | 67.0 |    20.0 |
|   10 | qf   |    80.0 | 66.0 |    78.0 |
|   11 | qp   |    90.0 | 77.0 |    89.0 |
+------+------+---------+------+---------+
13 rows in set (0.00 sec)

----------------------------------------------------------------------------------------------------
mysql> select * from exam_result where name <=>null;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|   14 | NULL |    NULL | NULL |    NULL |
+------+------+---------+------+---------+
1 row in set (0.00 sec)


mysql> select * from exam_result where name =null;
Empty set (0.00 sec)

分页查询limit

语法:

select * from exam_result limit 数字 offset 从哪里开始;

使用这个句子就可以从offset指定的下标开始,打印数字个元素

offset可以被省略

mysql> select * from exam_result limit 3;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    1 | Luo  |    67.0 | 98.0 |    56.0 |
|    1 | x    |    67.0 | 98.0 |    56.0 |
|    1 | qq   |    67.0 | 98.0 |    56.0 |
+------+------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from exam_result limit 3 offset 0;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    1 | Luo  |    67.0 | 98.0 |    56.0 |
|    1 | x    |    67.0 | 98.0 |    56.0 |
|    1 | qq   |    67.0 | 98.0 |    56.0 |
+------+------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from exam_result limit 3 offset 3;
+------+------+---------+------+---------+
| id   | name | chinese | math | english |
+------+------+---------+------+---------+
|    2 | qw   |    87.5 | 78.0 |    77.0 |
|    3 | qe   |    88.0 | 98.5 |    90.0 |
|    4 | qr   |    82.0 | 84.0 |    67.0 |
+------+------+---------+------+---------+
3 rows in set (0.00 sec)

limit还可以和order by一起使用

mysql> select id,name,math+chinese+english as total from exam_result order by total desc limit 4;
+------+------+-------+
| id   | name | total |
+------+------+-------+
|    3 | qe   | 276.5 |
|   11 | qp   | 256.0 |
|    2 | qw   | 242.5 |
|    4 | qr   | 233.0 |
+------+------+-------+
4 rows in set (0.01 sec)

update更改数据

update 表名 set 列名=值,列名=值,...where 条件

mysql> update exam_result set chinese=99, english=99 where chinese >=90 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

update最好要和where一起使用，限制修改的条件

和上面的更改不一样,update修改的数据是硬盘服务器上面的原始数据.是不可逆的

另外,where的条件限定也是非常重要的,他决定了有多少数据会被更改

还有一点,因为update是不可逆的,所以这一点也是非常的危险,要十分小心

如果想要修改一个列的所有的内容,就不加where就可以

mysql> update exam_result  set chinese=chinese/2;
Query OK, 12 rows affected, 2 warnings (0.01 sec)
Rows matched: 14  Changed: 12  Warnings: 2

delete删除数据

delete from 表名 where 条件;

mysql> delete from exam_result where name<=>null;
Query OK, 1 row affected (0.00 sec)

where的条件也是非常重要的，条件不对就会全部修改。

数据库的约束

在mysql中,在创建表的时候.可以对表的一些内容创建一个约束.

注意:这些约束都是对于一个列的约束,是在创建表的时候对列进行一些限制操作

下面是一些具体的约束:

not null(不能为空)

表示这一行的数据不可以是null

创建表的时候,在列名的后面加上限制就可以

mysql> create table student (id int not null,name varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |//id被设置为not null,Null就被设置为no了
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

如果插入了null,就会报警告

mysql> insert into student values(null,'zs');
ERROR 1048 (23000): Column 'id' cannot be null

unique(独一无二)

这个限定是表明这个列中不可以有相同的数据

mysql> create table student2(id int unique,name varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into student2 values(1,'qq');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student2 values(1,'qq');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'  -- 插入相同的id 1 就会报错

mysql> desc student2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

default(给予默认值)

这个就是给默认值一个值,正常条件下,默认值都是null,但是我们可以给定一个默认的一个值

mysql> create table student3(id int default 0,name varchar(30) default 'xx');--给予默认值
Query OK, 0 rows affected (0.02 sec)

mysql> desc student3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | 0       |       |
| name  | varchar(30) | YES  |     | xx      |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into student3 values();
Query OK, 1 row affected (0.01 sec)

mysql> select * from student3;
+------+------+
| id   | name |
+------+------+
|    0 | xx   |
+------+------+
1 row in set (0.00 sec)

primary key(自动计数)(not null+unique)

primary key只能作用于一列,用于对每一个行有一个标准去知道其标号是什么.作用还是很大的!

-- 对id进行primary key
mysql> create table student4 (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

-- 不可以输入null
mysql> insert into student4 values(null,'qq');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into student4 values(1,'qq');
Query OK, 1 row affected (0.01 sec)
-- 不可以加入重复的值
mysql> insert into student4 values(1,'qq');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> desc student4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

其实primary key真正的作用在于auto_increment------自增主键

顾名思义,设置了自增主键的列就可以不指定该列的值了,就可以插入null了

mysql> create table student6 (id int primary key auto_increment,name varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into student6(name) values('qq');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student6(name) values('gg');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student6;
+----+------+
| id | name |
+----+------+
|  1 | qq   |
|  2 | gg   |
+----+------+

使用auto_increment的时候也可以自己插入标记,但是其间的内容就不可以使用了

mysql> insert into student6 values(7,'gg');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student6(name) values('gg');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student6;
+----+------+
| id | name |
+----+------+
|  1 | qq   |
|  2 | gg   |
|  7 | gg   |
|  8 | gg   |-- 后来插入的顺寻就是从7开始的
+----+------+
4 rows in set (0.00 sec)

foreign key外键

这个外键是因为其中一个表A的其中一个列的值和另外一个表B的一个列有关。也就是A表中的某个列中的值必须都在是B表中某个列中的一个。它的范围就是在B中的。所以我们叫A为子表，B为父表

比如说下面的两个类，学生类和班级类

先创建班级类，创建3个班级

mysql> create table class(classid int primary key auto_increment,name varchar(20));-- 自增主键classid
Query OK, 0 rows affected (0.02 sec)

插入一些班级:

mysql> insert into class values(null,'尖刀班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into class values(null,'实验班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(null,'平行班');
Query OK, 1 row affected (0.01 sec)

mysql> select * from class;
+---------+-----------+
| classid | name      |
+---------+-----------+
|       1 | 尖刀班    |
|       2 | 实验班    |
|       3 | 平行班    |
+---------+-----------+
3 rows in set (0.00 sec)

在创建学生类:

mysql> create table student(id int primary key auto_increment,name varchar(20),class int ,foreign key(class) references class(classid));
Query OK, 0 rows affected (0.01 sec)

在班级那一列使用外键,父表是class表中的classid列

foreign key的格式:

foreign key(子表的某一列) references 父表名(父表的对应的列)

所以,在对学生表进行插入班级编号的时候,其插入的内容必须是父表中的classid列中的值,也就是只能插入1,2,3

mysql> insert into student values(null,'小米',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(null,'小明',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(null,'小李',3);
Query OK, 1 row affected (0.01 sec)

mysql> select* from student;
+----+--------+-------+
| id | name   | class |
+----+--------+-------+
|  1 | 小米   |     1 |
|  2 | 小明   |     1 |
|  3 | 小李   |     3 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> insert into student values(null,'小习',4);-- 插入4是不可以的,不属于外键的值
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class`) REFERENCES `class` (`classid`))

在插入这些class的时候,先会到父表的classid中查询是否存在,如果存在,就可以正常插入;如果不存在,就不可以插入,会报错

除了不可以插入外键以外的内容,还不可以更改为外键以外的内容

mysql> update student set class=9 where id=1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class`) REFERENCES `class` (`classid`))
mysql> update student set class=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select* from student;
+----+--------+-------+
| id | name   | class |
+----+--------+-------+
|  1 | 小米   |     2 |
|  2 | 小明   |     1 |
|  3 | 小李   |     3 |
+----+--------+-------+
3 rows in set (0.00 sec)

同时外键对主表也有限制作用

主表不可以用更改和删除被子表所使用的值

mysql> select*from student;
+----+--------+-------+
| id | name   | class |
+----+--------+-------+
|  1 | 小米   |     2 |
|  2 | 小明   |     2 |
|  3 | 小李   |     3 |
+----+--------+-------+
3 rows in set (0.00 sec)

子表只使用了2,3,没有使用1,所以就可以删除和更改没有被使用的,而被使用的却不可以

-- 删除1可以
mysql> delete from class where classid=1;
Query OK, 1 row affected (0.01 sec)

mysql> select* from class;
+---------+-----------+
| classid | name      |
+---------+-----------+
|       2 | 实验班    |
|       3 | 平行班    |
+---------+-----------+
2 rows in set (0.00 sec)
-- 删除2就不可以
mysql> delete from class where classid=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class`) REFERENCES `class` (`classid`))
mysql> update class set classid=5 where classid=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class`) REFERENCES `class` (`classid`))

所以,foreign key对主表和子表的影响都是很大的

如果在实际生活中,主表中的一些内容是不需要的了,不需要使用了.但是在子表中还是有被使用的,所以不可以对父表中的内容进行直接的删除,而是在逻辑上进行一些标记.

比如说商品表和订单表,可能商品表中的某些商品下架了,但是订单表中还需要保存此商品的信息.这里就是不能直接删除商品表中的商品,而是可以在商品中再加上一些逻辑判断值.如果逻辑值是no,就代表此商品被下架

数据库完整性

数据库完整性分为实体完整性、域完整性和参照完整性。实体完整性要求表中的主键字段不能为空且不能重复；域完整性要求表中数据都在有效范围内；参照完整性保证了相关联的表的数据一致性。

数据库的设计(表和表关系)

表和表的设计：

  1. 表的实体
  2. 表和表之间的关系

这里探索的是表和表 之间的关系

一对一

账号表(id,密码)和学生表(id,姓名)的关系

比如账号和学生之间,每个账号都有一个唯一的学生来匹配,每个学生也只有一个账号.

所以在上面的这种情况下,我们应该怎么来设计表呢?

方法一:可以在学生表中添加一列表示账号id;在账号表中加入一行表示学生id,这样就可以通过一个表找到另一个表

方法二:将这两个表的内容放在一张表中

一对多

学生表(id,姓名)

班级表(id,班级名)

一个班级中会有多个学生,但是一个学生只会在一个班级中

所以可以如何设计表呢?

方法一:在班级表中再添加一列,再那一列中填写是该班级的学生

方法二:在学生表中再添加一列,再那一列中填写该学生是那个班级的

image-20220309165518675 image-20220309165530925

对于mysql来说,我们只能选择使用方法二,因为MySQL中没有实现数组

多对多

比如课程和学生之间的关系.

image-20220309170437498

对于这种关系我们应该如何进行表的创建呢?

再建立一个表-----------学生课程表,在该表中可以看到每个学生选了那个课程,那个课程含有什么学生

image-20220309170419388

通过创建这个关联表就可以知道这两个表之间的关系了

新增(插入查询结果)insert into +select

将查询到的内容插入到一个表中

这是一个成批插入的一种方式

insert into 表A select 列名,列名 from 表B;

需要注意的是选择出来的结果必须和表A的内容一致,对应的内容应该一致

这个操作就是将查询到临时表中的内容插入到一个表中,选择的时候可以有一些where条件的限制.如limit/order by/distinct

mysql> select*from student;
+----+--------+-------+
| id | name   | class |
+----+--------+-------+
|  1 | 小米   |     2 |
|  2 | 小明   |     2 |
|  3 | 小李   |     3 |
+----+--------+-------+
3 rows in set (0.00 sec)
mysql> create table test1(id int ,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

-- 插入查询到的学生表中的id和name列
mysql> insert into test1 select id,name from student;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
-- 插入test1自己
mysql> insert into test1 select * from test1;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select* from test1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 小米   |
|    2 | 小明   |
|    3 | 小李   |
|    1 | 小米   |
|    2 | 小明   |
|    3 | 小李   |
+------+--------+
6 rows in set (0.00 sec)

查询

聚合查询

select 聚合函数(列名) from 表名;
count行数
select count(列名) from 表名;

count函数会统计该列中不为空的行,并返回总的行数

mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
|    6 | 孙权      |    70.0 | 73.0 |    78.5 |
|    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
+------+-----------+---------+------+---------+
mysql> select count(*) from exam_result;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.01 sec)

mysql> select count(id) from exam_result;
+-----------+
| count(id) |
+-----------+
|         7 |
+-----------+
1 row in set (0.01 sec)
-----------------------------------count不会统计该行的数值是null的行--------------------
mysql> insert into exam_result values(8,'小罗',null,null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
|    6 | 孙权      |    70.0 | 73.0 |    78.5 |
|    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
|    8 | 小罗      |    NULL | NULL |    NULL |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)

mysql> select count(math) from exam_result;
+-------------+
| count(math) |
+-------------+
|           7 |
+-------------+
sum求和

sum函数的作用是将指定列的所有行的值相加

select sum(值可相加的列名) from 表名 [where 条件];

mysql> select sum(chinese) from exam_result;
+--------------+
| sum(chinese) |
+--------------+
|        525.0 |
+--------------+
1 row in set (0.00 sec)

sum的列必须是一种可以求和的列

mysql> select * from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德    |    55.5 | 85.0 |    45.0 |
|    6 | 孙权      |    70.0 | 73.0 |    78.5 |
|    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
|    8 | 小罗      |    NULL | NULL |    NULL |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)

mysql> select sum(*) from exam_result;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from exam_result' at line 1

直接sum所有的列是不可以的

同样sum也不可以对字符串进行求和

mysql> select sum(name) from exam_result;
+-----------+
| sum(name) |
+-----------+
|         0 |
+-----------+
1 row in set, 8 warnings (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '唐三藏'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙悟空'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '猪悟能'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '曹孟德'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '刘玄德'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙权'      |
| Warning | 1292 | Truncated incorrect DOUBLE value: '宋公明'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '小罗'      |
+---------+------+-----------------------------------------------+
8 rows in set (0.00 sec)

sum可以一起算多个列

-- 下面这两种都是可以的
mysql> select sum(chinese+math+english) from exam_result;
+---------------------------+
| sum(chinese+math+english) |
+---------------------------+
|                    1550.0 |
+---------------------------+
1 row in set (0.01 sec)

mysql> select sum(chinese)+sum(math)+sum(english) from exam_result;
+-------------------------------------+
| sum(chinese)+sum(math)+sum(english) |
+-------------------------------------+
|                              1550.0 |
+-------------------------------------+
1 row in set (0.00 sec)
avg平均,max最大值,min最小值

这三个的用法和sum差不多,都是很简单的

mysql> select avg(math+english+chinese) from exam_result;
+---------------------------+
| avg(math+english+chinese) |
+---------------------------+
|                 221.42857 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select max(math+english+chinese) from exam_result;
+---------------------------+
| max(math+english+chinese) |
+---------------------------+
|                     276.5 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select min(math+english+chinese) from exam_result;
+---------------------------+
| min(math+english+chinese) |
+---------------------------+
|                     170.0 |
+---------------------------+
1 row in set (0.00 sec)

同时这些函数在计算的时候是不计算为空的值的

Group by分组操作

针对每一个行,对于指定的数据,将每一行的数据相同的分为一组

我们现在就以下面的这个表为例来看看:

mysql> select * from emp;
+----+--------------+--------------+----------+
| id | name         | role         | salary   |
+----+--------------+--------------+----------+
|  1 | 马云         | 服务员       |  1000.20 |
|  2 | 马化腾       | 游戏陪玩     |  2000.99 |
|  3 | 孙悟空       | 游戏角色     |   999.11 |
|  4 | 猪无能       | 游戏角色     |   333.50 |
|  5 | 沙和尚       | 游戏角色     |   700.33 |
|  6 | 隔壁老王     | 董事长       | 12000.66 |
+----+--------------+--------------+----------+
6 rows in set (0.00 sec)

我们按照role来进行分组,再按照聚合查询来查询一下

mysql> select role ,avg(salary),max(salary),min(salary) from emp group by role;
+--------------+--------------+-------------+-------------+
| role         | avg(salary)  | max(salary) | min(salary) |
+--------------+--------------+-------------+-------------+
| 服务员       |  1000.200000 |     1000.20 |     1000.20 |
| 游戏角色     |   677.646667 |      999.11 |      333.50 |
| 游戏陪玩     |  2000.990000 |     2000.99 |     2000.99 |
| 董事长       | 12000.660000 |    12000.66 |    12000.66 |
+--------------+--------------+-------------+-------------+
4 rows in set (0.00 sec)

我们看到,当执行上面的语句的时候,先根据role来进行分组,然后再每一个组的内部进行聚合函数来进行查询.

和直接进行聚合函数不一样的是,这个group by是先进行分组,然后组内再进行聚合查询.

而我们原来的聚合查询是针对全局的.

having

having是在我们group by之后进行的命令

mysql> select role ,avg(salary),max(salary),min(salary) from emp group by role;
+--------------+--------------+-------------+-------------+
| role         | avg(salary)  | max(salary) | min(salary) |
+--------------+--------------+-------------+-------------+
| 服务员       |  1000.200000 |     1000.20 |     1000.20 |
| 游戏角色     |   677.646667 |      999.11 |      333.50 |
| 游戏陪玩     |  2000.990000 |     2000.99 |     2000.99 |
| 董事长       | 12000.660000 |    12000.66 |    12000.66 |
+--------------+--------------+-------------+-------------+
4 rows in set (0.00 sec)

-- 用having来筛选掉min(salary)>1000
mysql> select role,avg(salary),max(salary),min(salary) from emp group by role having  min(salary)>1000;
+--------------+--------------+-------------+-------------+
| role         | avg(salary)  | max(salary) | min(salary) |
+--------------+--------------+-------------+-------------+
| 服务员       |  1000.200000 |     1000.20 |     1000.20 |
| 游戏陪玩     |  2000.990000 |     2000.99 |     2000.99 |
| 董事长       | 12000.660000 |    12000.66 |    12000.66 |
+--------------+--------------+-------------+-------------+
3 rows in set (0.01 sec)

where是在group by之前进行的命令

mysql> select role ,avg(salary),max(salary),min(salary) from emp group by role;
+--------------+--------------+-------------+-------------+
| role         | avg(salary)  | max(salary) | min(salary) |
+--------------+--------------+-------------+-------------+
| 服务员       |  1000.200000 |     1000.20 |     1000.20 |
| 游戏角色     |   677.646667 |      999.11 |      333.50 |
| 游戏陪玩     |  2000.990000 |     2000.99 |     2000.99 |
| 董事长       | 12000.660000 |    12000.66 |    12000.66 |
+--------------+--------------+-------------+-------------+
4 rows in set (0.00 sec)
-- 使用where 筛选出除董事长以外的人
mysql> select role,avg(salary),max(salary),min(salary) from emp where role!='董事长' group by role;
+--------------+-------------+-------------+-------------+
| role         | avg(salary) | max(salary) | min(salary) |
+--------------+-------------+-------------+-------------+
| 服务员       | 1000.200000 |     1000.20 |     1000.20 |
| 游戏角色     |  677.646667 |      999.11 |      333.50 |
| 游戏陪玩     | 2000.990000 |     2000.99 |     2000.99 |
+--------------+-------------+-------------+-------------+
3 rows in set (0.00 sec)

当然where和having也可以一起使用

mysql> select role,avg(salary),max(salary),min(salary) from emp where role!='董事长' group by role;
+--------------+-------------+-------------+-------------+
| role         | avg(salary) | max(salary) | min(salary) |
+--------------+-------------+-------------+-------------+
| 服务员       | 1000.200000 |     1000.20 |     1000.20 |
| 游戏角色     |  677.646667 |      999.11 |      333.50 |
| 游戏陪玩     | 2000.990000 |     2000.99 |     2000.99 |
+--------------+-------------+-------------+-------------+
3 rows in set (0.00 sec)

mysql> select role,avg(salary),max(salary),min(salary) from emp where role!='董事长' group by role having avg(salary)>1000;
+--------------+-------------+-------------+-------------+
| role         | avg(salary) | max(salary) | min(salary) |
+--------------+-------------+-------------+-------------+
| 服务员       | 1000.200000 |     1000.20 |     1000.20 |
| 游戏陪玩     | 2000.990000 |     2000.99 |     2000.99 |
+--------------+-------------+-------------+-------------+
2 rows in set (0.00 sec)

having和where的区别

在书写sql语句的时候,我们通常会和group by联合使用,这个时候就设计到执行的先后顺序问题.

where是先进行筛选再进行分组

having是先进行分组再进行筛选

where要写在group by的前面,having要写在group by的后面

SELECT class, AVG(score) FROM test WHERE class<4 GROUP BY class
SELECT class, AVG(score) FROM test GROUP BY class HAVING class<4

多表查询

前面我们介绍的都是一个表的情况,现在我们就可以看一下对于多个表应该怎么进行操作

多表查询就是对多个表的数据进行笛卡尔乘积

比如说有下面的两个表:

mysql> select* from exam_result;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
+------+-----------+---------+------+---------+
4 rows in set (0.00 sec)
-- 班级表
mysql> select * from class ;
+-----------+----------+
| studentId | classId  |
+-----------+----------+
|         1 | java102  |
|         2 | java103  |
|         3 | java101  |
|         4 | java 100 |
+-----------+----------+
4 rows in set (0.00 sec)

进行多表查询

格式1:

select 列名,列名,.. from 表名1,表名2;

格式2:

select 列名,列名--from 表1 join 表2 on 条件;

表名和表名之间使用逗号连接

mysql> select * from class,exam_result;
+-----------+----------+------+-----------+---------+------+---------+
| studentId | classId  | id   | name      | chinese | math | english |
+-----------+----------+------+-----------+---------+------+---------+
|         1 | java102  |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|         2 | java103  |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|         3 | java101  |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|         4 | java 100 |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|         1 | java102  |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|         2 | java103  |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|         3 | java101  |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|         4 | java 100 |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|         1 | java102  |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|         2 | java103  |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|         3 | java101  |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|         4 | java 100 |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|         1 | java102  |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
|         2 | java103  |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
|         3 | java101  |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
|         4 | java 100 |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
+-----------+----------+------+-----------+---------+------+---------+
16 rows in set (0.00 sec)

我们看到笛卡尔乘积后的表的列数是表1的列数+表2的列数,表的行数是表1的行数*表2的行数

选出有用的行where

但是我们观察上面的项,会发现有很多行是没有必要的,比如说studentId和id不相同的就是没有用的,所有我们就可以使用where来进行筛选

另外如果两个列名相同,就可以采用表名.列名的方式来进行

mysql> select * from class,exam_result where id=studentId;
+-----------+----------+------+-----------+---------+------+---------+
| studentId | classId  | id   | name      | chinese | math | english |
+-----------+----------+------+-----------+---------+------+---------+
|         1 | java102  |    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|         2 | java103  |    2 | 孙悟空    |    87.5 | 78.0 |    77.0 |
|         3 | java101  |    3 | 猪悟能    |    88.0 | 98.5 |    90.0 |
|         4 | java 100 |    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
+-----------+----------+------+-----------+---------+------+---------+
4 rows in set (0.01 sec)

显示出指定的列

但是我们只是想要知道学生具体是在那个班级,而上面显示太多,

我们还可以显示指定的列

mysql> select id,classId, name from class,exam_result where id=studentId;
+------+----------+-----------+
| id   | classId  | name      |
+------+----------+-----------+
|    1 | java102  | 唐三藏    |
|    2 | java103  | 孙悟空    |
|    3 | java101  | 猪悟能    |
|    4 | java 100 | 曹孟德    |
+------+----------+-----------+
4 rows in set (0.00 sec)

可以在select的后面填入指定的列,代表我们只关系学生和学生的班级的信息

多表查询的步骤

  1. 找到需要进行笛卡尔乘积运算的表
  2. 将不符合条件的表删除
  3. 找到我们要查看的指定信息
  4. 将不需要的列屏蔽

和分组相结合

例如:计算所有学生的总分,并显示学生的所有信息.

  1. 先进行笛卡尔乘积和分组运算
mysql> select *from student,score where student.id=score.student_id group by student_id;
+----+-------+---------------+-----------------+------------+-------+------------+-----------+
| id | sn    | name          | qq_mail         | classes_id | score | student_id | course_id |
+----+-------+---------------+-----------------+------------+-------+------------+-----------+
|  1 | 09982 | likui         | xuanfeng@qq.com |          1 |  70.5 |          1 |         1 |
|  2 | 00835 | puti          | NULL            |          1 |  60.0 |          2 |         1 |
|  3 | 00391 | baisuzhen     | NULL            |          1 |  33.0 |          3 |         1 |
|  4 | 00031 | xuxian        | xuxian@qq.com   |          1 |  67.0 |          4 |         1 |
|  5 | 00054 | buxiangbiye   | NULL            |          1 |  81.0 |          5 |         1 |
|  6 | 51234 | haohaoshuohua | say@qq.com      |          2 |  56.0 |          6 |         2 |
|  7 | 83223 | tellme        | NULL            |          2 |  80.0 |          7 |         2 |
+----+-------+---------------+-----------------+------------+-------+------------+-----------+
7 rows in set (0.00 sec)
  1. 然后进行sum(score)
mysql> select *,sum(score) from student,score where student.id=score.student_id group by student_id;
+----+-------+---------------+-----------------+------------+-------+------------+-----------+------------+
| id | sn    | name          | qq_mail         | classes_id | score | student_id | course_id | sum(score) |
+----+-------+---------------+-----------------+------------+-------+------------+-----------+------------+
|  1 | 09982 | likui         | xuanfeng@qq.com |          1 |  70.5 |          1 |         1 |      300.0 |
|  2 | 00835 | puti          | NULL            |          1 |  60.0 |          2 |         1 |      119.5 |
|  3 | 00391 | baisuzhen     | NULL            |          1 |  33.0 |          3 |         1 |      200.0 |
|  4 | 00031 | xuxian        | xuxian@qq.com   |          1 |  67.0 |          4 |         1 |      218.0 |
|  5 | 00054 | buxiangbiye   | NULL            |          1 |  81.0 |          5 |         1 |      118.0 |
|  6 | 51234 | haohaoshuohua | say@qq.com      |          2 |  56.0 |          6 |         2 |      178.0 |
|  7 | 83223 | tellme        | NULL            |          2 |  80.0 |          7 |         2 |      172.0 |
+----+-------+---------------+-----------------+------------+-------+------------+-----------+------------+
7 rows in set (0.00 sec)

三表查询

from形式的只是3个表之间使用逗号来连接就可以

mysql> select student.name,course.name,score.score from course,student,score where score.student_id=student.id and score.course_id=course.id;
+---------------+---------+-------+
| name          | name    | score |
+---------------+---------+-------+
| likui         | Java    |  70.5 |
| likui         | yuanli  |  98.5 |
| likui         | math    |  33.0 |
| likui         | english |  98.0 |
| puti          | Java    |  60.0 |
| puti          | math    |  59.5 |
| baisuzhen     | Java    |  33.0 |
| baisuzhen     | yuanli  |  68.0 |
| baisuzhen     | math    |  99.0 |
| xuxian        | Java    |  67.0 |
| xuxian        | yuanli  |  23.0 |
| xuxian        | math    |  56.0 |
| xuxian        | english |  72.0 |
| buxiangbiye   | Java    |  81.0 |
| buxiangbiye   | math    |  37.0 |
| haohaoshuohua | wenhua  |  56.0 |
| haohaoshuohua | chinese |  43.0 |
| haohaoshuohua | english |  79.0 |
| tellme        | wenhua  |  80.0 |
| tellme        | english |  92.0 |
+---------------+---------+-------+
20 rows in set (0.00 sec)

join on的格式:

select * from 表1 join 表2 on 条件 join 表3 on 条件;

mysql> select student.name,course.name,score.score from student join score on score.student_id=student.id join course on score.course_id=course.id;
+---------------+---------+-------+
| name          | name    | score |
+---------------+---------+-------+
| likui         | Java    |  70.5 |
| likui         | yuanli  |  98.5 |
| likui         | math    |  33.0 |
| likui         | english |  98.0 |
| puti          | Java    |  60.0 |
| puti          | math    |  59.5 |
| baisuzhen     | Java    |  33.0 |
| baisuzhen     | yuanli  |  68.0 |
| baisuzhen     | math    |  99.0 |
| xuxian        | Java    |  67.0 |
| xuxian        | yuanli  |  23.0 |
| xuxian        | math    |  56.0 |
| xuxian        | english |  72.0 |
| buxiangbiye   | Java    |  81.0 |
| buxiangbiye   | math    |  37.0 |
| haohaoshuohua | wenhua  |  56.0 |
| haohaoshuohua | chinese |  43.0 |
| haohaoshuohua | english |  79.0 |
| tellme        | wenhua  |  80.0 |
| tellme        | english |  92.0 |
+---------------+---------+-------+
20 rows in set (0.00 sec)

内连接和外连接

虽然说使用各各表之间进行连接和使用join on连接都是可以的,

但是第一种方法只支持内连接,而第二种方法内外连接都是可以的

下面我们就来具体看一看join on的一些具体是怎么进行连接的

在省略参数的情况下,join on是默认为inner的

select * from 表名1 [inner] join 表名2 on 条件;

同时join on也支持 left,right

select * from 表名1 left/right join 表名2 on 条件;

left是指可以显示左边表的所有指,right是指显示右边表的所有值

mysql> select * from student2;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    4 | 王五   |
+------+--------+
3 rows in set (0.00 sec)

mysql> select * from class2;
+------+---------+
| id   | name    |
+------+---------+
|    1 | java100 |
|    2 | java101 |
|    3 | java102 |
+------+---------+
3 rows in set (0.00 sec)

上面是我们建立的两个表,第一个表中没有3,第二个表中没有4

下面,我们依次使用inner,left,right的join on来观察到底是怎么样的.

mysql> select * from student2 [inner] join class2 on student2.id=class2.id;
-- 只筛选出同时出现在左右表中的元素
+------+--------+------+---------+
| id   | name   | id   | name    |
+------+--------+------+---------+
|    1 | 张三   |    1 | java100 |
|    2 | 李四   |    2 | java101 |
+------+--------+------+---------+
2 rows in set (0.00 sec)
-- 以左表为主,未出现在右表中的元素置为NULL
mysql> select * from student2 left join class2 on student2.id=class2.id;
+------+--------+------+---------+
| id   | name   | id   | name    |
+------+--------+------+---------+
|    1 | 张三   |    1 | java100 |
|    2 | 李四   |    2 | java101 |
|    4 | 王五   | NULL | NULL    |
+------+--------+------+---------+
3 rows in set (0.00 sec)
-- 以右表为主,出现在右表中的元素置为NULL
mysql> select * from student2 right join class2 on student2.id=class2.id;
+------+--------+------+---------+
| id   | name   | id   | name    |
+------+--------+------+---------+
|    1 | 张三   |    1 | java100 |
|    2 | 李四   |    2 | java101 |
| NULL | NULL   |    3 | java102 |
+------+--------+------+---------+
3 rows in set (0.00 sec)

上面就是join on的用法

自连接（行和行之间比较）

前面我们当我们涉及到比较的时候，采用的都是列和列之间的比较

如果之前的exam_result（id name chinese math english)我们想要比较chinese>math的同学有哪些,就直接使用列名进行筛选就可以了,但是如果要比较行和行之间的数据应该怎么办呢?这里就会用到自连接

比如说:看下面的数据

mysql> select* from score;
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  70.5 |          1 |         1 |
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  98.0 |          1 |         6 |
|  60.0 |          2 |         1 |
|  59.5 |          2 |         5 |
|  33.0 |          3 |         1 |
|  68.0 |          3 |         3 |
|  99.0 |          3 |         5 |
|  67.0 |          4 |         1 |
|  23.0 |          4 |         3 |
|  56.0 |          4 |         5 |
|  72.0 |          4 |         6 |
|  81.0 |          5 |         1 |
|  37.0 |          5 |         5 |
|  56.0 |          6 |         2 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  80.0 |          7 |         2 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
20 rows in set (0.00 sec)

我们想要找到课程3>课程1的学生id

但是和以往不同的是,这回数据是一行一行的了,不是一列一列的.所以为了让数据都变成一列一列的,我们就采用自连接的方式,让score出现两列.并加上连接条件,使得student_id都是相同的.并让左边的那一列都是显示3课程的分数,右边显示1课程的分数

子连接一般是需要取别名,区分出两个表

mysql> select * from score as s1,score as s2 where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1;
+-------+------------+-----------+-------+------------+-----------+
| score | student_id | course_id | score | student_id | course_id |
+-------+------------+-----------+-------+------------+-----------+
|  98.5 |          1 |         3 |  70.5 |          1 |         1 |
|  68.0 |          3 |         3 |  33.0 |          3 |         1 |
|  23.0 |          4 |         3 |  67.0 |          4 |         1 |
+-------+------------+-----------+-------+------------+-----------+
3 rows in set (0.00 sec)

最后,筛选出来左边分数大于右边分数的学生

mysql> select * from score as s1,score as s2 where s1.student_id=s2.student_id and s1.course_id=3 and s2.course_id=1 and s1.score>s2.score;
+-------+------------+-----------+-------+------------+-----------+
| score | student_id | course_id | score | student_id | course_id |
+-------+------------+-----------+-------+------------+-----------+
|  98.5 |          1 |         3 |  70.5 |          1 |         1 |
|  68.0 |          3 |         3 |  33.0 |          3 |         1 |
+-------+------------+-----------+-------+------------+-----------+
2 rows in set (0.00 sec)

子查询(SQL语句嵌套)

单行子查询

子查询就是多个SQL语句写在一行的操作,但是不太推荐这种做法,因为这种做法不清晰

请看下面的例子:

mysql> select * from student;
+----+-------+---------------+------------------+------------+
| id | sn    | name          | qq_mail          | classes_id |
+----+-------+---------------+------------------+------------+
|  1 | 09982 | likui         | xuanfeng@qq.com  |          1 |
|  2 | 00835 | puti          | NULL             |          1 |
|  3 | 00391 | baisuzhen     | NULL             |          1 |
|  4 | 00031 | xuxian        | xuxian@qq.com    |          1 |
|  5 | 00054 | buxiangbiye   | NULL             |          1 |
|  6 | 51234 | haohaoshuohua | say@qq.com       |          2 |
|  7 | 83223 | tellme        | NULL             |          2 |
|  8 | 09527 | laowai        | foreigner@qq.com |          2 |
+----+-------+---------------+------------------+------------+
8 rows in set (0.00 sec)

现在想要查询puti的同伴同学

正常的SQL语句是分为两个步骤,第一个步骤查出puti的班级号,第二个步骤是根据班级号来选学生

mysql> select classes_id from student where name='puti';
+------------+
| classes_id |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> select name from student where classes_id=1;
+-------------+
| name        |
+-------------+
| likui       |
| puti        |
| baisuzhen   |
| xuxian      |
| buxiangbiye |
+-------------+
5 rows in set (0.00 sec)

但是,现在这一个子查询的sql是将上面的两个语句整合到一起.

mysql> select name from student where classes_id=(select classes_id from student where name='puti');
+-------------+
| name        |
+-------------+
| likui       |
| puti        |
| baisuzhen   |
| xuxian      |
| buxiangbiye |
+-------------+
5 rows in set (0.00 sec)
多行子查询

当被嵌套的sql语句返回的是多行的话,那就是多行子查询

比如说下面的例子:

查出语文和英语的成绩信息

按照以往的sql语句:

先找出chinese和english的id值

mysql> select id from course where name='english' or name='chinese';
+----+
| id |
+----+
|  4 |
|  6 |
+----+
2 rows in set (0.00 sec)

再去找相应的信息

mysql> select score,course_id from score where course_id in(4,6);
+-------+-----------+
| score | course_id |
+-------+-----------+
|  98.0 |         6 |
|  72.0 |         6 |
|  43.0 |         4 |
|  79.0 |         6 |
|  92.0 |         6 |
+-------+-----------+
5 rows in set (0.00 sec)

同时,我们还可以将sql语句嵌套在一起

mysql> select score,course_id from score where course_id in(select id from course where name='english' or name='chinese');
+-------+-----------+
| score | course_id |
+-------+-----------+
|  98.0 |         6 |
|  72.0 |         6 |
|  43.0 |         4 |
|  79.0 |         6 |
|  92.0 |         6 |
+-------+-----------+
5 rows in set (0.00 sec)

合并查询union

将多个select语句用union/union all合并起来查询就是合并查询,但是需要注意的是各各select语句查询的列必须是相同的

mysql> select score,course_id from score where course_id=6 union select score,course_id from score where course_id=4;
+-------+-----------+
| score | course_id |
+-------+-----------+
|  98.0 |         6 |
|  72.0 |         6 |
|  79.0 |         6 |
|  92.0 |         6 |
|  43.0 |         4 |
+-------+-----------+
5 rows in set (0.00 sec)

这个关键字和in十分的像,但是in必须局限在同一个表中,而union可以在不同的表中

另外,union all是代表去重,union不去重

练习合集

找出总工资排名第二的部门

  1. select depart from
  2. group by 分组
  3. 求sum(salary)
  4. order by desc 降序排列,找出第二大的
  5. limit A offset B,从B之后开始找(不含B)找A个数
mysql> select depart,sum(salary) from emp2 group by depart order by sum(salary);
+--------+-------------+
| depart | sum(salary) |
+--------+-------------+
| 卫生   |       18000 |
| 教育   |       22000 |
| 交通   |       23000 |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select depart,sum(salary) from emp2 group by depart order by sum(salary) limit 1 offset 1;
+--------+-------------+
| depart | sum(salary) |
+--------+-------------+
| 教育   |       22000 |
+--------+-------------+
1 row in set (0.00 sec)
CAFE～BABE
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
「MySQL」- 基础增删改查
「zero」的博客
11-18 4662
前言 数据库中的 CRUD 操作 , 是最基本也是最重要的基础 , 这里的 CRUD 是指 增查改删 英文首字母 C : create 增加 R : retrieve 检索 U : updata 修改 D : delete 删除 本章介绍的 CRUD 操作 , 都是围绕着数据表来进行操作的 ...
【MySQL】的增删改查
lincoco49的博客
05-27 125
一.增 #向表中插入1条数据 1.insert into 表名(字段1,字段2,...) values(值1,值2,...) #向表中插入多条数据 2.insert into 表名(字段1,字段2,...) values(值1,值2,...), (值1,值2,...),...... #向表中插入来自其他表的数据 3.insert into 表名1(字段1,字段2,...) select...
MySQL数据库 -- 表的增删查改
qq_58724706的博客
11-15 2464
今天来讲MySQL数据库的表增删查改操作。今天主要是通过栗子来演示语法使用的，话不多说，直奔主题~Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）
MySQL5.7数据库-增删改查操作
Small-J
01-19 3931
文章目录数据库介绍数据库存储数据特点数据库的分类语法基础SQL介绍SQL语句主要分为数据完整性数据类型字符串类型枚举类型日期类型约束SQL语句操作数据库（DDL）数据库连接退出数据库查看已经创建的数据库查看数据库版本创建数据库查看创建数据库的命令查看当前使用的数据库使用数据库删除数据库操作数据表查看当前数据库中所有的表创建表查看表的描述查看表的创建语句练习添加表字段修改表字段删除表字段练习操作数据插入数据整行插入指定列中插入数据指定列中插入多条数据练习修改数据修改数据练习删除数据练习查询数据查询整个表数据查
【MYSQL】增删改查
qq_67469987的博客
05-15 1396
在 MySQL 中，增是通过指定表与列值进行数据插入；删是依据条件从表中移除相应行；改是根据条件设定新值更新指定列；查是利用条件从表中获取所需列的数据。以上就是对于 MySQL 中增删改查基本操作步骤的概括描述。
Java-Swing+Mysql+增删改查Demo
07-12
总之，"Java-Swing+Mysql+增删改查Demo"是一个结合了Java GUI编程和数据库操作的实例，它展示了如何使用Swing构建用户界面，并通过MySQL进行数据管理，对于初学者来说，这是一个很好的学习资源，可以帮助理解Java...
MySQL-增删改查(四)
12-14
在MySQL数据库管理中，"增删改查"（CRUD，Create, Read, Update, Delete）是最基础且重要的操作。本文将重点讨论“增”这一部分，即如何向数据库表中添加数据。我们以创建一个名为`message`的表为例，来详细讲解两种...
mysql数据库增删改查关键字_MySQL数据库（二）－数据库的增删改查
weixin_34733070的博客
01-28 2674
简介：以下是MySQL最基本的增删改查语句．在进行“增删改查”的操作之前，先建立一个包含数据表student的数据库,新建表grade(具体操作可以见上一篇)．一、＂增＂－添加数据1.1 为表中所有字段添加数据语法：INSERT INTO 表名 VALUES(值1,值2,...);举例：INSERT INTO VALUES(1,'zhangshan',98),(2,'lisi',62);若不指定字...
mysql增删改查基础语句
12-16
以上就是MySQL的基本增删改查操作。了解并熟练掌握这些语句是进行数据库管理的基础，对于数据库开发人员来说至关重要。在实际应用中，还需要结合索引、事务处理、存储过程等高级特性，以提高数据管理的效率和安全性...
Java项目: 基于SpringBoot+mysql房产销售系统 (含源码+数据库+开题报告+答辩PPT+毕业论文)
weixin_43860634的博客
09-03 605
Java项目: 基于SpringBoot+mysql房产销售系统 (含源码+数据库+开题报告+答辩PPT+毕业论文)
策略规划：在MySQL中实现数据恢复的全面指南
2401_85339615的博客
09-04 1082
数据恢复是数据库管理中至关重要的一环，它确保在发生数据丢失或损坏的情况下，能够迅速且准确地恢复数据。在MySQL中，实现有效的数据恢复策略规划需要综合考虑备份策略、备份类型、存储管理、故障转移机制以及恢复流程。本文将深入探讨如何在MySQL中进行数据恢复的策略规划，包括策略制定、技术实现和最佳实践。
MySQL数据备份的版本控制：策略、实践与自动化
2401_85341950的博客
09-04 920
数据备份的版本控制是指对数据库备份进行管理，以便可以追踪、访问和恢复到数据库的特定历史状态。数据恢复：在数据丢失或损坏的情况下快速恢复数据。审计和合规性：满足法规要求，提供数据访问和变更的历史记录。开发和测试：为开发和测试环境提供数据的特定版本。
【重学 MySQL】十四、显示表结构
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
09-06 644
以上就是在MySQL中显示表结构的几种常用方法。DESCRIBE或DESC命令和命令提 供了快速查看表列信息的简便方式，而查询数据库则提供了更详细、更灵活的信息获取方式。命令则适用于需要查看或复制表完整结构的情况。
go解析YAML文件多文档
xsw164711368的博客
09-03 1267
本文介绍了YAML文档的特点，并用代码演示了GO语言对YAML文档的处理，特别是描述了当YAML文件中存在多个YAML文档时如何处理的方法。
【MySQL】批量插入数据造数-存储过程
兴焉的博客
09-05 270
日常工作中可能有针对需要对某个表进行造数，如何批量插入呢？可以使用存储过程循环结构。下面是一个存储过程以插入100条，while语句后的
Mysql-数据库课程设计图书管理系统
最新发布
qq_38162031的博客
09-08 1401
在我们的"Mysql-数据库课程设计图书管理系统"教程中，我们成功地建立了一个图书管理系统，涵盖了数据库设计、数据存储、数据查询、数据分析等多个方面。在这个过程中，我们从浪浪云得到了极大的支持，他们高效的云服务，稳定的运行环境，以及专业的技术支持，让我们能够更好地完成教程。对于今后，我们希望浪浪云能持续为我们提供高质量的云服务，使我们能在实践与学习中不断进步。图书管理系统仅是数据库应用的冰山一角，未来我们有更多复杂的数据库课题等待我们去挑战。让我们携手浪浪云，共同在数据库领域探索、学习、进步！
【MySQL07】【锁】
猫吻鱼的博客
09-03 1111
最近在读《MySQL 是怎样运行的》、《MySQL技术内幕 InnoDB存储引擎 》，后续会随机将书中部分内容记录下来作为学习笔记，部分内容经过个人删改，因此可能存在错误，如想详细了解相关内容强烈推荐阅读相关书籍。
MySQL复习3
Lenn Louis' Scribe
09-03 555
通过视图，我们可以查看基表的部分数据。视图数据来自定义视图的查询中使用的表，使用动态图动态生成。）是一种虚拟存在的表，是一个逻辑表，本省没有数据，内容由查询定义。基表：用来创建视图的表叫做基表。
Java项目: 基于SpringBoot+mysql企业客户管理系统(含源码+数据库+答辩PPT+毕业论文)
weixin_43860634的博客
09-05 530
Java项目: 基于SpringBoot+mysql企业客户管理系统(含源码+数据库+答辩PPT+毕业论文)
辛星MySQL基础教程：2015春季版增删改查入门
"辛星MySQL教程是一本针对初学者的基础操作指南，主要涵盖了数据库的增删改查基础操作，同时也涉及到了字符集和模式等重要概念。教程由辛星于2014年8月开始编写，经过多次修订，形成了一个相对完整的知识体系。教程...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值