Java-MySQL基础

MySQL基础

数据库

1. 表的一行称之为一条记录
2. 普通表的本质就是一个文件
3. DDL：数据定义语义（create）
4. DML：数据操作语言（增加insert，修改update，删除delete）
5. DQL：数据查询语言（select sort ）
6. DCL：数据控制语言（管理数据库， grand revoke）

数据库操作

1. utf8mb4 – 真正的UTF-8 | utf8 – 是多为3个字节的UTF-8，不是真正意义上的UTF-8
2. utf8mb4_0900_as_cs
   1. 0900 表示 校对规则 的 Unicode
   2. ai 表示 不区分 口音， 相反 as 表示 区分 口音
   3. ci 表示 不区分 大小写， 相反 cs 表示 区分 大小写

-- 数据库的操作
-- 1. 创建一个名称为wxy_db01的数据库
create database wxy_db01
-- 2. 创建一个使用utf-8字符集的wxy_db02
create database wxy_db02 character set utf8
-- 3. 创建一个使用utf-8字符集，并带校对规则的wxy_db03
--  校对规则  utf8_bin 不区分大小写  默认校对规则是 utf8_general_ci 不区分大小写
create database wxy_db03 character set utf8 collate utf8_bin
-- 4. 删除数据库
DROP DATABASE wxy_db03

-- 5. 查看当前数据库服务器中的所有数据库
show Databases
-- 6. 查看前面创建的wxy_db01数据库的定义信息
show CREATE DATABASE wxy_db01
-- 7. 删除前面创建的wxy_db01数据库
DROP DATABASE wxy_db01 [IF EXISTS]
-- 为了规避关键字，可以使用反引号解决``

3. 备份数据库 Dos执行

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 路径\文件名.sql

4. 恢复数据库 进入mysql命令行再执行

source 文件名.sql

表

创建表

create table emp (
		id int unsigned AUTO_INCREMENT primary key,
		name varchar(20),
		sex char(1),
		brithday date,
		entry_day date,
		job varchar(20),
		Salay float,
		resume text
);

删除表

修改表

-- 修改表
select * from emp
select * from employee
desc emp -- 查看表的列
-- 添加列
-- alter table table_name add 
-- 	colume datatype [default expr]  ...
ALTER TABLE emp add
		image varchar(32)
-- 修改列
-- alter table table_name modify
-- 	colume datatype [default expr]  ...
alter table emp modify 
		job varchar(60)
-- 删除列
-- alter table table_name drop 
-- 	colume  ...
alter table emp drop
		image
-- 修改表名  rename table 表名 to 新表名
rename table emp to employee
-- 修改表的字符集  alter table 表名  charset 字符集  （character set）
alter table emp character set utf8mb4


-- 修改列名和信息
alter table employee
change name user_name varchar(20) not null default ' '

desc employee

MySQL数据类型△

数值类型

1. unsigned 表示无符号

字符串类型

1. varchar(size) size最大为 65535-3 / utf8或utf8mb4

2. char(size) 其中size表示字符数，不管是中文还是英文都是放size个

   varchar(size) 其中size表示字符数，不管是中文还是英文都以定义好的表的编码来存放数据

3. char(4) 定长的，当插入“aa” 也会占用四个字符的空间

   Varchar(4) 变长的，当插入“aa” 也不会占用四个字符的空间，而是按实际来分配占用空间，而且varchar需要1-3个字节来存放内容长度

4. 当数据是定长时，如身份证号、邮编号、md5密码、手机号等，使用char

   当数据是变长时，直接采用varchar

   查询速度：char > varchar

5. 当存放大量字符时，可以用text代替varchar，若更大可采用longtext、mediumtext

日期和时间类型

1. timestamp 需要自动更新时，需要设置为 not null default current_timestamp on update current_timestamp

bit

1. bit(m) m表示位数 1-64
2. 可存数据为 0 - 2^m -1
3. 查询时，仍然可以使用数值型进行查询
4. 当所需位数小于8时，可以采用bit

CRUD△

insert

-- insert into table_name (colume ...) values (value ...)
create table goods (
		id int primary key,
		goods_name varchar(30),
		price double
);

insert into goods values (1, '油麦菜', 12.35);
insert into goods values (2, '黄瓜', 2.12);
select * from goods;

1. 插入的数据与表中字段的数据类型一致
2. 数据的长度应与列的规定范围中
3. 在values中列出的数据位置必须与被加入的列的排列位置相应
4. 字符和日期型数据应包含在单引号中
5. 列可以插入空值[前提是该字段运行为空 null]
6. insert into tablename (columu) values (), (), ()… 添加多条记录
7. 若给表中所有字段添加信息，可以省略字段的书写
8. 默认值的使用，当不给某字段值时，若有默认值则会自动添加，否则报错，若没有指定not null，则会默认填充null

update

-- update table_name set columun_name = data1, columu_name = data2...
-- where id = ...
-- 没有where时，会对所有记录进行修改
update employee set salay = 5000;
-- where条件修改
update employee 
	set job = '测开'
	where id = 2;
-- 多字段修改
update employee 
	set salay = 19912, sex = 0
	where user_name = 'wxy';

delete

-- delete from table_name where ... 删除某个记录
-- where 条件删除
delete from employee where id = 1;
-- 无where的全部删除
delete from employee;

select

-- select [distinct(去重)] * | {columu1, columu2 ...} 
-- from table_name
-- where ...
select * from student;

select name, english from student;

select distinct english from student;
-- 每个字段都相同才会去重
select distinct * from student;

-- 1. select culumu_name as 别名 from table_name
select name ,chinese as '语文' from student;
select name, chinese, math, english, chinese+english+math as score
	from student;
select id, name, chinese, math, english, chinese + english + math + 10 as score
	from student
	order by id;
-- 2. where 比较运算的使用
select name, math, chinese from student
	where math > 60 and chinese > 90;
select * from student where chinese > english;
select name, chinese, english, math, chinese + english + math as score from student 
	where chinese + english + math > 200
	and math < chinese
	and name like '诸%'; -- 以诸开头，后面任意  _表示任意一个字符
-- 3. or between and
select * from student 
-- 	where english >= 80 and english <=90;
		where english between 80 and 90 -- 闭区间
select * from student
	where math = 12 or math = 11 or math = 90;
select * from student where name like '张%';
select * from student
	where math > 80 and chinese > 80;
-- 4. order by
select * from student
	order by math;
select * from student
	order by english + chinese + math desc;
select * from student
	where name like '刘%'
	order by math;

函数△

统计函数

-- 1. select count(*) | count(columu_name) from table_name
-- where ...
-- count(*)  返回满足条件的记录的行数
-- count(列名)  统计满足条件的某列有多少个，但会排除null
select count(*) from student;
select count(*) from student where math > 60;
select count(*) from student where (chinese + math + english) > 150;
select count(distinct mgr) from emp;  -- 去重

-- 2. select sum(columu_name1) {,sum(columu_name2),...} from table_name
-- where ...
select sum(math) from student;
select sum(math), sum(english), sum(chinese) from student;
select sum(math + english + chinese) from student;
select sum(chinese) / count(*) from student;

-- 3. select avg(columu_name1) {,avg(columu_name2),...} from table_name
-- where ...
select avg(math) from student;
select avg(math + english + chinese) from student;

-- 4. select max[min](columu_name1) from table_name
-- where ...
select max(math + english + chinese), min(math + english + chinese)
	from student;
select max(math) as math_high, min(math) as math_littel from student;

分组方法

-- select column1, column2 from table_name
-- group by column
-- having ...    
-- 1. 每个部门的平均工资和最高工资
select avg(sal), max(sal), deptno
	from emp
	group by deptno
	order by deptno;
-- 2. 每个部门的每种岗位的平均工资和最低工资
select deptno, job, avg(sal), min(sal)
	from emp
	group by deptno, job
	order by deptno;
-- 3. 平均工资低于2000的部门号和它的平均工资
select deptno, avg(sal) as avg_sal
	from emp
	group by deptno
	having avg_sal < 2500
	ORDER BY deptno;

字符串函数

-- 1. charset(str) 返回字符串字符集
select charset(ename) from emp; -- utf8mb4
-- 2. concat(string2 [,....]) 连接字符串 将多列拼接成一列
select concat(ename, '  job is  ', job) from emp;
-- 3. instr(string, substring) 返回substring在string中出现的位置，没有返回0
select instr('wangxinyu', 'wang') from dual; -- dual mysql中亚元表，可用于测试
-- 4. UCASE(string2) 转化成大写
select UCASE(ename) from emp;
-- 4. LCASE(string2) 转化成小写
select LCASE(ename) from emp;
-- 5. left(string2, length) 从string2中的坐标起取length个字符
select left(ename, 2) from emp;
select right(ename, 2) from emp;
-- 6. length(string) string长度
select job, length(job) from emp;
-- 7. replace(str, search_str, replace_str) 在str中用replace_str替换search_str
select replace(job, 'manager', '经理') from emp;
select replace(job, '经理', 'manager') from emp;
-- 8. strcmp(str1, str2) 逐字符比较两字符串大小, 不区分大小写 0 表示相同， -1表示不同
select strcmp('wxy', 'wxy') from dual;
-- 9. substring(str, pos [, len]) 从str的pos开始
select substring(ename, 1, 2) from emp;
-- 10. ltrim(string2) rtrim(string2) trim 去除前端空格或后端空格,左右都去
select ltrim("  wxy   ") from dual;
select rtrim("  wxy   ") from dual;
select trim("  wxy   ") from dual;

-- 练习：以首字母小写的方式显示所有员工emp中的姓名
select empno, concat(UCASE(substring(ename, 1, 1)), substring(ename,2)) from emp;

数学函数

-- 1. abs(num) 绝对值
select ABS(-20) from dual;
-- 2. bin(decimal_number) 十进制转二进制
select bin(10) from dual;
-- 3. ceiling(num) 向上取整
select ceiling(2.5) from dual;
-- 4. conv(number2, from_base, to_base) 进制转换
select conv(10, 10, 2) from dual;
-- 5. floor(num) 向下取整
select floor(2.5) from dual;
-- 6. format(number, decimal_places) 保留小数位数
select format(12.12345, 3) from dual;  -- 四舍五入
select format(avg(sal), 2) from emp group by deptno;
-- 7. hex(decimalNumber) 转十六进制
select hex(17) from dual;
-- 8. least(number1, number2 [,....])  求最小值
select least(10, 20, 30) from dual;
-- 9. mod(numerator, denominator) 求余
select mod(10, 3) from dual;
-- 10. rand([send]) 范围为 0 ≤ v ≤ 1.0 若加入种子后，若种子不变，返回的随机数也不会改变
select format(rand() * 9 + 1 , 0) from dual;

日期函数

-- 1. CURRENT_DATE()	返回当前日期	
select current_date from dual;
-- 2. CURRENT_TIME	返回当前时间	
select current_time from dual;
-- 3. CURRENT_TIMESTAMP()	返回当前日期和时间
select current_timestamp from dual;
insert into emp values
	(0001, 'wxyyy', 'salesman', 0 ,current_date, 9999.00, 9999, 40);
-- 4. DATE()	从日期或日期时间表达式中提取日期值
select id, content, date(send_time) from news;
-- 5. DATE_ADD(d，INTERVAL expr type)	计算起始日期 d 加上一个时间段后的日期
-- type 值可以是：MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
select id, content, send_time from news
	where date_add(send_time, interval 10 minute) >= now();
-- 6. DATE_SUB(date,INTERVAL expr type)	函数从日期减去指定的时间间隔。
select id, content, send_time from news
	where date_sub(NOW(), interval 10 minute) <= send_time;
-- 7. DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数
select datediff('2021-10-23', '2021-10-2') from dual;
select datediff(NOW(), '1998-12-02') / 365 from dual;
select datediff(date_add('1998-12-02', interval 80 year), '1998-12-02') from dual;
-- 8. now() 返回当前时间
select now() from dual;
-- 9. DAY(d)	返回日期值 d 的日期部分
select day(now()) from dual;
select month(now()) from dual;
select year(now()) from dual;
-- 10. unix_timestamp() : 返回的是1970-1-1到现在的毫秒数
select UNIX_TIMESTAMP() from dual;
-- 11. from_unixtime()  可以把unix_timestamp 秒数，转换成指定格式的日期
select from_unixtime(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s') from dual;
select from_unixtime(1628483484, '%Y-%m-%d') from dual;

加密和系统函数

-- 1. user() 查询用户 查看登录到mysql的所有用户和用户ID
select user() from dual;
-- 2. database() 数据库名称
select database();
-- 3. MD5(str) 为字符串算出一个MD5 32位的字符串,（用户密码）加密
select MD5('wxywxy123') from dual;
select length(MD5('wxywxy123')) from dual;
-- 4. Password(str)  mysql数据库的用户密码用password函数进行加密
select * from mysql.USER;
select password('wxy') from dual;

流程控制函数

-- 1. if(expr1, expr2, expr3) 如果expr1 为true，则返回expr2 否则返回expr3
-- (condition, value_if_true, value_if_false
select if(true, '北京', '上海') from dual;
select if(false, '北京', '上海') from dual;
select ename, if(comm is null, 0.0, comm) from emp;
-- 2. IFNULL(expression, alt_value)
-- IFNULL函数接受两个参数，如果不是NULL，则返回第一个参数。否则，IFNULL函数返回第二个参数。
select ifnull('沈阳' , '大连');
select ifnull(null , '大连');
-- 3. CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
select case '12'
			when '11' then 11
			when '12' then 12 end;
select case '12' when '11' then '11' else '12' end;

单表增强

-- 1. 查找1992.1.1 后入职的员工
-- 在mysql中 date数据可以直接比较
select * from emp
	where hiredate > '2000-01-01';

-- 2. like 模糊查询
-- % 表示0到多个任意字符
-- _ 表示单个任意字符
select ename, sal from emp
	where ename like 's%';
select ename, sal from emp
	where ename like '__o%';
select * from emp
	where mgr is null;
desc emp;

-- 3. order by
select * from emp order by sal;
select * from emp order by deptno asc, sal desc;
select * from emp order by sal desc, deptno asc; -- 两者不同

-- 4. 分页查询 select ... limit start, rows
-- 表示从 start+1 行 开始取， 取出rows行，start从0开始计算
select * from emp order by empno limit 0, 3;
select * from emp order by empno limit 3, 3; 
select * from emp order by empno limit 6, 3;

-- 5. 增强group by
select count(*), format(avg(sal), 2) from emp
		group by job;
select count(*), count(comm) from emp  -- count(列) 若该列是null，不进行统计
select count(*), count(if(comm is null, 1, null)) from emp;
select count(*), count(*) - count(comm) from emp;
select count(distinct mgr) from emp;  -- 去重
select max(sal) - min(sal) from emp;

-- 6. 多字句查询  group by  >  having >  order by  >  limit
select avg(sal) as avg_sal from emp
  group by deptno
	having avg_sal > 1000
	order by avg_sal desc
	limit 0, 2;

多表查询

查询

-- 1. 查询员工名，员工工资及所在部门名字
-- 当不适用where条件时，就是两个表或多个表之间的笛卡尔积
-- 笛卡尔积就是 每条记录之间两两组合
select ename, sal, dname, emp.deptno from emp, dept
		where emp.deptno = dept.deptno;
-- 2. 查询部门号为10 的 员工名，员工工资及所在部门名字
select ename, sal, dname, emp.deptno from emp, dept
		where emp.deptno = dept.deptno
		and dept.deptno = 10;
-- 3. 查询各个员工的姓名，工资，及其工资的级别
select ename, sal, grade
	from emp, salgrade
	where emp.sal between losal and hisal;

自连接

-- 自连接
-- 1. 查询工资员工姓名和他的上级的名字
select worker.ename as worker, boss.ename as boss
	from emp worker, emp boss
	where worker.mgr = boss.empno;
-- 自连接的特点： 
-- 1. 把同一张表当成两张表使用
-- 2.	需要给表取别名， 表别名
-- 3. 列名不明确，可以指定列的别名

子查询

-- 1. 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
-- 2. 单行子查询 是指只返回一行数据的子查询语句
-- 3. 多行子查询 是指返回多行数据的子查询，使用关键字in
-- 子查询
-- 1. 查询与simith同一部门的所有员工
select * from emp
	where deptno = (
		select deptno from emp
			where ename like 'smith');
-- 2. 查询和部门10的工作相同的员工的
--    名字、岗位、工资、部门号，但不含10自己的
select ename, job, sal, deptno from emp
	where deptno <> 10
	and job in (
		select distinct job from emp
			where deptno = 10
	);
	
-- 3. 查询ecshop中各个类别，价格最高的商品
select goods_id, cat_id, goods_name, shop_price
	from ecs_goods;

select cat_id, max(shop_price)
	from ecs_goods
	GROUP BY cat_id;
-- 把子查询当做一张临时表
select goods_id, ecs_goods.cat_id, goods_name, ecs_goods.shop_price
	from ecs_goods, (
		select cat_id, max(shop_price) as max_price
			from ecs_goods
			GROUP BY cat_id
	) temp
	where temp.cat_id = ecs_goods.cat_id
	and temp.max_price = ecs_goods.shop_price;


-- all  any
-- 4. 查询工资比部门30的所有员工的工资高的员工姓名、工资和部门号
select ename, sal, deptno from emp
	where sal > all (
		select sal from emp
			where deptno = 30
	);
select ename, sal, deptno from emp
	where sal > (
		select max(sal) from emp
			where deptno = 30
	);
	
-- 5. 查询工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno from emp
	where sal > any (
		select sal from emp
			where deptno = 30
	);
select ename, sal, deptno from emp
	where sal > (
		select min(sal) from emp
			where deptno = 30
	);

-- 6. 多列子查询
--  6.1 查询与allen的部门和岗位完全相同的所有员工
select * from emp
	where (deptno, job) = (
		select deptno, job from emp
		where ename = 'allen'
	);
--  6.2 查询与allen的部门和岗位完全相同的所有员工，但不包含allen本人
select * from emp
	where (deptno, job) in (
		select deptno, job from emp
		where ename = 'allen'
	) and ename <> 'allen';

表的复制与去重

-- 1. 表的复制
create table emp01 (
	empno int not null default 0,
	ename varchar(32),
	sal double,
	job varchar(32),
	deptno int
);

desc emp01;
select * from emp01;
-- 1.1 复制emp表给emp01
insert into emp01 (empno, ename, sal, job, deptno)
		select empno, ename, sal, job, deptno from emp;
		
-- 1.2 emp01自复制
insert into emp01
		select * from emp01;

select count(*) from emp01;
		
-- 2. 表的去重
--   思路： 
-- 2.1 创建一个临时表 temp ，此表与emp01相似
create table temp like emp01;
-- 2.2 通过关键字distinc，将emp01中的信息复制个temp表  
-- 这个时候 也可 直接删除emp01 然后 修改temp的名字为emp01
insert into temp select distinct * from emp01;
-- 2.3 清空emp01表
delete from emp01;
-- 2.4 将temp表中的信息复制给emp01
insert into emp01 select * from temp;
-- 2.5 将temp表删除
drop table temp;
select * from emp01;

合并查询

-- 1. 合并查询
-- 1.1 union all 直接 合并， 不会去重
select * from emp where sal > 2500 union all
select * from emp where job = 'manager';
-- 1.2 union 合并 自动去重
select * from emp where sal > 2500 union
select * from emp where job = 'manager';

外连接△

-- 外连接
-- 例：显示部门名称、员工名称，部门编号，工作，同时显示没有员工的部门
select dname, ename, dept.deptno,job
	from emp, dept
	where emp.deptno = dept.deptno
	order by dname;

select * from emp;

-- 1. 外连接分为 左外连接和右外连接
create table stu (id int, sname varchar(10));
insert into stu values (1, 'jack'),(2, 'tom'),(3, 'nono'),(4, 'wxy');
select * from stu;
create table exam (id int, grade int);
insert into exam values(1, 80),(2,90),(11,60);
select * from exam;
-- 2. 左外连接：左侧的表完全显示 即使没有匹配
-- 2.1 通过左外连接，显示所有人的成绩，若没有成绩，也显示此人的id和sname，成绩为空
select stu.id, sname, grade 
	from stu left join exam
	on stu.id = exam.id;
-- 3. 右外连接：右侧的表完全显示 即使没有匹配
-- 3.1 通过右外连接，显示所有成绩，若没有人，则名字为空
select exam.id, sname, grade 
	from stu right join exam
	on stu.id = exam.id; 
-- 练习：查询部门名称和这些员工信息，同时列出那些没有员工的部门名
select dname, ename, job, dept.deptno
	from emp right join dept
	on emp.deptno = dept.deptno;
select dname, ename, job, dept.deptno
	from dept left	join emp
	on emp.deptno = dept.deptno;

约束

主键 primary key

1. 字段非空，且唯一
2. 一张表只能有一个主键，但可以是复合主键
3. primary key (id, name)

唯一 unique

1. 该字段不能重复
2. 若无 not null 约束时， null值可以添加多个
3. 一张表可以多个unique约束

外键 foreign key

1. 外键约束要定义在从表上，主表则必须具有主键约束，或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键上存在或是为null
2. Foreign key (本表字段名) references 主表名(主键名或unique字段名)
3. 表的类型必须是innodb，这样的表才支持外键
4. 外键字段的类型要和主键字段的类型一致【长度可以不一致】
5. 外键字段的值，必须在主键字段中出现过，或者为null【前提是外键字段允许为空】
6. 一旦建立外键，数据不能随意删除

校验 check

1. Mysql5.7 可以使用，支持语法，但不会生效 。 oracle 和 sql server支持

2. 强制要求字段满足某种条件

3. sex varchar(5) check (sex in ('man', 'woman'))

   sal double check (sal > 1000 and sal < 90000)

自增长

1. id int primary key auto_increment
2. 从1开始，自动添加
3. 搭配 primary key 和 unique 使用
4. 修饰整型
5. 若给自增长的值，指定了一个值，以指定值为准

修改约束

1. alter table 表名 modify column 字段名 字段类型 新约束
   

2. alter table student modify column age int null;
   

3. alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]
   

4. alter table student drop primary key;
   

create table goods (
		goods_id int primary key auto_increment,
		goods_name varchar(32) not null default '',
		unitprice double not null default 0 check(unitprice >= 1.0 and unitprice <= 9999.99),
		category varchar(32),
		privider varchar(32) not null default ''
);

create table customer (
		customer_id int primary key,
		`name` varchar(32) not null default '',
		address varchar(32) not null default '',
		email varchar(32) unique,
		sex varchar(8) not null check (sex in ('男', '女')),
-- 		sex ENUM('男', '女') not null,
		card_id varchar(18) unique
);

create table purchase (
		order_id int unsigned primary key,
		customer_id int not null default 0,
		goods_id int not null default 0,
		nums int not null default 0,
		foreign key (customer_id) references customer(customer_id),
		foreign key (goods_id) references goods(goods_id)
);

desc goods;
desc customer;
desc purchase;

索引△

1. create index index_name on table_name(column_name)
2. 创建索引，会增加表文件的大小，但会优化查询时间 【索引占空间】
3. 创建索引只对创建索引的字段有效

索引机制

1. 不使用索引时，是对全表进行扫描
2. 使用索引，就是创建其他的查找数据结构 例如二叉树，提高查找效率
3. 会产生磁盘占用
4. 会对insert update delete 产生影响，因为每次修改都需要修改索引，也就是修改数据结构的信息

使用索引

1. 主键索引：主键自动的为主索引（类型primary key）

2. 唯一索引：unique

3. 普通索引：index

4. 全文索引：fulltext 适用于MyISAM

   建议使用：Solr 和 ElasticSearch ES

   一般不适用mysql自带的全文索引

-- 索引使用
create table table01 (
		id int,
		`name` varchar(32)
);

show indexes from table01;
-- 1. 添加索引
-- 1.1 添加唯一索引
create unique index id_index on table01(id);
-- 1.2 添加普通索引
-- 若某字段值，不会出现重复，则优先使用unique index  因为快
create index id_index on table01(id);
-- 1.3 添加方式2
alter table table01 add index id_index(id);
-- 1.4 添加主键索引
alter table table01 add primary key (id);

-- 2. 删除索引
drop index id_index on table01;
-- 2.1 删除主键索引
alter table table01 drop primary key;

-- 3. 修改索引 就是 先删除再添加

-- 4. 查询索引
show indexes from table01;
show index from table01;
show keys from table01;

创建索引规则

1. 较频繁作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合创建索引，即使作为查询条件
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在where自己中的字段不该创建索引

事务

1. 事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。

2. 事物和锁：

   当执行事务操作时(dml语句)，mysql会在表上加锁，防止其他用户改表的数据。

   dml (update insert delete)

事务操作

-- mysql 数据库控制台事物的重要操作
-- 1. start transaction;         开始一个事务
-- 2. savepoint  保存点名;         设置保存点
-- 3. rollback to 保存点名;        回退事务
-- 4. rollback;  					  		退回全部事务
-- 5. commit; 						提交事务，所有的操作生效，不能回退

create table table03 (
		id int,
		name varchar(32)
);
select * from table03;
-- 1. 开启事务
start transaction;
-- 2. 设置保存点
savepoint a;
-- 3. dml操作
insert into table03 values(100, 'tom');
-- 4. 再设置一个保存点
savepoint b;
-- 5. dml
insert into table03 values(200,'join');
-- 6. 回退到b  
-- 用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。
-- 当执行回退事务时，通过制定保存点可以回退到指定的点
-- 当回退到a后，不能再回退到b，先后顺序存在
rollback to b;
rollback to a;
rollback;
-- 7. commit 提交事务 回退失效
-- 使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、
-- 结束事务、删除保存点、释放锁、数据生效。
-- 当使用commit语句结束事务后，其它会话将可用查看到事务变化后的新数据
commit;

事务注意事项

• – 注意事项
• – 1. 若不开始事务，默认情况下，dml操作是自动提交，不能回滚
• – 2. 若开始一个事务，若没有创建保存点，可以执行rollback，
• – 默认就是退回到事务开始的状态
• – 3. 可以在这个事务中，创建多个保存点
• – 4. 可以在事务没有提交前，回退到任意保存点
• – 5. mysql的事务机制需要innobb存储引擎支持，myISAM不好使
• – 6. 开始一个事务 用 start transaction;
• – set autocommit = off;

隔离

隔离级别介绍

1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作
   以保证各个连接在获取数据时的准确性。
2. 如果不考虑隔离性，可能会造成：脏读、不可重复读、幻读
   2.1 脏读 dirty read ： 当一个事务读取另一个事务尚未提交的修改时，产生脏读
   2.2 不可重复度 nonrepeatable read：同一查询在同一事务中多次进行，由于其他
   提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复度
   在A事务中对同一数据两次查询不一致，可能原因是在A事务提交之前B事务对该数据进行了操作
   2.3 幻读 phantom read ： 同一查询在同一事务中多次进行，由于其他提交事务
   所做的插入操作，每次返回不同的结果接，此时产生幻读
   类似于不可重复读，都是在一个事务周期内读的数据不一致，
   区别在于幻读是侧重于插入操作带来的影响，而不可重复读是编辑或者删除带来的影响

隔离级别	脏读	不可重复读	幻读	锁
READ UNCOMMITTED	可能	可能	可能	不加锁
READ COMMITTED	不可能	可能	可能	不加锁
REPEATABLE READ	不可能	不可能	可能	不加锁
SERIALIZABLE	不可能	不可能	不可能	加锁

隔离级别操作

-- 1.1 查看当前mysql的隔离级别
select @@tx_isolation;  -- 当前
select @@global.tx_isolation;  -- 系统当前
-- 1.2 设置隔离界别
set session transaction isolation level read uncommitted;
-- 系统隔离级别
set global transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;  -- 系统默认级别
set session transaction isolation level serializable;
-- 1.3 开启事务 start transaction;
-- 1.4 可以通过修改mysql配置文件，设置系统默认隔离级别

事务的acid特性

1. 原子性 Atomicity

    原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
   

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

3. 隔离性 Isolation
   事务的隔离性是多个用户并发访问数据库时，数据库为每个用户开启的事务，
   不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

4. 持久性 Durability
   持久性是指一个事务一旦被提交，它对数据库中的数据的改变就是永久性的，
   接下来即使数据库发生故障也不应该对其有任何影响

存储引擎

基本介绍

1. mysql的表类型由存储引擎(Storage Engines) 决定，主要包括MyISAM、innoDB、Memory等
2. Mysql数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoDB。
3. 这六种又分为两类，一类是 “事务安全型”， 比如 innoDB；
   其余都属于第二类，称为“非事务安全型”

-- 1. 显示当前数据库支持的存储引擎
 show engines;
 -- 2. 修改存储引擎
alter table table_name engine = engines_name;

视图

基本概念

1. 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
2. 视图和基表关系
   通过视图可以修改基表的数据
   基表的改变，也会影响视图的数据
3. 创建视图后，到数据库中，对应视图只有一个视图结构文件

基本使用

-- 视图的基本使用
-- 1. create view 视图名 as select语句
-- 2. alter view 视图名 as select语句
-- 3. show create view 视图名
-- 4. drop view 视图名1, 视图名2

-- 1. 创建视图
create view emp_view01 as
	select empno, ename, job, deptno from emp;
-- 2. 查看视图
desc emp_view01;
-- 3. 查询
select * from emp_view01;
select empno, job from emp01 where empno = 1;
-- 4. 修改视图
alter view emp_view01 as 
	select empno, ename, job, deptno from emp;
-- 5. 查看创建视图的指令
show create view emp_view01;
-- 6. 删除视图
drop view emp_view01;

-- 修改视图
update emp_view01 set job = 'manager' where empno = 7369; 
update emp_view01 set job = 'salesman' where empno = 7369; 
select * from emp;
select * from emp_view01;
-- 利用视图创建视图
create view emo_view02 as
	select empno,deptno from emp_view01;

create view three_table as
select empno, ename, dept.deptno, dname, emp.sal, salgrade.grade
	from emp, dept, salgrade
	where emp.deptno = dept.deptno
	and emp.sal between losal and hisal;
	
select * from three_table;

视图最佳实践

1. 安全：一些数据表有着重要的信息，有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段，这样用户就可以查询自己需要的字段，不能查看保密的字段
2. 性能：关系数据库的数据常常会分表查询，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接，这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起就可以避免使用join查询数据
3. 灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃，然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样就可以少做很多改动，也达到了升级数据表的目的

管理

用户管理

1. user表的重要字段说明：
   1. host：允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址
   2. user：用户名
   3. authentication_string：密码，是通过mysql的password()函数加密之后的密码

-- 1. 创建用户
-- create user '用户名'@'允许登录位置' identified by '密码'
-- 说明：创建用户，同时指定密码
create user 'wxy_study'@'localhost' identified by '123456';

-- 2. 删除用户
-- drop user 'username' @ 'location';
drop user 'wxy_study'@'localhost';

-- 3. 查询用户表
select * from mysql.user;
select `host`, `user`, `authentication_string` from mysql.user;
select password('123456');

-- 4. 登录（控制台或软件登录）
-- 不同的数据库用户，操作的库和表不相同
-- 不同的数据库用户，登录到DBMS后，根据相应的权限，可以操作的数据库和数据对象(表、视图、触发器等)都不一样

-- 5. 修改自己的密码：
-- set password = password('密码');
-- 6. 修改他人的密码：（需要有修改用户密码的权限）
-- set password for '用户名'@'登录位置' = password('密码');

权限管理

权限	权限级别	权限说明
CREATE	数据库、表或索引	创建数据库、表或索引权限
DROP	数据库或表	删除数据库或表权限
GRANT OPTION	数据库、表或保存的程序	赋予权限选项
REFERENCES	数据库或表
ALTER	表	更改表，比如添加字段、索引等
DELETE	表	删除数据权限
INDEX	表	索引权限
INSERT	表	插入权限
SELECT	表	查询权限
UPDATE	表	更新权限
CREATE VIEW	视图	创建视图权限
SHOW VIEW	视图	查看视图权限
ALTER ROUTINE	存储过程	更改存储过程权限
CREATE ROUTINE	存储过程	创建存储过程权限
EXECUTE	存储过程	执行存储过程权限
FILE	服务器主机上的文件访问	文件访问权限
CREATE TEMPORARY TABLES	服务器管理	创建临时表权限
LOCK TABLES	服务器管理	锁表权限
CREATE USER	服务器管理	创建用户权限
PROCESS	服务器管理	查看进程权限
RELOAD	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT	服务器管理	复制权限
REPLICATION SLAVE	服务器管理	复制权限
SHOW DATABASES	服务器管理	查看数据库权限
SHUTDOWN	服务器管理	关闭数据库权限
SUPER	服务器管理	执行kill线程权限

-- 给用户授权
-- 基本语法：
--           授权用户
-- 1. grant 权限列表 on 库.对象名 to 'username'@'localtion' [identified by 'password']
-- 2. *.*  代表本系统中的所有数据库的所有对象(表，视图，存储过程)
-- 3. 库.* 表示某个数据库中的所有数据对象(表，视图，存储过程等)
-- 4. identified by 可以省略，也可写出
-- 4.1 若用户存在，就修改该用户的密码
-- 4.2 若用户不存在，就创建该用户
--           回收用户授权
-- 基本语法： revoke 权限列表 on 库.对象名 from 'username'@'localtion';
--           权限生成指令
-- 基本语法： flush privileges;

create database testdb;
create table news (
		id int,
		content varchar(32)
);
insert into news values (1, '北京新闻'),(2, '上海新闻'),(3, '广州新闻');
select * from testdb.news;
-- 给 study 分配 查看 news 和添加news 的权限
create user 'study'@'localhost' identified by '123456';
grant select, insert on testdb.news to 'study'@'localhost';
-- 修改密码
set password for 'study'@'localhost' = PASSWORD('abc');
-- 回收权限
revoke select on testdb.news from 'study'@'localhost';
revoke all on testdb.news from 'study'@'localhost';
-- 删除用户
drop user 'study'@'localhost'

补充

1. 在创建用户时，若不指定host，则为%，%表示所有IP都有连接权限
   create user xxx
2. create user 'xxx'@'192.168.11.%' 表示xxx用户在192.168.11.*的ip可以登录到mysql
3. 在删除用户的时候，若host不是%，需要明确指定’username’@‘localtion’