MySQL数据库

MySQL

数据库三层结构

1. 所谓安装MySQL数据库，就是在主机安装一个数据库管理系统(DBMS)，这个管理程序可以管理多个数据库。DBMS(database manage system)
2. 一个数据库中可以创建多个表，以保存数据(信息)。
3. 数据库管理系统(DBMS)、数据库和表的关系：Client：命令行/SQLyog/Java程序—>MySQL(DBMS)管理系统—>多个数据库---->多个表
   • MySQL数据库-普通表的本质仍然是文件

• 数据在数据库中的存储方式

  • 表分为列和行，表的一行称之为一条记录->在Java程序中，一行记录往往使用对象表示

• SQL语句分类

  • DDL：数据定义语句[create表，库……]
  • DML：数据操作语句[增加insert，修改update，删除delete]
  • DQL：数据查询语句[select]
  • DCL：数据控制语句[管理数据库：比如用户权限grant revoke]

MySQL数据库基本操作

• 创建数据库

  1. CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认utf8
  2. COLLATE：指定数据库字符集的校对规则(常用的utf8 bin[区分大小写]、utf8_general_ci[不区分大小写]注意默认是utf8_general_ci)

• 查看、删除数据库

  1. SHOW DATABASES：显示数据库语句
  2. SHOW CREATE DATABASE：显示数据库创建语句
  3. DROP DATABASE [IF EXISTS]：数据库删除语句[一定要慎用]

• 备份恢复数据库

  1. 备份数据库(注意：在DOS执行)：mysqldump-u用户名-p -B数据库1 数据库2 数据库n>文件名.sql
  2. 恢复数据库(注意：进入MySQL再运行)：source 文件名.sql
  3. 备份库的表：mysqldump -u用户名 -p密码 数据库 表1 表2 表n>d:\文件名.sql

• 创建表

CREATE TABLE table_name(

​ field1 datatype,

​ field2 datatype,

​ field3 datatype

)character set字符集 collate校对规则 engine存储引擎

field:指定列名 datatype:指定列类型(字段类型)

character set:如不指定则为所在数据库字符集

collate：如不指定则为所在数据库校对规则

engine：引擎(这个涉及内容较多，后面单独讲解)

MySQL常用数据类型(列类型)

分类	数据类型	说明
数值类型	BIT(M)	位类型。M指定位数，默认值1，范围1-64
数值类型	TINYINT[UNSIGNED]占1个字节	带符号的范围是-128到127。无符号0到255.默认是有符号
数值类型	SMALLINT[UNSIGNED]占2个字节	带符号是负的215到215-1,无符号0到2^16-1
数值类型	MEDIUMINT[UNSIGNED]占3个字节	带符号是负的223到223-1,无符号0到2^24-1
数值类型	INT[UNSIGNED]占4个字节	带符号是负的231到231-1,无符号0到2^32-1
数值类型	BIGINT[UNSIGNED]占8个字节	带符号是负的263到263-1,无符号0到2^64-1
数值类型	FLOAT[UNSIGNED]	占用空间4个字节
数值类型	DOUBLE[UNSIGNED]	表示比float精度更大的小数，占用空间8个字节
数值类型	DECIMAL[UNSIGNED]	定点数M指定长度，D表示小数点的位数
文本、二进制类型	CHAR(size) char(20)	固定长度字符串 最大255
文本、二进制类型	VARCHAR(size) varchar(20)	可变长度字符串 0~65535[即：2^16-1]
文本、二进制类型	BLOB LONGBLOB	二进制数据BLOB 0~2^16-1 LONGBLOB 0~2^32-1
文本、二进制类型	TEXT LONGTEXT	文本Test 0~2^16 LONGTEXT 0~2^32
时间日期	DATE/DATETIME/TIMESTAMP	日期类型(YYYY-MM-DD)(YYYY-MM-DD HH:MM:SS)，TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间

• 数值型(整数)的基本使用

  1. 使用规范：在能够满足需求的情况下，尽量选择占用空间小的类型
  2. 如何定义一个无符号的整数

  create table t(id tinyint);//默认是有符号的

  create table t(id tinyint unsigned);无符号的

#1.如果没有指定unsigned，则tinyint就是有符号
#2.如果指定unsigned，则tinyint就是无符号0~255
create table t(id tinyint);
create table t(id tinyint unsigned);
insert into t values(127);#这是非常简单的添加语句
select * from t;

• 数值型(bit)的使用

  1. 基本使用

  mysql>create table t(num bit(8));

  mysql>insert into t(1,3);

  mysql>insert into t values(2,65);

  2. 细节说明
     • bit字段显示时，按照位的方式显示
     • 查询的时候仍然可以用使用添加的数值
     • 如果一个值只有0,1可以考虑使用bit(1)，可以节约空间
     • 位类型。M指定位数，默认值为1，范围1-64
     • 使用不多

#1.bit(m) m在1-64
#2.添加数据范围按照你给的位数来确定，比如m=8表示一个字节0~255
#3.显示按照bit
#4.查询时，仍然可以按照数来查询
create table t(num bit(8));
insert into t values(255);
select * from t;
select * from t where num=1;

• 数值型(小数)的基本使用

  1. FLOAT/DOUBLE[UNSIGNED]

  FLOAT是单精度，DOUBLE是双精度

  2. DECIMAL[M,D] [UNSIGNED]
     • 可以支持更加精确的小数位，M是小数位位数(精度)的总数，D是小数点(标度)后面的位数
     • 如果D是0，则值没有小数点或分数部分。M最大65.D最大30.如果D被省略，默认是0.如果M被省略，默认是10
     • 建议：如果希望小数的精度高，推荐使用decimal

create table if not exists t(
	num1 float,
	num2 double,
	num3 decimal(30,20));
insert into t values(88.12345678912345,88.1234567891234,88.1234567891234)
select * from t;-- 结果第一个88.1235，第二个全部，第三个后面还有六个0
create table if not t(
	num decimal(65));
	#很顺利的加入了，而bigint则不行
insert into t values(89999999999999999999999999999999999999999999999999999999999999999999999);

• 字符串的基本使用
  1. CHAR(size)固定长度字符串，最大255字符
  2. VARCHAR(size)可变长度字符串 最大65532字节[utf8编码最大21844字符 1-3个字节用于记录大小]

#如果表的编码是utf8 varchar(size) size =(65535-3) /3=21844
#如果表的编码是gbk varchar(size) size =(65535-3) /2=32766
create table t(`name` char(255));
create table t(`name` varchar(21844));

• 字符串使用细节

  1. 细节一
     1. char(4)//这个4表示字符数(最大255)，不是字节数，不管是中文还是字母都是放四个，按字符计算
     2. vaechar(4)//这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据
     3. 不管是中文还是英文字母，都是最多存放4个， 是按照字符来存放的
  2. 细节2
     1. char(4)是定长(固定的大小)，就是说，即使你插入’aa’，也会占用分配的4个字符
     2. varcher(4)是变长(变化的大小)，就是说，如果你插入了’aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配
     3. varchar本身还需要占用1-3个字节来记录存放内容长度，L(实际数据大小)+(1-3)字节
  3. 细节3：什么时候使用char，什么时候使用varchar
     1. 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等.char(32)
     2. 如果一个字段的长度是不确定，我们使用varchar，比如留言，文章
     3. 查询速度：char>varchar
  4. 细节4
     1. 在存放文本时，也可以使用Test数据类型，可以将Test列视为VARCHAR列，注意Test不能有默认值，大小0-16^字节
     2. 如果希望存放更多字符，可以选择MEDIUMTEXT0-2^24 或者LONGTEST0-2^32

• 日期类型的基本使用

create table t(
	birthday date,-- 生日
	job_time datetime,-- 记录年月日时分秒
	login_time timestamp not null default current_timestamp
    			no update current_timestamp);-- 登录时间,如果希望login自动更新，需要配置
select * from t;
insert into t(birthday,job_time) 
values('2022-11-11','2011-11-11 10:10;10')
-- 如果我们更新t表的某条记录，login_time列会自动的以当前时间进行更新

• 日期类型的细节说明

TIMESTAMP在INSERT和UPDATE时，自动更新

修改表

• 基本介绍
  1. 使用ALTER TABLE 语句追加，修改，或删除列的语法
  2. 修改表名：Rename table 表名 to新表名
  3. 修改表字符集：alter table 表名 character set 字符集

alter table t-- 添加一个列，要求在resume后面
	add image varchar(32) not null default''
	after resume
desc t-- 显示表结构，可以查看表的所有列
alter table t-- 修改job列，使其长度为60
	modify job varchar(60) not null default''
alter table t-- 删除sex列
	drop sex
rename table t to m-- 表名改为m
alter table m character set utf8;-- 修改表的字符集为utf8
alter table m change name user_name varchar(32) not null default ''-- 列名name修改为user_name

数据库CRUD语句

• 基本介绍
  1. Insert语句(添加数据)
  2. Update语句(更新数据)
  3. Delete语句(删除数据)
  4. Select语句(查找数据)

Insert语句

• Insert语句细节说明
  1. 插入的数据应与字段的数据类型相同。比如把’abc’添加到int类型会错误
  2. 数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中
  3. 在varchar中列出的数据位置必须与被加入的列的排列位置相对应
  4. 字符和日期型数据应包含在单引号中
  5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null)
  6. insert into tab_name (列名…) values (),(),()形式添加多条记录
  7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
  8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错
     • 如果某个列没有指定not null，那么当添加数据时，没有给定值，则会默认给null
     • 如果我们希望指定某个列的默认值，可以在创建表时指定

#练习insert语句
-- 创建一张商品表goods(id int,goods_name varchar(10),price double);
-- 添加2条记录
create table `goods`(
    id int,
    goods_name varchar(10),
    price double);
-- 添加数据
insert into `goods`(id,goods_name,price)
	values(10,'华为手机',2000),
	(20,'苹果手机',3000);
	select * from `goods`;

update语句

• update语句使用细节
  1. update语法可以用新值更新原有表行中的各列
  2. set子句指示要修改哪些列和要给予哪些值
  3. where子句指定应更新哪些行。如没有where子句，则更新所有的行(记录)
  4. 如果需要修改多个字段，可以通过set字段1-值1，字段2=值2……

#练习update语句
-- 将所有员工薪水修改为5000元。[如果没有带where条件，会修改所有的记录，因此要小心]
update t set salary=5000;
-- 将姓名为小妖怪的员工薪水修改为3000元
update t 
	set salary=3000 
	where user_name='小妖怪';
-- 将老妖怪的薪水在原有基础上增加1000元
update t
	set salary=salary+1000;
	where user_name='老妖怪';

delete语句

• delete语句使用细节
  1. 如果不使用where子句，将删除表中所有数据
  2. delete语句不能删除某一列的值(可使用update设为null或者’’)
  3. 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。drop table 表名;

#练习delete语句-- 删除表中名称为'老妖怪'的记录delete from t	where user_name='老妖怪';-- 删除表中所有记录,一定要特别小心delete from t;

select语句

• select语句注意事项
  1. select指定查询哪些列的数据
  2. column指定列名
  3. *号代表查询所有列
  4. from指定查询哪张表
  5. distinct可选，指显示结果时，是否去掉重复数据

#练习select语句-- 查询表中所有学生的信息select * from;-- 查询表中所有学生的姓名和对应的英语成绩select name,english from;-- 过滤表中重复数据 distinctselect distinct from;-- 要查询的记录，每个字段都相同，才会去重select distinct * from;

#select语句的使用-- 统计每个学生的总分select name,(chinese+english+math) from student;-- 在所有学生的总分加10分的情况select name,(chinese+english+math+10) from student;-- 使用别名表示学生分数select name,(chinese+english+math) as total_score from student;

表复制和去重

• 自我复制数据(蠕虫复制)

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

-- 表复制create table my_tab01(	id int,	`name` varchar(32),	sal double,	job varchar(32),	deptno int);desc my_tab01;select * from my_tab01;-- 1.先把emp表的记录复制到my_tab01insert into my_tab01	(id,`name`,sal,job,deptno)	select empno,ename,sal,job,deptno from emp;-- 2.自我复制insert into my_tab01	select * from my_tab01;-- 去重-- 1.先创建一张表my_tab02-- 2.让my_tab02有重复的记录create table my_tab02 like emp;-- 这个语句吧emp表的结构(列)，复制到my_tab02insert into my_tab02	select * from emp;-- 3.考虑去重-- (1)先创建一张临时表my_tmp,该表的结构和my_tab02一样-- (2)把my_tab02的记录通过distinct关键字处理后把记录复制到my_tmp-- (3)清除掉my_tab02记录-- (4)把my_tmp表的记录复制到my_tab02-- (5)drop掉临时表my_tmpcreate table my_tmp like my_tab02;insert into my_tmp	select distinct * from my_tba02;delete from my_tab02;insert into my_tab02	select * from my_tmp;drop table my_tmp;

单表查询

运算符

• 在where子句中经常使用的运算符

比较运算符	> < <= >= = <> !=	大于、小于、大于(小于)等于、不等于
比较运算符	between …and…	显示在某一区间的值
比较运算符	in(set)	显示在in列表中的值，例：in(100,200)
比较运算符	like ‘张pattern’ not null	模糊查询
比较运算符	is null	判断是否为空
逻辑运算符	and	多个条件同时成立
逻辑运算符	or	多个条件任一成立
逻辑运算符	not	不成立，例：where not(salary>100);

-- select语句-- 查询姓名为赵云的学生成绩select * from student	where name='赵云';-- 查询英语成绩大于90分的同学select * from student	where english>90;-- 查询math大于60并且id大于4的学生成绩select * from student	where math>60 and id>4;-- 查询英语成绩大于语文成绩的同学select * from student	where english>chinese;-- 查询总分大于200分，并且数学成绩小于语文成绩的姓王的同学select * from student	where (chinese+english+math)>200 and math<chinese and `name` like '王%';-- 王%表示名字以王开头的就可以-- 查询英语成绩在80-90之间的同学select * from student	where english between 80 and 90;-- 查询学生分数为89,90,91的同学select * from student	where math in(89,90,91);

排序查询

• 使用order by子句排序查询结果
  1. order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名
  2. asc升序[默认]、desc降序
  3. order by子句应位于select语句的结尾

-- 练习order by使用-- 对数学成绩排序后输出[升序]select * from student	order by math;-- 对总分按从高到低的顺序输出[降序]select name,(chinese+english+math) as score from student	order by score desc;-- 对性李的学生成绩排序输出[升序]select name,(chinese+english+math) as score from student	where like '李%'	order by score asc;

分组统计

• 使用group by子句对列进行分组
• 使用having子句对分组后的结果进行过滤

-- 练习group by +having-- 有三个表，部门表、员工表、工资级别表-- 如何显示每个部门的平均工资和最高工资-- 按照部门表来分组查询员工表select avg(sal),max(sal) deptno	from emp group by deptno;-- 显示每个部门的每种岗位的平均工资和最低工资select avg(sal),max(sal) deptno,job	from emp group by deptno,job;-- 显示平均工资低于2000的部门号和它的平均工资select avg(sal),deptno	from emp group by deptno		having avg(sal)<2000;

分组增强

-- 使用分组函数和分组子句group by-- 显示每种岗位的雇员总数、平均工资select count(*),avg(sal),job	from emp	group by job;-- 显示雇员总数，以及获得补助的雇员数select count(*),count(if(comm is not null,1,null))	from emp;-- 显示管理者的总人数select count(distinct mgr)from emp;-- 显示雇员工资的最大差额select max(sal)-max(sal)from emp;

查询增强

-- 查询增强-- 使用where子句-- 如何查找1992.1.1后入职的员工？-- 说明：在mysql中，日期类型可以直接比较,需要注意格式select * from emp	where hiredate> '1992-01-01'-- 如何使用like操作符(模糊)-- %表示0到多个任意字符，_表示单个任意字符-- 如何显示首字母为s的员工姓名和工资？select ename,sal from emp	where ename like 'S%';-- 如何显示第三个字符为大写O的所有员工的姓名和工资？select ename,sal from emp	where ename like '__O%';-- 如何显示没有上级的雇员的情况select * from emp	where mgr is null;-- 查询表结构desc emp;-- 所有order by子句-- 如何按照工资的低到高的顺序[升序]，显示雇员的情况？select * from emp	order by sal asc;-- 按照部门号升序而雇员的工资降序，显示雇员信息？select * from emp	order by deptno asc,sal desc;

分页查询

• 基本语法：select …limit start,rows
• 表示start+1行开始取，取出rows行，start从0开始计算

-- 分页查询-- 按雇员的id号升序取出，每页显示3条记录，请分别显示第一页，第二页，第三页-- 第一页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;-- 推导一个公式select * from emp	order by empno -- limit 每页显示记录数*(第几页-1),每页显示的记录数 -- 练习：按雇员的empno号降序取出，每页显示5条记录。请分别显示第3页，第5页select * from emp 	order by empno desc 	limit 10,5;select * from emp 	order by empno desc 	limit 20,5;

mysql表查询加强

• 数据分组的总结

如果select语句同时包含有group by、having、limit、order by那么他们的顺序是group by、having、order by、limit

-- 请统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序，取出前两行记录select deptno,avg(asl) as avg_sal	from emp	group by deptno	having avg_sal >1000	order by avg_sal desc	limit 0,2;

多表查询

• 说明

多表查询是指基于两个和两个以上的表查询，在实际应用中，查询单个表可能不能满足你的需求。

• 在默认情况下：当两个表查询时，规则
  1. 从第一张表中，取出一行和第二张表的每一行进行组合，返回结果[含有两张表的所有列]
  2. 一共返回的记录数=第一张表行数*第二张表行数
  3. 这样多表查询默认处理返回的结果，称为笛卡尔集
  4. 解决这个多表的关键就是要写出正确的过滤条件where，需要进行分析
  5. 多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集

-- 多表查询-- 显示雇员名，雇员工资及所在部门的名字?-- 		1.雇员名，雇员工资来自emp表-- 		2.部门的名字来自dept表-- 		3.需求对emp和dept查询 ename，sal，dname，deptno-- 		4.当我们需要指定显示某个表的列时，需要表.列表select ename,sal,dname,emp.deptno	from emp,dept	where emp.deptno=dept.deptno;-- 如何显示部门号为10的部门名、员工名和工资select ename,sal,dname,emp.deptno	from emp,dept	where emp.deptno=dept.deptno	and emp.deptno=10;-- 显示各个员工的姓名，工资，以及工资的级别select ename,sal,grade	from emp,salgrade	where sal between losal and hisal;

自连接

• 自连接是指在同一张表的连接查询[将同一张表看做两张表]
• 自连接的特点
  1. 把同一张表当做两张表使用
  2. 需要给表取表名 表别名
  3. 列名不明确，可以指定列的别名 列名 as 列的别名
• 子连接分类
  1. 单行子查询：是指只返回一行数据的子查询语句
  2. 多行子查询：是指返回多行数据的子查询，使用关键字in

-- 多表查询的自连接-- 显示公司员工和他的上级的名字select worker.ename as '职员名',boss.ename as '上级名'	from emp worker,emp boss	where worker.mgr=boss.empno;

子查询

• 什么是子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

• 单行子查询

单行子查询是指只返回一行数据的子查询语句

• 多行子查询

多行子查询是指返回多行数据的子查询，使用关键字in

-- 子查询练习-- 如何显示与smith同一部门的所有员工?select *	from emp	where deptno=(    	select deptno    	from emp    	where ename='smith'    );-- 如何查询和部门10的工作相同的雇员的名字、岗位、部门号，但是不含10自己的select ename,job,sal,deptno	from emp	where job in(    	select distinct job    	from emp    	where deptno=10    )and depton !=10; -- 查询ecshop中各个类别中，价格最高的商品 -- 先得到各个类别中，价格最高的商品max+group by cat_id,当做临时表 -- 把子查询当做一张临时表可以解决很多复杂的查询 select cat_id,max(shop_price) 	from ecs_goods 	group by cat_id;select good_id,ecs_goods.cat_id,goods_name,shop_price	from  （select cat_id,max(shop_price) as max_price 	from ecs_goods 	group by cat_id 	）temp,ecs_goods 	where temp.cat_id=ecs_goods.cat_id 	and temp.max_price=ecs_goods.shop_price;-- all和any的使用-- 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号select ename,sal,deptno	from emp	where sal >all(    	select sal    	from emp    	where deptno=30    );-- 如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号select ename,sal,deptno	from emp	where sal >any(    	select sal    	from emp    	where deptno=30    );-- 多行子查询-- 如何查询与smith的部门和岗位完全相同的所有雇员(并且不含smith本人)select *	from emp	where (deptno,job)=(    	select deptno,job    	from emp    	where ename='smith'    )and ename!='smith';-- 查询每个部门的信息(包括：部门号、编号、地址)和人员数量-- 表.*表示将该表所有列都显示出来，可以简化sql语句-- 在多表查询中，当多个表的列不重复时，才可以直接写列名select tmp.*,dname,loc	from dept,(    	select count(*) as per_num,deptno    	from emp    	group by deptno    )tmp    where tmp.deptno=dept.deptno;

合并查询

• 介绍

有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符合union,union all

1. union all

该操作 符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行

2. union

该操作符与union all相似，但是会自动去掉结果集中重复行

-- 合并查询select ename,sal,job	from emp	where sal>2500unionselect ename,sal,job	from emp	where job='manager';

内连接

• 内连接查询根据表中共同的列进行匹配。特别是两个表存在主外键关系时，通常会使用内连接查询

-- select … from 表1 inner join 表2 on 条件

-- 内连接select *from stuinner join examon stu.id=exam.id;

外连接

• 说明

  1. 前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔集进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
  2. 比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门

• 外连接

  1. 左外连接（如果左侧的表完全显示我们就说是左外连接）

  -- select … from 表1 left join 表2 on 条件-- [表1：就是左表，表2：就是右表]
  

  1. 右外连接（如果右侧的表完全显示我们就说是右外连接）

  -- select … from 表1  rigth 表2 on 条件-- [表1：就是左表，表2：就是右表]
  

-- 外连接-- 创建stucreate table stu(	id int,	`name` varchar(32));	insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');-- examcreate table exam(	id int,	grade int);insert into exam values(1,56),(2,76),(11,8);-- 使用左外连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id)select *from stuleft join examon stu.id=exam.id;-- 右外连接(显示所有成绩，如果没有名字匹配，显示空)-- 即：右边的表(exam)和左表没有匹配的记录，也会把右表的记录显示出来select *from sturigth join examon stu.id=exam.id;

函数

统计函数

• 合计/统计函数-count
  • Count返回行的总数

-- 练习count的使用-- 统计一个班级共有多少学生select count(*) from student;-- 统计数学成绩大于90的学生共有多少个select count(*) from student	where math>90;-- 统计总分成绩大于250的人数有多少select count(*) from student	where (chinese+english+math)>250;-- count(*)和count(列)的区别：-- count(*)返回满足条件的记录的行数-- count(列)：统计满足条件的某列有多少个，但是会排除为null的情况

• 合计函数-sum
  • sum函数返回满足where条件的行的和
  • sum仅对数值起作用，否则会报错
  • 对多列求和，“，”号不能少

-- 练习sum的使用-- 统计一个班级数学总成绩select sum(math) from student;-- 统计一个班级语文、数学、英语各科的总成绩select sum(chinese),sum(math),sum(english)from student;-- 统计一个班级语文、数学、英语的成绩总和select sum(chinese+english+math) from student;-- 统计一个班级语文成绩的平均分select sum(chinese)/ count(*) from student;

• 合计函数-avg
  • avg函数返回满足where条件的一列的平均值

-- 练习avg的使用-- 求一个班级数学平均分select avg(math) from student;-- 求一个班级总分平均分select avg(chinese+english+math) from student;

• 合计函数- max/min
  • max/min函数返回满足条件的一列的最大/最小值

-- 练习max/min的使用-- 求班级最高分和最低分(数值范围在统计中特别有用)select max(chinese+english+math),min(chinese+english+math) from student;

字符串函数

字符串相关函数	函数说明
charset(str)	返回字串字符集
concat(string2 [,…])	连接字串
instr(string,substring)	返回substring在string中出现的位置，没有返回0
ucase(string2)	转换成大写
lcase(string2)	转换成小写
left(string2,length)	从string2只的左边起取length个字符
length(string)	string长度[按照字节]
replace(str,search_str,replace_str)	在str中用replace_str替换search_str
strcmp(string1,string2)	逐字符比较两字串大小
substring(str,position[,length])	从str的position开始[从1开始计算]，取length个字符
ltrim(string2)rtrim(string2)trim	取除前端空格或后端空格

-- 字符串相关函数的使用select charset(ename) from emp;-- utf8select concat(ename,'工作是',job) from emp;-- dual亚元表，系统表可以作为测试表使用select instr('aimifala','la') from dual;-- 7select ucase(ename) from emp;select lcase(ename) from emp;select left(ename,2) from emp;-- right 从右边取select right(ename,2) from emp;select length(ename,2) from emp;select ename,replace(job,'manager','经理') from emp;select strcmp('zs','ls') from dual;select substring(ename,1,2) from dual;select ltrim('  zs') from dual;select rtrim('zs   ') from dual;select trim('  zs  ') from dual;

-- 练习字符串相关函数的使用-- 以首字母小写的方式显示所有员工emp表的姓名select concat(lcase(substring(ename,1,1),ucase(substring(ename,2)))) as new_name from emp;

数学函数

数学相关函数	函数说明
abs(num)	绝对值
bin(decimal_number)	十进制转二进制
ceiling(number2)	向上取整，得到比num2大的最小整数
conv(number2,from_base,to_base)	进制转换
floor(number2)	向下取整，得到比num2小的最大整数
format(number,deciaml_places)	保留小数位数
hex(deciamlnumber)	转十六进制
least(number,number……)	求最小数
mod(numerator,denominator)	求余
rand([seed])	其范围为0<=v<=1.0

#练习数学相关函数select abs(-10) from dual;-- 10select bin(10) from dual;-- 1010select ceiling(1.1) from dual;-- 2#下面的含义8是16进制的8，转换成2进制输出select conv(8,16,2) from dual;-- 1000select floor(-1.1) from dual;-- -2select format(78.125456,2) from dual;-- 78.13select hex(16) from dual;-- 10select least(0,1,-3,4) from dual;-- -3select mod(10,3) from dual;-- 10/3=1#1.如果使用 rand() 每次返回不同的随机数，在0<=v<=1.0#2.如果使用rand(seed) 返回随机数，范围在0<=v<=1.0，如果seed不变，该随机数也不变了select rand() from dual;

日期函数

时间日期相关函数	函数说明
current_date()	当前日期
current_time	当前时间
current_timestamp	当前时间戳
date(datetime)	返回datetime的日期部分
date_add(date2,interval,d_valued_type)	在date2中加上日期或时间
date_sub(date2,interval,d_valued_type)	在date2上减去一个时间
datediff(date1,date2)	两个日期差(结果是天)
timediff(date1,date2)	两个时间差(多少小时多少分钟多少秒)
now()	当前时间
year|month|date(datetime)|from_unixtime()	年月日

#日期时间相关函数使用select current_date from dual;-- 当前日期select current_time from dual;-- 当前时间select current_timestamp from dual;-- 当前时间戳-- 显示所有新闻信息，发布日期只显示日期，不用显示时间select id,content date(send_time) from mes;-- 请查询在10分钟内发布的帖子select * from meswhere date_add(send_time,interval 10 minute)>=now();select * from meswhere date_sub(now(),interval 10 minute )<=send_time;-- 请在mysql的sql语句中求出2011-11-11和1990-1-1相差多少天select datediff("2011-11-11","1990-1-1") from dual;-- 请在mysql的sql语句求出你活了多少天？例：1986-11-11select datediff(now(),"1986-11-11") from dual;#12605-- 如果你能活到80岁，求出你还能活多少天select datediff(date_add("1986-11-11",interval 80 year),now()) from dual;#16615select timediff("12:20:45","10:25:31") from dual;#00：55:14select year("2013-11-10") from dual;-- 2013select month("2013-11-10") from dual;-- 11select day(now()) from dual;-- unix_timestamp():返回的是1970-1-1到现在的秒数select unix_timestamp()/(24*3600*365) from dual;-- from_unixtime():可以把一个 unix_timestamp()秒数[时间戳]，转成指定格式的日期#意义：在开发中，可以存放一个整数，，然后表示时间，通过from_unixtime转换select from_unixtime(1618483484,"%Y-%m-%d %H:%m:%s") from dual;

• 日期函数细节说明：
  1. date_add()中的interval后面可以是 year minute second day等
  2. date_sub()中的interval后面可以是 year minute second hour day等
  3. datediff(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
  4. 这四个函数的日期类型可以是date，datetime或者datestamp

加密和系统函数

加密和系统函数	函数说明
user()	查询用户
database()	数据库名称
md5(str)	为字符串算出一个MD5 32的字符串，(用户密码)加密
password(str) select * from mysql.user \G	从原文密码str计算并返回密码字符串，通常由于对mysql数据库的用户密码加密

# 加密和系统函数的使用-- 可以查看登录到mysql的有哪些用户，以及登录的IPselect user() from dual;-- 用户@IP地址-- 查询当前使用数据库名称select database();-- 为字符串算出一个MD5 32的字符串，常用(用户密码)加密-- root密码是'nz'->加密MD5->在数据库中存放的是加密后的密码select md5('nz') from dual;-- 加密函数，mysql数据库的用户密码就是password函数加密select password('nz') -- select * from mysql.user \G从原文密码str 计算并返回密码字符串-- 通常用于对mysql数据库的用户密码加密-- mysql.user 表示数据库.表

流程控制函数

流程控制函数	函数说明
if(expr1,expr2,expr3)	如果expr1为true，则返回expr2，否则返回expr3
ifnull(expr1,expr2)	如果expr1不为空null，则返回expr1，否则返回expr2
select case when expr1 then expr2 when expr3 then expr4 else expr5 end;[类似多重分支]	如果expr1为true，则返回expr2，如果expr3为t，返回expr4，否则返回expr5

# 流程控制语句使用select if(true,'北京','上海') from dual;-- 北京select ifnull(null,'hello') from dual;-- helloselect case	when true then 'jack'-- jack	when true then 'tom'	else 'mary' end;-- 查询emp表，如果comm是null，则显示0.0-- 判断是否为null，要使用is null ，判断不为空使用is notselect ename if(comm is null,0.0,comm)	from emp;-- 如果emp表的job是clerk则显示职员，如果是manager则显示经理-- 如果是aslesman 则显示销售人员，其他资产显示select ename (select case             when job='clerk' then '职员'             when job='manager' then '经理'             when job='aslesman' then '销售人员'             else job end)  as 'job'             from emp;

约束

• 基本介绍

约束用于确保数据库数据满足特定的商业规则。在mysql中，约束包括：not null/unique,primary key,foreign key和check五种

主键

• primary key(主键)基本使用

-- 字段名 字段类型 primary key

• 用于唯一的标识表行的数据，当定义主键约束后，该列不能重复
• primary key(主键)细节说明
  1. primary key不能重复而且不能为null
  2. 一张表最多只能有一个主键，但可以是复合主键
  3. 主键的指定方式有两种
     • 直接在字段名后指定：字段名 primary key
     • 在表定义最后写primary key(列名)
  4. 使用desc表名，可以看到primary key的情况
  5. 在实际开发中，每个表往往都会设计一个主键

-- 主键使用create table t(	id int primary key,-- 表示id列是主键	`name` varchar(32),    email varchar(32));-- 主键列的值是不可以重复insert into tvalues(1,'tom','tom@.com'),(1,'shitm','shitm@.com');-- 会报错-- 主键值不能为空insert into tvalues(null,'tom','tom@.com');-- 会报错create table t1(	id int,-- 表示id列是主键	`name` varchar(32),    email varchar(32),    primary key(id,`name`)-- 这里就是复合主键);-- 添加的时候复合主键的值都一样为重复insert into t1values(1,'tom','tom@.com')-- tinsert into t1values(1,'jack','jack@.com')-- tinsert into t1values(1,'tom','xx@.com')-- f这里就违反了复合主键desc t1-- 查看t1表的结果，显示约束的情况

非空

• not null(非空)

如果在列上定义了not null，那么当插入数据时，必须为列提供数据

-- 字段名 字段类型 not null

-- 非空使用create table t(	id int not null,-- 表示id列是非空	`name` varchar(32),    email varchar(32));-- 非空值不能为空insert into tvalues(null,'tom','tom@.com');-- 会报错

唯一

• unique(唯一)

当定义了唯一约束后，该列值是不能重复的

-- 字段名 字段类型 unique

• unique细节：
  1. 如果没有指定not null，则unique字段可以有多个null
  2. 一张表可以有多个unique字段

-- 唯一使用create table t(	id int unique,-- 表示id列是不可以重复的	`name` varchar(32),    email varchar(32));insert into tvalues(1,'tom','tom@.com'),(1,'shitm','shitm@.com');-- 会报错-- 如果没有指定not null，则unique字段可以有多个null-- 如果一个列(字段)，是unique not null使用效果类似primary keyinsert into tvalues(null,'tom','tom@.com'),(null,'shitm','shitm@.com');-- t-- 一张表可以有多个unique字段create table t(	id int unique,-- 表示id列是不可以重复的	`name` varchar(32) unique,-- 表示name列是不可以重复的    email varchar(32));

外键

• foreign(外键)

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

• foreign key(外键)细节说明
  1. 外键指向的表的字段，要求是primary key或者是unique
  2. 表的类型是innodb，这样的表才支持外键
  3. 外键字段的类型要和主键字段的类型一致(长度可以不同)
  4. 外键字段的值，必须在主键字段中出现过，或者为null(前提是外键字段允许为null)
  5. 一旦建立主外键的关系，数据不能随意删除了

-- foreign key(本表字段名)references-- 主表名(主键名或unique字段名)

-- 外键使用-- 创建主表my_classcreate table my_class(	id int primary key,-- 班级编号	`name` varchar(32) not null default ''    );-- 创建从表my_stucreate table my_stu(	id int primary key,-- 学生编号	`name` varchar(32) not null default '',    class_id int,-- 学生所在班级的编号    -- 下面指定外键关系    foreign key(class_id) references my_class(id));-- 测试数据insert into my_class	values(100,'java'),(200,'web');insert into my_stu	values(1,'tom',100);-- tinsert into my_stu	values(2,'jack',200);-- tinsert into my_stu	values(3,'smith',300);-- f这里会失败，因为300班级不存在insert into my_stu	values(4,'smith',null);-- 可以，外键没有写not null

check

• check基本介绍

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000_{2000之间如果不再1000}2000之间就会提示出错

• oracle和sql server均支持check，但是mysql5.7目前还不支持check，只做语法效验，但不会生效

-- 基本语法： 列名 类型 check (check条件)

-- check使用create table t(	id int primary key,	`name` varchar(32),    sex varchar(6) check(sex in('man','woman')),    sal double check(sal>1000 and sal<2000));insert into t	values(1,'jack','mid',1);-- 添加成功，check不生效-- 学习oracle，sql server,这两个数据库是真的生效

自增长

• 自增长基本介绍

用于使一个整数列在添加记录的时候，该列从1开始，自动的增长

• 自增长的使用细节
  1. 一般来说自增长是和主键配合使用的
  2. 自增长也可以单独使用[但是需要配合一个unique]
  3. 自增长修饰的字段为整数型(虽然小数也可以但是非常非常少这样使用)
  4. 自增长默认从1开始，也可以通过如下命令修改alter table 表名 auto_increment=新的开始值;
  5. 如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据

-- 字段名 整型 primary key auto_increment

-- 自增长的使用-- 创建表create table t(	id int primary key auto_increment,	`name` varchar(32),    email varchar(32));-- 测试增长的使用insert into t	values(null,'tom','tom@.com');-- 或者insert into (`name`,email)	values'tom','tom@.com');

索引

• 说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍
• 索引的原理
  1. 当我们没有索引，使用select * from 表名进行全表扫描，查询速度慢
  2. 使用索引会形成一个索引的数据结构，比如二叉数，进行比较，如果我们比较了30次，覆盖的表的范围2^30
  3. 索引的代价
     1. 磁盘占用
     2. 对dml(undate,delete,insert)语句的效率影响
  4. 不过在我们项目中，select[90%]，uodate,delete,insert[10%]

-- 例一个存有八百万的数据的表-- 在没有创建索引时，我们查询一条记录select *	from emp	where empno=1234567;-- 径测用时4.55秒-- 使用索引来优化一下，体验索引-- 在没有创建索引前，emp.ibd文件大小是524m-- 创建索引后emp.ibd文件大小是655m[索引本身也会占用空间]-- empno_index 索引名称-- ON emp(empno):表示在emp表的empno列创建索引create index empon_index on emp(empno);-- 创建索引后，查询的速度select *	from emp	where empno=1234567;-- 径测用时0.003秒-- 创建索引后，只对创建了索引的列有效select *	from emp	where emname='axJxCs';-- 没有在ename创建索引时，时间4.7screate index ename_index on(ename);-- 在ename上创建索引-- 创建ename列索引，emp.ibd文件大小是827m

• 索引的类型
  1. 主键索引，主键自动的为主索引(类型primary)
  2. 唯一索引(unique)
  3. 普通索引(index)
  4. 全文索引(fulltext)[适用于myisqm]，一般开发，不使用mysql自带的全文索引，而是使用：全文搜索solr和elasticsearch(ES)

create table t(id int primary key,-- 主键，同时也是索引，称为主键索引name varchar(32));create table t2(id int unique,-- id是唯一的，同时也是索引，称为unique索引name varchar(32));

-- 索引的创建create table t(	id int,	`name` varchar(32));-- 查询表是否有索引show indexes from t;-- 添加索引-- 添加唯一索引create unique index id_index on t(id);-- 添加普通索引方式1create index id_index on t(id);-- 如何选择-- 1.如果某列的值，是不会重复的，则优先考虑使用unique索引，否则使用普通索引-- 添加普通索引方式2alter table t add index id_index(id);-- 添加主键索引alter table t add primary key(id);-- 删除索引drop index id_indexx on t;-- 删除主键索引alter table t drop primary key;-- 修改索引，先删除，再添加新的索引-- 查询索引-- 1.方式show index from t;-- 2.方式show indexes from t;-- 3.方式show keys from t;-- 4.方式desc t;

• 小结：哪些列上适合使用索引

  1. 较频繁的作为查询条件字段应该创建索引

  select * from emp where empno =1

  2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

  select * from emp where sex =‘男’

  3. 更新非常频繁的字段不适合创建索引

  select * from emp where logincount=1

  4. 不会出现在where子句中字段不该创建索引

事务

• 什么是事务

事务用于保存数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用于保证数据的一致性

• 事务和锁

当执行事务操作时(dml语句)，mysql会在表上加锁，防止其他用户改表的数据，这对用户来讲是非常重要的

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

-- 事务的一个重要的概念和具体操作-- 1.创建一张测试表create table t(	id int,	`name` varchar(32));-- 2.开始事务start transaction;-- 3.设置保存点savepoint a;-- 执行dml操作insert into t values(100,'tom');select * from t;savepoint b;-- 执行dml操作insert into t values(200,'jack');-- 回退到brollback b;-- 进行回退到arollback a;-- 如果这样，表示直接回退到事务开始的状态rollback;

• 回退事务

在介绍回退事务前，先介绍一下保存点(savepoint)，保存点是事务中的点。回退用于取消部分事务，当结束事务时(commit)，会自动的删除该事务所定义的所有保存点，当执行回退事务时，通过指定保存点可以回退到指定的点。

• 提交事务

使用commit语句可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务后，其他会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效]。

• 事务细节
  1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
  2. 如果开始一个事务，你没有创建保存点，你可以执行rollback，默认就是回退到你事务开始的状态
  3. 你也可以在这个事务中(还没有提交时)，创建多个保存点。比如：savepoint aaa;执行dml，savepoint bbb;
  4. 你可以在事务没有提交前，选择回退到哪个保存点
  5. mysql的事务机制需要innodb的存储引擎才可以使用，myisam不支持
  6. 开始一个事务start transaction，set autocommit=off;

事务的隔离级别

• 概念：mysql隔离级别定义了事务与事务之间的隔离程度

• 事务隔离级别介绍

  1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性
  2. 如果不考虑隔离性，可能会引发如下问题：
     • 脏读
     • 不可重复读
     • 幻读

• 查看事务隔离级别

  1. 脏读(dirty read)：当一个事务读取另一个事务尚未提交的修改时，产生脏读。
  2. 不可重复读(nonrepeatable read)：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
  3. 幻读(phantom read)：同一查询在同一事务中多次进行，由于其他提交事务所做的添加操作，每次返回不同的结果集，此时发生幻读。

mysql隔离级别(4种)	脏读	不可重复读	幻读	加锁读
读未提交(Read uncommitted)	√	√	√	不加锁
读已提交(Read committed)	x	√	√	不加锁
可重复读(Repeatable read)	x	x	x	不加锁
可串行化(Serializable)	x	x	x	加锁

• 说明：√可能出现，x不会出现

• 隔离的重要操作

  1. 查看当前会话隔离级别

  select @@ tx_isolation;

  2. 查看系统当前隔离级别

  select @@ global.tx_isolation;

  3. 设置当前会话隔离级别

  select session transaction isolation level 级别;

  4. 设置系统当前隔离级别

  set global transaction isolation level 级别;

  5. mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目要求)

事务ACID

• 事务的ACLD特征
  1. 原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
  2. 一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态
  3. 隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
  4. 持久性：是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

存储引擎

• 基本介绍
  1. mysql的表类型由存储引擎(Storage Engines)决定，主要包括myisam、innodb、memory等
  2. mysql数据表主要支持六种类型，分别是：csv、memory、archive、mrg_myisam、myisam、innodb
  3. 这六种又分为两类，一类“事务安全型”(transaction-safe)，比如：innodb；其余都属于第二类，称为“非事务安全型”(non-transaction-safe)[mysiam和memory]

-- 表类型和存储引擎-- 查看所有的存储引擎show engines-- innodb存储引擎，是前面使用过的-- 1.支持事务 2.支持外键 3.支持行级锁-- myisam存储引擎create into t(	id int,	`name` varchar(32))engine myisam;-- 1.添加速度快 2.不支持外键和事务 3.支持表级锁-- memory 存储引擎-- 1.数据存储在内存中[关闭了mysql服务，数据丢失，但是表结构还在] 2.执行速度很快(没有IO读写) 3.默认支持索引(hash表)create into t(	id int,	`name` varchar(32))engine memory;

• 细节说明

  1. MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
  2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
  3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在。

• 如何选择表的存储引擎

  1. 如果你的应用不需要事务，处理的只是基本的crud操作，那么myisam是不二选择，速度快
  2. 如果需要支持事务，选择innobd
  3. memory存储引擎就是将数据存储在内存中，由于没有磁盘i/o的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失(经典用法：用户的在线状态)

• 修改存储引擎

-- alter table `表名` engine =存储引擎;

视图

• 基本概念

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，**其数据来自对应的真实表(**基表)

• 视图的基本使用
  1. create view 视图名 as select语句
  2. alter view 视图名 as select 语句-- 更新成新的视图
  3. show create view 视图名
  4. drop view 视图名1，视图名2

-- 视图的使用-- 创建视图create view emp_viewas	select empno,ename,job,deptno from emp;-- 查看视图desc emp_view;select * from emp_view;-- 查看成绩视图的指令show create view emp_view;-- 删除视图drop view emp_view;

• 视图细节

  1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式：视图名.frm)
  2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete]

  -- 针对前面的雇员管理系统--create view myview as select empno,ename,job,comm from emp;select * from myviewupdate myview set comm =200 where empno=7369;#修改视图，对基表也有变化update emp set comm =100 where empno =7369;#修改基表，对视图也有变化
  

  3. 视图中可以使用视图，数据仍然来自基表

• 视图最佳实践

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

MySQL管理

• Mysql用户

mysql中的用户，都存储在系统数据库mysql中user表中

• 其中user表的重要字段说明

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

• 创建用户

create user ‘用户名’ @ ‘允许登录位置’ identified by ‘密码’

说明：创建用户，同时指定密码

• 删除用户

drop user ‘用户名’ @ ‘允许登录位置’;

• 用户修改密码

  1. 修改自己的密码

  set password =password(‘密码’);

  2. 修改他人的密码(需要有修改用户密码权限)

  set password for ‘用户名’@‘登录位置’=password(‘密码’);

-- mysql用户的管理-- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的mysql操作权限-- 所以，mysql数据库管理人员(root)，根据需要创建不同的用户，赋给相应的权限，供人员使用-- 创建新的用户-- (1)'zs_edu'@'localhost'表示用户的完整信息 'zs_edu'用户名 'localhost' 登录的ip-- (2) 123456密码，但是注意存放到mysql.user表时，是password('123456')加密后的密码create user 'zs_edu'@'localhost' identified by '123456';-- 查询用户列表select * from mysql.user-- 删除用户drop user 'zs_edu'@'localhost'-- 修改自己的密码set password =password('abcdef');-- 修改他人的密码，需要权限set password 'root'@'localhost'=password('123456')-- f-- root 用户修改zs_edu密码，是可以成功的set password 'zs_edu'@'localhost'=password('123456')-- t

• 给用户授权

  • 基本语法

  grant 权限列表 on库.对象名 to ‘用户名’@‘登录位置’ [identified by ‘密码’]

  • 说明

    1. 权限列表，多个权限用逗号分开

    grant select on……

    grant select,delete ,create on……

    grant all[privileges] on //表示赋予该用户在该对象上的所有权限

    2. 特别说明

    -- *.*：代表本系统中的所有数据库的所有对象(表、视图、存储过程)-- 库.*：表示某个数据库中的所有数据对象(表、视图、存储过程等)
    

    3. identified by可以省略，也可以写出
       1. 如果用户存在，就是修改该用户密码。
       2. 如果该用户不存在，就是创建该用户！

• 回收用户授权

  • 基本语法

  revoke 权限列表 on库.对象名 from ‘用户名’@‘登录位置’

• 权限生效指令

如果权限没有生效，可以执行下面命令：

flush privileges

-- 用户权限管理的使用-- 下面是root用户-- 创建用户，zhangsan 密码123，从本地登录create user 'zhangsan'@'localhost' identified by '123';-- 使用root用户创建testdb，表newscreate database testdbcreate table news(	id int,	content varchar(32));-- 添加一条测试数据insert into news values(100,'北京新闻');select * from news-- 给zhangsan分配查看news 表和添加news的权限grant select ,insert	on testdb.news	to 'zhangsan'@'localhost'-- 增加权限updateq权限grant update	on testdb.news	to 'zhangsan'@'localhost'-- 修改 zhangsan的密码为abcset password 'zhangsan'@'localhost' =password('abc');-- 回收zhangsan用户在testdb.news表的所有权限revoke select,update,insert on testdb.news 'zhangsan'@'localhost' revoke all on testdb.news 'zhangsan'@'localhost' -- 删除zhangsan用户drop user 'zhangsan'@'localhost';

-- 下面是zhangsan用户-- 这里在默认的情况下，zhangsan用户只能看到一个默认的系统数据库-- root分配权限之后select *from news;-- 可以查看insert into news values(200,'上海新闻');-- 可以添加update news set content ='成都新闻'	where id=100;-- 修改失败-- 增加权限之后可以修改

• 细节说明

  1. 在创建用户的时候，如果不指定host，则为%,%表示所有ip都有连接权限create user xxx
  2. 你也可以指定create user ‘xxx’@'192.168.1.%'表示xxx用户在192.168.1.*的ip可以登录mysql
  3. 在删除用户的时候，如果host表示%，需要明确指定’用户’@‘host值’