mysql入门

最新推荐文章于 2024-07-11 23:28:02 发布

cumt~

最新推荐文章于 2024-07-11 23:28:02 发布

阅读量845

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_39644095/article/details/104957434

版权

文章目录

b站链接

1.登录数据库

mysql -u root -p123456

2.如何查询数据库服务器中所有的数据库

show databases;

3.创建数据库

creat database test;

4.如何选中某一个数据库进行操作

mysql>use test;

Database changed

5.查询某个数据库中所有的数据表

mysql> show tables

6.如何创建一个数据表

mysql> CREATE TABLE pet(
    ->     name VARCHAR(20),
    ->     owner varchar(20),
    ->     species VARCHAR(20),
    ->     sex VARCHAR(1),
    ->     birth DATE,
    ->     death DATE);
Query OK, 0 rows affected (0.47 sec)

7.查看是否创建成功

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

8.查看创建好的数据表的结构

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     | varchar(1)  | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

9.如何往数据表中添加数据记录

mysql> insert into pet
    -> values('puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (1.11 sec)

mysql> insert into pet values('claws','Gwen','cat','m','1993-02-03',NULL);
Query OK, 1 row affected (1.13 sec)

mysql> insert into pet values('Buffy','Tim','dog','f','1997-02-05',NULL);
Query OK, 1 row affected (0.25 sec)

mysql> insert into pet values('Slim','Tom','tiger','m','2000-05-31',NULL);
Query OK, 1 row affected (0.25 sec)
mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺财     | 周星驰    | 狗      | 公   | 1990-01-20 | NULL  |
| claws    | Gwen      | cat     | m    | 1993-02-03 | NULL  |
| Buffy    | Tim       | dog     | f    | 1997-02-05 | NULL  |
| Slim     | Tom       | tiger   | m    | 2000-05-31 | NULL  |
+----------+-----------+---------+------+------------+-------+
5 rows in set (0.00 sec)

10.查询数据表中的记录

mysql> select * from pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> insert into pet
    -> values('旺财','周星驰','狗','公','1990-01-20',NULL);
Query OK, 1 row affected (0.16 sec)
mysql>  select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺财      | 周星驰      | 狗      | 公   | 1990-01-20 | NULL  |
+----------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)

11.mysql常用数据类型

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型：Date、DateTime、TimeStamp、Time、Year

其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

12.删除数据

mysql> delete from pet where name='Slim';
Query OK, 2 rows affected (1.06 sec)

mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺财     | 周星驰    | 狗      | 公   | 1990-01-20 | NULL  |
| claws    | Gwen      | cat     | m    | 1993-02-03 | NULL  |
| Buffy    | Tim       | dog     | f    | 1997-02-05 | NULL  |
+----------+-----------+---------+------+------------+-------+
4 rows in set (0.00 sec)

13.如何修改数据

mysql> update pet set name='汪汪' where owner='周星驰';
Query OK, 1 row affected (1.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| puffball | Diane     | hamster | f    | 1999-03-30 | NULL  |
| 汪汪     | 周星驰    | 狗      | 公   | 1990-01-20 | NULL  |
| claws    | Gwen      | cat     | m    | 1993-02-03 | NULL  |
| Buffy    | Tim       | dog     | f    | 1997-02-05 | NULL  |
+----------+-----------+---------+------+------------+-------+
4 rows in set (0.00 sec)

14.总结常见数据记录

增加：insert
删除：delete
修改：update
查询：select

15.mysql建表的约束

15.1主键约束

它能够唯一确定一张表中的一条记录，也就是我们通过给某个字段添加约束，就可以使得字段不重复、不为空。

mysql> create table user(
    ->     id int primary key,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (2.20 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> insert into user values(110,'张三');
Query OK, 1 row affected (0.17 sec)

mysql> insert into user values(110,'张三');
ERROR 1062 (23000): Duplicate entry '110' for key 'user.PRIMARY'
mysql> insert into user values(100,'张三');
Query OK, 1 row affected (1.08 sec)

mysql> select * from user;
+-----+--------+
| id  | name   |
+-----+--------+
| 100 | 张三   |
| 110 | 张三   |
+-----+--------+
2 rows in set (0.00 sec)

mysql> insert into user values(null,'张三');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into user values(1,null);
Query OK, 1 row affected (0.15 sec)

mysql> select * from user;
+-----+--------+
| id  | name   |
+-----+--------+
|   1 | NULL   |
| 100 | 张三   |
| 110 | 张三   |
+-----+--------+
3 rows in set (0.00 sec)
--联合主键
--只要联合的主键值加起来不重复就可以了,但是任何一个字段不为空
mysql> create table user2(
    ->     id int,
    ->     name varchar(20),
    ->     passward varchar(20),
    ->     primary key(id,name)
    -> );
Query OK, 0 rows affected (1.21 sec)

mysql> insert into user2 values(1,'zhangshan','123456');
Query OK, 1 row affected (1.06 sec)
mysql> insert into user2 values(1,'zhangshan','123456');
ERROR 1062 (23000): Duplicate entry '1-zhangshan' for key 'user2.PRIMARY'
mysql> insert into user2 values(1,'lisi','123456');
Query OK, 1 row affected (0.10 sec)
mysql>  insert into user2 values(2,'zhangshan','123456');
Query OK, 1 row affected (1.08 sec)
mysql> select * from user2;
+----+-----------+----------+
| id | name      | passward |
+----+-----------+----------+
|  1 | lisi      | 123456   |
|  1 | zhangshan | 123456   |
|  2 | zhangshan | 123456   |
+----+-----------+----------+
3 rows in set (0.00 sec)

15.2自增约束

--auto_increment
mysql> create table user3(
    ->     id int primary key auto_increment,
    ->     name varchar(20));
Query OK, 0 rows affected (2.20 sec)
mysql> insert into user3 values(1,'xiaoming');
Query OK, 1 row affected (1.16 sec)
mysql> delete from user3 where id=1;
Query OK, 1 row affected (1.10 sec)
mysql> insert into user3 (name) values('xiaoming');
Query OK, 1 row affected (1.06 sec)

mysql> select * from user3;
+----+----------+
| id | name     |
+----+----------+
|  2 | xiaoming |
|  3 | xiaoming |
+----+----------+
2 rows in set (0.00 sec)

如果我们创建表的时候，忘记创建主键约束了，该怎么办

mysql> create table user4
    -> (
    ->     id int,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (2.01 sec)
mysql> describe user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | 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 (2.60 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 (2.66 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)
--使用modify修改字段，添加约束
mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (2.27 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)

15.3外键约束

涉及到两个表：父表、子表

主表、副表

--班级
create table classes
(
    id int primary key,
    name varchar(20)
);
--学生表
create table students
(
    id int primary key,
    name varchar(20),
    class_id int,
    foreign key(class_id) references classes(id)
)
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)

insert into classes values(1,'一班');
insert into classes values(2,'二班');
insert into classes values(3,'三班');
insert into classes values(4,'四班');

insert into students values(1001,'张三',1);
insert into students values(1002,'张三',2);
insert into students values(1003,'张三',3);
insert into students values(1004,'张三',4);

insert into students values(1005,'李四',5);
mysql> insert into students values(1005,'李四',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

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`))

--结论：
--1 主表classes中没有的数据值，在副表中，是不可以使用的；
--2 主表中的记录被副表引用，是不可以被删除的

15.4唯一约束

约束修饰的字段的值不可以重复

mysql> create table user5
    -> (
    ->     id int,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (2.09 sec)

mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (1.44 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,'Json');
Query OK, 1 row affected (1.06 sec)

mysql> insert into user5 values(1,'Json');
ERROR 1062 (23000): Duplicate entry 'Json' for key 'user5.name'
mysql> insert into user5 values(1,'Lisi');
Query OK, 1 row affected (0.12 sec)

mysql> select * from user5;
+------+------+
| id   | name |
+------+------+
|    1 | Json |
|    1 | Lisi |
+------+------+
2 rows in set (0.00 sec)


mysql> create table user6
    -> (
    ->      id int,
    ->      name varchar(20),
    ->     unique(name)
    ->  );
Query OK, 0 rows affected (2.30 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.00 sec)


mysql> create table user7
    -> (
    ->     id int unique,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (2.51 sec)
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

--add unique() 添加唯一约束
mysql> alter table user7 add unique(name);
Query OK, 0 rows affected (0.71 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user7 values(1,'lisi');
Query OK, 1 row affected (1.06 sec)
mysql> insert into user7 values(1,'lisi');
ERROR 1062 (23000): Duplicate entry '1' for key 'user7.id'
mysql> insert into user7 values(2,'lisi');
ERROR 1062 (23000): Duplicate entry 'lisi' for key 'user7.name'


mysql> create table user8
    -> ( id int,
    -> name varchar(20),
    -> unique(id,name)
    -> );
Query OK, 0 rows affected (2.52 sec)
--unique(id,name)表示两个键在一起不重复就可以
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user8 values(1,'lisi');
Query OK, 1 row affected (0.10 sec)
mysql> insert into user8 values(1,'lisi');
ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'user8.id'
mysql> insert into user8 values(2,'lisi');
Query OK, 1 row affected (0.09 sec)
mysql> select * from user8;
+------+------+
| id   | name |
+------+------+
|    1 | lisi |
|    2 | lisi |
+------+------+
2 rows in set (0.00 sec)

如何删除唯一约束：

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

--drop index 删除唯一约束
mysql> alter table user7 drop index id;
Query OK, 0 rows affected (1.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

--modify添加
mysql> alter table user7 modify id int unique;
Query OK, 0 rows affected (1.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

总结：

1.建表的时候就添加约束

2.可以使用alter……add……

3.alter……modify……

4.删除 alter……drop index……

15.5非空约束

mysql> create table user9
    -> (
    ->     id int,
    ->     name varchar(20) not null
    -> );
Query OK, 0 rows affected (2.11 sec)
mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| 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 user9 values(1,'zhangsan');
Query OK, 1 row affected (0.26 sec)
mysql> insert into user9 (id) values(2);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into user9 (name) values('lisi');
Query OK, 1 row affected (1.06 sec)
mysql> select * from user9;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
| NULL | lisi     |
+------+----------+
2 rows in set (0.00 sec)

15.6默认约束

当我们插入字段的时候，如果没有传值，就会使用默认值

mysql> create table user10(
    ->     id int,
    ->     name varchar(20),
    ->     age int default 10
    -> );
Query OK, 0 rows affected (2.13 sec)

mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| 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 user10 (id,name) values (1,'zhangsan');
Query OK, 1 row affected (1.06 sec)

mysql> select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
+------+----------+------+
1 row in set (0.00 sec)
mysql> insert into user10 values(1,'zhangsan',20);
Query OK, 1 row affected (1.07 sec)

mysql> select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    1 | zhangsan |   20 |
+------+----------+------+
2 rows in set (0.00 sec)

16.三大范式

16.1第一范式1NF

数据表中所有字段都是不可分割的原子值

create table student2(
    id int primary key,
    name varchar(20),
    address varchar(30)
);
insert into student2 values(1,'张三','安徽铜陵1号');
insert into student2 values(2,'李四','安徽铜陵2号');
insert into student2 values(3,'王五','安徽铜陵3号');

create table student3(
    id int primary key,
    name varchar(20),
    privince varchar(30),
    city varchar(30),
    number varchar(20)  
);
insert into student3 values(1,'张三','安徽','铜陵','1号');
insert into student3 values(2,'李四','安徽','铜陵','2号');
insert into student3 values(3,'王五','安徽','铜陵','3号');

--address字段值还可以继续拆分，就不能满足1NF
--范式设计的越详细，对于某些实际的操作可能更好，但是不一定不是绝对好

16.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)
);

16.3第三范式3NF

必须先满足第二范式，除开主键列的其他列之间不能有传递依赖

create table myorder(
    order_id int primary key,
    product_id int,
    customer_id int,  
    customer_phone varchar(15)
);
--customer_phone除了依赖主键，还可能依赖customer_id

--修改：
create table myorder(
    order_id int primary key,
    product_id int,
    customer_id int
);
create table customer(
    id int primary key,
    name varchar(20),
    customer_phone varchar(15)
);

17.mysql查询练习

学生表Student	课程表Course	成绩表Score	教师表Teacher
学号 sno	课程号 cno	学号sno	教师编号tno
姓名 sname	课程名cname	课程号cno	教师名字tname
出生年月日	教师编号	成绩	教师性别
所在班级			出生年月日
			职称
			所在部门

--建表
create table Student
(
    sno varchar(20) primary key,
    sname varchar(20) not null,
    ssex varchar(10) not null,
    sbirthday datetime,
    class varchar(20)
);
create table Teacher
(
    tno varchar(20) primary key,
    tname varchar(20) not null,
    tsex varchar(10) not null,
    tbirthday datetime,
    prof varchar(20) not null,
    depart varchar(20) not null
);
create table Course
(
    cno varchar(20) primary key,
    cname varchar(20) not null,
    tno varchar(20) not null,
    foreign key(tno) references Teacher(tno)
);
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)
);
--添加数据
#添加学生表数据
insert into student values('108','曾华','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王丽','女','1976-01-23','95033');
insert into student values('101','李军','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陆君','男','1974-06-03','95031');
insert into student values('102','哈桑','男','1974-06-03','95031');
insert into student values('104','金闪闪','男','1974-06-03','95031');
insert into student values('106','卫宫','男','1974-06-03','95031');
#添加教师数据
insert into teacher values('804','李诚','男','1974-06-03','副教授','计算机系');
insert into teacher values('856','张旭','男','1975-06-03','讲师','电子信息系');
insert into teacher values('825','王萍','女','1956-06-03','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-06-23','助教','电子信息系');
#添加课程表
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');
#添加成绩表
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');
--查询练习
--1.查询student表中所有记录的sname，ssex，和class列
mysql> select sname,ssex,class from student;
+-----------+------+-------+
| sname     | ssex | class |
+-----------+------+-------+
| 李军      | 男   | 95033 |
| 哈桑      | 男   | 95031 |
| 陆君      | 男   | 95031 |
| 金闪闪    | 男   | 95031 |
| 匡明      | 男   | 95031 |
| 卫宫      | 男   | 95031 |
| 王丽      | 女   | 95033 |
| 曾华      | 男   | 95033 |
| 王芳      | 女   | 95031 |
+-----------+------+-------+
9 rows in set (0.00 sec)


--2.查询教师表不重复的depart列
mysql> select distinct depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 电子信息系      |
+-----------------+
2 rows in set (0.04 sec)


--3.查询score表中成绩在60-80之间的所有记录
--between  and   包含边界值
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.02 sec)
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.85 sec)


--4.查询score表中成绩为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)


--5.查询student表中“95031”班或者性别为“女”的同学记录
mysql> select * from student where class='95031' or ssex='女';
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 102 | 哈桑      | 男   | 1974-06-03 00:00:00 | 95031 |
| 103 | 陆君      | 男   | 1974-06-03 00:00:00 | 95031 |
| 104 | 金闪闪    | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
| 106 | 卫宫      | 男   | 1974-06-03 00:00:00 | 95031 |
| 107 | 王丽      | 女   | 1976-01-23 00:00:00 | 95033 |
| 109 | 王芳      | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
7 rows in set (0.00 sec)


--6.以class降序查询student表中所有记录
mysql> select * from student order by class desc;
+-----+-----------+------+---------------------+-------+
| sno | sname     | ssex | sbirthday           | class |
+-----+-----------+------+---------------------+-------+
| 101 | 李军      | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王丽      | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华      | 男   | 1977-09-01 00:00:00 | 95033 |
| 102 | 哈桑      | 男   | 1974-06-03 00:00:00 | 95031 |
| 103 | 陆君      | 男   | 1974-06-03 00:00:00 | 95031 |
| 104 | 金闪闪    | 男   | 1974-06-03 00:00:00 | 95031 |
| 105 | 匡明      | 男   | 1975-10-02 00:00:00 | 95031 |
| 106 | 卫宫      | 男   | 1974-06-03 00:00:00 | 95031 |
| 109 | 王芳      | 女   | 1975-02-10 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)


--7.以cno升序、degree降序查询score表中的所有记录
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)


--8.查询“95031”班的学生人数
--统计  count
mysql> select count(*) from student where class='95031';
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.04 sec)


--9.查询score表中最高分的学生学号和课程号（子查询或者排序）
mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.06 sec)

步骤：
1.找到最高分
select max(degree) from score;
2.找最高分的sno,cno
select sno,cno from score where degree=(select max(degree) from score);

--排序的做法
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)

mysql> select sno,cno,degree from score order by degree desc limit 0,1;
--limit x,y   x表示开始下标，y表示查询数目
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.02 sec)


--10.查询每门课的平均成绩  
--avg()
--查询一门课
mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
|     85.3333 |
+-------------+
1 row in set (0.04 sec)
--查询每门课
--group by 分组
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)

--11.查询score表中至少有2名同学选修的并以3开头的课程的平均分数
select cno,avg(degree) from score 
group by cno 
having count(sno)>=2 and cno like '3%';
mysql> select cno,avg(degree) from score group by cno
    -> having count(sno)>=2 and cno like '3%';
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
+-------+-------------+
2 rows in set (0.07 sec)

学生表Student	课程表Course	成绩表Score	教师表Teacher
学号 sno	课程号 cno	学号sno	教师编号tno
姓名 sname	课程名cname	课程号cno	教师名字tname
出生年月日sbirthday	教师编号tno	成绩degree	教师性别tsex
所在班级			出生年月日
			职称
			所在部门

--多表查询
--12.查询所有学生的sname,cno和degree列
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)
--13.查询所有学生的sname,cname和degree列
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)
mysql> select sname,cname,degree,student.sno,course.cno from student,course,score
    -> where student.sno=score.sno and course.cno=score.cno;
+--------+-----------------+--------+-----+-------+
| sname  | cname           | degree | sno | cno   |
+--------+-----------------+--------+-----+-------+
| 陆君   | 计算机导论      |     92 | 103 | 3-105 |
| 陆君   | 操作系统        |     86 | 103 | 3-245 |
| 陆君   | 数字电路        |     85 | 103 | 6-166 |
| 匡明   | 计算机导论      |     88 | 105 | 3-105 |
| 匡明   | 操作系统        |     75 | 105 | 3-245 |
| 匡明   | 数字电路        |     79 | 105 | 6-166 |
| 王芳   | 计算机导论      |     76 | 109 | 3-105 |
| 王芳   | 操作系统        |     68 | 109 | 3-245 |
| 王芳   | 数字电路        |     81 | 109 | 6-166 |
+--------+-----------------+--------+-----+-------+
9 rows in set (0.00 sec)
mysql> select sname,cname,degree,student.sno,student.sno as stu_sno,course.cno,course.cno as cou_cno from student,course,score
    -> where student.sno=score.sno and course.cno=score.cno;
+--------+-----------------+--------+-----+---------+-------+---------+
| sname  | cname           | degree | sno | stu_sno | cno   | cou_cno |
+--------+-----------------+--------+-----+---------+-------+---------+
| 陆君   | 计算机导论      |     92 | 103 | 103     | 3-105 | 3-105   |
| 陆君   | 操作系统        |     86 | 103 | 103     | 3-245 | 3-245   |
| 陆君   | 数字电路        |     85 | 103 | 103     | 6-166 | 6-166   |
| 匡明   | 计算机导论      |     88 | 105 | 105     | 3-105 | 3-105   |
| 匡明   | 操作系统        |     75 | 105 | 105     | 3-245 | 3-245   |
| 匡明   | 数字电路        |     79 | 105 | 105     | 6-166 | 6-166   |
| 王芳   | 计算机导论      |     76 | 109 | 109     | 3-105 | 3-105   |
| 王芳   | 操作系统        |     68 | 109 | 109     | 3-245 | 3-245   |
| 王芳   | 数字电路        |     81 | 109 | 109     | 6-166 | 6-166   |
+--------+-----------------+--------+-----+---------+-------+---------+
9 rows in set (0.00 sec)

--14.查询“95031“班学生的每门课的平均分
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 |
+-----+-------+--------+
| 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 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 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.84 sec)

--15.查询选修”3-105“课程的成绩高于”109“号同学”3-105“成绩的所有同学的记录
mysql> select degree from score
    -> where sno='109' and cno='3-105';
+--------+
| degree |
+--------+
|     76 |
+--------+
1 row in set (0.00 sec)
mysql> select * from score where degree>(select degree from score where sno='109' and cno='3-105') and cno='3-105'
    -> ;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
2 rows in set (0.00 sec)
--16.查询成绩高于学号为”109“、课程号为”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)

--17.查询和学号为108、101的同学同年出生的所有学生的sno,sname和sbirthday列
select year(sbirthday) from student where sno in (101,108);
select * from student where  year(sbirthday) in(select year(sbirthday) from student where sno in (101,108));


mysql> select * from student where  year(sbirthday) in(select year(sbirthday) from student where sno in (101,108));
+-----+--------+------+---------------------+-------+
| sno | sname  | ssex | sbirthday           | class |
+-----+--------+------+---------------------+-------+
| 101 | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 107 | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108 | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
+-----+--------+------+---------------------+-------+
3 rows in set (0.02 sec)

--18.查询”张旭“教师任课的学生成绩
--根据表的关联
mysql> select sno,score.cno,degree from score,course,teacher
    -> where tname='张旭' and teacher.tno=course.tno
    -> and course.cno=score.cno;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
3 rows in set (0.00 sec)


--三层嵌套
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)

--19.查询选修某课程的同学多于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');


mysql> select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(cno)>5));
+--------+
| tname  |
+--------+
| 王萍   |
+--------+
1 row in set (0.00 sec)

--20.查询95033班和95031班全体学生的记录
mysql> insert into student values('110','张飞','男','1977-02-01','95038');
Query OK, 1 row affected (0.11 sec)
select * from student where class in ('95033','95031');

--21.查询“计算机系”与“电子工程系”不同制成的教师的tname和prof
--union求并集
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 | 李诚   | 男   | 1974-06-03 00:00:00 | 副教授    | 计算机系        |
| 856 | 张旭   | 男   | 1975-06-03 00:00:00 | 讲师      | 电子信息系      |
+-----+--------+------+---------------------+-----------+-----------------+
2 rows in set (0.00 sec)

--22.查询选修编号为3-105课程且成绩至少高于选修编号为3-245的同学的cno,sno和degree,
--并将degree降序排序
--至少？  即大于其中任意一个  any
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)
--23.查询选修编号为3-105且成绩高于选修编号为3-245课程的同学cno,sno和degree
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)

mysql> select * from score
    -> where cno='3-105' and degree>(select max(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)

--23.查询所有教师和同学的name,sex,birthday
mysql> select sname,ssex,sbirthday from student
    -> union
    -> select tname,tsex,tbirthday from teacher;
+-----------+------+---------------------+
| sname     | ssex | sbirthday           |
+-----------+------+---------------------+
| 李军      | 男   | 1976-02-20 00:00:00 |
| 哈桑      | 男   | 1974-06-03 00:00:00 |
| 陆君      | 男   | 1974-06-03 00:00:00 |
| 金闪闪    | 男   | 1974-06-03 00:00:00 |
| 匡明      | 男   | 1975-10-02 00:00:00 |
| 卫宫      | 男   | 1974-06-03 00:00:00 |
| 王丽      | 女   | 1976-01-23 00:00:00 |
| 曾华      | 男   | 1977-09-01 00:00:00 |
| 王芳      | 女   | 1975-02-10 00:00:00 |
| 张飞      | 男   | 1977-02-01 00:00:00 |
| 李诚      | 男   | 1974-06-03 00:00:00 |
| 王萍      | 女   | 1956-06-03 00:00:00 |
| 刘冰      | 女   | 1977-06-23 00:00:00 |
| 张旭      | 男   | 1975-06-03 00:00:00 |
+-----------+------+---------------------+
14 rows in set (0.00 sec)
mysql> select sname as name,ssex as sex,sbirthday as birthday from student
    -> union
    -> select tname,tsex,tbirthday from teacher;
+-----------+-----+---------------------+
| name      | sex | birthday            |
+-----------+-----+---------------------+
| 李军      | 男  | 1976-02-20 00:00:00 |
| 哈桑      | 男  | 1974-06-03 00:00:00 |
| 陆君      | 男  | 1974-06-03 00:00:00 |
| 金闪闪    | 男  | 1974-06-03 00:00:00 |
| 匡明      | 男  | 1975-10-02 00:00:00 |
| 卫宫      | 男  | 1974-06-03 00:00:00 |
| 王丽      | 女  | 1976-01-23 00:00:00 |
| 曾华      | 男  | 1977-09-01 00:00:00 |
| 王芳      | 女  | 1975-02-10 00:00:00 |
| 张飞      | 男  | 1977-02-01 00:00:00 |
| 李诚      | 男  | 1974-06-03 00:00:00 |
| 王萍      | 女  | 1956-06-03 00:00:00 |
| 刘冰      | 女  | 1977-06-23 00:00:00 |
| 张旭      | 男  | 1975-06-03 00:00:00 |
+-----------+-----+---------------------+
14 rows in set (0.00 sec)


--24.查询所有女教师和女同学的name,sex,birthday
mysql> select sname as name,ssex as sex,sbirthday as birthday from student where ssex='女'
    ->  union
    -> select tname,tsex,tbirthday from teacher where tsex='女';
+--------+-----+---------------------+
| name   | sex | birthday            |
+--------+-----+---------------------+
| 王丽   | 女  | 1976-01-23 00:00:00 |
| 王芳   | 女  | 1975-02-10 00:00:00 |
| 王萍   | 女  | 1956-06-03 00:00:00 |
| 刘冰   | 女  | 1977-06-23 00:00:00 |
+--------+-----+---------------------+
4 rows in set (0.00 sec)

--25.查询成绩比该课程平均成绩低的同学的成绩表
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)
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)
mysql> select * from score as a where degree <(select avg(degree) from score as 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)

--26.查询至少有2名男生的班级号
mysql>  select class from student where ssex='男' group by class having count(class )>=2;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.00 sec)


--27.查询student表中每个学生的姓名和年龄
--年龄=当前年份-出生年份     sage=year(now())-year(sbirthday)
mysql> select sname,year(now())-year(sbirthday) as sage from student;
+-----------+------+
| sname     | sage |
+-----------+------+
| 李军      |   44 |
| 哈桑      |   46 |
| 陆君      |   46 |
| 金闪闪    |   46 |
| 匡明      |   45 |
| 卫宫      |   46 |
| 王丽      |   44 |
| 曾华      |   43 |
| 王芳      |   45 |
| 张飞      |   43 |
+-----------+------+
10 rows in set (0.00 sec)


--28.查询男教师及其所上的课程
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)
--29.假设建了一个grade表
mysql> create table grade
    -> (
    ->     low int(3),
    ->     upp int(3),
    ->     grade char(1)
    -> );
Query OK, 0 rows affected, 2 warnings (1.33 sec)
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(50,59,'E');
--查询所有学生的sno,cno和grade列
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)

18.SQL的四种连接查询

--创建两个表：
--person: id name cardId
--card: id name
create table person
(
    id int,
    name varchar(20),
    cardId int
);
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);
--没有创建外键

18.1内连接 inner join 或者join

--inner join查询
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;

18.2外连接

18.2.1左连接 left join 或者 left outer join

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)
--左外连接，会把左边的表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来
--如果没有，就会补充NULL
select * from person left outer join card on person.cardid=card.id;

18.2.2右连接 right join 或者 right outer join

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)
--右外连接，会把右边的表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来
--如果没有，就会补充NULL
select * from person right outer join card on person.cardid=card.id;

18.2.3完全外连接 full join 或者 full outer join

select * from person full join card on person.cardid=card.id;
--mysql不支持 full join
--可以使用union实现 full 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;
+------+--------+--------+------+-----------+
| 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)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0zoWYTwA-1584546516889)(C:\Users\严高飞\AppData\Roaming\Typora\typora-user-images\1584534397110.png)]

19.mysql的事务

--mysql中，事务是一个最小的不可分割的工作单元，事务能够保证一个业务的完整性
--比如：银行转账
a->b  -100
update user set money=money-100 where name='a';
b->  +100
update user set money=money+100 where name='b';
--实际的程序中，如果只有一条语句执行成功了，而另一条语句没有执行成功？
--出现数据前后的不一致。

update user set money=money-100 where name='a';
update user set money=money+100 where name='b';
--多条sql语句可能会有同时成功的要求，要么就同时失败

--mysql中如何控制事务？
1.默认开启事务（自动提交）
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,'tom',1000);

--事务回滚，撤销sql语句的运行效果
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | tom  |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

--设置mysql 自动提交为false
set autocommit=0;
mysql>  select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

--上面的操作，关闭了mysql的自动提交（commit）
insert into user values(2,'jack',1000);
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | tom  |  1000 |
|  2 | jack |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.91 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | tom  |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

mysql> insert into user values(2,'jack',1000);
Query OK, 1 row affected (0.00 sec)
--手动提交（持久性）
mysql> commit;
Query OK, 0 rows affected (0.14 sec)
--此时rollback失效


--自动提交  @@autocommit=1
--手动提交  commit;
--手动回滚  rollback;

--转账：
mysql> update user set money=money-100 where name='tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update user set money=money+100 where name='jack';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | tom  |   900 |
|  2 | jack |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | tom  |  1000 |
|  2 | jack |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)
--事务给我们提供了一个返回的机会

20.事务的四大特征ACID

A:原子性：事务是最小的单位，不可以再分割
C:一致性：事务要求，同一个事务中的sql语句，必须保证同时成功或者同时失败
I:隔离性：事务1和事务2之间是有隔离性的
D:持久性：事务一旦结束（commit,rollback）,就不可以返回

事务开启：
    1.修改默认提交 set autocommit=0;
    2.begin;
    3.start transaction;
事务手动提交：
    commit;
事务手动回滚：
    rollback;
    

事务的隔离性：
    1.read uncommitted;            读未提交的
    2.read committed;              读已经提交的
    3.repeatable read;            可以重复读
    4.serializable;               串行化

20.1脏读(read uncommitted)

脏读：一个事务读到了另一个事务没有提交的数据，就叫做脏读

1.read uncommitted;
如果有事务a和事务b，a事务对数据进行操作，在操作过程中，事务没有被提交，但是b可以看见a操作的结果。

bank数据库user表
insert into user values(3,'小明',1000);
insert into user values(4,'淘宝店',1000);
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)

--如何查看数据库的隔离级别？
mysql 8.0
--系统级别
select @@global.transaction_isolation;
--会话级别
select @@transaction_isolation;
--mysql默认隔离级别（可重复读）
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)
--修改隔离级别
set global transaction isolation level read uncommitted;

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
1 row in set (0.00 sec)

--转账：小明在淘宝店买鞋子：800
    小明-》城都 ATM
    淘宝店-》广州 ATM
begin;
update user set money=money-800 where name='小明';
update user set money=money+800 where name='淘宝店';
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+
4 rows in set (0.00 sec)
--给淘宝店打电话，说你去查一下，是不是到账了
--淘宝店重启一个终端
mysql> use bank;
Database changed
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+
4 rows in set (0.00 sec)
--发货
--晚上请客吃饭花了1800

--小明突然rollback
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

--结账发现钱不够
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)
--如果两个不同的地方，都在进行操作，如果事务a开启之后，他的数据可以被其他事务读取到
--这样就会出现（脏读）
--脏读：一个事务读到了另一个事务没有提交的数据，就叫做脏读
--实际开发中是不允许出现脏读的

20.2不可重复读（read committed）

–虽然我只能读到你提交的数据，但是还是会出现问题
–读取同一个表的数据，发现前后不一致

2.read committed;  读已经提交的
--修改隔离级别为 READ-COMMITTED  
set global transaction isolation level read committed;
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+
1 row in set (0.00 sec)

--bank数据库user表
--小张：银行的会计
start transaction;
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)
--小张出门了一下
--小王：
mysql> use bank;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user values(5,'c',100);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.08 sec)
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+
5 rows in set (0.00 sec)
--小张回来了
mysql> select avg(money) from user;
+------------+
| avg(money) |
+------------+
|   820.0000 |
+------------+
1 row in set (0.00 sec)
--money 的平均不是1000，变少了？
--虽然我只能读到你提交的数据，但是还是会出现问题
--读取同一个表的数据，发现前后不一致
--不可重复读：read committed;

20.3可重复读（repeatable read）

–事务a和事务b同时操作一张表，事务a提交的数据，也不能被事务b读到，就造成幻读。

set global transaction isolation level repeatable read;
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)
--在 REPEATABLE-READ 隔离级别下会出现什么问题？
--张三--成都
start transaction;
--李四--北京
start transaction;
--张三--成都
mysql> insert into user values(6,'d',1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+
6 rows in set (0.00 sec)
--李四--北京
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+
5 rows in set (0.00 sec)
--张三--成都
mysql> commit;
Query OK, 0 rows affected (0.10 sec)
--李四--北京
mysql>  select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+
5 rows in set (0.00 sec)
mysql> insert into user values(6,'d',1000);
ERROR 1062 (23000): Duplicate entry '6' for key 'user.PRIMARY'
--这种现象就叫做幻读
--事务a和事务b同时操作一张表，事务a提交的数据，也不能被事务b读到，就造成幻读。

20.4串行化（serializable）

–当user被另外一个事务操作的时候，其他事务里面的写操作是不可以进行的
–进入排队状态，直到前面的事务结束之后，在没有等待超时的情况下现在的事务才可以执行写入操作

--修改隔离级别为串行化
set global transaction isolation level serializable;
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+
6 rows in set (0.00 sec)
--张三--成都
start transaction;
--李四--北京
start transaction;
--张三--成都
mysql> insert into user values(7,'赵铁柱',1000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.12 sec)

mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
|  7 | 赵铁柱    |  1000 |
+----+-----------+-------+
7 rows in set (0.00 sec)
--李四--北京
mysql> select * from user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | tom       |   900 |
|  2 | jack      |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
|  7 | 赵铁柱    |  1000 |
+----+-----------+-------+
7 rows in set (0.00 sec)
--张三--成都
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user values(8,'王小花',1000);


--等待状态，无法插入
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
--李四--北京
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--张三--成都
mysql> insert into user values(8,'王小花',1000);
Query OK, 1 row affected (0.00 sec)
--此时插入成功

--当user被另外一个事务操作的时候，其他事务里面的写操作是不可以进行的
--进入排队状态，直到前面的事务结束之后，在没有等待超时的情况下现在的事务才可以执行写入操作
--串行化带来的问题：性能特差

20.5总结

总结：read uncommitted>read committed>repeatable read>serializable
--隔离级别越高，性能越差
mysql默认隔离级别是repeatable read

cumt~

关注

1
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
mysql入门

文章目录1.登录数据库2.如何查询数据库服务器中所有的数据库3.创建数据库4.如何选中某一个数据库进行操作5.查询某个数据库中所有的数据表6.如何创建一个数据表7.查看是否创建成功8.查看创建好的数据表的结构9.如何往数据表中添加数据记录10.查询数据表中的记录11.mysql常用数据类型12.删除数据13.如何修改数据14.总结常见数据记录15.mysql建表的约束15.1主键约束15.2自增约...
复制链接

扫一扫