1、什么是数据库

数据库 DataBase 简称db 
数据库管理系统 DataBase Management System 简称DBMS 
sql是非过程化语言
创建时间和更新时间的默认值   CURRENT_TIMESTAMP

2、数据库设计过程阶段

按照规范的设计方法，一个完成的数据库设计一般分为以下六个阶段：
1. 需求分析： 分析用户的需求，包括数据、功能和性能需求；
2. 概念结构设计：主要采用E-R模型进行设计，包括画E-R图；
3. 逻辑结构设计：通过将E-R图转换成表，实现从E-R模型到关系模型的转换，进行关系规范化；
4. 数据库物理设计：主要是为所设计的数据库选择合适的存储结构和存储路径；
5. 数据库的实施：包括编程、测试和试运行；
6. 数据库运行和维护：系统的运行和数据库的日常维护

3、完整性描述

1.实体完整性指表中行的完整性
2.域完整性指列的值域的完整性，如数据类型、格式、值域范围、是否允许空值等等
3.参照完整性基于外键与被引用主键之间的关系，确保键值在所有表中的一致性

4、基本命令

连接数据库语句 : mysql -h 服务器主机地址 -u 用户名 -p 用户密码

mysql -uroot -p123456

update user set password=password('123456')where user='root'; --修改密码 
flush privileges; --刷新数据库 
show databases; --显示所有数据库 
use dbname； --打开某个数据库 
show tables; --显示数据库mysql中所有的表 
describe user; --显示表mysql数据库中user表的列信息 
create database name; --创建数据库 
use databasename; --选择数据库 
exit; 退出Mysql 
sc delete mysql;  清空服务 安装失败的时候使用
? 命令关键词 
: 寻求帮助 
-- 表示注释

5、DDL 数据定义语言

数据库操作

创建数据库 : create database [if not exists] 数据库名;

删除数据库 : drop database [if exists] 数据库名; 可选属性 判断是否存在

查看数据库 : show databases;

使用数据库 : use 数据库名;

数据库的列类型

数值：
	tinyint ：十分小的数值    1字节
	samllint：较小的数值		 2字节
	int：整数				 4字节
	mediumint：中等的数值	 3字节
	bigint：较大的数据		 8字节
	float：浮点数		     4字节
	double：双精度浮点数      8字节
	decimal：字符串形式的浮点数 金融计算的时候一般使用decimal		
	
字符串：
	char：字符串    固定大小   0-255
	varchar：可变字符串        0-65535 -->string
	tinytext：微型文本         2^8-1
	text：文本串               2^16-1
	
时间日期：
	date；YYYY-MM-DD  日期格式
	time：HH:mm:ss    时间
	datetime：YYYY-MM-DD HH:mm:ss 最常用的时间格式
	timestamp：时间戳  1920.1.1到现在的毫秒数
	year：年份表示
	
null：
	没有值，未知

数据库字段类型（属性）

UnSigned：
 - 无符号的 	
 - 声明该数据列不允许负数 .

ZEROFILL:
 - 0填充的
 - 不足位数的用0来填充 , 如int(3),5则为005
 - 
Auto_InCrement(自增):
 - 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)
 - 通常用于设置主键 , 且为整数类型
 - 可定义起始值和步长
 - 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表
 - SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)
 
NULL 和 NOT NULL:
 - 默认为NULL , 即没有插入该列的数值
 - 如果设置为NOT NULL , 则该列必须有值

DEFAULT:
 - 默认的
 - 用于设置默认值
 - 例如,性别字段,默认为”男” , 否则为 “女” ; 若无指定该列的值 , 则默认值为”男”的值

mysql数据表的类型

创建数据库表

SQL语法：

	create table if not exists `student` (
	`id` int (4) not null default comment '学号',
	`name` varchar(30) not null default '匿名‘ comment '姓名',
	`pwd` varchar(20) not null default '123456' comment '密码',
	`sex` varchar(2) not null default '男' comment '性别',
	`birthday` datetime default null comment '出生日期',
	`address` varchar(100) default null comment '家庭住址'，
	primary key(`id`)
) engine=innodb default charset=utf8

格式：

create table [if exists] `表名`(
	'字段名' 列类型 [属性] [索引] [、注释],
	'字段名' 列类型 [属性] [索引] [、注释],
	.....
	'字段名' 列类型 [属性] [索引] [、注释]
)[表类型] [字符集设置] [注释]

查看数据库创建语句：

show create database [数据库名]

查看表创建语句：

show create table [表名]

显示表结构：

desc [表名]

数据库表操作

-- 修改表名: 
ALTER TABLE 旧表名 RENAME AS 新表名

-- 增加表的字段: 
ALTER TABLE 表名 ADD 字段名 列属性

-- 修改表的字段（重命名，修改约束）
ALTER TABLE 表名 MODIFY 字段名 列属性[] -- 修改约束
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[] -- 字段重命名

-- 删除表的字段
ALTER TABLE 表名 DROP 字段名

删除表

-- 删除表（如果表存在再删除）
DROP TABLE [ IF EXISTS ] 表名

所有的创建和删除操作尽量加上判断，以免报错

外键

方式一：

	CREATE TABLE `student` (
`id` int NOT NULL COMMENT '学号',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`gradeid` INT(4) NOT NULL COMMENT '年级',
`sex` varchar(4) NOT NULL DEFAULT '男' COMMENT '性别',
`addr` varchar(50) DEFAULT NULL COMMENT '家庭住址',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES 	`grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE IF EXISTS `grade`(
	`gradeid` INT(4) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式二： 创建表成功后，添加外键约束

CREATE TABLE IF EXISTS `student` (
`id` int NOT NULL COMMENT '学号',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`gradeid` INT(4) NOT NULL COMMENT '年级',
`sex` varchar(4) NOT NULL DEFAULT '男' COMMENT '性别',
`addr` varchar(50) DEFAULT NULL COMMENT '家庭住址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE IF EXISTS `grade`(
	`gradeid` INT(4) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

6、DML数据操作语言

插入数据

INSERT INTO 表名[(字段1,字段2,字段3,…)] VALUES(‘值1’,’值2’,’值3’)

INSERT INTO grade(gradename) VALUES (‘大三’),(‘大四’);

修改数据

UPDATE 表名 SET column_name=value [,column_name2=value2,…] [WHERE condition];

UPDATE grade SET gradename = ‘高中’ WHERE gradeid = 1;

where 查询条件

删除数据

DELETE FROM 表名 [WHERE condition];

— 删除最后一个数据

DELETE FROM grade WHERE gradeid = 2

Truncate命令删除数据

作用：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;

语法：

TRUNCATE [TABLE] table_name; 
-- 清空年级表 
TRUNCATE grade

区别：注意：区别于DELETE命令

相同 : 都能删除数据 , 不删除表结构 , 但TRUNCATE速度更快
不同 :
使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器（清零）
使用TRUNCATE TABLE不会对事务有影响
DELETE 删除表自增不会清零

7、DQL数据查询语言

select语法

SELECT [ALL | DISTINCT] 
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]} 
FROM table_name [as table_alias] 
    [left | right | inner join table_name2] -- 联合查询 
    [WHERE ...] -- 指定结果需满足的条件 
    [GROUP BY ...] -- 指定结果按照哪几个字段来分组 
    [HAVING] -- 过滤分组的记录必须满足的次要条件 
    [ORDER BY ...] -- 指定查询记录按一个或多个条件排序 
    [LIMIT {[offset,]row_count | row_countOFFSET offset}]; 
    -- 指定查询的记录从哪条至哪条

where逻辑操作符

查询多个数据 – 多个列名，用英文逗号隔开

SELECT studentno,studentname FROM student;

统计表中有多少条数据 --count（*）

select count(*) from student

重复数据 去重 --distinct

SELECT DISTINCT `StudentNO` FROM result

模糊查询

模糊搜索查询

与like结合使用的操作符 ("%" 代表匹配0到任意个字符)("_" 匹配一个字符)
查询名字中间带嘉的同学
	select * from student WHERE studentname LIKE '%嘉%';
查询姓刘的同学，名字后只有一个字的
	select * from student where studentname like '刘_'

查询学号为1000，1001，1002学生的成绩

select * from student WHERE studentno IN (1000,1001,1002);

查询学号为在1002-1009之间的学生的成绩

select * from student WHERE studentno BETWEEN 1002 AND 1009;

查询BornDate为空的字段

select * from student WHERE BornDate IS NULL;

as取别名
作用：
· 可给数据列取一个新别名
· 可给表取一个新别名
· 可把经计算或总结的结果用另一个新名称来代替

– 这里是为列取别名(当然as关键词可以省略)

SELECT studentno AS 学号,studentname AS 姓名 FROM student; 

– 使用as也可以为表取别名

SELECT studentno AS 学号,studentname AS 姓名 FROM student AS S;

– 使用as,为查询结果取一个新名字
– CONCAT()函数拼接字符串

SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

连接查询

join

• INNER JOIN 如果表中有至少一个匹配，则返回行
• LEFT JOIN 即使右表中没有匹配，也从左表中返回所有的行
• RIGHT JOIN 即使左表中没有匹配，也从右表中返回所有的行

key值为两个表相连的中间值 相等即为连接

select_list 需要查询的信息：查询条件和中间值

TableA A 给表起别名 as可以省略

分页和排序

排序：升序 ASC, 降序 DESC

语法：ORDER BY 字段 [ASC| DESC]
# 查询的结果根据成绩降序排列
select   studentno,studentname,subjectname，subjectresult from student s 
inner join result r 
on s.studentno = r.studentno
order by subjectresult DESC

分页 limit

语法：limit 起始值，页面大小
第N页 （n-1）* pagesize，pagesize
select   studentno,studentname,subjectname，subjectresult from student s 
inner join result r 
on s.studentno = r.studentno
order by subjectresult DESC
limit 0,10

分组 和 过滤

group by
注意：group by 可以多个字段进行分组，筛选条件使用having
having在group by后面使用

select count(*),字段2 from 表
where 字段 = 值1
group by 字段2  
having count(*) > 10;

子查询

在where语句中嵌套一个语句

从里向外查询(先执行最里面在执行外面）

8、Mysql函数

• 常用函数

  • 数学运算函数

    select ABS(-8) -- 绝对值
    SELECT CTILING(9.4)   --向上取整
    SELECT FlOOR(9.4)   --向下取整
    SELECT RAND()    --取随机数
    SELECT SIGN(10)   --返回一个数的符号 正数返回1 负数返回-1
    

  • 字符串函数

    SELECT CHAR_LENGTH('hello world')   --返回字符串的长度
    SELECT CONCAT('我','你')   -- 拼接字符串
    SELECT INSERT('str',1,2,'str1')    --从第一个字符开始，将str的两个字符替换成str1
    SELECT LOWER('ABC')   --转换成小写
    SELECT UPPER('abc')   --转换成大写
    SELECT INSTR('hello world','w')  --返回第一次出现的字串的索引
    SELECT REPLACE('hello world','world','世界') --替换出现的指定字符串
    SELECT SUBSTR('坚持就能成功',4,3) --返回指定的字符串（源字符串，截取的位置，截取的长度）
    SELECT REVERSE ('坚持就能成功') --反转
    

  • 时间日期函数

    SELECT CURRENT_DATE() --获取当前日期
    SELECT CURDATE() --获取当前日期
    SELECT NOW() --获取当前时间
    SELECT LOCALTIME() --本地时间
    SELECT SYSDATE() --系统时间
    SELECT YEAR(NOW()) --年
    

  • 系统函数

    SELECT SYSTEM_USER() --系统用户
    SELECT USER() --系统用户
    SELECT VERSION() --系统版本
    

• 聚合函数

   COUNT() 计数
   	select count(data) --会忽略所有的null值 data为字段名查询指定列的个数
   	select count(*) --计算行数 所有列都会查
   	select count(1) --计算行数 只会查一列
   SUM() 求和
   	select SUM(data) from 表名
   AVG() 平均值
   MAX() 最大值
   MIN() 最小值
  

• MD5加密函数

增强算法复杂度和不可逆性

加密算法

UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1

插入时加密

INSERT INTO testmd5 VALUES(4,’xiaoming’,MD5(‘123456’))

如何校验 将用户传入数据库的密码 进行MD5加密

SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

9、事务

提交事务commit，回滚事务rollback

四大特性：ACID
A：原子性：事务是最小的工作单元，不可再分
C：一致性：事务必须保证多条DML语句同时成功或同时失败
I：隔离性：事务A与事务B之间具有隔离
D：持久性：最终数据必须持久化到硬盘文件中，事务才算成功结束

隔离性：
第一级别：读未提交（read uncommitted）

	事务A可以读取事务B未提交的数据

第二级别：读已提交（read committed）

	事务读取的数据是实时更新的数据

第三级别：可重复读（repeatable read）

	事务所读取的数据一直都是事务开启时的数据，即使数据被修改了，事务所读取的数据不会发生改变。事务开启读取的数据是备份数据，不能实时读取数据

第四级别：序列化读/串行读

	效率低，事务需要排队

10、索引

主键和具有unique约束的字段自动会添加索引

根据主键查询效率较高，尽量根据主键检索

创建索引：

create index 索引名 on 表名(字段名);
删除索引：

drop index 索引名 on 表名(字段名);
需要加索引的条件：

数据量庞大
该字段很少进行DML操作（字段进行修改操作，索引也需要维护）
该字段经常出现在where语句中（经常根据哪个字段查询）
索引底层采用的数据结构是：B + Tree

​ 通过B Tree 缩小扫描范围，底层索引进行了排序、分区，索引会携带数据在表中的“物理地址，最终通过索引检索到数据之后，获取数据的物理地址，通过物理地址定位到表中的数据

索引什么时候失效：

​ 模糊查询的时候，第一通配符使用的是%，这个时候索引会失效

11、三大范式

第一范式：任何一张表都应该有主键，并且每个字段原子性不可再分

第二范式：建立在第一范式基础之上，所有非主键的字段完全依赖主键，不产生部分依赖

多对多采用三张表，关系表存储另外两张表的主键，即关系表两个外键

第三范式：建立在第二范式基础之上，所有非主键的字段直接依赖主键，不能产生传递依赖

多对一采用两张表，多的加外键

一对一设计有两种方案：

主键共享：既是主键也是外键
外键唯一：一张表加外键，并且外键需要添加唯一约束