Mysql学习笔记

最新推荐文章于 2021-12-08 21:29:40 发布
最新推荐文章于 2021-12-08 21:29:40 发布
阅读量1.1k 收藏
点赞数 2
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43117447/article/details/94581888
版权

1.启动数据库服务

C:\Windows\system32>net start mysql57
MySQL57 服务正在启动 .
MySQL57 服务已经启动成功。

2.登陆数据库

C:\Windows\system32>mysql -u root -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

3.查询数据库服务器中所有的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kaige              |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.02 sec)

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

mysql> use test;
Database changed

5.退出数据库服务器

mysql> exit;
bye

6.在数据库服务器中创建我们的数据库

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

7.查看某个数据库中的所有的数据表

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

8.在某个数据库中创建数据表

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.03 sec)

9.查看数据表的结构详情

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.01 sec)

10.往数据表中添加数据记录

mysql> INSERT INTO pet
    -> values ("Puffball","Diane","hamster","f","1999-03-30",NULL);
Query OK, 1 row affected (0.01 sec)
mysql> select * from pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

mysql> INSERT INTO pet VALUES('Claws','Gwen','cat','m','1994-03-17',NULL);INSERT INTO pet VALUES('Buffy','Harold','dog','f','1989-05-13',NULL);INSERT INTO pet VALUES('Fang','Benny','dog','m','1990-08-27',NULL);INSERT INTO pet VALUES('Bowser','Diane','dog','m','1979-08-31','1995-07-29');INSERT INTO pet VALUES('Chirpy','Gwen','bird','f','1998-09-11',NULL);
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
| 旺财     | 周星驰 | 狗      | m    | 1999-01-01 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
+----------+--------+---------+------+------------+------------+
16 rows in set (0.00 sec)

11.mysql常用数据类型
MySQL 数据类型
12.删除数据

mysql> delete from pet where name="Buffy";
Query OK, 3 rows affected (0.01 sec)

13.修改数据

mysql> update pet set name="fang";
Query OK, 13 rows affected (0.01 sec)
Rows matched: 13  Changed: 13  Warnings: 0
mysql> select * from pet;
+------+--------+---------+------+------------+------------+
| name | owner  | species | sex  | birth      | death      |
+------+--------+---------+------+------------+------------+
| fang | Diane  | hamster | f    | 1999-03-30 | NULL       |
| fang | 周星驰 | 狗      | m    | 1999-01-01 | NULL       |
| fang | Benny  | dog     | m    | 1990-08-27 | NULL       |
| fang | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| fang | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| fang | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| fang | Benny  | dog     | m    | 1990-08-27 | NULL       |
| fang | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| fang | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| fang | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| fang | Benny  | dog     | m    | 1990-08-27 | NULL       |
| fang | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| fang | Gwen   | bird    | f    | 1998-09-11 | NULL       |
+------+--------+---------+------+------------+------------+
mysql> update pet set name = "汪汪" where owner="周星驰";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pet;
+------+--------+---------+------+------------+------------+
| name | owner  | species | sex  | birth      | death      |
+------+--------+---------+------+------------+------------+
| fang | Diane  | hamster | f    | 1999-03-30 | NULL       |
| 汪汪 | 周星驰 | 狗      | m    | 1999-01-01 | NULL       |
| fang | Benny  | dog     | m    | 1990-08-27 | NULL       |
| fang | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| fang | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| fang | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| fang | Benny  | dog     | m    | 1990-08-27 | NULL       |
| fang | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| fang | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| fang | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| fang | Benny  | dog     | m    | 1990-08-27 | NULL       |
| fang | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| fang | Gwen   | bird    | f    | 1998-09-11 | NULL       |
+------+--------+---------+------+------------+------------+
13 rows in set (0.00 sec)

14.mysql建表约束
14.1.主键约束
他能够唯一确定一张表中的一条记录,也就是我们通过给某个字段添加约束,就可以使得该字段不重复且不为空

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

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| pet             |
| user            |
+-----------------+
2 rows in set (0.00 sec)

mysql> describe user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user values(1,"张三");
Query OK, 1 row affected (0.01 sec)

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

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

mysql> insert into user values(NULL,"张三");
ERROR 1048 (23000): Column 'id' cannot be null

14.1.1联合主键
只要联合的主键值加起来不重复就可以

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user2 values(1,"张三","123");
Query OK, 1 row affected (0.01 sec)

mysql> insert into user2 values(2,"张三","123");
Query OK, 1 row affected (0.01 sec)

mysql> insert into user2 values(1,"李四","123");
Query OK, 1 row affected (0.01 sec)

mysql> select * from user2;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | 张三 | 123      |
|  1 | 李四 | 123      |
|  2 | 张三 | 123      |
+----+------+----------+
3 rows in set (0.00 sec)
mysql> describe user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

14.2自增约束

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into user3 (name) values("张三");
Query OK, 1 row affected (0.01 sec)

mysql> select * from user3;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
+----+------+
1 row in set (0.00 sec)
mysql> insert into user3 (name) values("张三");
Query OK, 1 row affected (0.01 sec)

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

14.3创建表的时候忘记创建主键约束怎么办
修改表结构,添加主键

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

mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | 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.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | 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 (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

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

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

mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user5 value(1,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert into user5 value(1,"张三");
ERROR 1062 (23000): Duplicate entry '张三' for key 'name'
mysql> insert into user5 value(1,"李四");
Query OK, 1 row affected (0.01 sec)

也可以在建表的时候添加约束

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

删除唯一约束

mysql> alter table user6 drop index name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

添加约束

mysql> alter table user6 modify name varchar(20) unique;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

14.3非空约束
修饰的字段不能为空值

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

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

14.4默认约束

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

mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | 10      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into user8(id,name) values(1,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> select * from user8;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
+------+------+------+
1 row in set (0.00 sec)

mysql> insert into user8 values(1,"李四",19);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user8;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 张三 |   10 |
|    1 | 李四 |   19 |
+------+------+------+
2 rows in set (0.00 sec)

14.5外键约束
涉及到两个表,父表和子表
–班级表

mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 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.02 sec)

往班级表插入数据

mysql> insert into classes values(1,"一班");
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes values(2,"二班");
Query OK, 1 row affected (0.01 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.01 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.01 sec)

mysql> insert into students values(1003,"赵武",4);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1004,"甄姬",3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1005,"廉颇",2);
Query OK, 1 row affected (0.01 sec)

试一下插入class_id = 5的数据

mysql> insert into students values(1006,"小明",5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test1`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

可以发现:
①主表classes中没有的的数据值,副表中是不可以使用的;
②主表中的记录被副表引用的情况下不能被删除.

mysql> delete from classes where id=4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test1`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

15.数据库的三大设计范式
15.1第一范式:数据表中的所有字段都是不可分割的原子值

mysql> create table student1(
    -> id int primary key,
    -> name varchar(20),
    -> address varchar(30)
    -> );
mysql> insert into student1 values(1,"诺克","英雄联盟诺克萨斯");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student1 values(2,"盖伦","英雄联盟德玛西亚");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student1 values(3,"后裔","王者荣耀不知名大陆");
Query OK, 1 row affected (0.01 sec)

mysql> select * from student1;
+----+------+--------------------+
| id | name | address            |
+----+------+--------------------+
|  1 | 诺克 | 英雄联盟诺克萨斯   |
|  2 | 盖伦 | 英雄联盟德玛西亚   |
|  3 | 后裔 | 王者荣耀不知名大陆 |
+----+------+--------------------+
3 rows in set (0.00 sec)

字段值还可以继续拆分,就不满足第一范式

mysql> create table student2(
    -> id int primary key,
    -> name varchar(20),
    -> game varchar(10),
    -> mainland varchar(10)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into student2 values(1,"诺克","英雄联盟","诺克萨斯");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student2 values(2,"盖伦","英雄联盟","德玛西亚");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student2 values(3,"后裔","王者荣耀","不知名大陆");
Query OK, 1 row affected (0.01 sec)
mysql> select * from student2;
+----+------+----------+------------+
| id | name | game     | mainland   |
+----+------+----------+------------+
|  1 | 诺克 | 英雄联盟 | 诺克萨斯   |
|  2 | 盖伦 | 英雄联盟 | 德玛西亚   |
|  3 | 后裔 | 王者荣耀 | 不知名大陆 |
+----+------+----------+------------+
3 rows in set (0.00 sec)

范式的设计越详细,对于某些实际操作可能更好,但不一定都是好处,比如地址分的太详细在取数的时候还要拼接操作,具体设计还要根据具体业务来定.
15.2 第二范式
必须是满足第一范式前提下,第二范式要求,除主键外的每一列都必须完全依赖于主键,如果要出现不完全依赖,只可能发生在联合主键的情况下
例:设计一个订单表

mysql> create table myorder(
    -> product_id int,
    -> customer_id int,
    -> product_name varchar(20),
    -> customer_name varchar(20),
    -> primary key(product_id,customer_id)
    -> );
Query OK, 0 rows affected (0.04 sec)

拆分成如下三张表之后才满足第二范式

mysql> create table myorder(
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int
    -> );
Query OK, 0 rows affected (0.02 sec)

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

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

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

mysql> create table myorder(
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int,
    -> customer_phone int#这样的设计phone是依赖于customer_id的,不符合第三范式
    -> );
Query OK, 0 rows affected (0.02 sec)

将phone字段添加到customer表中才符合第三范式

mysql> create table customer(
    -> id int primary key,
    -> name varchar(20),
    -> phone int
    -> );

16.查询练习
先单独创建一个用于练习的数据库

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

mysql> use Stutest;
Database changed

学生表(Student)
字段:学号,姓名,性别,出生年月日,所在班级

mysql> create table Student(
    -> snum varchar(20) primary key,
    -> sname varchar(20) not null,
    -> ssex varchar(10) not null,
    -> sbirthday datetime,
    -> class varchar(10)
    -> );
Query OK, 0 rows affected (0.02 sec)

教师表(Teacher)
字段:教师编号,教师名字,教师性别,出生年月日,职称,所在部门

mysql> create table teacher(
    -> tnu 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
    -> );
Query OK, 0 rows affected (0.02 sec)

课程表(Course)
字段:课程号,课程名称,教师编号

mysql> create table course(
    -> cnu varchar(20) primary key,
    -> cname varchar(20) not null,
    -> tnu varchar(20) not null,
    -> foreign key(tnu) references teacher(tnu)
    -> );
Query OK, 0 rows affected (0.02 sec)

成绩表(Score)
字段:学号,课程号,成绩

mysql> create table score(
    -> sno varchar(20),
    -> cno varchar(20),
    -> degree decimal,
    -> foreign key(sno) references student(snum),
    -> foreign key(cno) references course(cnu),
    -> primary key(sno,cno)
    -> );
Query OK, 0 rows affected (0.02 sec

mysql> show tables;
+-------------------+
| Tables_in_stutest |
+-------------------+
| course            |
| score             |
| student           |
| teacher           |
+-------------------+
4 rows in set (0.01 sec)

往数据表中添加数据(此处不一一展示)

insert into student values("101","廉颇","男","1977-09-01","95033");
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+----------+------+---------------------+-------+
| snum | sname    | ssex | sbirthday           | class |
+------+----------+------+---------------------+-------+
| 101  | 廉颇     | 男   | 1977-09-01 00:00:00 | 95033 |
| 102  | 张飞     | 男   | 1979-09-01 00:00:00 | 95033 |
| 103  | 晁盖     | 男   | 1970-09-01 00:00:00 | 95033 |
| 104  | 赵云     | 男   | 1980-09-01 00:00:00 | 95033 |
| 105  | 貂蝉     | 女   | 1994-09-01 00:00:00 | 95031 |
| 106  | 诸葛亮   | 男   | 1977-09-01 00:00:00 | 95031 |
| 107  | 金莲     | 女   | 1997-09-01 00:00:00 | 95031 |
| 108  | 镇关西   | 男   | 1967-09-01 00:00:00 | 95031 |
| 109  | 吴下阿蒙 | 男   | 1987-09-01 00:00:00 | 95031 |
+------+----------+------+---------------------+-------+
9 rows in set (0.01 sec)

mysql> insert into teacher values("804","曹操","男","1958-12-02","教授","历史系");
Query OK, 1 row affected (0.01 sec)

mysql> insert into teacher values("856","刘备","男","1968-12-12","副教授","工程系");
Query OK, 1 row affected (0.01 sec)

mysql> insert into teacher values("825","咪蒙","女","1998-12-12","讲师","心理系");
Query OK, 1 row affected (0.01 sec)

mysql> insert into teacher values("831","高圆圆","女","1998-02-22","讲师","心理系");
Query OK, 1 row affected (0.01 sec)

mysql> select * from teacher;
+-----+--------+------+---------------------+--------+--------+
| tnu | tname  | tsex | tbirthday           | prof   | depart |
+-----+--------+------+---------------------+--------+--------+
| 804 | 曹操   | 男   | 1958-12-02 00:00:00 | 教授   | 历史系 |
| 825 | 咪蒙   | 女   | 1998-12-12 00:00:00 | 讲师   | 心理系 |
| 831 | 高圆圆 | 女   | 1998-02-22 00:00:00 | 讲师   | 心理系 |
| 856 | 刘备   | 男   | 1968-12-12 00:00:00 | 副教授 | 工程系 |
+-----+--------+------+---------------------+--------+--------+
4 rows in set (0.00 sec)

mysql> insert into course values("3-105","心理学教育","825");
Query OK, 1 row affected (0.01 sec)

mysql> insert into course values("3-245","孙子兵法","804");
Query OK, 1 row affected (0.01 sec)

mysql> insert into course values("6-166","运筹学","856");
Query OK, 1 row affected (0.01 sec)

mysql> insert into course values("9-888","演员的自我修养","831");
Query OK, 1 row affected (0.01 sec)

mysql> select * from course;
+-------+----------------+-----+
| cnu   | cname          | tnu |
+-------+----------------+-----+
| 3-105 | 心理学教育     | 825 |
| 3-245 | 孙子兵法       | 804 |
| 6-166 | 运筹学         | 856 |
| 9-888 | 演员的自我修养 | 831 |
+-------+----------------+-----+
4 rows in set (0.00 sec)

mysql> insert into score values("109","6-166","81");
Query OK, 1 row affected (0.01 sec)

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)

查询练习题
1.查询student表中的所有记录

mysql> select * from student;
+------+----------+------+---------------------+-------+
| snum | sname    | ssex | sbirthday           | class |
+------+----------+------+---------------------+-------+
| 101  | 廉颇     | 男   | 1977-09-01 00:00:00 | 95033 |
| 102  | 张飞     | 男   | 1979-09-01 00:00:00 | 95033 |
| 103  | 晁盖     | 男   | 1970-09-01 00:00:00 | 95033 |
| 104  | 赵云     | 男   | 1980-09-01 00:00:00 | 95033 |
| 105  | 貂蝉     | 女   | 1994-09-01 00:00:00 | 95031 |
| 106  | 诸葛亮   | 男   | 1977-09-01 00:00:00 | 95031 |
| 107  | 金莲     | 女   | 1997-09-01 00:00:00 | 95031 |
| 108  | 镇关西   | 男   | 1967-09-01 00:00:00 | 95031 |
| 109  | 吴下阿蒙 | 男   | 1987-09-01 00:00:00 | 95031 |
+------+----------+------+---------------------+-------+
9 rows in set (0.00 sec)

2.查询student表中所有记录的sname,ssex和class列

mysql> select sname,ssex,class from student;
+----------+------+-------+
| sname    | ssex | class |
+----------+------+-------+
| 廉颇     | 男   | 95033 |
| 张飞     | 男   | 95033 |
| 晁盖     | 男   | 95033 |
| 赵云     | 男   | 95033 |
| 貂蝉     | 女   | 95031 |
| 诸葛亮   | 男   | 95031 |
| 金莲     | 女   | 95031 |
| 镇关西   | 男   | 95031 |
| 吴下阿蒙 | 男   | 95031 |
+----------+------+-------+
9 rows in set (0.01 sec)

3.查询教师表中不重复的depart列

mysql> select * from teacher;
+-----+--------+------+---------------------+--------+--------+
| tnu | tname  | tsex | tbirthday           | prof   | depart |
+-----+--------+------+---------------------+--------+--------+
| 804 | 曹操   | 男   | 1958-12-02 00:00:00 | 教授   | 历史系 |
| 825 | 咪蒙   | 女   | 1998-12-12 00:00:00 | 讲师   | 心理系 |
| 831 | 高圆圆 | 女   | 1998-02-22 00:00:00 | 讲师   | 心理系 |
| 856 | 刘备   | 男   | 1968-12-12 00:00:00 | 副教授 | 工程系 |
+-----+--------+------+---------------------+--------+--------+
4 rows in set (0.00 sec)
mysql> select distinct depart from teacher;
+--------+
| depart |
+--------+
| 历史系 |
| 心理系 |
| 工程系 |
+--------+
3 rows in set (0.01 sec)

4.查询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.01 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.01 sec)

5.查询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.01 sec)

6.查询student表中"95031"班或性别为"女"的同学记录

mysql> select * from student where class="95031" or ssex="女";
+------+----------+------+---------------------+-------+
| snum | sname    | ssex | sbirthday           | class |
+------+----------+------+---------------------+-------+
| 105  | 貂蝉     | 女   | 1994-09-01 00:00:00 | 95031 |
| 106  | 诸葛亮   | 男   | 1977-09-01 00:00:00 | 95031 |
| 107  | 金莲     | 女   | 1997-09-01 00:00:00 | 95031 |
| 108  | 镇关西   | 男   | 1967-09-01 00:00:00 | 95031 |
| 109  | 吴下阿蒙 | 男   | 1987-09-01 00:00:00 | 95031 |
+------+----------+------+---------------------+-------+
5 rows in set (0.00 sec)

7.以class降序查询student表的所有记录

mysql> select * from student order by class desc;
+------+----------+------+---------------------+-------+
| snum | sname    | ssex | sbirthday           | class |
+------+----------+------+---------------------+-------+
| 101  | 廉颇     | 男   | 1977-09-01 00:00:00 | 95033 |
| 102  | 张飞     | 男   | 1979-09-01 00:00:00 | 95033 |
| 103  | 晁盖     | 男   | 1970-09-01 00:00:00 | 95033 |
| 104  | 赵云     | 男   | 1980-09-01 00:00:00 | 95033 |
| 105  | 貂蝉     | 女   | 1994-09-01 00:00:00 | 95031 |
| 106  | 诸葛亮   | 男   | 1977-09-01 00:00:00 | 95031 |
| 107  | 金莲     | 女   | 1997-09-01 00:00:00 | 95031 |
| 108  | 镇关西   | 男   | 1967-09-01 00:00:00 | 95031 |
| 109  | 吴下阿蒙 | 男   | 1987-09-01 00:00:00 | 95031 |
+------+----------+------+---------------------+-------+
9 rows in set (0.01 sec)
#asc默认升序
mysql> select * from student order by class;
+------+----------+------+---------------------+-------+
| snum | sname    | ssex | sbirthday           | class |
+------+----------+------+---------------------+-------+
| 105  | 貂蝉     | 女   | 1994-09-01 00:00:00 | 95031 |
| 106  | 诸葛亮   | 男   | 1977-09-01 00:00:00 | 95031 |
| 107  | 金莲     | 女   | 1997-09-01 00:00:00 | 95031 |
| 108  | 镇关西   | 男   | 1967-09-01 00:00:00 | 95031 |
| 109  | 吴下阿蒙 | 男   | 1987-09-01 00:00:00 | 95031 |
| 101  | 廉颇     | 男   | 1977-09-01 00:00:00 | 95033 |
| 102  | 张飞     | 男   | 1979-09-01 00:00:00 | 95033 |
| 103  | 晁盖     | 男   | 1970-09-01 00:00:00 | 95033 |
| 104  | 赵云     | 男   | 1980-09-01 00:00:00 | 95033 |
+------+----------+------+---------------------+-------+
9 rows in set (0.00 sec)

8.以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)

9.查询"95031"班的学生人数

mysql> select count(*) from student where class="95031";
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

10.查询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)

排序(用排序查询的时候如果存在两个最高分会出现问题)

mysql> select * 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 * from score order by degree desc limit 0,1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.01 sec)

limit表示第一个数字从多少开始.第二个数字表示查多少条

mysql> select * from score order by degree desc limit 2,3;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
3 rows in set (0.00 sec)

11.查询每门课的平均成绩

mysql> select * from course;
+-------+----------------+-----+
| cnu   | cname          | tnu |
+-------+----------------+-----+
| 3-105 | 心理学教育     | 825 |
| 3-245 | 孙子兵法       | 804 |
| 6-166 | 运筹学         | 856 |
| 9-888 | 演员的自我修养 | 831 |
+-------+----------------+-----+
4 rows in set (0.01 sec)

mysql> select avg(degree) from score where cno="3-105";
+-------------+
| avg(degree) |
+-------------+
|     85.3333 |
+-------------+
1 row in set (0.01 sec)

在一个sql语句中查询每门课的平均成绩

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.01 sec)

12.查询score表中至少有两名学生选修的并以3开头的课程的平均分数
group by 后面跟条件需要用having

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)

mysql> select cno,avg(degree) from score group by cno having count(cno)>1 and cno like "3%";
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
+-------+-------------+
2 rows in set (0.00 sec)

mysql> select cno,avg(degree),count(cno) from score group by cno having count(cno)>1 and cno like "3%";
+-------+-------------+------------+
| cno   | avg(degree) | count(cno) |
+-------+-------------+------------+
| 3-105 |     85.3333 |          3 |
| 3-245 |     76.3333 |          3 |
+-------+-------------+------------+
2 rows in set (0.00 sec)

13.查询分数大于70,小于90的sno列

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.01 sec)

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)

14.查询所有学生的sname,cno和degree列

mysql> select sname,cno,degree from student,score where student.snum=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.01 sec)

15查询所有学生的sno,cname和degree列

mysql> select sno,cname,degree from course,score where course.cnu=score.cno;
+-----+------------+--------+
| sno | cname      | degree |
+-----+------------+--------+
| 103 | 心理学教育 |     92 |
| 103 | 孙子兵法   |     86 |
| 103 | 运筹学     |     85 |
| 105 | 心理学教育 |     88 |
| 105 | 孙子兵法   |     75 |
| 105 | 运筹学     |     79 |
| 109 | 心理学教育 |     76 |
| 109 | 孙子兵法   |     68 |
| 109 | 运筹学     |     81 |
+-----+------------+--------+
9 rows in set (0.00 sec)

16.查询所有学生的sname,cname和degree列

mysql> select sname,cname,degree from student,course,score
    -> where student.snum=score.sno and course.cnu=score.cno;
+----------+------------+--------+
| sname    | cname      | degree |
+----------+------------+--------+
| 晁盖     | 心理学教育 |     92 |
| 晁盖     | 孙子兵法   |     86 |
| 晁盖     | 运筹学     |     85 |
| 貂蝉     | 心理学教育 |     88 |
| 貂蝉     | 孙子兵法   |     75 |
| 貂蝉     | 运筹学     |     79 |
| 吴下阿蒙 | 心理学教育 |     76 |
| 吴下阿蒙 | 孙子兵法   |     68 |
| 吴下阿蒙 | 运筹学     |     81 |
+----------+------------+--------+
9 rows in set (0.01 sec)

mysql> select sname,cname,degree,student.snum,course.cnu from student,course,score
    -> where student.snum=score.sno and course.cnu=score.cno;
+----------+------------+--------+------+-------+
| sname    | cname      | degree | snum | cnu   |
+----------+------------+--------+------+-------+
| 晁盖     | 心理学教育 |     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.snum as stu_snum,course.cnu as cou_cnu from student,course,score
    -> where student.snum=score.sno and course.cnu=score.cno;
+----------+------------+--------+----------+---------+
| sname    | cname      | degree | stu_snum | cou_cnu |
+----------+------------+--------+----------+---------+
| 晁盖     | 心理学教育 |     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.01 sec)

17.查询"95031"班学生每门课的平均分

mysql> select * from student where class="95031";
+------+----------+------+---------------------+-------+
| snum | sname    | ssex | sbirthday           | class |
+------+----------+------+---------------------+-------+
| 105  | 貂蝉     | 女   | 1994-09-01 00:00:00 | 95031 |
| 106  | 诸葛亮   | 男   | 1977-09-01 00:00:00 | 95031 |
| 107  | 金莲     | 女   | 1997-09-01 00:00:00 | 95031 |
| 108  | 镇关西   | 男   | 1967-09-01 00:00:00 | 95031 |
| 109  | 吴下阿蒙 | 男   | 1987-09-01 00:00:00 | 95031 |
+------+----------+------+---------------------+-------+
5 rows in set (0.03 sec)

mysql> select * from score where sno in (select snum 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.01 sec)

mysql> select cno,avg(degree)
    -> from score
    -> where sno in (select snum 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.01 sec)

18.查询选修"3-105"课程的成绩高于"109"号同学"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.01 sec)

19.查询成绩高于学号为"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.02 sec)

20.查询和学号为108、101的同学同年出生的snum,sname和sbirthday列

mysql> select * from student where snum in (108,101);
+------+--------+------+---------------------+-------+
| snum | sname  | ssex | sbirthday           | class |
+------+--------+------+---------------------+-------+
| 101  | 廉颇   | 男   | 1977-09-01 00:00:00 | 95033 |
| 108  | 镇关西 | 男   | 1967-09-01 00:00:00 | 95031 |
+------+--------+------+---------------------+-------+
2 rows in set (0.01 sec)

mysql> select year(sbirthday) from student where snum in (108,101);
+-----------------+
| year(sbirthday) |
+-----------------+
|            1977 |
|            1967 |
+-----------------+
2 rows in set (0.01 sec)

mysql> select * from student where year(sbirthday) in (select year(sbirthday) from student where snum in (108,101));
+------+--------+------+---------------------+-------+
| snum | sname  | ssex | sbirthday           | class |
+------+--------+------+---------------------+-------+
| 101  | 廉颇   | 男   | 1977-09-01 00:00:00 | 95033 |
| 106  | 诸葛亮 | 男   | 1977-09-01 00:00:00 | 95031 |
| 108  | 镇关西 | 男   | 1967-09-01 00:00:00 | 95031 |
+------+--------+------+---------------------+-------+
3 rows in set (0.01 sec)

21.查询"咪蒙"教师任课的学生成绩

mysql> select tnu from teacher where tname="咪蒙";
+-----+
| tnu |
+-----+
| 825 |
+-----+
1 row in set (0.00 sec)

mysql> select cnu from course where tnu=(select tnu from teacher where tname="咪蒙");
+-------+
| cnu   |
+-------+
| 3-105 |
+-------+
1 row in set (0.00 sec)

mysql> select * from score where cno=(select cnu from course where tnu=(select tnu from teacher where tname="咪蒙"));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
3 rows in set (0.00 sec)

22.查询选修某课程的同学人数多于5人的教师姓名

mysql> insert into score values("101","3-105",90);
Query OK, 1 row affected (0.01 sec)

mysql> insert into score values("102","3-105","91");
Query OK, 1 row affected (0.01 sec)

mysql> insert into score values("104","3-105",89);
Query OK, 1 row affected (0.01 sec)

mysql> select * from score order by cno;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
12 rows in set (0.00 sec)

mysql> select cno from score group by cno having count(*)>5;
+-------+
| cno   |
+-------+
| 3-105 |
+-------+
1 row in set (0.01 sec)
mysql> select tnu from course where cnu=(select cno from score group by cno having count(*)>5);
+-----+
| tnu |
+-----+
| 825 |
+-----+
1 row in set (0.00 sec)
mysql> select * from teacher where tnu=(select tnu from course where cnu=(select cno from score group by cno having count(*)>5));
+-----+-------+------+---------------------+------+--------+
| tnu | tname | tsex | tbirthday           | prof | depart |
+-----+-------+------+---------------------+------+--------+
| 825 | 咪蒙  | 女   | 1998-12-12 00:00:00 | 讲师 | 心理系 |
+-----+-------+------+---------------------+------+--------+
1 row in set (0.00 sec)

23.查询"95033"班与"95031"班全体学生记录

mysql> select * from student where class in ("95031","95033");
+------+----------+------+---------------------+-------+
| snum | sname    | ssex | sbirthday           | class |
+------+----------+------+---------------------+-------+
| 101  | 廉颇     | 男   | 1977-09-01 00:00:00 | 95033 |
| 102  | 张飞     | 男   | 1979-09-01 00:00:00 | 95033 |
| 103  | 晁盖     | 男   | 1970-09-01 00:00:00 | 95033 |
| 104  | 赵云     | 男   | 1980-09-01 00:00:00 | 95033 |
| 105  | 貂蝉     | 女   | 1994-09-01 00:00:00 | 95031 |
| 106  | 诸葛亮   | 男   | 1977-09-01 00:00:00 | 95031 |
| 107  | 金莲     | 女   | 1997-09-01 00:00:00 | 95031 |
| 108  | 镇关西   | 男   | 1967-09-01 00:00:00 | 95031 |
| 109  | 吴下阿蒙 | 男   | 1987-09-01 00:00:00 | 95031 |
+------+----------+------+---------------------+-------+
9 rows in set (0.01 sec)

24.查询存在有85分以上成绩的课程cno

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.01 sec)

25.查询"心理系"教师所教课程的成绩表

mysql> select * from teacher where depart="心理系";
+-----+--------+------+---------------------+------+--------+
| tnu | tname  | tsex | tbirthday           | prof | depart |
+-----+--------+------+---------------------+------+--------+
| 825 | 咪蒙   | 女   | 1998-12-12 00:00:00 | 讲师 | 心理系 |
| 831 | 高圆圆 | 女   | 1998-02-22 00:00:00 | 讲师 | 心理系 |
+-----+--------+------+---------------------+------+--------+
2 rows in set (0.01 sec)

mysql> select * from course where tnu in (select tnu from teacher where depart="心理系");
+-------+----------------+-----+
| cnu   | cname          | tnu |
+-------+----------------+-----+
| 3-105 | 心理学教育     | 825 |
| 9-888 | 演员的自我修养 | 831 |
+-------+----------------+-----+
2 rows in set (0.00 sec)

mysql> select * from score where cno in (select cnu from course where tnu in (select tnu from teacher where depart="心理系"));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
6 rows in set (0.00 sec)

26.查询"计算机系"与"电子工程系"不同职称的tname和prof

mysql> insert into teacher values("911","李冰","男","1988-02-02","副教授","计算机系");
Query OK, 1 row affected (0.01 sec)

mysql> insert into teacher values("912","王平","男","1988-02-02","助教","计算机系");
Query OK, 1 row affected (0.01 sec)

mysql> insert into teacher values("913","秋雅","女","1988-02-03","助教","电子工程系");
Query OK, 1 row affected (0.01 sec)

mysql> insert into teacher values("914","静香","女","1998-02-03","讲师","电子工程系");
Query OK, 1 row affected (0.01 sec)

mysql> select * from teacher;
+-----+--------+------+---------------------+--------+------------+
| tnu | tname  | tsex | tbirthday           | prof   | depart     |
+-----+--------+------+---------------------+--------+------------+
| 804 | 曹操   | 男   | 1958-12-02 00:00:00 | 教授   | 历史系     |
| 825 | 咪蒙   | 女   | 1998-12-12 00:00:00 | 讲师   | 心理系     |
| 831 | 高圆圆 | 女   | 1998-02-22 00:00:00 | 讲师   | 心理系     |
| 856 | 刘备   | 男   | 1968-12-12 00:00:00 | 副教授 | 工程系     |
| 911 | 李冰   | 男   | 1988-02-02 00:00:00 | 副教授 | 计算机系   |
| 912 | 王平   | 男   | 1988-02-02 00:00:00 | 助教   | 计算机系   |
| 913 | 秋雅   | 女   | 1988-02-03 00:00:00 | 助教   | 电子工程系 |
| 914 | 静香   | 女   | 1998-02-03 00:00:00 | 讲师   | 电子工程系 |
+-----+--------+------+---------------------+--------+------------+
8 rows in set (0.00 sec)

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="计算机系");
+-----+-------+------+---------------------+--------+------------+
| tnu | tname | tsex | tbirthday           | prof   | depart     |
+-----+-------+------+---------------------+--------+------------+
| 911 | 李冰  | 男   | 1988-02-02 00:00:00 | 副教授 | 计算机系   |
| 914 | 静香  | 女   | 1998-02-03 00:00:00 | 讲师   | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+
2 rows in set (0.01 sec)

27.查询选修编号为"3-105"课程且成绩至少高于选修编号"3-245"的同学的cno,sno和degree,并按照degree降序排列

mysql> select * from score where cno="3-245";
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
+-----+-------+--------+
3 rows in set (0.01 sec)

mysql> select * from score where cno="3-105";
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
6 rows in set (0.00 sec)

至少–大于其中至少一个,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.01 sec)

28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学的cno,sno和degree
all表示所有的关系

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.07 sec)

29.查询所有教师和同学的name、sex和birthday

mysql> select tname,tsex,tbirthday from teacher;
+--------+------+---------------------+
| tname  | tsex | tbirthday           |
+--------+------+---------------------+
| 曹操   | 男   | 1958-12-02 00:00:00 |
| 咪蒙   | 女   | 1998-12-12 00:00:00 |
| 高圆圆 | 女   | 1998-02-22 00:00:00 |
| 刘备   | 男   | 1968-12-12 00:00:00 |
| 李冰   | 男   | 1988-02-02 00:00:00 |
| 王平   | 男   | 1988-02-02 00:00:00 |
| 秋雅   | 女   | 1988-02-03 00:00:00 |
| 静香   | 女   | 1998-02-03 00:00:00 |
+--------+------+---------------------+
8 rows in set (0.01 sec)

mysql> select sname,ssex,sbirthday from student;
+----------+------+---------------------+
| sname    | ssex | sbirthday           |
+----------+------+---------------------+
| 廉颇     | 男   | 1977-09-01 00:00:00 |
| 张飞     | 男   | 1979-09-01 00:00:00 |
| 晁盖     | 男   | 1970-09-01 00:00:00 |
| 赵云     | 男   | 1980-09-01 00:00:00 |
| 貂蝉     | 女   | 1994-09-01 00:00:00 |
| 诸葛亮   | 男   | 1977-09-01 00:00:00 |
| 金莲     | 女   | 1997-09-01 00:00:00 |
| 镇关西   | 男   | 1967-09-01 00:00:00 |
| 吴下阿蒙 | 男   | 1987-09-01 00:00:00 |
| 马云     | 男   | 1974-06-03 00:00:00 |
+----------+------+---------------------+
10 rows in set (0.01 sec)

mysql> select tname,tsex,tbirthday from teacher
    -> union
    -> select sname,ssex,sbirthday from student;
+----------+------+---------------------+
| tname    | tsex | tbirthday           |
+----------+------+---------------------+
| 曹操     | 男   | 1958-12-02 00:00:00 |
| 咪蒙     | 女   | 1998-12-12 00:00:00 |
| 高圆圆   | 女   | 1998-02-22 00:00:00 |
| 刘备     | 男   | 1968-12-12 00:00:00 |
| 李冰     | 男   | 1988-02-02 00:00:00 |
| 王平     | 男   | 1988-02-02 00:00:00 |
| 秋雅     | 女   | 1988-02-03 00:00:00 |
| 静香     | 女   | 1998-02-03 00:00:00 |
| 廉颇     | 男   | 1977-09-01 00:00:00 |
| 张飞     | 男   | 1979-09-01 00:00:00 |
| 晁盖     | 男   | 1970-09-01 00:00:00 |
| 赵云     | 男   | 1980-09-01 00:00:00 |
| 貂蝉     | 女   | 1994-09-01 00:00:00 |
| 诸葛亮   | 男   | 1977-09-01 00:00:00 |
| 金莲     | 女   | 1997-09-01 00:00:00 |
| 镇关西   | 男   | 1967-09-01 00:00:00 |
| 吴下阿蒙 | 男   | 1987-09-01 00:00:00 |
| 马云     | 男   | 1974-06-03 00:00:00 |
+----------+------+---------------------+
18 rows in set (0.01 sec)

使用union之后,查询的结果虽然合并了,但是字段名称却都是tname,tsex,tbirthday,所以需要自定义字段
使用别名 as

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 |
| 咪蒙     | 女  | 1998-12-12 00:00:00 |
| 高圆圆   | 女  | 1998-02-22 00:00:00 |
| 刘备     | 男  | 1968-12-12 00:00:00 |
| 李冰     | 男  | 1988-02-02 00:00:00 |
| 王平     | 男  | 1988-02-02 00:00:00 |
| 秋雅     | 女  | 1988-02-03 00:00:00 |
| 静香     | 女  | 1998-02-03 00:00:00 |
| 廉颇     | 男  | 1977-09-01 00:00:00 |
| 张飞     | 男  | 1979-09-01 00:00:00 |
| 晁盖     | 男  | 1970-09-01 00:00:00 |
| 赵云     | 男  | 1980-09-01 00:00:00 |
| 貂蝉     | 女  | 1994-09-01 00:00:00 |
| 诸葛亮   | 男  | 1977-09-01 00:00:00 |
| 金莲     | 女  | 1997-09-01 00:00:00 |
| 镇关西   | 男  | 1967-09-01 00:00:00 |
| 吴下阿蒙 | 男  | 1987-09-01 00:00:00 |
| 马云     | 男  | 1974-06-03 00:00:00 |
+----------+-----+---------------------+
18 rows in set (0.01 sec)

30.查询所有女教师和女同学的name、sex和birthday

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            |
+--------+-----+---------------------+
| 咪蒙   | 女  | 1998-12-12 00:00:00 |
| 高圆圆 | 女  | 1998-02-22 00:00:00 |
| 秋雅   | 女  | 1988-02-03 00:00:00 |
| 静香   | 女  | 1998-02-03 00:00:00 |
| 貂蝉   | 女  | 1994-09-01 00:00:00 |
| 金莲   | 女  | 1997-09-01 00:00:00 |
+--------+-----+---------------------+
6 rows in set (0.01 sec)

31.查询成绩比该课程平均成绩低的同学的成绩表

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.01 sec)

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.02 sec)

32.查询所有任课教师的tname和depart
意味着课程表中安排了课程

mysql> select * from teacher where tnu in (select tnu from course);
+-----+--------+------+---------------------+--------+--------+
| tnu | tname  | tsex | tbirthday           | prof   | depart |
+-----+--------+------+---------------------+--------+--------+
| 804 | 曹操   | 男   | 1958-12-02 00:00:00 | 教授   | 历史系 |
| 825 | 咪蒙   | 女   | 1998-12-12 00:00:00 | 讲师   | 心理系 |
| 831 | 高圆圆 | 女   | 1998-02-22 00:00:00 | 讲师   | 心理系 |
| 856 | 刘备   | 男   | 1968-12-12 00:00:00 | 副教授 | 工程系 |
+-----+--------+------+---------------------+--------+--------+
4 rows in set (0.00 sec)

33.查询至少有两名男生的班号

mysql> select class from student where ssex="男" group by class having count(*)>1;
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+
2 rows in set (0.01 sec)

34.查询student表中不姓"金"的同学记录

mysql> select * from student where sname not like "金%";
+------+----------+------+---------------------+-------+
| snum | sname    | ssex | sbirthday           | class |
+------+----------+------+---------------------+-------+
| 101  | 廉颇     | 男   | 1977-09-01 00:00:00 | 95033 |
| 102  | 张飞     | 男   | 1979-09-01 00:00:00 | 95033 |
| 103  | 晁盖     | 男   | 1970-09-01 00:00:00 | 95033 |
| 104  | 赵云     | 男   | 1980-09-01 00:00:00 | 95033 |
| 105  | 貂蝉     | 女   | 1994-09-01 00:00:00 | 95031 |
| 106  | 诸葛亮   | 男   | 1977-09-01 00:00:00 | 95031 |
| 108  | 镇关西   | 男   | 1967-09-01 00:00:00 | 95031 |
| 109  | 吴下阿蒙 | 男   | 1987-09-01 00:00:00 | 95031 |
| 110  | 马云     | 男   | 1974-06-03 00:00:00 | 95038 |
+------+----------+------+---------------------+-------+
9 rows in set (0.00 sec)

35.查询student表中每个学生的姓名和年龄
年龄=当前年龄-出生年份

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2019 |
+-------------+
1 row in set (0.00 sec)

mysql> select year(sbirthday) from student;
+-----------------+
| year(sbirthday) |
+-----------------+
|            1977 |
|            1979 |
|            1970 |
|            1980 |
|            1994 |
|            1977 |
|            1997 |
|            1967 |
|            1987 |
|            1974 |
+-----------------+
10 rows in set (0.00 sec)

mysql> select sname,year(now())-year(sbirthday) as "年龄" from student;
+----------+------+
| sname    | 年龄 |
+----------+------+
| 廉颇     |   42 |
| 张飞     |   40 |
| 晁盖     |   49 |
| 赵云     |   39 |
| 貂蝉     |   25 |
| 诸葛亮   |   42 |
| 金莲     |   22 |
| 镇关西   |   52 |
| 吴下阿蒙 |   32 |
| 马云     |   45 |
+----------+------+
10 rows in set (0.00 sec)

36.查询student表中最大和最小的sbirthday日期值

mysql> select max(sbirthday) as "最大",min(sbirthday) as "最小" from student;
+---------------------+---------------------+
| 最大                | 最小                |
+---------------------+---------------------+
| 1997-09-01 00:00:00 | 1967-09-01 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

37.以班号和年龄从大到小的顺序查询student表中的全部记录

mysql> select * from student order by class desc,sbirthday;
+------+----------+------+---------------------+-------+
| snum | sname    | ssex | sbirthday           | class |
+------+----------+------+---------------------+-------+
| 110  | 马云     | 男   | 1974-06-03 00:00:00 | 95038 |
| 103  | 晁盖     | 男   | 1970-09-01 00:00:00 | 95033 |
| 101  | 廉颇     | 男   | 1977-09-01 00:00:00 | 95033 |
| 102  | 张飞     | 男   | 1979-09-01 00:00:00 | 95033 |
| 104  | 赵云     | 男   | 1980-09-01 00:00:00 | 95033 |
| 108  | 镇关西   | 男   | 1967-09-01 00:00:00 | 95031 |
| 106  | 诸葛亮   | 男   | 1977-09-01 00:00:00 | 95031 |
| 109  | 吴下阿蒙 | 男   | 1987-09-01 00:00:00 | 95031 |
| 105  | 貂蝉     | 女   | 1994-09-01 00:00:00 | 95031 |
| 107  | 金莲     | 女   | 1997-09-01 00:00:00 | 95031 |
+------+----------+------+---------------------+-------+
10 rows in set (0.01 sec)

38.查询男教师及其所上的课程

mysql> select * from course where tnu in (select tnu from teacher where tsex="男");
+-------+----------+-----+
| cnu   | cname    | tnu |
+-------+----------+-----+
| 3-245 | 孙子兵法 | 804 |
| 6-166 | 运筹学   | 856 |
+-------+----------+-----+
2 rows in set (0.00 sec)

39.查询最高分同学的snum、cnu和degree列

mysql> select * from score where degree=(select max(degree) from score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.01 sec)

40.查询和张飞同学同性别的所有同学的sname

mysql> select sname from student where ssex = (select ssex from student where sname="张飞");
+----------+
| sname    |
+----------+
| 廉颇     |
| 张飞     |
| 晁盖     |
| 赵云     |
| 诸葛亮   |
| 镇关西   |
| 吴下阿蒙 |
| 马云     |
+----------+
8 rows in set (0.01 sec)

41.查询和张飞同性别并同班的同学sname

mysql> select sname from student where ssex = (select ssex from student where sname="张飞") and class = (select class from student where sname="张飞");
+-------+
| sname |
+-------+
| 廉颇  |
| 张飞  |
| 晁盖  |
| 赵云  |
+-------+
4 rows in set (0.00 sec)

42.查询所有选修心理学教育课程的男同学的成绩表

mysql> select * from score
    -> where cno=(select cnu from course where cname="心理学教育")
    -> and sno in (select snum from student where ssex="男");
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
5 rows in set (0.01 sec)

43.使用如下命令建立一个grade表:

mysql> create table grade(
    -> low int(3),
    -> upp int(3),
    -> grade char(1)
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into grade values(90,100,"A");
Query OK, 1 row affected (0.01 sec)
mysql> insert into grade values(80,89,"B");
Query OK, 1 row affected (0.01 sec)
mysql> insert into grade values(70,79,"C");
Query OK, 1 row affected (0.01 sec)
mysql> insert into grade values(60,69,"D");
Query OK, 1 row affected (0.01 sec)
mysql> insert into grade values(0,59,"E");
Query OK, 1 row affected (0.01 sec)
mysql> select * from grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+
5 rows in set (0.00 sec)

现查询所有同学的snu,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.01 sec)

17.SQL的四种连接查询
内链接
inner join 或者 join
外链接
1.左链接left join 或者left outer join
2.右链接right join 或者right outer join
3.完全外链接 full join 或者 full outer join
举例说明:
先创建一个数据库

mysql> create database testjoin;
Query OK, 1 row affected (0.00 sec)

mysql> use testjoin;
Database changed

再创建两个表

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

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

mysql> show tables;
+--------------------+
| Tables_in_testjoin |
+--------------------+
| card               |
| person             |
+--------------------+
2 rows in set (0.00 sec)

添加数据

mysql> 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,"邮政卡");
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
mysql> select * from card;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 饭卡   |
|    2 | 建行卡 |
|    3 | 农行卡 |
|    4 | 工商卡 |
|    5 | 邮政卡 |
+------+--------+
5 rows in set (0.00 sec)
mysql> insert into person values(1,"张三",1);insert into person values(2,"李四",3);insert into person values(3,"王五",6);
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

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

并没有创建外键,
1.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.01 sec)

2.left join(左外链接)
会把左边表里面的所有数据取出来,而右边表中的数据,如果有相等的就显示出来,如果没有就补NULL

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)

3.right join(右外链接)
会把右边表里面的所有数据取出来,而左边表中的数据,如果有相等的就显示出来,如果没有就补NULL

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)

4.full join(全外链接)
mysql不支持全外链接

mysql> select * from person full join card on person.cardId=card.id;
ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

如何实现,通过union合并左右连接

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.01 sec)

18.mysql事务
mysql中,事务其实是一个最小的不可分割的工作单元.事务能够保证一个业务的完整性
比如我们的银行转账:
a→-100

update user set money=money-100 where name="a";

b→+100

update user set money=money+100 where name="b";

在实际的程序中,如果只有一条语句执行成功,而另外一条没有执行成功?
出现数据前后不一致.
多条sql语句,可能会有同时成功的要求,要么同时成功或者同时失败
mysql中如何控制事务?
1.mysql默认是开启事务的(自动提交)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

默认事务开启的作用是什么?
当我们去执行一个sql语句的时候,效果会立即体现出来,且不能回滚

mysql> create database bank;
Query OK, 1 row affected (0.00 sec)

mysql> use bank;
Database changed
mysql> create table user(
    -> id int primary key,
    -> name varchar(20),
    -> money int
    -> );
Query OK, 0 rows affected (0.02 sec)

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)

事务回滚:撤销sql语句执行效果
rollback;

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

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

可以看到回滚失败,如何解决呢?
设置mysql自动提交为false

mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

上面的操作,关闭了mysql的自动提交

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.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 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.01 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)

自动提交@@autocommit=1
手动提交commit
事务回滚rollback
如果说这个时候转账

mysql> update user set money=money-100 where name="a";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money=money+100 where name="b";
Query OK, 1 row affected (0.01 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> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

事务给我们提供了一个返回的机会

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> update user set money=money-100 where name="a";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money=money+100 where name="b";
Query OK, 1 row affected (0.01 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> 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)

但是在执行sql语句的时候在前面加上begin就可以被撤销

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.01 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.01 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)

看看加上start transaction;同样可以回滚

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-100 where name="a";
Query OK, 1 row affected (0.01 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.01 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)

事务开启之后,一旦commit提交,就不可以回滚(也就是当前的这个事务在提交的时候就结束了)

mysql> update user set money=money-100 where name="a";
Query OK, 1 row affected (0.01 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> 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    |   800 |
|  2 | b    |  1200 |
+----+------+-------+
2 rows in set (0.00 sec)

事务的四大特征:
A 原子性:事务是最小的单位,不可以再分割
C 一致性:事务要求,同一事物的sql语句,必须保证同时成功或者同时失败
I 隔离性:事务1和事务2是具有隔离性的
D 持久性:事务一旦结束(commit,rollback),就不可以返回
事务开启:
1.修改默认提交 set autocommit=0;
2.begin;
3.start transaction
事务手动提交:commit
事务手动回滚:rollback
事务的隔离性:
1.read uncommited; 读未提交的
2.read commited; 读已经提交的
3.repeatable read; 可以重复读
4.serializable; 串行化
1-read uncommited
如果有事务a和事务b,
a 事务对数据进行操作,在操作的过程中,事务没有被提交,但是b可以看见a操作的结果
bank数据库 user表

mysql> insert into user values(3,"小明",1000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values(4,"淘宝店",1000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  3 | 小明   |  1000 |
|  4 | 淘宝店 |  1000 |
+----+--------+-------+
4 rows in set (0.00 sec)

如何查看数据库的隔离级别?
mysql 8.0:
系统级别

select @@global.transaction_isolation;

会话级别

select @@transaction_isolation;

mysql 5.x:

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.01 sec)

或者

select @@tx_isolation;

如何修改隔离级别?

mysql> set global transaction isolation level Read unCommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+
1 row in set (0.00 sec)

转账:小明在淘宝店买鞋子:100元
小明→成都 ATM
淘宝店→广州 ATM

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-100 where name="小明";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set money=money+100 where name="淘宝店";
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 |
|  3 | 小明   |   900 |
|  4 | 淘宝店 |  1100 |
+----+--------+-------+
4 rows in set (0.00 sec)

给淘宝店打电话去查账
淘宝店在广州查账到账了

mysql> use bank;
Database changed
mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  3 | 小明   |   900 |
|  4 | 淘宝店 |  1100 |
+----+--------+-------+
4 rows in set (0.00 sec)

晚上去消费了1100,结账的时候发现钱不够,原因是,小明在成都做了rollback操作

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

mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  3 | 小明   |  1000 |
|  4 | 淘宝店 |  1000 |
+----+--------+-------+
4 rows in set (0.00 sec)

如果两个不同的地方,都在进行操作,如果事务a开启之后,他的数据可以被其他事务读取到
这样就会出现脏读
脏读:一个事务读到了另外一个事务没有提交的数据.
实际开发是不允许脏读出现的
2.read committed;读取已经提交的
先修改隔离级别为READ-COMMITTED

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

bank 数据库 user 表
小张:银行的会计

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  3 | 小明   |  1000 |
|  4 | 淘宝店 |  1000 |
+----+--------+-------+
4 rows in set (0.01 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.02 sec)

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

mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  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下,会出现不可重复读的现象
3.repeatable read:可重复读

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.01 sec)

在REPEATABLE-READ隔离级别下又会出现什么问题?

mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  3 | 小明   |  1000 |
|  4 | 淘宝店 |  1000 |
|  5 | c      |   100 |
+----+--------+-------+
5 rows in set (0.01 sec)

张全蛋–成都

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

王尼玛–北京

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
查询数据
mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  3 | 小明   |  1000 |
|  4 | 淘宝店 |  1000 |
|  5 | c      |   100 |
+----+--------+-------+
5 rows in set (0.01 sec)

张全蛋–成都

mysql> insert into user values(6,"d",1000);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
查询数据
mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  3 | 小明   |  1000 |
|  4 | 淘宝店 |  1000 |
|  5 | c      |   100 |
|  6 | d      |  1000 |
+----+--------+-------+
6 rows in set (0.01 sec)

张全蛋提交之后王尼玛再查询数据

mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  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 'PRIMARY'

这种现象就叫做幻读:
事务a和事务b同时操作一张表,事务a提交的数据也不能被事务b读到,就会出现幻读.
4.serializable 串行化
修改隔离级别串行化

mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE          |
+-----------------------+
1 row in set (0.00 sec)

张全蛋-成都

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

王尼玛-北京

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

张全蛋-成都

mysql> insert into user values(7,"花木兰",1000);
Query OK, 1 row affected (0.01 sec)

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

mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  3 | 小明   |  1000 |
|  4 | 淘宝店 |  1000 |
|  5 | c      |   100 |
|  6 | d      |  1000 |
|  7 | 花木兰 |  1000 |
+----+--------+-------+
7 rows in set (0.01 sec)

王尼玛-北京

mysql> select * from user;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | a      |   800 |
|  2 | b      |  1200 |
|  3 | 小明   |  1000 |
|  4 | 淘宝店 |  1000 |
|  5 | c      |   100 |
|  6 | d      |  1000 |
|  7 | 花木兰 |  1000 |
+----+--------+-------+
7 rows in set (0.00 sec)

张全蛋-成都
进行写入操作是卡住了

mysql> insert into user values(8,"穆桂英",1000);

王尼玛-北京
事务一旦提交,张全蛋的插入数据就可以执行了

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

张全蛋-成都

mysql> insert into user values(8,"穆桂英",1000);
Query OK, 1 row affected (11.62 sec)

当user表被另外一个事务操作的时候,其他事务里面的写操作是不可进行的,
只能进入排队状态(串行化),直到王尼玛那边事务结束之后,张全蛋的这个写入操作才会执行(在没有等待超时的情况下)

那么串行化带来的问题是,性能特差
性能由好到差排序
READ-UNCOMMITTED > READ-COMMITTED>REPEATABLE-READ>SERIALIZABLE
隔离级别越高,性能越差
mysql默认隔离级别是REPEATABLE-READ

急着吃饭的李先生
关注
专栏目录
decode bytes in position 2-3: truncated \UXXXXXXXX escape
jacke121的专栏
07-15 9550
decode bytes in position 2-3: truncated \UXXXXXXXX escape
设有一个 Student 数据库，包括 数据库，包括 学生 ， 课程 ， 选修三个关系： 三个关系： 学生 学生:S ( Sno ，Sname ，Ssex ，Sage,Sdept ) 课程 课程:
热门推荐
海触的博客
04-07 4万+
设有一个 Student 数据库，包括 数据库，包括 学生 ， 课程 ， 选修三个关系： 三个关系： 学生 学生:S ( Sno ，Sname ，Ssex ，Sage,Sdept ) 课程 课程:C ( Cno ，Cname ，Term,Credit ) 选修 选修:SC ( Sno ，Cno ，Score ) 其中：学生关系 S 的属性分别表示：学号、姓名、性别、 的属性分别表示：学号...
SQl_Server--在练习中记忆
FOR DREAM
06-16 9425
实验1 数据库操作... 1 1．创建数据库：... 1 2．查看数据库属性：... 2 实验1 数据库操作... 1 1．创建数据库：... 1 2．查看数据库属性：... 2 3．删除数据库：... 2 实验2表操作... 2 1．创建表：... 2 2．修改表结构：... 5 3．删除表... 6 4．向表中输入数据记录... 6 实验3数据完
SQL语句
Pang的博客
12-16 1225
SQL基础应用 SQL语句自动补全 yum install python-pip -y pip install mycli SQL的介绍 SQL-92标准 SQL-99标准 SQL常用分类 DDL 数据定义语言 DCL 数据控制语言 DML 数据操作语言 DQL 数据查询语言 表的核心属性介绍 列的属性 数据类型 数字类型 tinyint -128-127 ...
MySQL57 服务正在启动 . MySQL57 服务无法启动。 服务没有报告任何错误。 请键入 NET HELPMSG 3534 以获得更多的帮助。
weixin_41987553的博客
05-26 3万+
以上为错误提示，解决方案如下：1.在C:\ProgramData\MySQL\MySQL Server 5.7中删除data文件夹，并在这里找到my.ini文件，复制到C:\Windows\system32&gt;cd C:\Program Files\MySQL\MySQL Server 5.7目录。2.用管理员权限运行cmd,执行C:\Program Files\MySQL\MySQL Ser...
Third season first episode,Ross‘s sex fantasy!!!
xiyangxia666hui的博客
10-02 8272
[Scene Central Perk, the whole gang is entering] Joey: I'm tellin' ya that girl totally winked at me. All: Did not, she did not wink at you… (sees that their sacred couch is occupied by strangers) . Chandler: Huh. (They all leave, dejected) Opening Credits
MySQL学习笔记
12-19
MySQL笔记MySQL笔记MySQL笔记MySQL笔记MySQL笔记MySQL笔记
mysql学习笔记
12-16
mysql基础学习的知识总结和学习过程的知识点..........
python入门到精通
xiongsheng
06-03 4万+
1.python基础知识 1.0 计算机组成原理和python基础知识 一，计算机基础 1、计算机硬件组成 1.1 一个程序的运行与三大核心硬件（cpu、内存、硬盘）的关系 （*****） 2、操作系统 2.1 计算机体系的三层结构 （*****） 2.2 平台与跨平台的概念 （*****） 3、扫盲（***） cpu相关（x86-64代表的意义） 存储器相关（IO问题） 操作系统启动流程 1.1 计算机组成原理 二，计算机组成 1. 什
MySQL复制跳过错误--slave_skip_errors、sql_slave_skip_counter、slave_exec_mode
coco3600的博客
07-22 4164
MySQL主从复制中的跳过错误处理参数--slave_skip_errors、sql_slave_skip_counter、slave_exec_mode 跳过复制错误——slave_s...
C++课程学习代码汇总基础
Andr3zzZZ的博客
11-10 1279
写在开头的话：  今天难得一天没空。打算把之前学习过的课程再重新看一遍。写了好多代码，编译运行之后都删掉写下一个了。 久而久之不免觉得浪费，现在把它们都贴在下面把。就跟看着自己的孩子一样。说不定以后有利用的价值，没事儿还可以翻出来看看。 （不定时补充） 文字信息统计#include using namespace std; int main() { const int N=100
数据库期末复习
xiaoka__的博客
12-08 1876
把不太会的sql语句写到这里，时常看看。 阴：列名是英文，显示要中文。如：select SNO as 学号, Sname as 姓名, AGE as 年龄, SEX as 性别 from S where SNO in (select Sno from SC where CNO=‘1001’) and SNO in (select Sno from SC where CNO=‘1003’) 1.select S.SNO as 学号,SNAME as 姓名, AVG(grade) as 平均分 from S
MySQL学习笔记.zip
最新发布
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
【错误】netstart mysql服务正在启动或停止中，请稍候片刻后再试一次
08-18 1万+
1.在任务管理器将mysqld进程结束掉 2.重新输入命令 net start mysql 解决C:\Users\Administrator>net start mysql MySQL 服务正在启动 . MySQL 服务已经启动成功。
javascript获取系统时间时区_如何使用javascript获取特定时区的时间，但与当前系统时间无关？...
weixin_39963465的博客
12-22 103
Now, before you mark this as a duplicate, please note that all the useful answers in the possible duplicate provide functions which work in relation to the current system time, which I cannot accept.W...
net start MySQL57 MySQL57 服务正在启动 . MySQL57 服务无法启动。
ElegantHedgehog的博客
11-25 1557
造成这种情况的原因有很多，如果直接百度错误信息的话，不一定能很快解决问题，所以，出现这种情况，我们可以使用 mysqld --console 命令来查看报错信息，然后根据报错信息来百度，这样就很快定位到问题所在。看到以下截图，我的问题是 Please make sure that directory exists and is accessible by MySQL Server. Supplied value : D:\My_Program_Files\mysql5.7.29\Uploads ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值