MySQL命令行实操一

先说好，一定要以管理员身份运行！！！！！！

例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;