MySQL：数据管理

3.MySQL数据管理

3.1外键（了解）

当前表中某个字段为另一张表的主键。那么这个字段为当前表的外键，为另一张表的主键。当前表为从表，另一张表位主表。

主表不能直接删除，必须先删除从表。

外键约束实现：

alter table 表名 add constraint 约束名 foreign key(表中外键) references 表名(表中的主键);

上述实现的外键是数据库级别的外键，即物理级别。

以后：

• 数据库只存表，不添加外键约束。

• 以后用程序去实现外键。

3.2DML语言

数据库的意义：存储和管理数据。

DML语言：数据库操作语言

• insert
• update
• delete

3.2.1添加

 --数据插入语句
 insert into 表名([字段名1,字段名2,字段名3,字段名4,字段名5]) values('值1','值2','值3','值4','值5')；
 --数据和字段要一一对应
 --若存在字段约束不能为空，那么插入数据时，必须给该字段赋值。除非有默认值。
 --若字段名不写，则值要和表字段顺序一一对应
 insert into student(`id`,`name`,`sex`,`age`,`lesson`) values('201215121','李勇','男',20,'CS');
 insert into student values('201215122','刘晨','女',19,'CS');
 --多条数据同时插入
 insert into student 
 values('201215121','李勇','男',20,'CS')
 ，('201215122','刘晨','女',19,'CS');

3.2.2修改

--修改表中数据
update 表名 set 字段名=值,[字段名=值,···] where [条件]
--where后是我们条件
update `student` set `name`=李四,`age`=22 where `id`=1

3.2.3删除

--删除指定数据
delete from 表名 where 条件

--删除表中的所有数据
truncate 表名
--or
delete from 表明
/*（区别：
-- 都不会表结构
-- truncate重置自增
-- truncate不会影响事务
*/

3.3DQL查询语言

3.3.1DQL

（data query language：数据查询语言）

• 所有查询操作都用它 select
• 简单的查询，复杂的查询
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

3.3.2指定查询字段

简单的查询语句：

--查询全部的学生
SELECT * FROM student

--查询指定字段
SELECT `studentno`,`studentname` FROM student

--别名，给结果取个名字。可以给表和字段都起别名
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student AS s

--函数 CONCAT(a,b)
SELECT CONCAT('姓名：',`studentname`) AS 姓名 FROM student

select 字段······ from 表名

去重：

--去重
--查询哪些同学参加了考试
SELECT DISTINCT `studentno` FROM result

数据库列

select 表达式 from 表名

--学生考试成绩加1
SELECT `studentno`,`studentresult`+1 AS '成绩' FROM `result`

3.3.3where条件子句

**作用：**检索数据中满足条件的值

逻辑表达式

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or ||	a or b b||a	逻辑或，其中一个为真，则结果为真
not ！	not a !a	逻辑非，真为假，假为真

尽量是用英文，例如用and 而不是用&&。

--查询成绩在95和100之间的
SELECT studentNo,`studentresult` FROM result WHERE `studentresult`>=95 AND `studentresult`<=100

SELECT studentNo,`studentresult` FROM result WHERE `studentresult` BETWEEN 80 AND 100

模糊查询

运算符	语法	描述
is null	a is null	如果为null，结果为真
is not null	a is not null	如果为not null，结果为真
between	a between b and c	a在b和c之间为真
like	a like b	a匹配b结果为真
in	a in （a1,a2,a3）	a在集合中结果为真

--查询姓刘的同学  %匹配0到多个字符  _匹配一个字符
SELECT`studentno`,`studentname` FROM `student`
WHERE studentname LIKE '张%'	

SELECT`studentno`,`studentname` FROM `student`
WHERE studentname LIKE '%张%'	

SELECT`studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1001,1002)

3.3.4联表查询

join 外连接

--联表查询
select s.studentNo,studentName,subjectNo,studentResult from student as s
inner join result as r
on s.studentNo = r.studentNo

--右连接
select s.studentNo,studentName,subjectNo,studentResult from student as s
right join result as r
on s.studentNo=r.studentNo

--左连接
select s.studentNo,studentName,subjectNo,studentResult from student as s
left join result as r
on s.studentNo=r.studentNo

连接方式	例子	描述
内连接	a inner join b on a.id=b.id	取a和b表中id相同且同时存在的数据
左外连接	a left join b on a.id=b.id	取a中的全部项，如果a的id在b中存在，那么就把b中id相同项连接在后面；如果a中的id在b中不存在，则在属性对应的位置设置为null
右外连接	a right join b on a.id=b.id	取b中的全部项，如果b的id在a中存在，那么就把a中id相同项连接在后面；如果b中的id在a中不存在，则在属性对应的位置设置为null
全外连接	a full join b on a.id=b.id	取a,b中的全部项，如果a中id没有在b中或者b中id没有在a中，那么缺少的值用null添加；如果a中的id和b中的id相会都在对方表中存在，那么则将表中对应的项连接起来

例：

---

a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
--------------------------------------------------
1） 内连接
select a.,b. from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2

2）左连接
select a.,b. from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null

3） 右连接
select a.,b. from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4

4） 完全连接
select a.,b. from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 　　 3 34 4
3 王武 null

等值连接

满足条件时，才连接，并得到连接后的项的组成表。

e.g S： A B R：B C

​ 1 2 2 1

​ 3 3 7 2

​ 5 9 3 5

等值连接（S.A=R.C）

​ A S.B R.B C

​ 1 2 2 1

​ 5 9 3 5

等值连接（S.B=R.B）

​ A S.B R.B C

​ 1 2 2 1

​ 3 3 3 5

自连接（自连接是特殊的等值连接后去重列）

满足两个表中存在相同字段时，根据相同字段值相同时才连接，并得到连接后的项的组成表，并去重列。

自然连接

​ A B C

​ 1 2 1

​ 3 3 5

自连接

一张表自己和自己连接，即两张一样的表连接。

满足条件才连接。

3.3.5查询分析

1. 分析需要查询哪些字段
2. 分析这些字段在哪些表中
3. 分析这些表的连接方式
4. 分析连接条件
5. 分析查询条件

3.3.6分页和排序

分页：limit

-- 分页的原因：缓解数据库压力，给人的体验更好
select * from 表名
limit 起始index,数据项个数

select * from student
limit 0,5

a:当前页，  b：总页数   n: 每页中个数的大小
起始值： (a-1)*n
b = 数据总数/n

排序：order by

--升序asc  降序desc
--根据查询的结果，根据字段排序
select * form student
order by score asc|desc

3.3.7子查询

本质，在where 语句中嵌套一个子查询语句

where (select语句)

3.3.8分组

3.4MySQL函数

分组：分组是和聚合函数一起用的。

group by 字段名 --按字段名分组
having 条件 --分组后应满足的条件

3.4.1常用函数

-- 数学函数
abs() --绝对值
ceiling() --向上取整
floor() --向下取整
rand() --返回一个0到1之间的随机数
sign() --返回一个数的符号 0-0 负数 - -1  正数 - 1

--字符串函数
char_length() --返回字符串的长度
concat() --合并字符串
insert() --插入替换
upper() --小写转大写
lower() --大写转小写
instr() --字符出现的位置
replace() --替换出现的字符串
substr() --截取指定位置的字符串
reverse() --反转字符串

--时间和日期函数
current_date() --获取当前日期
now() --获取当前函数
localtime() --本地时间
sysdate() --系统时间

--系统函数
system_user() --当前用户
user() --当前用户
version() --当前版本

3.4.2聚合函数

函数名	描述
count()	计数
sum()	求和
avg()	平均数
max()	最大值
min()	最小值
…

--都能统计表中的数据
select count(sutdentName) from student;  --会忽略null值
select count(*) from student;	--不会忽略null，
select count(1) from student;	--不会忽略null，

select sum(score) as sum from result
select avg(score) as avg from result
select max(score) as max from result
select min(score) as min from result

3.4.3数据库级别的MD5加密

md5：不可逆。

update student set pwd=md5(pwd) where id =1; --插入后加密
insert into student values(4,'李四',md5('123456'))