原书P58页
先说好,一定要以管理员身份运行!!!!!!
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/b92b866ca61db29b9a9074ca1136fdd9.png)
例1 院系表(带列级约束)
没注释的是源代码
mysql> use db_2;
-- Database changed
mysql> create table Department( Dno varchar(3) PRIMARY KEY, Dname varchar(30) UNIQUE, Office varchar(4) );
-- Query OK, 0 rows affected (0.08 sec)
mysql> insert into Department values('D1','信工学院','C101'),('D2','地理学院','S201'),('D3','工学院','F301' ),('D4','医 学院','B206' );
-- Query OK, 4 rows affected (0.02 sec)
-- Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Department;
/*
+-----+----------+--------+
| Dno | Dname | Office |
+-----+----------+--------+
| D1 | 信工学院 | C101 |
| D2 | 地理学院 | S201 |
| D3 | 工学院 | F301 |
| D4 | 医学院 | B206 |
+-----+----------+--------+
4 rows in set (0.01 sec) */
例2 创建带列级约束的学生表
(不好意思,本人已经挂在找语法错误的路上了,等我明白怎么改才正确再来更新此篇)
谢谢马老师的指正,原来课本上有些SQL语句是根据Oracle优化的,不适合简单的MySQL。
然后以下是改好的代码:
create table Student(Sno varchar(3) PRIMARY KEY, Sname VARCHAR(10) NOT NULL, Sex Char(2) DEFAULT '男' CHECK(Sex IN ('男','女')), Birth DATE, Dno varchar(3) REFERENCES Department(Dno) );
-- 非常长,可以分行
mysql> insert into Student values('S1','赵刚','男','1995-09-02','D1'),
-> ('S2','周丽','女','1996-01-06','D3'),
-> ('S3','李强','男','1996-05-02','D3'),
-> ('S4','刘珊','女','1997-08-08','D1'),
-> ('S5','齐超','男','1997-06-08','D2'),
-> ('S6','宋佳','女','1997-08-02','D4');
--Query OK, 6 rows affected (0.01 sec)
--Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Student;
/*
+-----+-------+------+------------+------+
| Sno | Sname | Sex | Birth | Dno |
+-----+-------+------+------------+------+
| S1 | 赵刚 | 男 | 1995-09-02 | D1 |
| S2 | 周丽 | 女 | 1996-01-06 | D3 |
| S3 | 李强 | 男 | 1996-05-02 | D3 |
| S4 | 刘珊 | 女 | 1997-08-08 | D1 |
| S5 | 齐超 | 男 | 1997-06-08 | D2 |
| S6 | 宋佳 | 女 | 1997-08-02 | D4 |
+-----+-------+------+------------+------+
*/
-- 6 rows in set (0.00 sec)
例3 课程关系(Course)
create table Course(Cno varchar(3) PRIMARY KEY, Cname varchar(20) UNIQUE, Cpno varchar(3), Credit INT);
insert into Course values('C1','数据库','C3',4),('C2','计算机基础',NULL,3),('C3', 'C_Design','C2',2),('C4','网络数据库','C1',4);
选课关系SC
CREATE TABLE SC(
Sno varchar(3) REFERENCES Student(Sno),
Cno varchar(3) REFERENCES Course(Cno),
Score INT,
CHECK ( Score BETWEEN 0 AND 100),
PRIMARY KEY(Sno,Cno)
);
insert into SC values( 'S1','C1',90 ),
( 'S2','C2',82 ),
( 'S3','C1',85 ),
( 'S4','C1',46 ),
( 'S5','C2',78 ),
( 'S1','C2',98 ),
( 'S3','C2',67 ),
( 'S6','C2',87 ),
( 'S1','C3',98 ),
( 'S5','C3',77 ),
( 'S1','C4',52 ),
( 'S6','C4',79 ),
( 'S4','C2',69 ),
( 'S6','C3',NULL)
;
SELECT * FROM SC;
/*
+-----+-----+-------+
| Sno | Cno | Score |
+-----+-----+-------+
| S1 | C1 | 90 |
| S1 | C2 | 98 |
| S1 | C3 | 98 |
| S1 | C4 | 52 |
| S2 | C2 | 82 |
| S3 | C1 | 85 |
| S3 | C2 | 67 |
| S4 | C1 | 46 |
| S4 | C2 | 69 |
| S5 | C2 | 78 |
| S5 | C3 | 77 |
| S6 | C2 | 87 |
| S6 | C3 | NULL |
| S6 | C4 | 79 |
+-----+-----+-------+
14 rows in set (0.00 sec)
*/
创建表Goodscore,包含SC表中大于90分的学生信息
课本P62
CREATE TABLE Goodscore AS SELECT * FROM SC WHERE Score>=90;
SELECT * FROM Goodscore;
/*
+-----+-----+-------+
| Sno | Cno | Score |
+-----+-----+-------+
| S1 | C1 | 90 |
| S1 | C2 | 98 |
| S1 | C3 | 98 |
+-----+-----+-------+
*/
复制表SC
课本P62
mysql> create TABLE SCnew AS SELECT * FROM SC;
Query OK, 14 rows affected (0.07 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM SCnew;
+-----+-----+-------+
| Sno | Cno | Score |
+-----+-----+-------+
| S1 | C1 | 90 |
| S1 | C2 | 98 |
| S1 | C3 | 98 |
| S1 | C4 | 52 |
| S2 | C2 | 82 |
| S3 | C1 | 85 |
| S3 | C2 | 67 |
| S4 | C1 | 46 |
| S4 | C2 | 69 |
| S5 | C2 | 78 |
| S5 | C3 | 77 |
| S6 | C2 | 87 |
| S6 | C3 | NULL |
| S6 | C4 | 79 |
+-----+-----+-------+
14 rows in set (0.00 sec)
不知不觉弄了这么些表了
mysql> show tables;
+----------------+
| Tables_in_db_2 |
+----------------+
| course |
| department |
| goodscore |
| sc |
| scnew |
| student |
+----------------+
6 rows in set (0.01 sec)
例7 为Department表增加一个学院成立时间字段
ALTER TABLE Department ADD Dtime DATE;