1 SQL介绍(Structured Query Language,结构化查询语言)
定义: SQL是一门特殊的语言,专门用来操作关系型数据库,当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL语言操作oracle、mysql、sql server、sqlite等等所有的关系型数据库。
分类:DQL:数据查询语言,用于对数据进行查询,如select
DDL:数据定义语言,进行数据库、表的管理,如create,drop
DML:数据操作语言,对数据进行增删改,如insert,update,delete
TPL:事务处理语言,对事物进行处理,如begin transaction,commint,rollback
2 sql语言中的注释
-- 行注释:--空格
/*内容*/:多行注释
3 常用数据类型
整数 int:有符号范围(-2147483648 ,2147483647),无符号int unsigned 范围(0,4294967295)
小整数 tinyint:有符号范围(-128,,127),无符号 tinyint unsigned 范围(0,255)
小数 decimal:如decimal(5,2)表示共有5位数,小数占2位,整数占3位
字符串:varchar 可变长字符串,varchar(10)表示最多存10个字符,一个中文或英文都占一个,char 定长字符串
日期时间 datetime:范围(1000-01-01 00:00:00 ~ 9999-12-31 23:59:59)
4 create创建表
-- 创建表b,字段要求姓名 可变长10 身高 5位数字,其中2位小数
create table b(
bname varchar(10),
height decimal(5,2)
)engine=innodb character set 'utf8';
-- 创建表c
create table c(
id int,
cname varchar(20),
age tinyint unsigned
);
5 insert表中插入数据
添加一条数据:
格式一:insert into 表名 values (...,...,...); 适用于对全部字段都插入
格式二:insert into 表名 (字段名1,字段名2) values (值1,值2); 适用于对部分字段进行插入
添加多条数据:
格式一:insert into 表名 values (...,...,...),(...,...,...),(...,...,...);
格式二:insert into 表名 (字段名1,字段名2) values (值1,值2)(值1,值2),(值1,值2);
-- 插入一条数据
insert into c values (0,'cxk',18);
insert into c (id,cname) values (1,'kk');
-- 插入多条数据
insert into c values (2,'cxk1',25),(3,'cxk2',18);
insert into c (id,cname) values (4,'kk1'),(5,'kk2');
6 select查找数据
查询表的所有字段:select * from 表名;
查询指定字段:select 字段名1,字段名2 from 表名;
7 update修改数据
用法:update 表名 set 字段=值,字段=值 where 条件;
如果没有where条件,则代表修改表中所有的对应数据
8 delete,truncate 删除数据
用法:delete from 表名 where 条件;
如果没有where,则表示删除表中所有数据,等同于 truncate table 表名;
两者区别:速度上,truncate>delete;
使用truncate删除数据后,自增长字段恢复从1开始,使用delete还是从删除前的值开始
9 drop删除表
drop table 表名;
drop table if exists 表名; :如果表a存在,则删除,不存在则什么也不做
10 字段的约束
常用约束:
主键(primary key):值不能重复,auto_increment代表自动增长
非空(not null):此字段的值不允许填写空值
唯一(unique):此字段的值不允许重复
默认值(defalut):当不填写此字段时,会自动使用默认值
11 主键与自增长
带有primary key 的字段,值不能重复
auto_increment为自增长
如果不指定自增长字段的值,想要在前一个数据的基础上+1,则需要使用占位符0或者null,不然的话,由于字段数量不匹配,添加信息会报错
在已有的表中增加主键:1) alter table 表名 add 字段名 数据类型 primary key;
2) alter table 表名 modify 字段名 数据类型 primary key;
主键的删除:alter table 表名 drop primary key;
主键的重要性:如果有两个相同的数据,使用主键时可以方便其修改
12 字段的别名
格式一:select 字段名 as 别名, 字段名 as 别名, 字段名 as 别名 from 表名;
格式二:select 字段名 别名, 字段名 别名, 字段名 别名 from 表名; 字段名和别名中间加个空格就行
13 表的别名
格式一:select 字段名, 字段名, 字段名 from 表名 as 别名;
格式二:select 字段名, 字段名, 字段名 from 表名 别名; 表名和别名中间加个空格就行
14 distinct 过滤重复数据
select distinct 字段名,字段名 from 表名;
15 where的多种运算符
比较运算符、逻辑运算符、模糊查询、范围查询、空判断
16 比较运算符
等于:=
大于:>
小于:<
小于等于:<=
大于等于:>=
不等于:!=
17 逻辑运算符
与 and:由两个条件,两个条件必须同时满足
或 or:两个条件,两个条件满足任意一个即可
非 not:一个条件,条件成立时,not之后就为不成立,条件不成立时,not之后就为成立
18 模糊查询
where 字段名 like ‘ ’;
%表示多个任意字符
_表示一个任意字符
例子:like '孙%':孙开头的数据
like ‘孙_’:孙开头且长度为2的数据
19 范围查找
在查找非连续的范围内时:where 字段名 in (‘条件1’,‘条件2’);
在查找连续的范围时:where 字段名 between a and b;
20 空判断
null 与 ‘’ 不同,null表示字符为空,而‘’表示字符长度为0的字符串
空判断:where 字段名 is null;
非空判断:where 字段名 is not null;
21 order by排序
select * from 表名 order by 字段名1 asc|desc,字段名2 asc|desc;
如果不写asc或者desc默认为从小到大排序,效果与asc一致,desc为降序排序
注:排序要在where子句之后,不然无法提取数据
22 count 函数
计算总数据的数量
select count( [distinct] */字段名) from 表名 where 条件;
23 max 最大值
查找数据中的最大值
select max( [distinct] 字段名) from 表名 where 条件;
注:max后面必须加字段名
24 min 最小值
查找数据中的最小值
select min( [distinct] 字段名) from 表名 where 条件;
25 sum 求和
select sum( [distinct] 字段名) from 表名 where 条件;
26 avg 平均值
select avg( [distinct] 字段名) from 表名 where 条件;
使用avg时,会忽略null值,即null值不能作为分母计算平均值
27 group by 数据分组
select 聚合函数 from 表名 where 条件 group by 字段;
gruop by就是配合聚合函数使用的,where子句可有可无
28 having 分组聚合之后的数据筛选
select * from 表名 group by 字段 having 条件;
having总是出现在group by之后
having与where 的区别:1)where先筛选符合条件的数据,然后聚合统计;having先分组聚合统计数据,在统计结果中筛选需要数据;2)where中不能出现聚合函数,having中可以出现聚合函数
29 limit 显示制定的行数
select * from 表名 where 条件 group by 条件 orderby 条件 limit start,count;
查询时下标从0开始,start=1时,表示从第二条记录开始查询显示
start省略不写时,默认从下标0开始
limit总是在select语句的最后
30 数据分页显示
m表示每页显示多少数据,n表示第n页
select * from 表名 limit (n-1)*m,m;
31 连接查询
连接查询可以通过连接运算符(连接条件)实现多个表查询
内连接:查询的结果只显示两个表中满足连接条件的部分,即两表共有部分
左连接:查询的结果为两表共有部分加上左表特有部分
右连接:查询结果为两表共有部分加上右表特有部分
32 内连接
语法一:select * from 表1 inner join 表2 on 表1.字段=表2.字段;
语法二:select * from 表1,表2 where表1.字段=表2.字段;(隐式内连接)
内连接的关键是找到两张表相关联的字段
与where一起使用:select * from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件;
多表的内连接查询:select * from 表1 inner join 表2 on 表1.字段=表2.字段 inner join 表3 on 表2.字段=表3.字段;
可以把第一次的内连接看成一个整体即:select * from (表1 inner join 表2 on 表1.字段=表2.字段) inner join 表3 on 表2.字段=表3.字段;
与order by一起使用:select * from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件 order by 字段名 asc|desc limit start,end;
33 左连接
select * from 表1 left join 表2 on 表1.字段=表2.字段 where 条件;
34 右连接
select * from 表1 right join 表2 on 表1.字段=表2.字段 where 条件;
35 多表联合查询,同名字段的处理方式
select 表名.字段名 from 表1 inner join 表2 on 表1.字段=表2.字段;
36 自关联
自关联是对同一张表做连接查询,并且保证关联字段名不同
select * from 表 as a1 inner join 表 as a2 on a1.字段名1=a2.字段名2 where 条件;
37 子查询
子查询与主查询的关系:1)子查询是嵌入主查询中的;2)子查询是辅助主查询的,要么充当条件,要么充当数据源;3)子查询是可以独立存在的雨具,是一条完整的select语句
标量子查询:子查询返回结果只有一行一列的时候,称为标量子查询
格式:select * from 表名 where age > (select avg(age) from 表名);
列子查询:子查询返回结果只有多行一列的时候,称为列子查询
格式:select * from 表名 where class in (select class from 表名 where 条件);
表级子查询:子查询返回结果只有多行多列的时候,称为表级子查询
格式:select * from (select * from 表1 where 条件) t1 inner join 表2 on t1.字段=表2.字段;
38 练习
-- 列出男职工总数和女职工总数
select sex,count(*) from employees group by sex;
-- 列出非党员职工的总数
select count(*) from employees where poliyicalsta!='党员';
-- 列出所有职工工号,姓名以及所在部门
select empid,empname,deptname from employees inner join departments on employees.deptid=departments.deptid;
-- 列出所有职工工号,姓名和工资
select e.empid,empname,salary from employees e inner join salary s on e.empid=s.empid;
-- 列出领导岗的姓名以及所在部门名称
select empname,deptname from employees e inner join departments d on e.detpid=d.detpid where leader is NULL;
-- 列出职工总人数大于4的部门号和总人数
select detpid,count(*) from employees group by detpid having count(*)>4;
-- 列出职工总人数大于4的部门号和部门名称
select e.detpid,deptname from employees e inner join departments d on e.deptid=d.detpid group by e.detpid having count(*)>4;
-- 列出开发部和测试部的职工号,姓名
select empid,empname from employees e inner join departments d on e.deptid=d.deptod where deptname in ('测试部','开发部');
-- 列出市场部所有女职工的姓名和政治面貌
select empname,politicalsta from employees e inner join departments d on e.deptid=d.deptid where deptname='市场部' and sex='女';
-- 显示所有职工姓名和工资,包括没有工资的职工姓名
select empname,salary from employees e left join salary s on e.empid=s.empid;
-- 求不姓‘孙’的所有职工工资总和
select sum(salary) from employees e inner join salary s on e.empid=s.empid where not empname like '孙%';
-- 这里采用左连接也可以,不影响
39 MySQL内置函数
1)拼接字符串:concat(参数1,参数2,...):参数可以为数字,也可以是字符串
2)字符长度:length(str):如果字符串编码为utf8,一个汉字长度为3
3)左侧截取字符:left(str,len):返回字符串的左端len个长度的字符,与length规则不同,中文英文字符长度都为1
4)右侧截取字符:right(str,len):返回字符串的右端len个长度的字符,与length规则不同,中文英文字符长度都为1
5)指定位置截取字符串:substring(str,start,len):start下标从1开始,
6)去除左侧空格:ltrim(str):去除字符串左侧的空格
7)去除右侧空格:rtrim(str):去除字符串右侧的空格
8)去除两边空格:trim(str):去除字符串两侧的空格
9)四舍五入:round(float,位数):位数表示保留具体的小数位
10)随机数:rand():生成一个0-1.0的随机浮点数,可以与order by使用,order by rand()表示随机排序
11)当前日期:current_date():返回当前日期
12)当前时间:current_time():返回当前时间
13)当前日期与时间:now():返回当前日期与时间
注:内置函数可以在where,order by子句中使用
40 存储过程
定义:存储过程procedure,也称存储程序,是一条或者多条SQL语句的集合。
创建存储过程:
当使用命令窗口时,由于 ; 表示换行,因此需要设置分割符
-- 例1:创建存储过程stu(),查询students表所有学生信息
create procedure stu()
begin
select * from students;
end
-- 调用存储过程,需要加()
call stu();
-- 删除存储过程,这里不需要加()
drop procedure stu;
drop procedure if exists stu;
41 视图
定义:视图是对查询结果的封装,不能对其中数据进行增删操作
创建视图:create view 视图名称 as select 语句;
删除视图:drop view 视图名称;
drop view if exists 视图名称;
注:可以把视图看成一张表,不过不能对其中数据进行增删操作,可以使用内连接对其与其他表进行操作
42 事务
定义:事务是一个操作序列,这些操作要么都执行,要么都不执行,是一个不可分割的工作单位。
事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性。
开始事务:begin,开始事务后执行修改update或删除delete记录语句,变更会写到缓存中,而不会立刻生效
回滚事务:rollback,放弃修改
提交事务:commit,将修改的数据写入实际的表中
-- 同时删除students和scores表中studentNo为001学生的记录
-- 回滚事务,放弃删除
begin;
delete from students where studentNo='001';
delete from scores where studentNo='001';
rollback;
如果开始一个事物,但是执行中出现异常,最终没有出现rollback或者commit,退出时,自动会执行rollback
43 索引
作用:加速select查询的速度
创建索引:create index 索引名称 on 表名(字段名(长度));
如果创建索引的字段是字符串,需要指定长度,建议长度与创建表时的长度一致。
查看索引:show index from 表名;
删除索引:drop index 索引名称 on 表名;
索引的优点:大大提高了select查询的速度
索引的缺点:降低更新表的速度,如进行insert,update和delete操作时,不仅要保存数据,还要保存所有文件
索引的必要性:1)实际应用中,执行select次数远远大于insert等语句,因此建立索引的必要的。2)在大量数据插入时,可以先删除索引,在批量插入数据,在添加索引,这样可以提高数据插入效率。
44 使用cmd进入mysql
如果mysql.exe没有添加到环境变量中的话,则需要先将路径切换到mysql/bin文件所在路径
指令:mysql -h [主机名] -u [用户名] -p
参数说明:-h [主机名],表示连接mysql地址,如果省略则默认为本地连接
-u [用户名],表示连接用户名
-p,执行之后会提示输入密码
45 cmd下常用的指令
1)查看数据库:show databases;
2)只用制定数据库:use 数据库名;
3)查看数据库中的表:show tables;
4)命令行默认字符与数据库不同,需要修改显示方式:set names gbk;
5)查看表结构:a. desc 表名; b. show create table 表名;
6)创建数据库:create database [ if not exists ] 数据库名 character set utf8; character set utf8表示字符集为utf8
7)删除数据库:drop database 数据库名;
8)修改数据库字符集:alter database 数据库名 character set 'utf8';
46 检查约束
格式一:字段名 enum('','',''),单选
格式二:字段名 set('','',''),多选
47 外键约束
作用:从表中用来和主表建立关联关系的字段
格式:
create table a(
字段1 数据类型,
字段2 数据类型,
foregin key (字段1) references 主表名 (字段1),
);
增加外键:alter table 表名1 add foregin key (字段名) references 表名2 (字段名);
外键关联数据变更操作指令设置:
删除更新操作种类:cascade(级联),no action(不能改),restrict(不能改),set null(设置为空)
任何设置:alter table 表名1 add foregin key (字段名) references 表名2 (字段名) on delete (cascade/no action/restrict/set null) on update (cascade/no action/restrict/set null);
删除外键:alter table 表名 drop foregin key 外键名;
48 修改表中数据类型
alter table 表名 modify 字段名 数据类型;
49 修改表中数据的字段名
alter table 表名 change 旧字段名 新字段名 数据类型;
50. 例题
商品表order,字段:oi表示订单编号,catei表示商品种类,oa表示订单金额,od表示订单日期
顾客表customer,字段:custi表示顾客编号,oi表示订单编号,cg表示顾客性别
现要求:根据上述表格,查询每个商品类别在2024-01-01到2024-06-30间的销售总额以及购买该商品的男性和女性顾客的数量
select
o.catei,
sum(o.oa),
count(distinct case when c.cg='男' then c.custi end),
count(distinct case when c.cg='女' then c.custi end)
from
order o
inner join
customer c on o.oi=c.oi
where
o.od>='2024-01-01' and o.od<='2024-06-30'
group by
o.catei