MySQL笔记整理

最新推荐文章于 2023-02-25 20:24:29 发布

dingxiaozhuang

最新推荐文章于 2023-02-25 20:24:29 发布

阅读量320

点赞数 1

分类专栏： MySql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/Judith__/article/details/109308317

版权

MySql 专栏收录该内容

1 篇文章

订阅专栏

1.数据库简介
（一）
1.文件存储
弊端：缺乏管理，不易修改、分析和共享
2.数据库是按照数据结构来组织的
3.DBMS管理数据库软件。
4.关系型数据库简介：描述两个关系之间的管理和对应
5.主要的关系型数据库：Oracle（数据库大）不是开源，大公司使用；MySQL（开源免费），也需要维护；SqlServer实际开发过程中使用的比较少
6.主流关系型数据库：
JDBC代码过多，但速度快，mybatis性能稍微差点，速度慢点
7.IO流文件存储的弊端（效率低）
8.SQL基础
SQL概述，创建、修改、操作表单数据（DML）
9.什么是DB、DBMS
DB：数据库；DBMS数据库管理系统，在关系型数据库中创建数据库
（二）

查看数据库：cmd输入mysql -u root-p
退出：exit
查看所有数据库：show database;
进入数据库：（1）选择数据库（use 数据库名称）
查看该数据库中的所有表：show tables;
查看表结构：show create table 表名；
创建一个库：create database 数据库名；（例：create database ykt;）
使用这个数据库:use 数据库名;（例：use ykt;）
创建数据库并指定字符集：create table 库名 character set utf8或gbk;
如果不设置utf8或gbk是插入不进去中文的
创建表：create table 表名（id int,name varchar(20),age int）;
插入数据：insert into 表名（数据库中的字段，例如：id,name,age）values(1,”张翠花”,20);
设置字符集：set names utf8或gbk
插入部分数据；insert into 表明（字段） value(对应字段的数据)；
批量插入：insert into 表名（id,name,age） values(6,‘高飞’，22),(7,’刘安’,23);
批量插入数据用逗号隔开，最后分号结束
查询表中的所有数据：select 字段from 表名；
例：select * from emp;
数据库中删除表：delete from 表名 where 字段=具体数据；
例：删除student表中名字是“张三”的学生
delete from student where name=”张三”；

2.数据库的插删改
一、
（1）创建表
create table emp(empno int,ename varchar(20));
（2）修改表名
rename table emp to t_emp;
例：将emp表名修改为t_emp：rename table emp to t_emp;
（3）alter和add的使用
例：alter table t_temp add column age int;
（4）在t_emp表中员工名字后面添加字段（dept）
例：alter table t_temp add dept int(10) after ename;
（5）在t_temp表中员工编码后面添加字段（sal）;
例：alter table t_temp add sal int(10) after empno;
（6）将t_temp字段sal删除
例：alter table t_temp drop column sal;
（7）change的使用
1）原来的字段更换为新的字段新的类型
（8）把t_temp表中的dept字段更换为dept2并且类型为字符串
例：alter table t_temp modify dept2 int(10);
（9）修改位置dept2字段在empno后面
例：alter table t_emp modify dept2 int(10) after empno;
二、
primary key auto_increment主键自增长
（1）创建表t_temp2 id name id 要求自增长
create table t_temp2(id int primary key auto_increment,name varchar(20));
（2）在t_temp2表中添加字段age
alter table t_emp2 add age int;
（3）在t_temp2表中插入3条数据
insert into t_temp2 values(null,”测试”,18);
（4）修改t_temp2表中的年龄是18岁的名字为”张三”
update t_temp2 set name=”张三” where age=18;
（5）删除名字是“张三”的数据
delete from t_temp2 where name=”张三”;
（6）没有条件的查询
select * from 表名；查询表中所有字段的数据
select 字段1，字段2,…from表名；
（7）查询表中的部分字段的数据，where条件查询
例：查询年龄为29的员工信息
select * from t_temp2 where age=29;
三、
（1）where条件查询
列值为null和不为null
例：查询没有上级领导的员工编号，姓名，工资
select empno,ename,sal from emp where mgr is null;
查询领导编号是7839的员工信息
select * from emp where empno=7839；
查询没有奖金（comm）的员工信息
select * from emp where comm=0;
（2）别名
select empno as e,ename as n from emp;
select empno “员工编号”，ename ”员工姓名” from emp;
（3）去重distinct
查询emp表中员工职位
select distinct job from emp;
(4)比较运算符 > = < <>表示非
1.查询工资高于2000的所有员工的编号,姓名,工资,职位
select empno,ename,sal,job from emp where sal>2000;
2.查询工资高于1600的所有员工的编号,姓名,工资
select empno,ename,sal from emp where sal sal>1600;
查询部门编号是20的所有员工姓名,职位
select ename,job from emp where deptno=20;
查询职位是manager的所有员工姓名,职位
select empno,job from emp where job=”manager”;
5.查询不是10号部门的所有员工姓名,工资,部门编号
select ename,sal,deptno from emp where deptno<>10;
6.查询单价不等于8443的商品信息
select * from t_item where price<>8443;
7.查询单价等于23的商品信息
select * from t_item where price=23;
四.
 and or and 等效于java的&& or等效于java中的||
1.查询不是10号部门,工资小于3000的员工编号,姓名,工资,部门编号
select empno,ename,sal,deptno from emp where deptno!=10 and sal<3000;
2.查询部门是30或者上级领导为7689的所有员工的姓名,部门编号,上级领导编号
select ename,deptno,mgr from emp where deptno=30 or mgr=7689;
 1.in和not in 如果查询字段的值为多个的时候可以使用in关键字
1.查询部门是20 30的员工编号,姓名,部门编号
select empno,ename,deptno from emp where deptno in(20,30);
select empno,ename,deptno from emp where deptno=20 or deptno=30;
2.查询员工工资是800,950,1600的员工名字和工资
select ename,sal from emp where sal in(800,950,1600);
3.查询员工工资不是800,950,1600的员工名字和工资
select ename,sal from emp where sal not in(800,950,1600);
五.
between x and y:在两两数值之间包含and两边的数值
1.查询员工，工资在500-1000的所有员工名字和工资
select ename,sal from emp where sal between 500 and 1000;
like,模糊查询,_代表单个未知的字符%代表多个未知的字符
1.查询名字以k开头的所有员工名字
select ename from emp where ename like “K%”;
2.查询名字第二个字母是L的所有员工名字
select ename from emp where ename like “_l%”;
3.查询名字第3个字母是L的所有员工名字
select ename from emp where ename like “l%”;
4.查询名字倒数第二个字母是e的所有员工名字
select ename from emp where ename like “%e”;
5.查询商品标题包含记事本的商品
select title from t_item where title like “%记事本%”;
6.查询单价低于100的记事本
select * from t_item where title like “%记事本%” and price<100;
7.查询有图片的得力商品
select * from t_item where image is not null and title like “%得力%”;
8.查询单价介于50到200之间的得力商品
select * from t_item where price between 50 and 200 and price like “%得力%”;
9.查询有赠品的"dell"商品
select * from t_item where sell_point like “%赠%” and title like “%dell%”;
查询结果排序,格式:order by 字段默认升序,指定升序是asc降序是desc默认是升序
多个字段排序,当第一字段相同时,第二个字段排序开始,字段中间由逗号隔开
1.查询员工的名称和工资，按照工资降序排列
select ename,sal from emp where sal desc;
2.查询员工的名称和工资，按照工资升序排列
select ename,sal from emp oreder by sal asc;
3.查询单价在100以下的商品名称和价格，按照价格降序排列
select title,sal from t_item where price<100 order by price desc;
4.查询单价在100以下记事本商品的名称和价格，按照价格升序排列
select title,price from t_item where price<100 order by price;
5.查询所有部门编号，工资，部门编号降序排序，工资降序排序
select deptno,sal,deptno from emp order by deptno desc,sal desc;
6.查询记事本商品的名字，价格，按照商品的分类降序排列，价格的升序排列,价格在100到30之间包含100和30
select title,price from t_item where price between 30 and 100 order by price,category_id desc;
limit分页查询
跳过条数(大于此条数),查询条数,例如:每一页显示5条,第一页则是limit 0,5;
1.查询商品表中商品名称和价钱，第一页数据，每一页显示5条
select title,price from t_item limit 0,5;
2.查询商品表中商品名称和价钱，第3页数据，每一页显示5条
select title,price from t_titem limit 10,5;
3.查询拿最高工资的员工信息
select * from emp order by sal desc limit 0,1;
4.查询拿最低工资的员工信息
select * from emp order by sal limit 0,1
5.查询第二个字母是l，工资小于等于2000的最高的前两名员工信息
select * from emp where ename like “_l%” and sal<2000 order by sal desc limit 0,2;
数值计算 + - * % (mod)
1.查询所有商品单价，库存，总价
select price,num,price*num from t_item;
2.%和mod都是取余作用
select 8%3;
select mod(8,3);

3.数据库相关函数
 日期相关函数
获取当前日期+时间 now()
select now();
获取当前日期curdate()
select curdate();
获取当前时间curtime()
select curtime();
从当前时间的日期和时间中提取日期date(now())
select Date(now())
从日期和时间中提取时间 time()
select time(date());
案例：查询商品创建的年月日
select Date(created_time) from t_item;
案例：查询商品创建的时间
select time(create time) from t_item;
 extract提起年月日extract()函数用户返回日期/时间的单独部分
语法:extract(unit from date)
select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());
 日期格式化date_fromat
 %Y:年 %m:月份 %d:天 %H:(24小时) %h:(12小时) %f:分 %s:秒
查询现在的时间
select date_format(now(),”%Y-%m-%d %H:%f:%s”);
查询商品创建年月日
select date_format(created_time,”%Y-%m-%d %h:%f:%s”)”创建日期”
把不标准的格式转换为标准格式str_to_date(“日期字符”,”格式”);
select str_to_date(“14号10月2020年”,”%d号%m月%Y年”)”时间”;
 ifnull()格式,age=ifnull(x,y)判断x是否为null,如果为null则age=y,如果不是null,则age=x;
查询员工表没有奖金的显示为0
select ename,ifnull(comm,0) “奖金” from emp;
把没有奖金的这些员工的奖金修改为0
update emp set comm = ifnull(comm,0);
 聚合函数:对多行数据进行合并统计
sum():sum()函数返回数值列的总数(金额)
avg():avg()函数返回列值的平均值,null值不包含在计算内
count():count(colum_name)函数返回指定列的值的数据,null值不计入
max():max()函数返回一列中的最大值,null值不包含在计算内
min():min()函数返回一列中的最小值,null值不包含在计算内
查询员工表中的平均工资，错误示范select ename avg(sal) from emp;
select avg(sal) from emp;
查询10号部门的最高工资
select max(sal) from emp;
查询dell商品的库存总和
select sum(num) from t_item where title like”%dell%”;
查询得力商品的条数 null值不计入
select count(title) from t_item where title like “%得力%”;
查询员工姓名包含a的最低工资
select ename,min(sal) from emp where ename like “%a%”;
 字符串相关函数
concal(a,b)字符串相连函数
select contact(“a”,”b”);
查询每个员工的工资,工资后面显示"无"
select contact(sal,”无”), sal from emp;
获取字符串的长度char_length(str)
select char_length(“小小丁”);
查询每个员工的名字及其名字的长度
select ename,car_length(ename)”长度” from emp;
insert(str,substr)获取substr在str中的位置(下标从1开始)
select insert(“ssss”,”r”);
locate(substr,str)获取substr在str中的位置
select locate(“s”,”fgsgh”);
insert(str,start,length,newstr);
str:字符串 start:从字符串第几个开始插入
length:从字符串中的第几个开始插入的位置,开始删除字符串的个数
newstr:插入的新字符
select insert(“kunge”4,2,”shuai”);
 lower(str)转小写
select lower(“input”);
 upper(str)转大写
select upper(“input”);
 trim(str)去掉两边的空白
select trim(“ fgh “);
取消两边空白并获取它的长度
select char_length(trim(“ dfgh ”))
 left(str,length)从左边截取多少字符
select left(“length”,3);
 right(str,length)从右边截取多少字符
select rigth(“good”,2);
 substring(str,index,length)截取字符串是从下标为index的开始截取,length长度的字符串
select substring(“contribution”,2,3)
 replace(str,old,new)替换
 repeat(str,lenth)重复
select repeat(“加油”,2);
 reverse(str)反转
select reverse(“我爱你”);
 数学相关函数
向下取整floor(num)
select floor(23.33);
四舍五入 round(num)
select round(34.522);
四舍五入 round(num,m) m:保留小数点后的位数
select round(78.99,1);
truncate(num m) 非四舍五入m,保留小数点后的位数
select truncate(99.3456,3);
随机数rand() 0-1之间的浮点数
select rand();
select rand()*10;
随机数并取整必须要乘,要不然结果就是0
select floor(rand()*10);

4查询
 分组插叙group by:分组查询通常和聚合函数结合使用,查询条件中每个xx就以xx作为分组条件
– 1.查询每个部门的最高工资的员工信息
selsect max(sal) from emp group by deptno;
– 2.查询每个分类商品的库存总量
select sum(num)from t_item group category_id;
– 3.查询每个部门多少人
select count(empno) from emp group by deptno;
– 4.查询每个部门的工资大于2000的有多少人
select count(empno) from emp where sal>2000 group by deptno;
– 5.查询每个分类下低于100元的的商品数量
select sum(num) from emp where price<100 group by category_id;
– 6.查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排序，工资总和的降序排序
select empno,count(empno) c ,sum(sal) s from emp group by deptno order by c,s desc;
– 7.查询工资在1000到3000之间的员工的信息，每个部门的编号，平均工资，最低工资，最高工资进行升序排列
select empno,avg(sal),min(sal),max(sal) m from emp where sal between 1000 and 3000 group by deptno order by m asc;
– 8.查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，根据人数进行降序，平均工资进行升序排列
select count(empno) c,sum(sal) s,avg(sal) a from emp where mgr is not null order by c desc,a;
– 9.查询每个部门，每个主管的手下人数
select detpno,mgr,count() from emp where magr is not null group by deptno,mgr;
– 10.查询每年入职的人数
select count() c,extract(YEAR from hierdada) e from emp group by e;
 having有条件的分组统计;where后面只能对普通字段记性筛选;having写在group by 后面,通常是和group by结合使用;普通字段的条件写在where后面,聚合函数调价写在having后,having写在group by 后面;where条件用于过滤行,而having子句用于过滤分组数据
– 1.查询每个部门的平均工资，平均工资大于2000
select avg(sal) a from emp group by deptno having a>2000;
– 2.查询所有分类对应的库存总量,要求库存总量高于100000;
select sum(num) from t_item where num>100000 group by category_id;
– 3.查询所有分类对应的平均单价低于100的分类
select category_if,avg(sal) a from t_item group by category_id having a<100;
– 4.查询每个部门中名字为’A’的员工的平均工资只显示平均工资高于2000的
select *,avg(sal)a from emp where ename like “%A%” group by deptno having a>2000;
 总结:

什么是子查询:嵌套在sql语句里面的sql语句
子查询可以有多层嵌套
写在where/having后面坐查询条件的值
#关联查询：同时查询多张表的数据
– 1.等值连接
– 1.1查询每一个员工姓名和所对应的部门名称和部门地址
#select 员工表的员工名字，部门表的部门名称，部门表的部门地址 from 员工表，部门表 where 员工表的deptno=部门表的deptno
SELECT emp.ename,dept.dname,dept.loc FROM emp,dept WHERE emp.deptno=dept.deptno;

#给表起别名，利用别名去访问对应表中的字段
SELECT e.ename,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno;

– 2.查看编号是7369的员工的部门信息和员工的名字，工资，上级领导
SELECT dept.*,emp.empno,emp.ename,emp.sal,emp.mgr FROM dept,emp WHERE dept.deptno=emp.deptno AND empno=7369;

#多表查询用join on
#语法：SELECT 表字段1，表字段2，…from 表1 INNER JOIN 表2 on 两表的等值连接条件—其中inner可以省略
/**
等值连接格式：select * from A表，B表 where A.x=B.x;
内连接：select * from A 表 inner Join B表 on A.x=B.x (inner可以不写）
内连节可读性更高，所以以后我们尽量使用内连接
**/

– 1.1查询每一个员工姓名和所对应的部门名称和部门地址
SELECT e.ename,d.dname,d.loc FROM emp e INNER JOIN dept d ON d.deptno=e.deptno;

– 2.查询每个商品的标题，商品单价，商品分类名称
SELECT t.title,t.price,tt.name FROM t_item t INNER JOIN t_item_category tt ON t.category_id=tt.id;

– 3.查询在new york工作的所有员工的信息
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno WHERE loc=“NEW YORK”;

– 4.查询emp表中开头以"S"有上级领导的员工信息和所对应的部门名字，部门地址
SELECT *,d.dname,d.loc FROM emp e INNER JOIN dept d ON e.deptno=d.deptno WHERE e.mgr IS NOT NULL AND e.ename LIKE “S%”;

– 5.查询每个部门的平均工资，最高工资以及他们的部门信息的最高平均工资的前两个部门
SELECT AVG(sal) a,MAX(sal) m,d.* FROM emp e INNER JOIN dept d ON e.deptno=d.deptno GROUP BY e.deptno ORDER BY a DESC empemplimit 0,2;

#外连接
#关联查询时只查询两张表有关系的数据，不能满足需求，如果需要查询某一张表所有数据（包含没有关系的）则使用外连接查询方式
#左外连接：以join左边表为主表查询所有数据，右边表只查询有关系的数据
#右外连接：以join右边表为主表查询所有数据，左边表只查询有关系的数据
#语法：select 表字段1，表字段2，…from 表1 left/right outer join 表2 on 条件等值（outer省略不写）
SELECT e.,d. FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

– 1.查询所有员工的名字和对应的部门名
SELECT e.ename,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

– 2.查询所有部门和对应的员工名
SELECT d.*,e.ename FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

– 1.每个部门的人数，根据部门人数排序
SELECT d.*,COUNT(empno) c FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno GROUP BY deptno ORDER BY c;

– 2.每个部门中，每个主管手下人数
SELECT d.,COUNT(empno)c FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno WHERE e.mgr IS NOT NULL GROUP BY deptno,mgr;
SELECT deptno,mgr,COUNT() FROM emp GROUP BY deptno,mgr;

– 3.每种工作的平均工资按照平均工资的降序排列的前两名
SELECT job,AVG(sal) a FROM emp GROUP BY job ORDER BY a DESC LIMIT 0,2;

– 4.每年入职的人数以降序排列
SELECT EXTRACT(YEAR FROM hiredada) e,COUNT(empno) c FROM emp GROUP BY e ORDER BY c DESC;

– 5.少于等于3个人的部门
SELECT deptno,COUNT(*) c FROM emp GROUP BY deptno HAVING c<=3;

– 6.少于等于3个人的部门信息
SELECT d.deptno,COUNT() c FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY e.deptno HAVING c<=3;
SELECT * FROM dept WHERE deptno IN(SELECT deptno FROM emp GROUP BY deptno HAVING COUNT()<=3);

– 7.那最低工资的员工信息所对应的部门号，部门名称
SELECT d.dname,e.* FROM emp e JOIN dept d ON e.deptno=d.deptno ORDER BY e.sal LIMIT 0,1;

– 8.只有一个下属的主管信息
– 8.1只有一个下属的主管
SELECT deptno,mgr FROM emp WHERE mgr IS NOT NULL GROUP BY mgr HAVING COUNT()=1;
– 8.2只有一个下属的主管的主管的个人信息
SELECT * FROM emp WHERE empno IN(SELECT mgr FROM emp WHERE mgr IS NOT NULL GROUP BY mgr HAVING COUNT()=1);
#自连接
SELECT p.* FROM emp e JOIN emp p ON e.mgr=p.empno WHERE e.mgr IS NOT NULL GROUP BY e.mgr HAVING COUNT(*)=1;

– 9.平均工资最高的部门编号以及部门名称，按照平均工资的升序排列
SELECT d.deptno,d.dname,AVG(sal) a FROM emp e JOIN dept d ON e.deptno=d.deptno
GROUP BY e.deptno HAVING AVG(sal)=MAX(sal);

SELECT d.*,AVG(sal) FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY e.deptno HAVING AVG(sal)=
(SELECT AVG(sal) FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY e.deptno ORDER BY AVG(sal) DESC LIMIT 0,1);

– 10.下属人数最多的领导的个人信息
– 10.1下属人数最多的领导
SELECT mgr,COUNT() c FROM emp GROUP BY mgr ORDER BY c DESC LIMIT 0,1;
– 10.2下属人数最多的领导的个人信息
SELECT * FROM emp WHERE empno=(SELECT mgr FROM emp GROUP BY mgr ORDER BY COUNT() DESC LIMIT 0,1);

SELECT p.* FROM emp e JOIN emp p ON e.mgr=p.empno GROUP BY e.mgr HAVING p.empno=
(SELECT mgr FROM emp GROUP BY mgr ORDER BY COUNT(*) DESC LIMIT 0,1);

– 11.拿最低工资的个人信息和部门名称
SELECT e.*,d.deptno FROM emp e LEFT JOIN dept d ON e.empno=d.deptno WHERE e.sal=(SELECT MIN(sal) FROM emp);

– 12.最后入职的不是以“S”开头的员工编号，员工工资和部门信息
SELECT e.empno,e.sal,d.* FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno WHERE e.ename NOT LIKE “S%” AND hiredada=(SELECT MAX(hiredada) FROM emp);

– 13.工资高于平均工资的员工信息及其部门信息
SELECT e.,d. FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE e.sal>(SELECT AVG(sal) FROM emp);

– 14.查询有领导的员工信息和部门名称
SELECT e.*,d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno WHERE e.mgr IS NOT NULL;

– 15.查询不是以“J”开头的员工信息，部门名称，所在城市
SELECT e.*,d.dname,d.loc FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno WHERE e.ename NOT LIKE “J%”;

– 16.“DALLAS”市所有员工的信息
SELECT e.*,d.loc FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno WHERE d.loc=“DALLAS”;

– 17.计算每个城市的员工数量
SELECT d.*,COUNT(empno),d.loc FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno GROUP BY d.loc;

– 18.查询员工信息和他的主管名字
SELECT e.*,p.ename FROM emp e JOIN emp p ON e.mgr=p.empno;

– 19.查询最低工资的员工信息，员工主管名字和员工主管部门名称
SELECT e.,p.ename,d.dname FROM emp e LEFT JOIN emp p ON e.mgr=p.empno LEFT JOIN dept d ON p.deptno=d.deptno
WHERE e.sal=(SELECT MIN(sal) FROM emp);
– 20.平均工资最高的部门信息
SELECT d. FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno GROUP BY e.deptno HAVING AVG(sal)=
(SELECT AVG(sal) a FROM emp e JOIN dept d ON e.deptno=d.deptno GROUP BY e.deptno ORDER BY a DESC LIMIT 0,1);

– 21.查询员工表中所有员工的编号，姓名，职位,工资根据工资进行升序排列的第3条到第6条员工信息
SELECT empno,ename,job,sal FROM emp ORDER BY sal LIMIT 2,4;

– 22.查询员工表中所有员工的编号,姓名,职位,工资以及该员工上级领导的编号,姓名,职位,工资
SELECT e.empno,e.ename,e.job,e.sal,p.empno,p.ename,p.job,p.sal FROM emp e LEFT JOIN emp p ON e.mgr=p.empno;

– 23.查询员工表中名字没有字母"K"的所有员工的编号,姓名,职位,以及所在部门的编号,名称,地址
SELECT e.empno,e.ename,e.job,d.* FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno WHERE e.ename NOT LIKE “%K%”;

– 24.查询部门表中所有的部门信息,以及与之关联的员工表中的员工的编号,姓名,职位,工资
SELECT d.,e.empno,e.ename,e.job,e.sal FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
#笛卡尔积
/
关联查询，如果不写关联关系，则查询结果为两张表的成绩，这个乘积称为“笛卡尔积”
笛卡尔积是一种错误的查询结果，工作中不允许出现此现象
*/
5添加数据

1.创建表

CREATE TABLE person(
id INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10),
gender CHAR(1),
rel VARCHAR(5)
);

#2.给person表添加数据
INSERT INTO person VALUES
(NULL,“周润发”,‘男’,‘三姑夫’),
(NULL,“蔡依林”,‘女’,‘二姨’),
(NULL,“成龙”,‘男’,‘大舅’),
(NULL,“特朗普”,‘男’,‘侄子’),
(NULL,“默克尔”,‘女’,‘外甥女’);

SELECT * FROM person;

INSERT INTO person VALUES
(NULL,“刘德华”,‘男’,‘四姨夫’),
(NULL,“普京”,‘男’,‘老丈人’);
#3.创建trade表
CREATE TABLE trade(
d INT PRIMARY KEY AUTO_INCREMENT,
ttime DATE,
tmoney DOUBLE,
ttype VARCHAR(5),
pid INT
);

#4.给trade表插入数据
INSERT INTO trade VALUES
(NULL,“2018-02-14”,1000,“微信”,1),
(NULL,“2018-02-16”,300,“现金”,2),
(NULL,“2018-02-15”,1500,“支付宝”,3),
(NULL,“2018-02-14”,-20,“支付宝”,4),
(NULL,“2018-02-14”,-600,“现金”,5);

SELECT * FROM trade;

INSERT INTO trade VALUES
(NULL,“2018-02-14”,150,“微信”,6),
(NULL,“2018-02-18”,80,“现金”,7);

#5.统计从2018年春节（2月15日）到现在的收益（收益=收入-支出）多少元红包？
SELECT SUM(tmoney) FROM trade t WHERE ttime>=“2018-02-15”;
SELECT SUM(tmoney) FROM trade t WHERE ttime>=STR_TO_DATE(“2018-02-15”,"%Y年%m月%日");
SELECT SUM(tmoney) FROM trade t WHERE ttime BETWEEN 2018-02-15 AND DATE(NOW());

#6.查询从2018年春节（2月15日）到现在的红包大于100元的所有女性亲戚的名字和对应的红包金额。
SELECT p.pname,t.tmoney FROM trade t JOIN person p ON t.pid=p.id WHERE t.ttime>=“2018-02-15” AND p.gender=“女” AND t.tmoney>100;

#7.查询统计现金，支付宝，微信三个平台分别收到的红包总金额？
SELECT SUM(tmoney),ttype FROM trade WHERE tmoney>0 GROUP BY ttype;

#权限管理
#3张表，用户表角色表，权限表
#两个关系，用户角色关系表，角色权限关系表

#1.创建用户表
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(10),
age INT
);

#2.插入数据
INSERT INTO users VALUES
(NULL,“测试1”,20),
(NULL,“测试2”,21),
(NULL,“测试3”,22),
(NULL,“测试4”,23),
(NULL,“测试5”,24);

#3.创建角色表
CREATE TABLE role(
id INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(10),
create_time TIMESTAMP
);

#4.插入数据
INSERT INTO role VALUES
(NULL,“管理员”,NOW()),
(NULL,“店小二”,NOW()),
(NULL,“用户”,NOW())
;

#5.创建用户角色
CREATE TABLE user_role(
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
role_id INT
);

#6.插入数据
INSERT INTO user_role VALUES
(NULL,1,3),
(NULL,2,1),
(NULL,3,3),
(NULL,4,2);

SELECT u.,r.,e.* FROM users u JOIN user_role r ON u.id=r.id JOIN role e ON e.id=r.role_id;

#查询出用户以及它所对应的角色
SELECT u.ename,e.rname FROM users u JOIN user_role r ON u.id=r.id JOIN role e ON e.id=r.role_id;

6 流程控制函数存储过程调用函数视图
#流程控制函数
#if函数：在java中if else的效果类似于三目运算条件？1:2
#java：三目运算 10>5?“大”:“小”
SELECT IF(10>5,“大”,“小”);
#案例：查询员工奖金，奖金是0显示没有奖金，否则显示有奖金
SELECT empno,IF(comm=0,“没有奖金”,“有奖金”) FROM emp ;

#case函数的使用，类似于Java中的switch case效果
/*
switch(变量）
case 变量：语句
break;
case 变量：语句
break;
default;
*/

/*
mysql中
case要判断的字段或者表达式
when 常量1 then要显示的值或语句
when 常量1 then要显示的值或语句
…
else 要显示的值或语句
end
/
#查询员工表中员工编号和工资，部门是10号的员工工资提高10倍，部门20号的员工工资提高0.1倍
SELECT empno,sal “原始工资”,CASE deptno WHEN 10 THEN sal10 WHEN 20 THEN sal*0.1 ELSE sal END "提高后的工资"FROM emp;

/*
case
when 条件1 then要显示的值或语句
when 条件2 then要显示的值或语句
…
else要显示的值或语句
end
*/
#案例：查询员工的工资情况，如果工资大于3000，显示A级别，如果工资大于2000，显示B级别，如果工资大于1000显示"C"级别，否则显示D级别
SELECT sal,CASE WHEN sal>=3000 THEN “A” WHEN sal>=2000 THEN “B” WHEN sal>=1000 THEN “C” ELSE “D” END “级别” FROM emp;

#存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1、提供代码的重用性
2、简化操作
3、减少编译次数并且减少了和数据库服务器的连接次数，提高了效率
注意：一般的存储过程处理了sal语句的增，删，改
含义：一组预先编译好的SQL语句集合，理解成成批处理语句
*/

/*
一、创建语法
create procedure 存储过程名（参数列表）
begin

存储过程体（一组合法的sql语句）

end

注意
1、参数列表包含三部分:参数模式，参数名，参数类型
例如：in username varchar(10)

参数模式：
in:该参数可以作为输入，也就是该参数需要调用方传入值

out:该参数可以作为输出，也就是该参数可以作为返回值

inout:该参数可以作为输入也可以作为输出，也就是该参数既可以作为传入值也可以作为输出方作为返回值

注意：存储过程中的每条sql语句的结果要求必须加分号，存储过程的结果可以使用
delimiter 重新设置结束符
delimiter 结束标记（sqlyog不支持）
*/

#二、调用语法
– call存储过程名（参数列表）

DELIMITER $

– 1.空参列表
#创建admin表
CREATE TABLE admin(
user_name VARCHAR(20),
user_password VARCHAR(10)
);
#创建存储过程
DELIMITER $

CREATE PROCEDURE my1()
BEGIN
INSERT INTO admin VALUES(“小龙”,“123456”),(“小君”,“654321”);
END$

CALL myl()$
– truncate table admin表示清空表

– 2.创建in模式参数的存储过程（in可以省略，但是不建议省略）
– 创建存储过程实现查询对应的部门员工信息
CREATE PROCEDURE my2(IN deptno INT)
BEGIN
SELECT * FROM emp WHERE emp.deptno=deptno;
END $

CALL my2(30)$

– 3.创建存储过程实现根据传入的条目数和起始索引，查询emp表中的记录
CREATE PROCEDURE my3(IN size INT,IN startIndex INT)
BEGIN
SELECT * FROM emp LIMIT startIndex,size;
END $

CALL my3(3,3)$

– 4.创建存储过程实现用户是否登录成功
CREATE PROCEDURE my4(IN ename VARCHAR(10),IN pwd VARCHAR(20))
BEGIN
SELECT IF((SELECT COUNT(*) FROM admin WHERE admin.user_name=ename AND admin.user_password=pwd
>0,“成功”,“失败”);
END$

– 5.创建带out模式的存储过程
– 创建存储过程实现查询员工表中员工表对应的名字
CREATE PROCEDURE my5(IN empno INT,OUT ename VARCHAR(10))
BEGIN
SELECT emp,ename INTO ename FROM emp WHERE emp.empno=empno;
END $
#调用
CALL my5(7521,@ename)$
#查询@n的返回数据
SELECT @n $

– 创建存储过程或函数回信传入一个日期，格式化为xx年xx月xx日并返回
my6
CALL my6(NOW(),@DATA)$
SELECT @DATA$

– 创建带有into的存储过程
– 传创建存储过程实现根据员工名字查询员工名字
CREATE PROCEDURE my7(INOUT ename VARCHAR(10))
/*begin
select emp.ename into ename from emp where emp.ename=ename;
end $
*/

– 删除存储过程
DROP PROCEDURE 存储过程名
DROP PROCEDURE my7;

– 查询所有数据库中的存储过程
SHOW PROCEDURE STATUS;

– 查询表中指定的存储过程名
SHOW CREATE PROCEDURE 存储过程名
SHOW CREATE PROCEDURE my6;

– 创建存储过程实现根据admin表中的名字删除对应的数据
CREATE PROCEDURE my7(IN ename vachar(20))
BEGIN
DROP * FROM emp WHERE user_name=ename;
END $
– 创建存储过程实现根据admin表中的名字修改对应的数据
CREATE PROCEDURE my9(IN old_pwd VARCHAR(20),IN new_pwd VARCHAR(10))
BEGIN
UPDATE admin SET user_password = new_pwd WHERE user_name=ename;
END$
CALL my9(“小君”,“666666”)$
/*
视图：数据库中存在多种对象，表和视图都是数据库中的对象，创建视图时名称不能和表明
重名，试图实际上是代表了一段sql查询语句，可以理解成视图是一张虚拟的表，表中的数据
会随着原表的改变而改变
为什么要使用视图：
有些数据的查询需要书写大量的sql语句，每次书写比较麻烦，使用视图可以起到sql重用的作用
可以隐藏敏感信息

*/
– 1.创建师徒模式
CREATE VIEW 视图名 AS 子查询

创建视图

– 案例：隐藏员工表中的工种，领导，员工入职时间，部门
CREATE VIEW myv1 AS (SELECT empno,ename,sal,comm FROM emp);

查询视图

#select * from 视图名
SELECT * FROM myv1;
– 1.查询视图中的部分字段
#select 字段1，字段2…from视图名
SELECT empno,sal FROM myv1;
– 2.创建视图查询平均工资最高的部门信息
CREATE VIEW myv2 AS(

SELECT d.* FROM emp e JOIN dept d ON e.deptno=d.deptno
GROUP BY e.deptno HAVING AVG(sal)=(SELECT AVG(sal) a FROM emp GROUP BY
deptno ORDER BY a DESC LIMIT 0,1)

);

SELECT * FROM myv2;

– 3.删除视图
#drop view 视图名
DROP VIEW myv2;

– 4.删除视图中的数据
DELETE FROM myv1 WHERE empno=7499;

– 5.给视图插入数据
INSERT INTO myv1 VALUES(8888,“admin”,8000,3000);

– 6.隐藏不是30号部门的所有员工信息
CREATE VIEW myv3 AS(SELECT empno,ename,sal,deptno FROM emp WHERE deptno=30);

– 7.给视图myv3插入数据
INSERT INTO myv3 VALUES(6666,“admin2”,7000,30);
INSERT INTO myv3 VALUES(5505,“admin3”,7000,20);

– 8.给视图中插入一条不显示的数据，但是原表会显示的数据称之为“数据污染"
– 在创建视图时需要使用with check option 的关键字来避免“数据污染”

CREATE VIEW myv4 AS(SELECT empno,ename,sal,deptno FROM emp WHERE deptno=30)WITH CHECK OPTION;
INSERT INTO myv4 VALUES(5506,“admin3”,7000,20);

– delete from myv4 where empno=“6666”;
– 9.修改myv4中的数据
UPDATE myv4 SET ename=“admin20” WHERE empno=7521

– 经验：使用视图修改数据会有很多限制，一般在实际开发中视图仅用于查询

7存储事务视图
#存储，视图，事务
#事务：
/*
什么是事务(transaction)?
是为了完成某个业务而执行的一条或者多条SQL语句的最小逻辑工作单元，保证数据的完整性，事务具有以下四个
特性：
原子性(Atomicity):事务是一个完整的操作，事务的各部操作是不可分开的(原子性)，要么执行，要么都不执行;
一致性(Consistency):当事务完成时，数据必须处于一致状态;
隔离性(Isolation):并发事务之间彼此隔离，独立，它不应该以任何方式依赖或影响其他事务;
持久性(Durability):事务完成后，它对数据库的修改被永久保存，保持
*/
CREATE TABLE band(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),money DOUBLE);
INSERT INTO band VALUES(NULL,“王云龙”,1000.00),(NULL,“李浩哲”,500);
DELETE FROM band WHERE id=3 OR id=4;

#查询自动提交的状态 1或者on 则为自动提交 0或者off则为手动提交
SHOW VARIABLES LIKE “autocommit”;

#设置提交状态
SET autocommit=0;#设置提交状态为手动
/*
A数据库服务端（支持一方）），B数据库服务端（接受一方）钱没收到，也就是数据
没有提交到数据库中，这是必须需要事务来处理该问题
*/
UPDATE band SET money=money-200 WHERE NAME=“王云龙”;
UPDATE band SET money=money+200 WHERE NAME=“李浩哲”;
/以上两条sql语句是在事务中执行的/
SET autocommit=1;#设置提交状态为自动

SET autocommit=0;

– 回滚rollback
INSERT INTO band VALUES(NULL,“丁静君”,1000),(NULL,“王云龙”,500),
(NULL,“小茹”,500),(NULL,“小微”,500),(NULL,“小柯”,500);
COMMIT;
#在这里假设插入一条数据时发生故障，那么回滚数据
INSERT INTO band VALUES(NULL,“小小丁”,500);
ROLLBACK;

DELETE FROM bank money IN(500,1500);

#约束
– 什么是约束？
– 约束就是给表字段添加的限制条件
– not null
– 添加非空约束的字段值不能为null
CREATE TABLE mytable(id INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR(10) NOT NULL);

INSERT INTO mytable VALUES(NULL,NULL);

#主键约束
#unique:唯一（不能重复）
CREATE TABLE mytable2(id INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(10) NOT NULL UNIQUE);

INSERT INTO mytable2 VALUES(NULL,“测试1”);

#主键约束：primary key 添加了主键约束的字段，值不能为null也不能重复
#一个表只能有一个主键
CREATE TABLE mytable3(id INT PRIMARY KEY);
INSERT INTO mytable3 VALUES(1);
INSERT INTO mytable3 VALUES(NULL);

SHOW TABLE mytable3(id INT PRIMARY KEY);

#删除主键索引

ALTER TABLE mytable3 DROP PRIMARY KEY;

/*外键约束
外键约束是保证一个或者两个表之间的参照完整性，保持数据一致性
表的外检可以是另一张表的主键（也可以是唯一约束），外键有重复，可以为空
*/

#创建外键约束
#年级表
CREATE TABLE classes(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10));
#学生表
CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),class_id INT REFERENCES class(id));

#创建外键约束方式二：
CREATE TABLE classes(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),classes_id INT,CONSTRAINT f

/*
索引：用来加快查询的技术很多，其中最重要的是索引（index)
1.通常索引能够快速提高查询速度哦
2.入股不使用索引，mysql必须从第一条记录开始然后读完整个表直到找出相关行，表越大，花费时间越长
索引可以用来改善性能，有时索引可能降低性能
*/

#1.创建索引语法：create inde 索引名字 on 表明（字段名）
#2.删掉索引语法：drop index 索引名on 表明
#3.查看索引：show index from 表名
#在mysql中选择数据库并且导入sql文件；
#例如：e盘下的item_backup.sql;
#导入sql文件

source D:/Java/item_backup.sql;

SELECT COUNT(*) FROM item2;

SHOW INDEX FROM emp;

SELECT * FROM item2 WHERE title=“110”;

#创建索引
CREATE INDEX index_num ON item2(num);
SELECT * FROM item2 WHERE num=857;

CREATE UNIQUE INDEX idndex_my2 ON mytable2(ename)
CREATE TABLE mytable2(id INT PRIMARY KEY AUTO_INCREMENT,ename VARCHAR(10));
INSERT INTO mytable3 VALUES(NULL,"");
#按照下列标准选择建立索引列
– 1.频繁搜索的列
– 2.经常用作查询选择的列
– 3.经常排序，分组的列
– 4.经常用作连接的列（主键/外键）

#请不要使用下面的列创建索引
#表中仅包含几行数据

/*
主键，外键，索引的区别
主键外键索引
定义：唯一标识一条记录，不能表的外键是另一张表的主键，该字段没有创建索引，也可设置重复值
有重复的，不允许为空外键可以有重复的，可以有空值但可以有一个空值

作用：用来保证数据完整性用来和其他建立联系用的是提高查询排序的速度

个数：一个表只能有一个主键一个表可以有多个外键一个表可以有多个索引

/*
一、系统变量
系统变量：
全局变量：对整个服务器有效
会话变量：对当前的客户端的一次连接
自定义变量
用户变量
局部变量
一、系统变量
说明：变量由系统提供，不是用户定义，属于服务器层面
使用语法：
1.查看所有系统变量
show global /session variables

2.查看满足条件的部分系统变量
SHOW GLOBAL/SESSION VARIABLES LIKE “%%”;

3.查看指定的某个系统变量
select @@global/session.系统变量名

4.为某个系统变量赋值
set @@global/session.系统变量值=值

*/
#.查看全局变量
SHOW GLOBAL VARIABLES;

#查看局部变量 session可以省略不写
SHOW SESSION VARIABLES;

#查看全部局部变量中带有"char"
SHOW GLOBAL VARIABLES LIKE “%char%”;

#查看会话中带有“char”
SHOW VARIABLES LIKE “%char%”;

#查看系统变量是
SELECT @@global.character_set_client;

#查看系统变量是autommit的值
SELECT @@global.autocommit;

#查看会话变量
SELECT @@autocommit;

#给全局变量autocommit赋值
SET @@global.cutocommit=0;
SET @@global.cutocommit=1;

#给会话变量autocommit设置

/*
二、自定义变量
说明：变量是用户自定义的并不是有系统提供的
使用步骤：
声明
赋值
使用（查看，比较，运算）

1.用户变量
作用域：针对当前会话有效，等同于会话变量的作用域一样，只对当前有效
2.局部变量
作用域：仅仅在定义它的begin end 中有效，必须应用在begin end中的第一句话

*/
#1.声明并初始化用户变量
– 方式一：set @用户变量名=值
SET @ename=“小丁”;
– 查看用户变量名
SELECT @ename;

– 方式二：set @用户变量名：=值
SET @pname:=“哈哈”;
SELECT @pname;

#赋值
– 1.方式一：set @用户变量名=值
– 2.方式二：set @用户变量名:=值
– 3.方式三：select @用户变量名:=值
– 4.方式四：通过select into 例如：select 字段 into 变量 from表（把表中查询出来的字段赋值给变量）
SELECT COUNT(*) INTO @c FROM emp;

SELECT @c;

/*
局部变量：
1.声明：declare 变量名类型 default 值
2.赋值：set 局部变量=值
3.查看：select 局部变量
*/
SET NAMES gbk$
DELIMITER $
CREATE PROCEDURE my09()
BEGIN
#设置局部变量
DECLARE result VARCHAR(10) DEFAULT “”;
SET result=“淘汰”;
SELECT result;

END $

CALL my09$

#案例：创建存储过程实现用户是否登录成功
CREATE PROCEDURE my010(IN ename VARCHAR(10),IN pwd VARCHAR(10))
BEGIN
#声明局部变量
DECLARE result VARCHAR(10) DEFAULT “”;
SELECT COUNT(*) INTO result FROM admin WHERE user_name=ename AND user_password=pwd;
SELECT IF(result>0,“成功”,“失败”);

END $

#创建带inout模式参数的存储过程
#创建存储过程实现传入a和b的两个值，最终a和b都翻倍并返回
CREATE PROCEDURE my11(INOUT a INT,INOUT b INT)
BEGIN
SET a=a2;
SET b=b2;

END$

SET @al=10$
SET @b1=20$

CALL my11(@a1,@b1)$
SELECT @a1,@b1$

#创建存储过程实现根据员工编号查询员工的领导编号
CREATE PROCEDURE my12(INOUT m INT)
BEGIN
SELECT mgr INTO m FROM emp WHERE empno=m;

END $

SET @mm=7369$
CALL my21(@mm)$
SELECT @mm$

8 数据库备份和恢复
一、为什么进行数据备份
1.数据库故障 2.突然断电 3.病毒入侵 4.错误操作导致数据丢失
使用mysqldump命名备份数据 mysqldump它就是一个常用的备份工具
1.将create和insert into 语句保存到文本文件中 2它属于dos命令
二、备份数据库中所有数据
语法：mysqldump -u 数据库账号 -p数据库密码 -h ip地址数据库名>备份的文件名
例如：备份数据库store_ykt的所有数据备份在"D盘中的store_ykt.sql"
C:\Users\臻冉>mysqldump -u root -p123456 -h localhost store_ykt>d:\store.sql
三、备份数据库中部分数据
语法：mysqldump -u 数据库账号 -p数据库密码 -h ip地址数据库名表1 表2…>备份的文件名
C:\Users\臻冉>mysqldump -u root -p123456 -h localhost store_ykt emp dept>d:emp_dept.sql
四、备份数据库中表的部分数据
首先进入mysql客户端（小黑窗）
例如：备份员工表中有奖金的员工数据
语法：select * from emp where comm!=0 into outfile “备份的位置及其文件名”
mysql> select * from emp where comm!=0 into outfile “d:\comm1.sql”;
五、数据的恢复：
1.使用mysql命令恢复数据
例如：把D盘中的store.sql恢复到数据库copydb中，先创建一个copydb数据库
create database copydb;
语法：mysql -u 数据库账号 -p数据库密码数据库<所要恢复的数据
C:\Users\臻冉>mysql -u root -p123456 copydb<d:\store.sql
六、删除
1.删掉带有奖金的员工数据
DELETE FROM emp WHERE comm!=0;
2.恢复部分数据：
有奖金的员工恢复到copydb数据库中的emp表中，
语法：load data infile “所恢复的数据及其位置” into table 表名
首先进入到copydb数据库中 use copydb;
load data infile “d:\comm.sql” into table emp;