导读
- mysql5.7：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
-
mysql -h 主机名 -P 端口 -u 用户名 -p密码 -
登录前保持服务启动
1、服务方式启动
2、net stop mysql
3、net start mysql
- navicat安装：navicat.com.cn/products/
- sqlyog安装：sqlyog.en.softonic.com
一、数据库
创建数据库
- 案例1：创建一个名为robber_db01数据库
create database if not exists robber_db01
- 案例2：创建一个使用utf8字符集的数据库
create database robber_db02 character set utf8
- 案例3：创建以恶搞使用utf8字符集，并带校对规则的robber_db03
create database robber_db03 character set uft8 collate utf_in
校对规则用于区分大小写
①select * form student where name = “Tom”
①select * form student where name = “tom”
查看、删除数据库
- 显示数据库语句：
select database - 显示数据库创建语句：
select create database robber_db01 - 数据库删除：
drop database if exists robber_db01
若数据库存在关键字则使用反引号
create
备份恢复数据库
- 备份数据库（DOS执行）：
mysqldump -u 用户名 -p 数据库1 数据库2 数据库n > d:\\文件名.sql - 恢复数据库（注意：进入sqllog再执行)：
Source d:\\文件名.sql - 案例：备份robber_db01和robber_db02并恢复数据库
二、表
创建
- 案例：创建一个员工表
create table emp(
id int,
name varchar(32),
sex char(1),
birthday date,
entry_date date,
job varchar(32),
salary decimal,
resume text
)character set utf8 collate utf8_bin engine innodb
数据类型
| 数值类型 | 数据类型 |
|---|---|
| 整形 | bit(M) tinyint【1字节】 smallint【2字节】 mediumint【3字节】 int【4字节】 bigint【8字节】 |
| 小数类型 | float【单精度4个字节】 double【双精度8字节】 decimal(M,D)【大小不确定】 |
decimal注意：如果省略了M和D，则M的默认大小就是10 D则默认是0
并且decimal可以存放比bigint还要大的数值
| 文本类型 | 数据类型 |
|---|---|
| 字符串类型 | char（size）【0-255字符】 varchar【0-65535字节】 text【0~2^16-1】 |
如果使用utf8则varchar只能存放（65532-3）/ 3
如果使用gbk则varchar只能存放（65532-3）/ 2
| 二进制类型 | 数据类型 |
|---|---|
| 二进制 | blob【0~2^16-1】 longblob【0~2^32-1】 |
| 日期类型 | 数据类型 |
|---|---|
| 日期 | data【日期 年月日】 time【时间 时分秒】 datatime【年月日 时分秒 YYY-MM-DD HH:mm:ss】 |
删除
- 删除表：
drop table employee - 删除表中的所有数据但是留下表结构：
drop employee
修改
-
添加列（不能为空默认为‘ ’,插入到末尾）
alter table emp age int not null default '' after resume -
修改列表
alter table emp modify job varcahr(60) not null default '' -
删除列
alter table emp drop sex -
修改表明：rename table 表名 to 新表名
rename table emp to employee -
修改表字符集：alter table 表名 character set 字符集
alter table employee character set utf8 -
修改字段名
alter table employee change `name` `user_name` varcahr(64) not null default '' -
查看表结构：
desc employee
三、新删改查
insert语句
细节
- 插入的数据应该与字段的数据类型相同
- 数据的长度应在列的规定范围内
- values必须
insert into employee(id,goods_name,price,)
values
(10,'华为手机',20000)
update语句
若没有指定where语句 则更新所有行
- 案例1：修改所有员工薪水
update employee
set salary = 5000
- 案例2：修改robber的薪水修改3003
update employee
set salary = 3003
where user_name = '小妖怪'
- 案例3：修改robber的薪水修改3003
update employee
set salary = salary + 1000, job = '出主意'
where user_name = '小妖怪'
删除语句
delete语句
不适用where语句会删除所有数据
- 删除robber
delete from employee
where user_name = 'robebr'
truncate语句
-
TRUNCATE TABLE语句
- 删除表中所有的数据
- 释放表的存储空间
-
举例
TRUNCATE TABLE detail_dept; -
TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚
阿里开发规范：
【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无
事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
select
关键字的顺序：
from -> join -> on -> where -> group by -> 聚合函数 -> having -> select -> distinct -> order by -> limit
-
distinct：去重
-
案例1：查询所有信息
select * from student -
案例2：只显示英语成绩和姓名
select `name`,english from student -
案例3：过滤重复数据
select distinct english form student只有每一列相同才能去重
-
案例4：统计每个学生的总分
select user_name (english+chinese+math) as all from student -
案例5：学生总分加十分
select user_name (english + chinese + math + 10) as all from student
单表查询
运算符
| 比较运算符 | > < <= >= = <> != | 大于、小于、大于等于、不等于 |
|---|---|---|
| 比较运算符 | between … and… | 显示在某一区间的数据 |
| 比较运算符 | in(set) | 显示在in列表中的数值 |
| 比较运算符 | like ‘张pattern’ | 模糊查询 |
| 比较运算符 | is null | 判断是否为空 |
| 逻辑运算符 | and | 多条件同时成立 |
|---|---|---|
| or | 多条件任意任一成立 | |
| not | 不成立，例：where not(salary >100) |
-
案例一：查询总分大于200分，并且数学成绩小于语文成绩的姓韩的同学
select * from student where (math+english+chinese) > 200 and math < chinese and name like '韩%' -
案例二：查询英语分数在80-90分数之间的同学
select * from student where english between 80 and 90 select * from student where english >=80 and english <= 90 -
案例三：查询89，90, 91
select * from student where math in (89,90,91)
order by 排序
- 对数学成绩升序排序
select * from student
order by math
- 对总分降序
select * from student
order by math desc
- 对性韩的学生成绩总分升序排序
select user_name,(math,english,chinese) as total_score from student
where user_name like name '韩%'
order by total_score
group by 分组
having
having 跟 where 区别：
- 第一个区别是，WHERE 是先筛选后连而 HAVING 是先连接后筛选。这一点，就决定了在关联查询中，WHERE比HAVING更高效。因为WHERE可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也就比较高。
- 第二个区别是，WHERE 可以直接使用表中的字段作为筛选条件，但不能以计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
- 第三个区别是，where不可以与聚合函数一起使用，则having则可以跟聚合函数使用
注意：
- having必须跟group by 配合使用
- having子句即可包含聚合函数作用的字段也可包括普通的标量字段
| 优点 | 缺点 | |
|---|---|---|
| where | 先筛选数据再进行链接，执行效率高 | 不能用分组计算函数对 数据筛选 |
| having | 可以使用分组计算函数进行筛选 | 再最后的结果集中对数据进行筛选，执行效率低 |
- 显示平均工资低于2000的部门号和它的平均工资
select avg(salary),deptno from emp
group by deptno
having avg(salary) <2000
with rollup
注意：
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥
的。
-
使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
SELECT department_id,AVG(salary) FROM employees WHERE department_id > 80 GROUP BY department_id WITH ROLLUP;
合并查询
union all 跟 union区别
union可以自带去重
-
union all
select * from emp union all select * from dept -
union
select * from emp union select * from dept
单表加强
where语句
- 案例：查询1992.1.1以后入职的员工
select * from emp where hiredate > '1992-01-01'
-
like操作符号
注意：
%：表示0到多个字符，
_：表示单个字符，可以用来占位
- 案例1：显示首字符为S的员工姓名
select * from where name like 'S%'- 案例2：显示第三个字符为大写O的员工
select * from where name like '__O%'
order by 子句
-
案例：按照部门号升序而雇员的工资降序排序
select * from emp order by depno asc, salary desc
分页查询
-
limit 每页显示记录数 * （第几页 - 1）， 每页显示记录数 -
案例：按员工的id升序取出，没页显示3条，请分别显示
-- 第一页 select * from emp order by emono asc limit 0, 3 -- 第二页 select * from emp order by emono asc limit 3, 3
group by 分组
-
案例：显示每种岗位的员工总数、平均工资
select count(*), avg(sal) from emp group by job -
案例：显示员工共总数，以及获得补助的员工数
select count(*) count(comm) from emp -
案例：显示管理者的总人数
select count(distinct mrg) from emp注意：count聚合函数是先执行内部表达式，在统计字数
-
案例：显示员工工资的最大差额
select user_name,max(salary) - min(salary) from emp -
案例：统计各个部门的平均工资，并且大于1000的，并且按照平均工资从高到低排序，取出前两行
select avg(sal) from emp group by depno having sal > 1000 order by desc limit 0,2
四、函数
合计函数
count 统计
- 统计总分大于250的人数
select count(*) from student
where (math+english+chinese) > 250
count (*) 和 count（列）区别
count（*）：返回满足条件的记录的行数
count（列）：统计满足条件某列有多少，但是排除为null的数据
问题1：用count(*)count(1)count(列名)谁好呢?
其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。
COUNT(*)是计算结果集中行的数量最常用的方法。它统计了表中所有非空的行数，包括所有列的值不为NULL的行。COUNT(1)也可以用来计算结果集中行的数量，但它的效率比COUNT(*)高，因为它不涉及到任何列的值。它只是统计了满足查询条件的结果集中的行数。COUNT(列名)则是计算指定列中非空值的数量。如果指定了一个包含NULL值的列，那么该列中的NULL值将被计算在内。因此，具体使用哪种方法取决于你的需求。如果你需要计算表中所有行的数量，包括NULL值，那么使用
COUNT(*)或COUNT(1)都可以。如果你只需要计算指定列中非空值的数量，那么使用COUNT(列名)。
问题2：能不能使用count(列名)替换count(*)?
不要使用 count(列名)来替代 count(),count() 是 SQL92 定义的标准统计行数的语法，跟数
据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
sum 函数
仅对数值起作用否则报错
- 案例：统计一个班级数学总成绩
select sum(math) from student
- 案例：统计一个班级语文成绩平均分
select sum(chinese) / count(*) from student
ave 平均分
- 求一个班级总分平均分
select avg(math+english+chinese) from student
max 和 min 使用
- 总分最高分和最低分
select max(math),min (math)
from student
字符串函数
| 函数 | 说明 |
|---|---|
| charset(str) | 返回字符串字符集 |
| concat(string2, […]) | 链接字符串 |
| instr(string, substring) | 返回substring在string总的位置 |
| ucase(string2) | 大写 |
| lcase(string2) | 小写 |
| left(string2, length) | 从左边截取字符 |
| length(string) | 长度 |
| replace(str, search_str, replace_str) | 替换 |
| strcmp(string1, string2) | 逐字比较字符串大小 |
| substring(str, position, [length]) | 从str的position开始取length长度 |
| ltrim(string) rtrim(string) trim | 去除空格 |
- 案例：去除ename的第一个字符转为小写
select concatl( case(substring(ename,1,1)), substring(ename,1) )
from emp
数学函数
| 函数 | 说明 |
|---|---|
| abs（num） | 绝对值 |
| bin（decimal_number） | 转成二进制 |
| ceiling（num） | 天花板函数 |
| conv（num,from _base,to_base） | 10进制转2进制 例：conv（8, 10, 2） |
| floor(num) | 地板函数 |
| format(num,decimal_places) | 保留小数 |
| hex(deciamlnum) | 十六进制 |
| least(number, number2, […]) | 最小值 |
| mod | 求余 |
| rang([seed]) | 返回随机数值（0 <= v <= 1.0） |
日期函数
| 函数 | 说明 |
|---|---|
| crrent_date() | 当前日期 |
| crrent_time() | 当前时间 |
| current_timestamp() | 当前时间戳 |
| last_day() | 当前月最后一天 |
date_add() 中的interval 后面可以是 year mintur second day等
- 案例：显示所有新闻信息，发布日期只显示 日期，不显示时间
select id, content, date(send_time)
from mes;
- 案例：请查询在10分钟内发布的新闻
select * from mes
where date_add(send_time, interval 10 minute) >= now()
- 案例：相差多少天
select datediff('2011-11-11','1990-01-01') from dual
- 案例：如果你能活到80随，求出你能活多少天
select datediff(date_add('1986-11-11',interval 80 year), now() )from dual
| 函数 | 说明 |
|---|---|
| timediff(date1,date2) | 两个时间差(多少小时多少分钟多少秒) |
| now() | 当前时间 |
| year|month|day(datetime) | 年月日 |
| unix_timestamp() | 返回秒数 |
| from_unixtime(秒数，‘%Y-%m-%s’) | 格式化 |
加密函数和系统函数
- 案例：查看用户
select user() from dual
- 案例：当前数据库
select database()
- 案例：加密
select md5('hsp') from dual
- 案例：加密（password）
select password('hso') from dual
流程控制函数
| 函数 | 说明 |
|---|---|
| if(expr,result1,result2) | 如果expr1为真返回result1，否则reuslt2 |
| ifnull(expr,result) | 如果expr为空则返回result |
| select case when expr1 then result1 when expr2 then result2 else expr3 end; | 只会返回第一个为真的表达式 |
- 如果user_name为 robber 那么就是本人，如果user_name other 那么就是其他
select user_name,(select case
when user_name = 'robber' then '本人'
else user_name then '其他' end
) as 'job'
form emp;
五、多表
注意：多表查询的条件最少要 【表的个数-1】
-
案例：显示部门号 10 的部门名、员工和工资
select ename,sal,dname from emp,dept where emp.deptno = emp.deptno and emp.deptno = 10 -
案例：显示各个员工姓名，工资，及其工资的级别
select ename,sal, from emp, salgrade -- where emp.sal >= salgrade.losal and emp.sal <= hisal where sal between losal and hisal
自连接
将一张表当作两张表来使用
-
思考题：显示公司员工名字和他的上级的名字
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno
单列子查询
-
案例：显示与SMITH同一部门的所有员工？
select * from emp where depno = ( select depno from emp where ename = 'SMITH' ) -
案例：查询和10号部门工作相同的员工的信息，但是不包含10自己的员工
select * from emp where job in ( select job from dep where depno = 10 ) and depno != 10 -
案例：找到各个种类中价格最高的商品名称
select goods_id,temp.cart_id ,goods_name,shop_price from ( select max(shop_price) as max_prince,cat_id from ecs_goods group by car_id ) temp, ecs_goods goods where temp.cart_id = goods.cart_id and temp.max_price = goods.shop_price
all any 操作符
-
案例：显示工资比部门30的所有员工的工资高的员工的信息
select * from emp where sal > all( select sal from emp where deptno = 30 ) select * from emp where sal > ( select max(sal) from emp where deptno = 30 ) -
案例：显示工资比部门30的其中一个员工的工资高的员工的信息
select * from emp where sal > any( select sal from emp where depno = 30 ) select * from emp where sal > ( select min(sal) from emp where depno = 30 )
多列子查询
-
案例：查询与smith的部门和岗位完全相同的所有雇员，并且不包括smith本人
select * from emp where (deptno,job) = ( select job,depno from emp where ename = 'smith' ) -
案例：和松江数学，英语，语文成绩完全相同的学生
select * from student where (math,english,chinese) = ( select math,english,chinese from student where ename = '宋江' )
练习
-
案例：查询每个员工工资高于本部门平均工资的人的信息
select * from ( select deptno,avg(sal) avg_sal from emp group by emptno ) temp, emp where emp.sal > temp.abg_sal and emp.deptno = temp.deptno -
案例：查询每个部门工资最高的人的信息
select * from emp where (depno, sal) in ( select deptno,max(sal) max_sal from emp group by depno ) select * from ( select deptno,max(sal) max_sal from emp group by deptno ) temp, emp where temp.deptno = emp.deptno and temp.max_sal = temp.sal -
案例：查询每个部门的信息（部门名，编号，地址）和人员数量
select dept.dname,dept.deptno,dept.loc,temp.person_num from ( select deptno,count(*) as person_num from emp group by deptno ) temp,dept where emp.deptno = dept.deptno
六、表复制
-
将emp表的记录复制到 my_test01
insert into my_test01 (id, 'name', sal, job, deptno) select empno,eame,sal,job,deptno from emp -
自我复制
insert into my_test01 select * from my_test01 -
面试题：删除表中重复的数据
1、创建一个临时表 2、将目标表去去重后插入临时表中 3、清空表中的数据 4、将临时表的数据插入目标表中 5、删除临时表 create table temp( id int, name varchar(255) ) insert into temp select distinct * from target drop target insert into target select * from temp drop table temp
七、外连接
-
左外链接（左侧的表完整显示）
select * from student left join exam on student.id = exam.id -
右外链接（右侧的表完整显示）
select * from student right join exam on student.id = exam.id -
案例：显示所有部门名称和这些部门的员工信息（名字和工作），同时列出哪些没有员工的部门
select emp.ename,emp.job from dept left join emp on dept.deptno = emp.deptno
八、约束
- 根据约束起的作用，约束可分为：
- NOT NULL 非空约束，规定某个字段不能为空
- UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
- PRIMARY KEY 主键(非空且唯一)约束
- FOREIGN KEY 外键约束
- CHECK 检查约束
- DEFAULT 默认值约束
primary key 主键
注意：
- primary key不能重复而且不能为null
- 一张表最多只有一个逐渐，但可以符合主键
- 主键的指定方式有两种
- 字段名 primary key
- 表定义最后 primary key
- 使用desc 查看表解构
-
添加主键
-- 单主键 create table temp( id INT primary key, name varchar(255) ) -- 复合主键 create table temp( id INT, name varchar(255), primary key (id, name) )注意：定了符合主键要同时满足【id和name】完全相同才会报错
auto_increment 自增长
注意：
- 一般自增长要和primary key（unique）使用
- 自增长修饰的字段整数型（很少使用小数）
- 自增长默认从1开始，你可以通过命令修改
alter table temp auto_increment = value- 如果你添加数据的时候制定了数据，就会根据指定数据增加
unique 和 not null 唯一
- unique特点
- 同一个表可以有多个唯一约束。
- 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
- 唯一性约束允许列值为空。
- 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
- MySQL会给唯一约束的列上默认创建一个唯一索引。
注意：
- 如果没有指定not null，则unique字段可以有多个null
- 一张表可以指定多个unique字段
create table temp(
id INT primary key,
user_name varchar(255) unique not null
)
alter table temp user_name add unique
foreign key 外键
用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有从表约束或者是unique，当定义外键约束后，要求外键列数据在主表的主键列存在或者是为null
列举：学生表和班级表
· 若王五存在11班，此时我们直接删除11班的班级表数据就会报错
外键的优劣性：
- 好处
- 保证了数据库数据的一致性和完整性
- 级联操作方便，减轻了程序代码量…
- 坏处
- **增加了工作的复杂性：**每次做DELETE或者UPDATE时都必须考虑外键约束，这样我们在开发和测试数据时会极其痛苦；并且外键的主从关系是固定的，当需求发生变化进行修改时会带来很多麻烦。
- **对分库分表不友好：**因为分库分表下外键是无法生效的。
注意：
- 外键必须是主键
- 表的类型是innodb
- 外键字段的类型要和主键一致
- 外键的数值必须在在主表字段中出现过
- 一旦建立主外表的关系，数据不能随意删除了
-- 学生表
create table my_stu(
id INT PRIMARY KEY,
stu_name VARCHAR(32) NOT NULL DEFAULT '',
class_id INT,
FOREIGN KEY (class_id) references my_class(id)
)
-- 班级表
create table my_class(
id INT PRIMARY KEY,
class_nane VARCHAR(32) UNIQUE NOT NULL,
)
check 检查
用于强制行数据必须满足的条件，假定在sal列上定义check约束，要求sal在1000~2000范围。
注意：oracle 和 sql server 均支持check，但是支持mysql5.7还不支持
create table temp(
id INT PRIMARY KEY,
sex VARCHAR(6) CHECK (sex in ('man','woman'))
)
商店销售案例
create table goods(
goods_id INT primary key,
goods_name VARCHAR(90) NOT NULL DEFAULT '',
unitprice DECIMAl(10,2) NOT NULL DEFAULT 0 CHECK (unitprice >= 1.0 AND unitprice <= 9999.99),
category VARCHAR(255) NOT NULL DEFAULT '',
provider VARCHAR(64) NOT NULL DEFAULT ''
)
create table customer(
customer_id INT PRIMARY KEY,
customer_name VARCHAR(255) NOT NULL DEFAULT '',
address VARCHAR(255) NOT NULL DEFAULT '',
email VARCHAR(255) UNIQUE NOT NULL,
sex CHAR(6) ENUM('男','女') NOT NULL,
card_id CHAR(18)
)
create table purchase(
order_id INT UNSIGNED PRIMARY KEY,
customer_id CHAR(8) NOT NULL DEFAULT '',
goods_id INT NOT NULL DEFAULT 0,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY goods_id REFERENCES goods(goods_id)
FOREIGN KEY customer_id REFERENCES customer(customer_id)
)
面试
面试1、为什么建表时，加 not null default ‘’ 或 default 0
- 不想让表中出现null值。
面试2、为什么不想要 null 的值
- 不好比较。null是一种特殊值，比较时只能用专门的is null 和 is not null来比较。碰到运算符，通常返回null。
- 效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ‘’ 或 default 0
面试3、带AUTO_INCREMENT约束的字段值是从1开始的吗？
-
在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。
-
设置自增属性（AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。
面试4、并不是每个表都可以任意选择存储引擎？
-
外键约束（FOREIGN KEY）不能跨引擎使用。
-
MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。
九、索引
添加索引
- 需要指定列添加索引
- 需要占用磁盘空间
CREATE INDEX empno_index on emp (empno)
索引机制
原因：找到了id = 1的数据以就会进行全盘扫描
索引的原理：
1、会形成一个索引二叉搜索树
2、对数据解构有影响（update、delete、insert），影响性能
索引类型
如何选择：如果某列的数值，是不会重复的，则优先考虑unique索引，否在选择使用普通索引
- 索引类型
- 主键索引，主键自动的为主键索引（primary key）
- 唯一索引（UNIQUE）
- 普通索引（INDEX）
- 全文索引（FULLTEXT）
-- 添加唯一索引
CREATE UNIQUE INDEX id_index on test (id);
-- 添加普通索引
CREATE INDEX id_index on test(od);
-- 添加普通索引2
ALTER TABLE TEST ADD INDEX id_index (id)
-- 添加主键索引
ALTER TABLE test ADD PRIMARY KEY (id)
-- 删除索引
DROP INDEX id_index on test
-- 删除主键索引
ALTER TABLE TEST DROP PRIMARY KEY
-- 查看索引
SHOW INDEX FROM test
SHOW INDEXES FROM test
SHOW KEYS FROM test
全文索引
- 主要针对文本的内容进行分词，加快查询速度
创建索引规则
- alter table temp (id INT, name text,fulltext index(name))engine=myisam;
-
较频繁的组为查询条件应该创建索引
SELECT * FROM EMP WHERE empno = 1 -
唯一性太差的字符段不适合单独创建索引，即使频繁作为查询条件
SELECT * FROM emp WHERE sex = '男' -
更新非常频繁的字段不适合创建索引
SELECT * FROM emp WHERE logincout = 1 -
不会出现在WEHRE不应该创建索引
十、事务
事务用于保证数据的一致性，它由一组相关的dml语句组件，该组的dml语句要么全部成功，要么全部失败，如：转账就用用来管理事务，用于保证数据的一致性
- 事务和锁
- 当事务操作时（dml语句），mysql会在表上加锁，放置其他用户改表的数据，这对用户来讲十非常重要的
- mysql几个重要操作
- start transaction：开启事务
- savepoint：设置保存点
- rollback to：回退事务
- rollback：退回全部事务
- commit
- 使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效]
事务管理
- 不开启事务，dml操作默认自动提交，不能回滚
- 没有设置保存点，会默认回退到事务开始的状态
- 可以设置多个保存点
- mysql的食物机制需要innodb的存储引擎才可以使用myisam不支持
- 开始一个食物 start transaction，set autocommit=off
-- 开启事务
start transaction
-- 设置保存点a
savepoint a
INSERT INTO test
values
(NULL,'Robber')
-- 设置保存点b
savepoint b
INSERT INTO test
values
(NULL,'Robber')
-- 回退到a
rollback to a
-- 回退到开启事务的地方
rollback
隔离级别
- 多个连接开启各自事务操作数据中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据的准确性。
- 如果不考虑隔离性，可能会引发如下问题：
- 脏读
- 不可重复读
- 幻读
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 加锁读 |
|---|---|---|---|---|
| 读未提交（Read uncommitted） | ✔ | ✔ | ✔ | 不加锁 |
| 读已提交(Read committed) | ❌ | ✔ | ✔ | 不加锁 |
| 可重复读（Repeatable read） | ❌ | ❌ | ❌ | 不加锁 |
| 可串行化(Serialiable) | ❌ | ❌ | ❌ | 加锁 |
脏读
- 当一个事务读取另一个事务尚未提交的修改时，产生脏读
不可重复读
- 同一查询在同一事务中多次进行，由于其他提交事务所作的修改或删除，每次返回不同的结果集，此时发生不可重复读
幻读
- 统一查询在统一事务中多次进行，由于其他提交事务所作的插入操作，每次返回会不同的结果集，此时产生幻读
隔离级别指令
-
查看当前隔离级别
select @@tx_isolation -
查看系统当前隔离级别
select @@global.tx_isolation -
设置当前会话隔离级别
set session transaction isolation level repeatable read -
设置系统当前隔离级别
set global transaction isolation level repeatable read -
mysql 默认的事务隔离级别十 repeatable read，一般情况下，没有特殊要求，没有必要修改（因为改级别可以满足大部门项目需求）
ACID
- 原子性（Atomicity）
- 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要
么都不发生。
- 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要
- 一致性（Consistency）
- 事务必须使数据库从一个一致性状态变换到另外一个一致性状态
- 隔离性（Isolation）
- 事务的隔离形式多个用户并发访问数据库时，数据库为每一个用户默认开启一个事务，不能被其他事务的操作数据所干扰，多个事务之间要相互隔离
- 持久化
- 持久化是一个事务一旦被提交，它对数据库的数据的改变就是永久性的，接下来即使是数据库发生故障也不应该对其有影响
十一、存储引擎
- MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
- 支持事务
- 支持外键
- 支持行级锁
- InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
- 添加速度快
- 不支持外键和事务
- 支持表级锁
- MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉, 表的结构还在。
- 数据存储在内存中（关闭mysql服务，数据丢失）
- 执行速度很快（没有IO）
- 默认支持索引（hash表）
- 如何选择表的粗出引擎
- 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择,速度快
- 如果需要支持事务，选择InnoDB
- Memory 存储引擎就是将数据存储在内存中，由于没有磁盘I/0的等待速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法 用户的在线状态）
十二、视图
– 视图是一个虚拟表，其内容由查询定义。同基表（真实表）一样，试图包含列，其数据来自对应的真是表
细节：
- 通过视图修改的数据会相互影响
- 只会产生结构文件，不会生成数据文件
- 视图中可以再使用视图
基本使用
- crate vie 试图名 as select 语句
- alter view 试图名 as select 语句
- 查看视图结构：
show create view 视图名 - drop view 视图名1，试图名2
- 查看视图：
select * from 视图名称
-
案例：创建一个视图，支撑只能查询emp（empno,ename）
create view emp_view as select * empno,ename from employees
视图最佳时间
- 安全：一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
- 性能：关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时数据库查询通常会用到连接==(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN==查询数据。
- 灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很
多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接
映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
-
案例：针对emp,dept 和 salgrade 三张表，创建一个视图只显示编号、名字、部门名称和薪水级别
create view temp as SELECT * FROM emp,dept,salgrade WHERE emp.deptno = dept.deptno AND emp.sal BETWEEN losal AND hisal
十三、MySql管理
原因：当我们做项目开发的时候，可以根据不同的开发人员，赋予不同的mysql操作权限权限
细节：
- 如果不指定host，则为%，表示用户可以远程登录
- create user ‘xx’@'xxx.xxx.xxx.%'表示用户可以在这个网段都可以登录
- 在删除用户的时候，如果host不是%，需要明确指定’用户’@‘地址’
-
创建新用户
CREATE USER 'robber'@'localhost' IDENTIFIED BY '12345' -- 查看 mysql.user 表 SHOW * FROM mysql.user -
删除用户
DROP USER 'robber'@'localhost' -
修改密码
-- 修改自己的密码 SET PASSWORD = PASSWORD('123') -- 修改别人的密码，需要权限 SET PASSWORD FOR 'robber'@'localhost' = PASSWORD('123456')
用户权限授权
-
基本语法
GRANT 权限列表 on 库.表 to 'robber'@'localhost' [IDENTIFIED BY '密码'] -
说明
- 列表多个权限用逗号分开
- grant select on …
- grant select,delete,create on …
- grant all [privileges] on… //赋予所有权限
- 特别说明
- 星.星：代表本系统中的所有数据库的对象（表，视图，存储过程）
- 库.星：表达某个数据库中的所有数据库对象(表，视图，存储过程)
- identified by：可以省略，也可以写出来
- 如果用户存在，就是修改该用户的密码
- 如果用户不存在，就创建用户
- 列表多个权限用逗号分开
-
回收权限
revoke 权限列表 on 库.对象名 from '用户名'@'登录位置' -
权限生成指令
-- 如果不起效果则需要执行 flush privileges
用户管理练习
- 创建一个用户(你的名字，拼音)，密码 123，并且只可以从本地登录，不让远程登录mysql
- 创建库和表 tesgb 下的 news 表 ，要求: 使用root 用户创建.
- 给用户分配查看 hews 表和添加数据的权限
- 测试看看用户是否只有这几个权限
- 修改密码为 abc ，要求: 使用root 用户完成
- 重新登录
- 使用 root 用户篇除你的用户
-- 创建用户
create user 'robber'@'localhost' identified by '123'
-- 创建表
create table news(
)
-- 赋予权限
grant select insert on testdb.news to 'robber'@'localhost'
-- 修改密码
set password for 'robber'@'localhost' = password ('abc')
-- 移除用户
drop user 'robber'@'localhost'
十四、练习
-
使用简单查询语句完成
- 显示所有部门名称。
- 显示所有雇员名及其全年收入 13月(工资+补助),并指定列别名”年收入“
-
限制查询数据
- 显示工资超过2850的雇员姓名和工资
- 显示工资不在1500到2850之间的所有雇员名及工资
- 显示编号为7566的雇员姓名及所在部门编号。
- 显示部门10和30中工资超过1500的雇员名及工资
- 显示无管理者的雇员名及岗位
-- 1. 显示工资超过2850的雇员姓名和工资 SELECT ename,sal FROM emp WHERE sal > 2850 -- 2. 显示工资不在1500到2850之间的所有雇员名及工资 SELECT ename,sal FROM emp WHERE !(sal BETWEEN 1500 AND 2850) -- 3. 显示编号为7566的雇员姓名及所在部门编号。 SELECT ename,deptno FROM emp WHERE empno = 7566 -- 4. 显示部门10和30中工资超过1500的雇员名及工资 SELECT ename,sal,deptno FROM emp WHERE sal > 1500 AND deptno IN (10,30) -- 5. 显示无管理者的雇员名及岗位 SELECT ename,job,mgr FROM emp WHERE mgr IS NULL -
排序数据。
- 显示在1991年2月1日到1991年5月1日之间雇用的雇员名,岗位及雇佣日期,并以庭佣日期进行
- 显示获得补助的所有雇员名,工资及补助,并以工资降序排序
-- 1. 显示在1991年2月1日到1991年5月1日之间雇用的雇员名,岗位及雇佣日期,并以庭佣日期进行 SELECT ename,job,hiredate FROM emp WHERE DATEDIFF(hiredate,'1991-02-01') <= DATEDIFF('1991-05-01','1991-02-01') AND DATEDIFF(hiredate,'1991-02-01') >= 0 ORDER BY hiredate DESC SELECT ename,job,hiredate FROM emp WHERE hiredate >= '1991-02-01' AND hiredate <= '1991-05-01' ORDER BY hiredate DESC -- 2. 显示获得补助的所有雇员名,工资及补助,并以工资降序排序 SELECT ename,sal,comm FROM emp WHERE comm IS NOT NULL ORDER BY sal DESC -
员工表
- 选择部门30中的所有员工
- 列出所有办事员(CLERK)的姓名，编号和部门编号.
- 找出佣金高于薪金的员工.
- 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
- 找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
- 找出收取佣金的员工的不同工作
- 找出不收取佣金或收取的佣金低于100的员工
- 找出各月倒数第3天受雇的所有员工
- 找出早于12年前受雇的员工
- 以首字母小写的方式显示所有员工的姓名
- 显示正好为5个字符的员工的姓名
-- 1. 选择部门30中的所有员工 SELECT * FROM emp WHERE deptno = 30 -- 2. 列出所有办事员(CLERK)的姓名，编号和部门编号. SELECT ename,empno,deptno FROM emp WHERE job = 'CLERK' -- 3. 找出佣金高于薪金的员工. SELECT * FROM emp WHERE IF(comm ,comm,0)> sal -- 4. 找出佣金高于薪金60%的员工 SELECT * FROM emp WHERE IF(comm ,comm,0) > sal * 0.6 -- 5. 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料. SELECT * FROM emp WHERE deptno = 10 AND job = 'MANAGER' OR deptno = 20 AND job = 'CLERK' -- 6. 找出部门10中所有经理(MANAGER), -- 部门20中所有办事员(CLERK), -- 还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.------ SELECT * FROM emp WHERE deptno = 10 AND job = 'MANAGER' OR deptno = 20 AND job = 'CLERK' OR (job != 'MANAGER' AND job != 'CLERK' AND sal >= 2000) -- 7. 找出收取佣金的员工的不同工作.------ SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL -- 8. 找出不收取佣金或收取的佣金低于100的员工- SELECT * FROM emp WHERE comm IS NULL OR comm < 100 -- 9. 找出各月倒数第3天受雇的所有员工 SELECT DAY(LAST_DAY(NOW())) FROM DUAL SELECT *,LAST_DAY(hiredate) - 2 FROM emp WHERE DAY(hiredate) = DAY(LAST_DAY(hiredate)) - 2 -- 10. 找出早于12年前受雇的员工 SELECT * FROM emp WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW() -- 11. 以首字母小写的方式显示所有员工的姓名 SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2,LENGTH(ename))) FROM emp -- 12.显示正好为5个字符的员工的姓名 SELECT * FROM emp WHERE ename LIKE '_____' -
emp表
- 显示不带有"R"的员工的姓名
- 显示所有员工姓名的前三个字符.
- 显示所有员工的姓名,用a替换所有"A"
- 显示满10年服务年限的员工的姓名和受雇日期
- 显示员工的详细资料 按姓名排序.
- 显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面工作和薪金,按工作降序排序,若工作相同则按薪金排序.
- 显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年分
- 显示所有员工的姓名、的员工排在最前面.
- 显示在一个月为30天的情况所有员工的日薪金,忽略余数
- 找出在(任何年份的)2月受聘的所有员工-
- 对于每个员工,显示其加入公司的天数
- 显示姓名字段的任何位置包含"A"的所有员工的姓名
- 以年月日的方式显示所有员工的服务年限。(大概)
-- 1. 显示不带有"R"的员工的姓名 SELECT * FROM emp WHERE !(ename LIKE 'R%') -- 2. 显示所有员工姓名的前三个字符. SELECT SUBSTRING(ename,1,3) '姓名' FROM emp -- 3. 显示所有员工的姓名,用a替换所有"A" SELECT REPLACE(ename,'A','a') FROM emp -- 4. 显示满10年服务年限的员工的姓名和受雇日期 SELECT ename,hiredate FROM emp WHERE hiredate <= DATE_ADD(NOW(),INTERVAL -10 YEAR) -- 5. 显示员工的详细资料 按姓名排序. SELECT * FROM emp ORDER BY ename DESC -- 6. 显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面工作和薪金, SELECT ename,hiredate,DATEDIFF(NOW(),hiredate) temp,sal,job FROM emp ORDER BY temp DESC -- 7. 显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年分 SELECT ename,MONTH(hiredate) month_date,YEAR(hiredate) year_date FROM emp ORDER BY month_date DESC, year_date ASC -- 9. 显示在一个月为30天的情况所有员工的日薪金,忽略余数 SELECT ROUND(sal / 30) FROM emp -- 10. 找出在(任何年份的)2月受聘的所有员工- SELECT * FROM emp WHERE MONTH(hiredate) = 2 -- 11. 对于每个员工,显示其加入公司的天数 SELECT DATEDIFF(NOW(),hiredate) FROM emp -- 12. 显示姓名字段的任何位置包含"A"的所有员工的姓名 SELECT * FROM emp WHERE ename LIKE '%A%' -- 13. 以年月日的方式显示所有员工的服务年限。(大概) SELECT CONCAT(FLOOR(DATEDIFF(NOW(),hiredate) / 365),'年',FLOOR(DATEDIFF(NOW(),hiredate) / 365 % 12),'月',FLOOR(DATEDIFF(NOW(),hiredate) / 365 % 12 % 30),'天') '服务年限' FROM emp -
emp + dept
- 列出至少有一个员工的所有部门
- 列出薪金比“SMITH”多的所有员工。
- 列出受雇日期晚于其直接上级的所有员工
- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
- 列出所有“CLERK”(办事员)的姓名及其部门名称。
- 列出最低薪金大于1500的各种工作。
- 列出在部门“SALES” (销售部) 工作的员工的姓名
- 列出薪金高于公司平均薪金的所有员工。
-- 1. 列出至少有一个员工的所有部门 SELECT DISTINCT dept.dname,dept.deptno FROM dept,emp WHERE dept.deptno = emp.deptno -- 2. 列出薪金比“SMITH”多的所有员工。 SELECT * FROM emp WHERE sal + comm > ( SELECT IFNULL(0,comm) + sal FROM emp WHERE ename = 'SMITH' ) -- 3. 列出受雇日期晚于其直接上级的所有员工 SELECT e1.ename,e1.hiredate,e1.mgr,e2.ename,e2.hiredate,e2.empno FROM emp e1,emp e2 WHERE e1.mgr = e2.empno AND e1.hiredate > e2.hiredate -- 4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门 SELECT * FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno -- 5. 列出所有“CLERK”(办事员)的姓名及其部门名称。 SELECT ename,dname,job FROM emp,dept WHERE emp.deptno = dept.deptno AND job = 'CLERK' -- 6. 列出最低薪金大于1500的各种工作。 SELECT MIN(sal),job FROM emp GROUP BY job HAVING MIN(sal) > 1500 -- 7. 列出在部门“SALES” (销售部) 工作的员工的姓名 SELECT deptno FROM dept WHERE dname = 'SALES' SELECT ename FROM emp WHERE deptno = ( SELECT deptno FROM dept WHERE dname = 'SALES' ) -- 8. 列出薪金高于公司平均薪金的所有员工。 SELECT * FROM emp WHERE sal > ( SELECT AVG(sal) FROM emp ) -
emp + dept
- 列出与“SCOTT”从事相同工作的所有员工
- 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金
- 列出在每个部门工作的员工数量、平均工资和平均服务期限。
- 列出所有员工的姓名、部门名称和工资
- 列出所有部门的详细信息和部门人数。
- 列出各种工作的最低工资列出MANAGER (经理)的最低薪金
- 列出所有员工的年工资,按年薪从低到高排序。
-- 1. 列出与“SCOTT”从事相同工作的所有员工 SELECT * FROM emp WHERE deptno = ( SELECT deptno FROM emp WHERE ename = 'SCOTT' ) -- 2. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金 SELECT ename,sal FROM emp WHERE sal > ALL ( SELECT sal + IFNULL(0,comm) FROM emp WHERE deptno = 30 ) -- 3. 列出在每个部门工作的员工数量、平均工资和平均服务期限。 SELECT COUNT(*),AVG(sal),FLOOR(AVG(DATEDIFF(NOW(),hiredate))) FROM emp GROUP BY deptno -- 4. 列出所有员工的姓名、部门名称和工资 SELECT ename,dname,sal FROM emp,dept WHERE emp.deptno = dept.deptno -- 5. 列出所有部门的详细信息和部门人数。 SELECT dept.dname,COUNT(*) FROM dept,emp WHERE dept.deptno = emp.deptno GROUP BY dept.deptno SELECT COUNT(*), deptno FROM emp GROUP BY deptno SELECT temp.person_sum,dept.* FROM dept,( SELECT COUNT(*) person_sum, deptno FROM emp GROUP BY deptno ) temp WHERE temp.deptno = dept.deptno -- 6. 列出各种工作的最低工资 SELECT MIN(sal) FROM emp GROUP BY job -- 列出MANAGER (经理)的最低薪金 SELECT MIN(sal) FROM emp WHERE job = 'MANAGER' -- 7. 列出所有员工的年工资,按年薪从低到高排序。 SELECT (sal+IFNULL(0,comm)) *12 salary FROM emp ORDER BY salary ASC -
建表
1777
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
