学习1：基本概念

1.数据库

数据库：长期存储在计算机内、有组织、可共享的大量的数据的集合。数据库中的数据按照一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。特点：永久存储、有组织、可共享。

数据库管理系统(DBMS)：位于用户与操作系统之间的一层数据管理软件。主要功能：数据定义功能（DDL）；数据组织、存储和管理；数据操纵功能（DML）；数据库的事务管理和运行管理；数据库的建立和维护功能；其他功能。

数据库系统（DBS）:由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员构成。

2.数据模型

两大数据模型

1. 概念模型：按用户的观点来对数据来对数据和信息建模，主要用于数据库设计。

2. 逻辑模型：主要包括层次模型、网状模型、关系模型，它是按照计算机系统的观点对数据建模，主要用于DBMS的实现。

数据模型的组成要素：数据结构、数据操作、完整性约束

1. 数据结构：数据库组成对象以及对象之间联系的描述。

2. 数据操作：对数据库中各种对象的实例允许执行的操作的集合。

3. 完整性约束：数据模型要满足的约束条件。包括实体完整性、参照完整性、用户定义完整性。

3.数据库语言四大类

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。

DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。

DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。

DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。

数据查询语言（DQL）：是由SELECT子句，FROM子句，WHERE子句组成的查询块

数据操纵语言（DML）:SELECT(查询) INSERT(插入) UPDATE(更新) DELETE(删除）

数据定义语言（DDL）：CREATE(创建数据库或表或索引）ALTER(修改表或者数据库）DROP(删除表或索引）

数据控制语言（DCL）：GRANT(赋予用户权限）REVOKE(收回权限）DENY(禁止权限)

事务控制语言（TCL）：SAVEPOINT (设置保存点）ROLLBACK (回滚) COMMIT(提交)

4.完整性

主键：能够唯一地标识一个元组的属性或属性组称为关系的键或候选键。若一个关系有多个候选键则可选其一作为主键(Primary key)。

外键：如果一个关系的一个或一组属性引用(参照)了另一个关系的主键，则称这个或这组属性为外码或外键(Foreign key)。

关系数据库：依照关系模型建立的数据库称为关系数据库。它是在某个应用领域的所有关系的集合。

关系的完整性约束条件包括三大类：实体完整性、参照完整性、和用户自定义完整性。

实体完整性：用于标识实体的唯一性。它要求基本关系必须要有一个能够标识元组唯一性的主键，主键不能为空，也不可取重复值。

参照完整性：用于维护实体之间的引用关系。它要求一个关系的外键要么为空，要么取与被参照关系对应的主键值，即外键值必须是主键中已存在的值。

用户定义的完整性：就是针对某一具体应用的数据必须满足的语义约束。包括非空、唯一和布尔条件约束三种情况。

5.关系数据库标准语言SQL

SQL：结构化查询语言的简称，是关系数据库的标准语言。SQL 是一种通用的、功能极强的关系数据库语言，是对关系数据存取的标准接口，也是不同数据库系统之间互操作的基础。集数据查询、数据操作、数据定义、和数据控制功能于一体。

SQL的组成

1. 数据定义（Data Definition）：用来定义数据库结构，包括定义表、视图和索引。

2. 数据操作（Data Manipulation）：包括数据查询和数据更新两大操作。数据更新包括插入、删除和修改操作。

3. 数据控制（Data Control）：包括对数据的安全性控制、完整性规则的描述以及对事务的定义、并发控制和恢复。

SQL的特点

1. 综合统一：SQL中的DDL、DML、DCL语言风格统一，可以独立完成数据库生命周期中的全部活动。

2. 高度非过程化：SQL进行数据操作，只要提出做什么，不需要指明怎么做。操作过程由系统自动帮助你完成。

3. 面向集合的操作方式

4. 以同一种语法结构提供多种使用方式：既是独立的语言，又是嵌入式语言。

5. 语言简洁、易学易用。

6.三大范式

第一范式：无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：属性完全依赖于主键 [ 消除部分子函数依赖 ]。如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。

第三范式：属性不依赖于其它非主属性 [ 消除传递依赖 ]。设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF. 满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。（eg：第一张表是学生表，主要信息为姓名，次要信息为成绩，那么其他的表中，就不能包含成绩。）

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

7.基础操作

创建关系

DROP TABLE IF EXISTS `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems` (

`quantity` INT(64) NOT NULL COMMENT '数量',

`item_price` INT(64) NOT NULL COMMENT '订单价格'

);

INSERT INTO `OrderItems` VALUES (1,100),

(10,1003),

(2,500);

quantity	item_price
1	100
10	1003
2	500

1：以下关于SQL主键的描述正确的是？

主键一定是唯一性索引，唯一性索引并不一定就是主键

一个表中可以有多个唯一性索引，但只能有一个主键

只允许以表中第一字段建立主键

创建唯一的索引，允许空值

正确答案：A B

2：主键和唯一索引的区别，下面说法正确的是（）

主键值是唯一的，切不能为空。

唯一索引可以为空，可以不是唯一的。

一个表的主键可以有多个，唯一索引只能有一个。

主键只能由一个字段组成，唯一索引可以有多个字段组成。

正确答案：A

B. 唯一性索引列允许空值，而主键列不允许为空值。

C.一个表最多只能创建一个主键，但是可以创建多个唯一索引。

D.联合索引好几个字段联合起来建立的索引；唯一索引业务中唯一的字段适合建立唯一索引

数据库关系的创建：https://www.cnblogs.com/chengzige/p/11385844.html

3：用于事务回滚的 SQL 语句是？（）

正确答案: D 你的答案: 空 (错误)

create table

commit

grant和revoke

rollback

A create table 创建表

B submit 提交

C grant和revoke 授权和撤销授权

D rollback 回滚

4.索引字段值不唯一，应该选择的索引类型为（）

正确答案: B 你的答案: 空 (错误)

主索引

普通索引

候选索引

唯一索引

[知识点] 表的索引：主索引，候选索引，普通索引，唯一索引
[评析] 索引类型分类：
①主索引：主索引是一种只能在数据库表中建立不能在自由表中建立的索引。在指定的字段或表达式中，主索引的关键字绝对不允许有重复值。
②候选索引：和主索引类似，它的值也不允许在指定的字段或表达式中重复。一个表中可以有多个候选索引。
③唯一索引：唯一索引允许关键字取重复的值。当有重复值出现时，索引文件只保存重复值的第1次出现。提供唯一索引主要是为了兼容早期的版本。
④普通索引：普通索引允许关键字段有相同值。在一对多关系的多方，可以使用普通索引。

5.下面哪一个是MySQL查询语句的正确执行顺序：

正确答案: C 你的答案: D (错误)

SELECT ---> FROM(including JOINs) ---> WHERE ---> GROUP BY ---> HAVING ---> DISTINCT ---> ORDER BY ---> LIMIT/OFFSET

SELECT ---> DISTINCT ---> FROM(including JOINs) ---> WHERE ---> GROUP BY ---> HAVING ---> ORDER BY ---> LIMIT/OFFSET

FROM(including JOINs) ---> WHERE ---> GROUP BY ---> HAVING ---> SELECT ---> DISTINCT ---> ORDER BY ---> LIMIT/OFFSET

FROM(including JOINs) ---> WHERE ---> GROUP BY ---> HAVING ---> DISTINCT ---> SELECT ---> ORDER BY ---> LIMIT/OFFSET

SQL语法：

select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤
having 筛选 ⑥
order by排序列表 ⑧
limit 起始条目索引，条目数; ⑨

6.题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

用户信息表：user_profile

30天内活跃天数字段（active_days_within_30）

发帖数量字段（question_cnt）

回答数量字段（answer_cnt）

你的查询返回结果需要对性别和学校分组，示例如下：

#方法一

#select gender,university,count(gender) as user_num, avg(active_days_within_30) as avg_active_day,

#avg(question_cnt) as avg_question_cnt

#from user_profile

#group by gender,university

#方法二

select

gender,university,count(gender) user_num,avg(active_days_within_30) avg_avtive_day,avg(question_cnt) avg_auestion_cnt

from

user_profile

group by

gender,university

7.插入多行数据

1、在已有的表中，插入一行数据：

a、第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：

INSERT INTO table_name VALUES (value1,value2,value3,...);

insert into subject values ('语文')

b、第二种形式需要指定列名及被插入的值：

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

insert into subject(subject_name) values ('数学')

2、在已有的表中，一次性插入多行行数据：

INSERT INTO table_name VALUES (value1,value2,value3,...)，(value1,value2,value3,...);

用逗号隔开，括号括起来，加多少行数据就写多少个。要指定列名插入的，参考1.b 的做法。

insert into subject values ('数学'),('英语')

3、将表1 数据的某些列插入到表2 中去（其中表2是已经创建好，与表1 插入列的属性是一样的）：

INSERT INTO 表2（column1，column2） SELECT （column1，column2）FROM 表1

insert into newtable(StuName) select studentname from student

8.从一个表插入指定条件的数据到另外一个表

示例1

输入：

drop table if EXISTS exam_record;

CREATE TABLE IF NOT EXISTS exam_record (

id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',

uid int NOT NULL COMMENT '用户ID',

exam_id int NOT NULL COMMENT '试卷ID',

start_time datetime NOT NULL COMMENT '开始时间',

submit_time datetime COMMENT '提交时间',

score tinyint COMMENT '得分'

)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS exam_record_before_2021 (

id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',

uid int NOT NULL COMMENT '用户ID',

exam_id int NOT NULL COMMENT '试卷ID',

start_time datetime NOT NULL COMMENT '开始时间',

submit_time datetime COMMENT '提交时间',

score tinyint COMMENT '得分'

)CHARACTER SET utf8 COLLATE utf8_general_ci;

TRUNCATE exam_record;

TRUNCATE exam_record_before_2021;

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES

(1001, 9001, '2020-01-01 09:00:01', null, null),

(1001, 9002, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 70),

(1001, 9002, '2020-09-02 09:00:01', null, null),

(1002, 9001, '2021-05-02 10:01:01', '2021-05-02 10:30:01', 81),

(1002, 9002, '2021-09-02 12:01:01', null, null);

复制

输出：

1|1001|9002|2020-01-02 09:01:01|2020-01-02 09:21:01|70

#方法一

#insert into exam_record_before_2021(uid,exam_id,start_time,submit_time,score)

#select uid,exam_id,start_time,submit_time,score

#from exam_record

#where submit_time < '2021-01-01'

#方法二

#insert into exam_record_before_2021

#select null,uid,exam_id,start_time,submit_time,score

#from exam_record

#where submit_time < '2021-01-01'

#方法三

insert into exam_record_before_2021(uid,exam_id,start_time,submit_time,score)

select uid,exam_id,start_time,submit_time,score

from exam_record

where year(submit_time) < 2021

9.Mysql中表student_table(id,name,birth,sex)，查询不重复的姓名总数，错误的是（C）？

A:select count(distinct name) from student_table ;

B:select count(name) from (

select distinct name from student_table

) t1

C:select count(name) from (

select name,count(*) as c1 from student_table

group by name

having c1 > 1

)t1

D:select count(name) from (

select name from student_table

group by name) t1;

C的结果是求有多少个姓名出现重复，比如表中李四出现2次，其他都是出现1次，只有李四是重复的则结果是1，所以C错误。

去重有2种方式用group by和distinct。

10.Mysql中表student_table(id,name,birth,sex)，插入如下记录：

('1001' , '' , '2000-01-01' , '男');

('1002' , null , '2000-12-21' , '男');

('1003' , NULL , '2000-05-20' , '男');

('1004' , '张三' , '2000-08-06' , '男');

('1005' , '张三' , '2001-12-01' , '女');

('1006' , '李四' , '2001-12-02' , '女');

查询男生女生存在的相同姓名，比如'张三' ，正确的是（）？

A:select distinct t1.name from

(select * from student_table where sex = '女')t1

inner join

(select * from student_table where sex = '男') t2

on t1.name = t2.name ;

B:select distinct t1.name from

(select * from student_table where sex = '女')t1

left join

(select * from student_table where sex = '男') t2

on t1.name = t2.name ;

C:select distinct t1.name from

(select * from student_table where sex = '女')t1

full join

(select * from student_table where sex = '男') t2

on t1.name = t2.name ;

D:select distinct t1.name from

(select * from student_table where sex = '女')t1

right join

(select * from student_table where sex = '男') t2

on t1.name = t2.name ;

正确答案：A

题目解析：

B会把'李四'也查询出来，此时是以t1为主表；

C执行报错，Mysql当前不支持full join；

D结果含有NULL，主表是t2存在NULL，左表t1对应字段值只能是NULL；

所以A正确，用inner join关联是最正确的。

11.Mysql中表student_table(id,name,birth,sex)，插入如下记录：

('1004' , '张三' ,'2000-08-06' , '男');

('1009' , '李四', '2000-01-01', '男');

('1010' , '李四', '2001-01-01', '男');

('1006' , '王五', '2000-08-06' , '女');

('1008' , '张三', '2002-12-01', '女');

('1012' , '张三', '2001-12-01', '女');

('1011' , '李四', '2002-08-06' , '女');

执行

select t1.*,t2.*

from (

select * from student_table where sex = '男' ) t1

left join

(select * from student_table where sex = '女')t2

on t1.name = t2.name ;

的结果行数是（）？

A:4

B:3

C:2

D:5

正确答案：A

题目解析：

on中相同的key匹配上时会产生笛卡尔积！1*2+2*1=4。查询结果如下：

12.下列修改表的列结构的语句中，错误的是（）

ALTER TABLE class MODIFY name VARCHAR(50);

ALTER TABLE student ADD grade INT;

ALTER TABLE place CHANGE name p_name VARCHAR(20);

ALTER TABLE grade ADD average FIRST;

正确答案：D

题目解析：

A选项修改了列的数据类型；选项B添加了新的一列；C选项修改了列名；D选项，在表的最前面添加了新的一列，但是语句缺少列的类型，错误。

选项A :ALTER TABLE class MODIFY name VARCHAR(50);

语法：修改字段数据类型 ALTER TABLE 表名 MODIFY 字段名字段类型；

选项B：ALTER TABLE student ADD grade INT;

语法：添加字段： ALTER TABLE 表名 ADD 新字段字段类型；

选项C：ALTER TABLE place CHANGE name p_name VARCHAR(20);

语法：更新字段： ALTER TABLE 表名 CHANGE 旧字段新字段字段类型；

选项D：ALTER TABLE grade ADD average FIRST;语法错误。

posted @ 2022-07-16 23:07 萧六弟阅读(487) 评论(0) 收藏举报

刷新页面返回顶部

戴小帅的学习笔记

学习1：基本概念

正确答案: D 你的答案: 空 (错误)

正确答案: B 你的答案: 空 (错误)

正确答案: C 你的答案: D (错误)

SQL语法：

示例1

公告