MySQL
数据库三层结构
- 所谓安装MySQL数据库，就是在主机安装一个数据库管理系统(DBMS)，这个管理程序可以管理多个数据库。DBMS(database manage system)
- 一个数据库中可以创建多个表，以保存数据(信息)。
- 数据库管理系统(DBMS)、数据库和表的关系：Client：命令行/SQLyog/Java程序—>MySQL(DBMS)管理系统—>多个数据库---->多个表
- MySQL数据库-普通表的本质仍然是文件
-
数据在数据库中的存储方式
- 表分为列和行，表的一行称之为一条记录->在Java程序中，一行记录往往使用对象表示
-
SQL语句分类
- DDL：数据定义语句[create表，库……]
- DML：数据操作语句[增加insert，修改update，删除delete]
- DQL：数据查询语句[select]
- DCL：数据控制语句[管理数据库：比如用户权限grant revoke]
MySQL数据库基本操作
-
创建数据库
- CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认utf8
- COLLATE：指定数据库字符集的校对规则(常用的utf8 bin[区分大小写]、utf8_general_ci[不区分大小写]注意默认是utf8_general_ci)
-
查看、删除数据库
- SHOW DATABASES：显示数据库语句
- SHOW CREATE DATABASE：显示数据库创建语句
- DROP DATABASE [IF EXISTS]：数据库删除语句[一定要慎用]
-
备份恢复数据库
- 备份数据库(注意：在DOS执行)：mysqldump-u用户名-p -B数据库1 数据库2 数据库n>文件名.sql
- 恢复数据库(注意：进入MySQL再运行)：source 文件名.sql
- 备份库的表：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操作的时间 |
-
数值型(整数)的基本使用
- 使用规范：在能够满足需求的情况下，尽量选择占用空间小的类型
- 如何定义一个无符号的整数
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)的使用
- 基本使用
mysql>create table t(num bit(8));
mysql>insert into t(1,3);
mysql>insert into t values(2,65);
- 细节说明
- 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;
-
数值型(小数)的基本使用
- FLOAT/DOUBLE[UNSIGNED]
FLOAT是单精度，DOUBLE是双精度
- 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);
- 字符串的基本使用
- CHAR(size)固定长度字符串，最大255字符
- 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));
-
字符串使用细节
- 细节一
- char(4)//这个4表示字符数(最大255)，不是字节数，不管是中文还是字母都是放四个，按字符计算
- vaechar(4)//这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据
- 不管是中文还是英文字母，都是最多存放4个， 是按照字符来存放的
- 细节2
- char(4)是定长(固定的大小)，就是说，即使你插入’aa’，也会占用分配的4个字符
- varcher(4)是变长(变化的大小)，就是说，如果你插入了’aa’，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配
- varchar本身还需要占用1-3个字节来记录存放内容长度，L(实际数据大小)+(1-3)字节
- 细节3：什么时候使用char，什么时候使用varchar
- 如果数据是定长，推荐使用char，比如md5的密码，邮编，手机号，身份证号码等.char(32)
- 如果一个字段的长度是不确定，我们使用varchar，比如留言，文章
- 查询速度：char>varchar
- 细节4
- 在存放文本时，也可以使用Test数据类型，可以将Test列视为VARCHAR列，注意Test不能有默认值，大小0-16^字节
- 如果希望存放更多字符，可以选择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时，自动更新
修改表
- 基本介绍
- 使用ALTER TABLE 语句追加，修改，或删除列的语法
- 修改表名：Rename table 表名 to新表名
- 修改表字符集：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语句
- 基本介绍
- Insert语句(添加数据)
- Update语句(更新数据)
- Delete语句(删除数据)
- Select语句(查找数据)
Insert语句
- Insert语句细节说明
- 插入的数据应与字段的数据类型相同。比如把’abc’添加到int类型会错误
- 数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中
- 在varchar中列出的数据位置必须与被加入的列的排列位置相对应
- 字符和日期型数据应包含在单引号中
- 列可以插入空值[前提是该字段允许为空]，insert into table value(null)
- insert into tab_name (列名…) values (),(),()形式添加多条记录
- 如果是给表中的所有字段添加数据，可以不写前面的字段名称
- 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错
- 如果某个列没有指定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语句使用细节
- update语法可以用新值更新原有表行中的各列
- set子句指示要修改哪些列和要给予哪些值
- where子句指定应更新哪些行。如没有where子句，则更新所有的行(记录)
- 如果需要修改多个字段，可以通过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语句使用细节
- 如果不使用where子句，将删除表中所有数据
- delete语句不能删除某一列的值(可使用update设为null或者’’)
- 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。drop table 表名;
#练习delete语句-- 删除表中名称为'老妖怪'的记录delete from t where user_name='老妖怪';-- 删除表中所有记录,一定要特别小心delete from t;
select语句
- select语句注意事项
- select指定查询哪些列的数据
- column指定列名
- *号代表查询所有列
- from指定查询哪张表
- 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子句排序查询结果
- order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名
- asc升序[默认]、desc降序
- 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;
多表查询
- 说明
多表查询是指基于两个和两个以上的表查询，在实际应用中，查询单个表可能不能满足你的需求。
- 在默认情况下：当两个表查询时，规则
- 从第一张表中，取出一行和第二张表的每一行进行组合，返回结果[含有两张表的所有列]
- 一共返回的记录数=第一张表行数*第二张表行数
- 这样多表查询默认处理返回的结果，称为笛卡尔集
- 解决这个多表的关键就是要写出正确的过滤条件where，需要进行分析
- 多表查询的条件不能少于表的个数-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;
自连接
- 自连接是指在同一张表的连接查询[将同一张表看做两张表]
- 自连接的特点
- 把同一张表当做两张表使用
- 需要给表取表名 表别名
- 列名不明确，可以指定列的别名 列名 as 列的别名
- 子连接分类
- 单行子查询：是指只返回一行数据的子查询语句
- 多行子查询：是指返回多行数据的子查询，使用关键字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
- union all
该操作 符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行
- 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;
外连接
-
说明
- 前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔集进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
- 比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门
-
外连接
- 左外连接（如果左侧的表完全显示我们就说是左外连接）
-- select … from 表1 left join 表2 on 条件-- [表1：就是左表，表2：就是右表]- 右外连接（如果右侧的表完全显示我们就说是右外连接）
-- 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;
- 日期函数细节说明：
- date_add()中的interval后面可以是 year minute second day等
- date_sub()中的interval后面可以是 year minute second hour day等
- datediff(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
- 这四个函数的日期类型可以是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(主键)细节说明
- primary key不能重复而且不能为null
- 一张表最多只能有一个主键，但可以是复合主键
- 主键的指定方式有两种
- 直接在字段名后指定：字段名 primary key
- 在表定义最后写primary key(列名)
- 使用desc表名，可以看到primary key的情况
- 在实际开发中，每个表往往都会设计一个主键
-- 主键使用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细节：
- 如果没有指定not null，则unique字段可以有多个null
- 一张表可以有多个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(外键)细节说明
- 外键指向的表的字段，要求是primary key或者是unique
- 表的类型是innodb，这样的表才支持外键
- 外键字段的类型要和主键字段的类型一致(长度可以不同)
- 外键字段的值，必须在主键字段中出现过，或者为null(前提是外键字段允许为null)
- 一旦建立主外键的关系，数据不能随意删除了
-- 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列值在10002000之间如果不再10002000之间就会提示出错
- 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开始，自动的增长
- 自增长的使用细节
- 一般来说自增长是和主键配合使用的
- 自增长也可以单独使用[但是需要配合一个unique]
- 自增长修饰的字段为整数型(虽然小数也可以但是非常非常少这样使用)
- 自增长默认从1开始，也可以通过如下命令修改alter table 表名 auto_increment=新的开始值;
- 如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据
-- 字段名 整型 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，查询速度就可能提高百倍千倍
- 索引的原理
- 当我们没有索引，使用select * from 表名进行全表扫描，查询速度慢
- 使用索引会形成一个索引的数据结构，比如二叉数，进行比较，如果我们比较了30次，覆盖的表的范围2^30
- 索引的代价
- 磁盘占用
- 对dml(undate,delete,insert)语句的效率影响
- 不过在我们项目中，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
- 索引的类型
- 主键索引，主键自动的为主索引(类型primary)
- 唯一索引(unique)
- 普通索引(index)
- 全文索引(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;
-
小结：哪些列上适合使用索引
- 较频繁的作为查询条件字段应该创建索引
select * from emp where empno =1
- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex =‘男’
- 更新非常频繁的字段不适合创建索引
select * from emp where logincount=1
- 不会出现在where子句中字段不该创建索引
事务
- 什么是事务
事务用于保存数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用于保证数据的一致性
- 事务和锁
当执行事务操作时(dml语句)，mysql会在表上加锁，防止其他用户改表的数据，这对用户来讲是非常重要的
- mysql数据库控制台事务的几个重要操作
- start transaction-- 开始一个事务
- savepoint 保存点名-- 设置保存点
- rollback to-- 保存点名-- 回退事务
- rollback-- 回退全部事务
- 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语句结束事务后，其他会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效]。
- 事务细节
- 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
- 如果开始一个事务，你没有创建保存点，你可以执行rollback，默认就是回退到你事务开始的状态
- 你也可以在这个事务中(还没有提交时)，创建多个保存点。比如：savepoint aaa;执行dml，savepoint bbb;
- 你可以在事务没有提交前，选择回退到哪个保存点
- mysql的事务机制需要innodb的存储引擎才可以使用，myisam不支持
- 开始一个事务start transaction，set autocommit=off;
事务的隔离级别
-
概念：mysql隔离级别定义了事务与事务之间的隔离程度
-
事务隔离级别介绍
- 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性
- 如果不考虑隔离性，可能会引发如下问题：
- 脏读
- 不可重复读
- 幻读
-
查看事务隔离级别
- 脏读(dirty read)：当一个事务读取另一个事务尚未提交的修改时，产生脏读。
- 不可重复读(nonrepeatable read)：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
- 幻读(phantom read)：同一查询在同一事务中多次进行，由于其他提交事务所做的添加操作，每次返回不同的结果集，此时发生幻读。
| mysql隔离级别(4种) | 脏读 | 不可重复读 | 幻读 | 加锁读 |
|---|---|---|---|---|
| 读未提交(Read uncommitted) | √ | √ | √ | 不加锁 |
| 读已提交(Read committed) | x | √ | √ | 不加锁 |
| 可重复读(Repeatable read) | x | x | x | 不加锁 |
| 可串行化(Serializable) | x | x | x | 加锁 |
-
说明：√可能出现，x不会出现
-
隔离的重要操作
- 查看当前会话隔离级别
select @@ tx_isolation;
- 查看系统当前隔离级别
select @@ global.tx_isolation;
- 设置当前会话隔离级别
select session transaction isolation level 级别;
- 设置系统当前隔离级别
set global transaction isolation level 级别;
- mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目要求)
事务ACID
- 事务的ACLD特征
- 原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
- 一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态
- 隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
- 持久性：是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
存储引擎
- 基本介绍
- mysql的表类型由存储引擎(Storage Engines)决定，主要包括myisam、innodb、memory等
- mysql数据表主要支持六种类型，分别是：csv、memory、archive、mrg_myisam、myisam、innodb
- 这六种又分为两类，一类“事务安全型”(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;
-
细节说明
- MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
- InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引
- MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在。
-
如何选择表的存储引擎
- 如果你的应用不需要事务，处理的只是基本的crud操作，那么myisam是不二选择，速度快
- 如果需要支持事务，选择innobd
- memory存储引擎就是将数据存储在内存中，由于没有磁盘i/o的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失(经典用法：用户的在线状态)
-
修改存储引擎
-- alter table `表名` engine =存储引擎;
视图
- 基本概念
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，**其数据来自对应的真实表(**基表)
- 视图的基本使用
- create view 视图名 as select语句
- alter view 视图名 as select 语句-- 更新成新的视图
- show create view 视图名
- 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;
-
视图细节
- 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式：视图名.frm)
- 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[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;#修改基表，对视图也有变化- 视图中可以使用视图，数据仍然来自基表
-
视图最佳实践
- 安全：一些数据表有着重要的数据。有些字段是保密的，不能让用户直接看到，这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
- 性能：关系数据库的数据常常会分表存储，使用外接建立这些表的之间关系。这时，数据库查询通常会用到连接(join)。这样做不但麻烦，效率相对也比较低，如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用join查询数据。
- 灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
MySQL管理
- Mysql用户
mysql中的用户，都存储在系统数据库mysql中user表中
-
其中user表的重要字段说明
- host：允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
- user：用户名
- authentication_string：密码，是通过mysql的password()函数加密之后的密码
-
创建用户
create user ‘用户名’ @ ‘允许登录位置’ identified by ‘密码’
说明：创建用户，同时指定密码
- 删除用户
drop user ‘用户名’ @ ‘允许登录位置’;
-
用户修改密码
- 修改自己的密码
set password =password(‘密码’);
- 修改他人的密码(需要有修改用户密码权限)
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 ‘密码’]
-
说明
- 权限列表，多个权限用逗号分开
grant select on……
grant select,delete ,create on……
grant all[privileges] on //表示赋予该用户在该对象上的所有权限
- 特别说明
-- *.*：代表本系统中的所有数据库的所有对象(表、视图、存储过程)-- 库.*：表示某个数据库中的所有数据对象(表、视图、存储过程等)- identified by可以省略，也可以写出
- 如果用户存在，就是修改该用户密码。
- 如果该用户不存在，就是创建该用户！
-
回收用户授权
- 基本语法
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;-- 修改失败-- 增加权限之后可以修改
-
细节说明
- 在创建用户的时候，如果不指定host，则为%,%表示所有ip都有连接权限create user xxx
- 你也可以指定create user ‘xxx’@'192.168.1.%'表示xxx用户在192.168.1.*的ip可以登录mysql
- 在删除用户的时候，如果host表示%，需要明确指定’用户’@‘host值’
1356
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
