这里写目录标题
SQL
- 每条语句以分号结尾。
- SQL 中不区分⼤⼩写,关键字中认为⼤写和⼩写是⼀样的
- 注释:
hhhh-- 注释内容 单⾏注释(- -后必须加空格)
/* 注释内容 */ 多⾏注释
hh # 注释内容 这是MySQL特有的注释⽅式
– 查看当前数据库的字符集 latin
show variables like ‘%char%’;
– MySQL服务器中存在很多的数据库
– 查看当前服务器中有哪些库
show databases;
– information_schema
– performance_schema: 数据库引擎相关
– sys: 数据库的配置相关
– mysql: 存储数据库中的登录用户等信息
SQL语句分类
DDL 数据定义语言
Data Definition Language (DDL 数据定义语⾔), 如:建库,建表
创建数据库
CREATE DATABASE 数据库名;
CREATE DATABASE IF NOT EXISTS 数据库名;//判断数据库是否已经存在,不存在则创建数据库
CREATE DATABASE 数据库名 CHARACTER SET 字符集;//创建数据库并指定字符集,例如gbk
查看数据库
show databases;//-- 查看所有的数据库
show create database db3;//-- 查看某个数据库的定义信息
修改数据库
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;//修改数据库默认的字符集
alter database db3 character set utf8;//-- 将db3数据库的字符集改成utf8
删除数据库
drop database db2;
使用数据库
SELECT DATABASE();//查看正在使⽤的数据库
USE 数据库名;//使⽤/切换数据库
面试题:
在 MySQL 数据库软件中,有test1、test2、test3三个数据库,登录数据库之后,输⼊语句:
select database test2; 运⾏结果是什么?
这是⼀条错误的语句,如果要选中⼀个数据库,应⽤使⽤:use test2;
创建表
-- 创建student表包含id,name,birthday字段
create table student (
id int comment '序号',
name varchar(20), -- 字符串
birthday date -- ⽣⽇,最后没有逗号
);
常见数据类型
查看表
-- 查看day21数据库中的所有表
use day21;
show tables;
-- 查看student表的结构
desc student;
-- 查看student的创建表SQL语句
show create table student;
快速创建
-- 创建s1表,s1表结构和student表结构相同
create table s1 like student;
desc s1;
删除表
drop table s1;//-- 直接删除表 s1 表
drop table if exists `create`;//-- 判断表是否存在并删除 s1 表
修改表结构
alter table student add remark varchar(20);//-- 为学⽣表添加⼀个新的字段remark,类型为varchar(20)
alter table student modify remark varchar(100);//-- 将student表中的remark字段的改成varchar(100)
alter table student change remark intro varchar(30);//-- 将student表中的remark字段名改成intro,类型varchar(30)
alter table student change remark intro varchar(30);//-- 删除student表中的字段intro
rename table student to student2;//-- 将学⽣表student改名成student2
alter table student2 character set gbk;//-- 将student2表的编码修改成gbk
DML 数据操纵语言
Data Manipulation Language(DML 数据操纵语⾔),如:对表中的记录操作增删改
增
INSERT INTO 表名 VALUES (值 1, 值 2, 值 3...);//插⼊全部字段,不写字段名,顺序必须一致
insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');//向学⽣表中,插⼊部分列
insert into user values(3, '张三', 18),//支持批量插入数据
(4, '张三', 19),
(5, '张三', 18),
(6, '张三', 19),
(7, '张三', 18);
注意事项:
- 插⼊的数据应与字段的数据类型相同
- 数据的⼤⼩应在列的规定范围内,例如:不能将⼀个⻓度为 80 的字符串加⼊到⻓度为 40的列中。
- 在 values 中列出的数据位置必须与被加⼊的列的排列位置相对应。在 mysql 中可以使⽤value,但不建议使⽤,功能与 values 相同。
- 字符和日期型数据应包含在单引号中。MySQL 中也可以使⽤双引号做为分隔符。
- 不指定列或使⽤ null,表示插⼊空值。
删
delete from user; -- 删除所有-挨个记录删除
truncate table user;-- 删除表中所有记录 - DDL - 删除表,然后重新创建
delete from user where id = 1 or id is null;-- 条件删除
改
SET SQL_SAFE_UPDATES = 0;//修改数据库安全模式,然后再进行update和delete操作
update user set name = '李四';//更新表中所有的记录
update user set name = '李四' where name = '张三';//有条件的更新 where
DQL 数据查询语言
Data Query Language(DQL 数据查询语⾔),如:对表中的查询操作
where XX is null
查询
select * from emp;-- 1.查询所有记录 * 会自动解析为所有字段名,解析效率较低
select -- 3.将查询出来的字段 起个别名 select 字段名 as 别名
-- as可以省略
empno as '编号',ename '姓名',sal,job,hiredate '入职时间'from emp;
select ename,sal from emp where sal BETWEEN 1000 and 2000;-- 4.3查询月薪大于1000,并且小于2000的人
-- 4.4查询在20或者30部门的人
select ename, deptno from emp where deptno = 20 or deptno = 30;
select ename, deptno from emp where deptno in (20,30);
select ename, deptno from emp where deptno not in (20,30);
select ename, comm from emp where comm is null;-- 4.5查询所有没有奖金的人
-- 4.7查询名字是以S开头的人
-- %可以0~n个 _ 一个占位符,一个字符
select ename from emp where ename like "s%";
-- 4.8查询名字有S的人
select ename from emp where ename like "%s%";
-- 4.9查询名字中第二个字母是A的人
select ename from emp where ename like "_A%";
-- 4.10查询员工的月薪*12+奖金 是多少
select ename, sal*12 '年薪' from emp;
-- comm 是 null 所以不能直接计算
select ename, sal*12 + ifnull(comm, 0) '年薪' from emp;
-- ftp:temp -> tools -> mysql
distinct去重复
-- 在emp表中查询都有哪些部门号 -- 去除重复
select distinct deptno from emp; -- 只能查出3条数据
select distinct (deptno, ename) from emp;
排序
-- 查询员工姓名,以及月薪,并且按照月薪由大到小排序
-- 升序-默认 asc,倒叙-desc
select ename, sal from emp order by sal desc
普通函数
/*
普通函数 - 方言
字符串相关,数学相关,时间相关
temp-resources-api
*/
select ceil(1.34);
select month(now());
select concat('ha','he');
select concat(ename,',',job) out_put from dept;
select ifnull("hello", 20);
聚合函数
出来的是单行单列的结果
使⽤聚合函数查询是纵向查询,它是对⼀列的值进⾏计算,然后返回⼀个结果值。聚合函数会忽略空值 NULL。
/*
聚合函数、分组函数
count(): 求总数
max(): 求最大值
min(): 求最小值
avg(): 求平均值
sum(): 求和
会自动将null值排除在外
注意: select 后面接的内容:组函数,出现在group by中的字段
*/
# IFNULL(列名,默认值) 如果列名不为空,返回这列的值。如果为 NULL,则返回默认值。
-- 查询 id 字段,如果为 null,则使⽤ 0 代替
select IFNULL(id, 0) from student;
-- 查询emp表中一共有多少个员工
select count(*) from emp;
-- 查询员工中的最高薪资
select max(sal) from emp;
-- 求所有员工的月薪总和
select sum(sal) from emp;
-- 查询员⼯最⾼⼯资和最低⼯资的差距,列名为 DIFFERENCE
select (max(sal)-min(sal)) DIFFERENCE from emp;
-- 求平均薪资多少
select round(avg(sal)) from emp;//round为四舍五入
-- 求平均的奖金
select avg(comm) from emp;
-- 列出职员表中所有薪⽔⾼于平均薪⽔值的员⼯信息
select ename, job, sal from emp where sal > (select avg(sal) from emp);
分组
GROUP BY 将分组字段结果中相同内容作为⼀组,并且返回每组的第⼀条数据
先分组,然后对分组查询的结果再进⾏过滤
组函数只能用having, 是对分组查询的结果再进⾏过滤
having xx,那么xx一定能在select后面找到,而且能使用having则一定已经存在分组
where是一开始就执行的,肯定在group by前面,where不能使用组函数
-- 先分组,然后对分组查询的结果再进⾏过滤
-- 查询除了10部门以外其他部门的平均薪资
select avg(sal), deptno from emp where deptno<>10 group by deptno;
select avg(sal), deptno from emp group by deptno having deptno <> 10; -- 不推荐使用,效率很低
-- 查询每个部⻔中各个职位的最⾼薪⽔。
select max(sal),job from emp group by job;
-- 查询各个管理者属下员⼯的最低⼯资,其中最低⼯资不能低于 2000,没有管理者的员⼯不计算在内
select min(sal),mgr from emp where sal>2000 group by mgr having mgr is not null;
-- 查询年龄⼤于25岁的⼈,按性别分组,统计每组的⼈数,并只显示性别⼈数⼤于2的数据
SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex having COUNT(*) > 2;
case…when…then
-- 写法一
select ename,job,(case job
when'PRESIDENT'
then 'A'
when'MANAGER'
then 'B'
when'ANALYST'
then 'C'
when'SALESMAN'
then 'D'
else 'E' end
)grade from emp group by job having job='CLERK'or job='SALESMAN';
-- 写法二
select ename,job,(case job
when'PRESIDENT'
then 'A'
when'MANAGER'
then 'B'
when'ANALYST'
then 'C'
when'SALESMAN'
then 'D'
else 'E' end
)grade from emp where job='CLERK'or job='SALESMAN' group by job;
补充,在Oracle中
-- oracle 中的 decode(field,if1,then1,if2,then2...,else) - if-else if...
select ename, job, decode(job,'PRESIDENT','A','MANAGER','B','ANALYST','C','SALESMAN','D','E') from emp;
limit分页查询
截取数据:
以下的两种方式均表示取2,3,4三条条数据。
1.select* from test LIMIT 1,3;
当limit后面跟两个参数的时候,第一个数表示要跳过的数量,后一位表示要取的数量。
2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)
当 limit和offset组合使用的时候,limit后面只能有一个参数,表示要取的的数量,offset表示要跳过的数量 。
分页查询 limit startRow起始行数, rowCount返回行数;
startRow 从0开始,通过 page 和 rowCount 计算
startRow = (page - 1) * rowCount
select * from emp;
select * from emp limit 0, 3; -- 第一页
select * from emp limit 3, 3; -- 第二页
select * from emp limit 6, 3; -- 第三页
select * from emp limit 9, 3; -- 第四页
select * from emp limit 12, 3; -- 第五页,最后一页条数不够,会全部显示
-- 排好序后分页
select * from emp order by sal limit 0,3;
分页查询employees表,每5行一页,返回第2页的数据
select * from employees limit 5,5
总结:单表查询完整sql的结构
select
字段、函数
from
表
where
条件
group by
分组字段
having
组函数条件
order by
排序方式
limit
startRow, pageSize 分页
DCL 数据控制语言
Data Control Language(DCL 数据控制语⾔),如:对⽤户权限的设置
规定你能不能做DDL、DML、DQL
四种语句各自的职能
特殊补充语句
substr
substr(string,start,length)
string - 指定的要截取的字符串。start - 必需,规定在字符串的何处开始。正数 - 在字符串的指定位置开始,负数 - 在从字符串结尾的指定位置开始,0 - 在字符串中的第一个字符处开始。length - 可选,指定要截取的字符串长度,缺省时返回字符表达式的值结束前的全部字符。
例如:select substr(‘abcdefg’,3,4) from dual; 结果是cdef select substr(‘abcdefg’,-3,4) from dual; 结果efg
注意:字符串中的第一个位置始终为1。以下两个sql查询的结果相同:
例如:select substr(‘abcdefg’,0,3) from dual; 结果是abc
select substr(‘abcdefg’,1,3) from dual; 结果是abc
与java类中的substring区别:
substring(int beginIndex, int endIndex) :从指定的 beginIndex 处开始,到指定的 endIndex-1处结束,字符串中的第一个位置始终为0。endIndex,可选,缺省时返回始于指定索引处的字符,一直到此字符串末尾。
例如:“unhappy”.substring(2) returns “happy”
“hamburger”.substring(3,8) returns “burge”
round
到这一位,然后判断这一位需要往前进吗,右边从0开始
1.带有两个参数.每二个参数是小数点的左边第几位或右边第几位,分别用正负表示.左边为负,右边为正.为四舍五入.
select round(748.585929,-1) 750.000000
select round(748.585929,2) 748.590000
2.带三个参数.第二个参数同上.第三个参数有两种可能,一种是取0为四舍五入.另一种是正负值,则为舍去
select round(748.585929,3,0) 748.586000
select round(748.585929,3,1) 748.585000
select round(748.585929,3,-1) 748.585000
trunc
TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。
下面是该函数的使用情况:
TRUNC(89.985,2)=89.98
TRUNC(89.985)=89
TRUNC(89.985,-1)=80
注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。
BigDecimal类型
set/getBigDecimal()
使用BigDecimal类型
•如果其长度为10到18,则Java类型解析器将java.lang.Long来代替了。
•如果长度为5到9,然后Java类型解析器将替换一个Java.lang.integer。
•如果其长度小于5,则Java类型解析器将java.lang.Short替代。
ceil和floor
ceil(n):返回大于等于n的最小整数值