select version();
show character set;
show variables like 'character%';
show collation;
create database Teach;
show databases;
show create database Teach;
show engines;
--[例3-4] 用SQL命令建立一个学生表S
DROP TABLE S
CREATE TABLE S
( SNo CHAR(6),
SN CHAR(8),
Sex CHAR(2) DEFAULT '男',
Age INT,
Dept VARCHAR(20))
select * from s;
--[例3-5] 建立一个S表，对SNo字段进行NOT NULL约束
DROP TABLE S;
CREATE TABLE S
( SNo CHAR(6) NOT NULL,
SN VARCHAR(8),
Sex CHAR(2),
Age INT,
Dept VARCHAR(20));
--[例3-6] 建立一个S表，定义SN为惟一键。
DROP TABLE S;
CREATE TABLE S
( SNo CHAR(6) PRIMARY KEY,
SN CHAR(8) UNIQUE,
Sex CHAR(2),
Age INT,
Dept VARCHAR(20));
select * from s;
--[例3-7] 建立一个S表，定义SN+SEX为惟一键，此约束为表约束。
DROP TABLE S;
CREATE TABLE S
( SNo CHAR(6) PRIMARY KEY,
SN CHAR(8) UNIQUE,
Sex CHAR(2),
Age INT,
Dept VARCHAR(20),
UNIQUE s_uniq(SN, Sex));
--[例3-8] 建立一个S表，定义SNo为S的主键，建立另外一个数据表C，定义CNo为C的主键
DROP TABLE S;
CREATE TABLE S
( SNo CHAR(6) PRIMARY KEY,
SN CHAR(8),
Sex CHAR(2),
Age INT,
Dept VARCHAR(20));
DROP TABLE C;
CREATE TABLE C
( CNo CHAR(5) PRIMARY KEY,
CN CHAR(20),
CT INT);
--[例3-9] 建立一个SC表，定义SNo+CNo为SC的主键
CREATE TABLE SC
( SNo CHAR(5) NOT NULL,
CNo CHAR(5) NOT NULL,
Score NUMERIC(4,1),
PRIMARY KEY SC_Prim(SNo,CNo));
select * from sc;
--[例3-10] 建立一个SC表，定义SNo,CNo为SC的外部键。
DROP TABLE SC;
CREATE TABLE SC
( SNo CHAR(6) NOT NULL,
CNo CHAR(5) NOT NULL,
Score NUMERIC(4,1),
PRIMARY KEY S_C_Prim(SNo,CNo),
FOREIGN KEY S_Fore(SNo) REFERENCES S(SNo),
FOREIGN KEY C_Fore(CNo) REFERENCES C(CNo));
select * from sc;
--[例3-11] 建立一个SC表，定义Score的取值范围为0～100之间。
DROP TABLE SC;
CREATE TABLE SC
( SNo CHAR(5),
CNo CHAR(5),
Score NUMERIC(4,1) CHECK(Score>=0 AND Score <=100));
select * from sc;
--[例3-12] 建立包含完整性定义的学生表。
DROP TABLE S;
CREATE TABLE S
( SNo CHAR(6) PRIMARY KEY,
SN CHAR(8) NOT NULL,
Sex CHAR(2) DEFAULT '男',
Age INT NOT NULL CHECK (Age BETWEEN 15 AND 50),
Dept CHAR(10) NOT NULL);
--[例3-12-补1] 自增长
-- 自增长:一个表中只能有一个自增长字段，一定要配合主键使用
-- 自增长字段一定是主键，但主键不一定是自增长的
CREATE TABLE user3(
id SMALLINT KEY AUTO_INCREMENT,
username VARCHAR(20)
)AUTO_INCREMENT=100;
-- 最后加上AUTO_INCREMENT=100是指自增长的超始值，如果不加就从1开始
INSERT user3 VALUES(1, 'king');
select * from user3;
-- 如果不指定id,则在已有的id最大值+1
INSERT user3(username) VALUES('queen');
-- id字段给NULL或DEFAULT, 也是自增长
INSERT user3 VALUES(NULL, 'queen2');
INSERT user3 VALUES(DEFAULT, 'queen2');
-- 如果在定义表的时候没有设置自增长起始值，后来也可以修改；
-- 修改之后添加的数据从修改的起始值开始
ALTER TABLE user3 AUTO_INCREMENT =500;
INSERT user3 VALUES(DEFAULT, 'queen5');
--[例3-12-补2] 约束的删除
CREATE TABLE tb_class
(
class_id INT AUTO_INCREMENT PRIMARY KEY,
class_name VARCHAR(30) NOT NULL
);
DROP TABLE tb_student;
CREATE TABLE tb_student
( stu_id INT,
name VARCHAR(30) ,
class_id INT NOT NULL,
PRIMARY KEY(stu_id), -- 主键约束
FOREIGN KEY fk_class_id (class_id) REFERENCES tb_class(class_id), -- 外键约束
UNIQUE uniq_name (name) -- 唯一性约束
);
DESC tb_student;
select * from tb_student;
-- 删除主键约束
ALTER TABLE tb_student DROP PRIMARY KEY;
-- 删除外键约束
ALTER TABLE tb_student DROP FOREIGN KEY fk_class_id;
-- 删除唯一性约束
ALTER TABLE tb_student DROP INDEX uniq_name;
DESC tb_student;
-- 添加外键约束
ALTER TABLE tb_student ADD CONSTRAINT FOREIGN KEY fk_class_id (class_id) REFERENCES tb_class(class_id);
--[例3-13] 在S表中增加一个班号列。
SELECT * FROM S
ALTER TABLE S
ADD
Class_No CHAR(6)
--使用此方式增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。
--[例3-15] 把S表中的SN列加宽到10个字符。
ALTER TABLE S
MODIFY COLUMN
SN CHAR(6);
DESCRIBE s;
--[例3-16] 删除S表中的一列“Class_No”
ALTER TABLE S
DROP COLUMN Class_No
SELECT * FROM S
--[例] 建立T表
CREATE TABLE T
( TNo CHAR(6) PRIMARY KEY,
TN CHAR(8) NOT NULL,
SEX CHAR(4),
Prof CHAR(8),
Age INT ,
SAL INT,
COMM INT,
Dept CHAR(20) );
--[例] 建立TC表
DROP TABLE TC
CREATE TABLE TC
( TNo CHAR(6),
CNo CHAR(5)
)
select * from TC
DROP TABLE TC;
CREATE TABLE TC
( TNo CHAR(6),-- CONSTRAINT T_Prim PRIMARY KEY,
CNO CHAR(6)
);
SELECT * FROM TC;
SELECT * FROM s;
/*向S表插入数据*/
delete FROM S
INSERT INTO S (SNo, SN, Age, Sex, Dept) VALUES ('S1', '赵亦', 17, '男', '计算机');
INSERT INTO S (SNo, SN, Age, Sex, Dept) VALUES ('S2', '钱尔', 18, '男', '信息');
INSERT INTO S (SNo, SN, Age, Sex, Dept) VALUES ('S3', '孙珊', 20, '女', '信息');
INSERT INTO S (SNo, SN, Age, Sex, Dept) VALUES ('S4', '李思', 21, '女', '自动化');
INSERT INTO S (SNo, SN, Age, Sex, Dept) VALUES ('S5', '周武', 19, '女', '计算机');
INSERT INTO S (SNo, SN, Age, Sex, Dept) VALUES ('S6', '吴丽', 20, '女', '自动化');
/*向C表插入数据*/
delete FROM C
INSERT INTO C (CNo, CN, CT) VALUES ('C1', '程序设计', 60);
INSERT INTO C (CNo, CN, CT) VALUES ('C2', '微机原理', 80);
INSERT INTO C (CNo, CN, CT) VALUES ('C3', '数字逻辑', 60);
INSERT INTO C (CNo, CN, CT) VALUES ('C4', '数据结构', 80);
INSERT INTO C (CNo, CN, CT) VALUES ('C5', '数据库', 60);
INSERT INTO C (CNo, CN, CT) VALUES ('C6', '编译原理', 60);
INSERT INTO C (CNo, CN, CT) VALUES ('C7', '操作系统', 60);
/*向T表插入数据*/
select * from T
delete FROM T
INSERT INTO T (TNO,TN,SEX,AGE,PROF,SAL,COMM,DEPT)
VALUES ('T1', '李力', '男', 47, '教授',1500,3000,'计算机');
INSERT INTO T (TNO,TN,SEX,AGE,PROF,SAL,COMM,DEPT)
VALUES ('T2', '王平', '女', 28, '讲师',800,1200,'信息');
INSERT INTO T (TNO,TN,SEX,AGE,PROF,SAL,COMM,DEPT)
VALUES ('T3', '刘伟', '男', 30, '讲师',800,1200,'计算机');
INSERT INTO T (TNO,TN,SEX,AGE,PROF,SAL,COMM,DEPT)
VALUES ('T4', '张雪', '女', 51, '教授',1600,3000,'自动化');
INSERT INTO T (TNO,TN,SEX,AGE,PROF,SAL,COMM,DEPT)
VALUES ('T5', '张兰', '女', 39, '副教授',1300,2000,'信息');
INSERT INTO T (TNO,TN,SEX,AGE,PROF,SAL,COMM,DEPT)
VALUES ('T6', '张三丰', '男', 99, '掌门',888,888,'武当');
INSERT INTO T (TNO,TN,SEX,AGE,PROF,SAL,COMM,DEPT)
VALUES ('T7', '欧阳力雄', '男', 45, '教授',2000,2000,'信息');
SELECT * FROM T;
/*向SC表插入数据*/
delete FROM SC
INSERT INTO SC (SNo, CNo, Score) VALUES ('S1', 'C1', 90);
INSERT INTO SC (SNo, CNo, Score) VALUES ('S1', 'C2', 85);
INSERT INTO SC (SNo, CNo, Score) VALUES ('S2', 'C2', 57);
INSERT INTO SC (SNo, CNo, Score) VALUES ('S2', 'C6', 80);
INSERT INTO SC (SNo, CNo) VALUES ('S2', 'C7' );
INSERT INTO SC (SNo, CNo, Score) VALUES ('S2', 'C5', 70);
INSERT INTO SC (SNo, CNo, Score) VALUES ('S3', 'C1', 75);
INSERT INTO SC (SNo, CNo, Score) VALUES ('S3', 'C4', 85);
INSERT INTO SC (SNo, CNo, Score) VALUES ('S4', 'C1', 93);
INSERT INTO SC (SNo, CNo, Score) VALUES ('S4', 'C2', 85);
INSERT INTO SC (SNo, CNo, Score) VALUES ('S4', 'C3', 83);
INSERT INTO SC (SNo, CNo, Score) VALUES ('S5', 'C2', 89);
select * from SC
/*向TC表插入数据*/
delete FROM TC
INSERT INTO TC (TNO, CNO) VALUES ('T1', 'C1');
INSERT INTO TC (TNO, CNO) VALUES ('T1', 'C4');
INSERT INTO TC (TNO, CNO) VALUES ('T2', 'C5');
INSERT INTO TC (TNO, CNO) VALUES ('T3', 'C1');
INSERT INTO TC (TNO, CNO) VALUES ('T3', 'C5');
INSERT INTO TC (TNO, CNO) VALUES ('T4', 'C2');
INSERT INTO TC (TNO, CNO) VALUES ('T4', 'C3');
INSERT INTO TC (TNO, CNO) VALUES ('T5', 'C5');
INSERT INTO TC (TNO, CNO) VALUES ('T5', 'C7');
/*选择操作*/
SELECT SNo, SN, Age FROM S
SELECT SN AS Name, SNo, Age FROM S
SELECT * FROM SC
SELECT DISTINCT SNo FROM SC
SELECT SNo FROM SC
SELECT SN Name, SNo, Age FROM S
SELECT DISTINCT SNo FR
