/*创建表*/
/* 学生表 */
CREATE TABLE student (
Sno CHAR (20) PRIMARY KEY,
sname CHAR (20) UNIQUE
);
/* 课程表 */
CREATE TABLE course (
cno VARCHAR (4) PRIMARY KEY,
cname VARCHAR (30),
cpno VARCHAR (4),
/* 先修课 */
ccredit INT,
FOREIGN KEY (cpno) REFERENCES course (cno) ON DELETE CASCADE
) TYPE = INNODB;
/* 选课表 */
CREATE TABLE sc (
sno CHAR (20),
cno VARCHAR (4),
grand INT,
PRIMARY KEY (sno, cno)
);
DROP TABLE course;
DROP TABLE sc;
/* 修改表 */
ALTER TABLE student ADD (
Ssex CHAR (2),
sage SMALLINT,
sdept CHAR (20)
);
ALTER TABLE student MODIFY COLUMN sname VARCHAR (20);
ALTER TABLE student ADD UNIQUE (sdept);
ALTER TABLE student DROP INDEX sdept;
/*删除唯一性约束*/
/**/
TRUNCATE TABLE student /*插入记录*/
INSERT INTO student (Sno, sname, Ssex, sage, sdept)
VALUES
(
'200215121',
'zhangyong',
'nan',
20,
'CS'
);
INSERT INTO student (Sno, sname, Ssex, sage, sdept)
VALUES
(
'200215122',
'lichen',
'nv',
19,
'CS'
);
INSERT INTO student (Sno, sname, Ssex, sage, sdept)
VALUES
(
'200215123',
'wangmin',
'nv',
18,
'MA'
);
INSERT INTO student (Sno, sname, Ssex, sage, sdept)
VALUES
(
'200215125',
'zhangli',
'nan',
19,
'IS'
);
/*创建视图*/
CREATE VIEW VIEW_IS_STU AS SELECT * from student WHERE sdept = 'IS'
DROP table student RESTRICT; /*若有依赖关系不删除*/
DROP table student CASCADE; /*若有依赖关系递归删除*/
/*创建索引*/
CREATE UNIQUE INDEX INDEX_STUName ON student(sname)
DROP INDEX INDEX_STUName on student; /*sql 标准为 DROP INDEX INDEX_NAME */
/*查找数据*/
SELECT sname , 2015 - sage from student;
SELECT sname , 'Year of Birth', 2015 - sage as bbb , sdept , LOWER(sdept) from student; /*其中 LOWER(str) , 将字符串转化为小写*/
SELECT DISTINCT sno FROM sc; /*DISTINCT 关键字指明去除重复!*/
SELECT * FROM student WHERE sage BETWEEN 20 AND 30 ; /*BETWEEN AND */
SELECT * FROM student WHERE sage NOT BETWEEN 20 AND 30 ;
SELECT * FROM student WHERE sdept in ('cs' , 'ma'); /*IN 查找属性值属于集合的的元组*/
SELECT * FROM student WHERE sdept not in ('cs' , 'ma');
/*字符匹配 sql 中 % 的功能是代指任意长度的字符串 , 跟* 在unix 环境下一样。 _ 代指单个字符*/
SELECT * FROM student WHERE sno LIKE '%1'; /*搜索学号中以1结尾的记录*/
SELECT * FROM student WHERE sno LIKE '%_1';
SELECT * FROM student WHERE sno NOT LIKE '%_1';
SELECT * FROM student WHERE sname LIKE 'li%';
SELECT * FROM student WHERE sname LIKE '%li';
/*涉及空查询*/
SELECT * FROM sc WHERE grand is NULL;
SELECT * FROM sc WHERE grand = NULL; /*如果用= 来跟NULL 比较, 则查不出值来 */
SELECT * FROM sc WHERE grand is not NULL;
SELECT * FROM sc WHERE grand is 92; /*IS 可以比较的 仅仅是 NULL */
SELECT * FROM student ORDER BY sdept DESC , sage ASC; /* 制定多个排序的字段, 默认 ASC 方式排序*/
/*聚集函数*/
SELECT COUNT(*) from student;
SELECT COUNT(DISTINCT sno) from sc;
SELECT AVG(grand) from sc WHERE cno = '1';
SELECT cno , COUNT(sno) FROM sc GROUP BY cno;
SELECT * FROM sc GROUP BY sno HAVING count(*) > 2 /*where 作用于表或者视图, having 作用于组(group)*/
/*概念: 自然连接 与等值连接*/
SELECT student.* , sc.* FROM student , sc WHERE student.sno = sc.sno; /*等值连接: 连接符合为=*/
SELECT s.sno , sname , ssex , sage , sdept , cno from student s , sc WHERE s.Sno = sc.sno; /*去掉等值连接中重复的属性为自然连接*/
SELECT f.cno, s.cpno from course f ,course s WHERE f.cpno = s.cno; /*自身连接自身, 给表取两个名字, 视作两个表*/
/*概念: 外连接: 条件连接 只有在符合给出条件的情况下才, 显示出来。
如: WHERE s.Sno = sc.sno 拿s 表的 sno去sc 中sno列找, 如果有相等的则显示,
若没有则舍弃s表中该条记录。。。。 外连接则是保留此条记录*/
SELECT student.Sno, sname , ssex ,sdept , cno FROM student LEFT JOIN sc ON (student.sno = sc.sno); /*注意左连接的语法!!*/
/*嵌套查询, 内层查询中不能用 order by , ORDER BY 只可用在最终结果*/
SELECT * FROM student WHERE sno in ( SELECT sno from sc WHERE grand > 90);
UPDATE student SET sage = 99 WHERE Sno = '200215121';
/* 学生表 */
CREATE TABLE student (
Sno CHAR (20) PRIMARY KEY,
sname CHAR (20) UNIQUE
);
/* 课程表 */
CREATE TABLE course (
cno VARCHAR (4) PRIMARY KEY,
cname VARCHAR (30),
cpno VARCHAR (4),
/* 先修课 */
ccredit INT,
FOREIGN KEY (cpno) REFERENCES course (cno) ON DELETE CASCADE
) TYPE = INNODB;
/* 选课表 */
CREATE TABLE sc (
sno CHAR (20),
cno VARCHAR (4),
grand INT,
PRIMARY KEY (sno, cno)
);
DROP TABLE course;
DROP TABLE sc;
/* 修改表 */
ALTER TABLE student ADD (
Ssex CHAR (2),
sage SMALLINT,
sdept CHAR (20)
);
ALTER TABLE student MODIFY COLUMN sname VARCHAR (20);
ALTER TABLE student ADD UNIQUE (sdept);
ALTER TABLE student DROP INDEX sdept;
/*删除唯一性约束*/
/**/
TRUNCATE TABLE student /*插入记录*/
INSERT INTO student (Sno, sname, Ssex, sage, sdept)
VALUES
(
'200215121',
'zhangyong',
'nan',
20,
'CS'
);
INSERT INTO student (Sno, sname, Ssex, sage, sdept)
VALUES
(
'200215122',
'lichen',
'nv',
19,
'CS'
);
INSERT INTO student (Sno, sname, Ssex, sage, sdept)
VALUES
(
'200215123',
'wangmin',
'nv',
18,
'MA'
);
INSERT INTO student (Sno, sname, Ssex, sage, sdept)
VALUES
(
'200215125',
'zhangli',
'nan',
19,
'IS'
);
/*创建视图*/
CREATE VIEW VIEW_IS_STU AS SELECT * from student WHERE sdept = 'IS'
DROP table student RESTRICT; /*若有依赖关系不删除*/
DROP table student CASCADE; /*若有依赖关系递归删除*/
/*创建索引*/
CREATE UNIQUE INDEX INDEX_STUName ON student(sname)
DROP INDEX INDEX_STUName on student; /*sql 标准为 DROP INDEX INDEX_NAME */
/*查找数据*/
SELECT sname , 2015 - sage from student;
SELECT sname , 'Year of Birth', 2015 - sage as bbb , sdept , LOWER(sdept) from student; /*其中 LOWER(str) , 将字符串转化为小写*/
SELECT DISTINCT sno FROM sc; /*DISTINCT 关键字指明去除重复!*/
SELECT * FROM student WHERE sage BETWEEN 20 AND 30 ; /*BETWEEN AND */
SELECT * FROM student WHERE sage NOT BETWEEN 20 AND 30 ;
SELECT * FROM student WHERE sdept in ('cs' , 'ma'); /*IN 查找属性值属于集合的的元组*/
SELECT * FROM student WHERE sdept not in ('cs' , 'ma');
/*字符匹配 sql 中 % 的功能是代指任意长度的字符串 , 跟* 在unix 环境下一样。 _ 代指单个字符*/
SELECT * FROM student WHERE sno LIKE '%1'; /*搜索学号中以1结尾的记录*/
SELECT * FROM student WHERE sno LIKE '%_1';
SELECT * FROM student WHERE sno NOT LIKE '%_1';
SELECT * FROM student WHERE sname LIKE 'li%';
SELECT * FROM student WHERE sname LIKE '%li';
/*涉及空查询*/
SELECT * FROM sc WHERE grand is NULL;
SELECT * FROM sc WHERE grand = NULL; /*如果用= 来跟NULL 比较, 则查不出值来 */
SELECT * FROM sc WHERE grand is not NULL;
SELECT * FROM sc WHERE grand is 92; /*IS 可以比较的 仅仅是 NULL */
SELECT * FROM student ORDER BY sdept DESC , sage ASC; /* 制定多个排序的字段, 默认 ASC 方式排序*/
/*聚集函数*/
SELECT COUNT(*) from student;
SELECT COUNT(DISTINCT sno) from sc;
SELECT AVG(grand) from sc WHERE cno = '1';
SELECT cno , COUNT(sno) FROM sc GROUP BY cno;
SELECT * FROM sc GROUP BY sno HAVING count(*) > 2 /*where 作用于表或者视图, having 作用于组(group)*/
/*概念: 自然连接 与等值连接*/
SELECT student.* , sc.* FROM student , sc WHERE student.sno = sc.sno; /*等值连接: 连接符合为=*/
SELECT s.sno , sname , ssex , sage , sdept , cno from student s , sc WHERE s.Sno = sc.sno; /*去掉等值连接中重复的属性为自然连接*/
SELECT f.cno, s.cpno from course f ,course s WHERE f.cpno = s.cno; /*自身连接自身, 给表取两个名字, 视作两个表*/
/*概念: 外连接: 条件连接 只有在符合给出条件的情况下才, 显示出来。
如: WHERE s.Sno = sc.sno 拿s 表的 sno去sc 中sno列找, 如果有相等的则显示,
若没有则舍弃s表中该条记录。。。。 外连接则是保留此条记录*/
SELECT student.Sno, sname , ssex ,sdept , cno FROM student LEFT JOIN sc ON (student.sno = sc.sno); /*注意左连接的语法!!*/
/*嵌套查询, 内层查询中不能用 order by , ORDER BY 只可用在最终结果*/
SELECT * FROM student WHERE sno in ( SELECT sno from sc WHERE grand > 90);
UPDATE student SET sage = 99 WHERE Sno = '200215121';