实训笔记6.27
6.27
一、座右铭
我的故事你说,我的文字我落,我值几两你定,我去何方我挑。
二、SQL语言
2.1 DDL:库和表的管理语言
DDL语言执行完成最多返回一个ok,执行失败直接报错
2.1.1 库的管理操作
- 数据库的创建
- 数据库的查询
- 数据库的修改
- 数据库的删除
2.1.2 表的管理操作
- 数据表的创建
create table if not exists table_name( 字段名 字段类型 字段约束... comment "注释", 字段名 字段类型 字段约束... comment "注释", ........ 表级约束 )engine="innodb",charset="utf8";
-
数据表的查询
- 查询某个数据库下的所有数据表:
show tables;
- 查询表结构:
desc table_name;
- 查询数据表的创建细节:
show create table table_name;
- 查询某个数据库下的所有数据表:
-
数据表的修改
alter table table_name rename/change/modify/add/drop ......
- 数据表的删除
drop table if exists table_name;
2.1.3 MySQL中数据类型
整数型、小数型、字符类型、时间日期类型、特殊的类型enum、set
2.1.4 MySQL中六大约束
约束 |
---|
主键约束 |
外键约束 |
唯一约束 |
非空约束 |
默认约束 |
检查约束 |
2.1.5 MySQL数据表设计三范式
原子性
唯一性
消除冗余-消除传递依赖
2.2 DML:管理表数据的语言
DML主要负责表数据的增加、删除和修改,DML语言执行完成会给我返回一个整数类型的结果,这个结果代表的是数据表受影响的行数
2.2.1 增加数据语法
insert into table_name(字段列表) values(值列表),(),().....
insert into table(字段列表) select查询语句
2.2.2 更改数据语法
update table_name set 字段名=字段值,字段名=字段值 where 筛选条件
2.2.3 删除数据语法
delete from table_name where 筛选条件
truncate table_name 截断表
——不能用在有外键的表中
截断表,增加数据时,自增列会从头开始重新自增,delete from table_name会从上一次删除的位置继续自增
2.3 DQL:查询表数据的语言
2.3.1 单表查询
select 查询列表 [from table_name] [where 筛选条件] [group by 分组字段] [having 分组后的筛选条件] [order by 排序字段 asc|desc] [limit 数字,数字]
-
select子语句中的查询列表:常量、函数、表字段、表达式
-
from table_name 从指定的数据表中查询数据,from子语句一旦出现,select子语句中就可以有表字段 如果要查询表中的所有字段 可以在select 后加上一个*号,*号慎用
-
where子语句
-
一般是和from子语句结合使用,代表从指定的数据表中筛选指定的行数据
-
筛选条件:需要是一个返回值为boolean类型的表达式
-
条件表达式
<> <= >= = !=
字段名 条件表达式符号 值
-
逻辑表达式
and or in not in is null is not null between a and b
-
模糊查询
- 筛选数据时,我们不清楚数据的完整的内容,只知道数据中部分内容,使用到模糊查询来筛选指定的数据
- 字段名 like “模糊条件”
- 两个特殊的字符: %0个或者多个字符 _一个字符
- 自定义转义字符 escape
-
-
-
group by分组查询
- 就表中的每一行的数据以指定的字段划分为不同的数据组,当指定的字段值一致时候,划分为同一个组中
- 一旦在MySQL中使用了分组查询,那么select的查询列表就不能随便写,只能写分组字段、常量、聚合函数
-
having分组后的筛选
- 筛选结果数据,用来表示哪些数据需要展示,哪些数据不需要展示
- 分组前的筛选having都能做,只不过having使用场景更加倾向于只有分组后才能筛选的情况
-
order by排序
order by 排序字段 asc|desc,排序字段 asc|desc
- limit 分页查询
limit n
limt offset,n
——offset=(页码-1)*n
【注意】
1、select查询语句查询回来的是一个虚拟二维表格,二维表格也是有表头的,表头我们是可以起别名的
2、单表查询的SQL语句的编写顺序
select---where--group by--having--order by --limit
单表查询的SQL语句的执行顺序
from---where---group by--having--select---order by ---limit
2.3.2 函数
-
查看MySQL自带的所有函数
show function status;
-
UDF:一对一函数,输入一个数据,输出一个数据
-
字符函数
函数 substring/substr concat(str…)concat如果拼接了null值,返回结果也是null lpad(str,length,padstr)|rpad() trim(str) length(str) upper|lower replace(str,需要替换的字符串,替换之后的字符串) -
数学函数
函数名 ceil(数字) floor(数字) abs(数字) round(数字,n) sqrt(数字) pow(数字,n次方) -
时间日期函数
函数名 now() current_date(); current_time(); YEAR(时间) str_to_date(str,formatstr) DATE_FORMAT(date,formatstr) -
其他函数
函数名 version() user() database() -
流程函数
if(判断条件,表达式或者值,表达式或者值)
case 要匹配的值
when 值 then 返回的结果
......
else 默认的返回
end as "别名"
-
-
UDAF:聚合函数,输入多个数据,输出一个数据
函数名 sum count avg max min -
UDTF:输入一个数据,输出多个数据
代码示例:
create database demo;
use demo;
create table demo(
time datetime,
time1 timestamp
);
insert into demo values("3000-1-1 8:00:00","2038-1-19")
use school;
desc department;
show create table department;
-- 1、修改表名
alter table department RENAME dept;
-- 2、修改列名
alter table dept change department_leader leader varchar(255);
-- 3、修改列的约束和列的类型
alter table dept modify department_intro varchar(255) not null;
-- 4、增加一个新列
alter table dept add dept_create_time datetime not null;
-- 5、删除一列
alter table dept drop dept_create_time;
insert into dept(department_name,leader,department_intro,major_number) select department_name,leader,department_intro,major_number from dept;
update dept set department_address="学院路3号" where department_name="软件学院";
delete from dept where department_name="软件学院";
truncate dept;
insert into dept(department_name,leader,department_address,department_intro,major_number) values("a","a","a","a",2),("a","a","a","a",2);
select 1 as "常量",1+1 as "表达式",version() as "函数";
select department_id,leader ,1 as "常量" from dept;
select * from dept;
select * from dept where department_name != "d";
select * from dept where department_name = "a" or department_name = "b";
select * from dept where department_name in ("a","b");
select * from dept where department_address="a" and major_number >=3;
select * from dept where department_address is not null;
select * from dept where department_name like "_u%";
select * from dept where department_name like "___";
select * from dept where department_name like "_a_%" escape "a";
create table user(
user_id int primary key auto_increment,
user_name varchar(20),
password varchar(20),
sex char(5)
);
select sex,max(age) from user group by sex having max(age) > 28;
select * from user order by age asc,user_name asc;
select * from user order by age desc limit 3;
select * from user limit 4,2
show function status;
select substring("zs123",2);
select concat("zs",null);
select rPAD("zs",5,"a");
select trim(" z s ");
select length("zs");
select length(user_name) from user;
select replace("zs","s","l");
2.3.3 联合查询
-
将多条查询语句的结果整合到一个结果集当中
-
查询语句1 union|union all 查询语句2 …
注意事项:
1、联合查询要求多个查询语句的返回的列数必须是相同的
2、联合查询最后得到的结果集的表头是第一个查询语句的表头
3、union不会保留重复性的数据,union all会保留重复性的数据
代码示例:
select ceil(1.1);
select abs(-1);
select ROUND(3.1465926,2);
select sqrt(9);
select pow(3,3);
select now();
select current_time();
select year(now());
select STR_TO_DATE("2023/10/11","%Y/%m/%d");
select DATE_FORMAT(now(),"%Y-%m-%d");
use school;
select version(),user(),database();
SELECT
user_id,
user_name,
PASSWORD,
age,
CASE sex
WHEN "0" THEN '男'
when "1" then '女'
ELSE '未知'
END as "sex"
FROM
USER;
SELECT
user_id,
user_name,
PASSWORD,
age,
if(sex = "0","女",if(sex = '1',"男","未知")) as "sex"
FROM
USER;
select "男" as sex,count(1) as count_people from user where sex='0'
union
select "女",count(1) as count_people from user where sex='1'
union
select "未知",count(1) as count_people from user where sex='2';
select * from user where age<30
union all
select * from user where sex='0';
2.3.4 多表查询
多表查询———查询的数据来自于多张表,而且多张表一般都是有外键关系的
-
语法:
select 查询列表 from table_nameA inner join|left join|right join|full join table_nameB on a表和b表的连接条件(a表和b表的关联字段) .......... where group by having order by limit
-
连接查询分类
- 内连接查询:只会保留两张表的匹配数据,不匹配数据全部删除
- 外连接查询
- 左外连接查询:会保留table_nameA表的所有数据,右表只会保留匹配数据。左表中不存在右表信息会使用null值填充
- 右外连接查询:会保留table_nameB表的所有数据,左表只会保留匹配数据。
- 全外连接查询:会保留左表和右表的所有数据
【注意】笛卡尔乘积问题,连接查询的时候忘记写连接条件
代码示例:
-- 内连接查询 查询部门存在的员工信息和员工的部门名
use myemployees;
SELECT
e.*,d.department_name
FROM
employees as e
inner JOIN departments as d
-- 查询哪个城市没有部门
SELECT
*
FROM
locations AS l
LEFT JOIN departments AS d ON l.location_id = d.location_id
where d.department_id is null;
-- 查询部门名为 SAL 或 IT 的员工信息
select
e.*,d.department_name
from employees as e inner join departments as d on e.department_id = d.department_id
where d.department_name in ("SAL","IT");
-- 查询部门的城市在Seattle的所有员工信息
select
e.first_name,d.department_id,l.city
from employees as e
inner join departments as d on e.department_id = d.department_id
inner join locations as l on d.location_id = l.location_id
where l.city="Seattle";
2.3.5 子查询
查询里面有嵌套了一个查询,其中嵌套的查询称之为子查询
子查询可以出现select、where、from子语句当中
代码示例:
-- 查询部门名为IT的所有员工信息
select department_id from departments where department_name = "IT";
select * from employees where department_id in (select department_id from
departments where department_name = " IT " ) ;
-- 查询部门名为IT的所有员工的不同工种的员工数量
select
job_id,count(1)
from(select
e.*
from employees as e
inner join departments as d on e.department_id=d.department_id
where d.department_name="IT")as temp
group by job_id