数据库

数据库

1.数据库的基本概念

数据库: DataBase 简称 DB
作用: 用于永久性存储和管理数据的仓库

2.常见数据库

A.MySQL 	简单的免费的数据库  ---> 现在学习
B.Oracle 	大型收费数据库	---> 后期学习
C.SQLServer	大学教材 微软的 C# 和 .Net 超市收银机
D.SQLite	嵌入式数据库 Android系统

3.SQL的含义（Structured Query Language）

SQL是用于操作关系型数据库的语言. (操作某种数据库的规则)

4.SQL的通用语法

A.SQL语句以分号结尾(分号:英文分号)
B.SQL语句不区分大小写(建议大家写关键字的时候,写大写)
C.注释
	a.单行注释
		方式1:  -- 单行注释,注意中间有空格
		方式2:  #这是注释,MySQL专属的注释
	b.多行注释
		/*这是多行注释,和Java的一样*/

5.SQL语句分类

A. DDL 操作数据库和表
B. DML 增,删,改 数据库当中表记录
C. DQL 查询数据库当中表记录
D. DCL 客户端授权数据库的操作(了解)

数据库备份还原

1、命令行备份和还原

A.备份数据库
	mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
	
B.还原数据库
	a.登录数据库
	b.创建数据库
	c.使用数据库
	d.执行文件  source 文件路径

MySQL数据库

1.MySQL启动服务操作

A.手动启动
	a.计算机 -> 管理 -> 服务和应用程序 -> 服务
	b.任务管理器 -> 服务
	
B.普通CMD小黑窗口
	a.指令: servers.msc   "微软ms控制c"
	
C.管理员CMD小黑窗口
	a.开启服务  net start mysql
	b.关闭服务  net stop  mysql	

2.MySQL登陆与退出

A.登录(三种方式,三选一)
	a.登录自己的
		mysql -u root -p 密码
	b.登录别人的(别人在安装的时候,勾选远程访问)
		方式1: mysql -h 127.0.0.1 -u root -p 密码
		方式2: mysql --host=127.0.0.1 --user=root --password==密码
		
B.退出(两种方式,二选一)
	a. exit
	b. quit

3.MySQL的重要文件

A.安装文件
	my.ini 可以修改编码问题(例如:可以解决中文乱码的问题)
	
B.数据文件
	文件夹 --------> 数据库
	excel文件 -----> 表
	很多条数据 ----> 表记录

MySQL的DDL

数据库的操作：【增，删，改，查，使用】

1.创建数据库

A.基本创建操作
	CREATE DATABASE 数据库名称;
	
B.综合创建操作(判断是否存在,指定字符集"不会出现中文乱码问题")
	CREATE DATABASE IF NOT EXISTS 数据库名称  CHARACTER SET 具体的编码;

2.查询数据库

A.显示所有的数据库
	SHOW DATABASES;
	
B.显示数据库的创建语句(隐含的作用:查看数据库的字符集)
	SHOW CREATE DATABASE 数据库名称;

3.修改数据库

A.修改数据库的字符集  
	ALTER DATABASE 数据库名称 CHARACTER SET 具体的编码;

4.删除数据库

A.基本的删除操作
	DROP DATABASE 数据库名称;
	
B.判断是否存在,存在则删除的操作
	DROP DATABASE IF EXISTS 数据库名称;

5.使用数据库

A.使用数据库
	USE 数据库名称;

B.查询正在使用当中的数据库
	SELECT DATABASE();

表的操作【增，删，改，查】

1.创建表的操作

A.基本的创建格式
	CREATE TABLE 表名称 (列名1 类型1,列名2 类型2,列名3 类型3);

B.复制表的操作
	CREATE TABLE 新表名称 LIKE 原始表;

C.常用的数据类型
	a.整数类型 int
	b.小数类型 double(4,1)   4表示有4个数字,1表示精确小数点后1位
	c.字符串类型 varchar(24) 24表示最大容纳24个字符
	d.日期类型 datetime		 需要手动的插入数据到数据库当中
	e.时间戳类型 timestamp 	可以自动添加当前的系统时间

2.删除表的操作

A.基本的删除操作
		DROP TABLE 表名称;

B.判断是否存在,存在则删除的操作
		DROP TABLE IF EXISTS 表名称;

3.修改表的操作

A.修改表名称
		ALTER TABLE 原始表名称 RENAME TO 新的表名;
	
B.修改表的字符集
		ALTER TABLE 表名称 CHARACTER SET  字符集编码;
	
C.修改列名
		ALTER TABLE 表名称 CHANGE 旧列名称  新列名称  新列数据类型;
		ALTER TABLE 表名称 MODIFY 要修改的列名 新的数据类型;
	
D.增加一列
		ALTER TABLE 表名称 ADD 列名称 数据类型;
	
E.删除一列
		ALTER TABLE 表名称 DROP 列名称;

4.查询表的操作

A.查询数据库当中所有的表信息
	SHOW TABLES;

B.查询某个具体表的结构(每个字段的信息)
	DESC 表名称;

MySQL的DML

1.表记录的添加语句

A.添加指定列的数据
		INSERT INTO 表名称 (列名1,列名2,列名3) VALUES (值1,值2,值3);
	
B.添加所有列的数据
		INSERT INTO 表名称  VALUES (值1,值2,值3,值4,值5);
	
C.注意事项
		除了数字类型(整数和小数)以外,其他的都要采用引号(单引号双引号都可以)引起来

2.表记录的删除语句

A.删除指定的某些条记录
		DELETE FROM 表名称 [WHERE 条件];
    
B.删除整个表的操作
		a. DELETE FROM 表名称;  	-- 删除整个表(逐行删除) 效率低
		b. TRUNCATE TABLE 表名称;	-- 删除整个表(先删表,再建表) 效率高 【推荐使用】
		c. DROP TABLE 表名称;  -- 删除整个表,直接删除表结构,从数据库当中直接删除掉.
		
C.《面试题》请问: TRUNCATE 和 DROP 有什么区别?
	回答: TRUNCATE 先删除表当中所有的数据，然后会创建一张一模一样的空表。 DROP 直接从数据库当中删除表，不会创建新的表。

3.表记录的修改语句

A.常见修改语句的操作(带有条件)
		UPDATE 表名称 SET 列名1=值1,列名2=值2 [WHERE 条件];
	
B.不常见的修改语句操作(修改所有的情况)
		UPDATE 表名称 SET 列名1=值1,列名2=值2;

MySQL的DQL

1.基础查询

A.多个字段的查询操作
	a.通用写法
		SELECT * FROM 表名称;
	b.指定列写法
		SELECT 列名1,列名2,列名3  FROM 表名称;

B.去掉重复的查询操作
		SELECT DISTINCT 列名 FROM 表名称;

C.四则运算计算列的操作
		SELECT IFNULL (列名1,0) + 列名2  FROM 表名称;

D.给查询结果起别名的操作
		 SELECT 列名1 AS 别名1,列名2 别名2 FROM 表名称; 

2.条件查询

A.与 && （理解）
	a.写法1:  SELECT * FROM 表名称 WHERE age>20 && age<30;
	b.写法2:  SELECT * FROM 表名称 WHERE age>20 AND age<30;    
	c.写法3:  SELECT * FROM 表名称 WHERE age BETWEEN 20 AND 30;
	
B.或 || （理解）
	a.写法1:  SELECT * FROM 表名称 WHERE age=20 || age=30;
	b.写法2:  SELECT * FROM 表名称 WHERE age=20 OR age=30;
	c.写法3:  SELECT * FROM 表名称 WHERE age IN(20,30);   -- 年龄是20或者30的才可以
	
C.非 !  （理解）
	a.写法1:  SELECT * FROM 表名称 WHERE address IS NULL;  -- 居住地是Null
	b.写法2:  SELECT * FROM 表名称 WHERE address IS NOT NULL;  -- 居住地不是Null

3.模糊查询

A.两个占位符
	a. '_' 下划线,表示1个字符
	b. '%' 百分号,表示0个或多个字符
	
B.案例
	a. 查询姓'武'
		SELECT * FROM hero WHERE name LIKE '武%'; 
	b. 查询姓'武'只有两个字
		SELECT * FROM hero WHERE name LIKE '武_';
	c. 查询第二个字是'文'的
		SELECT * FROM hero WHERE name LIKE '_文%';
	d. 查询只要带有'李'字的
		SELECT * FROM hero WHERE name LIKE '%李%';

DQL数据库表记录的查询

1.排序查询

A.基础语法:
	ORDER BY 列名称 排序规则

B.排序规则: 
	ASC 	升序(从小到大) 默认的
	DESC 	降序
		
C.案例:
	-- 按照数学成绩升序排列
	SELECT * FROM 学生表 ORDER BY 数学成绩 ASC ;  
	-- 先按照数学成绩升序排,数学成绩相同就按英语成绩降序
	SELECT * FROM 学生表 ORDER BY 数学成绩 ASC , 英语成绩 DESC;  

2.聚合函数

A.常用的聚合函数
	a. COUNT(列名)   	-- 求个数
	b. MAX(列名)		-- 求最大值
	c. MIN(列名)		-- 求最小值
	d. SUM(列名)		-- 求和
	e. AVG(列名)		-- 求平均值
	
B.注意事项
	聚合函数排除为NULL值的列.解决方案:
	a.不采用 为NULL的列
	b.函数 IFNULL(列名,0)
        
C.例如: 统计学生表当中,数学成绩的平均分
	SELECT AVG(IFNULL(数学成绩,0)) FROM 学生表;

3.分组查询

A.基础格式
	GROUP BY 分组的字段
	
B.注意事项
	a.分组之后,查询的结果是 分组的字段 或者是 聚合函数
	b.两个区别 WHERE 和 HAVING 
		WHERE 	分组之前的条件筛选,不满足条件,不参与分组. 后面不能跟聚合函数
		HAVING 	分组之后的条件筛选,不满足条件,不会显示出来. 后面直接跟聚合函数
		
C.案例
	--  根据性别进行分组,查询学生表当中的性别和对应的人数
	SELECT 性别,COUNT(主键id) FROM 学生表 GROUP BY 性别; 
	-- 从学生表当中查询成绩大于80,对性别分组要求人数大于2,显示最后的结果
	SELECT COUNT(主键id) FROM 学生表 WHERE 成绩>80 GROUP BY 性别 HAVING COUNT(主键ID)>2;  

4.分页查询

A.基础格式
	LIMIT 起始位置,每页条数
	
B.计算公式
	起始位置 = (当前页数-1)*每页条数
	
C.案例: 每页条数显示4条,在第6页的写法
	SELECT * FROM 表名称 LIMIT 20,4; -- (6-1)*4 =20

约束

约束的作用:（为什么要加上约束）

保证数据的完整性正确性。（可以校验数据是否是"重复的"是否是"不是空值"等）

对于每一种约束而言: 

(1) 在创建表的时候，添加约束

(2)在表创建之后，添加约束

(3)删除约束

1.非空约束

A.名称 ： NOT NULL
B.效果 ： 不能添加为空的数据
c.三种操作方式
    a.创建表的时候,添加非空约束
    	CREATE TABLE 表名称 (列名1 数据类型1,列名2 数据类型2 NOT NULL);
    b.表创建完毕之后,再添加非空约束
    	ALTER TABLE 表名称 MODIFY 列名称 数据类型 NOT NULL;
    c.删除非空约束
    	ALTER TABLE 表名称 MODIFY 列名称 数据类型;

2.唯一约束

A.名称 ：UNIQUE
B.效果 ：不能添加重复的数据，NULL只能有一个 例如 手机号，身份证号
C.三种操作方式
     a.创建表的时候,添加唯一约束
     	CREATE TABLE 表名称 (列名1 数据类型1,列名2 数据类型2 UNIQUE);
     b.表创建完毕之后,再添加唯一约束【条件:不能有重复的】
     	ALTER TABLE 表名称 MODIFY 列名称 数据类型 UNIQUE;
     c.删除唯一约束
     	ALTER TABLE 表名称 DROP INDEX 列名称;

3.主键约束

A.名称 ：PRIMARY KEY 主键的关键字
        AUTO_INCREMENT 自动增长的关键字（根据上一行数据进行的自动增长）
B.效果 ： 
       主键效果：非空且唯一，而且在一张表中只能有一个主键
       自动增长：在上一行的基础上,自动加上1
C.三种操作方式
        a.创建表的时候,添加主键约束
        	CREATE TABLE 表名称 (列名1 数据类型1 PRIMARY KEY AUTO_INCREMENT,列名2 数据类型2);
        b.表创建完毕之后,再添加主键约束【条件:不能有重复的】
        	ALTER TABLE 表名称 MODIFY 列名称 数据类型  PRIMARY KEY AUTO_INCREMENT;
        c.删除主键约束
        	ALTER TABLE 表名称 DROP PRIMARY KEY;  -- 删除主键约束
        	ALTER TABLE 表名称 MODIFY 自动增长的列名称 自动增长的数据类型;  -- 删除自动增长的效果

4.外键约束

A.名称 ：FOREIGN KEY
B.效果 ：让两个表之间产生关联,自己表的"外键"连上别人表的"主键"
C.三种操作方式
        a.创建表的时候,添加外键约束
            CREATE TABLE 表名称(
            	列名1 数据类型1,
           		列名2 数据类型2,
            	CONSTRAINT 外键别名 FOREIGN KEY (外键列名称) REFERENCES 其他表名称(其他表主键)
            );
        
        b.表创建完毕之后,再添加外键约束
        	ALTER TABLE 表名称 ADD CONSTRAINT 外键别名 FOREIGN KEY (外键列名) 
        	REFERENCES 其他表名(其他表主键);
        	
        c.删除外键约束
        	ALTER TABLE 表名称 DROP FOREIGN KEY 外键别名;
D.外键级联操作
        a.级联的含义: 
        	多个表主外键关系删除和修改的时候,彼此之间会受到影响
        	
        b.两个级联的关键字
        	级联更新  	ON UPDATE CASCADE
        	级联删除	ON DELETE CASCADE 
        	
        c.添加级联操作的语法
        	ALTER TABLE 表名称 ADD CONSTRAINT 外键别名 FOREIGN KEY (外键列名称) 
        	REFERENCES 其他表名称(其他表主键) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

多表查询的类型

1、一对多
    A.生活实例: 
    	部门和员工之间的关系
    B.建表原则:
    	一是主键,多是外键.多的外键指向一的主键
	
2、多对多 
    A.生活实例:
    	学生和课程之间的关系
    B.建表原则:
    	设计一个"中间表",至少包含两个字段(两个表的主键)

3、一对一
    A.生活实例:
    	人和身份证的关系
    B.建表原则:
    	在任意一方添加外键（唯一约束 UNIQUE ）指向另一方的主键

1、笛卡尔积

A.语法: 
	SELECT * FROM 表名称1,表名称2;
	
B.查询出来的数据条目数:
	a.计算公式
		表名称1的条目数x表名称2的条目数 
	b.例如: 
		表1有4条,表2里面9条. 查询的结果是 4*9 = 36 条

2、内连接查询

A.隐式内连接 语法
	SELECT * FROM 表1,表2 WHERE 表1.列1 = 表2.列2;  -- 标准写法
	SELECT * FROM 表1 别名1,表2 别名2 WHERE 别名1.列1 = 别名2.列2;  -- 别名的写法 
	
B.显式内连接 语法
	SELECT * FROM 表1 INNER JOIN 表2 ON 表1.列1 = 表2.列2; -- 完整格式
	SELECT * FROM 表1 JOIN 表2 ON  表1.列1 = 表2.列2; -- 省略INNER的格式
	
C.内连接查询的结果
	a.特点:
		两个表当中交集的部分.取数据多的。
		表1,有8条数据,6条数据在交集当中.
		表2,有20条数据,10条数据在交集当中.
		那么采用内连接查询的结果是 10 条数据。
	b.两个表内连接查询的时候,
		表1有10条数据,表2有5条数据.请问.最多有多少条数据,最少有多少条数据?
		最多10条,最少0条。

3、外连接查询

A.左外连接
	a.语法格式
		SELECT * FROM 表1 LEFT OUTER JOIN 表2 ON 表1.列1 = 表2.列2; -- 完整格式
		SELECT * FROM 表1 LEFT JOIN 表2 ON  表1.列1 = 表2.列2; -- 省略OUTER的格式
	b.结果特点
		左表的全部内容+左右表的交集。(JOIN的左边就是左表,JOIN的右边就是右表) 
		
B.右外连接
	a.语法格式
		SELECT * FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.列1 = 表2.列2; -- 完整格式
		SELECT * FROM 表1 RIGHT JOIN 表2 ON  表1.列1 = 表2.列2; -- 省略OUTER的格式
	b.结果特点
		右表的全部内容+左右表的交集。(JOIN的左边就是左表,JOIN的右边就是右表) 
		
C.思考问题
	两个表左外连接查询的时候,
		表1有5条数据,表2有10条数据.请问.最多有多少条数据,最少有多少条数据?
		左表是 表1,右表是 表2
		最多14条,最少5条。

4、子查询

A.概念:子查询,也可以理解为"嵌套查询".一个查询语句的结果是下一个查询语句的条件.
B.子查询的情况
        a.单行单列,主要采用> < >= <= = <> 判断
        	案例: 查询最高工资的员工信息
        	原始做法:
        		-- 查询到最高的员工工资
        		SELECT MAX(gongzi) FROM yuangong;  
        		-- 查询到的最高工资作为条件.再查询他信息
        		SELECT * FROM yuangong WHERE gongzi = 查询到的结果;  
        	子查询的情况	
        		SELECT * FROM yuangong WHERE gongzi = (SELECT MAX(gongzi) FROM yuangong);
        		
        b.多行单列,主要采用的 IN(选项1,选项2,选项3)
        	案例:查询财务部和市场部的员工信息
        	原始做法:
        		-- 查询到财务部和市场部的ID
        		SELECT id FROM yuangong WHERE name IN ('财务部','市场部');
        		-- 通过查询到的结果作为条件,查询满足条件的所有员工信息
        		SELECT * FROM yuangong WHERE id = 查询到的id结果; 
        	子查询的情况
        		SELECT * FROM yuangong WHERE id IN (SELECT id FROM 
        		yuangong WHERE name IN ('财务部','市场部')); 
        		
        c.多行多列,作为虚拟表,进行的连接查询.
        	案例: 查询入职日期是2011-11-11日之后的员工信息和部门信息
        	子查询的情况
        		SELECT * FROM bumen t1 ,(SELECT * FROM yuangong WHERE 
        		yuangong.join_date > '2011-11-11') t2 WHERE t1.id = t2.bumen_id;
        	采用普通内连接
        		SELECT * FROM bumen t1 , yuangong t2  WHERE  t1.id = t2.bumen_id 
        		AND t2.join_date > '2011-11-11';

5、多表查询的分析思路:

A. 观察
   观察需要查询的字段，来自于哪些表。确定要查询的有几张表，这些表会定义在查询语句的 FROM 后面
B.确定多表的类型
   a. 内连接查询（一般情况下都会是内连接查询）
   b. 外连接查询（有"所有的"和"全部的"这种字眼出现）
   c. 子查询(在不能够一步到位的情况，考虑子查询)"第一次查询的结果是下一次查询的条件"
C. 连接条件
    确定连接条件的数目:
    	如果涉及到的是 2 张表， 连接条件有 1 个
    	如果涉及到的是 3 张表， 连接条件有 2 个
    	如果涉及到的是 4 张表， 连接条件有 3 个
    连接条件的数目 = 涉及到表的数目 - 1
D. 查询的结果
    采用 表名称 或者 表的别名，点出字段名称，写在 SELECT 语句 和 FROM 语句 之间。
E. 小技巧
    对于子查询而言，可以给查询的 虚拟表 设置别名，查询的结果 也设置别名。
    在外面的查询当中可以使用 表别名.字段别名

范式

1、范式的含义

设计数据库的时候,遵循的规范

2、范式的分类

A. 第一范式(1NF) 每一项都是不可以分割的原子数据.不能存在合并项
B. 第二范式(2NF) 抽取了核心直接依赖关系,其他间接关系放在一边
C. 第三范式(3NF) 再次抽取间接表当中的依赖关系.
D. 巴斯-科德范式(BCNF)
E. 第四范式(4NF)
F. 第五范式(5NF)
(在同一张表当中，不要存在两行数据当中都是相同的。使劲抽)

数据库事务

1、事务的概念

在多个操作的SQL语句当中,要么同时成功执行,要么同时失败执行。

2、操作步骤:

A.开启事务	START TRANSACTION
B.回滚(出问题了,需要回到开启事务之前)     ROLLBACK
C.提交(正常执行,将改变的数据提交结束事务) COMMIT

3、查看修改事务提交方式

A.查看事务的提交方式
		SELECT @@autocommit;
	
B.修改事务的提交方式
		SET @@autocommit = 0;  -- 0表示手动提交,1表示自动提交

4、事务的四大特征

A.原子性: 在事务的多个SQL语句当中,不可以分割开来
B.持久性: 发生回滚和提交的时候,数据持久的保存在硬盘上面
C.隔离性: 尽可能的多个事务之间没有影响
D.一致性: 事务前后的总量不能改变

5、隔离级别

A.存在问题
	a.脏读：事务X读取到事务Y,未提交的数据.
	b.虚读(不可重复读)：在同一个事务当中,两次读取的数据不一样。
	c.幻读：事务X在操作数据库当中所有的记录,事务Y在添加一条新的记录。事务X读取不到自己的修改
	
B.对应的级别
	a. READ UNCOMMITED 读未提交 (出现"脏读","虚读","幻读")
	b. READ COMMITED  读提交 (出现"虚读","幻读")  ---> Oracle 默认采用
	c. REPEATABLE READ 可重复读 (出现"幻读") ---> MySQL 默认采用
	d. SERIALIZABLE 串行化 (可以解决所有问题,效率低)
	
C.设置和查看隔离级别
	a.查看隔离级别
		SELECT @@TX_ISOLATION;
	b.设置隔离级别
		SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

DCL管理用户

1、创建用户(账号密码)

语法: CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';  -- 主机名 本机 localhost 任意主机 %

2、删除用户(账号)

语法: DROP USER '用户名'@'主机名';

3、修改用户密码

语法1: UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
语法2: SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

4、查询用户(账号密码)

语法: USE mysql;  SELECT * FROM user;  -- 先要使用mysql自己的数据库,然后查询user表

5、忘记了超级管理员用户密码之后的解决方案:

a. cmd 输入 net stop mysql  -- 停止mysql服务 需要管理员运行该cmd
b. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
c. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
d. use mysql;
e. update user set password = password('你的新密码') where user = 'root';
f. 关闭两个窗口
g. 打开任务管理器，手动结束mysqld.exe 的进程
h. 启动mysql服务
i. 使用新密码登录。

6、数据库权限

A.查询权限
	语法: SHOW GRANTS FOR '用户名'@'主机名';
	
B.授予权限
	语法: GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';  -- 标准格式
	语法: GRANT ALL ON *.* TO 'zhangsan'@'localhost';  -- 给张三本机用户授予所有的权限
	
C.撤销权限
	语法: REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
	案例: REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';-- 撤销李四所有机器上的 UPDATE 权限