关于mysql:MySQL数据库基础学习笔记

45次阅读

共计 18018 个字符，预计需要花费 46 分钟才能阅读完成。

1、终端操作 MySQL 数据库

1.1 如何登陆数据库服务器：

C:Usersusername>mysql -uroot -proot

1.2 如何查询数据库服务器中的数据库


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

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

mysql> use test1
Database changed

1.4SQL 语句中的查问

mysql> select * from person;
+-----------+------+
| name      | sex  |
+-----------+------+
| 张三      | 男   |
| 李四      | 女   |
| 王麻子    | 男   |
+-----------+------+
3 rows in set (0.00 sec)

mysql> select * from person where name="张三";
+--------+------+
| name   | sex  |
+--------+------+
| 张三   | 男   |
+--------+------+
1 row in set (0.00 sec)

1.5 如何退出数据库服务器

mysql> exit;
Bye

2、如何创立数据库数据表

2.1 如何在数据库服务器中创立咱们的数据库

mysql> create database test2;
Query OK, 1 row affected (0.05 sec)

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

mysql> use test2;
Database changed

2.2 如何查看某个数据库中的所有数据表

mysql> show tables;
Empty set (0.00 sec)

2.3 如何创立一个数据表

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

创立了一个宠物数据表

查看数据表是否创立胜利

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

2.4 查看创立好的数据表的构造

mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

3、insert 减少数据记录

3.1 查看数据表中的记录

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

3.2 如何往数据表中增加数据记录

mysql> insert into pet
    -> values('Tom','LiMing','hamster','f','2020-11-11',null);
Query OK, 1 row affected (0.01 sec)

再一次查问

mysql> select * from pet;
+------+--------+---------+------+------------+-------+
| name | owner  | species | sex  | birth      | death |
+------+--------+---------+------+------------+-------+
| Tom  | LiMing | hamster | f    | 2020-11-11 | NULL  |
+------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> insert into pet
    -> values('旺财','周星星','狗','公','2020-11-11',null);
Query OK, 1 row affected (0.01 sec)

查看

mysql> select * from pet;
+--------+-----------+---------+------+------------+-------+
| name   | owner     | species | sex  | birth      | death |
+--------+-----------+---------+------+------------+-------+
| Tom    | LiMing    | hamster | f    | 2020-11-11 | NULL  |
| 旺财   | 周星星    | 狗      | 公   | 2020-11-11 | NULL  |
+--------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)

3.3MySQL 罕用数据类型

MySQL 反对多种类型，大抵能够分为三类：数值、日期 / 工夫和字符串 (字符) 类型。

3.3.1 数值类型

如果数值超出数值类型的范畴就会报错：

mysql>  create table testType(->  number tinyint);
Query OK, 0 rows affected (0.10 sec)

mysql> describe testType;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| number | tinyint | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into testType
    -> values(128);
ERROR 1264 (22003): Out of range value for column 'number' at row 1

3.3.2 日期和工夫类型

3.3.3 字符串类型

3.3.4 数据类型如何抉择

日期：依照日期格局

数值和字符串：依照大小

4、数据记录增删查改操作

4.1 插入：

mysql> insert into pet values('Buffy','Job','cat','m','2020-11-10',null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into pet values('Slim','邓紫棋','dog','f','2019-11-11',null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into pet
    -> values('Fang','成龙','cat','m','2018-11-11','2020-10-10');
Query OK, 1 row affected (0.01 sec)

mysql> select * from pet;
+--------+-----------+---------+------+------------+------------+
| name   | owner     | species | sex  | birth      | death      |
+--------+-----------+---------+------+------------+------------+
| Tom    | LiMing    | hamster | f    | 2020-11-11 | NULL       |
| 旺财   | 周星星    | 狗      | 公   | 2020-11-11 | NULL       |
| Buffy  | Job       | cat     | m    | 2020-11-10 | NULL       |
| Slim   | 邓紫棋    | dog     | f    | 2019-11-11 | NULL       |
| Fang   | 成龙      | cat     | m    | 2018-11-11 | 2020-10-10 |
+--------+-----------+---------+------+------------+------------+
5 rows in set (0.00 sec)

4.2 如何删除数据

mysql> delete from pet where name='Tom';
Query OK, 1 row affected (0.01 sec)

再次查看

mysql> select * from pet;
+--------+-----------+---------+------+------------+------------+
| name   | owner     | species | sex  | birth      | death      |
+--------+-----------+---------+------+------------+------------+
| 旺财   | 周星星    | 狗      | 公   | 2020-11-11 | NULL       |
| Buffy  | Job       | cat     | m    | 2020-11-10 | NULL       |
| Slim   | 邓紫棋    | dog     | f    | 2019-11-11 | NULL       |
| Fang   | 成龙      | cat     | m    | 2018-11-11 | 2020-10-10 |
+--------+-----------+---------+------+------------+------------+
4 rows in set (0.00 sec)

4.3 如何批改数据

mysql> update pet set name='旺财财' where owner='周星星';
Query OK, 1 row affected (0.01 sec)

再次查看：

mysql> select * from pet;
+-----------+-----------+---------+------+------------+------------+
| name      | owner     | species | sex  | birth      | death      |
+-----------+-----------+---------+------+------------+------------+
| 旺财财    | 周星星    | 狗      | 公   | 2020-11-11 | NULL       |
| Buffy     | Job       | cat     | m    | 2020-11-10 | NULL       |
| Slim      | 邓紫棋    | dog     | f    | 2019-11-11 | NULL       |
| Fang      | 成龙      | cat     | m    | 2018-11-11 | 2020-10-10 |
+-----------+-----------+---------+------+------------+------------+
4 rows in set (0.00 sec)

4.4 总结：数据记录常见操作

减少：insert

删除：delete

批改：update

查问：select

5、MySQL 建表束缚

5.1 主键束缚 primary key

它可能惟一确定一张表中的一条记录，也就是咱们通过给某个字段增加束缚，就能够使得该字段 不反复且不为空。

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

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

再次插入 id= 1 的记录

mysql> insert into user values(1,'李四');
ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'

id 为空的时候

mysql>  insert into user values(null,'李四');
ERROR 1048 (23000): Column 'id' cannot be null

5.2 联结主键

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

插入记录

mysql> insert into user2 values(1,'zhangsan','123');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user2 values(1,'zhangsan','234');
ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'user2.PRIMARY'

mysql>  insert into user2 values(2,'zhangsan','234');
Query OK, 1 row affected (0.01 sec)

联结主键只有主键字段之间加起来不反复就能够，但任何一个主键字段都不能够为空。

5.3 自增束缚 auto_increment

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

插入数据

mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

查看
mysql> select * from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

自增束缚和主键束缚搭配在一起应用时，它会主动给咱们管去控值，例如：主动减少id。

5.4 如果创立表的时候遗记创立主键束缚怎么办？

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

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

批改表构造，减少主键束缚 alter … add

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

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

如何删除呢？alter …drop

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

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

应用 modify，批改字段，增加束缚

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

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

5.5 惟一束缚

束缚润饰的字段的值不能够反复。

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

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

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

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

mysql> insert into user5 values(1,'zhangsan');
Query OK, 1 row affected (0.03 sec)

mysql> insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'

name=lisi 不能反复

mysql> insert into user5 values(1,'lisi');
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.07 sec)

能够对 id 和 name 都增加惟一束缚，这时候只有 id+name 不反复就能够增加记录。

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

mysql> insert into user7 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user7 values(2,'zhangsan');
Query OK, 1 row affected (0.04 sec)

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

如何删除惟一束缚

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

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

通过 modify 增加惟一束缚

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

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

总结：

  • 建表的时候增加束缚
  • 能够用 alter… add 增加
  • 能够用 alter… modify 增加束缚
  • 删除用 alter…drop

5.6 非空束缚

润饰的字段不能为空 null

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

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

测试：如果 name 为空

mysql> insert into user8 (id)values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

正确增加不为空时

mysql> insert into user8 values(1,'lisi');
Query OK, 1 row affected (0.01 sec)

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

如果只传 name 是能够的

mysql> insert into user8 (name)values('zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> select *from user8;
+------+----------+
| id   | name     |
+------+----------+
|    1 | lisi     |
| NULL | zhangsan |
+------+----------+
2 rows in set (0.00 sec)

同样能够用 alter ... add，alter ... modify 来增加惟一束缚。

5.7 默认束缚 default

就是当咱们插入字段值的时候，如果没有传值，就会应用默认值。

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

查看表构造

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

插入值时直插入 id 和 name 那么 age 应用默认值 10，如果 age 也插入值那么就应用插入的值不应用默认值。

mysql> insert into user9(id,name) values (1,'lisi');
Query OK, 1 row affected (0.01 sec)

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

5.8 外键束缚

波及到两个表：父表，子表。

班级表：

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

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

插入值

mysql> insert into classes values(1,'yiban');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into classes values(2,'erban');
Query OK, 1 row affected (0.03 sec)

mysql> insert into classes values(3,'sanban');
Query OK, 1 row affected (0.01 sec)

mysql> select *from classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | yiban  |
|  2 | erban  |
|  3 | sanban |
+----+--------+
3 rows in set (0.00 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.06 sec)
mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int         | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入值

mysql> insert into students values(1001,'zhangsan',1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into students values(1002,'zhangsan',2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into students values(1003,'zhangsan',3);
Query OK, 1 row affected (0.04 sec)

mysql> select *from students;
+------+----------+----------+
| id   | name     | class_id |
+------+----------+----------+
| 1001 | zhangsan |        1 |
| 1002 | zhangsan |        2 |
| 1003 | zhangsan |        3 |
+------+----------+----------+
3 rows in set (0.00 sec)

父表 classes 中没有的数据值，是不能够在子表 students 中应用的。

父表中的记录被子表援用，是不能够被删除的。

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

数据库三大范式

  1. 第一范式 1NF

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

mysql> create table student(
    -> id int primary key,
    -> name varchar(20),
    -> address varchar(30)
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> insert into student values(1,'zhangsan','甘肃会宁');
Query OK, 1 row affected (0.02 sec)

mysql> insert into student values(2,'lisi','甘肃会宁');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(3,'laowang','甘肃会宁');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+----------+--------------+
| id | name     | address      |
+----+----------+--------------+
|  1 | zhangsan | 甘肃会宁     |
|  2 | lisi     | 甘肃会宁     |
|  3 | laowang  | 甘肃会宁     |
+----+----------+--------------+
3 rows in set (0.00 sec)

address 字段值还能够持续拆分的就不满足第一范式。

create table student2(
    id int primary key,
    name varchar(20),
    country varchar(30),
    privence varchar(30),
    city varchar(30),
    details varchar(30)
 );

insert into student2 values(1,’ 张三 ’,’ 中国 ’,’ 甘肃 ’,’ 兰州 ’,’ 安宁区安定街道 30 号 ’);
insert into student2 values(2,’ 李四 ’,’ 中国 ’,’ 甘肃 ’,’ 兰州 ’,’ 安宁区安定街道 34 号 ’);
insert into student2 values(3,’ 王五 ’,’ 中国 ’,’ 甘肃 ’,’ 兰州 ’,’ 安宁区安定街道 80 号 ’);

mysql> select * from student2;
+----+--------+---------+----------+--------+----------------------------+
| id | name   | country | privence | city   | details                    |
+----+--------+---------+----------+--------+----------------------------+
|  1 | 张三   | 中国    | 甘肃     | 兰州   | 安宁区安定街道 30 号         |
|  2 | 李四   | 中国    | 甘肃     | 兰州   | 安宁区安定街道 34 号         |
|  3 | 王五   | 中国    | 甘肃     | 兰州   | 安宁区安定街道 80 号         |
+----+--------+---------+----------+--------+----------------------------+
3 rows in set (0.00 sec)

范式：设计的越具体，对于某些实际操作可能更好，但不肯定都有益处。个别要结合实际状况设计。

  1. 第二范式 2NF

第二范式必须是满足第一范式的前提下，要求，除主键外的每一列都必须齐全依赖于主键。
如果呈现不齐全依赖，只可能产生在联结主键的状况下。

create table myolder(
    product_id int,
    customer_id int,
    product_name varchar(30),
    customer_name varchar(30),
    primary key(product_id,customer_id)
);

** 问题：
除主键外的其余列，只依赖于主键的局部字段。
不满足第二范式
解决办法是拆表。**

create table myolder(
    older_id int primary key,
    product_id int,
    customer_id int
);

create table product(
    id int primary key,
    name varchar(20)
);

create table customer(
    id int primary key,
    name varchar(20)
);

分成三个表之后就满足第二范式的设计！！！

  1. 第三范式 3NF

必须先满足第二范式，除开主键列的其余列之间不能有传递依赖关系。
例如：

create table myolder(
    older_id int primary key,
    product_id int,
    customer_id int,
    customer_phone varchar(15)
);

在这个表中，咱们能够看到 customer_phone 和customer_id之间存在依赖关系，这样就不满足第三范式的要求；解决办法是：咱们把 customer_phone 放到 customer 表中。如下：

create table customer(
    id int primary key,
    name varchar(20),
        phone varchar(15)
);

查问练习题：

1. 数据筹备：

学生表 student

学号：姓名：性别：出生日期：所在班级；

create table student (sno varchar(20) primary key,
    sname varchar(20) not null,
    ssex varchar(10) not null,
    sbirthday datetime,
    class varchar(10)
);

老师表 teacher

老师编号，老师名字，老师性别，出生日期，职称，所在部门。

create table teacher(tno varchar(20) primary key,
    tname varchar(20) not null,
    tsex varchar(10) not null,
    tbirthday datetime,
    prof varchar(20),
    depart varchar(20) not null
);

课程表 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)
);

成绩表 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 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','王尼玛','男','1974-06-03','95031');
insert into student values('108','张全蛋','男','1974-06-03','95031');
insert into student values('109','赵铁柱','男','1974-06-03','95031');

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','助教','电子工程系');

insert into course values('3-105','计算机导论', '825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');

insert into score values('103','3-105','92');
insert into score values('103','3-245','86');
insert into score values('103','6-166','85');
insert into score values('105','3-105','88');
insert into score values('105','3-245','75');
insert into score values('105','6-166','79');
insert into score values('109','3-105','76');
insert into score values('109','3-245','68');
insert into score values('109','6-166','81');

正文完
mysql
发表至： mysql
2020-11-13
 0
关于日期及时间字段的查询
关于mysql:MySQL选错索引导致的线上慢查询事故
mysql数据库操作
关于mysql:新特性解读-MySQL-80-窗口函数一次疑问解答