MySQL（基础语法）

Select 列名 别称，列名 别称 from 表名
Select * from 表名 where 条件 Select * from 表名 where job = ‘CLERK’
Select * from 表名 where job = ‘clerk’
不区分大小写
Select * from 表名 where binary job = ‘clerk’ 区分大小写
Select * from 表名 where 列名 is not null
Select * from 表名 where 条件1 and 条件2
Select * from 表名 where 条件1 && 条件2
Select * from 表名 where 条件1 or 条件2
Select * from 表名 where 条件1 || 条件2
Select distinct 列名 from 表名 列中的数据不重复
Select * from 表名 order by 列名 asc
Select * from 表名 order by 列名 desc 升序排序(默认升序)
降序排序
Select * from 表名 where 条件 order by 列名 [asc/desc]
= 与where一起使用时，是关系运算符
= 与 set 一起使用时，是赋值运算符

MySQL中的函数
数值型函数
Select 3+5
Select ‘3’+5 --自动类型转换 字符转数字
Select 3-5
Select 3*5
Select 3/5 --非整除
Select 3 div 5 – 整除
Select 5%4 --余数
Select mod(5,4) --mod叫函数
Select abs(2),abs(-2),abs(0) --绝对值
Select ceiling(1.0001),ceil(-9.9999)
Select foor(1.999),floor(-9.0001)
Select round(1.4),round(1.5) – 四舍五入
Select PI()
Select pow(2,3),power(2,3) — 2的3次方
Select rand() – [0,1]
Select sqrt(4) --开平方

字符型函数
Select ascii(‘abc’) 字符串最左侧字符的ascii值
Select bin(5) 5的二进制数
Select char_length(列名) from 表名
Select char_length(‘helloworld’) 表中的列，列中值得长度
字符串的长度
Select concat(‘hello’,’world’,’java’) 字符串连接
Select insert(‘helloworld’,6,4,’javaSE’) 字符串，第几个，换几个，换成什么
Select left(‘helloworld’,5)
Select right(‘helloworld’,5)
Select upper(‘helloworld’)
Select lower(‘HELLO’)
Select ltrim(‘ hello world ’)
Select rtrim(‘ hello world ’)
Select length(rtrim(‘ hello world ’))
Select substring(‘helloworld’,6,3)
Select reverse(‘helloworld’)

日期型函数
Select curdate() – 年月日 2019-06-27
Select curdate() – 20190627
Select curtime() --时分秒
Select now() --年月日，时分秒
Select sysdate()
Select adddate(now(),interval 2 day)
–返回的是在指定的day,year,month加或减
Select adddate(now(),interval -2 day)
Select adddate(now(),interval -2 year)
Select adddate(now(),interval -2 month)
Select date(now()) --年月日
Select time(now()) --时分秒
Select datediff(curdate(),’2008-08-08’)
–两个星期之间的天数差
Select dayofweek(now()) --一周的第几天
Select dayofyear(now()) --一年的第几天
Select dayofmonth(now()) --一月的第几天
Select dayname(now()) --星期几

流程控制函数
双分支结构
Select 列名，sal+ifnull(comm,0) from 表名 – if comm is null sal+0,否则sal+comm
Select 列名，if(sal>=,’high’,’low’) as grande from emp – if sal>=3000 high,否则low
Select 列名,
Case when sal>=3000 then ‘high’
Else ‘low’
End
As grade
From 表名
多分支结构
Select 列名,
Case when sal>=3000 then ‘high’
Else case when sal>=2500 then ‘mid’
Else ‘low’
End
End
As grade
From 表名

多行函数
– 查询20号部门的员工的薪水
select * from emp where deptno = 20;
– 查询20号部门的员工的总薪水
select deptno,sum(sal) from emp where deptno =20
– 20号部门平均薪水（取整）
select deptno,round(avg(sal)) from emp where deptno =20
– 英文字母的升序 字符型
select max(ename),min(ename) from emp;
– 查询入职时间最晚的和入职时间最早的时间
select min(hiredate),max(hiredate) from emp

– 想计算入职时间最晚的与入职之间最早的时间差是多少年
select datediff(max(hiredate),min(hiredate))/365 from emp;
– 查询个数
select count(*) from emp where deptno = 20

Group by 分组
– 查询每个部分的总人数
select deptno,count(*) from emp where deptno is not null group by deptno
– 查询每个部门的平均薪水
select deptno,avg(sal) from emp where deptno is not null group by deptno
– 查询每种职位的平均薪水
select job,deptno,avg(sal) from emp group by job order by avg(sal) desc

– select后只能写分组项和分组函数
– 查询每个部门的总人数不低于5人的部门
select deptno,count()from emp group by deptno
select deptno,count() from emp where deptno is not null group by deptno having count(*)>5
– having 是分组之后的条件判断

select deptno,count(*) from emp group by deptno having deptno is not null;
/**where 与 having 都用于条件判断(筛选条件)
where 应用在group by之前
having 应用在group by 之后
非分组函数，只是普通的条件判断，使用where与having结果相同，但是建议使用where
where之后只有是普通的条件判断
having之后可以是普通的条件判断，也可以是分组函数的条件判断
*/

– 查询每个部门的总人数
select count(*) 人数 ,deptno 部门 from emp group by deptno

两表查询的92标准
语法:select … from 表名1，表名2 where 表名1.列=表名2.列
Eg：select * from emp e,dept d where e.deptno = d.deptno;
–查询部门名称为sales的员工的编号，姓名，职位
select empno,ename,job,dname from emp e,dept d where e.deptno = d.deptno and dname =‘sales’
–查询每个部门的员工的平均薪水，要求有danme，并且平均薪水大于2000
select dname,avg(sal) from emp e,dept d where e.deptno = d.deptno group by dname having avg(sal)>2000 order by avg(sal)asc

多表查询，要求N张表做连接查询,连接条件为n-1
select * from emp;
select * from dept
–多表连查询 ，要求，N张表做连接查询，连接条件为n-1
– 表student
create table student(
stuno int(4) primary key auto_increment,
stuname varchar(20) not null,
clazzid int(4)
);
– 表clazz
create table clazz(
clazzno int(4) primary key auto_increment,
clazzname varchar(20),
place int (4)
);
– 表school
create table school(
schoolid int(4) primary key auto_increment,
schoolname varchar(20)
);
–外键关系 clazz 的place 与 school schoolid （外键）
– student的clazzid与clazzno(外键)
alter table clazz add constraint fk_place foreign key (place) references school (schoolid)
alter table student add constraint fk_clazzid foreign key (clazzid) references clazz (clazzno)

– 添加数据
insert into school (schoolname) values (‘京南校区’);
insert into school (schoolname) values (‘西三旗校区’);
insert into school (schoolname) values (‘东三旗校区’);
insert into school (schoolname) values (‘上海校区’);
insert into school (schoolname) values (‘琛圳校区’);
select * from school;
insert into clazz (clazzname,place) values (‘零基础java’,1);
insert into clazz (clazzname,place) values (‘java大数据就业’,2);
insert into clazz (clazzname,place) values (‘H5前端’,1);
insert into clazz (clazzname,place) values (‘人工智能’,2);
insert into clazz (clazzname,place) values (‘python’,1);
select * from clazz;
insert into student (stuname,clazzid) values (‘张三’,1);
insert into student (stuname,clazzid) values (‘李四’,2);
insert into student (stuname,clazzid) values (‘王五’,1);
insert into student (stuname,clazzid) values (‘陈六’,1);
insert into student (stuname,clazzid) values (‘麻小七’,3);
select * from student;

–查询学生的姓名，所学课程名称及所在的校区名称
select stuname,clazzname,schoolname from student s ,clazz c,school sch where s.clazzid = c.clazzno and c.place = sch.schoolid

99标准的多表连接查询（内连接查询）两表的地位是相等的，查询的是两个表中“等值”列
语法：select…from 表名1[inner] join 表名2 on 连接条件 [where]
select empno,ename,job,sal,dname from emp e,dept d where e.deptno = d.deptno and d.name =‘sales’
– e.deptno=d.deptno 称为连接条件
– dname=‘sales’ 限定条件(筛选条件) 99年的时候 XXX 这样不好，将连条件与限定条件放在一起了，不容易区分
–所以99标准中，将连接条件与限定条件进行了区分，连接条件使用on，限定条件使用where
select empno ,ename,job,sal,dname from emp e
inner join dept d on e.deptno = d.deptno where dname = ‘sales’

– 三表连接
select stuname,clazzname,schoolname from student s
inner join clazz c on s.clazzid = c.clazzno
inner join school sc on c.place = sc.schoolid
where clazzname like ‘%java%’

外连接查新:查询中的两表有主次关系，可以查询一个表的所有数据
–外连接
–查询的是所有的员工的信息，包含没有部门的员工
select empno,ename,job,sal,dname from emp e left join dept d on e.deptno = d.deptno

select empno,ename,job,sal,dname from dept d right join emp e on e.deptno = d.deptno

–查询所有部门的信息，包含没有员工的部门
select * from emp e right join dept d on e.deptno = d.deptno
select * from dept d left join emp e on e.deptno = d.deptno

–查询员工表的所有的信息和部门表的所有信息，包含没有部门的员工和没有员工的部门(MySQL不支持，Oracle支持)
select * from emp e full outer join dept d on e.deptno = d.deptno

自连接查询：一张表当两张表来使用
–查询入职时间早于领导 的员工信息 92
select * from emp e ,emp m where e.mgr=m.empno and e.hiredate<m.hiredate
–99
select * from emp e inner join emp m on e.mgr = m.empno where e.hiredate<m.hiredate

数据库常用对象
1.索引
–创建索引
create index ind_ename on emp (ename);
–索引查询时自动起作用
select * from emp where ename = ‘SCOTT’

alter table emp drop index ind_ename – mysql支持，删除索引

Drop index ind_ename; – oracle 支持，mysql不支持
2.事务
–mysql需要使用start transaction 开启事务，需要使用commit提交事务，使用rollback回滚事务
create table account(
id int(4) primary key auto_increment,
accname varchar(20),
balance int(4)
);
– 添加测试数据
insert into account(accname,balance) values (‘zhangsan’,500);
insert into account(accname,balance) values (‘lisi’,200);
select * from account;

– 手动开启事务
start transaction ;
update account set balance = balance-200 where accname=‘zhangsan’;
update account set balance = balance+200 where accname = 'lisi ';

rollback; – 手动回滚
commit – 手动提交
– 如果没有start transaction ；mysql语句自动提交

Oracle中的事务是通过insert,update,delete开启事务,使用commit提交事务或DDL可以以自动提交事务,使用rollback回滚事务
Create table account(
Id number (4) primary key,
Accname varchar2(20),
Balance number(7,2)
);
– insert 是DML数据操作语言,自动开启事务
Insert into account values(1,’zhangsan’,500);
Insert into account values(2,’lisi’,200);

Create user bbt identified by bbt; – DDL数据定于语言,遇到DDL自动提交事务
Rollback;–手动回滚
Commit;–手动提交

视图
定义：
视图是若干基本表和其他视图构造出来的表
在创建一个视图是，只是存放的视图的定义，也即是动态检索数据的查询语句，而并不存放视图对应的数据在用户使用视图时才去求相对应的数据。所以视图被称作“虚表”
作用：
可以限制对数据的访问，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。可以使复杂的查询变的简单。在编写查询后，可以方便地重用它而不必知道他的基本查询细节。提供了对相同数据的不同显示。

分页查询和统计查询都写成了视图，作用，将复杂的SQL语句进行简化
Select * from emp order by sal desc;
– 分页查询
Select rownum as rid ,t.* from (select * from emp order by sal desc)t;

Create view vi_empsal
As
Select * from (select rownum as rid , t.* from(select * from emp order by sal desc)t) temp
Where rid>4 and rid<=8;

Select * from vi_empsal;

–统计查询
Create view vi_emp2
As
Select dname,numcount,maxsal,avgsal from dept d,
(select deptno,count(*) as numcount,max(sal) as maxsal ,avg(sal) as avgsal from emp)
Where deptno is not null group by deptno)t
Where d.deptno = t.deptno;
Select * from vi_emp2;

Mysql:中的视图
–视图，视图是一张虚表，在数据库中根本不存在，操作视图实际上就是在操作基本表
– 创建视图
create view vi_emp
AS
select * from emp;

– 查
select * from vi_emp;

– 增删改（对视图就是对视图所对应的基本表的操作）
update vi_emp set sal = 900 where ename = ‘SMITH’;-- 修改了视图中smith的薪水

select * from emp;

– 视图最主要的工作就是查询
create view vi_emp2
AS
select empno,ename,dname from emp e,dept d where e.deptno = d.deptno;

select * from vi_emp2;
删除视图
Drop view 视图名称

(4)储存过程(防止SQL注入)
格式：
声明
Delimiter//
Create procedure 自己起的名字(out/in 形参 数据类型)
Begin
SQL语句
End//
Delimiter;
使用：
Call 调用的储存的名字(参数)
delimiter//
create procedure mypro(out s int)
begin
select count(*) into s from emp;
end//
delimiter;

– call 存储过程的名称 调用存储过程
set @ s =0;
call mypro(@s)
select @s

– 删除的存储过程
delimiter//
create procedure deletebyno(in nid int)
begin
delete from emp where empno = nid;
end//
delimiter;
– 使用存储的过程
call deletebyno(7369);
select * from emp

– 新增的存储过程
delimiter//
create procedure insertemp(in deptno1 int , in dname1 varchar(20),in loc1 varcher(20))
begin
insert into dept values(deptno1,dname1,loc1);
end//
delimiter;

call insertemp(50,‘财务部’,‘北京’);
select * from dept;

Oracle中的存储过程
Version:1.0 StartHTML:0000000105 EndHTML:0000007936 StartFragment:0000000127 EndFragment:0000007918
· --根据员工编号查询
· SELECT * FROM EMP WHERE EMPNO=7902
· ----------------增加的存储过程-------------------------------
· CREATE OR REPLACE PROCEDURE up_save(
· EMPNO IN NUMBER,ENAME VARCHAR2,JOB VARCHAR2,MGR IN NUMBER,
· HIREDATE DATE,SAL NUMBER,COMM NUMBER,DEPTNO NUMBER
· )
· AS
· BEGIN
· INSERT INTO EMP VALUES(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
· END;
· · EXECUTE up_save(1111,‘xhna’,‘CLERK’,7902,SYSDATE,9000,890,20);
· ---------------------修改的存储过程-----------------------------------
· CREATE OR REPLACE PROCEDURE up_update(
· mempno IN NUMBER,mname VARCHAR2,mjob VARCHAR2,mmgr IN NUMBER,
· mhiredate DATE,msal NUMBER,mcomm NUMBER,mdeptno NUMBER
· )
· AS
· BEGIN
· UPDATE EMP SET ENAME=mname,JOB=mjob,MGR=mmgr,HIREDATE=mhiredate,SAL=msal,COMM=mcomm,
· DEPTNO=mdeptno WHERE EMPNO=mempno;
· END;
· EXECUTE up_update(1111,‘xhna’,‘CLERK’,7902,SYSDATE,1000,890,20);
· --------------------删除的存储过程--------------------------
· CREATE OR REPLACE PROCEDURE up_delete(eno IN NUMBER)
· AS
· BEGIN
· DELETE FROM EMP WHERE EMPNO=eno;
· END;
· --执行
· EXECUTE up_delete(1113);
· · · · · · --------------------根据部门编号删除员工的存储过程-------------------------------
· · CREATE OR REPLACE PROCEDURE up_deleteEmpByDeptno(tno IN NUMBER)
· AS
· BEGIN
· DELETE FROM EMP WHERE DEPTNO=tno;
· END;
· --------------------根据部门编号删除一个部门-------------------------------------
· CREATE OR REPLACE PROCEDURE up_deleteDeptno(tno IN NUMBER)
· AS
· BEGIN
· DELETE FROM DEPT WHERE DEPTNO=tno;
· END;
· ------------------------增加部门的存储过程---------------------------------------------------------
· · CREATE OR REPLACE PROCEDURE up_saveDept(tno IN NUMBER,tname VARCHAR2,tloc VARCHAR2)
· AS
· BEGIN
· INSERT INTO DEPT VALUES(tno,tname,tloc);
· END;
· -------------------------修改的存储过程--------------------------------------------------------
· CREATE OR REPLACE PROCEDURE up_updateDept(tno IN NUMBER,tname VARCHAR2,tloc VARCHAR2)
· AS
· BEGIN
· UPDATE DEPT SET DNAME=tname,LOC=tloc WHERE DEPTNO=tno;
· END;
· · ·
· · ---------------查看当前数据库实例名------------------
· SELECT name FROM v$database;