MySQL数据库课堂笔记

两年前的学科笔记，直接转载本人印象笔记上的内容，如若侵权请联系我

SQL语言基本数据类型

• 字符：CHAR、VARCHAR、TEXT

• char类型为固定长度的字符串，储存字母+数字+符号常用，最大长度为255
• varchar类型是长度可变的字符串，储存汉字常用，最大长度255
• text， 存放最大长度为 65,535 个字符的字符串。

• 整数：SMALLINT、INTEGER（INT）、BIGINT

• int, 长整型，带符号范围-2147483648到2147483647
• smallint,短整形， 带符号范围-32768到32767，无符号0到65535
• bigint,大整形， 带符号的范围是-9223372036854775808到9223372036854775807

• 浮点数：NUMBER(n,d)、FLOAT(n,d)

• FLOAT(size,d)， 带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。也可直接FLOAT(d)
• NUMBER(size,d),带有浮动小数点的小数字, 123.89- NUMBER ( 3 )-124, 123.89- NUMBER ( 6 , 2 )- 123.89, 123.89 - NUMBER ( 3 )-123.9, 123.89 - NUMBER (4,2 )-123.9- exceeds precision ( 有效位为 5 , 5 > 4 ), 123.89- NUMBER ( 6 , - 2 )-100

• 日期：DATE、DATETIME、TIME

• DATE(), 日期。格式：YYYY-MM-DD
• DATETIME(), 日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS
• TIME(), 时间。格式：HH:MM:SS

• 货币：MONEY

• money型数据可以存储从-922，337，203，685，477.5808到922， 337，203，685，477.5807的钱数。

---

数据定义SQL语句

数据库相关

• CREATE  DATABASE  <数据库名>;

• 例 CREATE DATABASE CourseDB;

• ALTER  DATABASE  <数据库名> <修改内容>;

• 例 将选课管理数据库CourseDB名称修改为 CourseManageDB：                                   ALTER  DATABASE  CourseDB  RENAME TO  CourseManageDB;

• DROP  DATABASE  <数据库名>;

• 例  DROP DATABASE CourseManageDB;

---

数据库表相关

• 数据库表创建

CREATE TABLE  <表名>

（ <列名1>  <数据类型>  [列完整性约束]，   

    <列名2>  <数据类型>   [列完整性约束]，       

    <列名3>  <数据类型>   [列完整性约束]，

     …    )；

列完整性约束关键词

• PRIMARY KEY——主键
• NOT NULL——非空值（必填）
• NULL——空值（非必填）
• UNIQUE——值唯一
• CourseName      varchar(20)  NOT  NULL  UNIQUE,
• CHECK——有效性检查（值在范围中）
• CourseType      varchar(10)  NULL CHECK(CourseType IN('基础课','专业课','选修课')
• DEFAULT——缺省值（默认值）
• TestMethod      char(4)      NOT  NULL  DEFAULT  '闭卷考试'

表约束定义主键（多个主键处理）

CREATE TABLE  <表名>

（ <列名1>  <数据类型>  [列完整性约束]，

     <列名2>  <数据类型>  [列完整性约束]，

     <列名3>  <数据类型>  [列完整性约束]，

     … 

  CONSTRAINT  <约束名>  PRIMARY Key(主键列)    )；

例

CREATE  TABLE  Plan( CourseID      char(4)      NOT  NULL,

TeacherID      char(4)      NOT  NULL,  

CourseRoom      varchar(30),   

CourseTime      varchar(30),   

Note          varchar(50),   

CONSTRAINT    CoursePlan_PK（数据库名）    PRIMARY Key(CourseID,TeacherID) );

使用表约束定义主键的优点：

• 便于定义复合主键
• 可命名主键约束
• 便于定义代理键

表约束定义代理键

CREATE TABLE  <表名> 

（ <代理键列名>  <Serial数据类型>  NOT NULL，    

   <列名2>  <数据类型>  [列完整性约束]，       

   <列名3>  <数据类型>  [列完整性约束]，  

   …   

   CONSTRAINT  <约束名>  PRIMARY Key(代理键列名)    )；

例

CREATE  TABLE  Plan( 

CoursePlanID    serial        NOT  NULL,   

CourseID      char(4)          NOT  NULL,   

TeacherID      char(4)          NOT  NULL,   

CourseRoom      varchar(30),   

CourseTime      varchar(30),   

Note          varchar(50),   

CONSTRAINT    CoursePlan_PK    PRIMARY Key(CoursePlanID) );

serial数据类型：自动编号

表约束定义外键

CREATE TABLE  <表名> （ 

<列名1>  <数据类型>  [列完整性约束]，     

<列名2>  <数据类型>   [列完整性约束]，         

<列名3>  <数据类型>  [列完整性约束]， 

    …     

CONSTRAINT  <约束名>  FOREIGN Key(外键列)    )；

例

CREATE  TABLE  Register( 

CourseRegID      serial    NOT  NULL,   

CoursePlanID      Int          NOT  NULL,   

StudentID      char(13),   

Note          varchar(30),   

CONSTRAINT    CourseRegID_PK    PRIMARY Key(CourseRegID),   

CONSTRAINT    CoursePlanID_FK    FOREIGN Key(CoursePlanID)  

   REFERENCES  Plan(CoursePlanID)    //键所在的表

 ON DELETE CASCADE,   //联级删除，即原表中删除了此处也会删除

CONSTRAINT    StudentID_FK    FOREIGN KEY(StudentID)   

   REFERENCES  Student(StudentID)    //键所在的表

 ON DELETE CASCADE );//联机删除

• 数据库表修改

1.语句基本格式

ALTER TABLE <表名> <修改方式>;

2.主要语句类型

  ADD修改方式，用于增加新列或列完整性约束、

  ALTER TABLE <表名> ADD <新列名称><数据类型>|[完整性约束];

  ALTER TABLE <表名> ADD <完整性约束名>（列名）;

  ALTER TABLE <表名> ADD 表级完整性约束条件;

    DROP修改方式，用于删除指定列或列的完整性约束条件

  ALTER TABLE<表名> DROP  COLUMN <列名>；

  ALTER TABLE<表名> DROP  CONSTRAINT<完整性约束名>；

  ALTER TABLE<表名> DROP  COLUMN <列名> CASCADE；引用了该列的其他对象一起删除

  ALTER TABLE<表名> DROP  COLUMN <列名> RESTRICT；若该列被引用则不删除

  ALTER TABLE<表名> DROP  CONSTRAINT<完整性约束名> CASCADE；联级删除

  ALTER TABLE<表名> DROP  CONSTRAINT<完整性约束名> CASCADE；限制删除

  RENAME修改方式，用于修改表名称、列名称

  ALTER TABLE <表名> RENAME TO <新表名>；

  ALTER TABLE <表名> RENAME <原列名> TO <新列名>；

 

  ALTER修改方式，用于修改列的数据类型

  ALTER TABLE <表名> ALTER  COLUMN <列名> TYPE<新的数据类型>；

• 数据库表删除

1.语句基本格式

  DROP TABLE <表名>;

例

DROP TABLE Register;

---

数据库索引相关

• 索引创建SQL语句 ： CREATE INDEX  <索引名>  ON <表名><（列名）>；

• 例 在学生信息表Student中，为出生日期Birthday列创建索引，以便支持按出生日期快速查询学生信息。
• CREATE  INDEX  Birthday_Idx  ON  STUDENT （Birthday）；

• 索引修改SQL语句 ：ALTER  INDEX  <索引名> <修改项>；

• 索引名称修改语句格式如下：ALTER  INDEX  <索引名> RENAME TO <新索引名>；
• 例 在学生信息表Student中，将原索引Birthday_Idx更名为Bday_Idx，其索引修改SQL语句如下：
• ALTER  INDEX Birthday_Idx  RENAME TO Bday_Idx；

• 索引删除SQL语句 ： DROP  INDEX  <索引名> ；

• 例 在学生信息表Student中，删除bday_idx索引，其索引删除SQL语句如下：
• DROP  INDEX bday_idx；

---

数据操纵相关

• 数据插入SQL语句

1.语句基本格式

  INSERT  INTO  <表名|视图名>[<列名表>]  VALUES （列值表）;

例 

INSERT INTO Student VALUES('2017220101105','柳因','女','1999-04-23','软件工程', 'liuyin@163.com');

  

• 数据更新SQL语句

1.语句基本格式

UPDATE  <表名|视图名>

SET  <列名1>=<表达式1> [，<列名2>=<表达式2>...] 

[WHERE   <条件表达式>]；

例 在学生信息表Student中，学生“赵东”的原有Email数据为空，现需要修改为“zhaodong@163.com”。

UPDATE  Student

SET  Email=‘zhaodong@163.com' 

WHERE   StudentName=‘赵东';

• 数据删除SQL语句

1.语句基本格式

DELETE 

FROM  <表名|视图名> 

[WHERE   <条件表达式>]；

例 在学生信息表STUDENT中，删除姓名为 “张亮”的学生数据，其数据删除的SQL语句如下：

DELETE 

FROM  STUDENT 

WHERE   StudentName='张亮';

---

单表数据查询

• 从单个表读取指定列

1.语句基本格式

SELECT  <目标列>[，<目标列>…]

FROM  <关系表>；

例

SELECT  studentID,studentNAME

FROM  student；

查询所有列：

SELECT  *

FROM  student；

过滤重复：

SELECT  DISTINCT major

FROM  student；

得到结果是不重复的几种专业

• 从单个表读取指定行

1.语句基本格式

SELECT  *

FROM  <关系表> 

WHERE  <条件表达式>;

例

SELECT  *

FROM  student

WHERE  gender='男';

• 从单个表读取指定行和列

1.语句基本格式

SELECT   <目标列>[，<目标列>…]

FROM  <关系表> 

WHERE  <条件表达式>;

例

SELECT  studentID,studentNAME

FROM  student

WHERE  gender='男';

• Where条件子句

• 使用BETWEEN..AND关键词来限定列值范围，还可以使用关键词LIKE与通配符来限定查询条件。
• 使用通配符来限定字符串数据范围。下划线（_）通配符用于代表一个未指定的字符。百分号（%）通配符用于代表一个或多个未指定的字符。

例 若要从STUDENT表中查询出生日期在“2000-01-01”到“2000-12-30”的学生数据。其数据查询SQL语句如下：

SELECT * FROM STUDENT

WHERE BirthDay BETWEEN '2000-01-01' AND '2000-12-30';

例 若要从STUDENT表中查询邮箱域名为“@163.com”的学生数据。其数据查询SQL语句如下：

SELECT  *FROM  STUDENT 

WHERE  Email  LIKE  '%@163.com’; 

通配符： %匹配任意长度字符，_匹配单个字符 

SELECT  * FROM  STUDENT 

WHERE  Email  LIKE  '____@163.com';

• 逻辑运算符（AND、OR、NOT）连接操作与IN或NOT IN关键词

例 从STUDENT表中查询性别为“男”，并且专业为“软件工程”的学生数据，其数据查询SQL语句如下。

SELECT  StudentID, StudentName, StudentGender, Major

FROM  STUDENT 

WHERE  Major='软件工程'  AND  StudentGender='男';

例 在STUDENT表查询时，使用IN关键字限定范围”计算机应用”专业的学生。其SQL语句如下所示。

SELECT  StudentID, StudentName, StudentGender, Major

FROM  STUDENT 

WHERE  Major IN  ('计算机应用');

• 对结果集进行排序

在SELECT查询语句返回的结果集中,行的顺序是任意的。如果需要结果集排序，可以在SELECT语句中加入ORDER BY关键字。

例 若要从STUDENT表中按学生出生日期降序输出学生数据，其数据查询SQL语句如下

SELECT  *

FROM  STUDENT 

ORDER  BY  Birthday DESC;

默认升序，ASC升序DESC降序

例 若要将STUDENT表查询数据，首先按出生日期降序排列，然后按姓名升序排列，其数据查询SQL语句如下：

SELECT  *

FROM  STUDENT 

ORDER  BY  Birthday DESC ,  StudentName  ASC;

---

内置函数与分组统计

• SQL聚合函数：一些对关系表中数值属性列进行计算并返回一个结果数值的函数。

聚合函数	功能
AVG （）	计算 结果集指定列 数据的 平均值
COUNT （）	计算结果集行数
MIN （）	找出 结果集指定列 数据的 最小值
MAX （）	找出 结果集指定列 数据的 最大值
SUM （）	计算 结果集指定列 数据的 总和

例 若要统计Student表中的学生人数，在SELECT语句中可以使用COUNT（）函数来计算，其查询SQL语句如下：

SELECT  COUNT（*） AS  学生人数

FROM  Student;

例 找出STUDENT表中年龄最大和年龄最小的学生出生日期，其查询SQL语句如下：

SELECT  Min（Birthday） AS 最大年龄，Max（Birthday） AS 最小年龄

FROM  Student;

• 分组数据统计，Group By子语句

分组统计SQL语句基本格式：

SELECT    统计函数（目标列）

FROM  <表名> 

[WHERE  条件] 

GROUP  BY  <目标列> 

[Having  条件];

例：若要分专业统计STUDENT表中男生人数，但限定只显示人数大于2的人数，其查询SQL语句如下：

SELECT  Major  AS 专业,  COUNT（StudentID） AS 学生人数

FROM  Student 

WHERE  StudentGender=’男’ 

GROUP  BY  Major 

HAVING  COUNT(*)>2;

group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面或者包含在having 后的聚合函数里。

where 子句的作用是在对查询结果进行分组前将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚合函数。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数。

having 子句中的每一个元素也必须出现在select列表中。

除了使用GROUP BY语句外，列的名称是不允许和内置函数一起混合使用。以下语句不规范。

SELECT    MaxHours, SUM(MaxHours)

FROM    PROJECT 

WHERE    ProjectID <= 1200;

DBMS产品在使用内置函数的方式也不一样。一般来说，内置函数是不能用于WHERE子句中的。以下语句不规范:

SELECT    ProjectID, MaxHours

FROM    PROJECT 

WHERE    MaxHours < AVG(MaxHours);

当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：

• 执行where子句查找符合条件的数据；
• 使用group by 子句对数据进行分组；
• 对group by 子句形成的组运行聚集函数计算每一组的值；
• 最后用having 子句去掉不符合条件的组。

---

多表关联查询

• 子查询与多表关联

子查询SQL语句基本格式：

SELECT    <目标列>[，<目标列>…]

FROM  <表名> 

WHERE  <条件中嵌套另一关系表的SELECT 查询结果集>

例 在选课管理系统数据库中，希望能检索出“计算机学院”的教师名单。

该操作需要关联教师信息表Teacher和学院信息表College，才能获得这些数据。这里可采用子查询方法实现两表关联查询，其查询SQL语句如下：

SELECT  TeacherID,  TeacherName,  TeacherTitle

FROM  Teacher 

WHERE  CollegeID  IN   

      (SELECT  CollegeID  

       FROM  College

       WHERE  CollegeName=’计算机学院’);

如果where子句用聚合运算，则必须使用子查询

• 使用连接关联多表查询

连接关联多表查询SQL语句基本格式：

SELECT    <目标列>[，<目标列>…] 

FROM  <表名1>，<表名2>，…, <表名n>， 

WHERE  <关系表之间的连接关联条件>

例 在选课管理系统数据库中，希望获得各个学院的教师信息列表，包括学院名称、教师编号、教师姓名、教师性别、职称等信息。要求按学院名称、教师编号分别排序输出，其查询SQL语句如下：

SELECT  B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称

FROM  Teacher  AS  A，College  AS  B 

WHERE  A.CollegeID=B.CollegeID  

ORDER  BY  B.CollegeName, A.TeacherID;

• JOIN…ON关键词

SELECT  <目标列>[，<目标列>…]

FROM  <表名1>  JOIN  <表名2>  ON <连接条件>;

例 在选课管理系统数据库中，希望获得各个学院的教师信息，包括学院名称、教师编号、教师姓名、教师性别、职称等信息。要求按学院名称、教师编号分别排序输出，其查询SQL语句如下：

SELECT  B.CollegeName AS 学院名称,  A.TeacherID  AS 编号, A.TeacherName  AS 姓名,  A.TeacherGender  AS 性别,  A. TeacherTitle  AS 职称

FROM  TEACHER  AS  A  JOIN  COLLEGE  AS  B 

ON  A.CollegeID=B.CollegeID  

ORDER  BY  B.CollegeName, A.TeacherID;

• 外部连接

例 在选课管理数据库中，希望查询所有开设课程的学生选课情况，包括课程名称、任课教师、选课学生人数。这需要关联课程信息表COURSE、教师信息表TEACHER、开课计划表PLAN、选课注册信息表REGISTER。其连接查询的SQL语句如下：

SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师,

   COUNT (R.CoursePlanID)  AS 选课人数 FROM  COURSE  AS  C  JOIN  PLAN  AS  P  ON  C.CourseID=P.CourseID

   JOIN  TEACHER  AS  T  ON  P.TeacherID=T.TeacherID

   JOIN  REGISTER  AS  R  ON  P.CoursePlanID=R.CoursePlanID 

GROUP  BY C.CourseName, T.TeacherName;

问题：在上面的内连接查询中，只能找出有学生注册的课程名称和选课人数，但不能找出没有学生注册的课程名称。

LEFT JOIN: 左外连接，即使没有与右表关联列值匹配，也从左表返回所有的行。

RIGHT JOIN: 右外连接，即使没有与左表关联列值匹配，也从右表返回所有的行。 

FULL JOIN: 全外连接，同时进行左连接和右连接，就返回所有行。

例 在选课管理系统数据库中，希望能查询所有开设课程的学生选课情况，包括课程名称、任课教师、选课学生人数。这需要关联课程信息表COURSE、开课计划表CPLAN、教师信息表TEACHER、选课注册信息表REGISTER。若使用左外连接查询，该JOIN…ON连接查询的SQL语句如下：

SELECT C.CourseName AS 课程名称, T.TeacherName AS 教师, COUNT  (R.CoursePlanID)  AS 选课人数 

FROM  COURSE  AS  C  JOIN  PLAN  AS  P   

ON  C.CourseID=P.CourseID  

JOIN  TEACHER  AS  T  ON  P.TeacherID=T.TeacherID LEFT  JOIN  REGISTER  AS  R  ON  P.CoursePlanID=R.CoursePlanID 

GROUP  BY C.CourseName, T.TeacherName;

---

数据控制

• GRANT权限授予语句

GRANT  <权限列表>  ON  <数据库对象>  TO  <用户或角色> [ WITH GRANT OPTION ]；

例 在选课管理系统数据库中，将课程注册表REGISTER的数据插入、数据修改、数据删除、数据查询访问权限赋予学生角色RoleS。

GRANT  SELECT, INSERT, UPDATE, DELETE  ON  REGISTER  TO  RoleS;

• REVOKE权限收回语句

REVOKE  <权限列表>  ON  <数据库对象>  FROM  <用户或角色> ;

例 在选课管理系统数据库中，收回学生角色RoleS在课程注册表REGISTER的数据删除访问权限。

REVOKE  DELETE  ON  REGISTER  FROM  RoleS;

• DENY权限拒绝语句

DENY  <权限列表>  ON  <数据库对象>  TO  <用户或角色> ;

例 在选课管理系统数据库中，若拒绝教师角色RoleT对教师表TEACHER的数据删除访问权限。

DENY  DELETE  ON  TEACHER  TO  RoleT;

---

视图

• 视图创建SQL语句

1.语句基本格式

CREATE  VIEW  <视图名>[(列名1)，(列名2)，…]  AS  <SELECT查询>；

例 在选课管理系统数据库中，若需要建立一个查看基础课数据的视图BasicCourseView，其创建SQL语句如下。

CREATE  VIEW  BasicCourseView  AS

SELECT  CourseName,  CourseCredit,  CoursePeriod,  TestMethod 

FROM    COURSE 

WHERE  CourseType=‘基础课’;

• 视图查询SQL语句

例 使用SELECT语句查询该视图数据，并按课程名称排序输出，其SQL语句如下：

SELECT   *

FROM  BasicCourseView 

ORDER  BY  CourseName;

• 视图删除SQL语句

1.语句基本格式

DROP  VIEW  <视图名>；

例

DROP  VIEW  BasicCourseView;

• 使用视图简化复杂SQL查询操作

例 在选课管理系统数据库中，查询选修“数据库系统原理与开发”课程的学生名单。这需要关联课程信息表COURSE、开课计划表PLAN、选课注册信息表REGISTER、学生信息表STUDENT，其查询SQL语句如下：

SELECT C.CourseName AS 课程名称, S.StudentID AS 学号, S.StudentName AS 姓名FROM  COURSE AS C, PLAN AS  P,  REGISTER  AS  R, STUDENT  AS  S   

WHERE  C.CourseID=P.CourseID  AND  C.CourseName='数据库原理及应用' AND  P.CoursePlanID=R.CoursePlanID  AND  R.StudentID=S.StudentID;

较冗杂

先定义一个名称为DatabaseCourseView视图，其创建SQL语句如下：

CREATE  VIEW  DatabaseCourseView  AS

    SELECT C.CourseName AS 课程名称, S.StudentID AS 学号, S.StudentName AS 姓名 FROM  COURSE AS C，PLAN AS  P,  REGISTER  AS  R, STUDENT  AS  S   

WHERE  C.CourseID=P.CourseID  AND  C. CourseName='数据库原理及应用'   AND  P.CoursePlanID=R.CoursePlanID  AND  R.StudentID=S. StudentID;

创建完成后外部就可以直接查询

SELECT  *  FROM  DatabaseCourseView;

• 使用视图提高数据访问安全性

例 在选课管理系统数据库中，除管理部门用户外，其他用户只能浏览教师基本信息，如教师编号、教师姓名、性别、职称、所属学院。教师其他信息需要被隐藏，可通过视图来处理，其视图创建SQL语句如下：

CREATE  VIEW  BasicTeacherInfoView  AS 

SELECT  T.TeacherID AS 编号, T.TeacherName  AS 教师姓名, T.TeacherGender  AS 性别, T. TeacherTitle  AS 职称,  C.CollegeName  AS 所属学院 FROM  TEACHER  AS  T,  COLLEGE      AS  C 

WHERE  T.CollegeID=C. CollegeID;

创建完成后外部就可以直接查询

SELECT  *  FROM  BasicTeacherInfoView

ORDER  BY  所属学院 , 教师姓名;