Mysql学习笔记
一、数据库基本概念
数据库： DateBase 简称 DB
-
数据库的特点：
（1）持久化存储数据的。其实是一个文件系统。
（2）方便存储和管理数据
（3）使用统一的方式操作数据库 —SQL
-
常见数据库软件：Oracle 、Mysql 、SQL server 等。
二、基本语法
查看数据库：
show databases;
使用数据库（mysql为例子）：
use mysql;
查看数据库中的表:
show tables;
创建数据库：
create * database test;
创建数据表：
create table pet(
name varchar(20),
owner varchar(20),
species varchar(20),
sex char(1),
birth date,
death date);
查看数据表的结构：
describe pet;
给数据表添加数据：
insert into pet
values('旺财','萧辽','狗','公','2020-01-01',null);
mysql支持三种数据类型：数值 、日期/时间 、字符串（字符）类型
增删改查
添加数据（增 : insert）
insert into pet values('Fluffy','Harold','cat','f','1993-02-04',null);
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);
insert into pet values('Whistler','Gwen','bird',null,'1997-12-09',null);
insert into pet values('Slim','Benny','snake','m','1996-04-29',null);
insert into pet values('Ouffball','Diane','amster','f','1999-03-30',null);
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| 旺财 | 萧辽 | 狗 | 公 | 2020-01-30 | NULL |
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Fluffy | Harold | cat | f | 1993-02-04 | 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 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Ouffball | Diane | amster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
删除数据（删 : delete）
delete from pet where name='Fluffy';
mysql> delete from pet where name='Fluffy';
Query OK, 2 rows affected (0.54 sec)
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| 旺财 | 萧辽 | 狗 | 公 | 2020-01-30 | 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 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Ouffball | Diane | amster | f | 1999-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)
修改数据（改 ：update）
update pet set name='大黄' where owenr='萧辽';
mysql> update pet set name='大黄' where owner='萧辽';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| 大黄 | 萧辽 | 狗 | 公 | 2020-01-30 | 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 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Ouffball | Diane | amster | f | 1999-03-30 | NULL |
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
+----------+--------+---------+------+------------+------------+
10 rows in set (0.00 sec)
查找(看)数据（查：select）
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| 大黄 | 萧辽 | 狗 | 公 | 2020-01-30 | 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 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Ouffball | Diane | amster | f | 1999-03-30 | NULL |
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
+----------+--------+---------+------+------------+------------+
10 rows in set (0.00 sec)
mysql> select * from pet where sex='m';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+--------+-------+---------+------+------------+------------+
4 rows in set (0.00 sec)
约束
主键约束 primary key（添加主键的属性不能重复且为空）
create table user1(
id int primary key,
name varchar(20),
age int
)
mysql> create table user1(
-> id int primary key,
-> name varchar(20),
-> age int);
Query OK, 0 rows affected (0.39 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
| user1 |
+----------------+
2 rows in set (0.00 sec)
mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into user1 values(1,'张三',20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user1;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 20 |
+----+--------+------+
1 row in set (0.00 sec)
mysql> insert into user1 values(1,'张三',20);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into user1 values(,'张三',20);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''张三',20)' at line 1
mysql> insert into user1 values(2,'张三',20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user1;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 张三 | 20 |
| 2 | 张三 | 20 |
+----+--------+------+
2 rows in set (0.00 sec)
联合主键（每个主键字段不能为空，可重复）
create table user2(
id int,
name varchar(20),
password varchar(20),
primary key(id,name)
);
mysql> create table user2(
-> id int,
-> name varchar(20),
-> password varchar(20),
-> primary key(id,name)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc 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)
mysql> insert into user2 values(1,'张三','123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user2;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | 张三 | 123 |
+----+--------+----------+
1 row in set (0.00 sec)
mysql> insert into user2 values(null,'张三','123');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into user2 values(1,null,'123');
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into user2 values(2,'张三','123');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user2 values(1,'李四','123');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user2;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | 张三 | 123 |
| 1 | 李四 | 123 |
| 2 | 张三 | 123 |
+----+--------+----------+
3 rows in set (0.00 sec)
自增约束 auto_increment（给定属性该约束，不给该属性赋值，该属性会自动增加；似java中的i++）
create table user3(
id int primary key auto_increment,
name varchar(20)
);
mysql> create table user3(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.33 sec)
mysql> desc user3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into user3(name) values('张三');
Query OK, 1 row affected (0.00 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.00 sec)
mysql> insert into user3(name) values('李四');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user3;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 张三 |
| 3 | 李四 |
+----+--------+
3 rows in set (0.00 sec)
建表后添加与删除（alter：修改表结构；add：添加；dorp：删除；modify：修改）
create table user4(
id int,
name varchar(20)
);
mysql> create table user4(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> alter table user4 add primary key(int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)' at line 1
mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(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.14 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)
mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.32 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)
唯一约束 unique（给定一个属性该约束其值不能重复；给定或多个个属性该约束，两属性中一个值不重复即可）
create table user5(
id int,
name varchar(20),
unique(name)
);
mysql> create table user5(
-> id int,
-> name varchar(20),
-> unique(name)
-> );
Query OK, 0 rows affected (0.01 sec)
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 values(1,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user5;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
+------+--------+
1 row in set (0.00 sec)
mysql> insert into user5 values(1,'张三');
ERROR 1062 (23000): Duplicate entry '张三' for key 'name'
mysql> insert into user5 values(1,'李四');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user5;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 1 | 李四 |
+------+--------+
2 rows in set (0.00 sec)
mysql> alter table user5 drop index name;
Query OK, 0 rows affected (0.72 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 | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table user5 modify name varchar(20) unique;
Query OK, 0 rows affected (0.01 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> create table user6(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.00 sec)
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 add unique(name);
Query OK, 0 rows affected (0.13 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)
mysql> create table user7(
-> id int,
-> name varchar(20) unique
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| 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> create table user8(
-> id int,
-> name varchar(20),
-> unique(id,name)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into user8 values(1,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user8 values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'id'
mysql> insert into user8 values(2,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user8 values(1,'李四');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user8;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 1 | 李四 |
| 2 | 张三 |
+------+--------+
3 rows in set (0.00 sec)
非空约束 not_null（给定属性被该约束，该属性不能为空）
create table user9(
id int,
name varchar(20),
age int not null
);
mysql> create table user9(
-> id int,
-> name varchar(20),
-> age int not null
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into user9 values(1,'张三',18);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user9 values(2,'李四',19);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user9(id,name) values(3,'王五');
ERROR 1364 (HY000): Field 'age' doesn't have a default value
mysql> select * from user9;
+------+--------+-----+
| id | name | age |
+------+--------+-----+
| 1 | 张三 | 18 |
| 2 | 李四 | 19 |
+------+--------+-----+
2 rows in set (0.00 sec)
默认约束 default（给定的属性会有一个默认值）
create table user10(
id int,
name varchar(20),
age int default 18
);
mysql> create table user10(
-> id int,
-> name varchar(20),
-> age int default 18
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | 18 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into user10(id,name) values(1,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user10 values(2,'李四',20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user10;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | 张三 | 18 |
| 2 | 李四 | 20 |
+------+--------+------+
2 rows in set (0.00 sec)
外键约束 foreign key … references…(，如果给予属性该约束，副表属性继承主表属性的值，副表给予的值不能超过主表属性的值，否则报错)
mysql> create table class(
-> id int primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| 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 class values(1,'一班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into class values(2,'二班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into class values(3,'三班');
Query OK, 1 row affected (0.00 sec)
mysql> insert into class values(4,'四班');
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 4 | 四班 |
+----+--------+
4 rows in set (0.00 sec)
mysql> create table student(
-> id int,
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
mysql> create table student(
-> id int primary key,
-> name varchar(20),
-> class_id int,
-> foreign key(class_id) references class(id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into student values(1001,'张三',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1002,'李四',2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1003,'王五',3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1004,'赵六',4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1005,'刘七',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
mysql> select * from student;
+------+--------+----------+
| id | name | class_id |
+------+--------+----------+
| 1001 | 张三 | 1 |
| 1002 | 李四 | 2 |
| 1003 | 王五 | 3 |
| 1004 | 赵六 | 4 |
+------+--------+----------+
4 rows in set (0.00 sec)
mysql> select * from class;
+----+--------+
| id | name |
+----+--------+
| 1 | 一班 |
| 2 | 二班 |
| 3 | 三班 |
| 4 | 四班 |
+----+--------+
4 rows in set (0.00 sec)
数据库的三大范式
第一范式（1NF，数据表中的所有字段都是不可分割的原子值）
- 错误案例（这里地区可再分，国家，省份，市，这里一律用一个字段带过）
mysql> create table student2(
-> id int primary key,
-> name varchar(20),
-> address varchar(30)
-> );
Query OK, 0 rows affected (0.13 sec)
mysql> desc student2;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| address | varchar(30) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into student2 values(1,'张三','中国湖南省长沙市');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student2 values(2,'李四','中国湖南省衡阳市');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student2 values(3,'王五','中国湖南省邵阳市');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student2;
+----+--------+--------------------------+
| id | name | address |
+----+--------+--------------------------+
| 1 | 张三 | 中国湖南省长沙市 |
| 2 | 李四 | 中国湖南省衡阳市 |
| 3 | 王五 | 中国湖南省邵阳市 |
+----+--------+--------------------------+
3 rows in set (0.00 sec)
- 正确示范
mysql> create table student3(
-> id int primary key,
-> name varchar(20),
-> country varchar(30),
-> province varchar(30),
-> city varchar(30)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc student3;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| country | varchar(30) | YES | | NULL | |
| province | varchar(30) | YES | | NULL | |
| city | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> insert into student3 values(1,'张三','中国','湖南省','长沙市');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student3 values(2,'李四','中国','湖南省','衡阳市');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student3 values(3,'王五','中国','湖南省','邵阳市');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student3;
+----+--------+---------+-----------+-----------+
| id | name | country | province | city |
+----+--------+---------+-----------+-----------+
| 1 | 张三 | 中国 | 湖南省 | 长沙市 |
| 2 | 李四 | 中国 | 湖南省 | 衡阳市 |
| 3 | 王五 | 中国 | 湖南省 | 邵阳市 |
+----+--------+---------+-----------+-----------+
3 rows in set (0.00 sec)
第二范式（2NF，需符合1NF，它的要求需除主键外的每一列都必须完全依赖于主键，如果要出现不完全依赖，则则只可能发生在联合主键的情况下。）
- 错误示例（这里除主键外，其他列只依赖于部分主键）
create table myorder(
product_id int,
customer_id int,
product_name varchar(20),
customer_id varchar(20),
primary key(product_id,customer_id)
)
- 正确示例
create table myorder(
order_id primary key,
product_id int,
customer_id int
);
----------------------------------------------------------------------------------------
crete table product(
id int primary key,
name varchar(20)
)
----------------------------------------------------------------------------------------
crete table product(
id int primary key,
name varchar(20)
)
第三范式（3NF，需满足2NF，除开主键列的其他列不能有传递依赖关系。）
- 错误示范（product_phone与customer_id有传递关系）
create table myorder(
order_id primary key,
product_id int,
customer_id int,
product_phone varcahr(20)
);
- 正确示范
create table myorder(
order_id primary key,
product_id int,
customer_id int
);
-----------------------------------------------------------------------------------------
crete table product(
id int primary key,
name varchar(20),
phone varchar(20)
)
三、查询练习
准备数据
学生表（Student，学号，姓名，性别，出生年月日，所在班级）
create table student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex char(1) not null,
sbirthday datetime,
class varchar(20)
);
-----------------------------------------------------------------------------------------
insert into student values('101','曾华','男','1977-09-01','95033');
insert into student values('102','匡明','男','1975-10-02','95031');
insert into student values('103','王丽','女','1976-01-23','95033');
insert into student values('104','李军','男','1976-02-20','95033');
insert into student values('105','王芳','女','1975-02-10','95031');
insert into student values('106','陆君','男','1974-06-03','95031');
insert into student values('107','王尼玛','男','1976-02-20','95033');
insert into student values('108','张全蛋','男','1975-02-10','95031');
insert into student values('109','赵铁柱','男','1974-06-03','95031');
-----------------------------------------------------------------------------------------
mysql> select * from student;
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
教师表（Teacher：教师编号，教师名字，教师性别，出生年月日，职称，所在部门）
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex char(1) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
-----------------------------------------------------------------------------------------
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
-----------------------------------------------------------------------------------------
mysql> select * from teacher;
+-----+--------+------+---------------------+-----------+-----------------+
| tno | tname | tsex | tbirthday | prof | depart |
+-----+--------+------+---------------------+-----------+-----------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+--------+------+---------------------+-----------+-----------------+
4 rows in set (0.00 sec)
课程表（Course：课程号，课程名称，教师编号）
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)
);
-----------------------------------------------------------------------------------------
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
-----------------------------------------------------------------------------------------
mysql> select * from course;
+-------+-----------------+-----+
| cno | cname | tno |
+-------+-----------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)
成绩表（Score：学号，课程号，成绩）
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 score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');
-----------------------------------------------------------------------------------------
mysql> select * from score;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
9 rows in set (0.00 sec)
基础查询练习
查询student表的所有记录
mysql> select * from student;
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
查询student表中的所有snme、ssex和class列
mysql> select sname,ssex,class from student;
+-----------+------+-------+
| sname | ssex | class |
+-----------+------+-------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆君 | 男 | 95031 |
| 王尼玛 | 男 | 95033 |
| 张全蛋 | 男 | 95031 |
| 赵铁柱 | 男 | 95031 |
+-----------+------+-------+
9 rows in set (0.00 sec)
查询教师所有的单位即不重复的depart列(distinct：查重)
mysql> select distinct(depart) from teacher;
+-----------------+
| depart |
+-----------------+
| 计算机系 |
| 电子工程系 |
+-----------------+
2 rows in set (0.00 sec)
查询score表中成绩在60-80之间的所有记录(between：区间查询这里是闭区间)
mysql> select * from score where degree between 60 and 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
4 rows in set (0.00 sec)
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.39 sec)
查询score表中成绩为85,86或88的记录(in：一列当中“或”的查询)
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)
查询student表中“95031”班或性别为女的同学记录(or：不同列”或“的查询)
mysql> select * from student where class='95031' or ssex='女';
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
6 rows in set (0.00 sec)
以class降序查询student表的所有记录
mysql> select * from student order by class desc;
+-----+-----------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+-----------+------+---------------------+-------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆君 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+------+---------------------+-------+
9 rows in set (0.00 sec)
以cno升序、degree降序查询score表的所有记录(asc：升序，desc：降序)
mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
+-----+-------+--------+
9 rows in set (0.00 sec)
查询”95031“班所有学生人数(count：统计)
mysql> select count(class) from student where class='95031';
+--------------+
| count(class) |
+--------------+
| 5 |
+--------------+
1 row in set (0.00 sec)
mysql> select count(*) from student where class='95031';
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
查询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 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)
查询score表中至少有两名学生选修的并以3开头的课程的平均成绩
mysql> select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like '3%';
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
+-------+-------------+
2 rows in set (0.00 sec)
查询分数大于70，小于90的sno列
mysql> select sno from score where degree between 70 and 90;
+-----+
| sno |
+-----+
| 103 |
| 103 |
| 105 |
| 105 |
| 105 |
| 109 |
| 109 |
+-----+
7 rows in set (0.00 sec)
mysql> select sno from score where degree>=70 and degree<=90;
+-----+
| sno |
+-----+
| 103 |
| 103 |
| 105 |
| 105 |
| 105 |
| 109 |
| 109 |
+-----+
7 rows in set (0.00 sec)
多表查询
查询所有学生的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)
查询所有学生的sno、cname和degree列
mysql> select sno,cname,degree from course,score where course.cno=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)
查询所有学生的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)
子查询
查询“95031”班学生每门成绩的平均成绩
mysql> select cno,avg(degree) from score
-> where sno in (select sno from student)
-> 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)
查询选修“3-105”课程的成绩大于“109”号同学“3-105”成绩的所有同学的记录
查询成绩高于学号为“109”、课程为“3-105“的成绩的所有记录
扫一扫
17万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
