第四章 使用sql管理数据库和表
4.1SQL的基本知识特点
SQL概述
SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，是一个综合的、通用的、功能极强的，同时又简洁易学的语言。
SQL特点
综合统一
高度非过程化
用同一种语法结构提供两种使用方式
语言简洁，易学易用
4.2 数据库管理
1. 创建数据库
语法格式：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS ]db_name [[DEFAULT] CHARACTER SET charset_name]
[[DEFALUT] COLLATE collation_name]
在MySQL中不区分大小写，在一定程度上方便使用。
【例5】创建一个名为StudentInfo的数据库，一般情况下在创建之间要用IF NOT EXISTS命令先判断数据库是否不存在。 CREATE DATABASE IF NOT EXISTS studentInfo ；
为了检验数据库中是否已经存在名为studentInfo的数据库，我们使用SHOW DATABASES;命令查看所有的数据库；
2. 修改数据库
语法格式：
ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT CHARACTER SET charset_name] | [[DEFAULT] COLLATE collation_name]
ALTER DATABASE用于更改数据库的全局特性，用户必须有数据库修改权限，才可以使用ALTER DATABASE修改数据库。
【例8】我们修改studentInfo数据库的字符集为gbk，执行结果如下:
3. 删除数据库
语法格式：
DROP DATABASE [IF EXISTS] db_name;
注意：删除数据库是指在数据库系统删除已经存在的数据库，删除数据库成功后，原来分配的空间将被收回。再删除数据库时，会删除数据库中的所有的表和所有的数据，因此，删除数据库时需要慎重考虑。
【例9】我们用drop database exampleDB命令删除刚才建立的exampleDB数据库，执行结果如下:
【例10】用以下两种命令再次删除exampleDB数据库，会有不同的提示：
4.3 SQL的数据表定义功能
1. 表的基本概念
数据库与表之间的关系：数据库是由各种数据表组成的，数据表是数据库中最重要的对象，用来存储和操作数据的逻辑结构。
表由列和行组成，列是表数据的描述，行是表数据的实例。
表的操作：创建新表、修改表和删除表。
2. 表字段的数据类型
为每张表的每个字段选择合适的数据类型是数据库设计过程中一个重要的步骤。
1). 数值类型
数字分为整数和小数。其中整数用整数类型表示，小数用浮点数类型和定点数类型表示。
浮点数类型包括单精度浮点数FLOAT类型和双精度浮点数DOUBLE类型。定点数类型就是DECIMAL类型,DEC和DECIMAL这两个定点数类型是同名词
定点数类型就是DECIMAL类型,DEC和DECIMAL这两个定点数类型是同名词.
注意事项：
数字类型的选择应遵循如下原则。
1)选择最小的可用类型，如果改字段的值不会超过127，则使用TINYINT比INT效果好。
2)对于完全都是数字的，即无小数点时，可以选择整数类型，比如年龄。
3)浮点类型用于可能具有的小数部分的数，比如学生成绩。
4)在需要表示金额等货币类型时优先选择DECIMAL数据类型。
2) 日期时间类型
MySQL主要支持5种日期类型：DATE、TIME, YEAR、DATATIME和TIMESTAMP。
3)字符串类型
字符串类型的数据分为普通的文本字符串类型（CHAR和VARCHAR）、可变类型（TEXT和BLOB）和特殊类型（SET和ENUM）。
5)二进制类型
MySQL主要支持7种二进制类型：binary、varbinary、bit、tinyblob、blob、mediumblob和longblob。
注：Text与blob都可以用来存储长字符串，text主要用来存储文本字符串，例如新闻內容、博客日志等数据；blob主要用来存储二进制数据，例如图片、音频、视频等二进制数据。
在真正的项目中，更多的时候需要将图片、音频、视频等二进制数据，以文件的形式存储在操作系统的文件系统中，而不会存储在数据库表中，毕竟，处理这些二进制数据并不是数据库管理系统的强项。
合适的数据类型
通常来说，数据类型的选择遵循以下原则。
(1)在符合应用要求（取值范围、精度）的前提下，尽量使用“短”数据类型。
(2)数据类型越简单越好。
(3)尽量采用精确小数类型（例如decimal），而不采用浮点数类型。
(4)在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储日期和时间。
(5)尽量避免NULL字段，建议将字段指定为NOT NULL约束。
3. 表的基本操作
1. 创建表
创建数据表可使用CREATE TABLE命令。
语法格式：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [ ( [ column_definition ], … | [ index_definition ] ) ] [table_option] [select_statement];
注意：在同一个数据库中，表名不能有重名。
2. 查看表
1)显示表的名称
语法格式： SHOW TABLES ;
【例２】显示数据库studentinfo中所有的表。
2)显示表的结构
查看表结构有简单查询和详细查询，可使用DESCRIBE/DESC语句和SHOW CREATE TABLE语句
语法格式： DESCRIBE 表名; 或者 DESC 表名; 或者 SHOW CREATE TABLE 表名;
3. 修改表
语法格式：
alter [ ignore] table table_name alter_specification [, alter_specification] add [column] column_definition[ first | after col_name] //添加字段 | alter [column] col_name {set default literal | drop default}//修改字段 | change [column] old_col_name column_definition [first| after col_name] //重命名字段 | modify [column] column_definition [first | after col_name] //修改字段 | drop [column] col_name //删除列 | rename [to] new_table_name //对表重命名 | order by col_name //按字段排序 | convert to character set character_name [collate collation_name] //将字段集转化为二进制 | [default] character set charset_name [collate collation_name//修改字符集
4. 复制表
语法格式：
create [temporary] table [if not exists] table_name [ () like old_table_name [] ] | [AS (select_statement)];
【例７】复制stu表中的学号(sno)，姓名(sname)到新的表SnoNameTable
5. 删除表
删除表可以用DROP TABLE 命令
语法格式：
drop table [if exists] table_name [,table_name] …
【例８】删除stu表和SnoNameTable表
可以用show tables命令查看studentInfo中先在剩余的表
6. 表管理中的注意事项
1）关于空值（NULL）的说明
空值通常用于表示未知、不可用或将在以后添加的数据，切不可将它与数字0或字符类型的空字符混为一谈。
2）关于列的标志(IDENTITY)属性
任何表都可以创建一个包含系统所生成序号值的标志列。该序号值唯一标志表中的一列，且可以作为键值。
3）关于列类型的隐含改变
在MySQL中，系统会隐含地改变在CREATE TABEL语句或ALTER TBALE语句中所指定的列类型。
长度小于4的VARCHAR类型会被改变为CHAR类型。
4.4 1 SQL的数据操纵功能
1.1 为表的所有字段插入数据
通常情况下，插入的新记录要包含表的所有字段。
INSERT语句有两种方式可以同时为表的所有字段插入数据。
第一种方式是不指定具体的字段名;
第二种方式是列出表的所有字段。
（1）INSERT语句中不指定具体的字段名
语法规则:
INSERTINTO 表名 VALUES(值1，值2，…，值n)
【例1】下面向student表中插入记录。
注意：student表包含6个字段，那么INSERT语句中的值也应该是6个。而且数据类型也应该与字段的数据类型一致。Sno，sname，ssex，sbirth，zno和sclass这6个字段是字符串类型，取值必须加上引号。如果不加上引号，数据库系统会报错。
（2) INSERT语句中列出所有字段
语法规则:
INSERT INTO表名(字段名1，字段名2，…，字段名n) VALUES(值1，值2，…，值n);
【例2】下面向student表中插入一条新记录。
注意：如果表的字段比较多，用第二种方法就比较麻烦。但是，第二种方法比较灵活。可以随意地设置字段的顺序，而不需要按照表定义时的顺序。值的顺序也必须跟着字段顺序的改变而改变。
【例3】下面向student表中插入一条新记录。INSERT语句中字段的顺序与表定义时的顺序不同。
注意：sbirth字段和ssex字段的顺序发生了改变。其对于值的位置也跟着发生了改变。
1.2 为表的指定字段插入数据
语法规则:
INSERT INTO表名(字段名1，字段名2，…，字段名n) VALUES(值1，值2，…，值n)；
【例4】下面向student表的sno，sname和ssex这3个字段插入数据。
注意：这种方式也可以随意的设置字段的顺序，而不需要按照表定义时的顺序。
1.3 同时插入多条记录
语法规则:
INSERT INTO表名[(字段名列表)] VALUES(取值列表1)，(取值列表2)，…(取值列表n)；
【例5】下面向student表的sno，sname和ssex这3个字段插入数据。总共插入3条记录。
【例6】下面向student表中插入3条新记录。
注意：不指定字段时，必须为每个字段都插入数据。如果指定字段，就只需要为指定的字段插入数据。
【例7】下面向student表的sno，sname和ssex字段插入数据。INSERT语句中，这3个字段的顺序可以任意排列。
技巧：向MySQL的某个表中插入多条记录时，可以使用多个INSERT语句逐条插入记录，也可以使用一个IN SERT语句插入多条记录。选择哪种方式通常根据个人喜好来决定。如果插入的记录很多时，一个INSERT语句插入多条记录的方式的速度会比较快。
2.1 修改数据
修改数据是更新表中已经存在的记录。
在MySQL中，通过UPDATE语句来修改数据。
语法规则:
UPDATE表名 SET字段名1＝取值1，字段名2＝取值2，… 字段名n＝取值n WHERE条件表达式
【例8】下面更新student表中sno值为1418855243的记录。将sname字段的值变为‘李壮’。将sbirth字段的值变为‘1996-03-23’。
说明：表中满足条件表达式的记录可能不止一条。使用UPDATE语句会更新所有满足条件的记录。但在MySQL中是需要一条一条的执行。
【例9】下面更新student表中sname值为李凯的记录。将sbirth字段的值变为“1997-01-01”。将ssex字段的值变为“女”。
结果显示更新了两条数据
2.2 删除数据
删除数据是删除表中已经存在的记录。
在MySQL中，通过DELETE语句来删除数据
语法规则:
DELETE FROM表名[WHERE条件表达式]
【例10】下面删除student表中sno值为1418855243的记录。
DELETE语句可以同时删除多条记录。
【例11】下面删除student表中sclass的值为‘商务1301’的记录。
结果显示删除了5条记录。
注意：DELETE语句中如果不加上“WHERE条件表达式”，数据库系统会删除指定表中的所有数据。请谨慎使用。
4.4. 2 单表查询
1．选择表中的若干列
(1)查询全部列。
【例】 查询全体学生的详细记录。 SELECT * FROM student
(2)查询指定列。
【例】 查询全体学生的学号与姓名。
SELECT Sno,Sname FROM student;
【例】 查询全体学生的姓名、学号、所在班级。
SELECT Sname,Sno,Sclass FROM student
(3)查询经过计算的值。
SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果
【例】 查询全体学生的姓名及其年龄。
SELECT Sname,YEAR(now())-YEAR(sbirth) FROM student
【例 】查询全体学生的姓名、出生年份。
SELECT Sname AS 学生姓名, YEAR(sbirth) AS 出生年份 FROM student
2．选择表中的若干元组
(1)消除取值重复的行。
【例】查询所有选修过课的学生的学号。
SELECT Sno FROM sc
该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，应使用DISTINCT短语：
SELECT DISTINCT Sno FROM sc
(2)查询满足条件的元组
查询满足指定条件的元组可以通过WHERE子句实现。
WHERE子句常用的查询条件如表所示。
3、比较大小
【例】 查询“商务1401”班的全体学生名单。
SELECT Sname FROM student WHERE Sclass = '商务1401'
【例】 查询所有“1995”年以前出生学生的姓名及其出生日期。
SELECT Sname,Sbirth FROM student WHERE Sbirth <’1995-01-01’
【例】 查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sno FROM sc WHERE Grade<60
这里使用了DISTINCT短语，会在结果集中去掉重复行。用在此处，使得当一个学生有多门课程不及格时，他的学号也只列一次。
②确定范围 
【例】查询在'1995-01-01' 和'1997-12-31'之间出生的学生的姓名、班级和出生日期。
SELECT Sname,Sclass,sbirth FROM student where sbirth BETWEEN '1995-01-01' AND '1997-12-31'
【例】查询在'1995-01-01' 和'1997-12-31'之间出生的学生的姓名、班级和出生日期。
SELECT Sname,Sclass,sbirth FROM student where sbirth NOT BETWEEN '1995-01-01' AND '1997-12-31'
③确定集合
【例】 查询“信管1401”和“工商1401”班学生的姓名和性别。
SELECT Sname,Ssex FROM student WHERE Sclass IN('信管1401'，'工商1401')
与IN相对的谓词是NOT IN，用于查找属性值不属于指定集合的元组。
【例】 查询既不是“信管1401”也不是“工商1401”班的学生的姓名和性别。 SELECT Sname, Ssex FROM student WHERE Sclass NOT IN('信管1401'，'工商1401')
④字符匹配
【例】 查询所有姓李的学生的姓名、学号和性别。
SELECT Sname,Sno,Ssex FROM student WHERE Sname LIKE '李%'
【例】 查询姓名中第二个字为“小”的学生的姓名和学号。 SELECT Sname,Sno FROM student WHERE Sname LIKE'_小%'
【例】 查询姓名中第二个字为“小”的学生的姓名和学号。
SELECT Sname,Sno FROM student WHERE Sname LIKE'_小%'
【例】 查询所有不姓李的学生的姓名。
SELECT Sname FROM student WHERE Sname NOT LIKE'李%'
⑤涉及空值的查询
【例】 某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，假设这些学生的成绩为空值(NULL)。下面我们来查一下缺少成绩的学生的学号和相应的课程号。
SELECT Sno,Cno FROM sc WHERE Grade IS NULL 注意这里的“IS”不能用等号“=”代替。
【例】查询所有成绩记录的学生学号和课程号。
SELECT Sno,Cno FROM sc WHERE Grade IS NOT NULL
⑥多重条件查询
【例】查询计算机1401班的男生的姓名和学号。
SELECT Sname,Sno FROM student WHERE Sclass = '计算机1401' AND Ssex=’男’
4.对查询结果排序
【例】 查询选修了“58130540”课程的学生的学号及其成绩，查询结果按分数降序排列。
SELECT Sno,Grade FROM sc WHERE Cno = ' 58130540' ORDER BY Grade DESC
【例】 查询全体学生的情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。
SELECT * FROM student ORDER BY Sclass,Sbirth DESC
5．使用集函数
【例】 查询学生总人数。
SELECT COUNT(*) FROM student
【例】 查询选修了课程的学生人数。
SELECT COUNT ( DISTINCT Sno ) FROM sc
【例】 计算选修课程号“58130540”的学生的平均成绩。
SELECT AVG (Grade ) FROM sc WHERE Cno = '58130540'
【例】查询选修课程号“58130540”的学生的最高分数。
SELECT MAX (Grade ) FROM sc WHERE Cno = '58130540'
5．使用集函数
【例】 查询选修课程号“58130540”的学生的最高分、最低分及平均分。
SELECT MAX (Grade )， MIN (Grade )， AVG (Grade ) FROM sc WHERE Cno = '58130540'
6．对查询结果分组
【例】 查询各个课程号与相应的选课人数。
SELECT Cno,COUNT (Sno ) FROM sc GROUP BY Cno
【例】 查询选修了2门以上课程的学生的学号。
SELECT Sno,COUNT(Cno) FROM sc GROUP BY Sno HAVING COUNT(Cno)> 2
4.5 SQL的数据查询功能（多表）
3.1 内连接查询
内连接查询是最常用的一种查询，也成为等同查询，就是在表关系的笛卡尔积数据记录中，保留表关系中所有相匹配的数据，而舍弃不匹配的数据。
按照匹配条件可以分为自然连接、等值连接和不等值连接。
（1）等值连接（inner join）
用来连接两个表的条件称为连接条件。如果连接条件中的连接运算符是=时，称为等值连接。
【例43】对选修表和课程表做等值连接(返回的结果限制在4条以内)
（2）自然连接（natural join）
自然连接操作就是表关系的笛卡尔积中，首先根据表关系中相同名称的字段进行记录匹配，然后去掉重复的字段。还可以理解为在等值连接中把目标列种重复的属性列去掉则为自然连接。
【例44】对选修表和课程表做自然连接(返回的结果限制在4条以内)
（3）不等值连接（inner join）
在where字句中用来连接两个表的条件称为连接条件。如果连接条件中的连接运算符是=时，称为等值连接。如果是其他的运算符，则是不等值连接
【例45】对选修表和课程表做不等值连接(返回的结果限制在4条以内)
3.2 外链接查询
外连接可以查询两个或两个以上的表，外连接查询和内连接查询非常的相似，也需要通过指定字段进行连接，当该字段取值相等时，可以查询出该表的记录；而且，该字段取值不相等的记录也可以查询出来。
外连接可分为左连接和右连接。
基本语法如下：
select 字段表 from 表1 LEFT | RIGHT [ OUTER ] JOIN 表2 ON 表1.字段=表2.字段
（1）左外连接（left join）
包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。
【例46】利用左连接方式 查询课程表和选修表
（2）右外连接（left join）
包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
【例47】利用左连接方式 查询课程表和选修表
3.3 子查询
有时候，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询。
通过子查询，可以实现多表之间的查询。子查询中可能包括IN， NOT IN， ANY， EXISTS和 NOT EXISTS等关键字。子查询中还可能包含比较运算符，如“=”、“!=”、“>”和“<”等。
（1）带IN关键字的子查询
IN关键字可以判断某个字段的值是否在指定的集合中
【例48】查询成绩在集合(65，75，85，95)中的学生的学号和成绩
【例50】下面查询选修过课程的student的记录。
（2）带EXISTS关键字的子查询
exists关键字表示存在，使用exists关的记录。键字时，内查询语句不返回查询
【例51】如果存在 “金融”这个专业，就查询所有的课程信息。
【例52】如果存在 “计算机科学与技术”这个专业，就查询所有的课程信息
（3）带ANY关键字的子查询
ANY关键字表示满足其中任何一个条件。
【例53】查询比其他班级比计算1401班级某一个同学年龄小的学生的姓名和年龄。
（4）带ALL关键字的子查询
ALL关键字表示满足所有的条件。
【例54】查询比其他班级比计算1401班级所有同学年龄都大的学生的姓名和年龄。
技巧
-
在具体应用中，如果需要实现多表数据记录查询，
-
一般不使用连接查询，因为该操作效率比较低，
-
于是MySQL又提供了连接查询的替代操作—子查询操作，
浙公网安备 33010602011771号