杭州多测师10期--课程笔记--navicatMySQL单表操作、多表连接...
单表语句：（1）查询一个表中所有的数据
格式：select *FROM 表名 ;
select *FROM hz13 ;
注意：*号表示所有，每个语句后面加上分号
(2)查询表中具体的字段
格式：select 字段名1，字段名2，字段名3FROM 表名 ;
select nameFROM hz13 ;
select name,ageFROM hz13 ;
(3)查询表中具体的字段取别名（别名是为了在查看数据时方便）
格式：select 字段1 as "姓名",字段2 "年龄" FROM 表，名;
select name as "姓名",age "年龄" FROM hz13 ;
备注：as 可以省略不写，as表示取别名
（4）查询指定内容 ，用where+条件
格式：select* from 表名 where字段名=字段 ;
select* from emp wheredept2=101 ;
where条件使用注意：
比较运算符
（1）where+条件（=(等于)， ！=（不等于） ，<>（不等于） ,<（小于）,>（大于）,>=（大于等于）,<=（小于等于））
select* from emp wheredept2=101 ;
select* from emp wheredept2 !=101
select* from emp wheredept2 <>101 ;
select* from emp wheredept2 >102 ;
select* from emp wheredept2 <102 ;
select* from emp wheredept2 <=102 ;
select* from emp wheredept2 >=102 ;
（2）and （与）
and同时满足所有条件，比如：条件1和条件2都要满足
案例：select* from emp wheredept2 >=102and age=57 ;
（3）or（或）
or 当有多个条件时满足其中任意1个条件都显示，，条件1和条件同时存在，只要满足条件1就显示，只要满足条件2就显示。
select* from emp wheredept2 =102or age=55 ;
（4）between..... and....在什么范围之间
格式：select* from 表名where表字段 BETWEEN 值得范围 and 值的范围
select* from emp whereage BETWEEN 24 and55 ;
（5）in在一组数据中匹配数据
格式：select* from 表名 where 字段名 in (数组值1，数组值2，数组值3);
select* from emp wheredept2 in (101,102,110);
（6）isnull 为空或isnotnull非空
6.1isnotnull非空
格式：select* from 表名 where字段名is not null ;
select* from emp whereageis not null ;
6.2is null为空
格式：
select* from 表名 where字段名is null ;
多行注释：ctrl+/
取消多行注释：ctrl+shift+/
单行注释：直接#号
排序：
对表中的数据进行拍讯：orderby asc 升序（可以省略） desc 降序
升序：
格式：select* from 表名 orderby字段名 asc ;
案例1：select* from emp orderbyage asc ;
案例2：select* from emp orderbyage asc ;（不填asc）
降序：
格式：
select*from 表名 orderby字段名 desc ;
案例：select* from emp orderbyage desc ;
二次排序：
格式：select* from 表名orderby 字段1 desc ,字段2 asc ;
select* from emp orderbydept2 desc , incoming asc ;
模糊匹配查询like
%：表会匹配0个字符或多个字符
—:表示一个字符
select* from empwheredept2 like "1%"#匹配1开头的部门编号数据
select* from empwheredept2 like "%2%" #匹配的部门包含2编号数据
select* from empwheredept2 like "%2" #匹配结尾包含2编号数据
select* from empwheredept2 like "__2" #匹配固定的字符2编号数据
限制查询（limit）后面接连个值，第一个值表示下标（索引），第二值是步长
备注：一个表中索引是从0开始
格式：select* from 表名 limit索引,行数 ;
案例1：select* from emp limit2,3 ;
案例2：select* from emp limit2 ; 直接查询两行
分组查询 groupby
groupby 一般和having 组合
案例：SELECTdept2,max(age) from empgroupbydept2 ;
SELECTdept2,max(age) from empgroupbydept2 ;
SELECTdept2,max(age) from empgroupbydept2 ;
group by一般不会单独使用，通常都是和函数组合使用。
groupby 后查询出来的结果，在需要的条件下可以接having
案例：SELECTdept2 ,max(age)ass from empgroupbydept2 HAVINGs>50 ;
having +条件和where+条件用法一样，但是场景不一样，一般group by 的后面接having
备注：函数求出以后，需要设置成别名，设置别名的字段，进行语句的操作。
错误案例：
（1）SELECTname,max(age) from emp
（2）SELECTname，max(age) from emp GROUP BYdept2
正确案例： select class,max(math) from Student1 group by class
以班级class分组，显示数据是班级class 、 班级数学math最高max分数，班级class要对应。
D:/0-IT/2/%E6%9C%89%E9%81%93%E4%BA%91/weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4/af043dd552c94ec38fb3e44f270f2a41/08b7333b5444.png
sql语句函数：
max最大值
案例：SELECTdept2,max(age) from emp
min最小值
案例：SELECT min(age) from emp
avg 平均值
案例：SELECT avg(age) from emp
count统计总数
案例：SELECT count(age) from emp
sum求和
案例：SELECT sum(age) from emp
distinct去重（去掉重复的）
案例：SELECTdistinct(dept2) from emp
====================================
改：
update ......set 改
格式：update 表名set 修改的字段名=字段新值 where条件 ；
案例：update empset name="成全"wheresid=1789 ;
====================================
删：
delete 删
truncate 快速删除表内数据
drop删除
删除数据速度 ：drop>truncate>delete
注意：
1、drop是删除表和数据
2、truncate 删除无法恢复
3、delete删除是可以恢复
delete：
（1）删除表中所有数据
格式：deletefrom表名：
案例：deletefromhz13；
（2）删除表中指定条件的数据
格式： deletefrom表名where条件
案例： deletefromempwhere dept2=103;
（3）快速删除表数据
格式：truncate表名;
案例：truncatehz15 ;
====================================
表取别名：
格式： 表名设置别名，
案例：SELECTs.dept2,s.name from emp as s ;（把emp表设置成临时表s）
====================================
备份表结构：
格式：createtable新表名 like备份原表名 ;
createtablehz110 likeemp ;
备份数据：
insertinto 新表 有表结构 select * from 备份表的原表
案例：INSERTinto hz110select*fromemp ;
备份部分数据：
格式：INSERTinto表名(字段名1,字段名2)selectsid ,dept2fromemp ;
INSERTinto hz110110(sid,dept2)selectsid ,dept2fromemp ;
备份表结构和数据:
格式：createtable 表名 as(select* from 备份原表 )
案例：createtablehzdcs110 as(select* from emp )
====================================
linux备份数据库：
备份用:>
格式：mysqldump -uroot -p 原数据库>数据库脚本.后缀名
案例：mysqldump -uroot -p hz10>/home/hz10bf.sql
D:/0-IT/2/%E6%9C%89%E9%81%93%E4%BA%91/weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4/ef5b7129aedb461d98cdb0d97e82cad8/bb89b7868d5b.png
还原数据库：
1、先建立一个新的空库
2、在linux中还原<
3、还原：mysql -u root-p 新建的空库<数据库备份好的数据脚本
================================
建表讲解：
dept 部门表
D:/0-IT/2/%E6%9C%89%E9%81%93%E4%BA%91/weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4/959dad6284c546d9984e7342997e4a9f/0befd013b46e.png
emp 员工表
D:/0-IT/2/%E6%9C%89%E9%81%93%E4%BA%91/weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4/fdd868525a134f27be4a1cd3f4cc7f32/d30e4f992380.png
D:/0-IT/2/%E6%9C%89%E9%81%93%E4%BA%91/weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4/e3ac46d5eee0471298b82d52c8a7bb0d/0071c6489236.png
一个学生表
分别记录姓名，年龄，性别，班级，语文，数学，英语字段
create table student2(
id int primary key ,
name char(20),
sex char(10),
age int(3),
mobile char(20),
class char(10),
english int(10),
chinese int(10),
math int(10)
)engine=innodb default charset=utf8;
insert into student2 values
(1,'小红','女',23,'13813828824','1719',77,88,98),
(2,'小明','男',23,'13713713711','1720',56,66,55),
(3,'小李','男',23,'15915913911','1719',78,64,87),
(4,'小张','男',23,'15915913912','1720',77,76,77),
(5,'小白','女',24,'15915913913','1719',90,89,98),
(6,'小陈','女',19,'15915913914','1719',84,100,81),
(7,'小钱','女',20,'15915913915',null,45,99,93);
题目
题目1
查询1719班学生的成绩信息
条件：class=1719 成绩信息：english ，math，chinese
select english，math，chinesefrom student2where class=1719 ;
题目2
查询1719班学生语文成绩大于80小于90的学生信息
条件：class=1719 chinese>80andchinese<90 * 是所有
SELECT * FROM student2 WHERE chinese>80 and chinese <90 and class='1719';
题目3
查询学生表中5-7行的数据信息
所有信息 *
Limit 4（索引） ，3（行数）
SELECT*FROMstudent2 LIMIT 4,3;
题目4
显示1719班英语成绩为90，数学成绩为98的name与mobile信息
Namemobile
条件： class=1719 english=90 math=98
SELECT name,mobile from student2 where class=1719 and english=90 and math=98 ;
题目5
显示1719班学生信息并且以语文成绩降序排序
条件class=1719 chinese order by desc
方法一：select* from student2 where class=1719 order by chinese desc;
题目6
查询1719与1720班，语文成绩与数学成绩都小于80的name与mobile信息
查询：namemobile
条件： class=1719，class=1720 ，chinese<80, math<80
方法一：select NAME,mobile from student2 where class in (1719,1720) andchinese<80 and math<80;
方法二：select NAME,mobile from student2 where(class =1719 or class =1720)andchinese<80 and math<80;
方法三：select NAME,mobile from student2 where(class =1719 andchinese<80 and math<80 ) or (class =1720andchinese<80 and math<80) ;
题目7
查询英语平均分大于80分的班级，英语平均分
显示：班级 class和平均分 avg（english）>80
条件：avg english>80
方法一：selectclass,avg(english) as aafromstudent2group by classHAVINgaa >80 ;
题目8
按班级查出数学最高分
条件： max math
方法一：SELECTclass,max(math) from student2 group by class;
题目9
查询出每班数学最低分
SELECTclass,min(math) from student2 GROUP BY class;
题目10
查询每班数学总分
select class,SUM(math) FROM student2 GROUP BY class;
题目11
查询每班数学平均分
select class,avg(math) FROM student2 GROUP BY class;
题目12
查询出每班学生总数
select class,avg(math) FROM student2 GROUP BY class;
题目13
在表中插入一条小谢的成绩数据
insert into student2 values(8,'小谢','女',23,null,null,100,50,100)
题目14
把语文分数小于80的同学分数改为80分
UPDATE student2 set chinese=80where chinese<80;
多表：
什么是多表关联查询？
定义：查询数据来源于多张表。
z
主要讲：
1、内连接（基本内连接和隐藏内敛及）
2、左连接（左外连接）
3、右连接（右外连接）
4、全连接（全外连接）
D:/0-IT/2/%E6%9C%89%E9%81%93%E4%BA%91/weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4/fca00ae4c1804fdbbc43019ab8135ac1/639585fb1069.png
=============================================
建表数据
1、讲解过程先建两个表 dept和emp表
dept 表：
dept1 部门编号
dept_name部门名称
D:/0-IT/2/%E6%9C%89%E9%81%93%E4%BA%91/weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4/3a7b8033867f4feeb541ebcf71c1e12a/755566a4f9df.png
emp表：
sid员工编号
name员工姓名
age员工年龄
woektime_start入职时间
incoming 薪资
dept2 部门编号
D:/0-IT/2/%E6%9C%89%E9%81%93%E4%BA%91/weixinobU7VjpXsC9t9lzi3H5-I_Qkqik4/f265078cf64a4dd69f3fe95f269c9377/c24c4c46e6bf.png
关联关系：dept1=dept2
=============================================
1、笛卡尔积查询（了解）
根据两张表相乘得到的结果：比如：左边有4条数据，右边有6条数据，查询出来就是4*6=24条数据，这种查询数据包含大量的错误结果，通常不会使用这种查询。
格式：SELECT * from表1,表二;
案例：SELECT * fromdept,emp ;
2、内连接（普通内连接，隐藏内连接）
查询两个表共有的关联数据。
2.1普通内连接：
格式：select *from表1 inner join 表2 on 表1.关联字段1=表2.关联字段2
案例1：select *fromdeptinner join emp on dept.dept1=emp.dept2 ;
案例2：select name,dept_namefromdeptinner join emp on dept.dept1=emp.dept2where sid=1789 ;
2.2隐藏内连接
格式：select *from表1，表2 where 表1.关联字段1=表2.关联字段2
select *fromdept,emp where dept.dept1=emp.dept2 ;
3、左连接
以左表为主（显示完整的左表），游标关联的数据就显示，没有的数据就以null显示
格式：
select *from表1leftjoin表2 on 表1.关联字段1=表2.关联字段2 ;
案例：
select *fromdeptleftjoin emp on dept.dept1=emp.dept2 ;
4、右连接
右表是以右表数据为主（显示整个右表）左表有关联的数据就显示，没有就以null值显示。
格式：select *from表1right join表2 on 表1.关联字段1=表2.关联字段2 ;
select*fromdeptright join emp on dept.dept1=emp.dept2 ;
5、左表独有数据
左表中独有的数据显示，（方法，左独有，以右表字段为空查询）
格式：select *from表1leftjoin表2 on 表1.关联字段1=表2.关联字段2 where 右表字段isnull
select *fromdeptleftjoin emp on dept.dept1=emp.dept2 where name isnull;
5、右表独有数据
右表中独有的数据显示，（方法，右独有，以左表字段为空查询）
格式：select *from表1 right join表2 on 表1.关联字段1=表2.关联字段2 where 左表字段isnull
案例：
select *fromdeptrightjoin emp on dept.dept1=emp.dept2 wheredept1isnull;
6、左表独有数据+右表独有数据：
union拼接
左独有union右独有
select *fromdeptleftjoin emp on dept.dept1=emp.dept2 where name isnull
union
select *fromdeptrightjoin emp on dept.dept1=emp.dept2 wheredept1isnull;
7、全连接
方法一：左独有+右独有+内连接
select *fromdeptleftjoin emp on dept.dept1=emp.dept2 where name isnull
union
select *fromdeptrightjoin emp on dept.dept1=emp.dept2 wheredept1isnull
UNION
select *fromdept innerjoinemp on dept.dept1=emp.dept2 ;
方法二：左连接+右独有
select *fromdeptleftjoin emp on dept.dept1=emp.dept2UNION
select *fromdeptrightjoin emp on dept.dept1=emp.dept2 wheredept1isnull ;
方法三：右连接+左独有
select *fromdeptright join emp on dept.dept1=emp.dept2 UNION
select *fromdeptleftjoin emp on dept.dept1=emp.dept2where name isnull ;
总结：
普通内连接：
select*from表1innerjoin 表2on 表1 的关联字段=表2的关联字段 ；
隐藏内连接：
select*from表1 ，表2where 表1 的关联字段=表2的关联字段 ；
左连接
select*from表1left join 表2on 表1 的关联字段=表2的关联字段 ；
右连接：
select*from表1rightjoin 表2on 表1 的关联字段=表2的关联字段 ；
左独有数据：
select*from表1left join 表2on 表1 的关联字段=表2的关联字段 where右表字段为空；
右独有数据：
select*from表1right join 表2on 表1 的关联字段=表2的关联字段 where 左表字段为空；
全外连接：
1、左独有+右独有+内连接
2、左连接+右独有
3、右连接+左独有
页:
[1]