我要评论
文章目录
结构化查询语句sql
sql是结构化查询语言（structure query language），它是关系型数据库的通用语言
sql主要可以划分为3个类别：
-
ddl（data definition language）语句
数据定义语言，这些语句定义了不同的数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。
-
dml（data manipulation language）语句
数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、update 和select 等。
-
dcl（data control language）语句
数据控制语句，用于控制不同的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。
库操作
- 查询数据库
show databases;
- 创建数据库
create database chatdb;
- 删除数据库
drop database chatdb;
- 选择数据库
use chatdb;
表操作
- 查看表
show tables;
- 创建表
create table user (
id bigint unsigned primary key not null auto_increment comment 'id号',
name varchar(50) unique not null comment '姓名',
age tinyint unsigned not null comment '年龄',
sex enum('m', 'w') not null comment '性别，m男性，w女性'
) engine=innodb default charset=utf8;
- 查看表结构
desc user;
- 查看建表sql（可以查看索引、外键等情况）
show create table user\g;
- 删除表
drop table user;
curd 操作
insert 增加
# 添加表数据
insert into user(name, age, sex) values('zhangsan', 18, 'm');
insert into user(name, age, sex) values('lisi', 19, 'w');
insert into user(name, age, sex) values('wangwu', 20, 'm');
insert into user(name, age, sex) values('zhaoliu', 21, 'w');
insert into user(name, age, sex) values('qianqi', 22, 'm');
insert into user(name, age, sex) values('zhangsan', 18, 'm'),('lisi', 19, 'w'),('wangwu', 20, 'm'),('zhaoliu', 21, 'w'),('qianqi', 22, 'm');
扩展：两种添加方式的不同
update 修改
update user set age = 23 where name = 'zhangsan';
update user set age = age + 1 where id = 3;
delete 删除
delete from user where age=23;
delete from user where age between 20 and 22;
delete from user;
select 查询
select * from user;
select id, nickname, name, age, sex from user;
select id, name from user;
select id, nickname, name, age, sex from user where sex = 'm' and age >= 20 and age <= 25;
select id, nickname, name, age, sex from user where sex = 'm' and age between 20 and 25;
select id, nickname, name, age, sex from user where sex = 'w' or age >= 22;
去重 distinct
select distinct name from user;
空值查询
- is [not] null
select * from user where name is null;
union 合并查询
- 一句话：连接两个select查询的sql语句（全查）
select country from websites union all select country from apps order by country;
注意：union 默认去重，不用修饰distinct，all显示所有重复值
带 in 子查询
- [not] in (元素1，元素2，…，元素n)
select * from user where id in (10, 20, 30, 40, 50)
select * from user where id not in (10, 20, 30, 40, 50)
select * from user where id in (select stu_id from grade where average >= 60.0)
分页查询
select id, nickname, name, age, sex from user limit 10;
select id, nickname, name, age, sex from user limit 2000,10;
select * from user limit n offset m;
explain进行分析：查看sql语句的执行计划
- 测试代码（自动生成2000000条数据）【测试分页，测试explain所用】
# 修改sql结束的符号
delimiter $
create procedure add_t_user (in n int)
begin
declare i int;
set i=0;
while i<n do
insert into t_user values(null,concat(i+1,'@fixbug.com'),i+1);
set i=i+1;
end while;
end$
delimiter ;
call add_t_user(2000000);
面试题：分页的效率低吗？为什么低？怎么修改呢？
排序 order by
select id,nickname,name,age,sex from user where sex='m' and age>=20 and age<=25 order by age asc;
select id,nickname,name,age,sex from user where sex='m' and age>=20 and age<=25 order by age desc;
**扩展：**order by 查询特别慢，查看explain进行分析，发现是using filesort，所以给排序列或者查询列添加索引
分组 group by
select sex from user group by sex;
select count(id), sex from user group by sex;
select count(id), age from user group by age having age>20;
**扩展：**group by 查询慢，explain 分析发现是 using temporary，即产生了一个临时表，因此最好给分组列添加索引
笔试实践题
下表bank_bill是某银行代缴话费的主流水表结构：
| 字段名 | 描述 |
|---|---|
| serno | 流水号 |
| date | 交易日期 |
| accno | 账号 |
| name | 姓名 |
| amount | 金额 |
| brno | 缴费网点 |
1、统计表中缴费的总笔数和总金额
2、给出一个sql，按网点和日期统计每个网点每天的营业额，并按照营业额进行倒序排序
create table bank_bill (
serno bigint unsigned primary key not null auto_increment,
date date not null,
accno varchar(100) not null,
name varchar(50) not null,
amount decimal(10, 1) not null,
brno varchar(150) not null
);
insert into bank_bill values
('101000', '2021-3-1', '111', 'zhang', 100, '高新区支行'),
('101001', '2021-3-1', '222', 'liu', 200, '碑林区支行'),
('101002', '2021-3-1', '333', 'gao', 300, '高新区支行'),
('101003', '2021-3-1', '444', 'lian', 150, '雁塔区支行'),
('101004', '2021-3-1', '555', 'lan', 360, '雁塔区支行'),
('101005', '2021-3-1', '666', 'wang', 300, '碑林区支行'),
('101006', '2021-3-1', '777', 'wei', 500, '碑林区支行'),
('101007', '2021-3-2', '444', 'lian', 150, '雁塔区支行'),
('101008', '2021-3-2', '555', 'lan', 360, '雁塔区支行'),
('101009', '2021-3-2', '666', 'wang', 300, '碑林区支行'),
('101010', '2021-3-3', '777', 'wei', 500, '碑林区支行');
连接查询
- 连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。
- sql 先根据 on 生成一张临时表，然后再根据 where 对临时表进行筛选。
内连接查询
select s.uid, s.name, s.age, s.sex, e.score from student s inner join exame e on s.uid = e.uid where e.uid = 1 and e.cid = 2;
注意：on s.uid = e.uid 会区分大表和小表，是按照记录的个数（数据量）区分
外连接查询
左连接查询
# 把left这边的表所有的数据显示出来，在右表中不存在相应数据，则显示null
select a.*, b.* from student a left join exame b on a.uid = b.uid;
右连接查询
# 把right这边的表所有的数据显示出来，在左表中不存在相应数据，则显示null
select a.*, b.* from student a right join exame b on a.uid = b.uid;
发表评论