1. 数据库相关概念
1.1 什么是数据库？
数据库（DataBase，DB）：指长期存储、维护和管理在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。
1.2 什么是数据库管理系统？
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。 用户通过数据库管理系统访问数据库中的数据。
常见的数据库管理系统：
-
Oracle
Oracle数据库被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行 在UNIX、Windows等主流操作系统平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性认证。
-
MySQL
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
-
DB2
DB2是IBM公司的产品，DB2数据库系统采用多进程多线索体系结构，其功能足以满足大中公司的需要，并可灵活地服务于中小型电子商务解决方案。
-
Microsoft SQL Server
SQL Server 是Microsoft公司推出的关系型数据库管理系统。具有使用方便、可伸缩性好、相关软件集成程度高等优点。
1.3 三大范式
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式），越高的范式数据库冗余越小。
这里举例介绍前三种范式，一般的数据库设计满足第三范式即可。
需要提前知道的一些基础定义：
- 码：是可以确定一个元组的所有信息的属性名或属性名组；
- 候选码：即候选码包含码，但是它的真子集中不存在码，候选码可以有多个；
- 主码：即主键，主码是任意一个候选码。
注意：码、候选码和主码的区别？
1.3.1 第一范式（1NF）
1.3.1.1 第一范式的定义
特点：字段不可分，每个字段是原子级别的。
当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
1.3.1.2 举例
例如：有个学生表（学号，姓名，性别，出生年月日），如果认为最后一列还可以再分成（出生年，出生月，出生日），它就不是一范式了，否则就是。
1.3.2 第二范式（2NF）
1.3.2.1 第二范式的定义
特点：有主键，属性完全依赖于主键 [ 在1NF的基础上消除部分依赖]。
如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。第二范式（2NF）是 在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
1.3.2.2 举例
例如：有个表（学号、课程号、姓名、学分），这个表明显说明了两个事务:学生信息, 课程信息。由于非主键字段必须依赖主键，这里学分只依赖课程号，姓名只依赖学号，所以不符合2NF。
这样会导致：
-
数据冗余：每条记录都大概率含有相同信息；
-
删除异常：删除所有学生成绩，就把课程信息全删除了；
-
插入异常：学生未选课，无法记录进数据库；
-
更新异常：调整课程学分，所有行都调整。
应该改为：
- 学生表(学号, 姓名)；
- 课程表(课程号, 学分)；
- 选课表(学号, 课程号, 成绩)。
1.3.3 第三范式（3NF）
1.3.3.1 第三范式的定义
属性不依赖于其它非主属性 [ 在2NF的基础上消除传递依赖 ：A->B->C]。
设R是一个满足第一范式条件的关系模式，X 是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。 满足 第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
2NF和3NF的概念很容易混淆，区分它们的关键点在于：
-
2NF
非主键列是否完全依赖于主键，还是依赖于主键的一部分；
-
3NF
非主键列是直接依赖于主键，还是直接依赖于非主键列。
1.3.2.2 举例
例如：有一张表（学号、所在院系、院系联系电话），存在传递依赖：院系联系电话依赖于所在院系，所在院系依赖于学号。
这样会导致：
-
数据冗余：每条记录都大概率含有相同信息；
-
更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况 。
应该改为：
-
学生表（学号，所在院系号）
-
院系表（院系号，院系联系电话）
2. SQL
2.1 SQL的概念
结构化查询语言（Structure Query Language, SQL），被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。
各数据库厂商都支持ISO的SQL标准（类比于国标），各数据库厂商在标准的基础上做了自己的扩展（类比于方言），SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目、查询内容、更新内容、 删除条目等操作。
SQL中的关键字是不区分大小写的，以分号；结束。
Create, Read, Update, and Delete 通常称为CRUD操作。
2.2 SQL语句的分类
-
数据定义语言（Data Definition Language, DDL）
用来定义数据库对象：库、表、列等。
-
数据操作语言（Data Manipulation Language, DML）
用来定义数据库记录（数据）增删改。
-
数据控制语言（Data Control Language, DCL）
用来定义访问权限和安全级别。
-
数据查询语言（Data Query Language, DQL）
用来查询记录（数据）查询。
2.3 DDL
2.3.1 操作数据库
对数据库对象（库、表、列）进行增删改查的操作。
2.3.1.1 创建数据库
##create database 数据库名
CREATE DATABASE mydb1;
##create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;
##create database 数据库名 set 编码方式 collate 排序规则
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;
编码方式：gb2312、utf-8、gbk、iso-8859-1。
2.3.1.2 查看数据库
show databases; ##查看当前数据库服务器中的所有数据库
show create database 数据库名; ##查看已创建的某个数据库
Select database(); ##查看当前使用的数据库
2.3.1.3 修改数据库
alter database 数据库名 character set 编码方式;
2.3.1.4 删除数据库
drop database 数据库名;
2.3.1.5 切换数据库
use 数据库名;
2.3.2 操作表
2.3.2.1 常用数据类型
| 类型 | 类型说明 | 使用说明 |
|---|---|---|
| double | 浮点型 | 给定长度，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为 999.99；默认支持四舍五入 |
| char | 固定长度字符串类型 | 给定长度，char(10) 'aaa ’ 占10位 |
| varchar | 可变长度字符串类型 | 给定长度，varchar(10) ‘aaa’ 占3位 |
| int | 整型 | 给定长度 |
| text | 字符串类型 | 存储字符串，比如说小说信息 |
| blob | 字节类型 | 存储二进制文件（视频、音频、图像……） |
| date | 日期类型 | yyyy-MM-dd |
| time | 时间类型 | hh:mm:ss |
| timestamp | 时间戳类型 | 会自动赋值，yyyy-MM-dd hh:mm:ss |
| datetime | 日期时间类型 | yyyy-MM-dd hh:mm:ss |
2.3.2.1 创建表
CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);
约束可有可无。点击查看相关约束
2.3.2.2 查看表
SHOW TABLES; ##查看当前数据库中的所有表
desc 表名; ##查看表的字段信息
show create table 表名; ##查看已经创建了的表格的创建细节
2.3.2.3 修改表或表中字段
alter table user rename users; ##改表名
ALTER TABLE user CHARACTER SET gbk; ##改表的编码方式
alter table 表名 add 新列名 新的数据类型; ##增一列
alter table 表名 change（或MODIFY） 旧列名 新列名 新的数据类型; ##修改表中某列
2.3.2.4 删除表或表中字段
alter table 表名 drop 列名; ##删除某一列
drop table 表名; ##删除表
truncate table 表名; ##删除表，然后创建一个与原表的数据结构一致的新表，删除的数据不能被找回，但是速度比delete快
2.4 DML
DML是对表中的数据进行增、删、改的操作。
2.4.1 SQL中的运算符
- 算术运算符: +、-、*、/、%；
- 赋值运算符: = ，赋值方向由右往左赋值；
- 逻辑运算符：and、or、not，在连接多个条件时使用；
- 关系运算符： >、<、>=、<=、!=、=、<>(不等于)
2.4.2 增加insert
- 列名要和列值一一对应；如果是给所有列添加数据的时候，添加数据的时候可以将列名省略，但是顺序要和表中字段顺序一致；
- 在mysql中，非数值的列值两侧需要加单引号（字符串类型和日期类型都要用单引号括起来），如果不想给值写null；
- 可以同时添加多行，用逗号隔开。
insert into 表名(列名) values(数据值);
2.4.3 更新update
UPDATE 表名 SET 列名1=列值1,列名2=列值2 ... WHERE 条件（列名=值）;
2.4.4 删除delete
DELETE from 表名 WHERE 列名=值; ##删除表中所有或部分数据，表结构还在，删除的数据也还可以找回（rollback）
2.5 DCL
定义访问权限和安全级别。
2.5.1 创建用户
如果是本地用户就使用localhost，不然就使用登陆所在地的ip地址，只有指定ip地址可以登录；如果想任意ip地址都可以登录，则用 '% '。
create user 用户名@指定ip地址 identified by 密码; ##只用指定的ip地址才可以登录
create user 用户名@'%' identified by 密码; ##任何ip地址都可以登录
2.5.2 用户授权
-
给指定用户授予指定数据库的指定权限；
grant 权限1,权限2,........,权限n on 数据库名.* to 用户名@IP; ##例如：将test数据下所有表的删、改、查权限，赋予给使用“127.0.0.1”ip地址登录的“test456”用户 grant select,insert,delete,create on test.* to 'test456'@'127.0.0.1'; -
给指定用户授予所有数据库所有权限；
grant all on *.* to 用户名@IP; -
用户权限查询；
show grants for 用户名@IP; -
撤销用户权限;
revoke 权限1,权限2,........,权限n on 数据库名.* from 用户名@IP;
2.5.3 删除用户
drop user 用户名@IP;
2.6 DQL
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端，查询返回的结果集是一张虚拟表。
2.6.1 DQL语法
- 查询语句书写顺序：select –> from -> where -> group by -> having -> order by -> limit
- 查询语句执行顺序：from -> where -> group by -> having -> select -> order by -> limit
2.6.2 查询select
查询所有可以写“*”星号。
select 列名，列名… from 表名 where 条件;
2.6.3 条件查询where
条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用：
- sql运算符，查询是否有满足指定条件的记录；
- in（set集合），查询是否有满足集合中任意条件的记录；
- BETWEEN 开始值 AND 结束值，查询是否满足指定条件范围的记录(开始值<结束值、查询范围包含临界值)；
- IS NULL（IS NOT NULL），查询某个字段是否满足为空（不为空）的记录。
2.6.4 模糊查询like
_(下划线)表示任一字符，%(百分号)表示任意0~n个字符。
举例：
-
查询姓名由3个字构成的学生记录;
SELECT * FROM stu WHERE sname LIKE '___'; -
查询姓名以“z”开头的学生记录；
SELECT * FROM stu WHERE sname LIKE 'z%'; -
查询姓名中第2个字母为“i”的学生记录；
SELECT * FROM stu WHERE sname LIKE '_i%'; -
查询姓名中包含“a”字母的学生记录。
SELECT * FROM stu WHERE sname LIKE '%a%';
2.6.5 字段控制查询
-
去除重复记录；
SELECT DISTINCT class FROM emp; -
查询时运算(横向运算)；
- 因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。
- 假定comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL。
SELECT *,sal+comm FROM emp; SELECT *,sal+IFNULL(comm,0) FROM emp; -
给列名起别名；
给列起别名时，是可以省略AS关键字的。
SELECT *, sal+IFNULL(comm,0) AS total FROM emp; SELECT *, sal+IFNULL(comm,0) total FROM emp;
2.6.6 排序order by
order by 列名 asc升序/desc降序，默认是升序的，null值被认为是最小值。
多列排序，例如：查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序。
SELECT * FROM emp ORDER BY sal DESC,empno ASC;
2.6.7 聚合函数（纵向运算）
-
COUNT(列名)
统计指定列不为NULL的记录行数，也可以通过”*“统计元组个数；
SELECT COUNT(comm) cnt FROM emp; SELECT COUNT(*) AS cnt FROM emp; -
SUM和AVG
- SUM(列名)：计算指定列的数值和；
- AVG(列名)：计算指定列的平均值。
如果指定列类型不是数值类型，那么计算结果为0。
SELECT SUM(sal) FROM emp; SELECT AVG(sal) FROM emp; -
MAX和MIN
- MAX(列名)：计算指定列的最大值；
- MIN(列名)：计算指定列的最小值。
如果指定列是字符串类型，那么使用字符串排序运算。
SELECT MAX(sal), MIN(sal) FROM emp;
2.6.8 分组查询group by
当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要通过部门来分组。
如果查询语句中有分组操作（多个分组条件顺序执行），则select后面能添加的只能是聚合函数和被分组的列名。
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
多列分组：统计出stu表中每个班级的男女生各多少人（先根据班级分组，然后根据男女分组）。
select gradename,gender ,count(*) from stu group by gradename,gender
2.6.9 having语句
##查询工资总和大于9000的部门编号以及工资和
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;
2.6.10 having和where的区别
- having是在分组后对数据进行过滤；where是在分组前对数据进行过滤。
- having后面可以使用分组函数(统计函数)；where后面不可以使用分组函数。
2.6.11 limit
LIMIT用来限定查询结果的起始行，以及总行数。
2.6.11.1 limit语法
-
limit 开始下标,显示条数；//开始下标从0开始。
SELECT * FROM emp LIMIT 0, 5; SELECT * FROM emp LIMIT 3, 10; -
limit 显示条数；//表示默认从0开始获取数据。
SELECT* FROM emp LIMIT 10;
2.6.11.2 分页查询
pageIndex 页码值 （数据库中数据从0开始）、pageSize 每页显示条数。
SELECT* FROM emp limit (pageindex-1)*pagesize, pagesize;
8324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
