当前位置：首页 > news >正文

【数据库】SQL语法

news 2025/7/19 15:52:28

1. 常用数据类型

2. 约束

3. 数据库操作

4. 数据表操作

查看表

创建表格

添加数据

删除数据

修改数据

单表查询数据

多表查询数据

模糊查询

关联查询

连接查询

数据查询的执行顺序

4. 内置函数

1. 常用数据类型

整型：int
浮点型：float
字符型：varchar
年月日：data
年月日时分秒：datatime

2. 约束

primary key：主键，物理上的存储顺序，主键一定是非空、唯一的
not null：此字段不允许为空
unique：此字段不允许重复
default：默认，当此字段无数据时，会填入默认值
foreign key：对关系数据进行约束，当为关键字填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

3. 数据库操作

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

4. 数据表操作

查看表

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

创建表格

--创建表格示例
create table if not exists `test_table`(`id` int unsigned auto_increment comment '编号',`title` varchar(100) not null comment '标题', `author` varchar(100) not null comment '作者', `cdate` date comment '日期', primary key (`id`)
)engine=InnoDB default charset=utf8 comment='测试表格';--if not exists `test_table`：如果 test_table 这张表不存在则新建
--auto_increment：从上一条数据自增 1 
--comment：后面的字符串为注释
--not null：如果该字段无数据则默认为 null

添加数据

--新增字段示例
alter table test_table add `position` varchar(100) not null comment '地区';--写入数据
insert into test_table(title,author,position,cdate
)value('孔乙己', '鲁迅', '中国', '1919-4-1');--批量写入
insert into test_table(title,author,position,cdate
)value('药', '鲁迅', '中国', '1919-4-25'),('白夜行', '东野圭吾', '日本', '1998-8-1'),('鲁宾逊漂流记', '笛福', '英国', '1719-4-25');

删除数据

--删除表
drop table table_name;--删除字段
alter table table_name drop column `字段名`;--删除数据
drop from table_name where 条件;

修改数据

--修改字段名示例
alter table test_table change `title` `book` varchar(100) not null comment '作品';--更新数据示例
update test_table
setbook = '彷徨',cdate = '1926-8-1'
where id = 1;

单表查询数据

--查询所有数据
select * from test_table;--限制数量查询
select * from test_table
limit 3;--查找指定字段数据
selectbook,author
from test_table;

多表查询数据

--多表查询示例
--新建另一个测试表格test_01
--test_01的字段是 id title author cdate
--test_01存储影视信息
selecttest_table.book,test_table.author,test_01.title,test_01.author
from test_table, test_01;--条件查询
select *
from table1, table2, ...
where 条件;select *
from table1, table2, ...
where 条件1         --选定表格后，选择数据前继续筛选
having 条件2;       --数据全部计算完之后进行筛选--起别名示例
selecta.id,a.book,a.author
from test_table a;--按字段数据去重查询示例
select distincta.author
from test_table as a;

模糊查询

--模糊查询1示例
select *
from test_table
where author like '%鲁%';   -- % 可匹配任意字符，一个或多个，也可不匹配--模糊查询2示例
select *
from test_table
where cdate between '1900-1-1' and '2000-1-1';--模糊查询3示例
select *
from test_table
where author in ('鲁迅', '陈独秀', '李大钊');

关联查询

--关联：将两张表拼接
--两张表的字段名可以不不相同，但字段数量字段类型要相同
--union distinct
selectid,book,author
from test_table
union
select id,title ,author
from test_01;--排序
--默认是从小排到大，加上desc是从大到小
--每个字段后面的参数只代表这个字段的排序法则
select * 
from test_table
order by cdate desc, id;    --优先排序出版时间逆序，再根据编号正序排序--聚合
--count(0)  统计数据条数
--min  max  avg  分别用于找最大值、最小值、平均数
selectcount(0)        ,min(cdate),max(cdate),avg(cdate)
from test_table;--分组
select book,author,count(0)
from test_table
group by book, author;
--用 group by 去重比 distinct 效率高--分组统计
selectcoalesce(字段1, 'total'),coalesce(字段2, 'total')
from 表名
where 条件
group by 字段1, 字段2
with rollup;

连接查询

连接查询
可同时关联两张表或多张表

内连接
join默认为内连接 inner join
内连接：保留两个关联表的交集

selecta.*,b.*
from test_table as a
join test_01 as bon a.id = b.idand a.author = '鲁迅';

左连接

左连接 left join
保留主表的全部数据和关联表的交集数据

selecta.*,b.*
from test_table as a
left join test_01 as bon a.id = b.id;

右连接
右连接 right join
通过调换字段顺序可以将右连接改为左连接

selecta.*,b.*
from test_01 as a
right join test_table as bon a.id = b.id;

自关联

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 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

4. 内置函数

常用默认函数

--当前日期
now()
--年
year(now())
--月
month(now())
--日
day(now())--字段长度
length(字段) from table_name;--设置返回值最小位数
select round(字段, 小数位数) from table_name;--反转字符串
select reverse(字符串);--截取字符串
select substring(字符串, start, length);--判空  ifnull/nvl/coalesce
--如果对象为空，则用默认值代替
select ifnull(对象, 默认值)

条件判断

--条件判断
select
case when 条件1 then ...when 条件2 then ...else ...end 新增字段
from table_name;--示例
selecttest_table.*,case when author = '鲁迅' then '鲁迅文集'when position = '日本' then '日本文学'else '西方文学'end '文化特色'
from test_table;

开窗函数

--开窗函数  partition by
--function(column) over(partition by 字段1, 字段2...) 新增字段
--function通常为聚合、排序函数--分类统计不同作者、不同国家的人
select *,count(id) over(partition by author, position) num
from test_table;--排序函数
--row_number()  排序名次累加，并列也累加                1 2 3 4 5 6 ...
--rank()        排序名次可并列，遇到并列则跳过该名次     1 2 2 4 4 6 ...
--dense_rank()  排序名次可并列，遇到并列不跳过名次       1 2 2 3 3 4 ...
select row_number() over(order by 字段) 新增字段;
select rank() over(order by 字段) 新增字段;
select dense_rank() over(order by 字段) 新增字段;--查询写入
insert into select ......
--将查询到数据写入另一个表格中，要求写入的数据一一对应