mysql命令的简单总结

#创建数据库
CREATE DATABASE lagou CHARACTER SET utf8;

#使用数据库
USE lagou;

#查询当前数据库
SELECT DATABASE();

#查询所用数据库
SHOW DATABASES;

#查询字符集
SHOW CREATE DATABASE pan;

#修改数据库字符集
ALTER DATABASE db1 CHARACTER SET utf8;

– 创建一个表结构与 test1 相同的 test2表
CREATE TABLE test2 LIKE test1;

– 查看表结构
DESC test2;

#修改表名
– rename table 旧表名 to 新表名;

#给表添加一个列
– alter table 表名 add 字段名 varchar(20);

#修改表中列的类型
– alter table 表名 modify 字段名称 字段类型;

#修改表明
– alter table 表名 change 旧列名 新列名 类型;

#删除列
– alter table 表名 drop 列名;

DML中需要注意的

– delete from 表；不推荐对表中的数据是逐条删除的
– truncate table 表名 推荐 删除整张表在创建一个一摸一样的新表（空表）

DQL中需要注意的

-- 去重 select distinct  列名 from 表名;
-- 不是  !=  <> 两种
-- 通配符 like %  _
-- 判断为空 is null    不为空  is not  null
-- 排序  order by  列名  desc  / asc

– 聚合函数
– 将一列数据作为一个整体，进行纵向计算
– count sum avg max min

###注意count在统计时会忽略null

– 分组查询
– select 聚合函数/分组字段 from group by 字段名;
– 在分组之后进行条件判断 用having

### 注意having和where区别

WHERE
	-- where用在分组之前
	-- where后面不能跟聚合函数
HAVING
	-- having用在分组之后
	-- having后可以跟聚合函数

SELECT  avg（字段）
FROM 表名
WHERE 字段条件 GROUP BY  字段名
HAVING avg（字段名）

limit

-- limit 可以指定数据的条数   参数（length -返回的行数 offset-默认从0开始）
		#select *from 表名 limit 5;
		#SELECT *FROM 表名 LIMIT 2,5;
-- 分页公式  起始行数=（当前页码-1）*每页显示条数

sql约束

-- 主键约束primary key
	特点： 不可重复且唯一 非空
	作用： 用来表示数据库中的每一条数据
	语法格式： -- 字段名  字段类型 primary key
		   -- primary key(字段名)
		   -- alter table 表名 primary key(字段名)
		   
		   -- 删除主键
		   ALTER TABLE 表名 DROP PRIMARY KEY;
	自增： AUTO_INCREMENT  类型必须是整型
		字段名  字段类型 PRIMARY KEY AUTO_INCREMENT
	修改自增起始值
		CREATE TABLE emp（
		字段 类型 主键 自增，
		字段 类型 
		
		）AUTO_INCREMENT=100;
		
	TRUNCATE  和 delect分别对自增的影响
		-- delect对原表自增没有影响
		-- truncate新表中自增从1开始

– 唯一约束
特点： 不能重复（可以为空）
语法： 字段 类型 UNIQUE
– 非空约束
特点： 某一列不允许为空
语法： 字段 类型 NOT NULL
– 外键约束

-- 什么是外键
		--外键指的是在  从表 中 与 主表 的主键对应的那个字段,比如员工表的 dept_id,就是外键
		-- 使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性
-- 语法
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 表名(主键字段名)

**###注意 删除外键约束**
-- alter table 从表 drop foreign key 外键约束名称

-- 1）从表外键类型必须与主表主键类型一致 否则创建失败.
-- 2) 添加数据时, 应该先添加主表中的数据.
-- 3) 删除数据时,应该先删除从表中的数据.

– 级联删除操作(了解)
– 如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作
– 级联删除
– ON DELETE CASCADE

– 默认值（可以被覆盖）
– 字段名 类型 default 默认值

– 事务

– 隔离级别
– 多个事务操作同一批数据
– 查看隔离级别
SELECT @@tx_isolation;

– 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别名称;

READ UNCOMMITTED 读未提交
READ COMMITTED  读已提交
REPEATABLE READ 可重复读
SERIALIZABLE   串行化

– 例如: 修改隔离级别为 读未提交（一个事务读到另一个事务未提交的数据）
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
– 脏读: 一个事务读取到了另一个事务中尚未提交的数据

– 解决:提升隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

– 不可重复读: 同一个事务中,进行查询操作,但是每次读取的数据内容是不一样的
– 解决不可重复读问题
– 将全局的隔离级别进行提升为： repeatable read

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

– 幻读: select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。
– 将事务隔离级别设置到最高 SERIALIZABLE ，以挡住幻读的发生

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

** ## 总结:**
– serializable 串行化可以彻底解决幻读,但是 事务只能排队执行,严重影响效率,
– 数据库不会使用这种隔离级别

一对多关系(常见)

一对多关系（1:n）
		例如：班级和学生，部门和员工，客户和订单，分类和商品
	一对多建表原则
		在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

USE pan;
CREATE TABLE province(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20),
        description VARCHAR(20)
	
);
CREATE TABLE city(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	description VARCHAR(20),
	 pid INT
);
ALTER TABLE city ADD  FOREIGN KEY (pid) REFERENCES province(id);

多对多关系(常见)

多对多（m:n）
	例如：老师和学生，学生和课程，用户和角色
n 多对多关系建表原则
	需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的 主键。

CREATE TABLE course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)

);
CREATE TABLE teacher(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)

);




-- 中间表

CREATE TABLE teacher_course(
	id INT PRIMARY KEY AUTO_INCREMENT,
	c_id INT,
	t_id INT


);
-- 添加约束
ALTER TABLE teacher_course ADD FOREIGN KEY(c_id) REFERENCES course(id);

ALTER TABLE teacher_course ADD FOREIGN KEY(t_id) REFERENCES teacher(id);

 DROP TABLE course;
 删除有外键关联的数据或者表的时候，mysql出现报错：

-- Cannot delete or update a parent row: a foreign key constraint fails

一对一关系(了解)

一对一（1:1）
		在实际的开发中应用不多.因为一对一可以创建成一张表。

– 笛卡尔积

多表查询（两个标的乘积）
	SELECT * FROM 表1，表2;

内连接查询

通过指定的条件匹配两张表中的内容

隐式内连接

	SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;
        

显示内连接
	SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件 
	-- inner 可以省略

	#分类表 (一方 主表)
CREATE TABLE category (
	cid VARCHAR(32) PRIMARY KEY ,
	cname VARCHAR(50)
);
#商品表 (多方 从表)

CREATE TABLE products(
	pid VARCHAR(32) PRIMARY KEY ,
	pname VARCHAR(50),
	price INT,
	flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
	category_id VARCHAR(32),
	FOREIGN KEY (category_id) REFERENCES category (cid)#添加外键约束
);	

#分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','鞋服');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','汽车');
#商品数据
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视
机',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空
调',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰
箱',4500,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球
鞋',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动
裤',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T
恤',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋
衣',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙
水',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大
宝',200,'1','c003');		
		

SELECT *FROM products p,category  c WHERE p.`category_id`=c.`cid` ;	

查询鞋服分类下,价格大于500的商品名称和价格

– 我们需要确定的几件事
– 1.查询几张表 products & category
– 2.表的连接条件 从表.外键 = 主表的主键
– 3.查询的条件 cname = ‘鞋服’ and price > 500
– 4.要查询的字段 pname price

SELECT
p.pname,
p.price
FROM products p INNER JOIN category c ON p.category_id = c.cid
WHERE p.price > 500 AND cname = '鞋服';

• 外连接
  ------- 左外连接
  左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略
  -------左外连接的特点
  以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据
  如果匹配不到, 左表中的数据正常展示, 右边的展示为null.
  1. 语法格式
     SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
  2. 左外连接, 查询每个分类下的商品个数

查询每个分类下的商品个数

/*
1.连接条件: 主表.主键 = 从表.外键
2.查询条件: 每个分类 需要分组
3.要查询的字段: 分类名称, 分类下商品个数
*/

SELECT
c.`cname` AS '分类名称',
COUNT(p.`pid`) AS '商品个数'
FROM category c LEFT JOIN products p ON c.`cid` = p.`category_id`
GROUP BY c.`cname`;

  -- 右外连接
	右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
	右外连接的特点
		以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
		如果匹配不到，右表中的数据正常展示, 左边展示为null
	1) 语法格式
		SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

– 右外连接查询
SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;

– 子查询 (SubQuery)

什么是子查询
		子查询概念
			一条select 查询语句的结果, 作为另一条 SELECT 语句的一部分
		子查询的特点
			子查询必须放在小括号中
			子查询一般作为父查询的查询条件使用
		子查询常见分类
			where型 子查询: 将子查询的结果, 作为父查询的比较条件
			from型 子查询 : 将子查询的结果, 作为 一张表,提供给父层查询使用   ###注意提供的表要起别名，要不然访问不到
				
	 1. 查询商品中,价格大于500的商品信息,包括 商品名称 商品价格 商品所属分类名称		

				SELECT
					p.`pname`,
					p.`price`,
					c.cname
				FROM products p

			-- 子查询作为一张表使用时 要起别名 才能访问表中字段

INNER JOIN (SELECT * FROM category) c
				ON p.`category_id` = c.cid WHERE p.`price` > 500;

		exists型 子查询: 子查询的结果是单列多行, 类似一个数组, 父层查询使用  IN 函数 ,包含子查询的结果
			查询价格小于两千的商品,来自于哪些分类(名称)

  		   SELECT
					p.cid,
					p.cname
           FROM
                    category p
           WHERE 
			cid IN( SELECT DISTINCT p.category_id  FROM products p WHERE p.price<2000);

			1. 查询家电类 与 鞋服类下面的全部商品信息

SELECT *
		FROM products p
		WHERE  p.`category_id`IN(SELECT 
		c.cid 
		FROM  category c
		WHERE
			c.cname='家电'OR c.cname='鞋服'
			);

子查询总结

1. 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.
2. 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).

数据库设计

 数据库三范式(空间最省)
概念: 三范式就是设计数据库的规则.
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据
库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关
系型数据库，必须满足一定的范式
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的
称为第二范式（2NF） ， 其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）就
行了

第一范式 1NF

概念:
原子性, 做到列不可拆分
第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个
字段都是不可再分的最小数据单元，则满足第一范式。
示例:
地址信息表中, contry这一列,还可以继续拆分,不符合第一范式

第二范式 2NF

概念:
在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
一张表只能描述一件事.
示例:
学员信息表中其实在描述两个事物 , 一个是学员的信息,一个是课程信息
如果放在一张表中,会导致数据的冗余,如果删除学员信息, 成绩的信息也被删除了

MySQL 索引

什么是索引
	在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和
运行更加高效。

常见索引分类

主键索引 (PRIMARY KEY)

特点: 主键是一种唯一性索引,每个表只能有一个主键,用于标识数据表中的某一条记录。
语法：	
	CREATE TABLE 表名( 
	-- 添加主键 (主键是唯一性索引,不能为null,不能重复,)
	字段名 类型 PRIMARY KEY, 
	);

	修改表结构 添加主键索引 ：ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

唯一索引(UNIQUE)

特点: 索引列的所有值都只能出现一次, 必须唯一.唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了
提高访问速度，而只是为了避免数据出现重复。

语法：
	CREATE TABLE 表名( 
	列名 类型(长度), 
	  -- 添加唯一索引
	UNIQUE [索引名称] (列名)
	); 
	使用create语句创建: 在已有的表上创建索引： create unique index 索引名 on 表名(列名(长度))
	修改表结构添加索引； ALTER TABLE 表名 ADD UNIQUE ( 列名 )
	

普通索引 (INDEX)

普通索引特点：（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只
为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建
索引。
1) 语法格式
	使用create index 语句创建: 在已有的表上创建索引 :create index 索引名 on 表名(列名[长度])
	修改表结构添加索引 :ALTER TABLE 表名 ADD UNIQUE ( 列名 )

删除索引

1) 语法格式：ALTER  TABLE  table_name  DROP  INDEX index_name;
2) 删除 demo01 表中名为 dname_indx 的普通索引。ALTER TABLE demo01 DROP INDEX dname_indx

索引的优缺点总结

添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。
索引的优点
1. 大大的提高查询速度
2. 可以显著的减少查询中分组和排序的时间。
索引的缺点
1. 创建索引和维护索引需要时间，而且数据量越大时间越长
2. 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护
速度

MySQL 视图

什么是视图
1. 视图是一种虚拟表。
2. 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。
3. 向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句.
4. 视图向用户提供基表数据的另一种表现形式
2.2 视图的作用
权限控制时可以使用
比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的
作用
简化复杂的多表查询
视图 本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图
就可以获取想要得到的信息(不需要再编写复杂的SQL)
视图主要就是为了简化多表的查询
1) 语法格式：
	create view 视图名 [column_list] as select语句;
	view: 表示视图
	column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中查询
的属性相同
	as : 表示视图要执行的操作
	select语句: 向视图提供数据内容

MySQL 存储过程

简单理解: 存储过程其实就是一堆 SQL 语句的合并。中间加入了一些逻辑控制。

MySQL触发器(了解)

简单理解: 当我们执行一条sql语句的时候，这条sql语句的执行会自动去触发执行其他的sql语句。

需求: 在下订单的时候，对应的商品的库存量要相应的减少，卖出商品之后减少库存量。
编写触发器
	-- 1.修改结束标识
	DELIMITER $
	-- 2.创建触发器
	CREATE TRIGGER t1
	-- 3.指定触发的时机,和要监听的表
	AFTER INSERT ON orders
	-- 4.行触发器 固定写法
	FOR EACH ROW
	-- 4.触发后具体要执行的事件
	BEGIN
	-- 订单+1 库存-1
	UPDATE goods SET num = num -1 WHERE gid = 1;
	END$

DCL

 创建用户   CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
 用户授权   GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
 查看权限   SHOW GRANTS FOR '用户名'@'主机名';
 删除用户   DROP USER '用户名'@'主机名';
 查询用户   -- 查询用户  SELECT * FROM USER;
 备份       mysqldump -u 用户名 -p 密码 数据库 > 文件路径
 恢复       source sql文件地址

---