使用工具:navicat
基础:
空值转换:
ifnull(a,b):如果a不为空,则使用a本身的值,如果a为空,则使用b的值
limit
限制返回的行数
limit a,b:从第a条数据开始,返回b条数据[a的索引起始点是0]
limit a:从第1条数据开始,返回a条数据
order by 排序
按照字段排序
按照别名排序
按照select后的顺序排序
按照表达式排序
字符串函数:
select upper('zhangsan') :zhangsan大写
select lower('ZHANGSAN') :ZHANGSAN小写
select length('zhangsan'):zhangsan长度
select replace('zhangsan','san','si'):zhangsan中san替换成si
select substring('zhangsan',3,2):zhangsan第三个位置开始取两个
select substring('zhangsan',3):zhangsan第三个位置一直到最后
select lpad('zhansgan',15,'A'):左边用A补齐到15长度
select rpad('zhansgan',15,'A'):右边用A补齐长度到15
select rpad('zhansgan',5,'A'):返回nsgan
select lpad('zhansgan',5,'A'):zhangs
select left('zhangsan',5):zhangsan左边五位:zhangs
select right('zhangsan',5):zhangsan右边五位:ngsan
select concat('my ','name',' ','is','zhangsan'):拼接:mynameiszhangsan
select instr('zhangsan','a'):a在zhangsan第一次出现的位置
数字函数
select floor(x):小于x的最大整数
select ceiling(x):大于x的最小整数
select mod(x,y):x/y的余数
select pow(x,y):x的y次方
select abs(x):x的绝对值
round(x,[y])保留y位小数
truncate(x,[y])
日期函数
curdate():当前日期
curtime():当前时间
now():当前日期和时间
last_day():当前月份的最后一天
date_add(x,interval y day/month/year):x加y 年或月或天
date_format(x,"%Y%m%d"):格式化x日期,年:Y表示四位,y表示两位
控制流函数
if(条件,b,c):如果条件成立,则使用b的值,如果条件不成立,则使用c的值
case ,
when then
else
end
多表查询
等值连接
不等值连接:
between and
外连接
左连接:查询出左表中的所有数据,以及两表等值的数据
select ...from A left outer join B on 等值条件
右连接:查询出右表中的所有数据,以及两表等值的数据
select ...from B right outer join A on 等值条件
自连接
聚合函数/分组函数
max() 最大值
min() 最小值
avg() 平均值
sum() 总和
聚合函数常和分组函数group by用 后面 having加条件,聚合函数在 select后
-- where 和having 都是进行条件筛选的,where是在分组之前进行条件筛选,而having是在分组之后
from
where
group by
having
select
distinct
order by
子查询
>ALL 指大于最大值
<ALL 指小于最小值
>Any 大于最小值 >a or >b or >c
<Any 小于最大值 <a or <b or <c
insert into 新增数据
--第一种方式 insert into 表 (字段1,字段2,....) values (值1,值2...) 字段的数量与类型要与值对应
--备份emp表 create table emp1 as select * from emp
insert into emp1(ename,empno,job) values ('zhangsan',11,'MANAGER')
-- 第二种方式 insert into 表 values(按照表结构依次填写所有字段) 值的数量与类型要与表结构对应
-- 第三种方式 从其他表拷贝数据(1、拷贝所有字段 2、拷贝指定字段) 语法规则同方式一和方式二
-- 1、拷贝所有字段(前提:两张表的字段数量和类型是匹配的) insert into 表1 select * from 表2...
-- 2、拷贝指定字段(前提:指定的字段数量和类型是匹配的) insert into 表1 (字段1,字段2) select 字段1,字段2 from 表2....
-- 备份emp表 或者表结构
create table emp1 as select * from emp
create table emp2 like emp
update 修改
update 表
set
where
delete 删除
delete from emp1
创建表 create table 表名
-- 创建一个stu学生表 包含学生id,学生姓名,年龄,班级
create table stu(sid int(6) not null,
sname varchar(20),
age int,
class varchar(4) default 4
)
对表进行操作:
alter table stu add(height varchar(10)); 增加一个字段
alter table stu alter class set default 5; 设置默认值
alter table stu modify sname varchar(30);更改字段类型
alter table stu change column sname name varchar(30);
alter table stu modify column name char(30);
delete truncate drop区别
-- delete 删除部分或所有表中数据,不删除表结构,支持回滚,再插入数据,序号会接着往下排
-- truncate 一次性清空数据,不删除表结构,不支持回滚
-- drop 删除表数据和表结构,不支持回滚
约束:
非空约束 not null
唯一约束 unique
主键约束 primary key
外键约束 foreign key
默认值 default
检查约束 check 条件
列级约束
-- 创建一个学生表,包含学生id sid 主键,学生姓名 sname 非空约束,班级id 默认值是5班,身份证carid 唯一
create table stu(sid int primary key,
sname varchar(20) not null,
class varchar(10) default '5班',
carid char(18) unique
)
表级约束
-- 创建一个课程表,包含课程id cid 主键,课程名称cname, 学生id sid 外键 关联stu表的sid
create table class(cid int,
cname varchar(20),
sid int,
constraint class_cid_pk primary key(cid),
constraint class_sid_fk foreign key(sid) references stu(sid)
)
-- on delete cascade 级联删除
-- on update cascade 级联更新
create table class(cid int,
cname varchar(20),
sid int,
constraint class_cid_pk primary key(cid),
constraint class_sid_fk foreign key(sid) references stu(sid) on delete cascade on update cascade
)
创建视图
create or replace view tmpjob
as select * from e
-- 删除视图的数据,是否影响基表?
delete from tmpjob where ename = 'BLAKE'
-- 删除视图,是否影响基表?
drop view tmpjob
-- 方法一:创建视图的同时,给字段取别名
create or replace view tmpjob
as select empno e1,ename e2,job j,sal s from emp where job = 'MANAGER'
-- 方法二:创建视图的同时,给字段取别名
create or replace view tmpjob(e11,e22,j,s,c)
as select empno,ename,job,sal,comm from emp where job = 'MANAGER'