Mysql笔记
创建数据库：create database 数据库名 default character set 字符编码 collate 字符编码校验规则
create database demo default character set utf8 collate utf8_general_ci;
使用Navicat导入三张表
DROP TABLE IF EXISTS EMP; DROP TABLE IF EXISTS DEPT; DROP TABLE IF EXISTS SALGRADE; CREATE TABLE DEPT (DEPTNO int(2) not null , DNAME VARCHAR(14) , LOC VARCHAR(13), primary key (DEPTNO) ); CREATE TABLE EMP (EMPNO int(4) not null , ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT(4), HIREDATE DATE DEFAULT NULL, SAL DOUBLE(7,2), COMM DOUBLE(7,2), primary key (EMPNO), DEPTNO INT(2) ) ; CREATE TABLE SALGRADE ( GRADE INT, LOSAL INT, HISAL INT ); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); commit; INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17' , 800, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20' , 1600, 300, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22' , 1250, 500, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02' , 2975, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28' , 1250, 1400, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01' , 2850, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09' , 2450, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19' , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17' , 5000, NULL, 10); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08' , 1500, 0, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788, '1987-05-23' , 1100, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03' , 950, NULL, 30); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03' , 3000, NULL, 20); INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23' , 1300, NULL, 10); commit; INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); commit;
一、表的查询
查看当前存在的所有数据库：show databases
修改数据库的字符编码：alter database 数据库名 default character set 修改的字符编码
alter database demo default character set gbk;
查看某表中的全部数据：select * from 表名
select * from demo;
不看表数据只查看表结构：desc 表名
DESC his_role;
简单查询：select 字段名 from 表名
查询部门名字？
select dname from dept;
查询多个字段：select 字段名1 ，字段名2 from 表名
select id , drug_type from his_drug;
删除表：drop table 表名
drop table demo;
给查询的列显示别名：select 列明 as 别名 from 表名（as可以用空格代替）
select id as ffff from his_drug；
注：字段可以使用数学表达式
select ename,sal * 12 from emp
条件查询：
查询薪资等于800的员工姓名和编号 select empno , ename from emp where sal = 800; 查询薪资大于800的员工姓名和编号 select empno , ename from emp where sal > 800； 查询薪资在800到1000之间的员工姓名和编号 select empno ，ename from emp where sal >= 800 and sal <= 1000; 或 select empno , ename from emp where between 800 and 1000; 查询空值时需要使用is， 例如查询工资为空的员工姓名和编号 select empno , ename from emp where sal is null; 查询工资不为空的员工姓名和编号 select empno , ename from emp where sal is not null; 查询工作岗位是MANAGER和SALESMAN的员工 select empno,ename,job frome emp where job = 'MANAGER' or jbo = 'SALESMAN'； in表示包含 相当于多个or （in不适一个区间 in后面跟的是具体的值） 查询工作岗位是MANAGER和SALESMAN的员工 select empno,ename,job from emp where job in ('manager' , 'salesman'); not in表示除了这几个值以外的数据 查询工作岗位除了MANAGER和SALESMAN的员工 select empno,ename,job from emp where job not in ('manager' , 'salesman');
模糊查询
like用做模糊查询 支持%和下划线匹配 % 匹配任意多个字符 _ 匹配任意一个字符
例子：找出名字中含有o字母的
select ename from emp where ename like '%o%';
查询结果排序：order by 默认是升序
查询所有员工薪资，并排序(默认是升序) select ename ， sal from order by sal;
降序:desc（升序是asc，不写也行）
select ename ， sal from order by sal desc;
多个字段排序。sal在前起主导 只有当sal相等时 才会启用ename排序
查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，按照名字升序排列
select ename , sal from emp order by sal asc , ename asc;
数据处理函数，单行处理函数
特点：一个输入对应一个输出
lower 转换小写 select lower(ename) from emp; upper 转换大写 select upper(ename) from emp; substr 取子字符串（起始下标从1开始） select substr(ename, 1,3) from emp; 例子：找出名字第一个字母为A的员工 select ename from emp where substr(ename,1,1)='A'; concat 字符串拼接 select concat (empno,ename) from emp; length 取长度 select length(ename) from emp; trim 去空格 select * from emp where name = trim(' KING'); round 四舍五入 select round(123.412) from emp; rand()生成随机数 select rand() from emp; ifnull空处理函数(ifnull(comm,0)) 在所有数据库中 只要有null参与的数学运算 最终结果都是null 例子：计算每个员工的年薪 select ename , (sal + ifnull(comm,0)) * 12 as result from emp
数据处理函数，分组函数，多行处理函数（输入多行 输出一行）
注意：分组函数自动忽略null，不需要提前对null进行处理
分组函数不能直接使用在where子句中
count 计数
例子：计算员工数量
select count(ename) from emp;
分组函数中count(*)和count(具体字段)有什么区别：
count(具体字段):表示统计该字段下所有部位NULL的元素总数
count(*):统计表中的总行数(只有有一行数据count则++)
sum 求和
例子：计算工资和
select sum(sal) from emp；
avg 平均值
例子计算平均工资
select avg(sal) from emp；
max 最大值
例子：找出最高工资
select ename , max(sal) from emp ;
min 最小值
例子 找出最低工资
select ename , min(sal) from emp ;
分组查询：在实际应用中 可能需要先进行分组 然后再对每一组数据进行操作 这个时候需要用到分组查询
关键字的执行顺序：select...from...where...group by...order by... 1.from 2.where 3.group by 4.select 5.order by groub by 分组查询 例子：找出每个工作岗位的工资和？ select job , sum(sal) from emp group by job; 找出每个部门，不同工作岗位的最高薪资 select deptno , job , max(sal) from emp group by DEPTNO,job; having可以对分完组之后的数据进行进一步过滤：having不能单独使用 必须和group by 联合使用 例子：找出每个部门的最高薪资，要求显示大于3000的？ select deptno , max(sal) from emp group by deptno having max(sal)>3000 where和having都可以使用时 优先选择where 优化后： select deptno , max(sal) from emp where sal > 3000 group by deptno;
distinct查询结果去重
1.查询有哪些工作岗位
select distinct job from emp;
2.统计工作岗位数量
select count(distinct job) from emp;
distinct出现在两个字段之前表示两个字段联合起来去重
连接查询
当两张表进行连接查询 没有任何条件限制的时候 最终查询结果条数 是两张表条数的乘积。
sql92：缺点 结构不清晰 查询每个员工所在的部门名称？ select ename , dname from emp , dept where emp.deptno = dept.deptno; 性能优化： select e.ename , d.dname from emp e , dept d where e.deptno = d.deptno; sql99：join...on...优点：表连接条件是独立的 select e.ename , d.dname from emp e join dept d on e.DEPTNO = d.DEPTNO
内连接，等值连接：inner join ... on ...(inner可以省略 带着可读性好一些)
查询每个员工所在的部门名称，显示员工名和部门名？
select e.ename , d.dname from emp e inner join dept d on e.DEPTNO = d.DEPTNO
内连接，非等值连接：
找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？ select ename , sal , grade from emp inner join salgrade on losal < sal and hisal > sal order by grade asc; 性能优化： select e.ename , e.sal , s.grade from emp e inner join salgrade s on s.losal < e.sal and s.hisal > e.sal order by grade asc;
内连接，自连接。技巧：一张表看做两张表
select a.ename as '员工', b.ename as'领导' from emp a join emp b on a.mgr = b.empno;
外连接，右外连接：right join 表示将join关键字右边的这张表看成主表，主要是为了这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接中 两张表连接产生了主次关系。与之相同的还有左外连接：left join。
查询所有员工和部门，无人部门也要显示？ select e.ename , d.deptno from emp e right outer join dept d on e.deptno = d.deptno; outer可以省略 带着可读性强
例子：查询每个员工的上级领导，要求显示所有员工的名字和领导名
select a.ename as '员工' , b.ename as '领导' from emp a left outer join emp b on a.mgr = b.empno;
多表连接：使用多个join on
找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级？ select e.ename ,e.sal , d.dname , s.grade from emp e inner join dept d on e.deptno = d.deptno inner join salgrade s on e.sal > s.losal and e.sal < s.hisal;
子查询：select语句中嵌套select语句，被嵌套的语句称为子查询。
例子：找出比最低工资高的员工姓名和工资？ 第一步：查询最低工作 select min(sal) from emp; 第二步：找出>800的 select ename , sal from emp where sal > 800; 第三步：合并 select ename , sal from emp where sal > (select min(sal) from emp);
from子查询：from后面的子查询可以将子查询的查询结果当做一张临时表
例子:找出每个岗位的平均高工资的薪资等级？ 第一步：查出每个岗位的平均工资 select job , avg(sal) from emp group by job； 把查询的结果视为临时表t 第二步：在当前查询的结果表t中找到工资等级 select t.* , s.grade from (select job , avg(sal) as avgsal from emp group by job ) t join salgrade s on t.avgsal between s. losal and s. hisal
select子查询：select嵌套的子查询如果多余一条会报错
union合并查询结果集，使用union时要保证两个结果集列数相同。oracle要求列和列的数据类型也要相同
例子：查询工作岗位是MANAGER和SALESMAN的员工 使用or： select ename , job from emp where job = 'manager' or job = 'salesman'; 或，使用in select ename , job from emp where job in ('manager', 'salesman'); 使用union合并结果集 select ename , job from emp where job = 'manager' union select ename , job from emp where job = 'salesman';
性能：union的效率高一些 对于表连接来说 每连接一次新表 则匹配次数满足笛卡尔积 指数翻倍 但是union可以减少匹配次数。
a连接b连接c a 10条记录 b 10条记录 c 10条记录 使用表连接，匹配次数为：10*10*10 = 1000次 使用union，匹配次数为10*10 + 10*10 = 200次
limit：将查询结果集的一部门取出来，通常在分页查询中使用。
例子：按照薪资降序，取出薪资最高的五名员工。 select ename , sal from emp order by sal desc limit 0,5;
0表示起始位置5表示长度
例子2：取出薪资排名在3到5的员工
select ename , sal from emp order by sal desc limit 2,3;
2表示从第三条记录开始 3表示长度
limit通用分页：每页显示pageSize条记录，第pageNo页：(pageNo - 1) * pageSize , pageSize
二、表的创建
建表的语法格式：
create table 表名(字段名1 数据类型 , 字段名2 数据类型，....);
mysql的数据类型：
varchar：可变长度字符串（根据实际的数据长度 动态分配空间） 优点：节省空间 缺点：需要动态分配空间，速度慢。 char：定长字符串（分配固定长度空间存储数据） 优点：不需要动态分配空间 速度快 缺点：使用不当可能会导致空间的浪费。 int：整型，等同于java中的int bigint：长整型，等同于java中的long float：单精度浮点型数据 double：双精度浮点型数据 date：短日期类型 datetime：长日期类型 clob：字符大对象 最多可以存储4G大字符串（超过255字符的都要采用字符大对象来存储） blob：二进制大对象，专门用来存储 图片、声音、视频等流媒体数据。blob字段上插入数据时需要使用IO流
例子：创建一个学生表
create table student(no int,name varchar(32),sex char,age int,email varchar(100))
删除表：drop
删除学生表（如果不存在会报错）： drop table student; 如果学生表存在的话，删除（如果表不存在也不会报错）： drop table if exists student;
default：创建表时添加默认值
创建学生表且使性别默认值为m create table student(no int,name varchar(32),sex char(1) default 'm', age int(3),email varchar(255));
insert into ：向表中插入数据，insert into 表名(字段名1,字段名2,字段名3...) values (值1,值2,值3...);
insert into student(no,name,sex,age,email)values(1,'张三','男',12,'22@qq.com')
注意：insert into 省略字段名需要把后面的值都写上。
format：格式化数字 format(数字,'格式')
select ename,format(sal,'$999,999') as sal from emp;
str_to_date：将字符串varchar类型转换成date类型。
格式：str_to_date('字符串日期','日期格式')，mysql的日期格式：
%Y:年 %m:月 %d:日 %h:时 %i:分 %s:秒
案例，插入学生生日 1.创建学生表 create table student1(name varchar(32) , age int , birth date); 2.插入学生张三 insert into student1(name,age,birth) value('张三',18,str_to_date('2000-01-02','%Y-%m-%d'))
注意：如果日期格式为：%Y-%m-%d格式则不需要使用str_to_date函数。
date_format：将data类型转换成具有一定格式的varchar字符串类型。
将日期类型转换为字符串并输出： select age,name,date_format(birth,'%Y/%m/%d')as birth from student1
若不使用日期格式化 sql会进行默认的日期格式化，自动将数据库中的date类型转换成varchar类型，并且采用的格式是musql的默认日期格式：'%Y-%m-%d'
三、表的修改
语法格式：update 表名 set 字段名1=值1,字段名2=值2,...where 条件;
注意：没有条件限制会造成所有数据全部更新
update student1 set name = 'zhangsan' where name = '张三';
四、删除数据
语法格式：delete from 表名where 条件;
注意：没有条件，整张表的数据会全部删除。
delete from student1 where name = 'zhangsan';
五、其他
1.insert语句可以一次插入多条数据
insert into student1(name,age,birth) value('zhangsan',12,'1999-01-02'),('lisi',12,'1999-01-02');
2.复制表
create table emp2 as select * from emp
原理：将一个查询结果当成一张新表创建 可以完成表的快速复制
3.truncate语句删除：删除效率高 物理删除 不支持回滚
truncate student1;
六、创建表约束
约束：在创建表时给表加上一些约束，以此来保证表中数据的完整性和有效性
1）非空约束：not null，约束的字段不能为null，not null只有列级约束 没有表级约束
例子：创建vip表，使id不能为空 create table vip(id int not null,name varchar(255),sex char(5));
2）唯一性约束：unique,约束的字段不能重复 但是可以为null
例子：创建VIP表使name具有唯一性。 drop table if exists vip; create table vip(id int not null , name varchar(255) unique , sex varchar(5));
例子2：创建VIP表使name和id联合起来具有唯一性 drop table if exists vip; create table vip(id int not null, name varchar(255) , sex varchar(5), unique(name,id));
注：在mysql中，如果一个字段同时被not null 和unique约束的话 该字段自动变成主键字段（oracle不存在这种方式）
3）主键约束：primary key，主键值是每一行记录的唯一标识。
任何一张表都应该有主键，没有主键的表是无效的。
主键的特征：unique、not null 不能为空，也不能重复
例子：创建vip表，使id为主键 drop table if exists vip; create table vip(id int primary key, name varchar(255) , sex varchar(5), unique(name,id)); 错误1：不能重复 insert into vip(id , name ,sex) values(1,'zhangsan2','男') > 1062 - Duplicate entry '1' for key 'PRIMARY'. 错误2：不能为空 insert into vip(name) values('ss') > 1364 - Field 'id' doesn't have a default value. 也可以使用表级约束添加主键 create table vip(id int , name varchar(255) unique, sex char(5) , primary key(id)); 复合主键 create table vip (id int , name varchar(255) , sex char(5) ,primary key (id,name)); 自增主键 create table vip(id int primary key auto_increment , name varchar(255) , sex char(5));
注意：一张表主键只能有一个，主键值建议使用 int bigint char等定长值
4）外键约束：foreign key
若添加了外键约束 删除表时应先删除子表 再删除父表。
创建班级表 create table class(classno int primary key, classname varchar(255)); 创建学生表 create table student(no int primary key auto_increment, name varchar(255) , cno int, foreign key(cno) references class(classno))
外键不一定是主键 但是必须具有unique约束
检查约束：check（Oracle支持）
略
事务：
一个事务就是一个完整的业务逻辑。
只有DML语句才有事务，其他语句与事务无关(insert/delete/updata)。
其实就是批量的DML语句同时成功或同时失败。
提交事务：commit;语句
回滚事务：rollback;语句（只能回滚到上一次的提交点）
mysql默认情况下是自动提交事务的，每执行一条DML语句自动提交一次。
mysql中关闭自动提交机制：start transaction;
事务的特性：
原子性：事务是最小的工作单元，不可再分。
一致性：所有事务要求，要同一事务中，所有的操作必须同时成功或同时失败以保证数据的一致性
隔离性：两个事务间具有一定的隔离。
持久性：事务最终结束的保障，事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。
事务隔离性的级别：
1.读未提交：read uncommitted（没有提交就能读到）
最低的隔离级别，事务A可以读取到事务B未提交的数据。脏读现象。这种级别是理论上的，大多数数据库都是比这种级别高。
2.读已提交：read committed （提交之后才能读到）
事务A只能读取到事务B提交之后的数据，这种隔离级别解决了脏读现象。不可重复读取数据。
在事务开启后，第一次读到的数据时3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条。3不等于4称为不可重复读取。
这种隔离级别是比较真实的数据，每一次读到的数据绝对真实，oracle默认的级别就是这个。
3.可重复读：repeatable read (提交之后也读不到，永远读取到的都是刚开启事务时的数据)
事务A开启后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变。
缺点：每一次读到的数据都是幻想，不够真实。
mysql的默认级别
4.序列化/串行化：serializable
这是最高隔离级别，效率最低，解决了所有问题
每一次读取到的数据都是最真实的，并且效率是最低的。
查看隔离级别：SEKECT @@tx_isolation;
设置隔离级别：set globle transaction isolation level 隔离级别名称;
索引
什么是索引：在数据库表的字段上添加，为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引。
索引可以简单理解为“排好序的快速查找数据结构“
