Mysql问题及SQL语句的使用教学提纲.docx
- 文档编号:29064649
- 上传时间:2023-07-20
- 格式:DOCX
- 页数:33
- 大小:294.68KB
Mysql问题及SQL语句的使用教学提纲.docx
《Mysql问题及SQL语句的使用教学提纲.docx》由会员分享,可在线阅读,更多相关《Mysql问题及SQL语句的使用教学提纲.docx(33页珍藏版)》请在冰豆网上搜索。
Mysql问题及SQL语句的使用教学提纲
Mysql问题及SQL语句的使用
1、mysql安装及简单配置:
安装Mysql时要选择UTF-8的字符集;
命令行启动Mysql:
netstartmysql
登录mysql:
mysql–uroot–p回车然后输入密码
或者mysql–hlocalhost–uroot–p回车然后输入密码
创建数据库:
createdatabasestudent
Charactersetgbk
查看已有表的详细信息:
describe表名;或者desc表名
在运行时若遇到字符集不能显示时,可在命令行通过以下命令:
setnamesgbk(改显示字符集)
结构化SQL语句:
(以下均是以学生表进行操作)
2、sql语句及使用:
2.1、select语句及使用:
查询成绩大于80的:
select*fromtb_scorewherescore>=80;
显示指定列信息:
selectsid,socrefromtb_scorewherescore>=80;
用orderby进行排序,orderby一定是应用在最后默认是升序排序(asc)降序排序是(desc)
select*fromtb_scoreorderbyscoredesc;(按照分数降序排序)
select*fromtb_scoreorderbykiddesc,score;(ID降序,分数升序进行排序)
有多个排序的时候,按照从左到右依次进行,先排第一个再排第二个…
单表查询综合应用:
selectstuid,scorefromtb_scorewherescore>80orderbyscoredesc;
含义:
从成绩表中选择成绩大于80的人的ID以及成绩按照得分的降序进行排序显示
选择列的最大值:
SELECTMAX(列名)AS(显示结果包括的列)FROMshop;
选择一列中不同的数据:
Null和“”不一样;null是没有数据‘’是有数据,但是是空
Select*fromtb_stuwheretelisnotnullandtel<>‘’;
2.2、distinct关键字的使用
Selectdistinct(列名)from(表名);
selectdistinctsold_datefromstore_information;
2.3、where子句的使用
where子句的操作符:
=等于<>或者!
=不等于
<<=小于,小于等于!
<不小于
>>=大于,大于等于!
>不大于
Betweenand在…和…之间
isnull或者isnotnull为空或者不为空
select*fromtb_studentwheretelisnull;
查出tel为null的记录tel为‘’的记录查询不出来
查询电话不为空的记录的名单
select*fromtb_studentwheretelisnotnullandtel<>‘’;
存储的时候‘’会自动TRIM掉变成‘’
Null和“”不一样;null是没有数据‘’是有数据,但是是空
Null表示允许插入值,但是没有设置过,与字段包含0,空字符串或者仅仅包含空格不同。
2.4、and和or关键字使用
selectstore_namefromstore_informationwhere(sales<2000andsales>1000)orsales>100;
几个条件可以用and和or连接起来,不同的条件间加括号分开。
And的优先级高于or
Selectprod_name,prod_pricefromproducts
Wherevenid=’dell01’orvenid=’brs01’andprod_price>10;
上面语句的意思是:
查询供应商brs01制造的价格在十元以上的产品,或者是dell01供应商生产的产品。
Selectprod_name,prod_pricefromproducts
Where(venid=’dell01’orvenid=’brs01’)andprod_price>10;
上面语句的意思是:
查询供应商brs01或者是dell01供应商生产价格在十元以上的产品。
2.5、in关键字的使用
在sql中,有两种情况会用到inz这个指令,这里先介绍其中之一:
与where有关的那一个情况,在这种情况下,我们事先至少知道一个我们需要的值。
而将这些知道的值放到in这个子句中。
SELECT"栏位名"FROM"表格名"WHERE"栏位名"IN('值一','值二',...);
select*fromstore_informationwherestore_namein("Los_Angeles");
select*fromstore_informationwheresales>200andstore_namein("Los_Angeles");
等价于:
select*fromstore_informationwheresales>200and
Store_name="Los_Angeles";
in操作符的优点:
in关键字支持和其他条件用and和or进行联合条件的查询
查询效率一般比or操作符号高
In后面可以包含select子句
2.6、between的使用
Between让我们可以在一定的范围内抓取数据库中的值
Select列名from表名where列名betweenAandB;
Select*fromtb_studentwhereagebetween16and19;
包含and后面的数值
2.7通配符
%和_
%表示任何字符或者字符串,出现任意次数_表示一个字符,一个汉字要两个下划线;
select*fromstore_informationwheresaleslike"%700%";
通配符的优缺点:
通配符比其他条件会发花费更多的时间,能用其它的条件优先选择其它的查询条件。
通配符尽量不要放在搜索条件的首部,因为这样是最慢的
2.8not、notexists操作符
not操作符只有一个功能:
就是否定它之后所跟的任何条件。
mysql中使用notexsists
2.9like关键字的使用
select*fromtb_studentwheretellike‘136*’;
使用like时对后面‘’里面的内容是区分大小写的
2.10拼接字段在mysql中使用concat
拼接字段在不同的数据库中有不同的表示:
OracleDB2Sybase使用||
SQLServer使用+
Mysql不支持以上两种字符的使用,它使用concat函数
Mysql中||相当于or,&&相当于and;
2.10.1trim的使用
去掉字段两边的空格
selecttrim(‘1111’)asafromtb_student;
运行结果:
ltrim以及rtrim去除字符串左边空格或者右边空格
2.10.2执行算术运算
mysql>select(sage+1)asxusuifromtb_stu;
可以对一些字段进行算术操作
2.11函数操作
每个DBMS都有不同的函数,这里需要参照每个数据库自己的使用说明
如提取字符串:
oracle,DB2使用SUNSTR()
SQLServerMysql使用SUNSTRING()
数据类型转化DB2使用CAST()
MysqlSQLServer使用convert()
获取当前日期
DB2使用current_date();
Mysql使用curdate();
oracle使用sysdate()
sqlserver使用getdate();
Mysql操作:
mysql>selectcurdate()astodayfromtb_stu;
操作结果:
left从左边取字符串,5表示取5位
lpad表示左补齐例子中为左补齐,补齐后为11位,用0补齐
lower(str)将str变成小写
upper(str)将str变成大写
length(str)返回str字符串的长度
right()返回字符串右边的字符
selectright(‘hellworld’,5);结果:
world
mysql>SELECTSUBSTRING('Quadratically',5);
->'ratically'
mysql>SELECTSUBSTRING('foobarbar'FROM4);
->'barbar'
mysql>SELECTSUBSTRING('Quadratically',5,6);
->'ratica'
mysql>SELECTSUBSTRING('Sakila',-3);
->'ila'
mysql>SELECTSUBSTRING('Sakila',-5,3);
->'aki'
mysql>SELECTSUBSTRING('Sakila'FROM-4FOR2);
->'ki'
日期处理函数:
mysql>selectyear(curdate())ascuryy;
运行结果:
|curyy|
|2011|
不同系统的日期操作函数:
数值处理函数:
2.12聚集函数汇总数据
聚集函数有:
avg(),返回某列的平均值
count()返回某列的行数
max()min()sum()返回某列的最大最小值总和值
count函数使用的两种形式:
使用count(*)对表中行的数目进行计数,不管某列的值是不是NULL还是非空值都会统计
使用count(某一具体列)时,如果其中某些值是NULL则这些不计入count的结果中
也可以此而成selectcount
(1)fromtb_stu;
maxminsum统计时也是忽略值为NULL的行的
selectmax(score)fromtb_score;
selectmax(distinctscore)fromtb_score;
一个默认是统计All所有的,一个是统计不同的值的聚集函数
2.13分组函数:
groupby和having子句
按照groupby后面的字段进行分组
mysql>selectavg(score)fromtb_scoregroupbykid;
|avg(score)|
|88.83333|
|91.00000|
|67.50000|
mysql>selectstuid,kid,avg(score)fromtb_scoregroupbykid;
|stuid|kid|avg(score)|
|2001|1|88.83333|
|2001|2|91.00000|
|2002|4|67.50000|
group后面不能使用别名进行分组
2.13.1过滤分组:
having子句
mysql>selectkid,avg(score)asavg,max(score)asmax,min(score)asminfromtb_scoregroupbykidhavingavg>=75;
|kid|avg|max|min|
|1|88.83333|99.0|80.5|
|2|91.00000|92.0|90.0|
不加having子句的时候
mysql>selectkid,avg(score)asavg,max(score)asmax,min(score)asminfromtb_scoregroupbykid;
|kid|avg|max|min|
|1|88.83333|99.0|80.5|
|2|91.00000|92.0|90.0|
|4|67.50000|85.0|50.0|
having子句不一定与groupby一起出现,但一起出现的情况比较多
having后面的字段一定是在select中出现的字段
where过滤的是原始行的数据,having过滤的是分组之后的数据
2.14各个子句的顺序
2.15内连接innerjoinon
应该选择一个基准表。
on关键字表示条件,两个join中间不用逗号
不采用内连接的写法:
selectst.stuname,sub.kname,sc.score
fromtb_stust,tb_subjectsub,tb_scoresc
wherest.stuid=sc.stuidandsub.kid=sc.kidandscore>70;
采用内连接写法:
selects.stuname,k.kname,c.score
fromtb_scorecinnerjointb_stusons.stuid=c.stuid
innerjointb_subjectkonc.kid=k.kid
wherec.score>=70;
两种写法的运行结果是一致的,知识查找效率有区别
在SQL规范中推荐使用innerjoin的用法,因为查找效率更高一些
运行结果:
+---------+-------+-------+
|stuname|kname|score|
+---------+-------+-------+
|张三|数学|80.0|
|张三|语文|81.0|
|张三|英语|82.0|
|张三|物理|NULL|
|张三|化学|84.0|
|李四|数学|70.0|
|李四|语文|NULL|
|李四|英语|72.0|
|李四|物理|73.0|
|李四|化学|74.0|
|李四|生物|75.0|
|王五|数学|80.0|
|王五|语文|81.0|
|王五|英语|NULL|
|王五|物理|83.0|
|王五|化学|84.0|
|赵六|NULL|NULL|
|张飞|NULL|NULL|
|曹操|NULL|NULL|
|赵云|NULL|NULL|
|司马懿|NULL|NULL|
|关羽|NULL|NULL|
|孙策|NULL|NULL|
|周瑜|NULL|NULL|
|杨过|NULL|NULL|
|小龙女|NULL|NULL|
|小龙女|NULL|NULL|
|小龙女|NULL|NULL|
2.16外连接、
leftouterjoin…on左连接
rightoutejoin…on右连接
fullouterjoin…on全连接检索两个表中的所有的行,并关联那些可以关联的行mysql不支持
leftouterjoin表示指定包含左边的表
rightouterjoin表示指定包含右边的表
leftouterjoin…on实例:
应该选定一个基准表。
selects.stuname,k.kname,c.score
fromtb_stus
leftouterjointb_scoreconc.stuid=s.stuid
leftouterjointb_subjectkonk.kid=c.kid;
注:
以tb_stu为中心,把表中所有的值列出,后面匹配的列出值,不匹配的输出为NULL
运行结果:
|李四|英语|72.0|
|李四|物理|73.0|
|李四|化学|74.0|
|李四|生物|75.0|
|王五|数学|80.0|
|王五|语文|81.0|
|王五|英语|82.0|
|王五|物理|83.0|
|王五|化学|84.0|
|赵六|NULL|NULL|
|张飞|NULL|NULL|
|曹操|NULL|NULL|
|赵云|NULL|NULL|
|司马懿|NULL|NULL|
|关羽|NULL|NULL|
|孙策|NULL|NULL|
|周瑜|NULL|NULL|
相同的语句改成内连接:
selects.stuname,k.kname,c.score
fromtb_stus
innerjointb_scoreconc.stuid=s.stuid
innerjointb_subjectkonk.kid=c.kid;
执行结果:
|stuname|kname|score|
|张三|数学|80.0|
|张三|语文|81.0|
|张三|英语|82.0|
|张三|物理|NULL|
|张三|化学|84.0|
|李四|数学|70.0|
|李四|语文|NULL|
|李四|英语|72.0|
|李四|物理|73.0|
|李四|化学|74.0|
|李四|生物|75.0|
|王五|数学|80.0|
|王五|语文|81.0|
|王五|英语|NULL|
|王五|物理|83.0|
|王五|化学|84.0|
内连接和外连接的差别(自己觉得):
内连接只显示所选内容不全为空的值,即至少会满足一些条件,外连接是只要是基准表中有这个值与他相关的属性就会显示出来。
外连接的查询结果一般会比内连接更多。
带聚集函数的连接:
就是多了个聚集函数,没什么他别的
selectst.stuname,count(sc.kid)ascount
fromtb_scoresc
innerjointb_stustonst.stuid=sc.stuid
groupbysc.stuid;
运行结果:
|stuname|count|
|张三|5|
|李四|6|
|王五|5|
2.17自连接
在一个表中查询自己的字段,自连接的查询速度较快
mysql>select*fromtb_stu
->st1,tb_stust2
->wherest1.stuid=st2.stuid
->andst2.stuname='张三';
|stuid|stuname|sage|sdept|stuid|stuname|sage|sdept|
|2001|张三|18|NULL|2001|张三|18|NULL|
2.18自然连接
先列出所有表,再写出表之间的关系
selectst.stuname,sc.score,sub.kname
fromtb_stust,tb_scoresc,tb_subjectsub
wheresc.stuid=st.stuidandsc.kid=sub.kid;
运行结果:
|stuname|score|kname|
|张三|80.0|数学|
|张三|81.0|语文|
|张三|82.0|英语|
|张三|NULL|物理|
|张三|84.0|化学|
。
。
。
2.19使用连接的注意事项
一般使用内连接,但需要查出所有值的时候也使用外连接
应该总是提供连接条件,否则会出现笛卡尔乘积
2.20组合查询
用union连接不同的select查询语句
两种情况会用到组合查询:
1、在单个查询中从不同的表中返回相似的数据结构
2、查询多个表,按单个查询结果返回
使用规则:
1、union中必须由两条级以上的selec语句组成
2、每个查询语句必须包含相同的列,表达是或者聚集函数
3、列数据必须兼容,类型不必完全相同,但DBMS必须可以隐式的转换数据类型
4、union从查询结果中自动去除重复的行,如果要返回所有的行,可以使用unionall
5、在使用组合查询时只能有一条orderby语句,只能放在最后。
orderby是用来排序组合查询结果集,而不是针对某一select查询结果集
2.21插入、更新、删除数据
2.21.1插入数据
使用insert关键字。
插入数据有几种方式:
插入完整的行,插入行的一部分,插入查询结果
插入时,每列必须提供一个值,若不插入且允许为空,则数据库会提供一个NULL值
插入完整的行:
mysql>insertintotb_Stu(stuid,stuname,sage,sdept)values('2027','郭靖',40,'武当');
mysql>insertintotb_Stuvalues('2027','郭靖',40,'武当');
插入行的一部分
mysql>insertintotb_Stu(stuid,stuname,sage)values('2027','郭靖',40);
插入查询结果
查询结果中的字段类型在插入的表中应该是相似的。
并不强制要求两个的列名完全一致,但需要对应,相应的数据类型可以做转化
使用insert…select语句,一次可以插入多个数据
insertintotb_studentselect*fromtb_stu;
2.21.2从一个表完全复制到另外一个表中
selectintomysql使用createtable
insertselect和selectinto的区别:
insertselec是增补数据到一个已经存在的表
selectinto是将从一个已经存在的表中检索的数据插入到一个新表中,有的DBMS可以覆盖已经存在的表,有的不行,着以来于具体的DBMS的规定。
mysql创建一个新表:
createtabletb_studentselect*fromtb_stu;
注意事项:
任何select子句都可以使用包括where和groupby子句
可以使用多表连接选择数据
2.21.3更新数据
更新要有where子句,要不然就全部更新了
更新一列:
mysql>select
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Mysql 问题 SQL 语句 使用 教学 提纲
![提示](https://static.bdocx.com/images/bang_tan.gif)