MYSQL数据管理

MYSQL数据管理

一、外键

--添加外键
alter table 外键表 add constraint FK_约束名 foreign key ([外键列]) references [主键表] ([主键列]) 
alter table person1 add `work_id` int(4) not null comment "工作id"		--添加外键列
alter table `work1` add constraint `fk_work_id` foreign key (`id`) references `person1` (`work_id`)

--！！！！删除有外键关系的表的时候，必须要先删除引用的表（从表），再删除被引用的表（主表）
--！以上操作都是物理外键，数据库级别的外键，我们都不建议使用！（避免数据库过多而造成困扰）

二、DML语言（数据操作语言）

1、添加（insert）

--插入语句
insert into 表名 ([字段1，字段2，字段3,...]) values('字段1值'，'字段2值','字段3值',...)
insert into `work1` (`mywork`,`work_adress`)values('学生','xxxx职业技术学院')

--!!由于主键自增，我们可以省略

--！！！如果不写字段，就会一一匹配，id自增长也会匹配；一般我们写插入语句，我们一定要数据和字段一一对应
insert into `work1` values(null,'老师1','xxxx职业技术学院')

--插入多个字段
insert into 表名 字段名 （属性1），（属性2）.....
insert into `work1` `mywork` values('老师2'),('老师3')

--!字段是可以省略的，但是跟后面的值必须一一对应
--！可以同时插入多条数据，values后面的值，需要使用‘，’隔开即可--》》（"值1"）,("值2")...

2、修改（update）

--修改语句
update 表名 set `字段名`=？ where 条件;
update `work1` set `mywork`='科学家' where id = 5;
update `work1` set `mywork`='科学家' where id > 5;			--改变id大于5的属性
--！！！不指定条件将会修改整张表

--修改多个属性，用英文‘，’隔开
update 表名 set `字段名`=？,`字段名`=？ where 条件;
update `work1` set `mywork`='农民',`Work_adress`='农场' where id = 6;

--通过多个条件定位数据
update 表名 set `字段名`=？,`字段名`=？ where 条件1 OR 条件2;	--条件1、条件2任意成立都改变
update 表名 set `字段名`=？,`字段名`=？ where 条件1 AND 条件2;--只改变条件1与条件2都成立的属性
update `work1` set `mywork`='教师',`Work_adress`='xxx学院' where id = 7 or `mywork`="老师";

3、删除(delete)

• delete 命令

--删除表数据
delete 表名			--不会影响自增
--删除数据删除指定数据
delete from 表名  [where 条件]
delete from `work1` where `mywork`="学生"

• TRUNCATE 命令

作用：完全清空一个数据库表，表的结构和索引不变

--清空表数据
TRUNCATE 表名			--自增会归零

• delete以及TRUNCATE的区别
  • TRUNCATE 重新设置 自增列 计数器会归零
  • TRUNCATE 不影响事务
  • delete清空数据表后不会影响自增，但是会出现以下两种现象：
    • 引擎是INNODB的情况下，重启数据库会重置自增（存在内存中断电即无）
    • 引擎是MYISAN的情况下，重启数据库会继续上一个自增量（存在文件中不会丢失）

4、运算符

算术运算符

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

比较运算符

符号	描述	备注
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

逻辑运算符

运算符号	作用
NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

位运算符

运算符号	作用
&	按位与
|	按位或
^	按位异或
!	取反
<<	左移
>>	右移

5、DQL查询数据

（Data Query LANGUAGE：数据查询语言）

• 所有查询操作都用到 ：select
• 简单查询、复杂查询都能做到
• 使用频率最高的语言

--查询所有字段
select * from 表

--查询指定字段
select 字段名,字段名 from 表名

--为查询字段起一个别名
select 字段名 as 别名,字段名 as 别名... from 表名
select id as "学号",name as "姓名" from person1

--拼接函数 Concat（a,b)
select concat("姓名：",`name`) as 新字段名 from person1
--！拼接函数要取别名，不然会字段名会显示concat("姓名：",`name`)

--发现重复的数据，可以使用distinct去重 
select distinct 字段名 from 表名
select distinct name from person1

--查询语句的一些其他用法
select version() 					--查看系统版本
select 2123*32-123 as "计算结果："	--计算结果
select @@auto_increment_increment 	--查询自增的步长
select `id`+1 as "加一后的id" from person1  	--操作查询结果的值

• 模糊查询

---like结合_（表示一个字符）；like结合%（表示0~任意个字符）

--查询匿性人员id及姓名
select `id`,`name` as "匿性" from `person1` where `name` like "匿%" 
--查询匿性人员名字后面只有一个字的id及姓名
select `id`,`name` as "匿性" from `person1` where `name` like "匿_" 
--查询名字中含有匿字的人员id及姓名
select `id`,`name` as "匿性" from `person1` where `name` like "%匿%" 

---in（具体一个或者多个值）
select `id`,`name` from `person1` where `name` in ("匿名") 				  --单个值
select `id`,`name` from `person1` where `name` in ("匿名","匿天而行","匿子")  --多个值

---null not null
--查询电话为空的人员
select `id`,`name` from `person1` where `tellphone` IS　NULL

• jion联表查询（重点）！！！

连接可以大致分为三大类：

（1）、内连接：join，inner join

（2）、外连接：left join，left outer join ，right join ，right outer join ， union ，union all

（3）、交叉连接：cross join

--内连接（join, inner join)
select r.`studentno`,`subjectno`,`studentresult`
from `student` as s
inner join `result` as r
on s.studentno = r.studentno

/*
内连接可以理解为，两个表中同时满足某条件的数据记录组合。也就是表A和表B中满足条件a.id = b.a_id的所有记录。
当表A中的一条记录对应表B中的多条记录时，会以重复的方式对应多条表B记录出现在结果集中。
当表B中的一条记录对应表A中的多条记录时，会以重复的方式对应多条表A记录出现在结果集中。
*/

--左连接(left join , left outer join)
select s.`studentno`,`subjectno`,`studentresult`
from `student` as s
LEFT JOIN `result` as r
on s.studentno = r.studentno
/*
左外连接，会以左边的表为主表，返回所有行，即使右表中没有匹配的行。
如果左边的表在右表中找不到一条记录，则返回左所有记录并且右表相应的字段设为null。
*/

--右连接（，right join ，right outer join）
select s.`studentno`,`subjectno`,`studentresult`
from `student` as s
right OUTER JOIN `result` as r
on s.studentno = r.studentno
/*
右外连接，会以右边的表为主表，返回所有行，即使左表中没有匹配的行。
如果右边的表在左表中找不到一条记录，则返回右表所有记录并且左表相应的字段设为null。
*/

--练习：查询参加考试的同学的信息：学号，姓名，课程名，分数
select r.`studentno`,`studentname`,`subjectname`,`studentresult`
from `student` as s
right OUTER JOIN `result` as r
on s.studentno = r.studentno
inner join `subject`	as sub
on sub.subjectno=r.subjectno

--mysql并不支持全连接，不过有相应的替代方案，就是left join union right join 来代替。
--全连接会从表A和表B中返回所有的行，如果表A中的行在表B中没有匹配，或是表B中的行在表A中没有匹配，这些行都会显示，不存在的字段以null补充。
--union会把其中重复的行合并。
select * from a left join b on a.id = b.a_id
union
select * from a right join b on a.id = b.a_id;

--交叉连接，交叉连接实际上就是表A与表B的笛卡尔乘积。
select * from a cross join b;
select * from a, b;

• 自连接

自己的表和自己的表连接，核心：将一张表拆成两张表

--查询父子信息，将一张表看成两张表来查询
select p.`categoryname` as '父类',s.`categoryname` as '子类'
from `category`	as p,`category` as s
where p.categoryid=s.pid;

• 分页（limit)和排序(order by)

  排序

  • 升序

  select * from `result`
  order by `studentresult` asc   --asc 可以省略
  

  • 降序

  select * from `result`
  order by `studentresult` desc
  

  分页

  • 缓解数据库压力，给人更好的体验

  --limit分页
  select * from `result`
  order by `studentresult` desc
  limit 0,2
  --！limit在查询语句最后一条
  --！limit 起始页，单个页面数据条数
  

• 子查询和嵌套查询

本质：在where 语句中再嵌套一个where 语句；

--子查询：查询所有subjectno等于9的学员学号、课程号、分数，降序排列
select `studentno`,`subjectno`,`studentresult` 
from `result` 
where subjectno=(
	select subjectno from `subject`
	where subjectno =9
)
ORDER BY studentresult asc

--！！！报错：Subquery returns more than 1 row：表示子查询返回了多行数据
例如:
select * from 表名1 where 字段名=(select 字段 from 表名2)
解决方法：添加any
select * from 表名1 where 字段名=any(select 字段 from 表名2)

--联表查询与嵌套查询乱入
select stu.studentno,studentname from student as stu
	inner join result as r
	on stu.studentno=r.studentno
	where studentresult >= 60 AND subjectno in (
		select subjectno from `subject` as sub 
		where subjectname = "Java程序设计-1"
	)

6、Mysql函数

• 常用函数

  • 处理绝对值的函数

    • 绝对值函数：abs(x)
    • 向上取整函数：ceil(x)
    • 向下取整函数:floor(x)
    • 取模函数：mod(x,y)
    • 随机数函数：rand()
    • 四舍五入函数：round(x,y)
    • 数值截取函数：truncate(x,y)

  • 处理时间的函数

    • 获取当前日期：curdate()，current_date()
    • 获取当前时间：curtime()，current_time()
    • 获取当前日期时间：now()
    • 从日期中选择出月份数：month(date),monthname(date)
    • 从日期中选择出周数：week(date)
    • 从日期中选择出周数：year(date)
    • 从时间中选择出小时数：hour(time)
    • 从时间中选择出分钟数：minute(time)
    • 从时间中选择出今天是周几：weekday(date),dayname(date)

  • 字符串函数

    • 合并字符串函数：concat(str1,str2,str3…)
    • 比较字符串大小函数：strcmp(str1,str2)
    • 获取字符串字节数函数：length(str)
    • 获取字符串字符数函数：char_length(str)
    • 字母大小写转换函数：大写：upper(x),ucase(x)；小写lower(x),lcase(x)

• 聚合函数

  • avg

    • --计算平均分
      select avg(`studentresult`) from result
      

  • count

    • --计算总个数，会忽略所有的null值
      select count(`studentname`) from student
      --select（*)，不会忽略null值，计算总行数
      select count(*) from student
      

  • max

    • --计算最大值
      select max(`studentresult`) from result
      

  • min

    • --计算最小值
      select min(`studentresult`) from result
      

  • sum

    • --计算总和
      select sum(`studentresult`) from result
      

• 分组

--group by 分组字段
--having 过滤分组记录必须满足的次要条件
--练习：查询不同课程的最高分最低分平均分，以课程名分组,且平均分大于60
select subjectname,avg(`studentresult`) as avgr,max(`studentresult`),min(`studentresult`)
from result as r
inner join subject as sub
on sub.subjectno=r.subjectno
group by subjectname
having avgr>60

7、MD5加密

主要增强算法复杂度和不可逆性

MD5不可逆，具体的值的mad5是一样的

--加密
update `student` set loginpwd=MD5(loginpwd) where studentno=1000	--加密单个属性
update `student` set loginpwd=MD5(loginpwd)							--全部加密

--插入的时候加密
insert into `student` values(10007,"多来"，MD5（"123456"）)