数据库笔记（2）

语法格式：

create {database | schema} [if not exists ] 数据库名称

[[default] character set 字符集名称]

[[defalut] 校对规则] ;

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS ] 数据库名称

[[DEFAULT] CHARACTER SET 字符集名称]

[[DEFALUT] 校对规则] ;

create创建

database数据库

schema数据库对象集合，中文含义为纲要，提要

if not exists如果不存在

default不履行

character set字符集

举例说明：创建schooldb 数据库，编码为uft8

create database schooldb default character set utf8;

如果不存在，创建studentinfo数据库

create database if not exists studentinfo;

如果字符集和校对规则参数不写，默认使用服务器的字符集和校对规则

可以通过命令查询当前服务器或数据库的字符集：

SHOW VARIABLES LIKE ‘character_set_server’| ‘character_set_database’

show variables like ‘character_set_server’| ‘character_set_database’

查看所有的数据库：

SHOW DATABASES| SCHEMAS;

show databases| schemas;

例如： SHOW DATABASES;

查看数据库的详细信息：

SHOW CREATE {DATABASE|SCHEMA} 数据库名称 ;

show create {database|schema} 数据库名称 ;

例如： SHOW CREATE DATABASE test;

在某个数据库中操作：

USE 数据库名称;

例如：USE test;

show variables like，意为展示变量，如

character set server，字符集服务器

character se database，字符集数据库

2修改数据库

修改数据库名

如果MySQL数据库的存储引擎是MyISAM，那么只要修改DATA目录下的库名文件夹就可以了。

如果存储引擎是InnoDB，是无法修改数据库名称的，只能修改字符集和校对规则。

一般情况下，不建议修改数据库名

修改数据库的编码方式和校对规则的语法格式：

ALTER {DATABASE | SCHEMA} [db_name数据库名称]

[DEFAULT CHARACTER SET charset_name字符集名称] |

[[DEFAULT] COLLATE collation_name校对规则]

alter {database | schema} [db_name数据库名称]

[default character set charset_name字符集名称] |

[[default] collate collation_name校对规则]

例：将schoolDB的编码方式有GBK修改为UTF8。

ALTER DATABASE schoolDB CHARACTER SET UTF8;

3删除数据库

删除数据库成功后，原来分配的空间将被收回，所有数据全部删掉，无法恢复。

建议先将该数据库备份，然后再进行删除，防止误删。

删除数据库语法格式：

DROP DATABASE [IF EXISTS] 数据库名字;

drop database [if exists] 数据库名字;

例如：删除schoolDB数据库。

DROP DATABASE IF EXISTS schoolDB；

总结

创建数据库：CREATE DATABASE mydb;

切换（使用）数据库：USE mydb;

查询数据库：SHOW DATABASES;

查看数据库定义：SHOW CREATE DATABASE mydb;

查看当前打开数据库名称：SELECT DATABASE();或SELECT SCHEMA();

删除数据库：DROP DATABASE mydb;

查看上一步操作产生的警告信息:SHOW WARNINGS;

创建数据库：create database mydb;

切换（使用）数据库：use mydb;

查询数据库：show databases;

查看数据库定义：show create database mydb;

查看当前打开数据库名称：select database();或select schema();

删除数据库：drop database mydb;

查看上一步操作产生的警告信息:show warnings;

select选择

drop终止，其他中文含义有推动；帮助；宣扬；下降；终止”，“滴；落下；空投；微量；

warnings警告

第7章 MySQL表定义与完整性约束控制

数据表

数据库是由各种数据表组成的，数据表是数据库中最重要的对象，是数据库中存储数据的基本单位，也是其他对象的基础。

表由一个或多个字段组成，每个字段需要有对应的数据类型。

数据表先有表结构，再有数据（没有数据称为空表),对表的操作包括表结构的操作、表中数据的操作

数据表至少有一列，可以没有行或者多行

数据表名称要求唯一，而且不要包含特殊字符

建表的原则

1.每个表只包含关于一个主题的信息，“一事一表”原则

2.在一个数据库中，同一表内、不同表之间都不包含重复信息

3.遵循关系规范化原则，达到3NF

为表的每个字段选择合适的数据类型

合适的数据类型可以有效地节省数据库的存储空间，包括内存和外存，同时也可以提升数据的计算性能，节省数据的检索时间。

关于MYSQL中的数据类型

数值类型

数字分为整数和小数。

整数：用整数类型表示。

INTEGER或INT、SMALLINT、TINYINT、MEDIUMINT和BIGIN

integer或int、smallint、tinyint、mediumint和bigin

整数类型	字节数
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INTEGER或INT	4
BIGINT	8

可以指定整数值的最大显示宽度，如 int(10)

如果插入的数值大于宽度，只要不超出该类型的取值范围，依然可以正确插入

小数：整数部分+小数部分

浮点数类型：单精度浮点数FLOAT float、双精度浮点数DOUBLE double

定点数类型(精确数值型)：DECIMAL declmal类型

都可以用（M，D）来对数据类型进行修饰，M表示该值的总共长度，D表示小数点后面的长度，如float(7,4)，decimal(4,2)

如果小数部分超出位数，则进行四舍五入。整数部分超出会报错。

不指定M和D时，FLOAT和DOUBLE默认会按照实际的精度来显示，而DECIMAL默认M为10，D为0。

类型	字节数
FLOAT	4
DOUBLE	8
DECIMAL	最大8字节，实际范围由M、D决定当M>D时为M+2，否则为D+2

浮点数(FLOAT、DOUBLE)与定点数(DECIMAL)的区别：

Double和Decimal类型的取值范围内一样

建议使用DECIMAL，尽量不使用浮点类型浮点数会丢失精度

建表时数字类型的选择应遵循的原则：

1)根据数据可能的取值范围，选择最小的可用类型，如果改字段的值不会超过127，则使用TINYINT比INT效果好。

2)对于无小数点的数据，选择整数类型，比如年龄。

3)在需要表示精确数值时，优先选择DECIMAL数据类型

日期时间类型

MySQL主要支持的日期类型：

（1）DATE表示日期，默认格式为‘YYYY-MM-DD’；

（2）TIME表示时间，默认格式为‘HH:ii:ss’；

（3）YEAR表示年份；

（4）DATATIME、TIMESTAMP是日期和时间的混合类型，默认格式为‘YYYY-MM-DD HH:ii:SS’。日期类型的数据要使用单引号括起来，如 ’2021-4-1’ ；

日期类型的数据是一个数值类型，可以参与简单的加、减运算。

字符串类型

定长字符串CHAR(n)

字符串长度固定为n，n取值在0～255之间, 数据超过长度n报错。

变长字符串VARCHAR(n)

字符串最大长度为n，n取值在0~65535之间，数据超过长度n报错。

区别：字符串末尾有空格时，CHAR类型的值会去掉空格，而VARCHAR不会；

CHAR分配固定大小的存储空间,占用空间大，速度快；

VARCHAR根据实际存储的数据分配空间，占用空间小，速度慢

文本类型

TEXT

保存非二进制字符串，存储大容量文本，只能存储文本字符；

TEXT不能有默认值

数据检索速度由快到慢： CHAR > VACHAR > TEXT 为以后优化数据时，能不用text就不用text

复合类型

包括枚举类型ENUM、集合类型SET 本质上是字符型

enum只允许从一个集合中取一个值，类似于单选按钮

set允许从一个集合中取得多个值，类似于复选框的功能

二进制类型

MySQL主要支持7种二进制类型：binary、varbinary、bit、tinyblob、blob、mediumblob和longblob。

二进制类型的字段主要用于存储由 ’ 0’ 和 ’ 1’ 组成的字符串

二进制类型与字符串类型的区别：字符串类型的数据按字符为单位进行存储，因此存在多种字符集、多种字符序；二进制类型按位或按字节为单位进行存储，仅存在二进制字符集binary。

注： (1)text与blob都可以用来存储长字符串 text主要存储文本字符串，如新闻、博客 blob主要存储二进制数据，如图片、音视频 (2)实际项目中，图片、音频、视频等二进制数据并不直接存储在数据库中，而以文件的形式直接在操作系统中。

在创建表时，使用字符串类型时应遵循以下原则：

1)从速度方面考虑，对于固定宽度的数据列，建议使用CHAR类型。

2)要节省空间，宽度不确定的数据列，使用VARCHAR类型。

3)要将列中的内容限制在一种选择，可以使用ENUM类型。

4)允许在一个列中有多于一个的条目，可以使用SET类型。

5)大容量文本，要搜索的内容不区分大小写，可以使用TEXT类型，少用。

6)大容量文本，如果要搜索的内容区分大小写，可以使用BLOB类型，少用。

选择数据类型的原则：

(1)在符合应用要求（取值范围、精度）的前提下，尽量使用“短”数据类型。

(2)数据类型越简单越好。

(3)尽量采用精确小数类型（例如decimal），而不采用浮点数类型。

(4)在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储日期和时间。

(5)尽量避免NULL字段，建议将字段指定为NOT NULL约束。

运算符

MySQL主要支持算术运算符、比较运算符、逻辑运算符和位运算符四种类型。

算术运算符

MySQL数据库支持的算术运算符包括加、减、乘、除和取余运算。他们是最常用的、最简单的一类运算符。

计算结果不确定时返回NULL，如0做除数。

比较运算符

在两个操作数均为NULL时，返回1，其中一个为NULL时返回0

如果有一个操作数值为NULL，返回NUL

逻辑运算符

如果有一个操作数值为NULL，返回NULL

位运算

将给定的操作数转化为二进制后，对各操作数的每一位都进行指定的逻辑运算，将得到的二进制结果再转化为十进制数。

运算符：

&、 |、 ^、 ~、 >>、 <<

位与位或位异或位取反位右移位左移

数据表操作

建立数据表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <表名>

create [temporary] table [if not exists] <表名>

(

<列名1> <数据类型> [列级完整性约束条件] ,

<列名2> <数据类型> [列级完整性约束条件] …… ,

<表级完整性约束条件> ] |[索引定义]

)

[表选项]；

temporary短暂的，临时的

表选项： ENGINE=存储引擎 DEFAULT CHARSET=字符集 COLLATE=校对规则;

说明（续）：

1.不允许存在同名表

2. IF NOT EXISTS：如果不存在同名表时才执行CREATE TABLE操作，避免重名引起错误信息

3.列的定义。包括指定列名、数据类型、以及约束条件(是否允许空值，指定默认值、主键约束、唯一性约束、注释字段名、是否为外键等)。

查看数据表结构

1)显示表的名称

显示指定数据库中存放的所有表名

语法格式：

SHOW TABLES ;

show tables ;

2)显示表的结构

查看表结构有简单查询和详细查询

语法格式：

DESCRIBE 表名;

DESC 表名;

SHOW CREATE TABLE 表名;

describe 表名;desc 表名;show create table 表名;

修改数据表

修改操作包括：

(1)更改表名（改变后与此表相关的对象将无效）

(2)增加新列/约束

(3)修改已有列的属性（数据类型、是否为空值、约束条件）

(4)删除列/约束（被删除的列是不可恢复的。删除前，必须先删除该列的索引和约束）

用SQL命令修改数据表

Alter Table命令

（1）Add 选项 Add 新增列/新增约束

（2）Modify 选项 Modify 修改列 / 修改NOT NULL、UNIQUE、DEFAULT约束

其他约束：可以采取先删除再重新添加的方式

（3）Drop 选项 Drop 删除列 / 删除约束

修改表名

方法一：

语法格式： ALTER TABLE 表名 RENAME [TO|AS] 新表名；

alter table 表名 rename [to|as] 新表名；

注：TO 或AS都可以，也以省略掉

例如：ALTER TABLE employee RENAME TO reg_user;

方法二：

语法格式：RENAME TABLE 表名 TO 新表名；

rename table 表名 to 新表名；

例如：RENAME TABLE reg_user TO employee ; 不建议修改表名

1.ADD添加数据表字段

ALTER TABLE 表名

ADD 列定义[FIRST|AFTER 已有列名]

|ADD 完整性约束定义

例，ALTER TABLE employee ADD testcol3 INT DEFAULT 100 AFTER username;

新列的值自动填充为NULL，所以不能为新列指定NOT NULL约束。

可以为新增的列指定位置。

FIRST表示将新增列作为表的第一个字段

AFTER表示将新增列添加到指定列后面

2.Modify选项

ALTER TABLE 表名

Modify 列名数据类型 [NULL|NOT NULL][UNIQUE][DEFAULT]

[FIRST][AFTER已有列名]

例，将S表Dno字段修改为CHAR(3), 非空，默认值 ‘123’ 并移动到第一列

ALTER TABLE S MODIFY Dno CHAR(3) NOT NULL DEFAULT '123' FIRST;

不能将含有空值的列修改为NOT NULL约束；

若列中已有数据，则不能减少该列的宽度；

3.Drop 选项

1删除列 ALTER TABLE 表名 DROP 列名

例，删除S表中的SCORE字段

Alter Table S DROP SCORE;

2删除约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名

约束名：在创建约束的时候，给约束起的名字

ALTER TABLE SC ADD Check (Score >0);

ALTER TABLE SC ADD CONSTRAINT C1 Check (Score >0);

ALTER TABLE SC DROP CONSTRAINT C1;

有名字的约束可以通过名字删除

没有名字的约束，系统会自动起名，删除时，需要在系统中使用“ show create table 表名”命令查找出名字

4.Change 选项修改列的名字

语法格式：

ALTER TABLE 表名 CHANGE 旧字段名称新字段名称；

例，将S表name字段名改为sname

ALTER TABLE S CHANGE name sname;

alter table 表名

add [column]列名[ first | after 已有列名] //添加列

| alter [column]列名 {set default 默认值| drop default} //修改字段

| change [column] 旧列名新列名 [first| after 已有列名] //重命名列

| modify [column] 列定义[first | after 已有列名] //修改列

| drop [column] 列名 //删除列

| rename [as| to] 新表名 //对表重命名

| order by 列名 //按字段排序

| [default] character set 字符集 [collate 校对规则] //修改字符集

复制数据表

可以通过CREATE TABLE 命令复制表的结构和数据

语法格式只复制表结构，不复制数据

CREATE TABLE [IF NOT EXISTS] 表名 LIKE 原表名

create table [if not exists] 表名 like 原表名

复制表结构和数据

CREATE TABLE 表名 AS select查询语句;

将select查询的结果也复制到表中

【例7-6】复制student表，复制后的新表名为stu。

CREATE TABLE stu LIKE student;

【例7-7】复制student表中的sno、sname列到stu1中

CREATE TABLE stu1 AS SELECT sno,sname FROM student;

删除数据表

删除表语法格式

DROP TABLE [IF EXISTS] 表名;

drop table [if exists] 表名;

删除vipuser12 DROP TABLE vipuser12;

删除多张表 DROP TABLE IF EXISTS vipuser11,vipuser10,vipuser9;

关于列类型的隐含改变

在MySQL中，存在以下一些情形，系统会自动地改变建表时所指定的列类型。

（1）长度小于4的VARCHAR类型会被改变为CHAR类型。

（2）当一张表含有任何变长的列时，例如VARCHAR，TEXT，BLOB类型的列，该表中所有大于３个字符的其他CHAR类型列被改变为VARCHAR类型列。

MYSQL约束控制

MySQL中常用约束 [CONSTRAINT <约束名> ] <约束类型>

列约束

对某一特定列的约束，包含在列的定义中

表级约束

与列定义相互独立，通常用于对多个列一起约束

约束类型有：

PRIMARY KEY主键 FOREIGN KEY外键 NOT NULL非空 UNIQUE KEY唯一 DEFAULT默认值 AUTO_INCREMENT自增长 CHECK检查

primary key主键 foreign key外键 not null非空 unique key唯一 default默认值 auto_increment自增长 check检查

数据完整性约束

实体完整性，参照完整性，域完整性，用户自定义完整性

字段约束

1.主键约束（PRIMARY KEY）

用来定义表的主键，起唯一标识作用。

数据表必须定义主键，一个表只能定义一个主键（可能包含一个列或多个列）

主键列的值不能为NULL，也不能重复

主键可以定义为列约束或者表约束，复合主键必须定义为表约束

Primary Key用于列约束格式: [ Constraint 约束名] Primary Key

Primary Key用于定义表约束 [Constraint 约束名]Primary Key (列名1 [{, 列名2}])

一个表只能有一个主键

复合主键必须定义为表级约束

2.外键约束（FOREIGN KEY）

指定一个(组)列为表的外键，该表称为“从表”

外键与其他表的主键或唯一键相关联，此表称为“主表”

外键的取值是主表中主键或唯一键的值，或者为Null

父表对子表数据更新的影响：

外键约束的级联选项

FOREIGN KEY（列名）REFERENCES 主表（列名）

[ ON DELETE {CASCADE| RESTRICT |SET NULL | NO ACTION} ]

[ON UPDATE {CASCADE| RESTRICT |SET NULL | NO ACTION} ]

foreign key（列名）references 主表（列名）

[ on delete {cascade| restrict |set null | no action} ]

[on update {cascade| restrict |set null | no action} ]

CASCADE: 从父表删除或更新时，自动删除或更新子表中匹配的行。

SET NULL：从父表删除或更新行时，设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL，且不是主属性。

RESTRICT：拒绝对父表的删除或更新操作。

NO ACTION：与RESTRICT相同。

3.非空约束(NOT NULL)

NULL表示“没有数据”、“不确定” 。不是0也不是空格

NOT NULL约束表示该列的值不能为NULL

格式：[Constraint 约束名] NOT NULL

4.唯一约束（UNIQUE）

指表在某一列或多个列组合的取值必须唯一

说明：一个表中可以存在多个唯一约束。

满足唯一约束的字段允许取NULL值，但该列只能出现一个空值。

唯一约束自动在指定字段上创建唯一索引，如果删除唯一约束，只需删除对应的索引。

5.默认约束（Default）

为某个字段添加默认值

输入数据时，如果不输入该列值，则取默认值

语法格式：列定义 DEFAULT 默认值

6.自增约束（AUTO_INCREMENT）

AUTO_INCREMENT是MySQL扩展的完整性约束，当为数据库表中插入新记录时，字段上的值会自动生成唯一的ID。

在具体设置AUTO_INCREMENT约束时，一个表中只能有一个字段使用该约束，该字段的数据类型必须是整型类型

由于设置auto_increment约束后的字段会生成唯一的ID，所以该字段也经常会设置为PK主键。

从1开始增长的每次加1。

7. Check约束

用来检查字段值所允许的取值范围

一个表中可以定义多个Check约束

当更新表中的数据时，Check约束将验证数据，如果不符合check约束，会被拒绝 Check既可以用于列约束，也可以用于表约束

格式：[Constraint 约束名] Check (条件 )

删除约束

一个字段的所有命令都可以用ALTER TABLE命令删除

重点：对数据表操作的SQL语句

使用sql语句创建数据表读者（借书证号，姓名，性别，所属院系，借阅数量），数据类型和长度自行设定.

create table 读者

( 借书证号 char(10),

姓名 varchar(30) ,

性别 char(3),

所属院系 varchar(10)，

借阅数量 int )

创建数据表读者（借书证号，姓名，性别，院系编号，借阅数量）要求借书证号列为主键，院系编号列为外键char(4)，与院系表的院系编号列相关联。

create table 读者

( 借书证号 char(10) primary key,

姓名 varchar(30),

性别 char(3),

院系 char(4) references 院系(院系编号),

借阅数量 decimal(2) )

创建数据表读者（借书证号，姓名，性别，院系编号，借阅数量）要求借书证号列为主键，姓名列不能为空，性别只能取’男’或’女’，院系列为外键，与院系表的院系编号列相关联，借阅数量列默认值为0。

create table 读者

( 借书证号 char(10) primary key ,

姓名 varchar(30) not null ,

性别 char(3) check (性别 in(‘男’,’女’) ) ,

院系编号 char(4) references 院系(院系编号) ,

借阅数量 decimal(2) default 0

)

使用sql语句实现

(1)在sc表中增加完整性约束定义，使score列值在0～100之间。

(2)在sc表中增加level列，varchar(10)类型

(1) alter table sc add check (score between 0 and 100);

（2） alter table sc add level varchar(10) ;

使用sql语句创建数据库jxgl，字符集为utf8，校对规则为utf8_bin

我的答案：

create database if not exists jxgl

default character set utf8

default collate utf8_bin;

使用sql语句创建 teacherinfo 表。提交语句

我的答案：

create table teacherinfo(

id int(4) not null auto_increment,

num char(6) not null,

name varchar (20) not null ,

sex varchar (4) not null ,

birthday date not null,

address varchar (50) ,

primary key(id)

);

使用sql语句创建工资表salary，表结构如下.提交语句

我的答案：

create table salary(

sid char(5) not null,

snum char(6) not null,

saltotal decimal (10,2) not null ,

salreal decimal (10,2) not null ,

saldate date not null,

primary key(sid)

);

第8章 MySQL数据操作管理

SELECT查询语句

是使用频率最高的SQL语句。

SELECT语句的基本格式:

SELECT 列名1 [, 列名2, …]

FROM 表名1 [, 表名2 , …]

[WHERE 检索条件 ]

分别对应，投影，关系，选取

Select语句的执行过程

1.根据where条件从from指定表中选取行

2.将选出的行按照列名进行投影

[例8-13]查询student表中学生的所有信息。*

SELECT FROM student; 或 SELECT * FROM student;

查询全体学生的姓名、学号、班级。

SELECT sname, sno, sclass FROM student;

说明：可以根据需要改变列的显示顺序。

3.消除取值重复的行DISTINCT

4.为表、列起别名AS

5.在结果集中对列值计算

对各列值进行四则运算，只显示结果集计算后的结果，不改变表中的数据

条件查询

在select语句中使用where子句指定查询条件

条件描述：where 列名1 比较运算符列名2或常数

1.带关系运算符和逻辑运算符的查询

注意：表名、列名不需要加引号，表中的字符数据必须加引号

例如：查询成绩高于90分的学生信息。 SELECT * FROM SC WHERE GRADE > 90;

多条件之间用NOT、AND、OR 连接可以使用小括号改变优先级

例] 查询工商1401班女生的姓名。

SELECT sname FROM student

WHERE sclass= '工商1401’ AND ssex= '女’;

2.带IN关键字的查询 (NOT) IN操作：属性值(不)在指定集合中。

[例8-21]查询SC表中成绩是65、75、85或95的学生学号、课号和成绩。

SELECT sno,cno,grade FROM sc

WHERE grade IN (65,75,85,95);

注意：每个元素中间用逗号隔开

3.带BETWEEN AND关键字的查询

例8-22 查询学号在1411855228~1414320425之间的学生信息

select * from student where sno BETWEEN '1411855228' and '1414320425';

注意：UTF8编码中，数字字符和英文字符按ASCII排序

4. NULL空值查询

某个字段没有值称之为具有空值NULL 空值不同于0和空格，它不占任何存储空间

[例3-36] 在SC表查询没有参加考试的学生的学号和相应的课程号。

SELECT SNo, CNo FROM SC WHERE Grade IS NULL;

注意：要写is null 不能写=null

非空：IS NOT NULL

5.部分匹配查询LIKE

使用LIKE或NOT LIKE进行部分匹配查询（模糊查询）

格式： WHERE 属性名 LIKE 字符串常量

字符串常量中可以使用通配符

例：查询姓李且姓名是三个字的学生信息。

SELECT * FROM student

WHERE sname LIKE ‘李_ _';

通配符

功能

举例

%

代表0个或多个字符

‘ab%’,  ’刘%华’

_

代表一个字符

‘ab_’,  ’刘_华’

[     ]

在某一集合内的字符

[abc]

如果like要匹配的字符串本就含有通配符‘%’ 或‘_’,需要使用ESCAPE<转化码>短语，对通配符进行转义。

例，查询以“DB_”开头的，且倒数第三个字符为‘i’的课程的详细情况。

SELECT * FROM course WHERE cname like ‘DB\ _%i _ _’ ESCAPE '\';

正则表达式

MySQL中使用正则表达式查询：REGEXP '匹配方式‘

模式字符	含义
^	匹配字符开始部分
$	匹配字符结尾的部分
.	代表字符串中的任意一个字符，包括回车和换行
[字符集合]	匹配字符集合中的任一个字符
[^字符集合]	匹配除了字符集合以外的任何一个字符
S1\|S2\|S3	匹配S1、S2、S3中的任意一个字符串
*	代表0个1或者多个其前的字符
+	代表1个或者多个其前的字符
String{N}	字符串出现N次
字符串{M,N}	字符串至少出现M次，最多N次

正则表达式应用示例：

查询用户以t开始的用户（不区分大小写）

SELECT * FROM cms_user WHERE username REGEXP '^t';

$匹配字符串结尾的部分，查询用户以g结尾的用户（不区分大小写）

SELECT * FROM cms_user WHERE username REGEXP 'g$';

查询用户名包含“lto”

SELECT * FROM cms_user WHERE username REGEXP '[lto]';

‘lg’中间有两位任意字符（使用下划线，错误的）

SELECT * FROM cms_user WHERE username REGEXP 'l__g';

‘lg’中间有两位任意字符（使用下划线，通过like来实现）

SELECT * FROM cms_user WHERE username LIKE 'l__g';

高级查询

排列

ORDER BY 列名1[ASC|DESC]，列名2,...

可以将查询结果集按一个或多个列的值进行排序

升序：ASC(缺省值)；

降序：DESC 当列中有NULL值时，认为NULL最小。

ASC：排序列为NULL的记录最先显示

DESC：排序列为NULL的记录最后显示

例8-31：查询student表中所有记录，按照zno字段进行排序。

SELECT * FROM student ORDER BY zno ;

限制查询结果显示数量

使用LIMIT 子句。

格式：LIMIT [m,]n 或 n OFFSET m

说明：m表示偏移量，即从第m条记录取，默认第一行为0

n表示要获取的行数

[例] 查询student表中的前3条记录的学生姓名。

SELECT Sname FROM Student LIMIT 0,3;

或： LIMIT 3 OFFSET 0 LIMIT 3

聚合函数

SQL 提供库函数用于对查询结果的指定列进行简单的统计汇总。

函数名称	功能
AVG	计算一列的平均值
SUM	计算一列值的总和
MAX	求一列中的最大值
MIN	求一列中的最小值
COUNT	统计一列中值的个数

1）count函数：计数（非NULL值的个数）

[例] 统计成绩的个数。

SELECT COUNT (Grade) FROM SC

成绩为0的同学也计算在内，成绩为空值NULL的不计算。

COUNT (Distinct CNo)

使用Distinct去掉在CNo列上的重复值后再计数

COUNT（*）

用来统计行数，不允许使用DISTINCT关键字，不消除重复行。

[例3-42] 统计选修了C1课程的学生总数。

SELECT COUNT(*)

WHERE CNo= 'C1’

1.COUNT(列名)

统计结果集中该列上数据的个数包含重复数据，使用DISTINCT消除重复数据不包含空值NULL

2. COUNT(*)

统计结果集中的行数，不允许使用DISTINCT 包含重复行

2）SUM函数、AVG函数

[例]查看S1学生选修各课程的总分和平均分。

SUM(grade) AS 总分,

AVG(grade) AS 平均成绩

FROM SC

WHERE Sno= ‘S1';

注意： SUM和AVG计算时遇到空值则跳过，只处理非空值。只能计算数值类型的字段，INT、FLOAT、DOUBLE、DECIMAL等

3）MAX函数、MIN函数

求一列中最大值、最小值。

不仅仅适用于数值类型，也适用于字符类型。

字符型数据使用ASCII码进行比较。

分组查询

GROUP BY子句：将数据表中各行按指定列的值进行分组，每组在指定列上值相同

连接查询：将两个或两个以上的表按某个条件连接起来，从中选取需要的数据。

连接查询类型：

内连接查询和外连接查询。最常用的是内连接查询。

内连接是在表的笛卡尔积中，保留所有相匹配的数据，而舍弃不匹配的数据。

内连接的语句：Where，Inner Join、Nature Join 外连接的语句：Left Join、Right Join

SQL内连接方法 :

1. WHERE连接：From 表名1，表名2 Where 连接条件

2. JOIN连接：From 表名1 INNER JOIN 表名2 ON 连接条件

1.等值连接

Where连接条件的格式

[表名1].列名1 比较运算符 [表名2].列名2

AND

[表名2].列名22 比较运算符 [表名3].列名3

AND…

引例，查询所有教师的姓名和教授的课程号 Select TN, CNo From T, TC Where T.TNo=TC.TNo

join连接

Join连接可以实现：内连接外连接自然连接

(1) 内连接

按照ON条件连接两个表的所有字段，结果集中为满足连接条件的行，保留重复记录

Select * from table1 inner join table2 on t1=t3 等价于 Select * from table1, table2 where t1=t3

系统默认连接为inner join ，可以省略Inner

Select语句执行过程：

1. join连接

2.where条件筛选

3. Group by 分组列进行分组

4. 选择满足Having 条件的分组

5. Select 选取列并执行函数(每一组分别选取和执行)

6. Order By 排序

7. Limit

三表连接：查询钱尔同学选修的课程名和考试成绩

Select CN, Grade From S join SC on S.SNo=SC.SNo

join Course on SC.CNo= Course.Cno Where SN=‘钱尔'

2.自然连接

自然连接不需要写连接列名，并且会去掉重复列

与等值连接的区别

外连接分为左外连接，右外连接，只能对两个表进行

①左连接：包括满足条件的行和左表中所有行如果右表中没有满足连接条件的记录，则结果集中右表中的相应行数据填充为NULL

Select * from table1 left join table2 on t1=t3

② 右连接：包括满足条件的行和右表中所有行

Select * from table1 right join table2 on t1=t3

补充：自身连接

一个表与自身进行连接, 需为表指定两个别名，引用各列时要使用别名

'在表中同一列的值进行比较时使用自连接

子查询

子查询是将一个查询语句嵌套在另一个查询语句的where条件中。

内层查询语句的查询结果，可以作为外层查询语句提供条件。

内层称为子查询，外层称为父查询。

子查询分类： 1.返回一个值的子查询：=、>、 <、>=、 <=、 <>、 !=、 <=>

2.返回多个值的子查询：[NOT]IN、ANY、ALL

3. EXISTS子查询：返回True或者False

ANY表示任何一个，只要满足内查询语句返回结果中的一个，就可以通过该条件来执行外层查询语句

ALL表示满足所有的条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才能执行外层的查询语句。 >ALL表示大于所有的值，<ALL表示小于所有的值

Exists 子查询 *

(Not) Exists用于判断子查询的结果是否为空

Exists子查询不返回结果集, 只是根据子查询结果集是否为空返回一个Ture或False的结果

当子查询的结果集为空时，父查询的where条件不成立（False），

不为空时where条件成立（True）

Exit语句的执行顺序：先执行父查询，再执行子查询

执行顺序：先执行父查询, 再针对父查询结果集的每一条记录执行一遍子查询，如果子查询不为空，则接受父查询的这条记录

1.执行父查询，取得第一条记录（T1，李力，教授）

2.执行子查询，结果（T1，T3），不为空，Exist返回True

3.父查询结果中的此条记录(T1，李力，教授)作为最终结果的记录输出

4.继续判断父查询的下一条记录（T2，王平，讲师）

5.执行子查询，结果为（T1，T3），Exist返回True

6.记录（T2，王平，讲师）作为最终结果的记录输出

7.继续执行，直到T表中所有记录判断完毕

相关子查询：子查询中引用了父查询中的属性值

[例3-59] 查询选修所有课程的学生姓名。

Select SN From S Where Not Exists

( Select * From C Where Not Exists

( Select * From SC

Where SNO = S.SNo And CNo = C.CNo ) )

MySQL数据操作管理

插入数据

（1）添加一行记录 INSERT INTO 表名 [ ( 列名1 [, 列名2…] ) ] VALUES(值1，值2…)
（1）添加一行记录 insert into 表名 [ ( 列名1 [, 列名2…] ) ] values(值1，值2…)
如果添加所有列，可以省略列名

值与列名一一对应，类型相容

INSERT INTO Student (Sno, Sname, Zno) VALUES ('00109', '王一', ‘1102');

未插入数据的列取NULL或默认值

（2）添加多行记录 insert into 表名 [ ( 列名1 [, 列名2…] ) ] values (值11，值12…)，(值21，值22…)，(值31，值32…)，...

（3）将select查询结果插入表中

格式：INSERT INTO 表名 [( 列名1 [, 列名2 …])] Select 语句

注：SELECT结果集中记录的字段数和字段类型要与目标表完全一致。

例，将S表中年龄超过20岁的学生记录放入表Bigage中,假设Bigage表已经存在

Insert Into Bigage Select * from Student where age>20

使用REPLACE语句插入数据

语法格式：replace [into] 表名 values（值列表）

与INSERT语句的区别

当插入的新记录主键值或者唯一约束列值与已有记录相同，则

REPLACE语句：已有记录被删除后再添加新记录

INSERT语句：报错

示例：向specialty表插入一条数据。 replace specialty values('1807','信息管理')

修改数据

语法格式

update 表名

set 字段名1＝取值1，字段名2＝取值2，… 字段名n＝取值n

[where 条件表达式 ]

功能：在指定表中，将满足where条件记录的指定列值进行修改。

用子查询选择要修改的行

例3-71 把讲授C3课程的教师的工资增加100元。 UPDATE T SET Sal = Sal + 100 WHERE TNo IN( SELECT TNo FROM TC Where CNo = 'C3')

删除数据

删除数据是删除表中已经存在的记录。

delete from 表名 [where条件表达式]

示例：清空学生表 DELETE FROM student;

删除学生’张三’ DELETE FROM student WHERE sname=‘张三’;

使用TRUNCATE 完全清空某一个表，表结构会保留。

truncate [table] 表名

示例：清空course表 TRUNCATE course;

truncate 与delete的区别：

清空数据表时，truncate 比 delete 速度快占用资源少。

truncate，delete，drop放在一起比较：

truncate ：dml，删除内容、释放空间但不删除表的定义。

delete：dml，删除内容、不删除表的定义，不释放空间。

drop：ddl，删除内容和表的定义，释放空间。

查询：单表查询，多表查询

重点：关于查询的语句书写

在specialty表中查询经济管理学院开设的课程名和学分

正确答案：

(1)

select cname,ccredit from specialty where cdept='经济管理学院';select cname,ccredit from specialty where cdept='经济管理学院';

在SC表查询选修C1或C2课程且分数高于85分学生的学号、课程号。

正确答案：

(1) SELECT SNo, CNo FROM SC Where (Cno='C1' Or Cno='C2') AND GRADE>85;SELECT SNo, CNo FROM SC Where (Cno='C1' Or Cno='C2') AND GRADE>85;SELECT SNo, CNo FROM SC Where Cno in('C1','C2') AND GRADE>85;

使用IN关键字在SC表中查询没有选修C1和C2的学生的学号、课程号和成绩。

正确答案：

(1) SELECT SNo,CNo,Grade FROM SC WHERE CNo NOT IN('C1','C2');SELECT SNo,CNo,Grade FROM SC WHERE CNo NOT IN('C1','C2'); SELECT * FROM SC WHERE CNo NOT IN('C1','C2');SELECT * FROM SC WHERE CNo NOT IN('C1','C2');

查询student表中所有14级班级的学生信息

正确答案：

(1) select * from student where sclass like '%14%';select * from student where sclass like '%14%';

查询student表中生日列的值为空的学生姓名

正确答案：

(1) select sname from student where sbirth is null

在SC表中查询考试没有通过的学生人数

正确答案：

(1) Select count(Distinct SNo) from SC where grade<60;Select count(Distinct SNo) from SC where grade<60;

查询C1课程的考试平均分

正确答案：

(1) Select avg(grade) from SC Where CNo='C1';Select avg(grade) from SC Where CNo='C1';

显示学号以及该学生考试的平均成绩

正确答案：

(1) select Sno,AVG(Grade) from sc group by Sno;select Sno,AVG(Grade) from sc group by Sno;

统计各科的课程号和没有参加考试的人数

正确答案：

(1) select Cno,count(*) from sc where grade is null group by Cno;select Cno,count(*) from sc where grade is null group by Cno;select Cno,count(sno) from sc where grade is null group by Cno;select Cno,count(sno) from sc where grade is null group by Cno;

在Student表中查询女学生人数超过2人的班级和女生人数

正确答案：

Select sclass, count(Sno) From Student Where ssex='女'

Group by sclass Having count(Sno)>2

查询信息专业学生的姓名，所选修的课程的编号和考试成绩

正确答案：

Select SN,CNo, Grade

From S , SC

Where S.SNo=SC.SNo And dept=‘信息'

正确答案：

Select SN,CNo, Grade

From S join SC on S.SNo=SC.SNo And dept=‘信息'

或者

Select SN,CNo, Grade

From S join SC on S.SNo=SC.SNo

where dept=‘信息'

查询选修了K001课程的学生人数

正确答案：

(1)

select count(学号) from 学生作业表 where 课程号='K001';

select count(*) from 学生作业表 where 课程号='K001';

select count(distinct 学号) from 学生作业表 where 课程号='K001' ;

查询选修三门以上（含三门）课程的学生的学号和选修课程数量（别名为选课数量），按选课数量降序输出

正确答案：

(1)

select 学号,count(*) as 选课数量 from 学生作业表 group by 学号 having count(*)>=3 order by 2 desc;

select 学号,count(课程号) as 选课数量 from 学生作业表 group by 学号 having count(课程号)>=3 order by 2 desc;

select 学号,count(*) as 选课数量 from 学生作业表 group by 学号 having count(*)>=3 order by count(*) desc;

select 学号,count(课程号) as 选课数量 from 学生作业表 group by 学号 having count(课程号)>=3 order by count(课程号) desc;

select 学号,count(课程号) as 选课数量 from 学生作业表 group by 学号 having count(课程号)>=3 order by 选课数量 desc;

查询所有老师的姓名和所教的课程数量

正确答案：

Select TN, count(CNo) From T left join TC on T.TNo=TC.TNo Group by T.TNo

查询比人民邮电出版社出版的高等数学价格都低的同名书信息

正确答案：

select * from book where bname='高等数学' and bprice< ALL(select bprice from book join publish on

book.pno=publish.pno where pname='人民邮电出版社' and bname='高等数学')

重点：数据表插入删除修改

1.向S表插入新记录(‘S5’,’徐丽娜’,’女’)

Insert into S(Sno, SN, Sex) values (‘S5’,’徐丽娜’,’女’)

2.在S表中删除所有姓“孙”的学生信息

Delete from S where SN like ‘孙%’

3.将所有女同学的年龄增加1岁

Update S set Age=age+1 where sex=‘女’

1.在S表中删除没有选课的学生信息

Delete from S where SNo not in (select SNo from SC)

2.将“赵毅”同学所选课程的考试成绩都改为60

Update SC set Score=60 where SNo = (select Sno from S where SN=‘赵毅’)

上机2

查询各位学生的学号、专业班级和姓名

(1) select 学号,专业班级,姓名 from 学生表;select 学号,专业班级,姓名 from 学生表;SELECT 学号,姓名,专业班级 FROM 学生表

查询课程的全部信息

(1) select * from 课程表;select * from 课程表;SELECT 课程号 , 课程名 , 学分数 , 学时数 , 任课教师 FROM 课程表

查询所有的专业班级

(1) select distinct 专业班级 from 学生表;select distinct 专业班级 from 学生表;

查询学时大于60的课程信息

(1) select * from 课程表 where 学时数>60;select * from 课程表 where 学时数>60;

查询姓张的学生的学号、姓名和专业班级

(1) select 学号,姓名,专业班级 from 学生表 where 姓名 like '张%';select 学号,姓名,专业班级 from 学生表 where 姓名 like '张%';

查询没有作业成绩的学号和课程号

(1) select 学号,课程号 from 学生作业表 where 作业1成绩 is null or 作业2成绩 is null or 作业3成绩 is null;select 学号,课程号 from 学生作业表 where 作业1成绩 is null or 作业2成绩 is null or 作业3成绩 is null;

查询班级为电子04和电子05的学生的姓名。使用IN查询实现。

(1) select 姓名 from 学生表 where 专业班级 in ('电子04','电子05');select 姓名 from 学生表 where 专业班级 in ('电子04','电子05');

查询所有课程的名称和任课教师，查询结果按学时数降序排列

(1) select 课程名,任课教师 from 课程表 order by 学时数 desc;select 课程名,任课教师 from 课程表 order by 学时数 desc;

查询K002课程的作业1成绩最高的3名同学的学号

(1) select 学号 from 学生作业表 where 课程号='K002' order by 作业1成绩 desc limit 3;select 学号 from 学生作业表 where 课程号='K002' order by 作业1成绩 desc limit 3;select 学号 from 学生作业表 where 课程号='K002' order by 作业1成绩 desc limit 3 offset 0;select 学号 from 学生作业表 where 课程号='K002' order by 作业1成绩 desc limit 3 offset 0;select 学号 from 学生作业表 where 课程号='K002' order by 作业1成绩 desc limit 0,3;select 学号 from 学生作业表 where 课程号='K002' order by 作业1成绩 desc limit 0,3;

将teacherinfo表的num字段改名为t_id。

(1) alter table teacherinfo change num t_id int(10);alter table teacherinfo change num t_idint(10);alter table teacherinfo change num t_id;alter table teacherinfo change num t_id;

将 teacherinfo 表的address字段删除。

(1) alter table teacherinfo drop address;alter table teacherinfo drop address;

在 teacherinfo 表中增加名为wages的字段，数据类型为decimal(6,2)。

(1) alter table teacherinfo add wages decimal(6,2);alter table teacherinfo add wages decimal(6,2);

将 teacherinfo 表的name字段的数据类型改为 VARCHAR(30)。

(1)alter table teacherinfo modify name varchar(30);alter table teacherinfo modify name varchar(30);

使用SQL语句在xszy数据库中创建teacherinfo表

create table teacherinfo(

id int(4) not null auto_increment primary key comment '编号',

num char(6) not null comment '教工号',

name varchar(20) not null comment '姓名',

sex varchar(4) not null comment '性别',

birthday date comment '出生日期',

address varchar(50) comment '家庭住址'

)

上机3-4

在XSZY数据中查询学号为0538的学生的作业1总分

(1) select sum(作业1成绩) from 学生作业表 where 学号='0538'

查询选修了K001课程的学生人数，不需要起别名

(1) select count(学号) from 学生作业表 where 课程号='K001';

select count(*) from 学生作业表 where 课程号='K001';

select count(distinct 学号) from 学生作业表 where 课程号='K001' ;

查询数据库中共有多少个班级

(1) select count(distinct 专业班级) from 学生表

查询专业班级名称和各班人数

(1) select 专业班级,count(*) from 学生表 group by 专业班级;

select 专业班级,count(学号) from 学生表 group by 专业班级;

查询选修三门以上（含三门）课程的学生的学号和选修课程数量（别名为选课数量），按选课数量降序输出

(1) select 学号,count(*) as 选课数量 from 学生作业表 group by 学号 having count(*)>=3 order by 2 desc;

select 学号,count(课程号) as 选课数量 from 学生作业表 group by 学号 having count(课程号)>=3 order by 2 desc;

select 学号,count(*) as 选课数量 from 学生作业表 group by 学号 having count(*)>=3 order by count(*) desc;

select 学号,count(课程号) as 选课数量 from 学生作业表 group by 学号 having count(课程号)>=3 order by count(课程号) desc;

select 学号,count(课程号) as 选课数量 from 学生作业表 group by 学号 having count(课程号)>=3 order by 选课数量 desc;

查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分、作业3平均分，输出结果列名（别名）为学号、平均分1、平均分2、平均分3

(1) select 学号, avg(作业1成绩) as 平均分1, avg(作业2成绩) as 平均分2,avg(作业3成绩) as 平均分3 from 学生作业表 group by 学号 having count(*)>=3;

select 学号, avg(作业1成绩) as 平均分1, avg(作业2成绩) as 平均分2,avg(作业3成绩) as 平均分3 from 学生作业表 group by 学号 having count(课程号)>=3

在学生作业表中查询作业1、作业2和作业3的平均成绩都大于等于80分的课程号

(1) select 课程号 from 学生作业表 group by 课程号 having avg(作业1成绩)>=80 and avg(作业2成绩)>=80 and avg(作业3成绩)>=80

3.8 查询计算机图形学课程的所有作业成绩

使用where连接语句( )，使用join连接语句( )

(1) select 作业1成绩,作业2成绩,作业3成绩 from 课程表,学生作业表 where 课程表.课程号=学生作业表.课程号 and 课程名='计算机图形学';

select 作业1成绩,作业2成绩,作业3成绩 from 学生作业表,课程表 where 学生作业表.课程号 = 课程表.课程号 and 课程表.课程名 = "计算机图形学";

(2) select 作业1成绩,作业2成绩,作业3成绩 from 课程表 join 学生作业表 on 课程表.课程号=学生作业表.课程号 and 课程名='计算机图形学';

select 作业1成绩,作业2成绩,作业3成绩 from 课程表 join 学生作业表 on 课程表.课程号=学生作业表.课程号 where 课程名='计算机图形学';

select 作业1成绩,作业2成绩,作业3成绩 from 学生作业表 join 课程表 on 学生作业表.课程号 = 课程表.课程号 where 课程名 = "计算机图形学";

3.9 查询于兰兰的选课信息，列出学号、姓名、课程名，

使用where连接方式实现语句为( )，使用join连接方式实现语句为( )

正确答案：

(1) select 学生表.学号,姓名,课程名 from 学生表,课程表,学生作业表 where 学生表.学号=学生作业表.学号 and 学生作业表.课程号=课程表.课程号 and 姓名='于兰兰';

select 学生表.学号,姓名,课程名 from 学生表,课程表,学生作业表 where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名='于兰兰';

select 学生表.学号,姓名,课程名 from 学生表,学生作业表,课程表 where 学生表.学号=学生作业表.学号 and 学生作业表.课程号=课程表.课程号 and 姓名='于兰兰';

Select 学生作业表.学号,姓名,课程名 From 学生表,学生作业表,课程表 Where 学生表.学号=学生作业表.学号 AND 课程表.课程号=学生作业表.课程号 AND 姓名='于兰兰' ;

(2) select 学生表.学号,姓名,课程名 from 学生表 join 学生作业表 on 学生表.学号=学生作业表.学号 join 课程表 on 课程表.课程号=学生作业表.课程号 where 姓名='于兰兰';

select 学生表.学号,姓名,课程名 from 学生表 join 学生作业表 on 学生表.学号=学生作业表.学号 join 课程表 on 学生作业表.课程号=课程表.课程号 where 姓名='于兰兰';

select 学生表.学号,姓名,课程名 from 学生表 join 学生作业表 on 学生表.学号=学生作业表.学号 join 课程表 on 学生作业表.课程号=课程表.课程号 and 姓名='于兰兰';

select 学生表.学号,姓名,课程名 from 学生表 join 学生作业表 on 学生表.学号=学生作业表.学号 join 课程表 on 课程表.课程号=学生作业表.课程号 and 姓名='于兰兰';

Select 学生作业表.学号,姓名,课程名 From 学生表,学生作业表,课程表 Where 学生表.学号=学生作业表.学号 AND 课程表.课程号=学生作业表.课程号 AND 姓名='于兰兰' ;

查询K002课程中作业1成绩在70~80分之间的学生学号（使用between and）

(1) select 学号 from 学生作业表 where 课程号='K002' and 作业1成绩 between 70 and 80;

select 学号 from 学生作业表 where 作业1成绩 between 70 and 80 and 课程号='K002'

查询tb_book表中userno字段的记录,并去除重复值的是( A)

A. SELECT DISTINCT userno FROM tb_book;

B. SELECT userno DISTINCT FROM tb_book;

C. SELECT DISTINCT(userno) FROM tb_book;

D. SELECT userno FROM DISTINCT tb_book;

查询tb001数据表中的前5条记录,并升序排列,语法格式是( A)。

A. SELECT * FROM tb001 ORDER BY id ASC LIMIT 0,5;

B. SELECT * FROM tb001 ORDER BY id DESC LIMIT 0,5;

C. SELECT * FROM tb001 ORDER BY id GROUP BY LIMIT 0,5;

D. SELECT * FROM tb001 ORDER BY id ORDER LIMTT 0,5;

3. 在SOL语言中,条件"BETWEEN 20 AND 30"表示年龄在20到30之间,且(A

A. 包括20岁和30岁

B. 不包括20岁和30岁

C. 包括20岁,不包括30岁

D. 不包括20岁,包括30岁

4. SQL语言中,删除EMP表中全部数据的命令正确的是( C)。

A. delete * from emp;

B. drop table emp;

C. truncate table emp;

D. 没有正确答案

5. 下面正确表示Employees表中有多少非NULL的Region列的SQL语句是(B )

A. SELECT COUNT(*) FROM Employees

B. SELECT COUNT(ALL Region) FROM Employees

C. SELECT COUNT(DISTINCT Region) FROM Employees

D. SELECT SUM(ALL Region) FROM Employees

6. 数据库管理系统中负责数据模式定义的语言是( A)。

A. 数据定义语言DDL

B. 数据管理语言 DMAL

C. 数据操纵语言 DML

D. 数据控制语言DCL

7. 下面可以通过聚合函数的结果来过滤查询结果集的SQL子句是(C )

A. WHERE子句

B. GROUP BY子句

C. HAVING子句

D. ORDER BY子句

8. 以下关于SELECT语句描述错误的是( C)。

A. SELECT语句用于查询一个表或多个表的数据

B. SELECT语句属于数据操作语言(DML)

C. SELECT语句的列必须是基于表的列的

D. SELECT语句表示数据库中一组特定的数据记录

9.在语句SELECT * FROM student WHERE sname LIKE ‘%晓%’; 其中WHERE关键字表示的含义是( A)。

A. 条件

B. 在哪里

C. 模糊查询

D. 逻辑运算

10. 若要求查找S表中,姓名的第一个字为王的学生学号和姓名。下面列出的SOL中正确的是(B )。

A. select Sno, SNAME from S WHERE SNAME=’王%’

B. select Sno, SNAME from S WHERE SNAME LIKE ’王%’

C. select Sno, SNAME from S WHERE SNAME LIKE ’王__’

D. 全部正确

11.若要求“查询选修了3门以上课程的学生的学生号”,正确的SQL语句是(B )。

A. SELECT Sno FROM SC GROUP BY Sno WHERE COUNT(*)>3

B. SELECT Sno FROM SC GROUP BY Sno HAVING (COUNT(*)>3)

C. SELECT Sno FROM SC ORDER BY Sno WHERE COUNT(*)>3

D. SELECT Sno FROM SC ORDER BY Sno HAVING COUNT(*)>=3

4.1 填空,每空填一个词或者一个表达式

查询与张志国同一班级的学生信息，使用的语句为

select * from ( ) where ( ) ( )

(select ( ) from ( ) where 姓名='张志国')

(1) 学生表

(2) 专业班级

(3) =;IN;=any

(4) 专业班级

(5) 学生表

填空,每空填一个词或者一个表达式：

查询比计算机应用基础学时多的课程号和课程名（使用自连接查询实现）

select ( ), ( ) from 课程表 as x,课程表 as y where ( ) and ( )='计算机应用基础'

(1) x.课程号;Y.课程号;

(2) x.课程名;y.课程名;

(3) x.学时数>y.学时数

(4) y.课程名;x.课程名;

查询没有选修K001和M001的学生的学号、姓名（使用子查询NOT IN）

select 学生表.学号,姓名 from 学生表 where 学生表.学号 not in (select 学号 from 学生作业表 where 课程号 in ('K001','M001')) ;

select 学生表.学号,姓名 from 学生表 where 学生表.学号 not in (select distinct 学号 from 学生作业表 where 课程号 in ('K001','M001'));

select 学号,姓名 from 学生表 where 学号 not in (select distinct 学号 from 学生作业表 where 课程号 in ('K001','M001'));select 学号,姓名 from 学生表 where 学号 not in (select 学号 from 学生作业表 where 课程号 in ('K001','M001')) ;

select 学生表.学号,姓名 from 学生表 where 学生表.学号 not in (select distinct 学号 from 学生作业表 where 课程号 in ('K001','M001'));

select 学号,姓名 from 学生表 where 学号 not in (select distinct 学号 from 学生作业表 where 课程号 ='K001' or 课程号='M001'));select 学号,姓名 from 学生表 where 学号 not in (select 学号 from 学生作业表 where 课程号='K001' or 课程号='M001') ;

在学生表删除所有姓张的学生信息

delete from 学生表 where 姓名 like '张%'

16. 向学生表插入新记录('0433','张艳','女','生物04','1986-09-13')

(1) insert into 学生表(学号,姓名,性别,专业班级,出生日期) values('0433','张艳','女','生物04','1986-09-13')

17.将所有学生课程K001的作业1成绩加5分

(1) update 学生作业表 set 作业1成绩=作业1成绩+5 where 课程号='K001'

18. 在课程表中删除没有布置过作业的课程（提示：删除语句中的子查询使用not in）

(1) delete from 课程表 where 课程号 not in(select 课程号 from 学生作业表);delete from 课程表 where 课程号 not in(select distinct 课程号 from 学生作业表)

19. 先创建MAN表，再将所有男同学的学号和姓名插入到MAN表，只写插入语句

(1) INSERT into MAN select 学号,姓名 from 学生表 where 性别='男';INSERT into MAN（学号，姓名） select 学号,姓名 from 学生表 where 性别='男'

20. 将王丽丽同学所有课程的作业1成绩都改为95

(1) update 学生作业表 set 作业1成绩=95 where 学号=(select 学号 from 学生表 where 姓名='王丽丽');update 学生作业表 set 作业1成绩=95 where 学号 in (select 学号 from 学生表 where 姓名='王丽丽');

期末考试以及总结

试卷

1. (单选题, 2分)商品和顾客两个实体集之间联系一般是( D)

A. 多对一

B. 一对一

C. 一对多

D. 多对多

2. (单选题, 2分)一个数据库系统的外模式(B )

A. 至少两个

B. 可以有多个

C. 最多只能有一个

D. 只能有一个

3. (单选题, 2分)关系数据库管理系统能实现的专门关系运算包括( B)

A. 排序、索引、统计

B. 选取、投影、连接

C. 关联、更新、排序

D. 显示、打印、制表

4. (单选题, 2分)规范化过程主要为消除数据库逻辑结构中的插入异常、删除异常以及(D )

A. 更新异常(答案)

B. 数据丢失

C. 结构不合理

D. 数据冗余

5. (单选题, 2分)下列说法中错误的是(C )。

A. 一个关系模式是关系的“型”,可表示为:关系名(属性1,属性2,…,属性n)

B. 关系模型的数据操作是集合操作,操作对象和操作结果都是关系

C. 实体直接的联系由1对多和多对多组成。

D. 一个关系中只能有一个主键

6. (单选题, 2分)创建数据表时,如果给某个字段定义PRIMARY KEY约束时,该字段的数据(A )。

A. 不允许有空值

B. 允许有多个空值

C. 允许有一个空值

D. 以上都不对

7. (单选题, 2分)下列SQL语句中,( B)不是数据操纵语句。

A. INSERT

B. CREATE

C. DELETE

D. UPDATE

8. (单选题, 2分)外键约束必须在(B )中定义。

A. 主表

B. 从表

C. 主表或者从表

D. 以上都不对

9. (单选题, 2分)关系模式中，满足2NF的模式（ B）。

A. 可能是1NF

B. 必定是1NF

C. 必定是3NF

D. 必定是BCNF

二. 填空题（共7题，52分）

10. (填空题, 7.4分)

C1. 在数据库中创建图书借阅数据表BOOK，包括属性为：图书编号BNO（定长字符串，长度为5，主键），图书名BNAME（非空），价格BPRICE(数字，最大长度为5,带1位小数)，借书人编号LNO(外键，关联到S表的SNO列)。

( ) BOOK

( BNO （）（）,

  BNAME varchar(40) （）,

  BPRICE （）,

  LNO  char(2) （）

  )

我的答案：

(1) create table

(2) char(5)

(3) primary key

(4) not null

(5) decimal(5,1)

(6) references S(SNO)

11. (填空题, 7.4分)

C4. 查询有成绩为空的学生学号和课程号

select sno,Cno from SC where ( )

我的答案：

(1) score is null

12. (填空题, 7.4分)

C5. 查询所有职称(prof)是讲师的教师岗位津贴(Comm)的平均值

select ( ) from T ( )

我的答案：

(1) avg(Comm)

(2) where prof='讲师'

13. (填空题, 7.4分)

C6.查询每个年龄的学生人数

select （） from S （）

我的答案：

(1) count(SNo)

(2) group by Age

14. (填空题, 7.4分)C7. 查询'赵亦'同学选修的课程号和成绩（使用join连接查询）

select CNO,score from ( ) on ( ) where ( )

我的答案：

(1) S join SC

(2) S.SNo=SC.SNo

(3) SN='赵亦'

15. (填空题, 7.4分)

C8. 查询所有讲授C1课程的教师的姓名和月收入（使用子查询，月收入=工资sal+岗位津贴comm）

select tn,( ) as 月收入 from T where Tno ( ) (select tno from TC ( ) )

我的答案：

(1) sal+comm

(2) in

(3) where CNo='C1'

16. (填空题, 7.6分)

C9. 查询平均成绩高于80分的学生的姓名和平均分

select SN,（） as 平均分 from S join SC on S.SNo=SC.SNo group by SC.SNo （）

我的答案：

(1) avg(score)

(2)having avg(score)>80

三. 简答题（共3题，18分）

17. (简答题, 6分)

写关系代数表达式（注意：不是SQL语句）

已知有教师表T（编号，姓名，年龄，职称，院系编号），院系表D（院系编号，院系名）。查询‘计算机学院’职称为‘讲师’的教师姓名

我的答案：

∏姓名（σ院系名='计算机学院'∧职称='讲师'(T∞D)）

18. (简答题, 6分)设有关系模式R（A，B，C，D，M，N），函数依赖集F=｛N→D，M→D，D→B，BC→D，DC→N｝，求R的候选码

我的答案：

19. (简答题, 6分)函数依赖集F=｛A→B，B→C，C→A，C→B，A→C，BC→A｝，求它的最小函数依赖集

我的答案：

四. 论述题（共1题，12分）

20. (论述题, 12分)

一个医院管理系统包括如下信息：

医生：编号、姓名、年龄、性别、学历、职称

病人：病历号、姓名、年龄、联系方式、地址

科室：科室编号、科室名、科室电话

关系为：一个科室有多名医生，一个医生只能属于一个科室。一个医生可以为多个病人做诊断，一个病人可以找多名医生诊断，医生在诊断时需要做诊断记录，包括看病的时间、症状、诊断结果。

请根据以上要求完成

（1）画出E-R图(6分)

（2）将E-R图转换为关系模式，并指出各关系模式的主键(6分)

我的答案：

鹤早早

关注

4
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
数据库笔记（2）

创建数据库：CREATE DATABASE mydb;切换（使用）数据库：USE mydb;查询数据库：SHOW DATABASES;查看数据库定义：SHOW CREATE DATABASE mydb;查看当前打开数据库名称：SELECT DATABASE();或SELECT SCHEMA();删除数据库：DROP DATABASE mydb;查看上一步操作产生的警告信息:SHOW WARNINGS;创建数据库：create database mydb;切换（使用）数据库：use mydb。
复制链接

扫一扫