MySQL数据库管理系统与SQL

数据库常识

DBMS丶DB丶SQL三者之间的关系
DBMS:DataBase Management System 数据库管理系统,通过编译SQL语言操作数据库DB数据.
sql:是结构化查询语言,是一门标准通用的高级语言,适用于所有的数据库产品,写好的sql语言通过DBMS编译后操作DB的数据.
DB:DateBase 数据库,数据库实际上在硬盘上以文件形式存在.
Mysql全称为数据库管理系统,属于DBMS,DBMS去编译执行SQL语句来操作数据库DB当中的数据.

SQL脚本
文件以sql结尾的被称为sql脚本,当一个文件的扩展名是.sql,并且文件中编写了大量的sql语句,就称为sql脚本,可以直接使用source命令执行sql脚本,sql脚本的数据量太大的时候,文件无法打开,就可以使用source命令完成初始化.

Mysql数据库管理系统

表table

表table是数据库的基本组成单元,所有数据都是以表格的形式组织,作用是可读性强.

表table包括行和列:行被称为数据data.列被称为字段column

字段column应该包括:字段名,数据类型,相关的约束

表名在数据库一般建议以:t_或者tbl_开始.
创建表语法格式:
drop table if exists 表名;
create table 表名(
字段名1 数据类型,
字段名3 数据类型,
…);

插入数据

insert语句插入数据
语法格式:insert into 表名 (字段名1,字段名2,…)values(值1,值2,…),表名后的字段可以省略不写,但是默认得对应表中的字段,可以一次性插入多条数据,字段的数量和值的数量相同,并且数据类型要对应相同,如果只是插入部分的字段数据,没有设置默认值,没有插入的数据默认为null,插入数据的字段可以写一次,值可以连续写多次.

将查询结果插入到另一张表
insert into 表名 select * from 另一张表名;
注意表的字段相互对应

表的复制语法

create table 表名 as select 字段 from 表名;
将查询结果当做表创建出来

修改数据

update修改表语法:
update 表名 set 字段名1=值1,字段名2=值2…where 条件;
如果没有条件整张表数据会全部更新

删除数据

删除表的数据语法:
delete from 表名 where 条件;
如果没有条件会全部删除.

删除大表的数据(重点)语法:
truncate table 表名;
表被截断,不可回滚,永久丢失.

CRUD是直接写到java程序中,修改表的结构直接使用工具操作,主要掌握CRUD增删改查.

MySQL常用命令

MySQL数据库管理系统的常用命令
show databases; 查询所有的数据库
creadte database 数据库名; 创建数据库
drop database 数据库名;删除数据库
use 数据库名; 使用数据库
show tables; 查询正在使用的数据库的所有表
source 脚本文件的绝对路径.sql 导入数据到数据库
desc 表名; 查看当前正在使用数据库的表结构.
select database(); 查看当前正在使用的数据库的名字.
select version(); 查看当前正在使用的数据库的版本
\c或者Ctrl + C结束当前命令.
show tables from 数据库名; 查看其他数据库的表
show create table 表名; 查看建表的语句,.sql文件的内容.

导入数据的步骤:
登陆mysql
查看所有数据库show databases;
创建数据库creadte database 数据库名;
使用数据库use 数据库名;
查看数据库的表show tables;
初始化数据source sql语句所在的绝对路径.xxsql,注意路径不能有中文,直接把sql脚本拖到DOS窗口.
最后初始化完成会有三张表dept,emp,salgrade.

MySQL创建表字段常用的数据类型

int 整数型
bigint 长整型
float 浮点型
char 定长字符串
varchar 可变长字符串
date 日期类型
BLOB 二进制大对象(存储图片,视频等) Binary Large OBject
CLOB 字符大对象(存储较大文本,比如,可以存储4G字符串) Character Large OBject

关于char和varchar的使用
char的长度是固定的,一般用于性别,生日等
varchar的长度是动态的,一般用于姓名,简介等

BLOB和CLOB的使用
BLOB:比如海报视频,视频一般不放在数据库,图片可以.
CLOB:比如电影情节,文本很大

SQL语言

SQL语言的分类
DQL 数据查询语言
DML 对表的数据进行增删改.
DDL create,drop,alter,对表进行增删改.
TCL commit提交,rollback回滚.
DCL grant授权,revoke撤销权限等.

SQL语法规则:
sql语句不区分大小写,任何一条sql语句都是以;结尾

sql语句的字段可以参与运算,可以取别名用as,as可以省略

sql语句要求字符串必须使用单引号括起来,虽然MySQL支持双引号,但是不通用

查询表的所有字段可以使用*,虽然很方便,但是效率很低,写在Java程序的不要写*

运算符between…and…的使用数字必须是左小右大,字符串是左闭右开,比如between ‘A’ and ‘C’,只查A,B

数据库的null不是值,只是代表空,不能用等号衡量,必须使用is null或者is not null,如果是运算,有null参与的结果都为null

运算符and和or联合使用,and优先级高于or,会先执行and语句,当不确定优先级的时候使用小括号,小括号是最优先执行的.

运算符in等同于or

运算符like有两个特殊符号必须掌握,百分号%表示任意多个字符,下划线_表示任意一个字符

排序用order by默认是asc升序,降序是desc,可以同时对多个字段进行排序,越靠前的字段起主导作用,只有当前面的字段无法完成排序的时候,比如数据相等了,才会启用后面的字段.order by后面还可以跟数字,比如跟1,就只是对第一列的字段进行排序.

分组函数/聚合函数/多行处理函数一共五个sum求和,count计数,avg平均值,max最大值,min最小值.
分组函数必须先分组了才能使用,如果没有分组默认整张表是一组.
分组函数是对某字段所有列数据进行操作的.运算时分组函数会自动忽略null,不会受语法规定null参与运算的结果都为null的限制.
分组函数不能直接使用在where语句当中,因为where优先group by执行,执行前还没有分组,也就没有分组函数.
当一条语句有group by的话,select后面只能跟分组函数和参与分组的字段.
多个字段可以连起来一起分组.

isnull语句属于单行处理函数,可以把字段为null的改为其他,比如ifnull(comm,0)表示薪资为null的变成数字0,因为语法规定null参与运算的结果都为null

having必须是跟group by联合使用,作用是再次筛选过滤.

distinct的作用是给字段的数据去重,只能出现在所有字段的最前面,去重的目标是所有字段联合起来的数据,比如1,2跟2,1是不同的.

inner join是用于内连接,虽然inner可以省略,但是带着可读性好些.

union的作用是将查询结果集相加,两张不相干的表也可以相加,字段是数量必须一致,并且字段名以第一个表的字段名为主

limit是mysql特有的用来分页查询,语法的机制是limit startIndex,length,startIndex表示起始位置,length表示取几条数据

limit分页每页显示的数据条数算法:
第N页 = (当前页数 - 1) * 数据条数,数据条数

TimeStampDiff(间隔类型,前一个日期,后一个日期);是MySQL数据库管理系统用来计算时间差的,间隔类型可以是YEAR年,QUARTER季度,MONTH月,WEEK星期,DAY天,HOUR小时,MINUTE分钟,SECOND秒

SQL语句案例(单表)

一个完整的select语句格式:
select 字段5
from 表名 1
where… 2
group by…3
having…4
order by…6
limit…7

找出工资不等于3000的员工

第一种:
select ename,sal from emp where sal != 3000;
第二种:
select ename,sal from emp where sal<> 3000;

找出工资在1100到3000的员工

第一种:
select ename,sal from emp where sal >= 1100 and sal <=3000;
第二种:
select ename,sal from emp where sal between 1100 and 3000;

找出哪些员工津贴的为null

select ename,comm from emp where comm is null;

找出工作岗位是MANAGER和SALESMAN的员工

第一种:
select job,ename from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种:
select job,ename from emp where job in('MANAGER','SALESMAN');

找出薪资大于1000并且部门编号是20或30部门的员工

select sal,deptno,ename from emp where sal > 1000 and (deptno = 20 or deptno = 30);

找出薪资不是800或者1100的员工

select sal,ename from emp where sal not in(800,1100);
select sal,ename from emp where sal != 800 and sal != 1100;

找出名字含有o的员工

select ename from emp where ename like '%o%';

找出名字有下划线的员工

select ename from emp where ename like '%\_%';

按照工资升序,找出员工名和薪资

select ename,sal from emp order by sal;

按照工资降序排列,当工资相同在按照名字的升序排列

第一种:
select sal,ename from emp order by sal desc,ename asc;
第二种:
select sal,ename from emp order by 1 desc,2 asc;

找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排列

select job,ename,sal from emp where job = 'SALESMAN' order by sal desc;

求员工的总人数

select count(*) from emp;

计算每个员工的年薪

//comm是津贴
select ename,(sal + ifnull(comm,0)) * 12 as yearsal from emp;

找出工资高于平均工资的员工信息

第一步,先找出平均工资
select avg(sal) from emp;
第二步,找出工资高于平均工资的员工
select * from emp where sal > (select avg(sal) from emp);

找出每个工作岗位的最高薪资

select job,max(sal) from emp group by job;

找出每个部门不同工作岗位的最高薪资

select deptno,job,max(sal) from emp group by deptno,job;

找出每个部门的最高薪资,要求薪资大于2900的数据

第一种:用where效率高
select deptno,max(sal) from emp where sal > 2900 group by deptno;
第二种:效率低
select deptno,max(sal) from emp group by deptno having max(sal) > 2900;

找出每个部门的平均薪资,要求显示平均薪资大于2000的数据

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

统计岗位的数量

select count(distinct job) as '岗位数' from emp;

SQL语句案例(多表重点)

找出每个员工的部门名称,要求显示员工名和部门名
//等值连接

//第一种:
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;
//第二种:子查询
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

找出每个员工的工资等级,要求显示员工名,工资,工资等级
//非等值连接

select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

找出每个员工的上级领导,要求显示员工名和对应的领导名
//自连接

select e.ename '员工名',e2.ename '领导名' from emp e inner join emp e2 on e.mgr = e2.empno;
//使用内连接的KING的上级是null,没有匹配的所以没有查,所以一般使用外连接,可以查询所有的数据,没匹配上默认为null
select e.ename '员工名',e2.ename '领导名' from emp e left join emp e2 on e.mgr = e2.empno;

找出每个员工的部门名称以及工资等级
//内连接,外连接的使用场景:当字段的数据可能是空时,选外连接.

select e.ename,d.dname,e.sal,s.grade from emp e join dept d on e.deptno = d.deptno left join salgrade s on e.sal between s.losal and s.hisal;

找出每个部门平均薪资的薪资等级

第一步:找每个部门的平均薪资
select deptno,avg(sal) as 'avgsal' from emp group by deptno;
第二步:找部门平均薪资的薪资等级,把第一步当成临时表t
select t.*,s.grade from salgrade s join (select deptno,avg(sal) as 'avgsal' from emp group by deptno) t on t.avgsal between s.losal and s.hisal;

找出每个部门薪水等级的平均值

第一步:找出每个员工的薪水等级
select e.ename,s.grade from emp e join salgrade s on e.sal between losal and hisal;
第二步:按照deptno分组,求grade的平均值
select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;

找出工作岗位是SALESMAN和MANAGER的员工
union的使用

第一种:
select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
第二种:
select job,ename from emp where job in('SALESMAN','MANAGER');
第三种:
select job,ename from emp where job = 'SALESMAN'
union select job,ename from emp where job = 'MANAGER';

取工资前五名的员工

select ename,sal from emp order by sal desc limit 5;

找出工资排名在第4到第9名的员工

select ename,sal from emp order by sal desc limit 3,6;

将部门为10的loc修改为’SHANGHAI’,将部门的名称修改为RENSHIBU

update dept set loc = 'SHANGHAI',DNAME = 'RENSHIBU' where deptno = 10;

给两个列或多个列添加unique
usercode username联合使用

drop table if exists t_user;
create table t_user(
	id int unique,//列级约束
	usercode varchar(255),
	username varchar(255),
	unique(usercode,username)//表级约束
)

SQL语言了解内容

连接查询
在实际开发中,一般都是多张表联合查询最终的结果.
一般一个业务都会对应多张表,比如学生和班级,起码两张表,如果都在一张表上会造成数据的重复,导致数据的冗余.

连接查询分类:
SQL92:DBA数据库管理员,老一些的还在使用这个语法
SQL99:比较新的,学这个.

笛卡尔积现象
当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积.

表的别名
开发中一般都会给表起别名,作用是提高执行效率,可读性强.

避免笛卡尔积现象,加条件过滤
匹配的次数还是两张表的乘积,只是从中筛选符合条件的数据.

表的连接方式划分:
内连接:等值连接,非等值连接,自连接
外连接:左外连接(左边是主表),右外连接(右边是主表)
全连接:(很少用,不讲)

内连接
假设两张表A和B进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,就是内连接,AB表没有主副之分,是平等的,没有匹配上就不查了.

外连接
假设AB表进行连接,使用外连接的话,AB两表中有一张是主表,一张是副表,主要查询主表的数据,附带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出null与之匹配.
最大的特点是主表的信息无条件的全部查询出来.

子查询
select语句当中嵌套select语句,被嵌套的select是子查询,可以出现在select,from,where后面.

union可以将查询的结果集相加,注意两个SQL的字段的个数必须是相同的.

limit是mysql特有的,Oracle分页的是rownum
语法机制 limit startIndex,length.
startIndex表示起始位置默认从0开始,length表示取几个.
公式:已知当前页数,每页显示的数据条数.
limit (当前页数 - 1) * 数据条数,数据条数

约束

在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的合法性,有效性,完整性.

常见的约束:
非空约束:not null 约束的字段不能为null
唯一约束:unique 约束的字段不能重复,但是可以为null,多个null,约束单个字段叫列级约束,约束多个字段联合起来叫表级约束.注意not null约束只有列级约束,没有表级
主键约束:primary key 约束的字段不能为null也不能重复.简称PK,在字段后加是列级约束,单独写是表级约束.
外键约束:foreign key 简称FK
检查约束:check,这个只有Oracle数据库才有,MySQL没有.

主键的相关术语
主键约束,主键字段,主键值

主键的作用是表的唯一标识,一张表的主键只有一个.

主键的分类
主键分为单一主键,复合主键,自然主键,业务主键,常用的是单一主键,自然主键不跟业务挂钩,推荐使用,复合主键违背建表的三范式不常用,业务主键跟业务挂钩不常用

主键自增auto_increment
字段 数据类型 parimary key auto_increment,
字段自动维护一个自增的数字,从1开始,依次递增.
Oracle数据库的自增机制叫序列sequence对象.

外键约束的相关术语
外键约束,外键字段,外键值

外键约束foreign key
A表引用B表的字段,A表叫子表,B表叫父表.
语法:foreign key(字段) references t_其他表(其他表字段)
外键可以为null,外键引用其他表的字段,被引用的字段不一定是主键,但至少具有unique约束.
顺序要求:
删除数据先删除子表,再删除父表.
添加数据先添加父表,再添加子表.
创建表先创建父表,在创建子表.
删除表先删除子表,再删除父表.

存储引擎(了解)

完整的建表语句
create table `t_x`(
`id` int(11) default null
)engine=InnoDB default=utf8;

在MySQL凡是标识符都是可以用飘号括起来,但是不建议用,不通用.

建表的时候可以指定存储引擎,指定字符集,MySQL默认的存储引擎是InnoDB,默认字符集是utf8

存储引擎是MySQL独叫的.Oracle相同的机制没有名字,就是表的存储方式.

使用show engines \g 查看数据库支持的存储引擎
MySQL支持的存储引擎有9个,最常用的有MyISAM存储引擎,InnoDB存储引擎

MyISAM引擎:
是MySQL最常用的引擎,采用三个文件组织一张表.
格式文件:存储表结构的定义(mytable.frm)
数据文件:存储表行的内容(mytable.MYD)
索引文件:存储表上的索引(mytable.MYI)
优点:可以压缩节省存储空间,可以转换成只读表,提高检索效率
缺点是不支持事务

InnoDB引擎:
优点:支持事务,行级锁,外键等.这种存储引擎数据得到安全保障,是MySQL的默认存储引擎.
表的结构存储在xx.frm文件
缺点:数据存储在tablespace表空间,无法被压缩,无法转换成只读.
MySQL数据库崩溃后会提供自动恢复机制.
InnoDB支持级联删除和级联更新.

MEMORY引擎:
优点:查询速度最快
缺点:不支持事务,数据容易丢失,因为所有的数据和索引都是存储在内存.

事务

一个事务是一个完整的业务逻辑单元,不可再分.
只对DML增删改有效,因为只有DML和数据库的表有关,两条以上的DML必须同时成功或者失败就需要事务机制.

事务的存在就是为了保证数据的完整性,安全性.
通常一个事务需要多条DML语句共同联合完成,如果只需要1条DML语句就能完成根本不需要事务.

事务的四大特性:ACID
原子性:事务是最小的工作单元,不可再分.
一致性:事务必须保持多条DML语句同时成功或者失败
隔离性:事务A与事务B相互隔离,互不影响
持久性:最终数据必须持久化到硬盘文件,事务才算成功的结束.

关于事务的隔离性:理论上隔离级别有4个
第一级别:读未提交read uncommitted,对方事务还没有提交,我们当前事务可以读取到对方未提交的数据.读未提交存在脏读现象,表示读到了脏的数据.
第二级别:读已提交read committed,对方事务提交之后的数据我方可以读取到,虽然解决了脏读现象,但是不可重复读
第三级别:可重复读repeatable read,虽然解决了不可重复读,但是读到的数据是虚拟的幻想
第四级别:序列化读/串行化读,虽然解决了所有问题,但是效率低,事务需要排队
Oracle默认的隔离级别是读已提交
MySQL默认的隔离级别是可重复读

演示事务的回滚和提交

默认是自动提交的,只要执行任意一条任意DML语句就提交一次.

准备表

drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);

插入数据

insert into t_user (username) values('张三');

rollback回滚
再次查询还是有数据,说明执行DML语句自动提交,不能回滚了.

演示关闭MySQL自动提交功能

start transaction;//关闭自动提交的命令

插入数据

insert into t_user (username) values('李四');

//这里如果提交了就会有李四的记录,没提交直接回滚了就没有李四
rollback回滚
再次查询只有张三的记录
如果回滚前用commit提交了,持续化到硬盘了就会有李四的记录

演示事务的隔离级别

演示读未提交read uncommitted
使用数据库use mydatabase;
设置事务的全局隔离级别:

set global transaction isolation level read uncommitted;//读未提交

查看事务的全局隔离级别:

select @@global.transaction_isolation,@@transaction_isolation;

退出MySQL重新登陆
两个DOS窗口都关闭自动提交后在第二个DOS插入数据

insert into t_user(username) values('王五');

成功插入数据不提交也不回滚,再次查询数据发现可以查到插入的数据,这种隔离机制就是读未提交

演示读已提交
设置事务的全局隔离级别:

set global transaction isolation level read committed;

查看事务的全局隔离级别:

select @@global.transaction_isolation,@@transaction_isolation;

退出MySQL重新登陆
两个DOS都关闭MySQL自动提交

start transaction;//关闭自动提交的命令

第二个DOS插入数据commit提交

insert into t_user(username) values('王五');

再次查询可以查到commit提交的数据,如果没有commit插入的数据不会更新到数据库

演示可重复读
不管第二个DOS怎么修改,第一个DOS读到的数据都是一样的,是备份的幻想

登陆数据库使用mydatabase
设置事务的全局隔离级别:

set global transaction isolation level repeatable read;//读未提交

关闭MySQL自动提交功能

start transaction;//关闭自动提交的命令

在第二个DOS随便修改前提是没有commit提交,第一个DOS查询的结果都是修改前的数据

演示序列化读/串行化读
必须得DML语句执行并且commit提交了,另一个DOS窗口才能查询到结果,不然会一直等待

登陆mysql使用mydatabase数据库设置全局隔离级别

set global transaction isolation level serializable;

关闭mysql自动提交

start transaction;//关闭自动提交的命令

插入数据

insert into t_user(username) values('小路');

另一个DOS窗口查询会进入等待状态,直到DOS窗口commit提交了才会查询出结果

索引

索引就相当于书的目录,通过目录快速的找到对应的资源,数据库方面,查询一张表有两种检索方式:第一种全表扫描,效率低,第二种根据索引检索,效率高

索引的原理是缩小了扫描的范围,达到提高检索效率的目的

索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,需要数据库不断的维护,维护需要成本.

如果表的数据经常被修改就不适合添加索引,因为数据一旦修改,索引需要重新排序进行维护

添加索引是给某一个字段,或者说某些字段添加索引.

添加索引的条件是:数据量庞大,该字段很少的DML操作,该字段经常出现在where字句中

主键和unique约束的字段会自动添加索引,主键的查询效率最高尽量根据主键检索.

查看sql语句的执行计划,select前加explain

explain select ename,sal from emp where sal = 5000;

结果类型type是all,rows是14

创建索引对象语法

create index 索引名称 on 表名(字段名);

给薪资sal字段添加索引

create index emp_sal_index on emp(sal);

再次查看sql语句执行计划,结果类型type是ref,rows是1,快速定位了,并没有全部扫描

索引会自动排序,底层的数据结构是:B + Tree

删除索引对象

drop index 索引名称 on 表名;

索引的实现原理
通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的物理地址,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表的数据,效率是最高的.
select ename from emp where ename = ‘SMITH’;
通过索引转换为:
select ename from emp where 物理地址 = 0x3;

索引的分类
单一索引:给单个字段添加索引
复合索引:给多个字段联合起来添加一个索引
主键索引:主键上自动添加索引
唯一索引:有unique约束的字段上会自动添加索引

当模糊查询时,第一个通配符使用的%,索引会失效

视图(了解)

视图是站在不同的角度去看到数据,比如同一张表的数据,通过不同的角度去看待

创建视图

先复制备份表
create table emp_bak as select * from emp;

只有DQL语句才能以视图对象的方式创建出来

create view myview as select empno,ename from emp_bak;

通过视图修改原表数据

update myview set ename = '小新',sal = 1 where empno = 7369;

通过视图删除原表数据

delete from myview where empno = 7369;

删除视图

drop view myview;

对视图进行增删改查会影响到原表数据,只是通过视图影响原表数据的,不是直接操作的原表

视图的作用:
视图可以隐藏表的实现细节,保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD

DBA命令导入/导出数据

DBA命令
将数据库当中的数据导出,在DOS窗口执行命令
mysqldump导出,bjpowernode库,>到,SQL脚本文件.sql目录,-uroot -p权限

mysqldump bjpowernode>SQL脚本文件.sql -uroot -p

导出数据库的指定表

mysqldump bjpowernode 表名>SQL脚本文件.sql -uroot -p

导入数据

create database 数据库名;
use 数据库名;
source SQL脚本.sql

数据库设计三范式

设计范式:是设计表是依据,按照三范式设计表不会出现数据冗余

第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分

第二范式:建立在第一范式的基础上,所有非主键字段完全依赖主键,不可产生部分依赖

第三范式:建立在第二范式的基础上,所有非主键字段直接依赖主键,不能产生传递依赖

多对多:三张表,关系表两个外键

一对多:两张表,多的表加外键

一对一:两种方案
第一种主键共享
第二种外键唯一

主键共享,两张表,一张表的主键被其他表引用,这张表的主键是主键加外键pk+fk,这个外键是另一张表的主键.

外键唯一,两张表,一张表的主键当成另一张表的外键+unique唯一

作业题

一个完整的select语句格式是怎样的,顺序是怎么样的

select * from emp where … group by … having … order by … limit …
where 1,group by2, having3,* 4,order by5,limit 6

SQL语句出现字符串的要求是什么

必须使用单引号’'括起来,

使用between…and需要注意什么

数字必须左小右大,字符串左闭右开

谈谈你对null的理解

数据库的null不是值,只是代表空,不能用等号衡量,必须是is null或者is not null
有null参与的运算结果都为null

and,or,in的区别

and:表示并且,跟or联合使用时,优先级高于or
or:表示或者
in:等同于or

like是什么,特殊符号的含义是什么

like是模糊查询,%表示任意多个字符,_表示任意一个字符

分组函数的字段分别是哪些,有什么用

min:求字段的最小值
max:求字段的最大值
count:计数
avg:求平均值
sum:求和

ifnull有什么用,怎么用

ifnull可以把字段为null的空改为其他值参与运算
比如ifnull(sal,0),就把sal为null的改成0参与运算

怎么去重,有什么要求

使用distinct去重,位置必须在所有字段的最前面,给所有字段去重

怎么将表的查询结果集相加,需要注意什么

使用union,两张表的字段数量必须相同,字段名以第一个表为主

为什么用数据库设计三范式,怎么设计

防止数据库的数据出现冗余
三范式:
1.任何表都有主键,字段原子性不可再分
2.建立在第一范式基础上,所有非主键字段完全依赖主键
3.建立在第二范式基础上,所有非主键字段直接依赖主键
关系:
多对多,三张表,关系表两个外键
一对多,两张表,多的表加外键
一对一,主键共享或者外键唯一

----------------------------------------------------------------------
取每个部门最高薪水的人员名称

第一步:取每个部门的最高薪资
select deptno,max(sal) as maxsal from emp group by deptno;
第二步:把以上结果当成临时表t,在跟emp连接条件是部门编号和薪水相同
select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.maxsal = e.sal and e.deptno = t.deptno;

哪些人的薪水在部门的平均薪水之上

第一步:先取部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
第二步:取部门员工薪水在平均薪水之上的员工
select e.ename,t.*,e.sal from emp e join (select deptno,avg(sal) avgsal from emp group by deptno) as t on e.deptno = t.deptno and e.sal > t.avgsal;

取得部门中所有人的薪水等级的平均值

select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;

不准用分组函数,取最高薪水,至少两种方案

//第一种降序分页
select e.sal,e.ename from emp e order by e.sal desc limit 1;
//第二种表的自连接(锻炼自关联的意识)
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

取得平均薪水最高的部门的部门编号,至少两种方案

第一种:先取每个部门的平均薪水,在降序取最高
select deptno,avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
第二种:用max
select max(t.avgsal) from (select avg(sal) avgsal,deptno from emp group by deptno) t; 
select deptno,avg(sal) avgsalmax from emp group by deptno having avgsalmax = (select max(t.avgsal) from (select avg(sal) avgsal,deptno from emp group by deptno) t);

取得平均薪水最高的部门的部门名称

select d.dname,avg(e.sal) avgsalmax from emp e join dept d on d.deptno = e.deptno group by d.dname order by avgsalmax desc limit 1;

取平均薪水等级最低的部门的部门名称

第一步,取平均薪水最低的部门
select avg(e.sal) avgsal,d.dname from emp e join dept d on d.deptno = e.deptno group by d.dname order by avgsal asc limit 1;
第二步,取最低平均薪水的等级
select s.grade from salgrade s join (select avg(e.sal) avgsal,d.dname from emp e join dept d on d.deptno = e.deptno group by d.dname order by avgsal asc limit 1) t on t.avgsal between s.losal and s.hisal;
第三步,合并
select t.*,s.grade from (select avg(e.sal) avgsal,d.dname from emp e join dept d on d.deptno = e.deptno group by d.dname order by avgsal asc limit 1) t join salgrade s on t.avgsal between s.losal and s.hisal where s.grade = (select s.grade from salgrade s join (select avg(e.sal) avgsal,d.dname from emp e join dept d on d.deptno = e.deptno group by d.dname order by avgsal asc limit 1) t on t.avgsal between s.losal and s.hisal);

取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

第一步,取哪些人是普通员工
select distinct mgr from emp where mgr is not null;//这是领导
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);//排除领导,取普通员工的最高薪水
第二步,取比普通员工薪水高的领导
select ename,sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
-------------------------------------------
1.找出领导
select distinct ifnull(mgr,0) from emp;
2.找出普通员工的最大薪资
select max(sal) maxsal from emp where 
empno not in(select distinct ifnull(mgr,0) from emp);
3.找出比2大的领导
select ename '领导',sal from emp where sal > (select max(sal) maxsal from emp where 
empno not in(select distinct ifnull(mgr,0) from emp));

取得薪水最高的前五名员工

select ename,sal from emp order by sal desc limit 5;

取得薪水最高的第六到第十名的员工

select sal,ename from emp order by sal desc limit 5,5;

取得最后入职的五名员工

select ename,hiredate from emp order by hiredate desc limit 5;

取得每个薪水等级有多少员工

第一步,取每个员工的薪水等级,接着按等级分组,统计每个等级对应的员工人数
select s.grade,count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;

面试题:有三张表,S(学生表),C(课程表),SC(学生选课表) S(SNO,SNAME) 代表(学号,姓名)

列出所有员工以及领导的名字

select e.ename '员工名',e2.ename '领导名' from emp e left join emp e2 on e2.empno = e.mgr;

列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

select a.empno,a.ename,a.deptno,a.hiredate '员工入职日期',d.dname,b.hiredate '领导入职日期'  from emp a join emp b on a.mgr = b.empno join dept d on a.deptno = d.deptno where b.hiredate > a.hiredate;

列出部门名称和这些部门的员工信息,包括列出没有员工的部门

select d.dname,e.* from emp e right join dept d on e.deptno = d.deptno;//是部门有null,用右外连接

列出至少有五个员工的部门

select e.deptno,count(*) sum from emp e group by e.deptno having sum > 4;

列出薪金比’SMITH’多的员工信息

select sal from emp where ename = 'smith';
select * from emp where sal > (select sal from emp where ename = 'smith');

列出所有’clerk’(做办事员工作)的姓名及其部门名称,部门的人数

第一步,取这个岗位的员工姓名和部门名
select e.ename,d.dname,e.job,d.deptno from emp e join dept d on e.deptno = d.deptno where job = 'clerk';
第二步,给每个部门的员工计数
select deptno,count(*) from emp group by deptno;
第三步做表连接
select t1.*,t2.* from (select e.ename,d.dname,e.job,d.deptno from emp e join dept d on e.deptno = d.deptno where job = 'clerk') t1 join (select deptno,count(*) from emp group by deptno) t2 on t1.deptno = t2.deptno;

列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

select job,count(*) from emp group by job having min(sal) > 1500;

列出在部门"SALES"(销售部)工作的员工的姓名,假设不知道销售部的部门编号

select deptno from dept where dname = 'sales';
select ename from emp where deptno = (select deptno from dept where dname = 'sales');

列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级

select e.ename '员工',d.dname,e2.ename '领导',s.grade from emp e join dept d on e.deptno = d.deptno left join emp e2 on e.mgr = e2.empno join salgrade s on e.sal between losal and hisal where e.sal > (select avg(sal) from emp);

列出与’scott’(员工名)从事相同工作的所有员工及部门名称

select job from emp where ename = 'scott';
select e.job,e.ename,d.dname from emp e join dept d on e.deptno = d.deptno where job = (select job from emp where ename = 'scott') and e.ename != 'scott';

列出薪金等于部门30中员工的薪金的其他部门员工的姓名和薪金

select e.sal from emp e where e.deptno = 30;
select e.ename,e.sal from emp e where e.sal in(select e.sal from emp e where e.deptno = 30) and e.deptno != 30;//查询结果为空

列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金,部门名称

select max(sal) from emp where deptno = 30;
select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno where e.sal > (select max(sal) from emp where deptno = 30);

列出在每个部门工作的员工数量,平均工资和平均服务期限,没有员工的部门,人数是0

select d.deptno,count(*),ifnull(avg(e.sal),0),ifnull(avg(TimeStampDiff(Year,e.hiredate,now())),0) '部门年限' from emp e right join dept d on e.deptno = d.deptno group by d.deptno;

列出所有员工的姓名,部门名,工资

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

列出所有部门的详细信息和人数

select d.*,count(e.ename) '人数' from dept d left join emp e on d.deptno = e.deptno group by d.deptno;

列出各种工作的最低工资及从事此工作的雇员

select min(e.sal),e.job from emp e group by e.job;
select e.ename,t.* from emp e join (select min(e.sal) jobminsal,e.job from emp e group by e.job) t on e.job = t.job and e.sal = t.jobminsal;

列出各个部门的工作岗位是MANAGER的最低薪金

select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;

列出所有员工的年工资,按年薪从低到高排序

select ename,(sal+ifnull(comm,0))*12 yearsal from emp order by yearsal asc;

求出员工的领导的薪水超过3000的员工名称与领导

select e.ename '员工',e2.ename '领导',e.sal '员工薪水',e2.sal '领导薪水' from emp e join emp e2 on e.mgr = e2.empno where  e2.sal > 3000;

求出部门名称中,带’s’字符的部门员工的工资合计,部门人数

select d.dname,sum(e.sal),count(e.ename) from emp e right join dept d on e.deptno = d.deptno where d.dname like '%s%' group by d.dname;

给任职日期超过30年的员工加薪10%

第一种
select ename,((sal*0.1)+sal) '加薪后工资' from emp where TimeStampDiff(YEAR,hiredate,now()) > 30;
第二种
update emp set sal = sal * 1.1 where TimeStampDiff(YEAR,hiredate,now()) > 30;
select * from emp;