- Mysql实战
- 1 终端操作数据库
- 2 数据库建表约束
- 3 数据表设计范式
- 4 查询练习
- 4.1 数据准备
- 4.2 1到10
- 4.3 10过程解析
- 4.4 分组计算平均成绩
- 4.5 分组条件与模糊查询
- 4.6 范围查询的两种方式
- 4.7 多表查询
- 4.8 三表关联查询
- 4.9 子查询加分组求平均分
- 4.10 子查询
- 4.11 year函数与待in关键字的子查询
- 4.12 多层嵌套子查询
- 4.13 多表查询
- 4.14 in表示或者关系
- 4.15 where条件查询
- 4.16 子查询
- 4.17 union和notin的使用
- 4.18 any表示至少一个desc降序
- 4.19 all表示所有
- 4.20 as取别名-union求并集
- 4.21 union求并集
- 4.22 复制表数据做条件查询
- 4.23 子查询
- 4.24 条件加分组筛选
- 4.25 notlike模糊查询取反
- 4.26 year函数与now函数
- 4.27 max与min函数
- 4.28 多字段排序
- 4.29 子查询
- 4.30 max函数与子查询
- 4.31 子查询
- 4.32 按等级查询
- 5 索引
- 6 连接查询
- 7 事务
- 8 事务隔离性
关系型数据库
1 终端操作数据库
1.1 登陆数据库
mysql -u root -p 83848521
1.2 查询数据库服务器中所有的数据
show databases;
#显示
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
1.3 选中某一个数据库进行操作
use sys;
#显示
mysql> use sys
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
1.4 退出数据库服务器
mysql> exit
Bye
1.5 在数据库服务器中创建数据库
create database test;
#显示
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
1.6 查看某个数据库中所有的数据表
show tables;
#显示
mysql> show tables;
Empty set (0.00 sec)
1.7 创建一个数据表
create table pet (
name varchar(20),
owner varchar(20),
species varchar(20),
sex char(1),
birth date,
death date
);
#显示
mysql> create table pet (
-> name varchar(20),
-> owner varchar(20),
-> species varchar(20),
-> sex char(1),
-> birth date,
-> death date);
Query OK, 0 rows affected (0.00 sec)
1.8 查看数据表是否创建成功
show tables;
#显示
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
+----------------+
1 row in set (0.00 sec)
1.9 查看创建好的数据表的结构
describe pet;
#显示
mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
1.10 查看表中的记录
select * from pet;
#显示
mysql> select * from pet;
Empty set (0.01 sec)
1.11 往数据表中添加数据记录
insert into pet values('Puffbal', 'Diane', 'hamster', 'f', '1999-03-30', NULL);
#显示
mysql> insert into pet
-> values('Puffbal', 'Diane', 'hamster', 'f', '1999-03-30', NULL);
Query OK, 1 row affected (0.00 sec)
#再次查询
select * from pet;
#显示
mysql> select * from pet;
+---------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+---------+-------+---------+------+------------+-------+
| Puffbal | Diane | hamster | f | 1999-03-30 | NULL |
+---------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
#再次插入
insert into pet values('旺财', '周星驰', '狗', '公', '1990-01-01', NULL);
#显示
mysql> insert into pet
-> values('旺财', '周星驰', '狗', '公', '1990-01-01', NULL);
Query OK, 1 row affected (0.00 sec)
#再次查询
mysql> select * from pet;
+---------+-----------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+---------+-----------+---------+------+------------+-------+
| Puffbal | Diane | hamster | f | 1999-03-30 | NULL |
| 旺财 | 周星驰 | 狗 | 公 | 1990-01-01 | NULL |
+---------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)
1.12 mysql常用数据类型
- 数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 字节 | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 字节 | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 字节 | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 字节 | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 字节 | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
create table testType (
number tinyint
);
#显示
mysql> create table testType (
-> number tinyint
-> );
Query OK, 0 rows affected (0.00 sec)
#查看表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
| testType |
+----------------+
2 rows in set (0.01 sec)
#查看
mysql> describe testType;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| number | tinyint | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
#插入值超过表示范围
mysql> insert into testType values(128);
ERROR 1264 (22003): Out of range value for column 'number' at row 1
- 日期和时间类型
类型 | 大小 (字节) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
- 字符串类型
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
TEXT | 0-65 535字节 | 长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
1.13 数据类型如何选择
- 日期选择按照格式
- 数值和字符串按照大小
1.14 删除数据
delete from pet where name = 'Puffbal';
#显示
mysql> delete from pet where name = 'Puffbal';
Query OK, 1 row affected (0.00 sec)
#查看
mysql> select * from pet;
+--------+-----------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+-----------+---------+------+------------+-------+
| 旺财 | 周星驰 | 狗 | 公 | 1990-01-01 | NULL |
+--------+-----------+---------+------+------------+-------+
1 row in set (0.00 sec)
#已删除
1.15 修改数据
update pet set name = '旺旺财' where owner = '周星驰';
#显示
mysql> update pet set name = '旺旺财' where owner = '周星驰';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#查看
mysql> select * from pet;
+-----------+-----------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-----------+-----------+---------+------+------------+-------+
| 旺旺财 | 周星驰 | 狗 | 公 | 1990-01-01 | NULL |
+-----------+-----------+---------+------+------------+-------+
1 row in set (0.00 sec)
#已修改
总结
- 数据库中的常见操作
#增
insert
#删
delete
#改
update
#查
select
2 数据库建表约束
2.1 主键约束
- 它能够唯一确定一张表中的一条记录,也就是我们通过给某个字段添加约束,就可以使的该字段不重复且不为空
mysql> create table user (
id int primary key,
name varchar(20)
);
#显示
mysql> create table user (
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.00 sec)
#查看
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
| testType |
| user |
+----------------+
3 rows in set (0.00 sec)
#内容
mysql> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-
联合主键
只要联合主键的主键值加起来不重复就可以
mysql> create table user2 (
id int,
name varchar(20),
password varchar(20),
primary key(id, name)
);
#显示
mysql> create table user2 (
-> id int,
-> name varchar(20),
-> passeord varchar(20),
-> primary key(id, name)
-> );
Query OK, 0 rows affected (0.00 sec)
#插入
mysql> insert into user2 values(1, '张三', '123');
Query OK, 1 row affected (0.00 sec)
#插入
mysql> insert into user2 values(2, '张三', '123');
Query OK, 1 row affected (0.00 sec)
#插入
mysql> insert into user2 values(1, '李四', '123');
Query OK, 1 row affected (0.00 sec)
2.2 自增约束
自动管控ID值,自动增加
#新建表
mysql> create table user3(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
#插入
mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
#查看
mysql> select * from user3;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
#插入
mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
#查看
mysql> select * from user3;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)
如果说创建表的时候,忘记添加主键
#新建表
mysql> create table user4 (
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
#查看
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#添加主键
mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查看
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#删除主键
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查看
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#修改主键
mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查看
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2.3 唯一约束
- 约束修饰的字段的值不可以重复
mysql> create table user5 (
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
#设置唯一性
mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查看
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#插入
mysql> insert into user5 values(1, 'zhengsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user5 values(1, 'zhengsan');
ERROR 1062 (23000): Duplicate entry 'zhengsan' for key 'user5.name'
-
直接设置唯一性
mysql> create table user6 ( -> id int, -> name varchar(20), -> unique(name) -> ); Query OK, 0 rows affected (0.00 sec) #查看 mysql> desc user6; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
-
删除唯一性
mysql> alter table user6 drop index name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 总结
1、建表的时候可以添加约束
2、可以使用alter、add添加约束
3、可以使用alter、modif添加约束
4、删除alter、drop
2.4 非空约束
- 修饰字段不能为空 NULL
mysql> create table user7 (
-> id int,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.01 sec)
#查看
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#插入
mysql> insert into user7 (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
2.5 默认约束
- 就是当我们插入字段值的时候,如果没有传值,就会使用默认值
mysql> create table user8 (
-> id int,
-> name varchar(20),
-> age int default 10
-> );
Query OK, 0 rows affected (0.01 sec)
#查看
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#插入
mysql> insert into user8 (id, name) values(1, 'zhangsan');
Query OK, 1 row affected (0.00 sec)
#查看
mysql> select * from user8;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 10 |
+------+----------+------+
1 row in set (0.00 sec)
2.6 外键约束
- 涉及到两个表:父表,子表
- 主表,副表
#班级
mysql> create table classes (
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
#学生表
mysql> create table students (
-> id int primary key,
-> name varchar(20),
-> class_id int,
-> foreign key(class_id) references classes(id)
-> );
Query OK, 0 rows affected (0.01 sec)
#查看
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#插入
mysql> insert into classes values(1, '一班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(2, '二班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into classes values(3, '三班');
Query OK, 1 row affected (0.01 sec)
mysql> insert into classes values(4, '四班');
Query OK, 1 row affected (0.00 sec)
#查看
mysql> select * from classes;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 4 | 四班 |
+----+--------+
4 rows in set (0.00 sec)
#插入
mysql> insert into students values(1001, '张三', 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(1002, '张三', 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(1003, '张三', 3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into students values(1004, '张三', 4);
Query OK, 1 row affected (0.00 sec)
#错误
mysql> insert into students values(1004, '张三', 5);
ERROR 1062 (23000): Duplicate entry '1004' for key 'students.PRIMARY'
#查看
mysql> select * from students;
+------+--------+----------+
| id | name | class_id |
+------+--------+----------+
| 1001 | 张三 | 1 |
| 1002 | 张三 | 2 |
| 1003 | 张三 | 3 |
| 1004 | 张三 | 4 |
+------+--------+----------+
4 rows in set (0.00 sec)
- 总结
- 主表(父表),classes中没有的数据值,在副表(子表)中,是不可以使用的。
- 主表中的记录被副表引用,是不可以被删除的
#错误
mysql> delete from classes where id = 4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
3 数据表设计范式
3.1 第一范式1NF
- 数据表中的字段都是不可分割的原子值?
mysql> create table student2(
-> id int primary key,
-> name varchar(30),
-> address varchar(30)
-> );
Query OK, 0 rows affected (0.00 sec)
#插入
mysql> insert into student2 values(1, '张三', '黑龙江省绥化市100');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student2 values(2, '张三', '黑龙江省绥化市99');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student2 values(3, '张三', '黑龙江省绥化市98');
Query OK, 1 row affected (0.00 sec)
#查看
mysql> select * from student2;
+----+--------+--------------------------+
| id | name | address |
+----+--------+--------------------------+
| 1 | 张三 | 黑龙江省绥化市100 |
| 2 | 张三 | 黑龙江省绥化市99 |
| 3 | 张三 | 黑龙江省绥化市98 |
+----+--------+--------------------------+
3 rows in set (0.00 sec)
#字段值还可以拆分,就不属于第一范式
#理论上,原子值拆分的越细,可操作性越好
3.2 第二范式2NF
- 满足第一范式的条件下
- 除主键外的每一列都必须完全依赖于主键
- 如果出现不完全依赖,只可能发生在联合主键的情况下
#订单表
create table myorder (
product_id int,
customer_id int,
product_name varchar(20),
customer_name varchar(20),
primary key (product_id, customer_id)
);
#问题?
#除主键外的其他列,只依赖于主键的部分字段
#拆表
create table myorder (
order_id int primary key,
product_id int,
customer_id int
);
create table product (
id int primary key,
name varchar(20)
);
create table customer (
id int primary key,
name varchar(20)
);
#分成三个表之后,就满足了第二范式的要求
3.3 第三范式3NF
- 必须满足第二范式,除主键列的其他列之间不能有传递依赖关系
create table myorder (
order_id int primary key,
product_id int,
customer_id int
product_phone varchar(15),#错误
customer_phone varchar(15),#错误
);
create table product (
id int primary key,
name varchar(20),
phone varchar(15)
);
create table customer (
id int primary key,
name varchar(20),
phone varchar(15)
);
4 查询练习
4.1 数据准备
#创建数据库
mysql> create database selectTest;
Query OK, 1 row affected (0.01 sec)
#使用数据库
mysql> use selectTest
Database changed
- 学生表
#学生表
#学生编号
#学生名字
#学生性别
#出生日期
#所在班级
create table student (
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20)
);
#显示
mysql> create table student (
-> sno varchar(20) primary key,
-> sname varchar(20) not null,
-> ssex varchar(10) not null,
-> sbirthday datetime,
-> class varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
#添加数据
insert into student values('101', '曾华', '男', '1977-09-01', '95033');
insert into student values('102', '匡明', '男', '1975-10-02', '95031');
insert into student values('103', '王丽', '女', '1976-01-23', '95033');
insert into student values('104', '李军', '男', '1976-02-20', '95033');
insert into student values('105', '王芳', '女', '1975-01-10', '95031');
insert into student values('106', '陆君', '男', '1974-06-03', '95031');
insert into student values('107', '王尼玛', '男', '1976-02-20', '95033');
insert into student values('108', '张全蛋', '女', '1975-01-10', '95031');
insert into student values('109', '赵铁柱', '男', '1974-06-03', '95031');
#查看
mysql> select * from student;
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-01-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 女 | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
- 教师表
#教师表
#教师编号
#教师名字
#教师性别
#出生年月日
#职称
#所在部门
create table teacher (
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
#显示
mysql> create table teacher (
-> tno varchar(20) primary key,
-> tname varchar(20) not null,
-> tsex varchar(20) not null,
-> tbirthday datetime,
-> prof varchar(20) not null,
-> depart varchar(20) not null
-> );
Query OK, 0 rows affected (0.01 sec)
#添加数据
insert into teacher values('804', '李成', '男', '1958-12-02', '副教授', '计算机系');
insert into teacher values('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
insert into teacher values('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
insert into teacher values('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');
#查看
mysql> select * from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李成 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)
- 课程表
#课程表
#课程号
#课程名称
#教师编号
create table course (
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
#显示
mysql> create table course (
-> cno varchar(20) primary key,
-> cname varchar(20) not null,
-> tno varchar(20) not null,
-> foreign key(tno) references teacher(tno)
-> );
Query OK, 0 rows affected (0.01 sec)
#添加数据
insert into course values('3-105', '计算机导论', '825');
insert into course values('3-245', '操作系统', '804');
insert into course values('6-166', '数字电路', '856');
insert into course values('9-888', '高等数学', '831');
#查看
mysql> select * from course;
+-------+-----------------+-----+
| cno | cname | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)
- 成绩表
#成绩表
#学号
#课程号
#成绩
create table score (
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno),
primary key (sno, cno)
);
#显示
mysql> create table score (
-> sno varchar(20) not null,
-> cno varchar(20) not null,
-> degree decimal,
-> foreign key(sno) references student(sno),
-> foreign key(cno) references course(cno),
-> primary key (sno, cno)
-> );
Query OK, 0 rows affected (0.01 sec)
#添加数据
insert into score values('103', '3-245', '86');
insert into score values('105', '3-245', '75');
insert into score values('109', '3-245', '68');
insert into score values('103', '3-105', '92');
insert into score values('105', '3-105', '88');
insert into score values('109', '3-105', '76');
insert into score values('103', '6-166', '85');
insert into score values('105', '6-166', '79');
insert into score values('109', '6-166', '81');
#查看
mysql> select * from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
9 rows in set (0.00 sec)
4.2 1到10
- 1、查询student表的所有记录
select * from student;
#显示
mysql> select * from student;
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-01-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 女 | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
- 2、查询student表中的所有记录的sname、ssex和class列
select sname,ssex,class from student;
#显示
mysql> select sname,ssex,class from student;
+-----------+------+-------+
| sname | ssex | class |
+-----------+------+-------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆君 | 男 | 95031 |
| 王尼玛 | 男 | 95033 |
| 张全蛋 | 女 | 95031 |
| 赵铁柱 | 男 | 95031 |
+-----------+------+-------+
9 rows in set (0.00 sec)
- 3、查询教师所有的单位既不重复的depart列,使用distinct
select depart from teacher;
#查看有重复
mysql> select depart from teacher;
+-----------------+
| depart |
+-----------------+
| 计算机系 |
| 计算机系 |
| 电子工程系 |
| 电子工程系 |
+-----------------+
4 rows in set (0.00 sec)
#修改命令
select distinct depart from teacher;
#查看
mysql> select distinct depart from teacher;
+-----------------+
| depart |
+-----------------+
| 计算机系 |
| 电子工程系 |
+-----------------+
2 rows in set (0.00 sec)
- 4、查询score表中的成绩在60到80之间的所有记录
#查询区间between ... and ...
select * from score where degree between 60 and 80;
#显示
mysql> select * from score where degree between 60 and 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
4 rows in set (0.00 sec)
#另一种操作,运算符比较
select * from score where degree > 60 and degree < 80;
#显示
mysql> select * from score where degree > 60 and degree < 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
4 rows in set (0.00 sec)
- 5、查询score表中成绩为85,86或88的记录
#表示的或者关系的查询 in
select * from score where degree in(85, 86, 88);
#查看
mysql> select * from score where degree in(85, 86, 88);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
+-----+-------+--------+
3 rows in set (0.00 sec)
- 6、查询student表中“95031”班或者性别为“女”的同学记录
#不同字段的或者关系 or
select * from student where class='95031' or ssex='女';
#查看
mysql> select * from student where class='95031' or ssex='女';
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-01-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 女 | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
6 rows in set (0.00 sec)
- 7、以class降序查询student表的所有记录
#升序
select * from student order by class desc;
#查看
mysql> select * from student order by class desc;
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-01-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 女 | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
#升序,一般是默认的不需要写
select * from student order by class asc;
- 8、以cno升序、degree降序查询score表的所有记录
select * from score order by cno asc, degree desc;
#查看
mysql> select * from score order by cno asc, degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
+-----+-------+--------+
9 rows in set (0.00 sec)
- 9、查询“95031”班的学生人数
#统计 count
select count(*) from student where class = '95031';
#查看
mysql> select count(*) from student where class = '95031';
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
- 10、查询score表中的最高分的学生学号和课程号。(子查询或者排序)
select sno,cno from score where degree=(select max(degree) from score);
#查看
mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)
4.3 10过程解析
#1、找到最高分
select max(degree) from score;
#2、找到最高分的sno和cno
select sno, cno from score where degree=(select max(degree) from score);
#排序的做法
select sno,cno,degree from score order by degree;
#显示
mysql> select sno,cno,degree from score order by degree;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 109 | 3-245 | 68 |
| 105 | 3-245 | 75 |
| 109 | 3-105 | 76 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
| 103 | 6-166 | 85 |
| 103 | 3-245 | 86 |
| 105 | 3-105 | 88 |
| 103 | 3-105 | 92 |
+-----+-------+--------+
9 rows in set (0.00 sec)
#limit 0,1 参数0代表从0开始,参数1代表第一个元素
select sno,cno,degree from score order by degree desc limit 0,1;
#显示
mysql> select sno,cno,degree from score order by degree desc limit 0,1;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
+-----+-------+--------+
1 row in set (0.00 sec)
4.4 分组计算平均成绩
- 11、查看每门课的平均成绩
select * from course;
#avg()
select avg(degree) from score where cno='3-105';
#显示
mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
| 85.3333 |
+-------------+
1 row in set (0.00 sec)
#使用分组 group
select cno, avg(degree) from score group by cno;
#显示
mysql> select cno, avg(degree) from score group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)
4.5 分组条件与模糊查询
- 12、查询score表中至少有两名学生选修的并以3开头的课程的平均分数
select cno from score group by cno having count(cno) >= 2;
#显示
mysql> select cno from score group by cno having count(cno) >= 2;
+-------+
| cno |
+-------+
| 3-105 |
| 3-245 |
| 6-166 |
+-------+
3 rows in set (0.00 sec)
select cno from score group by cno having count(cno) >= 2 and cno like '3%';
#显示
mysql> select cno from score group by cno having count(cno) >= 2 and cno like '3%';
+-------+
| cno |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.00 sec)
select cno,avg(degree) from score group by cno having count(cno) >= 2 and cno like '3%';
#显示
mysql> select cno,avg(degree) from score group by cno having count(cno) >= 2 and cno like '3%';
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
+-------+-------------+
2 rows in set (0.00 sec)
select cno,avg(degree),count(*) from score group by cno having count(cno) >= 2 and cno like '3%';
#显示
mysql> select cno,avg(degree),count(*) from score group by cno having count(cno) >= 2 and cno like '3%';
+-------+-------------+----------+
| cno | avg(degree) | count(*) |
+-------+-------------+----------+
| 3-105 | 85.3333 | 3 |
| 3-245 | 76.3333 | 3 |
+-------+-------------+----------+
2 rows in set (0.00 sec)
4.6 范围查询的两种方式
- 13、查询分数大于70,小于90的sno列
select sno,degree from score
where degree>70 and degree<90;
#显示
mysql> select sno,degree from score
-> where degree>70 and degree<90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
+-----+--------+
7 rows in set (0.00 sec)
#between...and...
select sno,degree from score
where degree between 70 and 90;
#显示
mysql> select sno,degree from score
-> where degree between 70 and 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
+-----+--------+
7 rows in set (0.00 sec)
4.7 多表查询
- 14、查询所有学生的sname、cno和degree列
select sno,sname from student;
#显示
mysql> select sno,sname from student;
+-----+-----------+
| sno | sname |
+-----+-----------+
| 101 | 曾华 |
| 102 | 匡明 |
| 103 | 王丽 |
| 104 | 李军 |
| 105 | 王芳 |
| 106 | 陆君 |
| 107 | 王尼玛 |
| 108 | 张全蛋 |
| 109 | 赵铁柱 |
+-----+-----------+
9 rows in set (0.00 sec)
select sno,cno,degree from score;
#显示
mysql> select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
9 rows in set (0.00 sec)
#多表查询
select sname,cno,degree from student,score
where student.sno = score.sno;
#显示
mysql> select sname,cno,degree from student,score
-> where student.sno = score.sno;
+-----------+-------+--------+
| sname | cno | degree |
+-----------+-------+--------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
+-----------+-------+--------+
9 rows in set (0.00 sec)
- 15、查询所有学生的sno、cname和degree列
select cno,cname from course;
#显示
mysql> select cno,cname from course;
+-------+-----------------+
| cno | cname |
+-------+-----------------+
| 3-105 | 计算机导论 |
| 3-245 | 操作系统 |
| 6-166 | 数字电路 |
| 9-888 | 高等数学 |
+-------+-----------------+
4 rows in set (0.00 sec)
select cno,sno,degree from score;
#显示
mysql> select cno,sno,degree from score;
+-------+-----+--------+
| cno | sno | degree |
+-------+-----+--------+
| 3-105 | 103 | 92 |
| 3-245 | 103 | 86 |
| 6-166 | 103 | 85 |
| 3-105 | 105 | 88 |
| 3-245 | 105 | 75 |
| 6-166 | 105 | 79 |
| 3-105 | 109 | 76 |
| 3-245 | 109 | 68 |
| 6-166 | 109 | 81 |
+-------+-----+--------+
9 rows in set (0.00 sec)
#多表查询
select sno,cname,degree from course,score
where course.cno = score.cno;
#显示
mysql> select sno,cname,degree from course,score
-> where course.cno = score.cno;
+-----+-----------------+--------+
| sno | cname | degree |
+-----+-----------------+--------+
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 数字电路 | 85 |
| 105 | 数字电路 | 79 |
| 109 | 数字电路 | 81 |
+-----+-----------------+--------+
9 rows in set (0.00 sec)
4.8 三表关联查询
- 16、查询所有学生的sname、cname和degree列
#sname->student
#cname->scouse
#degree->score
select sname,cname,degree from student,course,score
where student.sno=score.sno and course.cno=score.cno;
#显示
mysql> select sname,cname,degree from student,course,score
-> where student.sno=score.sno and course.cno=score.cno;
+-----------+-----------------+--------+
| sname | cname | degree |
+-----------+-----------------+--------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
+-----------+-----------------+--------+
9 rows in set (0.00 sec)
4.9 子查询加分组求平均分
- 17、查询“95031”班学生每门课的平均分
select * from student where class='95031';
#显示
mysql> select * from student where class='95031';
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-01-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 女 | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
5 rows in set (0.00 sec)
select * from score where sno in (select sno from student where class='95031');
#显示
mysql> select * from score where sno in (select sno from student where class='95031');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
6 rows in set (0.00 sec)
select cno,avg(degree) from score where sno in (select sno from student where class='95031') group by cno;
#显示
mysql> select cno,avg(degree) from score where sno in (select sno from student where class='95031') group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+-------------+
3 rows in set (0.00 sec)
4.10 子查询
- 18、查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录
select degree from score where sno='109' and cno='3-105';
#显示
mysql> select degree from score where sno='109' and cno='3-105';
+--------+
| degree |
+--------+
| 76 |
+--------+
1 row in set (0.00 sec)
#完成
select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');
#显示
mysql> select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
+-----+-------+--------+
2 rows in set (0.00 sec)
- 19、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
select * from score where degree>(select degree from score where sno='109' and cno='3-105');
#显示
mysql> select * from score where degree>(select degree from score where sno='109' and cno='3-105');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
6 rows in set (0.00 sec)
4.11 year函数与待in关键字的子查询
- 20查询学号和为108、101的同学同年出生的所有学生的sno、sname和sbirthday列
select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));
#显示
mysql> select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-01-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 女 | 1975-01-10 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
4 rows in set (0.00 sec)
4.12 多层嵌套子查询
- 21、查询“张旭”教师任课的学生成绩
#查看张旭的信息
select * from teacher where tname='张旭';
#查看张旭的课程号
select cno from course where tno=(select tno from teacher where tname='张旭');
#完成
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));
#显示
mysql> select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
3 rows in set (0.00 sec)
4.13 多表查询
- 22、查询选修某课程的同学人数对于5人的教师姓名
#数据不够插入数据
insert into score values('101', '3-105', '90');
insert into score values('102', '3-105', '91');
insert into score values('104', '3-105', '89');
#选择人数大于5的课程编号
select cno from score group by cno having count(*)>5;
#完成
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5));
#显示
mysql> select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5));
+--------+
| tname |
+--------+
| 王萍 |
+--------+
1 row in set (0.00 sec)
4.14 in表示或者关系
- 23、查询“95033”班和“95031”班全体学生的记录
#增加一个信息
insert into student values('110', '张飞', '男', '1974-06-03', '95038');
#完成
select * from student where class in ('95033', '95031');
#显示
mysql> select * from student where class in ('95033', '95031');
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-01-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 女 | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
4.15 where条件查询
- 24、查询存在有85分以上的成绩的课程cno
select cno,degree from score where degree>85;
#显示
mysql> select cno,degree from score where degree>85;
+-------+--------+
| cno | degree |
+-------+--------+
| 3-105 | 90 |
| 3-105 | 91 |
| 3-105 | 92 |
| 3-245 | 86 |
| 3-105 | 89 |
| 3-105 | 88 |
+-------+--------+
6 rows in set (0.00 sec)
4.16 子查询
- 25、查询出“计算机系”教师所教课程的成绩表
#查询老师
select * from teacher where depart='计算机系';
#显示
mysql> select * from teacher where depart='计算机系';
+-----+--------+------+---------------------+-----------+--------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+--------+------+---------------------+-----------+--------------+
| 804 | 李成 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
+-----+--------+------+---------------------+-----------+--------------+
2 rows in set (0.00 sec)
#查询课程
select * from course where tno in(select tno from teacher where depart='计算机系');
#显示
mysql> select * from course where tno in(select tno from teacher where depart=' 计算机系');
+-------+-----------------+-----+
| cno | cname | tno |
+-------+-----------------+-----+
| 3-245 | 操作系统 | 804 |
| 3-105 | 计算机导论 | 825 |
+-------+-----------------+-----+
2 rows in set (0.00 sec)
#完成
select * from score where cno in (select cno from course where tno in(select tno from teacher where depart='计算机系'));
#显示
mysql> select * from score where cno in (select cno from course where tno in(select tno from teacher where depart='计算机系'));
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+-----+-------+--------+
9 rows in set (0.00 sec)
4.17 union和notin的使用
- 26、查询“计算机系”和“电子工程系”不同职称的教师的tname和prof
select prof from teacher where depart='电子工程系';
select * from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='电子工程系')
union
select * from teacher where depart='电子工程系' and prof not in(select prof from teacher where depart='计算机系');
#显示
mysql> select * from teacher where depart='计算机系' and prof not in(select prof from teacher where depart='电子工程系')
-> union
-> select * from teacher where depart='电子工程系' and prof not in(select prof from teacher where depart='计算机系');
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李成 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+--------+------+---------------------+-----------+-----------------+
2 rows in set (0.00 sec)
4.18 any表示至少一个desc降序
- 27、查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的cno,sno和degree,
- 并按照degree从高到低次序排序
#选择3-245
select * from score where cno = '3-245';
#选择3-105
select * from score where cno = '3-105';
#完成
select * from score
where cno = '3-105'
and degree>any(select degree from score where cno='3-245')
order by degree desc;
#显示
mysql> select * from score
-> where cno = '3-105'
-> and degree>any(select degree from score where cno='3-245')
-> order by degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+-----+-------+--------+
6 rows in set (0.00 sec)
4.19 all表示所有
- 28、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree
select * from score
where cno = '3-105'
and degree>all(select degree from score where cno='3-245');
#显示
mysql> select * from score
-> where cno = '3-105'
-> and degree>all(select degree from score where cno='3-245');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
+-----+-------+--------+
5 rows in set (0.00 sec)
4.20 as取别名-union求并集
- 29、查询所有教师和同学的name、sex和birthday
select tname as name, tsex as sex, tbirthday as birthday from teacher
union
select sname,ssex,sbirthday from student;
#显示
mysql> select tname as name, tsex as sex, tbirthday as birthday from teacher
-> union
-> select sname,ssex,sbirthday from student;
+-----------+-----+---------------------+
| name | sex | birthday |
+-----------+-----+---------------------+
| 李成 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
| 曾华 | 男 | 1977-09-01 00:00:00 |
| 匡明 | 男 | 1975-10-02 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 李军 | 男 | 1976-02-20 00:00:00 |
| 王芳 | 女 | 1975-01-10 00:00:00 |
| 陆君 | 男 | 1974-06-03 00:00:00 |
| 王尼玛 | 男 | 1976-02-20 00:00:00 |
| 张全蛋 | 女 | 1975-01-10 00:00:00 |
| 赵铁柱 | 男 | 1974-06-03 00:00:00 |
| 张飞 | 男 | 1974-06-03 00:00:00 |
+-----------+-----+---------------------+
14 rows in set (0.00 sec)
4.21 union求并集
- 30、查询所有“女”教师和“女”同学的name,sex和birthday
select tname as name, tsex as sex, tbirthday as birthday from teacher where tsex='女'
union
select sname,ssex,sbirthday from student where ssex='女';
#显示
mysql> select tname as name, tsex as sex, tbirthday as birthday from teacher where tsex='女'
-> union
-> select sname,ssex,sbirthday from student where ssex='女';
+-----------+-----+---------------------+
| name | sex | birthday |
+-----------+-----+---------------------+
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 王芳 | 女 | 1975-01-10 00:00:00 |
| 张全蛋 | 女 | 1975-01-10 00:00:00 |
+-----------+-----+---------------------+
5 rows in set (0.00 sec)
4.22 复制表数据做条件查询
- 31、查询成绩比该课程平均成绩低的同学们的成绩表
#每门课程的平均成绩
select cno,avg(degree) from score group by cno;
#显示
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 87.6667 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)
#查看成绩
select * from score;
#显示
mysql> select * from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
12 rows in set (0.00 sec)
#完成
select * from score a where degree < (select avg(degree) from score b where a.cno=b.cno);
#显示
mysql> select * from score a where degree < (select avg(degree) from score b where a.cno=b.cno);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
5 rows in set (0.00 sec)
4.23 子查询
- 32、查询所有任课教师的tname和depart
select tname,depart from teacher where tno in (select tno from course);
#显示
mysql> select tname,depart from teacher where tno in (select tno from course);
+--------+-----------------+
| tname | depart |
+--------+-----------------+
| 李成 | 计算机系 |
| 王萍 | 计算机系 |
| 刘冰 | 电子工程系 |
| 张旭 | 电子工程系 |
+--------+-----------------+
4 rows in set (0.00 sec)
4.24 条件加分组筛选
- 33、查询至少有两名男生的班号
select class from student where ssex='男' group by class having count(*)>1;
#显示
mysql> select class from student where ssex='男' group by class having count(*)>1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.00 sec)
4.25 notlike模糊查询取反
- 34、查询student中不姓“王”的同学的记录
select * from student where sname not like '王%';
#显示
mysql> select * from student where sname not like '王%';
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 女 | 1975-01-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
+-----+-----------+------+---------------------+-------+
7 rows in set (0.00 sec)
4.26 year函数与now函数
- 35、查询student表中每个学生的姓名和年龄
#年龄=当前年份-出生年份
select year(now());
#显示
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2020 |
+-------------+
1 row in set (0.00 sec)
select year(sbirthday) from student;
#显示
mysql> select year(sbirthday) from student;
+-----------------+
| year(sbirthday) |
+-----------------+
| 1977 |
| 1975 |
| 1976 |
| 1976 |
| 1975 |
| 1974 |
| 1976 |
| 1975 |
| 1974 |
| 1974 |
+-----------------+
10 rows in set (0.00 sec)
#做差
select sname,year(now())-year(sbirthday) as '年龄' from student;
#显示
mysql> select sname,year(now())-year(sbirthday) as '年龄' from student;
+-----------+--------+
| sname | 年龄 |
+-----------+--------+
| 曾华 | 43 |
| 匡明 | 45 |
| 王丽 | 44 |
| 李军 | 44 |
| 王芳 | 45 |
| 陆君 | 46 |
| 王尼玛 | 44 |
| 张全蛋 | 45 |
| 赵铁柱 | 46 |
| 张飞 | 46 |
+-----------+--------+
10 rows in set (0.01 sec)
4.27 max与min函数
- 36、查询student表中最大最小的sbirthday日期值
select max(sbirthday) as '最大', min(sbirthday) as '最小' from student;
#显示
mysql> select max(sbirthday) as '最大', min(sbirthday) as '最小' from student;
+---------------------+---------------------+
| 最大 | 最小 |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)
4.28 多字段排序
- 37、以班号和年龄从大到小顺序查询student表中的全部数据
select * from student order by class desc, sbirthday;
#显示
mysql> select * from student order by class desc, sbirthday;
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-01-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 女 | 1975-01-10 00:00:00 | 95031 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
10 rows in set (0.00 sec)
4.29 子查询
- 38、查询“男教师”及其所上的课程
select * from course where tno in (select tno from teacher where tsex='男');
#显示
mysql> select * from course where tno in (select tno from teacher where tsex='男');
+-------+--------------+-----+
| cno | cname | tno |
+-------+--------------+-----+
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
+-------+--------------+-----+
2 rows in set (0.00 sec)
4.30 max函数与子查询
- 39、查询最高分同学的sno、cno和degree列
select * from score where degree=(select max(degree) from score);
#显示
mysql> select * from score where degree=(select max(degree) from score);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
+-----+-------+--------+
1 row in set (0.00 sec)
4.31 子查询
- 40、查询和“李军”同性别的所有同学
select sname from student where ssex = (select ssex from student where sname='李军');
#显示
mysql> select sname from student where ssex = (select ssex from student where sname='李军');
+-----------+
| sname |
+-----------+
| 曾华 |
| 匡明 |
| 李军 |
| 陆君 |
| 王尼玛 |
| 赵铁柱 |
| 张飞 |
+-----------+
7 rows in set (0.00 sec)
- 41、查询和“李军”同性别并且同班的同学sname
select sname from student
where ssex = (select ssex from student where sname='李军')
and class = (select class from student where sname='李军');
#显示
mysql> select sname from student
-> where ssex = (select ssex from student where sname='李军')
-> and class = (select class from student where sname='李军');
+-----------+
| sname |
+-----------+
| 曾华 |
| 李军 |
| 王尼玛 |
+-----------+
3 rows in set (0.00 sec)
- 42、查询所有选修“计算机导论”课程的“男”同学的成绩表
select * from score
where cno=(select cno from course where cname='计算机导论')
and sno in (select sno from student where ssex='男');
#显示
mysql> select * from score
-> where cno=(select cno from course where cname='计算机导论')
-> and sno in (select sno from student where ssex='男');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
+-----+-------+--------+
4 rows in set (0.00 sec)
4.32 按等级查询
- 43、假设使用如下明令建立一个grade表
create table grade (
low int(3),
upp int(3),
grade char(1)
);
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
#现查询所有同学的sno、cno和grade列
select sno,cno,grade from score,grade where degree between low and upp;
#显示
mysql> select sno,cno,grade from score,grade where degree between low and upp;
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | C |
| 105 | 6-166 | C |
| 109 | 3-105 | C |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
+-----+-------+-------+
12 rows in set (0.00 sec)
5 索引
5.1 普通索引
#在已知表上添加索引
create index indexID on student(sno);
或者
alter table student add index indexID(sno);
#显示
mysql> create index indexID on student(sno);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#显示添加的索引
show index from student;
#显示
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student | 0 | PRIMARY | 1 | sno | A | 8 | NULL | NULL | | BTREE | | | YES | NULL |
| student | 1 | indexID | 1 | sno | A | 8 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
#删除索引
drop index [indexID] ON student;
#显示
mysql> drop index indexID ON student;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student | 0 | PRIMARY | 1 | sno | A | 8 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length
#创建表的时候直接指定
create table indexTest (
id int not null,
name varchar(20) not null,
index iname (name(20))
);
#显示
mysql> create table indexTest (
-> id int not null,
-> name varchar(20) not null,
-> index iname (name(20))
-> );
Query OK, 0 rows affected (0.01 sec)
#查看一下
mysql> desc indexTest;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | NO | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
#查看索引
mysql> show index from indexTest;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| indexTest | 1 | iname | 1 | name | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
5.2 唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| sno | varchar(20) | NO | PRI | NULL | |
| sname | varchar(20) | NO | | NULL | |
| ssex | varchar(10) | NO | | NULL | |
| sbirthday | datetime | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#创建索引
create unique index indexID ON student(sno(20));
或者
alter table student add unique indexID (sno(20));
#显示
mysql> alter table student add unique indexID (sno(20));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查询
mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student | 0 | PRIMARY | 1 | sno | A | 8 | NULL | NULL | | BTREE | | | YES | NULL |
| student | 0 | indexID | 1 | sno | A | 8 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
#创建表的时候直接指定
create table indexTest (
id int not null,
name varchar(20) not null,
unique iname (name(20))
);
#显示
mysql> create table indexTest (
-> id int not null,
-> name varchar(20) not null,
-> unique iname (name(20))
-> );
Query OK, 0 rows affected (0.01 sec)
#查看
mysql> show index from indexTest;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| indexTest | 0 | iname | 1 | name | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)
6 连接查询
#创建数据库
create database testJoin;
#person
#id
#name
#cardId
create table person (
id int,
name varchar(20),
cardId int
);
#card表
#id
#name
create table card (
id int,
name varchar(20)
);
#插入数据
insert into card values(1, '饭卡');
insert into card values(2, '建行卡');
insert into card values(3, '农行卡');
insert into card values(4, '工商卡');
insert into card values(5, '邮政卡');
insert into person values(1, '张三', 1);
insert into person values(2, '李四', 3);
insert into person values(3, '王五', 6);
6.1 内连接
- inner join 或者 join
- 内连接查询,其实就是两张表中的数据,通过某个字段相对,查询出相关记录数据
select * from person inner join card on person.cardId=card.id;
#显示
mysql> select * from person inner join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
+------+--------+--------+------+-----------+
2 rows in set (0.00 sec)
select * from person join card on person.cardId=card.id;
#效果是一样的
6.2 外连接
- 左外连接 left join 或者 left outer join
- 以左表为基础,匹配右表
select * from person left join card on person.cardId=card.id;
#显示
mysql> select * from person left join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
+------+--------+--------+------+-----------+
3 rows in set (0.00 sec)
select * from person left outer join card on person.cardId=card.id;
#效果是一样的
- 右外连接 right join 或者 right outer join
- 以右表为基础,匹配左表
select * from person right join card on person.cardId=card.id;
#显示
mysql> select * from person right join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
5 rows in set (0.00 sec)
select * from person right outer join card on person.cardId=card.id;
#效果是一样的
- 全外连接 full join 或者 full outer join
#mysql不支持全外连接
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;
#显示
mysql> select * from person left join card on person.cardId=card.id
-> union
-> select * from person right join card on person.cardId=card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
+------+--------+--------+------+-----------+
6 rows in set (0.00 sec)
7 事务
- 事务是一个最小的不可分割的工作单元,事务能够保证一个业务的完整性
7.1 事务用来做什么
#比如我们的银行转账:
a->-100
update user set money=money-100 where name ='a';
b->+100
update user set money=money+100 where name ='b';
#在实际的程序中,如果只有一条语句执行成功了,而另一天没有执行成功?
#出现数据前后不一致
#所以完成sql语句的过程中,要么全部完成,要么全部失败
7.2 如何开启事务commit-rollback
#mysql是默认开启事务的(自动提交)
select @@autocommit;
#显示
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
#默认事务开启的作用是什么?
#当我们去执行一个sql语句的时候,效果会立即体现出来,且不能回滚。
#创建一个数据库
create database bank;
#创建一个数据表
create table user (
id int primary key,
name varchar(20),
money int
);
insert into user values(1, 'a', 1000);
#事务回滚:撤销sql语句执行效果
rollback;
#显示
mysql> insert into user values(1, 'a', 1000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
#关闭自动提交
set autocommit = 0;
#显示
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
#接下来就可以回退了
#显示
mysql> insert into user values(2, 'b', 1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
#手动提交
#插入数据
mysql> insert into user values(2, 'b', 1000);
Query OK, 1 row affected (0.00 sec)
#手动提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
#回滚失败!
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
7.3 手动开启事务begin-start-transaction
mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
#begin 或者 start transaction;
#都可以帮我们手动开启一个事务
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.01 sec)
#转账
mysql> update user set money=money-100 where name ='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money+100 where name ='b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)
#手动开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set money=money-100 where name ='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money+100 where name ='b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
+----+------+-------+
2 rows in set (0.00 sec)
#撤销
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)
7.4 ACID特征与使用
A 原子性(Atomicity):事务中的全部操作在数据库中是不可分割的,要么全部完成,要么全部不执行。
C 一致性(Consistency):几个并行执行的事务,其执行结果必须与按某一顺序串行执行的结果相一致。
I 夺隔离性(Isolation):事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的。
D 夺持久性(Durability):对于任意已提交事务,系统必须保证该事务对数据库的改变不被丢失,即使数据库出现故障。
8 事务隔离性
8.1 脏读
- read uncommitted 读为提交的
#如果有事务a, 和事物b,
#a事物对数据进行操作,在操作的过程中,事务没有被提交,但是b可以看见a操作的结果
insert into user values(3, '小明', 1000);
insert into user values(4, '淘宝店', 1000);
#显示
mysql> insert into user values(3, '小明', 1000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(4, '淘宝店', 1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)
#查看隔离级别
#系统级别
select @@global.transaction_isolation;
#会话级别
select @@transaction_isolation;
#显示
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
1 row in set (0.00 sec)
#如果两个不同的地方,都在进行操作,如果事务a开启之后,他的数据可以被其它事物读取到。
#这样就会出现(脏读)
#脏读:一个事务读到了另外一个事务没有提交的数据,就叫做脏读
#实际开发是不允许脏读出现的
8.2 不可重复读
修改隔离级别
set global transaction isoation level read uncommitted;
select @@global.transaction_isolation;
read committed 读已提交的
#虽然我只能读到另外一事务提交的数据,但还是会出现问题,就是
#就是同一个表的数据,发现前后不一致
8.3 幻读
修改隔离级别
set global transaction isoation level repeatable read;
select @@global.transaction_isolation;
repeatable 可以重复读
#事务a和事务b同时对一张表,事务a提交的数据,也不能被事务b读到,就可以造成幻读
8.4 串行化
修改隔离级别
set global transaction isoation level serializable
select @@global.transaction_isolation;
serializable 串行化
#当数据表被另一个事务操作的时候,其他事务里面的写操作,是不可以进行的
#进入排队状态(串行化),直到事务结束之后,本地执行者的操作才会被执行
#在没有等待超时的情况下