sql 系列功能 复习 （mysql 运行通过）

/*创建表*/
/* 学生表 */
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';