MySql数据库入门

本文包括了MySql数据库SQL语言学习和数据库考试复习笔记两部分

MySql数据库SQL语言学习

Create命令

#创建一个数据库
create database [database_name]; 

#创建一个表
create table [table_name] (
    <列名> <数据类型>[ <列级完整性约束条件> ]
 [，<列名> <数据类型>[ <列级完整性约束条件>] ] …
 [，<表级完整性约束条件> ] 
); 

#创建一个视图
create view <视图名>  [(<列名>  [，<列名>]…)]
as
select [查询内容]
[WITH  CHECK  OPTION]；
/*
子查询不允许含有 order by 和 distinct 语句。
视图只是一个表结构，不储存数据。
使用视图和使用表的语法基本相同。
WITH CHECK OPTION 语句使得插入操作只能插入满足视图的限制条件的元组。
*/

#创建登录用户 
create user [user_name] identified by [user_password]
#创建一个普通用户

#创建数据库角色
create role 'R1';

Grant 命令

#授予某个用户权限
Grant <权限>[,<权限>]...
[on <对象类型> <对象名>] #对象可以是一个表或者是一个视图，或者是一个数据库
to <用户>[,<用户>]
with grant option.

#with grant option子句表示该权限可以进行传播，否则不能。
#grant权限时可以用all表示所有的权限，或者也可以是select等具体的权限。
#在进行授权的时候可以具体到某一个列比如说: Grant update(cname) on ...
#to的后面也可是一个角色。

#将某个角色授予某个用户
Grant 'role_name' to [user_name];
#这样可以批量修改一类用户的权限。

Revoke命令

#收回某个用户的权限
Revoke <权限>[,<权限>]...
[on<对象类型><对象名>]
From <用户>[,<用户>]...

#Revoke权限时可以用all表示收回所有的权限，或者也可以是select等具体的权限。
#From的后面也可是一个角色。

Alter命令

#Alter命令是在建好表后修改表结构时使用的语句。
alter table [table_name] 
add [column_name] [数据类型] [完整性约束] 
#在表中添加一个新列
alter table [table_name] 
add foreign key([column_name]) references [table_name]([column_name])
#在表中添加一外键
alter table [table_name] 
modify column [column_name] <数据类型> [完整性约束]；
#修改表中的一列
alter table [table_name] drop column [column_name]
#删除表中的一列
alter table [table_name] drop primary key;
#删除表的主键
alter table [table_name] drop foreign key [key_name];
#删除表的一个外键

Drop命令

#Drop命令是删除表或者数据库结构时使用的语句。
drop database [database_name]; # 删除一个数据库
drop table [table_name]; # 删除一个数据库中的表
#注意：如果有外键，不能删除表，应该先删除外键或者先删除外键连接的表。

#删除登录用户
drop user [user_name]

---

Insert命令

#插入一条元组
insert into [table_name]([column_name1], [column_name2] ...)
values([column_name1_value], [column_name2_value] ...);
#如果table_name后面属性列为空，则默认为定义表时的属性和顺序。
#在table_name后面的属性可以自定义顺序。
#插入的属性值可以是default, null或者具体的值。

#插入子查询的结果
insert into [table_name]([column_name1], [column_name2] ...)
select ...
from ...
where ...

#插入多条元组
insert into [table_name]([column_name1], [column_name2] ...)
values([column_name1_value], [column_name2_value] ...),
([column_name1_value], [column_name2_value] ...),
([column_name1_value], [column_name2_value] ...),
... ;

Update命令

#带子查询的修改语句（修改满足条件的元组）
update [table_name] set [column_name1] = [column_name1_value]，[column_name2] = [column_name2_value]，
where [column_name] = [column_name_value]
#在修改值时注意实体完整性和参照完整性。
#主码不允许修改，用户定义的完整性，NOT NULL约束，UNIQUE约束，值域约束

Select命令

#一个查询语句的基本结构
select [all/distinct]  
<目标列表达式1>, <目标列表达式2>,  ... 
from <table_name1>, <table_name2>, ...
where <条件表达式>
group by <column_name1>	having <条件表达式>
order by <column_name1> [asc/desc]	limit (num)

/*选择表中的若干列*/
#如果选中所有的属性列那么用*来表示
#DISTINCT关键词消除取值重复的行，如果没有指定，则缺省为ALL 
#如果要将属性列更名的话在需要更名的列后面加上 as 新名字
#在查询语句中还可以自定义表名，方法为 from [table_name] as [new_name]
#在定义别名的时候可以省略 as ，但是不建议。
#目标列表达式可以为：
/*
算术表达式	2016 - Sage
字符串常量	'出生年份：'
函数	year(curdate()) //表示当前的年份
列别名 sno as 学号
*/

/*
常用的查询条件:
比较运算符：=，>，<，>=，<=，!=，<>，!>，!<；
确定范围：
	BETWEEN <..> AND <..>，
	NOT BETWEEN <..> AND <..>
确定集合:
	IN(...)，
	NOT IN(...)
字符匹配：
	LIKE ''，
	NOT LIKE ''
通配符：
	% 任意长度的字符
	_ 一个长度的字符
	/ 将后面紧跟的一个符号转义成正常符号
空值：
	IS NULL，
	IS NOT NULL
多重条件（逻辑运算）:
	AND，OR，NOT	
	可以用括号改变优先级
*/

/*ORDER BY子句*/
#可以按照一个或者多个属性进行排序
#升序：ASC；降序：DESC；缺省值为升序
#当排序列含空值时
#ASC：排序列为空值的元组最后显示
#DESC：排序列为空值的元组最先显示 
#e.g.	ORDER BY Grade DESC；
#e.g.	ORDER BY Sdept，Sage DESC； 
#limit后面跟的数字为输出表中的前几行。

/*自然连接*/
SELECT  Student.*，SC.*
FROM    Student，SC
WHERE   Student.Sno = SC.Sno；
#将student和sc表将sno相同的元组自然连接然后输出

/*聚集函数*/
#聚集函数：
/*
计数
	COUNT（[ DISTINCT | ALL ] *）
	COUNT（[ DISTINCT | ALL ] <列名>）//这两个值在all条件下是相等的
计算总和
	SUM（[ DISTINCT | ALL ] <列名>）	
计算平均值
	AVG（[ DISTINCT | ALL ] <列名>）
最大最小值
	MAX（[ DISTINCT | ALL ] <列名>）
	MIN（[ DISTINCT | ALL ] <列名>）
*/

/*GROUP BY子句*/
#细化聚集函数的作用对象
/*
 未对查询结果分组，聚集函数将作用于整个查询结果
 对查询结果分组后，聚集函数将分别作用于每个组 
 作用对象是查询的中间结果表
 按指定的一列或多列值分组，值相等的为一组
*/

/*Having子句*/
#Having短语作用于组，从中选择满足条件的组
#Where子句作用于基表或者视图，从中选择满足条件的元组

/*ORDER BY子句*/
#将结果进行排序之后再输出
/*
ASC 升序（小的在前面）
DESC 降序（大的在前面）
缺省为升序
order by 后面可以跟多个列名，按照顺序进行排序。
*/
#当排序存在空值时
/*
ASC 空值的元组最后显示
DESC 空值的元组最先显示
*/

/*limit子句*/
#只输出排序结果中的前num个元组。
#limit 3 的意思是输出前三行，limit 1,3 的意思是输出2道4行
#表中的元组的序号是从0开始的

/*Percent子句（！该知识点没有细讲，考试不考）*/
/*
select top <n> percent ...
from ...
where ...
表示输出前半分之n的数据，如果结果不够一条的话会向上取整为一条。
*/

/*into子句（！该知识点没有细讲，考试不考）*/
/*
select * into [table_name] 
from ...
where ...
表示将查询结果保存到table_name的表中。
*/

Select查询进阶

/*连接查询*/
#将两个表进行自然连接
select *
from A, b
where A.a = B.a;
#将三个表进行自然连接
select *
from A, B, C
where A.a = B.a and A.a = C.a;
#将一个表进行自身连接
select A.a, A.pa, B.pa
from table1 as A, table1 as B
where A.pa = B.a;
/*
[例35]查询每一门课的间接先修课（即先修课的先修课），输出：课程号、先修课的课程号、先修课的先修课的课程号。
*/
/*
SELECT  FIRST.Cno, FIRST.Cpno, SECOND.Cpno
FROM  Course As FIRST，Course As SECOND
WHERE FIRST.Cpno = SECOND.Cno；
*/
#两个表用inner join来写自然连接
select *
from A inner join B on (A.a = A.b)
where ...
#两个表用left outer join来写左外连接
select *
from A left outer join B on(A.a = B.a)
where ...
#两个表用right outer join来写右外连接
select *
from A right outer join B on(A.a = B.a)
where ...
#嵌套查询
/*将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询*/
#不相关子查询
select *
from A 
where a in (
    select a
    from B
    where ...
);
#相关子查询
select *
from A 
where a in (
    select a
    from B
    where A.b = B.b and ... 
);
/*可以进行多层嵌套，当可以确定内层返回为单值的话，in可以用=或者<，>等代替。*/
#带有ANY和ALL谓词的子查询
select *
from A
where a >= ANY(
    select a
    from B
    where ...
);
/*ANY表示查询结果中的某个值，ALL表示查询结果中的所有值*/
#带有EXISTS谓词的子查询
/*存在谓词谓词的查询不返回任何数据，只产生逻辑真值或者逻辑假值，若内层查询为空则返回假，若内层查询为非空则返回为真。
EXISTS内层只要有结果，外层对应的记录就输出。
NOT EXISTS内层只要没有结果，外层就被输出。
*/
select *
from A
where exists (
    select *
    from B
    where ...
)
#集合查询：交并差查询
#Union, Intersect, Except。
/**/

Delete命令

#带查询的删除（删除满足条件的元组）
delete 
from [table_name]
where ...
#where中的语句格式和select的where一样。

其他常用命令

show databases;
select database();
use [database_name];
DESC [table_name];#查看该表的结构
select user();#查看当前用户
select * from mysql.user #查看所有用户
#用户授权

数据类型

int
smallint
char([num])
varchar([num])#变长字符串
date
enum(a, b, ...) #枚举类型，值只能是枚举集合中的一个元素

常用约束条件

default [val] # 默认值
unique # 唯一性约束
not null # 非空值约束
primary key([column_name],...) # 主码约束
foreign key([column_name]) references [table_name]([column_name]) # 外键约束
chekc([条件])	# 用户定义
auto_increment # 序号自动加一
constraint [constraint_name] <限制条件> # 为后面的限制条件起一个名字
on delete 
	no action
	cascade
	set null
#在外键被参照的元组的属性进行删除时进行参照的元组的属性值的变化情况
on update 
	no action
	cascade 
	set null
#针对外键被参照的元组的属性进行更新值时进行参照的元组的属性值的变化情况

常用列函数

Count()	#统计某一列的元组数量，某一列的Count运算结果和Count(*)的结果是相等的。
Sum()	
Avg()
Max()
Min()

数据库考试复习笔记

知识点：

三类经典的数据模型是：1.层次模型，2.网状模型和3.关系模型。

数据库的保护措施有1.安全性控制，2.完整性控制，3.并发控制和4.数据库恢复等。

事务的四个性质是：1.原子性，2.一致性，3.隔离性，4.持久性。

并发控制的主要方法是封锁，封锁有两种类型：即1.排它锁（X锁，写锁），2.共享锁（S锁，读锁）。

故障恢复的基本手段有：1.数据转储，2.登记日志文件。

数据库设计的步骤有：1.需求分析，2.概念结构设计，3.逻辑结构设计，4.物理设计，5.数据库实施，6.数据库的运行和维护。

并发控制带来的三种数据不一致是：1.丢失数据，2.不可重复读，3.读脏数据。

数据模型的三要素：1.数据结构，2.数据操作和3.数据完整性。
数据结构是对数据系统的静态特性的描述，数据操作是对数据库系统的动态特性的描述。

数据库系统的故障：1.系统故障，2.事务故障，3.介质故障，4.计算机病毒。

当局部E-R图合成为全局E-R图时，可能出现冲突：1.属性冲突，2.结构冲突，3.命名冲突。

传统的关系运算：1.交，2.并，3.差，4.笛卡尔积。

专门的关系运算：1.选择，2.投影，3.连接，4.除。

基本运算是：1、并，2、差，3、选择，4、投影，5、乘积（笛卡尔积）

数据库的完整性是指：1.正确性，2.相容性 ， 3.有效性。

一个关系模式的定义主要包括：1、关系名，2、属性名、3、属性类型、4、属性长度、5、关键字。

关系数据库中基于数学上两类运算是1、关系代数、2、关系演算。

数据库是长期存储在计算机内，有1.组织的，2.可共享的数据集合。

数据库管理系统的主要功能有1.数据定义功能，2.数据库的组织存储和管理，3.数据操纵功能，4.数据库的运行管理和5.数据库的建立及维护等。

SQL语言具有1、数据定义、2、数据操纵、3、数据控制的作用。

SQL语言具有两种使用方式，分别为1、交互式SQL和2、嵌入式SQL。

参照完整性是指在基本表中，外码可以是1、空值或者2、另一个关系主码的有效值。

数据冗余可能导致的问题有：1、浪费存储空间及修改麻烦，2.潜在的数据不一致性。

安全性控制的一般方法有：1、用户标识鉴定、2、存取控制、3、审计、4、数据加密。

存取权限包括两方面的内容：1、要存取的数据对象，2、对数据对象进行操作的类型。

DBMS存取控制极值主要包括两部分：1、自主存取控制，2、强制存取控制。

在关系数据库规范化理论中，在执行分解时，必须遵守规范化原则：1、保持原有的依赖关系，和2、无损连接性。

易错点：

数据库在磁盘上的基本组织形式是文件。

数据库的概念模型独立于具体的机器和DBMS。

数据库是存储在计算机内有结构的数据的集合。

在数据库中，产生数据不一致的根本原因是：数据冗余。

数据库系统的最大特点是：数据的三级模式和二级映像。

实体与之对应的数据库术语为记录。

层次模型不能直接表示多对多的关系。

数据库奠基人E.F从1970年起发表过多篇论文，主要论述的是关系数据模型。

关系运算中花费时间可能最长的运算是：除

SQL语言是非过程化语言。

SQL是结构化查询语言。

在SQL sever 中删除触发器用 DROP。

关系规范化理论是设计逻辑结构的指南和工具。

对于非规范化的模式，经过使属性域变为简单域转变为1NF，将1NF经过消除非主属性对主关键字的部分依赖转变为2NF，将2NF经过消除非主属性对主关键字的传递依赖转变为3NF。

数据流程图（DFD）是用于描述结构化方法中需求分析阶段的工具。

在数据库设计中，把数据需求携程文档，他是各类数据描述的集合，包括数据项，数据结构，数据流，数据存储和数据加工过程等的表述，通常称为数据字典。

若数据库中质保函成功事务提交的结果，则此数据库就称为处于一致状态。

数据库恢复是将数据库从错误状态恢复到某一已知正确的状态的功能。

数据库系统是利用存储在外村上其他地方的冗余数据来重建被破坏的数据库，他主要有两种：1.后援副本，2、日志文件。

死锁不是并发操作带来的数据不一致性的问题。

事务是用户定义的一个数据库操作序列。

简答：

试述视图的作用？

1. 视图能够简化用户的操作。（1 分）

2. 视图使用户能以多种角度看待同一数据。（1 分）

3. 视图对重构数据库提供了一定程度的逻辑独立性。（1 分）

4. 视图能够对机密数据提供安全保护。（1 分）

登记日志文件时必须遵循什么原则？
登记日志文件时必须遵循两条原则：

1. 登记的次序严格按并发事务执行的时间次序。（1 分）

2. 必须先写日志文件，后写数据库。（2 分）

简述系统故障时的数据库恢复策略：

1. 正像扫描日志文件，找出在故障发生前已经提交的事务，将其事务标识记入 REDO队列。同时找出故障发生时尚未完成的事务，将其事务标识记入 UNDO队列。
2. 对 UNDO队列中的各个事务进行撤销处理。
3. 对REDO队列中的各个事务进行重做处理。(错一个扣1.5分，全错不得分)

简述等值连接与自然连接的区别和联系：

连接运算符是“=”的连接运算称为等值连接。它是从关系 R 与 S 的广 义笛卡尔积中选取 A，B 属性值相等的那些元组。自然连接是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。

说明视图与基本表的区别和联系？

视图是从一个或几个基本表导出的表，它与基本表不同，是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据存放在原来的基本表中，当基本表中的数据发生变化，从视图中查询出的数据也就随之改变。视图一经定义就可以像基本表一样被查询、删除，也可以在一个视图之上再定义新的视图，但是对视图的更新操作有限制

简述事务的特性。

事务具有四个特性，即 ACID 特性：

1. 原子性：事务中包括的所有操作要么都做，要么都不做。

2. 一致性：事务必须使数据库从一个一致性状态变到另一个一致性状态。

3. 隔离性：一个事务内部的操作及使用的数据对并发的其他事务是隔离的。

4. 持续性：事务一旦提交，对数据库的改变是永久的。

试述数据、数据库、数据库管理系统、数据库系统的概念。

数据：描述事物的符号记录。（1 分）
数据库：长期存储在计算机内的、有组织的、可共享的数据集合。（1 分）
数据库管理系统：是位于用户与操作系统之间的具有数据定义、数据操纵、数据库的运行管理、数据库的建立和维护功能的一层数据管理软件。（1 分）
数据库系统：在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员和用户构成。