MySQL数据管理（增删改查）

一.增删改查

这里是为了查询而建立的三张表
Course(课程表)

SC（选课表）

Student（学生表）

1.查询

数据查询是数据库的核心操作。SQL提供了SELECT语句进行数据查询，该语句具有灵活的使用方式和丰富的功能，其一般格式为：

`SELECT [ALL | DISTINCT]

{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,…]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] #联合查询
[WHERE …] #指定结果需满足的条件
[GROUP BY …] #指定结果按照哪几个字段来分组
[HAVING] #过滤分组的记录必须满足的次要条件
[ORDER BY …] #指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
注意 : [ ] 括号代表可选的 , { }括号代表必须得
`

1.单表查询

单表查询指的是仅涉及一个表的查询：
一.选择表中的若干列
1.查询指定字段

SELECT * FROM Student;

SELECT Sname ,Ssex FROM Student;

2.查询经过计算结果的值
eg:查询全体学生的姓名以及出生年份
SELECT Sname,2019-Sage AS 生日 FROM Student;

3.查询所有学生的姓名、出生年份、和所在的院系
SELECT Sname ,‘Year of birth’,2019-Sage,LOWER(Sdept)AS department FROM Student;

二.选择表中的若干元组
1.查询选秀了课程的学号
SELECT Sno FROM SC;

2.上面的结果中可以去掉重复的行，使用关键字DISTINCT，这里需要注意的是DISTINCT的作用范围是所有目标列。
SELECT DISTINCT Sno FROM SC;

三.查询满足条件的元组可以通过关键字WHERE实现，WHERE子句常用的查询条件如下图所示：其中AND的优先级大于OR

3.1比较大小
  用于比较的运算符一般包括 =(等于)，>（大于）,<（小于）,>=（大于等于）,<=（小于等于）,!=（不等于）,!>（不大于）,!<（不小于）
1.查询计算机科学系的所有学生名单
SELECT Sname FROM Student WHERE Sdept=‘CS’;

2.查询年龄在20岁以上的所有学生的姓名

SELECT Sname FROM Student WHERE Sage>20;

3.2确定范围
  谓词BETWEEN…AND…和NOT　BETWEEN…AND…可以用来查找属性值在指定范围内的元组，其中BETWEEN后面的是下限AND后面的是上限
1.查询年龄在20到25岁之间的学生的姓名和学号
SELECT Sname,Sno FROM Student WHERE Sage BETWEEN 20 AND 25;

2.查询年龄不在20到25之间的学生的姓名和学号
SELECT Sname,Sno FROM Student WHERE Sage NOT BETWEEN 20 AND 25;

3.3确定集合
  谓词IN可以用来查找属性值属于指定集合的元组
1.查找计算机系（CS）、数学系（MA）、和信息系（IS）的学生姓名和性别
SELECT Sname,Ssex FROM Student WHERE Sdept IN(‘CS’,‘MA’,‘IS’);

3.4字符匹配（模糊查询）
  谓词LIKE可以用来进行字符串匹配，其一般语法格式为[NOT ]LIKE’<匹配串>’ [ESCAPE’<换码字符>’ ]，其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。其中

• %代表任意长度（长度可以为0）的字符串。例如a%b标识以a开头以b结尾的任意长度的字符串 例如abb,adsdb,ab等等都是满足的。
• _(下划线)代表任意单个字符，例如a_b表示以a开头以b结尾的长度为3的任意字符串，例如acb,adb都满足条件。

1.查询学号为03163093学生的所有信息
SELECT * FROM Student WHERE Sno LIKE ‘03163093’;
等价于SELECT * FROM Student WHERE Sno = ‘03163093’;
如果LIKE后面的匹配串不包含通配符，就可以用＝运算符取代LIKE谓词，用,!=取代运算符NOT LIKE谓词

２.查询姓张的所有学生的全部信息
SELECT * FROM Student WHERE Sname LIKE ‘张%’;

3.查询姓张的且名字长度为两个汉字的学生的信息
注意：数据库字符集为ASCII时一个汉字需要两个，当字符集为GBK时只需要一个
SELECT * FROM Student WHERE Sname LIKE ‘张_’;

4.查询不姓张的学生的所有信息
SELECT * FROM Student WHERE Sname NOT LIKE ‘张%’;

5.如果我们要查询的字符集里面本上就包括通配符%或者_，这是就需要ESCAPE ‘<换码字符>‘短语对通配符进行转义了
例如：查询DB_Design课程的课程号和学分
SELECT Cno,Ccredit FROM Course WHERE Cname LIKE ‘DB_Design’ ESCAPE’’;
ESCAPE’'表示“\”为换码字符，这样匹配串紧跟在“\”后面的“”不再具有通配符的含义，转义为普通的“”字符。
3.5空值的查询
  1.例如有些学生选修课程之后但是没有考试，这时它们的成绩应该为空而不是0，例如查询查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Grade FROM Sc WHERE GRADE IS NULL;
2.查询所有有成绩的学生的学号和成绩
SELECT Sno ,Grade FROM Sc WHERE Grade IS NOT NULL;

3.6多重条件查询
  逻辑运算符AND和OR可以用来连接多个查询条件。AND的优先级高于OR，但是可以利用括号改变优先级
1.查询计算机系年龄在20岁以上的学生的姓名
SELECT Sname FROM Student WHERE Sdept ='CS’AND Sage>20;

3.7ORDER BY 子句
  用户可以利用ORDER BY对子句查询结果按照一个或多个属性列的升序（ASC）或降序（ESC）排序，默认为升序。
ASC空值最后显示，DESC空值最先显示，另外一个SELECT语句中只能有一个排序语句。
1.查询选修了三号课程的学生的学号和成绩并且将成绩按照降序进行排序
SELECT Sno,Grade FROM Sc WHERE Cno =‘3’ ORDER BY Grade DESC ;

3.8聚集函数（WHERE子句中不能用聚集函数作为表达式）
为进一步方便用户，增强检索功能，SQL提供了许多聚集函数
1.查询学生总人数
SELECT COUNT(1)FROM Student;

2.查询选修了课程的人数
SELECT COUNT(DISTINCT Sno)FROM Sc;

3.计算学修了3号课程学生的平均成绩
SELECT AVG(Grade)FROM Sc WHERE Cno=‘2’;

4.查询选修了2号课程的学生最高分数
SELECT MAX(Grade)FROM Sc WHERE Cno=‘2’;

5.查询学生03163093选修课程的总学分数
SELECT SUM(Ccredit)FROM Sc,Course WHERE Sno = '03163093’AND Course.Cno=Sc.Cno;

3.9GROUP　BY子句
  GROUP　BY子句将查询结果按照某一列或者多列的值分组，值相等的为一组
1.查询各个课程号以及相应的选课人数
SELECT Cno,COUNT(Sno)FROM Sc GROUP BY Cno;

2.查询选修了三门以上课程的学生学号
SELECT Sno FROM Sc GROUP BY Sno HAVING COUNT(*)>=3;

  这里是先用GROUP　BY子句按照Sno进行分组，再用聚集函数COUNT对每一组计数，HAVING短语给出了选择组的条件，只有满足条件的组才会被选出来。
  WHERE和HAVING的区别在于多用对象不同，WHERE子句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于元组，从中挑出满足条件的元组。

３.查询平均成绩大于等于80分的学生的学号和平局成绩
SELECT Sno, AVG(Grade)FROM Sc GROUP BY Sno HAVING AVG(Grade)>80;

2.连接查询

  若一个查询同时涉及两个以上的表，则称为连接查询，连接查询分为等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询等。

2.AS子句作为别名
1.为列取别名
SELECT Sname AS ‘姓名’,Ssex AS ‘性别’ FROM Student;

2.为表取别名
SELECT Sname AS ‘姓名’,Ssex AS ‘性别’ FROM Student AS s;

3.CONCAT()函数拼接字符串,可把经计算或总结的结果用另一个新名称来代替
SELECT CONCAT(‘姓名:’,Sname)AS 姓名 FROM Student ;

2.多表查询

一.主键外键

1.什么是主键外键：

主键
  某个属性可以唯一标识某一个元组，那么就称这个属性为主键

外键
  如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系中的外键。由此可以知道，外键建立了两个关系表之间的关联。以另一个关系的外键作为主关键字的表称为主表，具有此外键的表称为主表的从表。
外键作用
  保证数据一致性，完整性，主要目的是控制存储在外键表中的数据约束。使得两张表形成关联，外键只能引用外表中的列的值或使用空值。

2.创建外键的两种方式：

1.第一种创建方式，创建子表的时候创建外键

CREATE TABLE Student(
Sno CHAR(8)PRIMARY KEY,
Sname CHAR(20)UNIQUE,
Ssex CHAR(2),
Sage INT,
Sdept CHAR(20)

);

CREATE TABLE SC(
Sno CHAR(8),
Cno CHAR(4),
Ccredit INT,
Cno PRIMARY KEY,
FOREIGN KEY(Sno)REFERENCES Student(Sno),
);

2.第二种创建方式：创建完子表之后，修改子表添加外键

格式：ALTER TABLE '表名1' ADD CONSTRAINT  '约束名' FOREIGN KEY(字段名)REFERENCES '表明2'(字段名);
ALTER TABLE Course ADD CONSTRAINT  aaa FOREIGN KEY(Cpno)REFERENCES Course(Cno);

3.删除外键：

格式：ALTTER TABLE '表名1' DROP FOREIGNKEY '约束名'
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;