文章目录
一、mysql的三大范式
-
- 原子性 字段不可在分割
-
- 唯一性 字段必须依赖于主键
-
- 冗余性 避免数据量过大
二、连表联查
删除之前的表,创建两个新表,插入数据
student
score
1.union
union:多个结果集进行合并(纵) 格式:select 字段 from 表名 union 表名; 注意:查询时多张表的字段数量保持一致 直接使用union时,相同的数据只显示一行 使用union all时,相同的数据都会显示 查询结果的列名以第一次sql语句的结果列为准
truncate table student;清空表
2.笛卡尔积
前面的数据与后面表的数据每一行去匹配,10*30会变成300行数据
3.表连接
为了减少数据膨胀,在笛卡尔积的基础上增加条件
1个ID对应三科成绩,数据变为30行
join
- join中连接条件用on 不用where。
- left join:左表为准 匹配右表的数据,左表中的数据匹配不到右表的数据就显示null,右边的数据和左表不匹配就不会显示
格式:select 字段 from 表名 left join 表名 on 连接条件; - right join:右表为准 匹配左表的数据,右表中的数据匹配不到左表的数据就显示null,右边的数据和左表不匹配就不会显示
格式:select 字段 from 表名 right join 表名 on 连接条件; - inner join:两张表的交集
格式:select 字段 from 表名 inner join 表名 on 连接条件;
在原先的student中增加一个学生 “学生”,便于观察结果
左连接
以左表为准,右边没有补空
右连接
以右表为准,学生没有匹配到
内连接
只显示两者共有的,不以某一个为准
4.逻辑判断
逻辑判断:case when 判断条件 then 值 [when 判断条件 then]* [else 值] end
格式一:
格式二:
注意:枚举类型判断时,要加上引号,如上的1,0表示男女,判断时需加上引号
5.连表查询
- select * from student where id in(select distinct studentid from score where score<(select avg(score) from score));
查询有科目小于平均值的学生信息
- select * from student,(select studentid,sum(score) as “总分” from score group by studentid) s where s.studentid=student.id;
查询每个学生的总分和学生的信息 - select * from student left join (select studentid,sum(score) as “总分” from score group by studentid) s on s.studentid=student.id;
查询每个学生的总分和学生的信息
- select * from student
inner join
(select studentid,sum(score) as ss from score group by studentid) s
on s.studentid=student.id order by ss desc limit 3;
获取前三信息
注意:得到的表和值如果不是原先存在的,后期需要用到,需要重给名,但给的不能是“…”类型,只能由字母或数字组成
- select * from (
select * from student
inner join
(select studentid,sum(score) sum_score from score group by studentid)
ss on student.id=ss.studentid) s where (select count(*) from
(select * from student
inner join
(select studentid,sum(score) sum_score from score group by studentid)
ss on student.id=ss.studentid) s1 where s.sex=s1.sex and s.sum_score<s1.sum_score)< 3;
查询男生中的前三和女生中的前三
- 查询每科最高分
select subjectname,max(score) from score group by subjectname;
- 查询语文最高分及学生信息
- select * from student inner join (select * from score where subjectname=“语文” and score=(select max(score) from score where subjectname=“语文”)) s on student.id=s.studentid;
注意:不能在在查询聚合函数例如最大值最小值得时候,直接查询其他数据。
三、从其他表中加载数据
从其他表中加载数据 格式1:create table 表名 as 查询语句 例如:create table student1 as select * from student; 格式2:insert into 表名 查询语句 例如:insert into test select * from score;
四、视图
-
视图:是一张虚拟表,数据都是存储在基础表中
-
创建视图
格式:create view 表名 as 查询语句;
例如:create view tmp as select * from student_info where age=25;
-
查看视图结构:
格式:desc 表名
例如:desc tmp;
格式:show create view/table 表名;
例如:show create table tmp;
-
删除视图:
格式:drop view 表名
例如:drop view tmp;
-
视图和基础表的关系是一对一:在视图中进行增删改查时,会在基础表上发生改变,基础表发送改变,视图也会改变
-
视图和基础表的关系是一对多:视图中不能进行insert和delete,可以修改和查询,且基本表中数据也会改变。
五、sql执行顺序
- from–where–group by–having–select–order by
- from:需要从哪个数据表检索数据
- where:过滤表中数据的条件
- group by:如何将上面过滤出的数据分组
- having:对上面已经分组的数据进行过滤的条件
- select:查看结果集中的哪个列,或列的计算结果
- order by :按照什么样的顺序来查看返回的数据
六、索引
索引:便于查询,主键默认拥有一个索引
-
索引不是越多越好,因为索引也需要存储空间去存放,索引越多占的资源就越多
-
添加索引:
格式:alter table 表名 add index 索引名称(索引列);
例如:alter tabel student add index Index_name(name); -
删除索引:
格式:alter table 表名 drop index 索引名称;
例如:alter table student drop index Index_name;
七、事务
- 事务就是一段sql语句的批处理,一个操作下可以分为多个操作,事务决定了这多个小操作都实行完成,才认为这一个操作执行结束
- sql默认执行自动提交
- set autocommit=0;关闭i自动提交,等下一次打开数据库时,又默认为自动提交
- 事务的操作:
begin:开始一个事务
rollback:事务出现问题进行回滚
commit:事务没有问题进行提交- 例如:
BEGIN;
update person set money=5000-2000;
update bank set money=0+2000;
select * from person;
select * from bank;
commit;
- 例如:
注意:事务中所有的操作都是临时或虚拟的,在进行commit才会把这些操作才原数据上进行执行
关闭自动提交事务
这里暂时为500,由于未提交,实际表中的数据并未被更改。
加上commit后,事务提交,表中数据更改
在交之前如果怕数据出错,可以加上回滚,出错后,会重新运行,确保每次提交的数据都是正确的。
八、在xshell中执行脚本运行sql语句
Shell操作mysql 格式:#!/bin/sh MYSQL="mysql -h192.168.154.110 -uroot -p123456 --default-character- set=utf8" sql="select * from shujuku.student" result="$($MYSQL -e "$sql")" echo -e "$result"
建立j脚本
执行脚本
九、总结
mysql三大范式 1. 原子性 字段不可在分割 2. 唯一性 字段必须依赖于主键 3. 冗余性 避免数据量过大 连表联查 union:多个结果集进行合并(纵) 格式:select 字段 from 表名 union 表名; 注意:查询时多张表的字段数量保持一致 直接使用union时,相同的数据只显示一行 使用union all时,相同的数据都会显示 查询结果的列名以第一次sql语句的结果列为准 join left join:左表为准 匹配右表的数据,左表中的数据匹配不到右表的数据就显示null,右边的数据和左表不匹配就不会显示 格式:select 字段 from 表名 left join 表名 on 连接条件; right join:右表为准 匹配左表的数据,右表中的数据匹配不到左表的数据就显示null,右边的数据和左表不匹配就不会显示 格式:select 字段 from 表名 right join 表名 on 连接条件; inner join:两张表的交集 格式:select 字段 from 表名 inner join 表名 on 连接条件; 逻辑判断:case when 判断条件 then 值 [when 判断条件 then]* [else 值] end 从其他表中加载数据 格式1:create table 表名 as 查询语句 例如:create table student1 as select * from student; 格式2:insert into 表名 查询语句 例如:insert into test select * from score; 视图:是一张虚拟表,数据都是存储在基础表中 创建视图 格式:create view 表名 as 查询语句; 例如:create view tmp as select * from student_info where age=25; 查看视图结构: 格式:desc 表名 例如:desc tmp; 格式:show create view/table 表名; 例如:show create table tmp; 删除视图: 格式:drop view 表名 例如:drop view tmp; 视图和基础表的关系是一对一:在视图中进行增删改查时,会在基础表上发生改变 视图和基础表的关系是一对多:视图中不能进行insert和delete from--where--group by--having--select--order by from:需要从哪个数据表检索数据 where:过滤表中数据的条件 group by:如何将上面过滤出的数据分组 having:对上面已经分组的数据进行过滤的条件 select:查看结果集中的哪个列,或列的计算结果 order by :按照什么样的顺序来查看返回的数据 索引:主键默认拥有一个索引 索引不是越多越好,因为索引也需要存储空间去存放,索引越多占的资源就越多 添加索引: 格式:alter table 表名 add index 索引名称(索引列); 例如:alter tabel student add index Index_name(name); 删除索引: 格式:alter table 表名 drop index 索引名称; 例如:alter table student drop index Index_name; 事务:一个操作下可以分为多个操作,事务决定了这多个小操作都实行完成,才认为这一个操作执行结束 执行事务之前关闭自动提交:set autocommit=0; 事务的操作: begin:开始一个事务 rollback:事务出现问题进行回滚 commit:事务没有问题进行提交 例如: BEGIN; update person set money=5000-2000; update bank set money=0+2000; select * from person; select * from bank; commit; 注意:事务中所有的操作都是临时或虚拟的,在进行commit才会把这些操作才原数据上进行执行 Shell操作mysql 格式:#!/bin/sh MYSQL="mysql -h192.168.154.110 -uroot -p123456 --default-character-set=utf8" sql="select * from shujuku.student" result="$($MYSQL -e "$sql")" echo -e "$result"
java
java入门基础学习(一)
java入门基础学习(二)
java入门基础学习(三)
java入门基础学习(四)
java入门基础学习(五)
java入门基础学习(六)
java入门基础学习(七)
java入门基础学习(八)
java入门基础学习(九)
java入门基础学习(十)
java入门基础学习(十一)
java入门基础学习(十二)
java入门基础学习(十三)
java入门基础学习(十四)Maven Git
java总结,题目+笔记
java进阶之常见对象(一)
java进阶之常见对象(二)
java进阶之冒泡排序
java进阶之选择排序
java进阶之面向对象(封装)
java进阶之面向对象(代码块、继承)
java进阶之面向对象(多态、抽象、接口)
java进阶之匿名内部类、访问修饰符、包
java进阶之io流(字节流,字符流)
Linux
Linux基础一
Linux基础二
Mysql
mysql一
mysql二