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 nulla is null如果为null,结果为真
is not nulla is not null如果为not null,结果为真
betweena between b and ca在b和c之间为真
likea like ba匹配b结果为真
ina 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'))
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页