Mysql

一、数据库简介

文本形式存储数据存在很多弊端：执行效率低；占用内存。

DB：

DateBase数据库，本质就是文件集（多个文件）会按照特定存储规范进行数据增删改查；

DBMS：

DateBaseManagementSystem数据库管理系统，本质就是管理数据库文件的软件，Oracle Mysql

SQL：

Strctured Query Language：结构化查询语言，通过sql语言和DBMS进行交互

二、数据库分类

关系型数据库：

是经过数学理论验证过，可以保存现实生活中任何关系的数据库 ，存放数据是以表为单位

Oracle Mysql DB2 Sqlite Sqlserver都是关系型数据库。

非关系型数据库：

Redis数据库(非关系型数据库)

Key-Value

三、数据库相关SQL

创建数据库指定字符集	create database db1 character set utf8;
查看所有数据库	show databases;
查看某个数据库	show create database db1;
使用某个数据库	use 数据库名;
删除某个数据库	drop database db2;

四、表相关SQL

表的定义：表是数据库中存放数据的单元,任何数据都是存放到表中，类似于java中的Class,表中的字段对应Class的属性。

创建表		create table 表名(字段1名 类型,字段2名 类型,字段3名 类型);
查询所有表		show tables；
查看单个表属性(引擎和字符集)		show create table 表名；
查看表字段详情		desc 表名；
创建表时指定引擎和字符集		create table t1(id int,name varchar(10)) engine=myisam charset=utf8;
删除表		drop table t_stu;
修改表名称		rename table 原名 to 新名;
修改表的属性(引擎和字符集)		alter table 表名 engine=innodb charset=utf8;
添加表字段	在最后添加	alter table 表名 add fatherAge int;
	在最前添加	alter table 表名 add fatherAge int first;
	在某个字段后面添加	add fatherAge int after id;
修改字段名和类型		alter table 表名 change 原字段名 新字段名
修改字段类型和位置		alter table 表名 modify 字段名 类型 位置(first/after ***);
删除字段		alter table 表名 drop 字段名;
给表添加字段的时候可以给表的字段添加注释		create table t5(id int comment '用户的id',age int comment '用户的年龄');

执行原理:当在终端中输入完建表语句敲回车 终端会把写好的sql 发送给DBMS(mysql),然后解析到 create table 时 识别出要创建一个表, 在表名后面的括号内部的内容是决定此表有哪些字段；

innoDB:默认,支持数据库的高级操作,包括事务 主外键等；

myisam: 只具备基本的数据存储功能创建表时指定引擎和字符集；

五、数据相关SQL

插入语句：	全表插入格式	insert into 表名 values(值1,值2,值3);
	指定字段插入格式	insert into 表名 (字段1名,字段2名) values(值1,值2)；
	批量插入	insert into hero values(11,'美国队长',34),(12,'钢铁侠',43),(13,'蜘蛛侠',23),(14,'雷神',55);
	指定字段的批量插入	insert into hero (name) values('张三'),('李四'),('王五');
查询语句		select * from hero;
更新语句(修改)		update 表名 set age=25 where id=1; 不加where条件则对整表数据操作
删除语句		delete from 表名 where 条件;
TRUNCATE语句		truncate table hero;

六、乱码问题

1. 数据库字符集 要设置utf8

2. 表的字符集 设置为utf8

3. windows系统的命令行里 有些版本是gbk的编码格式

可以通过set names gbk;的方式把mysql接收到数据时的解码格式设置为gbk,这个位置的gbk 和数据库还有表的utf8没有关系

4.在windows系统中修改mysql默认的数据库编码:

1）. 找到安装文件中的my.ini的配置文件 在里面添加如下代码:

character-set-server=utf8

2）. 添加完之后 尽量重启下电脑

七、eclipse里面写sql

1.先下载mysql驱动包

mysql-connector-java-5.1.6.jar

2.window->show view ->other->Data Management->Data Source Exploer 和 SQL Results  Open

3.在Data Source Exploer选项卡里面的 DataBaseConnections上右键 new

4.选择MYSQL

5.点击下拉箭头右侧的加号

6.选择 5.1  点击JarList  clear All

7.点击 add jar在弹出窗口中 找到下载的 jar文件 然后OK

8.修改URL /后面的内容  改成自己电脑上数据库的名字

9.输入密码 没有密码则空着

10.点击test Connection  如果显示 Ping Successed则点击finish

11.在大桶上面右键 Open SQL...

12.type 中选择 mysql5.1 Name中选择 New Mysql

Database中选择 database  在最右侧如果显示Connected则可以开始写sql语句

八、关键词

关键词	特点	语法/解决办法
主键约束	非空并唯一	create table t1(id int primary key,age int);
自增约束	从最大值基础上+1，每张表只能有一个字段为自增字段	create table t2(id int primary key auto_increment,age int);
非空	不为空	create table t3(id int primary key auto_increment, age int not null);
注释	给表添加字段的时候可以给表的字段添加注释	create table t5(id int comment '用户的id',age int comment '用户的年龄');
数据的冗余	表内的数据出现大量重复	拆分表的形式避免或降低冗余的可能性
事务	数据库中sql语句执行的最小单元	见表九

九、事务：原子性，一致性，永久性，隔离性

查看自动提交状态	show variables like '%autocommit%'；
修改状态	set autocommit=0/1；
起始点	savepoint begin；
设置回滚点	savepoint s1；
提交	commit；
回滚	rollback to begin/s1;

十、SQL分类

DDL	Data Definition Language:数据定义语言	常见命令 create alter drop truncate
DML	Data Manipulation Language:数据操作语言	常见命令:insert update delete (select)
DQL(属于DML)	Data Query Language:数据查询语言	常见命令:select
TCL	Transaction Control Language:事务控制语言	常见命令:begin commit rollback savepoint
DCL	Data Control Language:数据控制语言	给用户分配权限相关的sql语言

十一、数据库数据类型

关键字	类型	字节数/长度	语法
Int（m）	整数	4	create table t_int (num int(10) zerofill);（m代表现实长度,当现实的数据长度小于m值时会补0 **一定要结合zerofill使用）
bigint		8	create table t_int (num bigint);
double（m，d）	浮点数	56.234   5,3	m代表数据的总长度,d代表小数点后面的位数 涉及高精度运算时使用decimal 通常涉及钱的地方使用decimal
decimal(m,d)
char(20)	字符串	固定长度(20长度)	执行效率高 最大255
varchar(20)		可变长度	节省空间 最大65535,但是超过255建议使用text
text		可变长度	最大65535
date	日期	月日	create table t(d1 date,d2 time,d3 datetime,d4 timestamp); insert into t values('2008-8-8','12:06:35','2008-08-12 14:30:22',null);
time		分秒
datetime		年月日时分秒	默认值null 最大值9999-12-31
timestamp		年月日时分秒	默认值是当前时间 最大时间2038-01-19

十二、没有条件简单的查询语句

查询语句			语法
查询表中所有的数据			select * from 表名；
查询表中所有数据，只查看某些列		列值为null ： is null	select empno,ename,job,comm from emp where comm is not null and comm>0;
		列值不为null： is not null
别名			select ename as '姓名' from emp;
去重复			select distinct job from emp;
= > <   >=  <= !=(<>)			select title from t_item where price!=8443;
between and			select price from t_item where price between 10 and 100;
in(a,b，c)			select category_id from t_item where category_id in (238,917,163);
AND			select empno,ename,deptno from emp where deptno!=10 and sal<3000;
OR			select ename,job,deptno from emp where deptno=30 or mgr=7698;
LIKE _ : 代表单个未知字符    % : 代表多个未知字符   以a开头的字符  a%      以a结尾 %a 包含a  %a%           第二个字母是a  _a%			1.案例：查询标题包含记事本的商品 select title from t_item where title like '%记事本%'; 2.案例：查询单价低于500的联想产品 select title,price from t_item where price<5000 and title like '%联想%';
ORDER BY 子句  默认升序 升序 asc,降序 desc			需求:按照分类id升序 价格降序 select category_id,price from t_item order by category_id,price desc;
Limit a，b【a:跳过的条数,b：每页的条数】			select name from user where id=1 order by id limit 0,1;
CONCAT() 实现多个字符串的拼接			select concat(id,name,money) as '呵呵' from user;
数值计算:+ - * / 7%2等效 mod(7,2)			select price,num,price*num 总价 from t_item;
日期相关函数	now() 获取当前日期+时间		select now(),curdate(),curtime();
	curdate() 获取当前日期
	curtime() 获取当前时间
	date(now()) 从日期和时间中获取日期		select date(now()), time(now());
	time(now()) 从日期和时间中 获取时间
	extract() 提取年月日时分秒		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_FORMAT() 函数	- %Y 4位年  2018 - %y 2位年   18 - %m 月     05 - %c 月      5 - %d 日 - %H 24小时 - %h 12小时 - %i 分 - %s 秒	select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');   案例：查询商品,并显示商品上传日期(x年x月x日) select title,date_format(created_time,'%Y年%m月%d日') from t_item;
str_to_date()函数	把字符串转成日期格式	举例:2018年10月22日 select str_to_date('2018年10月22日','%Y年%m月%d日'); -案例:14.08.2018 08:12:54 转成日期格式 select str_to_date('14.08.2018 08:12:54','%d.%m.%Y %H:%i:%s');
IFNULL(a,b)	age=ifnull(a,b) 如果a是null age=b 如果不是null age=a;	update EMP set comm=ifnull(comm,0);
聚合函数	sum(): 求和	select sum(num) from t_item;
	avg(): 求平均值	select avg(price) from t_item;
	count(): 计算数量	select count(*) from t_item;
	max(): 最大值	select max(price) from t_item;
	min(): 最小值	select min(price) from t_item;
字符串的函数	char_length(str) 获取字符串长度	select char_length('abc');
	instr(str,substr) 获取subStr在str中的位置从1开始	select instr('abcdefg','d');
	locate(substr,str)获取subStr在str中的位置从1开始	select locate('e','abcdefg');
	insert(str,start,length,newStr) 在str中从start位置替换newStr	select insert('abcdefghijk',3,5,'mm'); 结果:abmmhijk
	lower(str) 转小写	select lower('NBa');
	upper(str) 转大写	select upper('NBa');
	left(str,count)从左边截取count个字符串	select left('abcdefg',3);
	right(str,count)从右边截取count个字符串	select right('abcdefg',3);
	trim(str)去字符串两端的空格	select trim('    abc  ');
	substring(str,index) 从index位置开始截取到最后	select substring('abcdefg',2);
	substring(str,index,length);从index位置开始截取  length个长度	select substring('abcdefg',2,2);
	repeat(str,count) 重复	select repeat('ab',2);
	replace(str,old,new) 替换	select replace('mysql','my','your');
	reverse() 反转	select reverse('nba');
数学相关函数	floor(num) 向下取整	select floor(3.13);     3
	round(num) 四舍五入	select round(3.54);    4
	round(num,m) m代表小数点位数	select round(2.34245,2);   2.34 select round(1234.1234,-2);  1200
	truncate(num,m) 和上面一样但是非四舍五入	select truncate(2.34545,2);  2.34
	rand() 随机数	案例: 获取0-5的随机数 select floor(rand()*6); 案例: 获取5-10的随机数 select floor(rand()*6) + 5; 案例: 获取3到5的随机数 0-2 select floor(rand()*3)+3; 案例: 获取4到7的随机数 0-3  +4 select floor(rand()*4)+4;

根据以下员工表，部门表，商品表，商品分类表的字段回答问题

Emp表:字段：empno,ename,job,mgr,Hiredate,sal,comm,deptno;

Dept表:字段:deptno,dname,loc;

t_item:Id,category_id,item_type,title,sell_point,price,num,barcode,image,status,priority,created_time, modified_time ,datetime,created_user, modified_user;

t_item_category:Id,bigint(20),parent_id,name,status,sort_order,is_parent,created_time,modified_time , created_user,modified_user；

Day03 练习

1. 案例：查询没有上级领导的员工的编号，姓名，工资

select empno,ename,sal from emp where mgr is null;

2. 案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金

select ename,job,sal,comm from emp where comm is null;

3. 案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金

select empno,ename,job,sal from emp where comm !=0;

4. 案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号

select ename,job,mgr from emp where mgr is not null;

5. 案例：查询emp表中名字以‘S’开头的所有员工的姓名

Select ename from emp where ename like “S%”;

6. 案例：查询emp表中名字的最后一个字符是'S'的员工的姓名

Select ename from emp where ename like “%S”;

7. 案例：查询倒数的第2个字符是‘E’的员工的姓名

Select ename from emp where ename like “%E_”;

8. 案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名

Select ename from emp where ename like “%N__”;

9. 案例：查询emp表中员工的名字中包含‘A’的员工的姓名

Select ename from emp where ename like “%A%”;

10. 案例：查询emp表中名字不是以'K'开头的员工的所有信息

Select ename from emp where ename not like “K%”;

11. 案例：查询emp表中名字中不包含‘A’的所有员工的信息

Select ename from emp where ename not like “%A%”;

12. 案例：做文员的员工人数（job_id 中 含有 CLERK 的）

Select count(clerk) from job_id;

13. 案例：销售人员 job: SALESMAN 的最高薪水

 Select max(sal) from emp where job='SALESMAN';

14. 案例：最早和最晚入职时间

Select max(Hiredate),min(Hiredate) from emp;

15. 案例：查询类别 163的商品总库存量

Select num from t_item where category_id=163;

16. 案例：查询 类别 163 的商品

Select * from t_item where category_id=163;

17. 案例：查询商品价格不大于100的商品名称列表

Select title from t_item where price<=100;

18. 案例：查询品牌是联想,且价格在40000以上的商品名称和价格

Select title,price from t_item where price>40000 and title like “%联想%”;

19. 案例：查询品牌是三木,或价格在50以下的商品名称和价格

Select title,price from t_item where title like “%三木%”or price<50;

20. 案例：查询品牌是三木、广博、齐心的商品名称和价格

Select title,price from t_item where title like “%三木%”or  title like“%广博%”or  title like“%齐心%”;

21. 案例：查询品牌不是联想、戴尔的商品名称和价格

Select title ,price from t_item where  title not like “%联想%”and  title not like “%戴尔%”;

22. 案例：查找品牌是联想且价格大于10000的电脑名称

Select title from t_item where price>10000 and title like “%联想%”and category_id=163;

23. 案例：查询联想或戴尔的电脑名称列表

Select title from t_item where title like “%联想%”or  title like “%戴尔%”and category_id=163;

24. 案例：查询联想、戴尔、三木的商品名称列表

Select title from t_item where title like “%联想%”or  title like “%戴尔%”or  title like “%三木%” ;

25. 案例：查询不是戴尔的电脑名称列表

Select title from t_item where title not like “%戴尔%”and category_id=163;

26. 案例：查询所有是记事本的商品品牌、名称和价格

Select left(title,2),title,price from t_item where category_id=163;

27. 案例：查询品牌是末尾字符是'力'的商品的品牌、名称和价格

Select left(title,2),title,price from t_item where left(title,2) like “_力”;

28. 案例：名称中有联想字样的商品名称

Select title from t_item where title like “%联想%”;

29. 案例：查询卖点含有'赠'产品名称

Select title from t_item where sell_point like “%赠%”;

30. 案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。

Select empno,ename,job,sal from emp where sal between  1000 and 2000;

31. 案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号

Select ename,job,mgr, deptno from emp where deptno=10 and mgr is not null;

32. 案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。

Select empno,ename,job,sal from emp where ename like “%E%”;

33. 案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号

Select empno,ename,deptno from emp where deptno=10 or deptno=20;

34. 案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金

Select empno,ename,job,sal from emp where comm is null or ename not like “%T_”;

35. 案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号

Select ename,job,sal,hiredate,deptno from emp where sal>3000 or deptno=30;

36. 案例：查询不是30号部门的员工的所有信息

Select * from emp where deptno !=30;

37. 案例：查询奖金不为空的员工的所有信息

Select  * from emp where sal is not null;

38. 案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列

Select empno,ename,job from emp order by empno desc;

39. 案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列

Select ename,job,sal from emp where deptno in(10,30) order by sal;

40. 案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列

Select * from emp order by deptno,empno desc;

41. 案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。

Select empno,ename,sal,deptno,mgr from emp where sal>1000 or mgr is null order by deptno desc,sal;

42. 案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列

Select empno,ename,sal,comm from emp where ename not like “%S%”order by sal,empno desc;

43. 案例：统计emp表中员工的总数量

Select count(empno) from emp;

44. 案例：统计emp表中获得奖金的员工的数量

Select count(empno) from emp where comm is not null;

45. 案例：求出emp表中所有的工资累加之和

Select sum(sal) from emp;

46. 案例：求出emp表中所有的奖金累加之和

Select sum(comm) from emp;

47. 案例：求出emp表中员工的平均工资

Select avg(sal) from emp;

48. 案例：求出emp表中员工的平均奖金

Select avg(comm) from emp;

49. 案例：求出emp表中员工的最高工资

Select max(sal) from emp;

50. 案例：求出emp表中员工编号的最大值

Select max(empno) from emp;

51. 案例：查询emp表中员工的最低工资。

Select min(sal) from emp;

52. 案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。

 Select count(empno) as '员工的人数',sum(sal) as '工资的总和',avg(sal) as '平均工资',max(comm) as '奖金的最大值',min(comm) as '奖 金的最小值'from emp;

53. 案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。

Select deptno,count(empno),sum(sal) from emp  group by deptno order by count(empno),sum(sal) desc;

54. 案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。

Select deptno,avg(sal),min(sal),max(sal) from emp where sal between 1000 and 3000 order by avg(sal);

55. 案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列

Select count(empno),sum(sal),avg(sal),min(sal) from emp where mgr is not null group by job order by count(empno) desc,avg(sal);

56. 案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资

Select empno,ename,job,sal from emp where sal between 1000 and 3000;

57. 案例：查询emp表中奖金在500~2000之间所有员工的编号，姓名，工资以及奖金

Select empno,ename,sal,comm from emp where comm between 500 and 2000;

58. 案例：查询员工的编号是7369，7521，

Select * from emp where empno in(7369,7521);

59. 案例：查询emp表中，职位是ANALYST，

select * from emp where job='ANALYST';

60. 案例：查询emp表中职位不是ANALYST,

select * from emp where job not like “ANALYST”;

十三、GROUP BY、HAVING语句

概念	理解	语法
GROUP BY 语句	通常查询每个部门(性别/分类) 就以部门(性别/分类)为分组条件	每年的入职人数: Select count(*), extract(year from hiredate) year from emp group by year;
HAVING 语句	Where后的条件过滤不能使用聚合函数，这时使用Having代替Where	查询每个部门的平均工资,只显示平均工资大于2000的： select deptno,avg(sal) a from emp group by deptno having a>2000;
语法格式：select  from  where  group by having order by  limit;

 练习:

1.案例：查询emp表中，每个部门的平均工资高于2000的部门的编号,部门的人数,平均工资,最后根据平均工资进行升序排列。

Select deptno,count(empno),avg(sal)  s from emp group by deptno having s>2000 order by s;

2.案例：查询emp表中名字中不是以'K'开头的信息,每个部门的最低工资高于1000的部门的编号，工资总和,平均工资以及最低工资，最后根据平均工资进行升序排列。

Select deptno,count(sal),avg(sal) a,min(sal) s from emp where ename not like “K%”group by deptno having s>1000 order by a;

3.案例：查询emp表中部门编号是10,30号部门的员工,每个职业的最高工资低于5000的职业的名称,人数，平均工资，最高工资，最后根据人数进行升序排列，如果人数一致，根据最高工资进行降序排列。

Select job,count(empno) e,avg(sal),max(sal) s from emp where deptno in(10,30) group by job having s<5000 order by e,s desc;

4.案例：查询emp表中，每个部门的编号，人数，工资总和，最高工资以及最低工资，过滤掉最高工资是5000的部门，根据部门的人数进行升序排列，如果人数一致，则根据最高工资进行降序排列。

Select deptno,count(empno) e,sum(sal),max(sal) m,min(sal) from emp group by deptno having  m!=5000 order by e,m desc;

5. 案例：查询emp表中工资在1000~3000之间的员工信息，每个部门的编号，工资总和，平均工资，过滤掉平均工资低于2000的部门，按照平均工资进行升序排列

  Select deptno,sum(sal),avg(sal) s from emp where sal between 1000 and 3000 group by deptno having s>=2000 order by s;

6. 案例：查询emp表中名字不是以‘S’开头，每个职位的名字，人数，工资总和，最高工资，过滤掉工资是3000的职位，根据人数进行升序排列，如果人数一致，根据工资总和进行降序排列。

Select job,count(empno) c,sum(sal) s,max(sal) from emp where ename not like “S%”and sal !=3000 group by job order by  c,s desc;

7. 案例：查询emp表的信息，每个职位的名称，人数，平均工资，最低工资，过滤掉平均工资是3000的职位信息，根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列

Select job,count(empno) c,avg(sal) a,min(sal) from emp group by job having a<>3000 order by c desc,a;

十四、MySQL子查询、关联查询数据、笛卡尔积、等值连接、内连接左/右外连接

概念	定义/理解	使用原因	语法
MySQL子查询	在一条sql语句中嵌入另外一条sql查询语句。	使用子查询可以把多条sql语句整合成一条执行,提供效率。	拿最低工资的员工信息 select * from emp where sal=(select min(sal) from emp);
关联查询数据	同时查询多个表中的数据的查询方式	普通单表查询没有办法同时获取多个表里面的数据	使用 等值连接 内连接 和 左/右 外连接的方式进行关联查询
笛卡尔积	如果不写关联关系,查询出的两张表的乘积称为笛卡尔积
等值连接	将关联关系写在where 后面的两张表的查询方式		select * from A,B where A.x=B.x and age>18;
内连接	将关联关系写在on 后面	将关联关系和筛选条件分离,更清晰	select * from A join B on A.x=B.x where age>18;
左/右外连接	以某一张表为主表显示全部数据,另外一张表只显示关联数据的查询方式	内连接只能查询出有关系的数据,如果需要显示某一张表所有数据,则只能使用外连接，左外是以左边表为主表,右外是以右边表为主表	查询出所有员工名字和所对应部门的名字 select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno；

练习

1. 每个部门的人数,根据人数排序

Select deptno,count(deptno) c from emp group by deptno order by c;

  2. 每个部门中，每个主管的手下人数

select deptno,mgr,count(*) from emp group by deptno,mgr;

  3. 每种工作的平均工资

Select job,avg(sal) from emp from emp group by job;

  4. 每年的入职人数

select extract(year from hiredate) year,count(empno) from emp group by year;

  5. 少于等于3个人的部门

Select deptno from emp group by deptno having count(empno) <=3;

  6. 拿最低工资的员工信息

Select * from emp where sal=(select min(sal) from emp);

  7. 只有一个下属的主管信息

select * from emp where empno in(select mgr from emp group by mgr having count(empno)=1);

  8. 平均工资最高的部门编号

     select deptno,count(empno),avg(sal) a from emp group by deptno order by a desc limit 0,1;

  9. 下属人数最多的人，查询其个人信息

  select * from emp where empno=(select mgr from emp group by mgr order by count(empno) desc limit 0,1);

  10. 拿最低工资的人的信息

Select * from emp where sal=(select min(sal) from emp);

  11. 最后入职的员工信息

Select * from emp where hiredate=(select max(hiredate) from emp);

  12. 工资多于平均工资的员工信息

Select * from emp where sal>(select avg(sal) from emp);

13. 查询员工信息，部门名称

Select e.*,d.dname from emp e left join dept d on e.deptno=d.deptno;

14. 员工信息，部门名称，所在城市

Select e.*,d.dname,d.loc from emp e left join dept d on e.deptno=d.deptno;

15. DALLAS 市所有的员工信息

Select e.* from emp e left join dept d on e.deptno=d.deptno where d.loc=’DALLAS’;

16. 按城市分组，计算每个城市的员工数量

Select d.loc,count(e.empno) from dept d,emp e where e.deptno=d.deptno group by d.loc;

17. 查询员工信息和他的主管姓名

Select e.*,m.ename from emp e left join emp m on e.mgr=m.empno;

  18. 员工信息，员工主管名字，部门名

  第一种在第17上直接join的方式

select e.*,m.ename,d.dname from emp e left join emp m on e.mgr=m.empno left join dept d on m.deptno=d.deptno;

  第二种把17查询结果当成新表

   select n.*,d.dname

   from (select e.*,m.ename 主管名

   from emp e join emp m

   on e.mgr=m.empno)n join dept d

   on n.deptno=d.deptno;

注意:如果多表查询时出现重复字段名称 需要通过别名的方式进行区分

  20. 员工和他所在部门名

Select e.*,d.dname from emp e left join dept d on e.deptno=d.deptno;

Select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

Select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

  22. 案例：查询emp表中所有员工信息以及该员工上级领导的信息

  Select * from emp e join emp m on e.mgr=m.empno;

  23. 案例：查询emp表中名字中没有字母'K'的所有员工的编号，姓名，职位以及所在部门的信息

 Select e.empno,e.ename,e.job,d.* from emp e right join dept d on e.deptno=d.deptno where e.ename not like “%K%”;

  24. 案例：查询dept表中所有的部门的所有的信息，以及与之关联的emp表中员工的编号，姓名，职位，工资

Select d.*,e.empno,e.ename,e.job,e.sal from dept d left join emp e on d.deptno=e.deptno;

十五、表设计关联关系

表设计关联关系	定义	实例
自关联	当前表的数据和当前表里面的数据有关联关系	员工和上级领导
一对一关联	A表和B表,A表中的任何一条数据会对应B表中的一条数据	用户表和用户信息扩展表
一对多关联	A表和B表,A表中的任何一条数据会对应B表中一条或多条数据	部门和员工,分类和商品
多对多关联	A表和B表,A表中的任何一条数据会对应B表中一条或多条数据并且B表中的任意一条数据对应A表中的一条或多条	老师和学生,用户和角色

十六、如何让两张表建立关系

关联关系	关联条件
自关联	需要有一个字段记录当前表主键的值
一对一	需要在从表中有个字段表示主表的主键值
一对多	部门和员工为例,需要在多一端表中通过一个字段记录另外一张表的主键
多对多	需要准备一张关系表,表中保存两张表的主键值

 

1）创建1对1的表并练习

create table user(id int primary key auto_increment,username varchar(10),password varchar(10));

create table userinfo(id int,nick varchar(10));

insert into user values(1,'libai','admin'),(2,'zhaosi','admin');

insert into userinfo values(1,'李白'),(2,'赵四');

练习:查询每一个用户的所有信息

select u.*,ui.nick

from user u join userinfo ui

on u.id=ui.id;

2）创建多对多的表并练习

创建老师表(id,name)和学生表(id,name)每张表插入三条数据 思考:如何实现数据多对多的关系

create table teacher(id int primary key auto_increment,name varchar(10));

insert into teacher values(1,'唐僧'),(2,'刘备');

create table student(id int primary key auto_increment,name varchar(10));

insert into student values(1,'小明'),(2,'小红');

create table t_s(tid int,sid int);

insert into t_s values(1,1),(1,2),(2,1),(2,2);

-练习:查询小明的所有老师

1.得到小明的id

select id from student where name='小明'

2.通过学生id去关系表中查询对应的老师id

select tid from t_s where sid=(select id from student where name='小明');

3.去老师表中查询id等于第二步结果的老师

select * from teacher where id in(select tid from t_s where sid=(select id from student where name='小明'));

-练习2:查询所有老师对应的所有学生

select t.id tid,t.name tname,s.id sid,s.name sname

from teacher t join t_s ts

on t.id=ts.tid

join student s

on ts.sid=s.id;

-练习3:查询唐僧的所有学生

select t.id tid,t.name tname,s.id sid,s.name sname

from teacher t join t_s ts

on t.id=ts.tid

join student s

on ts.sid=s.id where t.name='唐僧';

十七、连接方式和关联关系的区别

连接方式:包括 内连接 外连接(左外,右外),是指查询两张表时使用的查询方式

关联关系: 一对一,一对多,多对多, 是指两张表之间存在的逻辑关系

十八、数据库设计之权限管理

概念	定义	实现方式
权限管理	不同的用户登录网站后可能会有不同的权限,实现此功能的过程称为权限管理	总共需要5张表 用户表            角色表                模块表 用户和角色关系表   角色和模块的关系表

具体实现：

 -创建5张表

create table user(id int,name varchar(10));

create table role(id int,name varchar(10));

create table module(id int, name varchar(10));

create table u_r(uid int, rid int);

create table r_m(rid int, mid int);

-插入主表数据

insert into user values(1,'小明'),(2,'小红');

insert into role values(1,'游客'),(2,'管理员');

insert into module values(1,'增'),(2,'删'),(3,'改'),(4,'查');

-插入关系数据

insert into u_r values(1,1),(2,1),(2,2);

insert into r_m values(1,4),(2,1),(2,2),(2,3),(2,4);

 

-查询小红对应的所有权限(子查询)

1. 得到小红的id

select id from user where name='小红'

2. 通过小红id找到对应的角色id

select rid from u_r where uid=(select id from user where name='小红')

3. 通过角色id 找到对应的权限id

select mid from r_m where rid in(select rid from u_r where uid=(select id from user where name='小红'))

4. 通过权限id 找到权限的名字

select name from module where id in(select mid from r_m where rid in(select rid from u_r where uid=(select id from user where name='小明')));

-查询小红对应的所有权限(内连接)

select distinct m.name

from user u join u_r ur

on u.id=ur.uid

join r_m rm

on ur.rid=rm.rid

join module m

on rm.mid=m.id

where u.name='小明';

十九、视图：工作中使用视图时只是为了DQL(数据查询)

		理解	使用原因/语法
视图		1、在数据库中存在多种对象,表和视图都是数据库中的对象,创建视图时视图名称不能和表名相同； 2、视图实际上就代表了一段sql查询语句,也可以理解成是一张虚拟的表,虚拟表中的数据会随着原表数据改变而改变。	1、有些数据的查询需要书写大量的sql语句,每次书写比较麻烦； 2、使用视图可起到重用sql语句的作用,可以通过视图隐藏敏感信息
创建视图的格式		create view 视图名 as 子查询	创建员工视图 隐藏工资字段 create view view_emp_nosalas (select empno,ename,job,mgr,hiredate,comm from emp);
视图分类	简单视图	创建视图的子查询中 不包含去重,函数,聚合,关联查询的视图	create view v_emp_20 as (select * from emp where deptno=20 and sal<3000);
	复杂视图	和简单视图相反	create view v_emp as (select deptno,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);
查看视图		查看方式和表一样	select * from 视图名;
视图数据污染		往视图中插入一条视图中不显示但是在原表中显示的数据（在视图中只有进行insert操作时才会造成数据污染    因为update和delete只能操作视图中存在的数据）	insert into v_emp_20 (empno,ename,deptno) values (43234,'孙悟空',30);
避免视图污染		使用 with check option 关键字	create view v_emp_30 as (select * from emp where deptno=30) with check option;
修改视图			create or replace view v_emp_20 as (select * from emp where deptno=20 and sal<1000);
删除视图			drop view if exists view_emp_10;

案例1:创建视图显示每一个部门名所对应的员工名

create or replace view v_emp as (select d.dname,e.ename

from emp e join dept d

on e.deptno=d.deptno);

案例2:修改上面的视图在上面题的前提下只显示工资在3000以内的

create or replace view v_emp as (select d.dname,e.ename

from emp e join dept d

on e.deptno=d.deptno where e.sal<3000);

案例3:删除上面创建的视图;

drop view if exists v_emp;

 

二十、 索引

		理解	使用原因/语法
索引		索引是用来提高查询速度的技术,它类似于一个目录,查询数据时会先从目录中对数据进行定位,然后直接找到数据的所在位置	不使用索引的话,查询数据会按照磁盘块一块一块的去查,如果数据量很大,效率很低.
索引分类	聚簇索引	数据是有序保存在磁盘中的,在树的叶子里面保存地址主键和数据	在mysql中数据库会为主键创建聚集索引
	非聚集索引	在树的叶子里面保存的是数据存在的磁盘块的地址
索引内部实现原理:B+tree		数据库中创建索引的过程是数据库内部自己控制,然后使用索引的过程也是数据库自己操作的不需要程序员干涉
索引是越多越好？		1、索引会占用磁盘空间所以创建索引需谨慎,只创建有查询需求的索引； 2、索引要建立在大量数据的表中,如果数据量不够大,使用索引可能会降低查询效率
创建索引格式		create index 索引名 on 表名(字段名(长度));		-创建索引之前先查询看时间 select * from item2 where title='100'; -创建title索引     create index index_title on item2(title); -再次查询看时间 select * from item2 where title='100';
查看索引				show index from item2;
删除索引				drop index index_title on item2;
复合索引		复合索引即创建索引的时候指定多个字段,此时如果查询数据正好过滤条件为这两个字段的话,可以降低磁盘块的访问量从而提高查询效率		select * from item2 where title='100' and price<100; create index index_title_price on item2(title,price);
创建表的时候直接添加索引				create table t_index(id int,age int,index index_age(age));

 

二十一、MySQL 约束

	理解	语法
约束	约束就是对表字段的数据进行限制的规则
唯一约束 unique	添加唯一约束的字段 不能插入重复数据否则报错	create table t_uni(id int,age int unique);
主键约束(PRIMARY KEY)		-创建表时添加主键 create table t(id int primary key auto_increment); -创建表后添加主键 alter table t1 add primary key(id); -删除主键约束 alter table t1 drop primary key;
自增约束 auto_increment	1. 当字段赋的值为null时 ,字段会自动增长； 2. 如果删除了某条数据,自增数值不会减； 3. 如果指定了一个比较大的值,下次插入数据会从最大的值基础上+1；4. 如果使用delete删除数据,自增的值不会从头开始； 5. 如果使用truncate table t_auto的方式删除 则从1开始自增。
外键约束(FOREIGN KEY)	外键约束是保证一个表(自关联)或两个表之间数据一致性和完整性的约束	外键总结: 保证一个表或两个表之间数据的一致性和完整性,工作不怎么用,外键的值是关联表的主键,值可以是null可以重复,不能是不存在的数据,使用外键必须两张表使用innodb引擎
非空约束(NOT NULL)	让该字段的数据不能为null 否则报错
默认约束(DEFAULT)	给字段设置默认值	create table t_def(id int,age int DEFAULT 0);