SQL数据库语法

目录

1. 常用数据类型

2. 约束

4. 数据库操作

5. 数据表操作

---

1. 常用数据类型

• int                整型
• double         浮点数
• varchar        字符型
• data             年月日
• datetime      年月日 时分秒

2. 约束

• 主键 primary key : 物理上存储的顺序(存在真实排序), 主键都是非空/唯一的
• 非空 not null : 此字段不允许为空
• 唯一 unique  : 此字段不允许重复
• 默认 default : 当不填写此字段时, 会使用默认值
• 外键 foreign key : 对关系数据进行约束, 当为关键字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常
• 虽然外键可以保证数据的有效性, 但在进行数据的增删查改时, 都会降低数据库的性能, 故不推荐使用

4. 数据库操作

--查看数据库
show databases;--创建数据库
create database 数据库名;--删除数据库
drop database 数据库名;--选择数据库
use 数据库名;--查看当前使用的数据库
select database();

5. 数据表操作

查看表:

--查看当前数据库中的所有表
show tables;--查看表结构
desc 表名;

增添数据:

--创建表格示例
create table if not exists `test_01`(`id` int unsigned auto_increment comment '编号',`title` varchar(100) not null comment '标题',`author` varchar(40) not null comment '作者',`cdate` date comment '日期',primary key (`id`)
)engine=InnoDB  default charset=utf8 comment='测试表格01';
-- if not exists 表名 : 表示若该表不存在则创建
-- auto_increment : 表示从上一条数据自增1
-- comment : 注释字段--新增字段    
alter table 表名 add 字段名 字段数据类型及约束;              --系统默认值为null    
--设置的默认值要与数据类型匹配--写入数据
insert into 表名(字段名1,字段名2
)value(内容1, 内容2, ...);--批量写入
insert into 表名(字段名1,字段名2
)value(内容1, 内容2, ...),(内容1, 内容2, ...),...(内容1, 内容2, ...);

删除数据:

--删除表格
drop table 表名;--删除数据
delete from 表名 where 条件;

修改数据:

--修改字段名称
alter table 表名 change 原名 新名 类型及约束;--更新数据
update  表名
set字段1=...,字段1=...,...
where 条件;

查询数据:

--查询数据
select * from 表名;        --查询表里所有数据--限制查询数据条数
select * form 表名
limit 数据条数;--查找字段数据
select字段1,字段2,...
from 表名;--多表查询
select表1.字段x,表1.字段y,...表2.字段z,...
form 表1, 表2;--条件查询
select * 
from 表名1, 表名2, ...
where 条件;select *
from 表名
where 条件1        --选定表格之后,选择数据之前
having 条件2;      --数据全部计算完之后进行筛选--起别名
select本名1 as 别名1,本名2 as 别名2,...
from 表本名 as 表别名
limit 数据量;--按字段数据去重查询
select distinct字段1,字段2,...
from 表名;--模糊查询1
select *
from 表名
where 字段1 like '_est'        --下划线可任意匹配一个字符
where 字段2 like '%e%';        --百分号可任意匹配多个字符, 可以匹配也可以不匹配--模糊查询2
select *
from 表名
where 字段名 in ('test1', 'test2',...);--模糊查询3
select *
from 表名
where 字段名 between x and y;    --最好用于查询数字/日期, 返回x到y之间的模糊匹配结果--关联, 将两张表上下拼接
--两张表关联的字段名可以不相同, 但字段数量和字段类型要相同
--union distinct 关联时去重
select id,name
from table1
union
selectid,name
from table2;--排序
--默认是从小到大排序, 加上desc是从大到小排序
--每个字段后面的参数只代表这个字段的排序法则
select * from 表名
order by 字段1, 字段2 desc       --哪个字段写在前面, 哪个字段优先排
limit 10;--聚合
--选择想要查询的信息
selectcount(0)        --统计数据条数,max(字段名)    --找最大值,min(字段名)    --找最小值,avg(字段名)    --求平均值...
from 表名
where 条件;         --统计符合条件的数据条数--分组
select字段1,字段2,count(0)    --统计每个分组的人数
from 表名
where 条件
group by 字段1, 字段2;    --用group by去重比distinct效率高--分组统计
selectcoalesce(字段1, 'total'),coalesce(字段2, 'total')
from 表名
where 条件
group by 字段1, 字段2
with rollup;--链接查询
--可同时关联两张表或者多张表
--join默认为内连接 inner join
--内连接: 保留两个关联表的交集数据
select别名1.字段1,别名1.字段2,别名1.字段3,别名2.字段1,别名2.字段2...
from 表名1 (别名1)        --主表
join 表名2 (别名2)        --副表on 别名1.关联字段 = 别名2.关联字段and 副表条件...
where 主表条件条件
limit 数据量;--左连接 left join
--保留主表的全部数据和关联表的交集数据
with 别名1 as (select *from 表名1where 条件
)
,别名2 as (select *from 表名2where 条件
)
select 字段...
from 别名1            --主表
left join 别名2       --副表on 别名1.关联字段 = 别名2.关联字段
where 条件;           --where里面的条件对应from后面的表(别名1)的条件--右连接 right join
--通过调换字段顺序可以将右关联的表改为左关联
with 别名1 as (select *from 表名1where 条件
)
,别名2 as (select *from 表名2where 条件
)
select 字段...
from 别名1             --副表
right join 别名2       --主表on 别名1.关联字段 = 别名2.关联字段
where 条件;--外连接 outer join
--将两张表关联, 没有数据的位置由null补充, 只要其中一个表存在数据则返回数据
--通过其他的关联操作实现外关联--自关联
create table if not exists `city` (`id` int not null comment '编号',`name` varchar(100) comment '城市名称',`pid` varchar(4) comment '父id',primary key (`id`)
)engine=InnoDB default charset=utf8 comment='城市表格';insert into city(id,name,pid) values 
(1,'上海市',null),
(12,'闵行区',1),
(13,'浦东新区',1),
(2,'北京市',null),
(23,'朝阳区',2),
(24,'海淀区',2),
(25,'望京区',2),
(3,'广东省',null),
(31,'广州市',3),
(32,'东莞市',3),
(33,'珠海市',3),
(321,'莞城区',32);selecta.ID,a.name,b.ID,b.name,c.ID,c.name
from city a
left join city bon a.ID = b.PID
left join city con b.ID = c.PID
where a.PID is null;--子查询
select * from 表名
where 字段 >= (select avg(字段) from 表名);

总结:

--代码格式
select distinct字段
from 表名
join
where
group by
having 
order by
limit start, count--执行顺序
from 表名
join
where
group by
select distinct 字段
having
order by
limit start, count

内置函数:

--日期 now  
now();   --当前日期 时间
year(now());     --年
month(now());    --月
day(now());      --日select year(now());--字段长度 length
length(对象字段) from 表名;--设置返回值小数位数 round
select round(对象字段, 小数位数) from 表名;--反转字符串 reverse
select reverse(对象字符串);--截取字符串 substring
select substring(对象字符串, start, length);--判空 ifnull / nvl / coalesce
select ifnull(对象, 默认值);      --如果对象为null, 则用默认值替换--条件判断 case when
select
case when 条件1 then ...when 条件2 then ...else ...end 新增字段
from 表名select emp_no,case when emp_no > 10020 then '大'     --最优先when emp_no > 10010 then '中'else '小'end '编号等级'
from dept_emp
where emp_no < 10050
limit 30;--分组(维度)聚合 grouping sets
--开窗函数 partition by
--function(column) over(partiton by 字段1,字段2...) 新增字段
--function通常为聚合函数/排序函数--分类统计不同部门不同性别的人数
with a as (select a.emp_no,a.gender,b.dept_nofrom employees a join dept_emp b on a.emp_no = b.emp_no
)
select distinctdept_no,gender,count(emp_no) over (partition by dept_no, gender) dept_cnt
from a;--排序函数
--row_number()  排序名次累加, 即使数据相同也累加                  1 2 3 4 5 6
--rank()        排序名次可能相同, 遇到数据相同则跳过该名次         1 1 3 4 4 6
--dense_rank()  排序名次可能相同, 遇到数据相同则从上一个名次累加   1 1 2 2 2 3 4
select row_number() over(order by 字段) 新增字段;
select rank() over(order by 字段) 新增字段;
select dense_rank() over(order by 字段) 新增字段;--查询写入 insert into select
--将查询到的数据写入到另一个表格中, 要求字段一一对应