一、库语句
1.查询现有数据库
(1)语法:show databases;
**(2)示例 **
表示当前有五个数据库
2.创建数据库
(1)语法:create database 库名
(2)创建失败示例:忘记加关键字database
(3)创建成功示例
(4)细节
数据库的名字也不能使用mysql中的关键字
除非:加上的符号,例如:
关键字`
(5)指定字符集(*)
语法:charset 字符集名
在创建数据库时,在名字后面加上 charset utf8
如:create database 数据库名 charset utf8
一般mysql默认字符集是GBK,这样是无法使用汉字字符的,需要指定utf8才可。我们是推荐都在创建数据库的后面加上,否则后续只能重新建数据库
3.选中数据库
(1)语法:use 数据库名
(2)示例
(3)作用:只有选中数据库,才能进行后续的表操作
4.删除数据库
(1)语法:drop database 数据库名字
(2)示例:
(3)后果:删除数据库后,对应所有的表也会消失,所以这个是一个很危险的操作
二、初阶表操作
在选中数据库之后才能进行的表操作,同一个数据库中,表名不能重复;不同库则可以
1.查看数据库现有表
(1)语法:show tables;
(2)示例:
2.查看表结构
(1)语法:desc 表名
(2)示例:可以查询表的结构,有多少列,分别对应什么名字和类型
3.创建表
(1)语法:create table 表名(变量名字 类型,变量名字 类型,……);
(2)示例:
(3)注意:名字在前,类型在后;类型就决定了后续插入数据的类型
4.删除表
(1)语法:drop table 表名
(2)示例:
(3)注意:删表是比删库还危险的操作,慎重!
5.全列查询
(1)语法:select * from 表名
(2)作用:查询这个表的所有列和已有的数据
(3)示例:该表中只有一个数据
(4)注意:这种操作只适合在前期学习的时候使用,当数据量庞大的时候,不推荐
6.删除表2
(1)语法:delete from 表名 where 条件;
删除的维度都是一行,也需要添加合适的条件;不添加任何条件,就是删除这个表的所有
(2)与drop的区别
drop是删除整个表,表和内容都没了。
delete删除是把表的内容都删完了,但是表格还在。
(3)示例
7.修改操作
(1)语法:update 表名 set 列名 = 值 where 条件;
如果不加限制条件,会将一列所有的元素全部修改。这种会影响硬盘上面存储的数据
(2)示例:修改单个列
(3)示例:修改多个列
注意事项:
修改时,不能超出类型本身的限制,否则就是修改失败。
为了防止被修改的数据太多,我们建议一条一条的修改,也就是在后面加上limit 1的限制
三、插入操作
进行插入操作时,最好是清楚表的结构,否则容易出现错误
1.全列插入
(1)语法:insert into 表名 valuse(值,值,值,值…);
(2)作用:这样的操作是对所有的列都插入操作,值必须刚好对应上所有的列
(3)示例:关键字into是可以省略的
2.指定列插入
(1)语法:insert into 表名 (列名,列名……) values(值,值……);
(2)注意:每个值要对应的相应的列;两个列名的顺序可以换,但是相应的值顺序也要换
(3)示例:这里只插入了name、age,未插入的列默认为null值
3.一次插入多组数据
(1)一次性全列插入多组数据
做法:只需要多加几个括号即可
(2)一次性指定列插入多组数据
4.插入操作的细节
(1)基本数据类型
(1)表示整数:int
(2)表示小数:double
(3)表示字符串:varchar 使用时在后面指定长度
(4)表示时间日期:datetime
(5)decimal(a,b):表示小数,a表示这个数的长度,b表示小数的位数
(2)插入字符串
字符串例如:汉字,字符、字符串;需要在两头加上单引号或者双引号(英文)
(3)插入时间
插入时间例如:2018-3-28 也需要加上单引号或者双引号,如:"2018-3-28"
四、数据库的约束
这是在建表的时候加上的约束,约束也是对表起的效果
1.not null
(1)作用:用来限制某一列不能为空
(2)正常无约束时
(3)加上约束后
(4)插入数据失败
(5)成功插入数据
2.unique
(1)作用:限制这一列的值不能有重复的
(2)没有约束时
(3)加约束
(4)插入数据失败
3.default
(1)作用:设置默认值。当没有对这列数据赋值时,默认是default。没有加该约束时,默认是NULL
(2)正常无约束时
(3)未加约束时的默认展示
(4)加了约束后
(5)添加数据后
4.primary key
(1)作用:主键,用来标记某一列,作为身份的唯一标识。类似身份证的作用
外键的两个要求:
(1)不能为null
(2)不能重复
(2) 未设置主键时
(3)加了主键后
(4)当被主键约束且为空时
(5)当重复时
(6)not null和unique配合也可以承当主键
(7)主键的使用推荐
1.一个表只能有一个主键
2.一个主键不一定只针对一个列(很少用)
3.主键一般设置在整数类型
(8)自增主键
写法:在设置主键时,跟在主键后面。关键字:auto_increment
添加后:
作用:
当插入数据的这一列不赋值时,系统自动赋值为:当前最大值+1
插入数据:
各种情况:
3-99的值如果需要用,需要手动指定
5.foreign key
(1)作用:外键约束,用于两张表的约束。设置外键的列在父表中也是唯一的(被主键约束或者被unique约束)
(2)语法格式:子表的列名不一定要和父表名字一样
列名 类型,foreign key (子表的列名) references 主表(父表的列)
(3)未设置外键时
他们的列名相同,按理来说student的classId必须遵循class表中的classId,也就是如果class表中没有的id,student中是不能有的。但是这两个表当前并没有任何关系,也就是没有被约束,所以可以随便填
(4)重新设置外键
外键设置的表称为子表,表示受限于父表
此时,class称为父表,student称为子表。student中的classId值在class表中要必须存在
(5)当值不存在时
正常添加:
(6)不能随便删和修改除父表的值
如何删除父表呢?:加一个标记字段
1.在父表中增加一个字段(可以约定这个值为1时,表示有效数据;当为0是,表示无效数据)
2.在想要删除的时候,不能直接delete,而是可以把这个字段改成0
3.后续在查询的时候,只能返回有效数据,因此相当于删除了
4.这种做法在计算机很多方面都有使用,称为逻辑删除
6.check
用来规定填入的某个数据只能是某个值,如:男或女
语法格式:sex varchar(2),check (sex = '男' or sex = '女')
这种用法很少,且新版本才支持,所以用的很少很少
五、一阶查询
前面提到过,全列查询是一个危险操作,所以下面学习其他方式的查询操作
1.指定列查询
(1)语法:select 列名,列名 from 表名
(2)示例:
2.表达式查询
(1)语法:select 表达式(由列组成) from 表名;
(2)示例:
(3)表达式可以起别名,语法:as 别名
不仅表达式可以起别名,后续的很多情况也可以起别名
3.去重查询
(1)语法:select distinct 列名 from 表名;
(2)作用:查询的所有列中,会将重复的两行合并成一行
(3)示例:第一个是未查重的,第二个是查重操作
合并失败:所有的列并不重复,只有部分重复
4.排序查询
(1)语法:select * from 表名 order by 列名/表达式 默认排升序
(2)排降序语法:select * from 表名 order by 列名/表达式 desc
(3)未排序:
按照math排升序
按照math排降序
(4)细节
1.NULL在order by 的时候,视为最小值
2.如果存在多个NULL,他们的顺序是不确定的
3.select 列名 from oeder by 列名,order by后面的列名不一定要在select后面的列名中出现
4.在SQL中,对操作数进行算数运算时,只要有一个NULL,最终结果就是NULL
(5)可以排序多个列:如果第一个列名相同,则会继续比较第二个
5.条件查询(*)
(1)语法:select */列名/表达式/去重…… from 表名 where 条件(条件就是一些一些符号表达式,可以有多个)
(2)表达式(用来表示条件)这里不推荐使用别名
(3)示例展示
1)>,>=,<,<=
2)=、<=>、!=、<>
用来判断null,一般使用<=>和<>
3)between and 前闭后闭
4)in 匹配离散集合
5)like
两个通配符
(1)%:表示可以匹配任意多个(包括0)任意字符
(2)_:表示任意一个字符
查询孙某
like模糊匹配的缺点:开销很大,性能非常低
6)is NULL、is not null
用来查询非空/空的行
7)and、or、not 一般and的优先级高于or
逻辑符号,相当于java中的&&、||和!
(4)作用:使用好可以配合后面的操作,更细腻的查询精准的信息
6.分页查询
(1)语法:
(1)limit+数字:限制每次查询最多返回的记录个数。如:limit 3只能查询到前3条记录
(2)offset:偏移量。如:limit 3 offset 3:偏移了前3条记录,从第4条开始向后查询3条。不写offset时默认是0
(3)limit 6,3;这里的6代表offset,3代表查询的最大条数
(2)作用:配合排序操作,可以查询第几条消息
(3)示例
查询总成绩前三的同学:
六、二阶查询
这里的查询操作,可以把多个行进行合并
1.新增插入查询
(1)语法:insert into 表1 select * from 表2;
(2)作用:在查询表1的时候,可以同时把查询到的数据插入到表2中
(3)全列查询插入
(4)指定列查询插入
2.聚合查询
通过聚合函数将多个行合并
(1)语法:select 聚合函数(列名) from 表名;
(2)聚合函数
函数说明count返回查询到的数据的数量sum返回查询到的数据总和,不是数字没有意义avg
返回查询道德数据的平均值,不是数字没有意义
max返回查询到的数据的最大值,不是数字没有意义min返回查询到的数据的最小值,不是数字没有意义
(3)count
返回查询到的数据的数量(行数)。
步骤:先查询结果,再进行聚合
求总行数:NULL也会计算在内
求个别列:NULL不会计算
(4)sum
求总和,不是数字没有意义
sum不能进行*查询,只能指定列
(5)avg
求平均值,不是数字没有意义
(6)max
求最大值,不是数字没有意义
(7)min
求最小值,不是数字没有意义
3.分组查询 group by
(1)定义:
对指定列进行分组操作:指定某个列,针对这个类,把值相同的行,分到一组中,可以针对每个组,分别进行聚合查询。
(2)注意事项:
1.select指定的列必须是分组依据字段,否则就会像下面的一样,有的数据不会显示。
2.非group by的列,不应该直接写在select查询的列中,但是搭配聚合函数是可以的。
会有这些问题:
(3)group by结合聚合查询进行:
查询每个岗位有多少人:
步骤
第一:先执行select role,id from emp(先将指定表中的这些列查询出来)
第二:再根据group by role,按照role这个列的值和上面查询的结果进行分组
第三:根据每个组,进行count聚合操作
id和role都进行了聚合,就不会发生数据丢失
但是这样子不可以,必须配合聚合操作
(4)求每个岗位的平均的薪水(分组依据:岗位)
错误:非group byd的列role应该使用聚合函数
修改 :待考证
having:
给聚合查询指定条件。在group by分组之后,不能使用where语句,而是需要having
where可以在分组前使用(聚合之前的条件),也就是在平均工资里面,没有计算张三
查询每个岗位的平均工资,但是刨除平均工资超过2w的数据(条件是在聚合之后执行的,只能用having)
上面两种条件结合:
七、三阶查询/多表查询
联合查询又称多表查询,一次性可以查询多个表。
基本语法1:select * from 表名,表名 where 条件
基本语法2:select * from 表名 join 表名 on 条件
1.多表查询及步骤
(1)同时查询两个表就行将两个表中的行分别进行全排列,也就称为笛卡儿积
实践多表查询:
通过下面的查询:这样不设置条件直接查询,会得出很多无效的数据
加上条件:
(2)总结步骤
下面有四张表:
1:班级表
2:学生表
3:课程表
4:分数表
汇总:
总结步骤:
(1)先确定要查询的信息,来自哪些表
(2)针对这两个表进行笛卡儿积
(3)加上连接条件,去掉无效条件
(4)再根据题目要求,补充其他条件
(3)按照步骤进行查询
查询许仙同学的成绩:
1)确定信息来自哪些表
2)对这两个表进行笛卡儿积
3)加上连接条件,去掉无效条件
4)接着补充条件
优化:
2.多表查询join on
语法:表1 join 表2 on 条件
(1)使用join on查询许仙同学的成绩
(2)查询所有同学的总成绩,及同学的个人信息
第一步:表来自student、score
第二步:加入连接条件
第三步:优化
(3)查询所有同学的成绩,及同学的个人信息
第一步:列出相关的表:学生表、课程(所有的成绩,需要知道对应的科目)、分数表
第二步:三个表笛卡儿积
第三步:设置连接条件
第四步:精简
一般多个表之间笛卡儿积的话,每两个表之间都会有联系的,否则是不好笛卡儿积的,数据量太庞大。
以上的写法属于内连接
3.外连接
内连接和外连接都是基于笛卡儿积进行计算的,但是对于空值/不存在的值处理方式是不一样的。内连接只能得到两张表中都存在的数据,而外连接不一样
外连接:只能使用join on 的方式写,可以在join前头加上left/right的关键字,称为左连接/右连接。加上后表明left/right表的数据会全部输入
student join score,此时,在join的左边student就是左侧表,右侧就是右侧板。
加了left左边的表会保证每个数据都会存在,不存在部分会有null补充
4.自连接
自连接就是同一张表对自己进行笛卡儿积
(1)查询所有同学的“计算机原理”成绩比“Java”成绩高的成绩信息
所有成绩:
对应的课程:
题目要求就是找到课程号3的成绩大于课程和1的,也就是3>1。两个成绩都在一张表上,比较的话就是行与行之间的比较,不好实现。这个时候借助笛卡儿积就可以就两个表放在一起,这就是自连接。
直接笛卡儿积:
做法:自连接时,给两张表都起别名。下面圈起来的就是满足条件的
加上优化条件:对应同一个人
加上:左边是计算机原理课程,右边是Java课程
加上:分数对应大小关系,3>1
自连接的优缺:
优:可以把行之间的关系转换成列之间的关系
缺:产生的很多结果是没有必要的
5.子查询
这种查询方式将简单问题复杂化,例如直接将一个函数的返回值作为另一个函数的参数,直接写在了一行上面(复杂化)
概念: 把多个sql嵌套成了一个sql,在实际中不推荐使用。返回一行记录的子查询
(1)查询与“不想毕业”同学的同伴同学
步骤:要想知道他的同班同学,需要知道是什么班;通过名字可以查询到他们是什么班级
正常查询:
先查询“不想毕业”同学的班级号:
通过班级号可以查询到他们班:
使用子查询:
(2)多行子查询
使用in()的条件查询,在in中可以有多个参数
查询“语文”或“英语”课程的成绩信息
需要知道课对应程号,通过课程号可以查到这科的成绩
正常的查询:
多行子查询:
6.合并查询
可以同时查询多个表,最后把结果合并到一张表上并输出。使用关键字:union
正常查询:查询id小于3,或者名字为“英语”的课程。
使用or
使用合并查询:
这样使用union不是多此一举吗?不是的,union可以查询不同的表,而or只能针对一张表。
要求:每个表查询的结果集合列中的类型和个数都要匹配,才能合并,并且可以自动去重
两个不同的表:
合并查询:
版权归原作者 代码小娥 所有, 如有侵权,请联系我们删除。