mysqlsh应用程序错误 mysql 应用

转载

mob6454cc70863a 2023-11-06 13:36:16

文章标签 mysqlsh应用程序错误 mysql java 数据库字段 文章分类 MySQL 数据库

文章目录

一、创建

1.创建数据库
2.查看自己创建的数据库
3.选中自己创建的数据库
4.查看新建的表在原来的数据库中是否存在
5.新建表，同时给表里面赋上属性
6.查看自己创建的表
7.显示字段

二、CRUD（增、删、查、改）

1、增

1.全列插入
2.查看
3.插入指定列
4.删除指定行
5.指定列查询
6.将id列所有数+10
7.查询字段为表达式
8.起别名

2、查

1.去重 distinct
2.排序 order by
3.累计查询 +
4.条件查询 where
5.模糊查询：like
6.分页查询 Limit

3、改

1.数据更新 update
2.综合操作

4、删

1.删除表中数据 delete
2.删除表的本身
3. 删除数据库

三、进阶查询

1、数据库的约束

1.NULL约束
2.唯一约束 unique
3.默认值约束 default
4.主键约束 primary key
5.外键约束 foreign key
6.check约束

2、进阶查询

1.聚合函数
2.分组 group by
3.联合查询

内连接
外连接
自连接
子查询

四、数据库索引

1.何为索引？
2.为什么要使用索引？
3.索引的优点
4.索引的缺点
5.索引使用的场景
6.避免使用索引的场景
7.索引的类型
8.通配符失效
9.索引使用的数据结构

五、事务

1.为什么使用事务？
2.事务的概念
3.事务的特性(ACID)
4.事务的使用

一、创建

1.创建数据库

查看系统原有的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

创建自己数据库

mysql> create database if not exists huashanzhizai;
Query OK, 1 row affected (0.00 sec)

2.查看自己创建的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| huashanzhizai      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

到这里，自己的数据库已经创建好了，接下来就应该在数据库中创建自己的表。就相当于买了杯子，现在可以往水杯里面加水了！

3.选中自己创建的数据库

mysql> use huashanzhizai;
Database changed

4.查看新建的表在原来的数据库中是否存在

mysql> drop table if exists stu_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

5.新建表，同时给表里面赋上属性

mysql> create table stu_test(
id INT,
name varchar(10),
password varchar(10),
age int ,
sex varchar(1),
birthday timestamp,
amout decimal(6,2),
resume text
);
Query OK, 0 rows affected (0.04 sec)

6.查看自己创建的表

mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| stu_test                |
+-------------------------+
1 row in set (0.00 sec)

添加注释

mysql> drop table if exists stu_test;
Query OK, 0 rows affected (0.02 sec)


mysql> create table stu_test(
id INT,
name varchar(10) comment '姓名',
password varchar(10) comment '密码',
age int comment '年龄',
sex varchar(1),
birthday timestamp,
amout decimal(6,2),
resume text
);
Query OK, 0 rows affected (0.04 sec)

7.显示字段

mysql> desc stu_test;
+---------+---------------+----------+---------+---------------------+-------------------+
| Field   | Type          | Null     | Key     | Default             | Extra             |
+---------+---------------+----------+---------+---------------------+-------------------+
| id      | int(11)       | YES      |         | NULL                |                   |
| name    |varchar(10)    | YES      |         | NULL                |                   |
| password|varchar(10)    | YES      |         | NULL                |                   |
| age     | int(11)       | YES      | 		   | NULL                |                   |
| sex     |varchar(1)     | YES      |         | NULL                |                   |
| birthday| timestamp     | NO       |         | CURRENT_TIMESTAMP   |on update CURRENT_TIMESTAMP  |
| amout   |decimal(6,2)   | YES      |         | NULL                |                   |
| resume  | text          | YES      |         | NULL                |                   |
+---------+---------------+----------+---------+---------------------+-------------------+
8 rows in set (0.01 sec)

二、CRUD（增、删、查、改）

1、增

准备工作

mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> create table student(
id int,
sn int ,
name varchar(20),
qq_mail varchar(20)
);
Query OK, 0 rows affected (0.04 sec)

1.全列插入

//单行全列插入
mysql> insert into student values(1,101,'bit','1963599369@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(2,102,'yanghua','123@qq.com');
Query OK, 1 row affected (0.01 sec)
//每次只能插入一行，且每一个字段要和数据库对应

2.查看

mysql> select *from student;
+------+------+---------+-------------------+
| id   | sn   | name    | qq_mail           |
+------+------+---------+-------------------+
| 1    | 101  | bit     | 1963599369@qq.com |
| 2    | 102  | yanghua | 123@qq.com        |
+------+------+---------+-------------------+
2 rows in set (0.00 sec)

3.插入指定列

mysql> insert into student (id,name) values(3,'kangxiangkun'),(4,'cuiyulu');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from student;
+------+------+--------------+-------------------+
| id   | sn   | name         | qq_mail           |
+------+------+--------------+-------------------+
| 1    | 101  | bit          | 1963599369@qq.com |
| 2    | 102  | yanghua      | 123@qq.com        |
| 3    | NULL | kangxiangkun | NULL              |
| 4    | NULL | cuiyulu      | NULL              |
+------+------+--------------+-------------------+
4 rows in set (0.00 sec)

4.删除指定行

mysql> delete from student 
where id = 2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+------+--------------+-------------------+
| id   | sn   | name         | qq_mail           |
+------+------+--------------+-------------------+
| 1    | 101  | bit          | 1963599369@qq.com |
| 3    | NULL | kangxiangkun | NULL              |
| 4    | NULL | cuiyulu      | NULL              |
+------+------+--------------+-------------------+
3 rows in set (0.00 sec)

5.指定列查询

mysql> select id,name 
from student;
+------+--------------+
| id   | name         |
+------+--------------+
| 1    | bit          |
| 3    | kangxiangkun |
| 4    | cuiyulu      |
+------+--------------+
3 rows in set (0.00 sec)

6.将id列所有数+10

mysql> select id+10,name 
from student;
+-------+--------------+
| id+10 | name         |
+-------+--------------+
| 11    | bit          |
| 13    | kangxiangkun |
| 14    | cuiyulu      |
+-------+--------------+
3 rows in set (0.00 sec)

7.查询字段为表达式

mysql> select id,name,10 
from student;
+------+--------------+----+
| id   | name         | 10 |
+------+--------------+----+
| 1    | bit          | 10 |
| 3    | kangxiangkun | 10 |
| 4    | cuiyulu      | 10 |
+------+--------------+----+
3 rows in set (0.00 sec)

8.起别名

字段起别名

mysql> select id,name 
as 姓名 
from student;
+------+--------------+
| id   | 姓名         |
+------+--------------+
| 1    | bit          |
| 3    | kangxiangkun |
| 4    | cuiyulu      |
+------+--------------+
3 rows in set (0.00 sec)

表起别名

mysql> select id,name 
from student 
as teacher;
+------+--------------+
| id   | name         |
+------+--------------+
| 1    | bit          |
| 3    | kangxiangkun |
| 4    | cuiyulu      |
+------+--------------+
3 rows in set (0.00 sec)

2、查

准备工作

//删除已经存在的同名的表
mysql> DROP TABLE IF EXISTS exam;
Query OK, 0 rows affected, 1 warning (0.00 sec)

//新建表
mysql> CREATE TABLE exam (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
Query OK, 0 rows affected (0.05 sec)

//全列插入
mysql> INSERT INTO exam (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0

//查看字段
mysql> select * from exam;
+------+-----------+---------+------+---------+
| 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.00 sec)

1.去重 distinct

去掉数学成绩有重复并显示

mysql> select distinct math 
from exam_result;
+------+
| math |
+------+
| 98.0 |
| 78.0 |
| 84.0 |
| 85.0 |
| 73.0 |
| 65.0 |
+------+
6 rows in set (0.00 sec)

2.排序 order by

默认情况下是升序

按数学成绩升序 asc

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

降序 desc

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

注意：不要使用关键字当作表名，例如desc；如果非要使用，则将关键字表名改为 desc（esc下面的那个按键）

对排序字段中存在NULL时进行升序

//插入一个字段
mysql> insert into exam (id,name, chinese, math, english) values
 (8,'鲁智深', 70, null, 38);
Query OK, 1 row affected (0.01 sec)

//显示
mysql> select * from exam;
+------+-----------+---------+------+---------+
| 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    | 鲁智深    | 70.0    | NULL | 38.0    |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)

//按数学进行升序排列
mysql> select * from exam 
order by math 
asc;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
| 8    | 鲁智深     | 70.0    | NULL | 38.0    |
| 7    | 宋公明     | 75.0    | 65.0 | 30.0    |
| 6    | 孙权       | 70.0 	| 73.0 | 78.5    |
| 2    | 孙悟空     | 87.5	   | 78.0 | 77.0    |
| 4    | 曹孟德     | 82.0    | 84.0 | 67.0    |
| 5    | 刘玄德     | 55.5    | 85.0 | 45.0    |
| 1    | 唐三藏     | 67.0    | 98.0 | 56.0    |
| 3    | 猪悟能     | 88.0    | 98.5 | 90.0    |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)

对排序字段中存在NULL时进行降序

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

同时对多个字段进行排序（chinese降序，math，english升序）

mysql> select name,chinese,math, english 
from exam
order by chinese 
desc,math,english 
asc;
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 猪悟能    | 88.0    | 98.5 | 90.0    |
| 孙悟空    | 87.5    | 78.0 | 77.0    |
| 曹孟德    | 82.0    | 84.0 | 67.0    |
| 宋公明    | 75.0    | 65.0 | 30.0    |
| 鲁智深    | 70.0    | NULL | 38.0    |
| 孙权      | 70.0    | 73.0 | 78.5    |
| 唐三藏    | 67.0    | 98.0 | 56.0    |
| 刘玄德    | 55.5    | 85.0 | 45.0    |
+-----------+---------+------+---------+
8 rows in set (0.00 sec)

3.累计查询 +

mysql> select id,name,chinese+math+english 
from exam;
+------+-----------+----------------------+
| id   | name      | chinese+math+english |
+------+-----------+----------------------+
| 1    | 唐三藏    | 221.0                |
| 2    | 孙悟空    | 242.5                |
| 3    | 猪悟能    | 276.5                |
| 4    | 曹孟德    | 233.0                |
| 5    | 刘玄德    | 185.5                |
| 6    | 孙权      | 221.5                |
| 7    | 宋公明    | 170.0                |
| 8    | 鲁智深    | NULL                 |
+------+-----------+----------------------+
8 rows in set (0.00 sec)

4.条件查询 where

where
查询数学成绩大于80分的同学的id,name,math

mysql> select id,name,math from exam where math > 80;
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 1    | 唐三藏    | 98.0 |
| 3    | 猪悟能    | 98.5 |
| 4    | 曹孟德    | 84.0 |
| 5    | 刘玄德    | 85.0 |
+------+-----------+------+
4 rows in set (0.00 sec)

查询数学成绩大于80分的同学的id,name,math，然后根据数学成绩升序排序

mysql> select id,name,math 
from exam 
where math > 80 
order by math;
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 4    | 曹孟德    | 84.0 |
| 5    | 刘玄德    | 85.0 |
| 1    | 唐三藏    | 98.0 |
| 3    | 猪悟能    | 98.5 |
+------+-----------+------+
4 rows in set (0.00 sec)

查询数学成绩等于98分的同学的id,name,math

mysql> select id,name,math from exam where math = 98;
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 1    | 唐三藏    | 98.0 |
+------+-----------+------+
1 row in set (0.00 sec)

注意NULL: = 不安全对于NULL 查不到

mysql> select id,name,math from exam where math = NULL;
Empty set (0.00 sec)

解决方法：使用 <=> 这个运算符，就可以查到

mysql> select id,name,math 
from exam 
where math <=> NULL;
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 8    | 鲁智深    | NULL |
+------+-----------+------+
1 row in set (0.00 sec)

查询数学成绩不等于98分的同学的id,name,math

mysql> select id,name,math 
from exam 
where math != 98;
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 2    | 孙悟空    | 78.0 |
| 3    | 猪悟能    | 98.5 |
| 4    | 曹孟德    | 84.0 |
| 5    | 刘玄德    | 85.0 |
| 6    | 孙权      | 73.0 |
| 7    | 宋公明    | 65.0 |
+------+-----------+------+
6 rows in set (0.00 sec)

除了这个值之外的其他值，与 != 效果相同 <>
不建议使用<>,无法使用索引,查询效率低

mysql> select id,name,math 
from exam 
where math <> 98;
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 2    | 孙悟空    | 78.0 |
| 3    | 猪悟能    | 98.5 |
| 4    | 曹孟德    | 84.0 |
| 5    | 刘玄德    | 85.0 |
| 6    | 孙权      | 73.0 |
| 7    | 宋公明    | 65.0 |
+------+-----------+------+
6 rows in set (0.00 sec)

查询数学成绩70-90分之间的同学==id,name,math == A and B

mysql> select id,name,math 
from exam
where math 
between 70 and 90;
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 2    | 孙悟空    | 78.0 |
| 4    | 曹孟德    | 84.0 |
| 5    | 刘玄德    | 85.0 |
| 6    | 孙权      | 73.0 |
+------+-----------+------+
4 rows in set (0.00 sec)

查询数学成绩在98,85,73中的所有同学的==id,name,math == in（x，y，z，…）

mysql> select id,name,math 
from exam 
where math 
in(98,85,73);
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 1    | 唐三藏    | 98.0 |
| 5    | 刘玄德    | 85.0 |
| 6    | 孙权      | 73.0 |
+------+-----------+------+
3 rows in set (0.00 sec)

用 or 也可以达到精确查询的效果

mysql> select id,name,math 
from exam 
where math = 98 or math = 85 or math = 73;
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 1    | 唐三藏    | 98.0 |
| 5    | 刘玄德    | 85.0 |
| 6    | 孙权      | 73.0 |
+------+-----------+------+
3 rows in set (0.01 sec)

6.NULL和NOT NULL

mysql> select id,name,math 
from exam 
where math 
is NULL;
+------+-----------+------+
| id | name | math |
+------+-----------+------+
| 8 | 鲁智深 | NULL |
+------+-----------+------+
1 row in set (0.00 sec)


mysql> select id,name,math 
from exam 
where math 
is NOT NULL;
+------+-----------+------+
| id   | name      | math |
+------+-----------+------+
| 1    | 唐三藏    | 98.0 |
| 2    | 孙悟空    | 78.0 |
| 3    | 猪悟能    | 98.5 |
| 4    | 曹孟德    | 84.0 |
| 5    | 刘玄德    | 85.0 |
| 6    | 孙权      | 73.0 |
| 7    | 宋公明    | 65.0 |
+------+-----------+------+
7 rows in set (0.00 sec)

5.模糊查询：like

准备工作

//添加字段
mysql> insert into exam(id,name,chinese,math,english) values(9,'张三丰',10,11,90);
Query OK, 1 row affected (0.01 sec)
mysql> insert into exam values(10,'张三',80,61,50);
Query OK, 1 row affected (0.00 sec)


mysql> select * from exam;
+------+-----------+---------+------+---------+
| 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    | 鲁智深    | 70.0    | NULL | 38.0    |
| 9    | 张三丰    | 10.0    | 11.0 | 90.0    |
| 10   | 张三      | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)

查询name当中包含== "三"的学生===的信息 %三%

mysql> select * from 
exam 
where name 
like '%三%';
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
| 1    | 唐三藏    | 67.0    | 98.0 | 56.0    |
| 9    | 张三丰    | 10.0    | 11.0 | 90.0    |
| 10    | 张三     | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+------+---------+
3 rows in set (0.00 sec)

查询name中以“三”结尾的学生的信息 %三

mysql> select * from exam 
where name 
like '%三';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
| 10   | 张三   | 80.0    | 61.0 | 50.0    |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)

查询name中以“三”开头的学生的信息三%

mysql> select * from exam 
where name 
like '三%';
Empty set (0.00 sec)

另一种模糊匹配机制

’孙_’ 表示孙 + 一个模糊匹配的字

mysql> select * from exam 
where name 
like '孙_';
+------+--------+---------+------+---------+
| id   | name   | chinese | math | english |
+------+--------+---------+------+---------+
| 6    | 孙权   | 70.0    | 73.0 | 78.5    |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)

’孙_’表示孙+两个模糊匹配的字

在这里插入代码片

6.分页查询 Limit

为什么要分页？因为在一个网页上加载太多数据会影响cpu的效率，分页的话，系统只会加载额定的数据，效率提高

mysql> select * from exam 
limit 0,5;
+------+-----------+---------+------+---------+
| 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    |
+------+-----------+---------+------+---------+
5 rows in set (0.00 sec)


mysql> select * from exam 
limit 1,4;
+------+-----------+---------+---------+---------+
| id   | name      | chinese | math    | english |
+------+-----------+---------+---------+---------+
| 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    |
+------+-----------+---------+------+------------+
4 rows in set (0.00 sec)


//如果偏移量太大，则只能查到表中有限个数据
mysql> select * from exam 
limit 5,10;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
| 6    | 孙权      | 70.0    | 73.0 | 78.5    |
| 7    | 宋公明    | 75.0    | 65.0 | 30.0    |
| 8    | 鲁智深    | 70.0    | NULL | 38.0    |
| 9    | 张三丰    | 10.0    | 11.0 | 90.0    |
| 10   | 张三      | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+------+---------+
5 rows in set (0.00 sec)


//如果起始数太大则查不到数据
mysql> select * from exam 
limit 12,5;
Empty set (0.00 sec)


//如果没有写起始数据，则默认从0开始
mysql> select * from exam 
limit 5;
+------+-----------+---------+------+---------+
| 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    |
+------+-----------+---------+------+---------+
5 rows in set (0.00 sec)

3、改

1.数据更新 update

将孙权的数学成绩，更新为99分

//更新前：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| 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    | 鲁智深    | 70.0    | NULL | 38.0    |
| 9    | 张三丰    | 10.0    |11.0  | 90.0    |
| 10   | 张三      | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)


//更新代码：
mysql> update exam 
set math = 99 
where name = '孙权';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0


//更新后：
mysql> select *from exam;
+------+-----------+---------+------+---------+
| 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    | 99.0 | 78.5    |
| 7    | 宋公明    | 75.0    | 65.0 | 30.0    |
| 8    | 鲁智深    | 70.0    | NULL | 38.0    |
| 9    | 张三丰    | 10.0    | 11.0 | 90.0    |
| 10   | 张三      | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)

将曹孟德同学的数学成绩变更为== 60 分==，语文成绩变更为70 分

//更新前：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| 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    | 99.0 | 78.5    |
| 7    | 宋公明    | 75.0    | 65.0 | 30.0    |
| 8    | 鲁智深    | 70.0    | NULL | 38.0    |
| 9    | 张三丰    | 10.0    | 11.0 | 90.0    |
| 10   | 张三      | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)


//更新代码：
mysql> update exam 
set math = 60,chinese = 70 
where name = '曹孟德';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0


//更新后：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| 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    | 曹孟德    | 70.0    | 60.0 | 67.0    |
| 5    | 刘玄德    | 55.5    | 85.0 | 45.0    |
| 6    | 孙权      | 70.0    | 99.0 | 78.5    |
| 7    | 宋公明    | 75.0    | 65.0 | 30.0    |
| 8    | 鲁智深    | 70.0    | NULL | 38.0    |
| 9    | 张三丰    | 10.0    | 11.0 | 90.0    |
| 10   | 张三      | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)

2.综合操作

将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

//更新前：先倒序,再取前三,找到这三位同学
mysql> select id ,name,chinese+english+math 
as total 
from exam 
order by chinese+english+math 
asc 
limit 1,3;
+------+-----------+-------+
| id   | name      | total |
+------+-----------+-------+
| 9    | 张三丰    | 111.0 |
| 7    | 宋公明    | 170.0 |
| 5    | 刘玄德    | 185.5 |
+------+-----------+-------+
3 rows in set (0.00 sec)


//更新代码：
mysql> update exam 
set math = math - 30 
where chinese + math + english 
is not NULL 
order by chinese + math + english 
limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0


//更新后：
mysql> select * from exam;
+------+-----------+---------+-------+---------+
| 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    | 曹孟德    | 70.0    | 60.0 | 67.0    |
| 5    | 刘玄德    | 55.5    | 55.0 | 45.0    |
| 6    | 孙权      | 70.0    | 99.0 | 78.5    |
| 7    | 宋公明    | 75.0    | 35.0 | 30.0    |
| 8    | 鲁智深    | 70.0    | NULL | 38.0    |
| 9    | 张三丰    | 10.0    | -19.0| 90.0    |
| 10   | 张三      | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+-------+---------+
10 rows in set (0.00 sec)

4、删

1.删除表中数据 delete

删除孙悟空的考试成绩

//原表代码：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| 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    | 曹孟德    | 70.0    | 60.0 | 67.0    |
| 5    | 刘玄德    | 55.5    | 85.0 | 45.0    |
| 6    | 孙权      | 70.0    | 99.0 | 78.5    |
| 7    | 宋公明    | 75.0    | 65.0 | 30.0    |
| 8    | 鲁智深    | 70.0    | NULL | 38.0    |
| 9    | 张三丰    | 10.0    | 11.0 | 90.0    |
| 10   | 张三      | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+------+---------+
10 rows in set (0.00 sec)


//删除代码：
mysql> delete from exam 
where name = '孙悟空';
Query OK, 1 row affected (0.01 sec)


//删除后：
mysql> select * from exam;
+------+-----------+---------+------+---------+
| id   | name      | chinese | math | english |
+------+-----------+---------+------+---------+
| 1    | 唐三藏    | 67.0    | 98.0 | 56.0    |
| 3    | 猪悟能    | 88.0    | 98.5 | 90.0    |
| 4    | 曹孟德    | 70.0    | 60.0 | 67.0    |
| 5    | 刘玄德    | 55.5    | 85.0 | 45.0    |
| 6    | 孙权      | 70.0    | 99.0 | 78.5    |
| 7    | 宋公明    | 75.0    | 65.0 | 30.0    |
| 8    | 鲁智深    | 70.0    | NULL | 38.0    |
| 9    | 张三丰    | 10.0    | 11.0 | 90.0    |
| 10   | 张三      | 80.0    | 61.0 | 50.0    |
+------+-----------+---------+------+---------+
9 rows in set (0.00 sec)


//直接删除表：会删除表内的所有的数据，但是这张表还是存在的，只不过里面没有数据了
mysql> delete from exam;
Query OK, 9 rows affected (0.01 sec)


//表中没有数据了
mysql> select * from exam;
Empty set (0.00 sec)


//但是表还在，只是表中没有数据了
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| exam                    |
| student                 |
+-------------------------+
2 rows in set (0.00 sec)

2.删除表的本身

mysql> drop table exam;
Query OK, 0 rows affected (0.03 sec)


//现在表已经不存在了
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| student                 |
+-------------------------+
1 row in set (0.00 sec)

3. 删除数据库

这个操作很危险,不要轻易尝试

mysql> drop database if exists 库名;

三、进阶查询

1、数据库的约束

1.NULL约束

指定属性的语句不能为NULL

//建表，准备工作
mysql> drop table if exists student;
Query OK, 0 rows affected (0.05 sec)


//指定name字段不能为NULL
mysql> create table student(
id int,
sn int,
name varchar(20) not NULL,
qq_mail varchar(20)
);
Query OK, 0 rows affected (0.15 sec)


mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| student                 |
+-------------------------+
1 row in set (0.00 sec)


//此时准备往表里插入元素
mysql> insert into student(id,sn,name,qq_mail)
values(1,101,NULL,'123@qq.com');
ERROR 1048 (23000): Column 'name' cannot be NULL


//但是报错了，原因如下
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| sn      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(20) | NO   |     | NULL    |       |//name这一列不能为NULL，否则报错，这就是NULL约束
| qq_mail | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

2.唯一约束 unique

指定属性的语句不能插入两次

//建表，准备工作
mysql> drop table if exists student;
Query OK, 0 rows affected (0.02 sec)


//指定sn字段的值只能唯一
mysql> create table student(
id int,
sn int unique,
name varchar(20),
qq_mail varchar(20)
);
Query OK, 0 rows affected (0.04 sec)


//添加第一条语句
mysql> insert into student(id,sn,name,qq_mail)
values(1,101,'bit','123@qq.com');
Query OK, 1 row affected (0.03 sec)


//添加后的效果
mysql> select * from student;
+------+------+------+------------+
| id   | sn   | name | qq_mail    |
+------+------+------+------------+
| 1    | 101  | bit  | 123@qq.com |
+------+------+------+------------+
1 row in set (0.00 sec)


//添加sn和第一条语句相同的第二条语句时：
mysql> insert into student(id,sn,name,qq_mail)
values(2,101,'bit2','1232@qq.com');
ERROR 1062 (23000): Duplicate entry '101' for key 'sn'

3.默认值约束 default

在声明属性的时候就赋值，如果后面添加成员变量，则系统会默认赋值

//建表，准备工作
mysql> drop table if exists student;
Query OK, 0 rows affected (0.03 sec)


//指定qq_mail默认赋值为110@qq.com
mysql> create table student(
id int,
sn int,
name varchar(20),
qq_mail varchar(20) default '110@qq.com'
);
Query OK, 0 rows affected (0.05 sec)


//第一次给qq_mail赋值的情况：
mysql> insert into student(id,sn,name,qq_mail)
values(1,101,'bit','123@qq.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+------+------+------------+
| id   | sn   | name | qq_mail    |
+------+------+------+------------+
| 1    | 101  | bit  | 123@qq.com |
+------+------+------+------------+
1 row in set (0.00 sec)


//第二次给qq_mail赋值为NULL的情况：
mysql> insert into student(id,sn,name,qq_mail)
values(2,102,'bit2',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+------+------+------------+
| id   | sn   | name | qq_mail    |
+------+------+------+------------+
| 1    | 101  | bit  | 123@qq.com |
| 2    | 102  | bit2 | NULL       |
+------+------+------+------------+
2 rows in set (0.00 sec)


//第三次不给qq_mail赋值的情况：
mysql> insert into student(id,sn,name)
values(3,103,'bit3');
Query OK, 1 row affected (0.02 sec)

mysql> select * from student;
+------+------+------+------------+
| id   | sn   | name | qq_mail    |
+------+------+------+------------+
| 1    | 101  | bit  | 123@qq.com |
| 2    | 102  | bit2 | NULL       |
| 3    | 103  | bit3 | 110@qq.com |
+------+------+------+------------+
3 rows in set (0.00 sec)
//发现不给qq_mail赋值时，系统会自动给qq_mail附上我们最开始设置的默认值

4.主键约束 primary key

NOT NULL 和 unique的结合，也就是说，当一个字段被primary key修饰后，那么这个字段就是不能为空且是独一无二的！！！一般搭配：
auto_increment,自增函数
auto_decrement,自减函数

//建表，准备工作
mysql> drop table if exists student;
Query OK, 0 rows affected (0.02 sec)


//给id字段加上主键约束
mysql> create table student(
id int primary key auto_increment,
sn int unique,
name varchar(20) NOT NULL,
qq_mail varchar(20) DEFAULT '110@qq.com'
);
Query OK, 0 rows affected (0.04 sec)


//当创建好表之后，表中没有任何的数据，当第一次执行插入的时候，当前主键，也就是ID，会自动从1开始
mysql> insert into student(sn,name,qq_mail)
values(101,'bit','123@qq.com');
Query OK, 1 row affected (0.01 sec)


mysql> select * from student;
+----+------+------+------------+
| id | sn   | name | qq_mail    |
+----+------+------+------------+
| 1  | 101  | bit  | 123@qq.com |
+----+------+------+------------+
1 row in set (0.00 sec)


//清除数据
mysql> delete from student 
where id = 1;
Query OK, 1 row affected (0.01 sec)


mysql> select * from student;
Empty set (0.00 sec)


//当我将刚刚插入的数据删除后，再次进行插入的时候，就会在原来的基础，也就是上一次最后插入的语句的ID上开始加1
mysql> insert into student(sn,name,qq_mail)
values(101,'bit','123@qq.com');
Query OK, 1 row affected (0.01 sec)


mysql> select * from student;
+----+------+------+------------+
| id | sn   | name | qq_mail    |
+----+------+------+------------+
| 2  | 101  | bit  | 123@qq.com |
+----+------+------+------------+
1 row in set (0.00 sec)
//如果再想让id=1，则需要删除表，重新来！！！

5.外键约束 foreign key

外键用于关联其他表的主键或唯一键
例如:熊二受熊大的管辖
语法：foreign key (字段名) references 主表(列)

//1、建表
mysql> drop table if exists classes;
Query OK, 0 rows affected, 1 warning (0.00 sec)

    
//创建classes班级表，id为主键
mysql> create table classes(
id int primary key auto_increment,
name varchar(20),
`desc` varchar(30)
);
Query OK, 0 rows affected (0.04 sec)


mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)


//再创建学生表student，一个学生对应一个班级，一个班级对应多个学生。使用id为主键，classes_id为外键，关联班级表id
mysql> create table student(
id int primary key auto_increment,
sn int unique,
name varchar(20) NOT NULL,
qq_mail varchar(20) default '110@qq.com',
classes_id int,
foreign key (classes_id) references classes(id)
);
Query OK, 0 rows affected (0.05 sec)


//查看建表成功
mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| classes				  |
| student				  |
+-------------------------+
2 rows in set (0.00 sec)


//2.插入（可以自增长）
//先插入主表
mysql> insert into classes (name,`desc`) 
values('java18','今天也要加油鸭！');
Query OK, 1 row affected (0.00 sec)


mysql> select * from classes;
+----+--------+--------------------------+
| id | name   | desc					 |
+----+--------+--------------------------+
| 1  | java18 | 今天也要加油鸭！			|
+----+--------+--------------------------+
1 row in set (0.00 sec)


//再插入附表
mysql> insert into student (sn,name,qq_mail,classes_id) 
values(101,'bit','123@qq.com',1);
mysql> insert into student (sn,name,qq_mail,classes_id) values
(102,'bit','365@qq.com',1);
Query OK, 1 row affected (0.01 sec)


mysql> select * from student;
+----+------+------+------------+------------+
| id | sn   | name | qq_mail    | classes_id |
+----+------+------+------------+------------+
| 1  | 101  | bit  | 123@qq.com | 1          |
| 2  | 102  | bit  | 365@qq.com | 1          |  
+----+------+------+------------+------------+
2 rows in set (0.00 sec)
//其中classes_id是和班级id有关系的,即这些学生都是这一个班级的学生


//3、删除规则
//先删除子表中与主表想关联的，或者删除没有与子表关联的主表,否则会报错
//假如班级不在了,那么学生也没有地方上课了.反之,学生先走了,但是不会影响班级的存在
mysql> delete from student 
where id = 1 or id = 2;
Query OK, 1 row affected (0.01 sec)


mysql> select * from student;
Empty set (0.00 sec)


//再删除主表
mysql> delete from classes 
where id = 1;
Query OK, 1 row affected (0.01 sec)


mysql> select * from classes;
Empty set (0.00 sec)

6.check约束

检查约束可用来实施一些简单的规则，比如列值必须在某个范围内。

mysql> drop table if exists test_user;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> create table test_user (
id int,
name varchar(20),
sex varchar(1),
check (sex ='男' or sex='女') //表示在sex只能插入‘男’or‘女’，否则系统报错
);
Query OK, 0 rows affected (0.04 sec)


mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| classes                 |
| student                 |
| test_user               |
+-------------------------+
3 rows in set (0.00 sec)


mysql> insert into test_user 
values(1,'bit','哈');
Query OK, 1 row affected (0.01 sec)
//但是在MySQL上并不会报错！！！

2、进阶查询

建表，准备工作

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| huashanzhizai      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


mysql> use huashanzhizai;
Database changed


mysql> show tables;
+-------------------------+
| Tables_in_huashanzhizai |
+-------------------------+
| classes                 |
| exam                    |
| student                 |
| test_user               |
+-------------------------+
4 rows in set (0.00 sec)


mysql> select * from exam;
Empty set (0.00 sec)


mysql> drop table if exists exam;
Query OK, 0 rows affected (0.03 sec)


mysql> create table exam (
id int,
name varchar(20),
chinese decimal(3,1),
math decimal(3,1),
english decimal(3,1)
);
Query OK, 0 rows affected (0.10 sec)


mysql> insert into exam (id,name, chinese, math, english) values
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0


mysql> select * from exam;
+------+-----------+---------+------+---------+
| 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.00 sec)

1.聚合函数

mysqlsh应用程序错误 mysql 应用_java

函数	说明
count（列）	求数据的数量
sum（数字列）	求总和，总分等
avg（数字列）	求某一列平均数
max（列）	求最大值
min（列）	求最小值

//1、查询数据的数量
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
| 7		   |
+----------+
1 row in set (0.01 sec)


//2、sum 加权求和
mysql> select sum(math) from exam;
+-----------+
| sum(math) |
+-----------+
| 679.5     |
+-----------+
1 row in set (0.00 sec)

mysql> select sum(math)from exam where math < 70;
+-----------+
| sum(math) |
+-----------+
| 65.0      |
+-----------+
1 row in set (0.00 sec)


//3、avg 查询平均成绩
mysql> select avg(math) from exam;
+-----------+
| avg(math) |
+-----------+
| 84.93750  |
+-----------+
1 row in set (0.00 sec)


//4、max 查询最大值
mysql> select max(math) from exam;
+-----------+
| max(math) |
+-----------+
| 98.5      |
+-----------+
1 row in set (0.00 sec)


//5、min 查询最小值
mysql> select min(math) from exam;
+-----------+
| min(math) |
+-----------+
| 65.0      |
+-----------+
1 row in set (0.00 sec)

注意点:在where后面，不要出现聚合函数

mysql> select id,name 
from exam 
where max(math) > 60;
ERROR 1111 (HY000): Invalid use of group function

2.分组 group by

准备数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| huashanzhizai      |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


mysql> use huashanzhizai;
Database changed


mysql> drop tables if exists emp;
Query OK, 0 rows affected, 1 warning (0.01 sec)


mysql> create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
Query OK, 0 rows affected (0.08 sec)


mysql> insert into emp(name, role, salary) values
('鹏哥','讲师', 1000.20),
('高博','讲师', 2000.99),
('老汤','讲师', 999.11),
('静静','班主任', 333.5),
('莎莎姐','班主任', 700.33),
('隔壁老王','市场', 12000.66);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0


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)

查询每个角色的最高工资、最低工资和平均工资

mysql> select role,
max(salary),
min(salary),
avg(salary) 
from emp 
group by role;
+-----------+-------------+-------------+--------------+
| role      | max(salary) | min(salary) | avg(salary)  |
+-----------+-------------+-------------+--------------+
| 市场       | 12000.66    | 12000.66    | 12000.660000 |
| 班主任     | 700.33      | 333.50      | 516.915000   | //同为班主任，这个类的最高，最低，平均
| 讲师       | 2000.99     | 999.11      | 1333.433333  |
+-----------+-------------+-------------+--------------+
3 rows in set (0.03 sec)


//不正确表达
//区别
mysql> select max(salary),
min(salary),
avg(salary)
from emp;
+-------------+-------------+-------------+
| max(salary) | min(salary) | avg(salary) |
+-------------+-------------+-------------+
| 12000.66    | 333.50      | 2839.131667 | //这个是在一个表中的最高，最低，平均
+-------------+-------------+-------------+
1 row in set (0.00 sec)

过滤条件 having

mysql> select role,
max(salary),
min(salary),
avg(salary) 
from emp 
group by role
having avg(salary) < 1500;
+-----------+-------------+-------------+-------------+
| role      | max(salary) | min(salary) | avg(salary) |
+-----------+-------------+-------------+-------------+
| 班主任     | 700.33      | 333.50      | 516.915000  |
| 讲师       | 2000.99     | 999.11      | 1333.433333 |
+-----------+-------------+-------------+-------------+
2 rows in set (0.00 sec)

3.联合查询

（两张表或者两张以上的表，进行连接查询）

准备工作

1.//新建库test0311
mysql> drop database if exists test0311;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> create database test0311;
Query OK, 1 row affected (0.01 sec)


mysql> use test0311;
Database changed


mysql> drop table if exists classes;
Query OK, 0 rows affected, 1 warning (0.00 sec)


2.//在test0311库里面创建classes班级表
mysql> create table classes(
id int primary key auto_increment,
name varchar(50),
`desc` varchar(50)
);
Query OK, 0 rows affected (0.07 sec)


mysql> insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0


//在test0311库里面创建student学生表
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> create table student(
id int primary key auto_increment,
sn int,
name varchar(30),
qq_mail varchar(30),
classes_id int
);
Query OK, 0 rows affected (0.09 sec)


mysql> insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0


//在test0311库里面创建course课表
mysql> drop table if exists course;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> create table course(
id int primary key auto_increment,
name varchar(20)
);
Query OK, 0 rows affected (0.05 sec)


mysql> insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0


//在test0311库里面创建score分数表
mysql> drop table if exists score;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> create table score(
id int primary key auto_increment,
score DECIMAL,
student_id int,
course_id int
);
Query OK, 0 rows affected (0.04 sec)


mysql> insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖-> (60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);
Query OK, 20 rows affected, 3 warnings (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 3


3.//查看已建好的各种表
mysql> show tables;
+--------------------+
| Tables_in_test0311 |
+--------------------+
| classes            |
| course             |
| score              |
| student            |
+--------------------+
4 rows in set (0.00 sec)


//查询classes班级表
mysql> select * from classes;
+----+-------------------------+-------------------------------------------------------------------+
| id | name                    |desc 												    	   |
+----+-------------------------+-------------------------------------------------------------------+
| 1 | 计算机系2019级1班          | 学习了计算机原理、C和Java语言、数据结构和算法					   |
| 2 | 中文系2019级3班            | 学习了中国传统文学											   |
| 3 | 自动化2019级5班            | 学习了机械自动化											   |
+----+-------------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)


//查询course课表
mysql> select * from course;
+----+--------------------+
| id | name               |
+----+--------------------+
| 1  | Java               |
| 2  | 中国传统文化        |
| 3  | 计算机原理          |
| 4  | 语文               |
| 5  | 高阶数学            |
| 6  | 英文                |
+----+--------------------+
6 rows in set (0.00 sec)


//查询score分数表
mysql> select * from score;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 1  | 71    | 1          | 1         |
| 2  | 99    | 1          | 3         |
| 3  | 33    | 1          | 5         |
| 4  | 98    | 1          | 6         |
| 5  | 60    | 2          | 1         |
| 6  | 60    | 2          | 5         |
| 7  | 33    | 3          | 1         |
| 8  | 68    | 3          | 3         |
| 9  | 99    | 3          | 5         |
| 10 | 67    | 4          | 1         |
| 11 | 23    | 4          | 3         |
| 12 | 56    | 4          | 5         |
| 13 | 72    | 4          | 6         |
| 14 | 81    | 5          | 1         |
| 15 | 37    | 5          | 5         |
| 16 | 56    | 6          | 2         |
| 17 | 43    | 6          | 4         |
| 18 | 79    | 6          | 6         |
| 19 | 80    | 7          | 2         |
| 20 | 92    | 7          | 6         |
+----+-------+------------+-----------+
20 rows in set (0.00 sec)


//查询student学生表
mysql> select * from student;
+----+-------+-----------------+------------------+------------+
| id | sn    | name            | qq_mail          | classes_id |
+----+-------+-----------------+------------------+------------+
| 1  | 9982  | 黑旋风李逵        | xuanfeng@qq.com | 1         |
| 2  | 835   | 菩提老祖         | NULL             | 1         |
| 3  | 391   | 白素贞           | NULL             | 1         |
| 4  | 31    | 许仙            | xuxian@qq.com     | 1         |
| 5  | 54    | 不想毕业         | NULL             | 1         |
| 6  | 51234 | 好好说话         | say@qq.com       | 2         |
| 7  | 83223 | tellme          | NULL             | 2         |
| 8  | 9527  | 老外学中文       | foreigner@qq.com | 2         |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.00 sec)

内连接

语法1：select 字段 from 表1 别名1 inner join 表2 别名2 on 连接条件 and 其他条件；
语法2：select 字段 from 表1 别名1，表2 别名2 where 连接条件 and 其他条件；

查询“许仙”同学的成绩（逐渐增加约束条件）

语法1演示

查询student和score两个表所有的笛卡尔积(此方法慎用,影响性能)

mysql> select * from student 
inner join score;
...//太多，就不一一展示了
...
...
160 rows in set (0.01 sec)

显示student表中id和score表中student_id两个相等的对应的值

mysql> select * 
from student 
inner join score 
on student.id = score.student_id;
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| id | sn    | name            | qq_mail         | classes_id | id | score |student_id  | course_id |
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| 1  | 9982  | 黑旋风李逵 	   | xuanfeng@qq.com | 1		 | 1  | 71    | 1  		  | 1 		  |
| 1  | 9982  | 黑旋风李逵 	   | xuanfeng@qq.com | 1          | 2  | 99    | 1  	   | 3		   |
| 1  | 9982  | 黑旋风李逵	   | xuanfeng@qq.com | 1 		  | 3  | 33    | 1   	  | 5  		  |
| 1  | 9982  | 黑旋风李逵 	   | xuanfeng@qq.com | 1 		  | 4  | 98    | 1   	  | 6  		  |
| 2  | 835   | 菩提老祖 	    | NULL 			 | 1		  | 5  | 60    | 2   	  | 1  		  |
| 2  | 835   | 菩提老祖 	    | NULL 			 | 1 		  | 6  | 60    | 2   	  | 5  		  |
| 3  | 391   | 白素贞 		     | NULL		      | 1 		   | 7  | 33    | 3   	    | 1  	    |
| 3  | 391   | 白素贞 		     | NULL 		  | 1 		   | 8  | 68    | 3  	    | 3  	    |
| 3  | 391   | 白素贞		     | NULL 		  | 1		   | 9  | 99    | 3   	    | 5  	   |
| 4  | 31    | 许仙 		      | xuxian@qq.com   | 1 		| 10 | 67    | 4  		 | 1  		|
| 4  | 31    | 许仙 		      | xuxian@qq.com   | 1		    | 11 | 23    | 4  		 | 3  		|
| 4  | 31    | 许仙			  | xuxian@qq.com   | 1 	    | 12 | 56    | 4 		 | 5  		|
| 4  | 31    | 许仙			  | xuxian@qq.com   | 1		    | 13 | 72    | 4 		 | 6  		|
| 5  | 54    | 不想毕业		     | NULL			  | 1		  | 14 | 81    | 5  	  | 1  		  | 
| 5  | 54    | 不想毕业		     | NULL 		  | 1 		  | 15 | 37    | 5  	  | 5  		  |
| 6  | 51234 | 好好说话		     | say@qq.com 	   | 2		  | 16 | 56	   | 6  	   | 2  	  |
| 6  | 51234 | 好好说话 		 | say@qq.com 	   | 2 		  | 17 | 43    | 6  	   | 4  	  |
| 6  | 51234 | 好好说话 		 | say@qq.com 	  | 2 		  | 18 | 79    | 6  	   | 6  	  |
| 7  | 83223 | tellme  	 	     | NULL			 | 2 		 | 19 | 80    | 7  		  | 2  		 |
| 7  | 83223 | tellme 		     | NULL			 | 2 	     | 20 | 92    | 7  		  | 6  		 |
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
20 rows in set (0.01 sec)

显示student和score两个表的所有内容

mysql> select * from student 
inner join score 
on student.id = score.student_id
and student.id = 4;
+----+------+--------+---------------+------------+----+-------+------------+-----------+
| id | sn   | name   | qq_mail       | classes_id | id | score | student_id |course_id  |
+----+------+--------+---------------+------------+----+-------+------------+-----------+
| 4  | 31   | 许仙   | xuxian@qq.com  | 1          | 10 | 67   | 4			| 1  		|
| 4  | 31   | 许仙   | xuxian@qq.com  | 1          | 11 | 23   | 4			| 3  		|
| 4  | 31   | 许仙   | xuxian@qq.com  | 1          | 12 | 56   | 4			| 5  		|
| 4  | 31   | 许仙   | xuxian@qq.com  | 1          | 13 | 72   | 4			| 6  		|
+----+------+--------+---------------+------------+----+-------+------------+-----------+
4 rows in set (0.01 sec)

显示student和score两个表的选定的内容

mysql> select student.id,student.name,score.score 
from student 
inner join score
on student.id = score.student_id 
and student.id = 4;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
| 4  | 许仙   | 67    |
| 4  | 许仙   | 23    |
| 4  | 许仙   | 56    | 
| 4  | 许仙   | 72    |
+----+--------+-------+
4 rows in set (0.00 sec)

用score表的course_id将course表也连接起来

mysql> select student.id,student.name,score.score,course.name 
from student 
inner join score 
on student.id = score.student_id 
inner join course 
on score.course_id = course.id
and student.id = 4;
+----+--------+-------+-----------------+
| id | name   | score | name            |
+----+--------+-------+-----------------+
| 4  | 许仙   | 67     | Java 	      |
| 4  | 许仙   | 23     | 计算机原理 	  |
| 4  | 许仙   | 56     | 高阶数学 	   |
| 4  | 许仙   | 72     | 英文 			|
+----+--------+-------+-----------------+
4 rows in set (0.00 sec)

修改名字

mysql> select student.id,student.name as 姓名,score.score as 分数,course.name as 课程表
from student 
inner join score 
on student.id = score.student_id 
inner join course
on score.course_id = course.id 
and student.id = 4;
+----+--------+--------+-----------------+
| id | 姓名    | 分数   | 课程表           |
+----+--------+--------+-----------------+
| 4  | 许仙    | 67    | Java 			 |
| 4  | 许仙    | 23    | 计算机原理 		 |
| 4  | 许仙    | 56    | 高阶数学		 |
| 4  | 许仙    | 72    | 英文			 |
+----+--------+--------+-----------------+
4 rows in set (0.00 sec)

语法2演示：

mysql> select student.id,student.name as 姓名,score.score as 分数,course.name as 课程表
from student,score,course 
where student.id = score.student_id
and score.course_id = course.id
and student.id = 4;
+----+--------+--------+-----------------+
| id | 姓名   | 分数    | 课程表		   |
+----+--------+--------+-----------------+
| 4  | 许仙   | 67     | Java 		   |
| 4  | 许仙   | 23	 | 计算机原理	   |
| 4  | 许仙   | 56 	 | 高阶数学 	    |
| 4  | 许仙   | 72 	 | 英文 			 |
+----+--------+--------+-----------------+
4 rows in set (0.00 sec)

外连接

（外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接）

查询所有同学的成绩，及同学的个人信息，如果该同学没有成绩，也需要显示

之前创建好的表

//查看已建好的各种表
mysql> show tables;
+--------------------+
| Tables_in_test0311 |
+--------------------+
| classes            |
| course             |
| score              |
| student            |
+--------------------+
4 rows in set (0.00 sec)


//查询classes班级表
mysql> select * from classes;
+----+-------------------------+-------------------------------------------------------------------+
| id | name                    |desc 												    	   |
+----+-------------------------+-------------------------------------------------------------------+
| 1 | 计算机系2019级1班          | 学习了计算机原理、C和Java语言、数据结构和算法					   |
| 2 | 中文系2019级3班            | 学习了中国传统文学											   |
| 3 | 自动化2019级5班            | 学习了机械自动化											   |
+----+-------------------------+-------------------------------------------------------------------+
3 rows in set (0.00 sec)


//查询course课表
mysql> select * from course;
+----+--------------------+
| id | name               |
+----+--------------------+
| 1  | Java               |
| 2  | 中国传统文化        |
| 3  | 计算机原理          |
| 4  | 语文               |
| 5  | 高阶数学            |
| 6  | 英文                |
+----+--------------------+
6 rows in set (0.00 sec)


//查询score分数表
mysql> select * from score;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 1  | 71    | 1          | 1         |
| 2  | 99    | 1          | 3         |
| 3  | 33    | 1          | 5         |
| 4  | 98    | 1          | 6         |
| 5  | 60    | 2          | 1         |
| 6  | 60    | 2          | 5         |
| 7  | 33    | 3          | 1         |
| 8  | 68    | 3          | 3         |
| 9  | 99    | 3          | 5         |
| 10 | 67    | 4          | 1         |
| 11 | 23    | 4          | 3         |
| 12 | 56    | 4          | 5         |
| 13 | 72    | 4          | 6         |
| 14 | 81    | 5          | 1         |
| 15 | 37    | 5          | 5         |
| 16 | 56    | 6          | 2         |
| 17 | 43    | 6          | 4         |
| 18 | 79    | 6          | 6         |
| 19 | 80    | 7          | 2         |
| 20 | 92    | 7          | 6         |
+----+-------+------------+-----------+
20 rows in set (0.00 sec)


//查询student学生表
mysql> select * from student;
+----+-------+-----------------+------------------+------------+
| id | sn    | name            | qq_mail          | classes_id |
+----+-------+-----------------+------------------+------------+
| 1  | 9982  | 黑旋风李逵        | xuanfeng@qq.com | 1         |
| 2  | 835   | 菩提老祖         | NULL             | 1         |
| 3  | 391   | 白素贞           | NULL             | 1         |
| 4  | 31    | 许仙            | xuxian@qq.com     | 1         |
| 5  | 54    | 不想毕业         | NULL             | 1         |
| 6  | 51234 | 好好说话         | say@qq.com       | 2         |
| 7  | 83223 | tellme          | NULL             | 2         |
| 8  | 9527  | 老外学中文       | foreigner@qq.com | 2         |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.00 sec)

左外连接

mysql> select * from student,score;
...
...//结果太多不一一展示
...
160 rows in set (0.01 sec)


//通过student表中的id进行排序
mysql> select * 
from student,score 
group by student.id;
+----+-------+-----------------+------------------+------------+----+-------+------------+-----------+
| id | sn    | name            | qq_mail 		  | classes_id | id | score |student_id  | course_id |
+----+-------+-----------------+------------------+------------+----+-------+------------+-----------+
| 1 | 9982   | 黑旋风李逵 	   | xuanfeng@qq.com  | 1 		  | 1  | 71	   | 1 		    | 1 	    |
| 2 | 835    | 菩提老祖 	   | NULL 			  | 1 		  | 1  | 71	   | 1		    | 1 	     |
| 3 | 391    | 白素贞 	        | NULL 			   | 1 		   | 1  | 71	| 1 		 | 1 		 |
| 4 | 31     | 许仙 		    | xuxian@qq.com     | 1 	    | 1  | 71	  | 1 		   | 1 		   |
| 5 | 54     | 不想毕业 	   | NULL 			  | 1 		  | 1  | 71	   | 1 		    | 1 	     |
| 6 | 51234  |  好好说话        | say@qq.com	   | 2 		   | 1  | 71	| 1 		 | 1 	 	 |
| 7 | 83223  | tellme		   | NULL 			  | 2 		 | 1  | 71	  | 1 		    | 1 	    |
| 8 | 9527   | 老外学中文	   | foreigner@qq.com | 2 		  | 1  | 71	   | 1 		     | 1 		 |
+----+-------+-----------------+------------------+------------+----+-------+------------+-----------+
8 rows in set (0.01 sec)


mysql> select * 
from student,score 
where student.id = score.student_id 
group by student.id;
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| id | sn    | name 		   | qq_mail 		 | classes_id | id | score |student_id  | course_id |
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
| 1  | 9982  | 黑旋风李逵	   | xuanfeng@qq.com | 1		  | 1  | 71    | 1 			| 1 		|
| 2  | 835   | 菩提老祖 		   | NULL			 | 1		  | 5  | 60		| 2			 | 1 		|
| 3  | 391   | 白素贞		   | NULL			 | 1 		  | 7  | 33		| 3			 | 1 		|
| 4  | 31    | 许仙 		   | xuxian@qq.com   | 1 		  | 10 | 67		| 4 		| 1 		|		
| 5  | 54    | 不想毕业		   | NULL 			 | 1 		  | 14 | 81		| 5 		| 1 		|
| 6  | 51234 | 好好说话 		   | say@qq.com      | 2 		  | 16 | 56		| 6 		| 2		    |
| 7  | 83223 | tellme		   | NULL 			 | 2		  | 19 | 80		| 7 		| 2 		|
+----+-------+-----------------+-----------------+------------+----+-------+------------+-----------+
7 rows in set (0.00 sec)//利用左外连接可以将其进行显示
//此时发现student表中的name老外学中文这一项没有了，即没有score.student_id与之匹配


mysql> select * 
from student 
left join score 
on student.id = score.student_id
group by student.id;
+----+-------+-----------------+------------------+------------+------+-------+------------+-----------+
| id | sn    | name 		   | qq_mail		  | classes_id | id   | score |student_id  | course_id |
+----+-------+-----------------+------------------+------------+------+-------+------------+-----------+
| 1  | 9982  | 黑旋风李逵 	   | xuanfeng@qq.com  | 1 			| 1   | 71	  | 1 			| 1 		|
| 2  | 835   | 菩提老祖 		   | NULL 			  | 1 			| 5   | 60	  | 2 			| 1 		|
| 3  | 391   | 白素贞 		   | NULL 			  | 1 			| 7   | 33	  | 3 			| 1 		|
| 4  | 31    | 许仙 		   | xuxian@qq.com    | 1 			| 10  | 67	  | 4 			| 1 		|
| 5  | 54    | 不想毕业 		   | NULL 			  | 1 			| 14  | 81	  | 5 			| 1 		|
| 6  | 51234 | 好好说话 		   | say@qq.com       | 2			 | 16 | 56	  | 6 			| 2 		|
| 7  | 83223 | tellme		   | NULL 			  | 2 			 | 19 | 80	  | 7 			| 2 		|
| 8  | 9527  | 老外学中文 	   | foreigner@qq.com | 2 			| NULL | NULL | NULL 		| NULL 		|
+----+-------+-----------------+------------------+------------+------+-------+------------+-----------+
8 rows in set (0.00 sec)

右外连接

mysql> select * 
from score 
right join student 
on student.id = score.student_id
group by student.id;
+------+-------+------------+-----------+----+-------+-----------------+------------------+------------+
| id   | score | student_id | course_id | id | sn    |  name 			|qq_mail 		  | classes_id |
+------+-------+------------+-----------+----+-------+-----------------+------------------+------------+
| 1    | 71 	| 1 		| 1 		| 1  | 9982  | 黑旋风李逵 		|xuanfeng@qq.com  | 1			|
| 5    | 60 	| 2 		| 1 		| 2  | 835   | 菩提老祖 			|NULL 			  | 1		   |
| 7    | 33 	| 3 		| 1 		| 3  | 391   | 白素贞			 |NULL 			  | 1 			|
| 10   | 67 	| 4 		| 1 		| 4  | 31    | 许仙 			|xuxian@qq.com    | 1 			|
| 14   | 81 	| 5 		| 1 		| 5  | 54    | 不想毕业 			|NULL 			  | 1 			|
| 16   | 56 	| 6 		| 2 		| 6  | 51234 | 好好说话 			|say@qq.com 	  | 2 			|
| 19   | 80 	| 7 		| 2 		| 7  | 83223 | tellme 			|NULL			  | 2 			|
| NULL | NULL   | NULL 	    | NULL 		| 8  | 9527  | 老外学中文 		|foreigner@qq.com | 2 			|
+------+-------+------------+-----------+----+-------+-----------------+------------------+------------+
8 rows in set (0.00 sec)

自连接

自连接是指在同一张表连接自身进行查询

显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

mysql> select * 
from score as s1,score as s2 //这时候因为两个表名字一样，所以需要起别名
where s1.student_id = 1
and s2.student_id = 3
and s1.score < s2.score;
+----+-------+------------+-----------+----+-------+------------+-----------+
| id | score | student_id | course_id | id | score | student_id | course_id |
+----+-------+------------+-----------+----+-------+------------+-----------+
| 3  | 33 	 | 1 		  | 5		  | 8  | 68    | 3		    | 3 		|
| 1  | 71	 | 1 		  | 1 		  | 9  | 99    | 3 		    | 5 		|
| 3  | 33	 | 1 		  | 5 		  | 9  | 99    | 3		    | 5 		|
| 4  | 98 	 | 1		  | 6 		  | 9  | 99    | 3		    | 5 		|
+----+-------+------------+-----------+----+-------+------------+-----------+
4 rows in set (0.00 sec)


//改进
mysql> select s2.* 
from score as s1,score as s2
where s1.student_id = 1
and s2.student_id = 3
and s1.score < s2.score;
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 8  | 68    | 3 		  | 3 		  |
| 9  | 99 	 | 3 		  | 5		  |
| 9  | 99	 | 3		  | 5		  |
| 9  | 99 	 | 3 		  | 5		  |
+----+-------+------------+-----------+
4 rows in set (0.00 sec)

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

查询与“不想毕业” 同学的同班同学

mysql> select * 
from student 
where classes_id = (select classes_id from student where name = '不想毕业');
+----+------+-----------------+-----------------+------------+
| id | sn   | name 			  | qq_mail 		| classes_id |
+----+------+-----------------+-----------------+------------+
| 1  | 9982 | 黑旋风李逵 	  | xuanfeng@qq.com | 1 		 |
| 2  | 835  | 菩提老祖 		  | NULL 		    | 1 		 |
| 3  | 391  | 白素贞			  | NULL 			| 1			 |
| 4  | 31   | 许仙 			  | xuxian@qq.com   | 1 		 |
| 5  | 54   | 不想毕业		  | NULL 			| 1 		 |
+----+------+-----------------+-----------------+------------+
5 rows in set (0.01 sec)


//这句话等价于
mysql> select * 
from student
where classes_id = 1;
+----+------+-----------------+-----------------+------------+
| id | sn   | name 			  | qq_mail 		| classes_id |
+----+------+-----------------+-----------------+------------+
| 1  | 9982 | 黑旋风李逵		  | xuanfeng@qq.com | 1 		 |
| 2  | 835  | 菩提老祖 		  | NULL 			| 1			 |
| 3  | 391  | 白素贞 		  | NULL		    | 1			 |
| 4  | 31   | 许仙 			  | xuxian@qq.com   | 1			 |
| 5  | 54   | 不想毕业 		  | NULL 		    | 1			 |
+----+------+-----------------+-----------------+------------+
5 rows in set (0.00 sec)


//多行子查询：返回多行记录的子查询,查询“语文”或“英文”课程的成绩信息使用in
mysql> select * 
from score 
where course_id 
in(select id from course where name =('语文' or name='英文');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 4  | 98    | 1		  | 6 		  |
| 13 | 72    | 4 		  | 6		  |
| 17 | 43    | 6 		  | 4		  |
| 18 | 79    | 6		  | 6		  |
| 20 | 92    | 7		  | 6		  |
+----+-------+------------+-----------+
5 rows in set (0.00 sec)


//使用 not in
mysql> select * 
from score 
where course_id 
not in(select id from course where name != '语文' and name !='英文');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 4  | 98    | 1 		  | 6		  |
| 13 | 72    | 4 		  | 6		  |
| 17 | 43    | 6 	 	  | 4		  |
| 18 | 79    | 6 		  | 6		  |
| 20 | 92    | 7 		  | 6		  |
+----+-------+------------+-----------+
5 rows in set (0.00 sec)


//使用not exists（只要括号内的表达式为true，则就执行括号外的语句）
mysql> select * 
from student 
where exists(select id from student where id = 1);
+----+-------+-----------------+------------------+------------+
| id | sn    | name 		   | qq_mail		  | classes_id |
+----+-------+-----------------+------------------+------------+
| 1  | 9982  | 黑旋风李逵 	   | xuanfeng@qq.com  | 1 		   |
| 2  | 835   | 菩提老祖 		   | NULL 			  | 1		   |
| 3  | 391   | 白素贞		   | NULL 			  | 1 		   |
| 4  | 31    | 许仙			   | xuxian@qq.com    | 1 		   |
| 5  | 54    | 不想毕业		   | NULL 			  | 1 		   |
| 6  | 51234 | 好好说话 		   | say@qq.com       | 2 		   |
| 7  | 83223 | tellme 		   | NULL 		      | 2 		   |
| 8  | 9527  | 老外学中文 	   | foreigner@qq.com | 2 		   |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.02 sec)


//联合查询（求并集,会自动去掉结果集中的重复行）
mysql> select * 
from student 
where id <= 3
union
select * from student where name = '白素贞';
+----+------+-----------------+-----------------+------------+
| id | sn   | name 			  | qq_mail  		| classes_id |
+----+------+-----------------+-----------------+------------+
| 1  | 9982 | 黑旋风李逵		  | xuanfeng@qq.com | 1 		 |
| 2  | 835  | 菩提老祖 		  | NULL		    | 1 		 |
| 3  | 391  | 白素贞 		  | NULL 		    | 1			 |
+----+------+-----------------+-----------------+------------+
3 rows in set (0.01 sec)


mysql> select * 
from student 
where id <= 3
union
select * from student where name = '好好说话';
+----+-------+-----------------+-----------------+------------+
| id | sn    | name 		   | qq_mail  	     | classes_id |
+----+-------+-----------------+-----------------+------------+
| 1  | 9982  | 黑旋风李逵  	   | xuanfeng@qq.com | 1 		  |
| 2  | 835   | 菩提老祖 		   | NULL		     | 1 		  |
| 3  | 391   | 白素贞 		   | NULL			 | 1 		  |
| 6  | 51234 | 好好说话 		   | say@qq.com	     | 2		  |
+----+-------+-----------------+-----------------+------------+
4 rows in set (0.00 sec)

四、数据库索引

1.何为索引？

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

通俗一点讲：索引相当于目录。

2.为什么要使用索引？

例如

在没有索引的情况下，要求数据库在10000个学生名单中搜索一个学生的名字，当数据库搜索到一个之后，他并不会停止检索，而是将整个数据库全部遍历一遍才可以，这样的话就十分损耗性能。

3.索引的优点

可以大大加快检索数据的速度。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
在分组和排序子句的过程中，可以减少查询过程中分组和排序的时间。
可以在查询过程中，使用优化隐藏器，提高系统的性能。

4.索引的缺点

空间方面：索引需要占用一定的数据空间。
时间方面：在进行数据库表的增加,删除和修改时都需要耗费时间（相当于在B树/B+树上插入和删除）,使性能降低。

5.索引使用的场景

经常需要搜索的列。
作为主键的列。
经常用在连接的列,这些列主要是一些外键,可以加快连接的速度。
经常需要根据范围进行搜索的列上。
经常需要排序的列上。
经常使用在where子句上面的列上。

6.避免使用索引的场景

查询过程中很少用到列的情况。
对于那些具有很少数据值的列。比如人事表的性别列,bit数据类型的列。
对于那些定义为text,image的列。因为这些列的数据量相当大。
当对修改性能的要求远远大于搜索性能时。因为当增加索引时,会提高搜索性能,但是会降低修改性能（相当于在B树/B+树上插入和删除）。

7.索引的类型

普通索引

基本的索引类型，没有唯一性，为了加速查询使用。创建表的时候直接指定。

create table mytable(  

ID int not NULL,   

username varchar(16) not NULL,  

index [indexName] (username(length))  

);

删除索引

drop index [indexName] on mytable;

唯一索引

数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

create table mytable(  

ID int not NULL,   

username varchar(16) not NULL,  

unique [indexName] (username(length))  

);

主键索引

数据列不允许重复，不允许为NULL，一个表只有一个主键。一般是在创建表的时候直接指定。

mysql> create table user (
id int unsigned not NULL auto_increment,
name varchar(50) not NULL,
email varchar(50) not NULL,
primary key(id)
);


mysql> insert into user(name,email)values
('张三','111111@qq.com'),
('李四','22222@qq.com');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from user;
+----+--------+---------------+
| id | name   | email         |
+----+--------+---------------+
|  1 | 张三   | 111111@qq.com |
|  2 | 李四   | 22222@qq.com  |
+----+--------+---------------+
2 rows in set (0.00 sec)

全文索引

是目前搜索引擎使用的一种关键技术。

8.通配符失效

通配符在搜索词首出现时,索引失效。

select * from student where name like '%huasahnzhizai';

在索引列上使用not，会采用全表扫描。

select * from student where not (score=100);

select * from student where score <> 100;

select * from student where score != 100;

select * from student where score not in (80,90,100);

//not exist也不走索引

当数据类型是字符串类型的时候，如果条件数据没有被引号引起来，索引失效。

select * from student where dept_id = 1
    //应该使用：
    select * from student where dept_id = '1'

当使用or的情况下，如果不是每一列的条件都有索引，索引失效。

//student表内name建立了索引，sex没有建索引
select * from student where name = 'zhangsan' or sex = 1

组合索引，不是使用第一列索引，索引失效。

//建立组合索引（key1，key2）;
select * from key1 = 1;--组合索引有效；
select * from key1 = 1 and key2= 2;--组合索引有效；
select * from key2 = 2;--组合索引失效；不符合最左前缀原则

当全表扫描速度比索引速度快时，数据库会使用全表扫描，此时索引失效。

9.索引使用的数据结构

为了尽量减少I/O操作，计算机系统一般采取磁盘预读的方式，预读的长度一般为页（page）的整倍数。每个存储块称为一页。

B 树（多路平衡搜索树）

B树中每个节点包含了键值和键值对于的数据对象存放地址指针，所以成功搜索一个对象可以不用到达树的叶节点。

假如我们要查找 id=28 的用户信息，那么我们在上图 B 树中查找的流程如下：

先找到根节点页 1，判断 28 在键值 17 和 35 之间，根据页 1 中的指针 p2 找到页 3。（1次IO）
将 28 和页 3 中的键值相比较，28 在 26 和 30 之间，根据页 3 中的指针 p2 找到页 8。（1次IO）
将 28 和页 8 中的键值相比较，发现匹配的键值 28，键值 28 对应的用户信息为（28，zx）。（1次IO）

B+树

B树与B+树的不同

(1) B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。
(2)B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

五、事务

1.为什么使用事务？

准备测试表

drop table if exists accout;
create table accout(
id int primary key auto_increment,
name varchar(20) comment '账户名称',
money decimal(11,2) comment '金额'
);
insert into accout(name, money) values
('阿里巴巴', 5000),
('四十大盗', 1000);

比如说，四十大盗把从阿里巴巴的账户上偷盗了2000元

//阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
//四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗'

假如在执行以上第一句SQL时，出现网络错误，或是数据库挂掉了，阿里巴巴的账户会减少2000，但是四十大盗的账户上就没有了增加的金额。

解决方案：使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败。

2.事务的概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。

在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

3.事务的特性(ACID)

原子性(Atomicity)：指事务包含的所有操作要么全部成功，要么全部失败。
一致性(Consistency)：数据提交后的状态合集称为一致，也就是数据库只包含事务提交的状态。
隔离性(Isolation)：并发，对于任意两个并发的事务A和B，在事务A看来，B要么在A开始之前就已经结束，要么在A结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
持久性(Durability)：指一个事务一旦提交了，就保存到硬盘上，对数据库中的数据的改变就是永久性的。

4.事务的使用

开启事务：start transaction;
执行多条SQL语句
回滚或提交：rollback/commit;

rollback即是全部失败，commit即是全部成功

start transaction;
-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';
commit;

完!

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：Vivo手机安装谷歌套件服务框架 voov国际版安卓下载

下一篇：springcloud docker 如何指定register地址 springcloud docker自动部署

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯