网站模板与网站定制版的区别,网站的轮播图怎么做,四川省级建设主管部门网站,长沙建设工程信息网MySQL数据库基础#xff08;五#xff09;——SQL查询 一、单表查询 1、查询所有字段 在SELECT语句中使用星号“”通配符查询所有字段在SELECT语句中指定所有字段select from TStudent; 2、查询指定字段 查询多个字段select Sname,sex,email from TStudent; 3、查询指定记录…MySQL数据库基础五——SQL查询 一、单表查询 1、查询所有字段 在SELECT语句中使用星号“”通配符查询所有字段在SELECT语句中指定所有字段select from TStudent; 2、查询指定字段 查询多个字段select Sname,sex,email from TStudent; 3、查询指定记录 在SELECT 语句中通过WHERE子句对数据进行过滤语法格式为SELECT 字段名1,字段名2,…,字段名n FROM 表名WHERE 查询条件select Sname,sex,email,Class from TStudent where classjava; 4、带IN关键字的查询 查询满足指定范围内的条件的记录使用IN操作符将所有检索条件用括号括起来检索条件用逗号分隔开只要满足条件范围内的一个值即为匹配项。查新姓 王 刘 石的学生select * from TStudent where left(sname,1) in (王,刘,石); 5、带BETWEEN AND的范围查询 查询某个范围内的值该操作符需要两个参数即范围的开始值和结束值如果字段值满足指定的范围查询条件则这些记录被返回。以下查询条件查询学号100到150的学生包括100和150select from TStudent where convert(studentid,signed) between 100 and 150等价于select from TStudent where convert(studentid,signed)100and convert(studentid,signed)150 自动转换类型select * from TStudent where studentid between 100 and 150 6、带LIKE的字符匹配查询 百分号通配符‘%’匹配任意长度的字符甚至包括零字符下划线通配符‘_’一次只能匹配任意一个字符查找姓名中间字为“志”字的学生select from TStudent where sname like 志 ;查找姓名中有“志”字的学生select from TStudent where sname like %志%; 7、查询空值 在SELECT语句中使用IS NULL子句可以查询某字段内容为空记录。查找邮箱是空值的记录select * from s where email is null; 8、带AND的多条件查询 使用AND连接两个甚至多个查询条件多个条件表达式之间用AND分开。select * from TStudent where sex男 and Classnet and studentidgt;20 and studentidlt;50; 9、带OR的多条件查询 OR操作符表示只需要满足其中一个条件的记录即可返回。OR也可以连接两个甚至多个查询条件多个条件表达式之间用AND分开。select * from TStudent where sname like %志% or classnet; 10、查询结果不重复 在SELECT语句中可以使用DISTINCE关键字指示MySQL消除重复的记录值。SELECT DISTINCT 字段名 FROM 表名;查询一共有几个班select distinct class from TStudent; 11、用LIMIT限制查询结果的数量 LIMIT关键字可以返回指定位置的记录。LIMIT [位置偏移量,] 行数返回前10个学生select from TStudent limit 10;返回第11-20个学生偏移量是10就意味着从第11个开始取10条记录。select from TStudent limit 10,10; 12、合并查询结果 利用UNION关键字可以给出多条SELECT语句并将它们的结果组合成单个结果集。合并时两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。要求第一个SQL语句返回的列和第二条返回的列数相同select studentid,sname from TStudent where studentid10unionselect studentid, sname from TStudent where sname like 王%; 13、为表和字段取别名 为字段取别名MySQL可以指定列别名替换字段或表达式。列名 [AS] 列别名select studentid as 学号,sname as 姓名,sex as 性别 from TStudentselect studentid 学号,sname 姓名,sex 性别 from TStudent为表取别名 为了方便操作或者需要多次使用相同的表时可以为表指定别名用别名替代表原来的名称。表名 [AS] 表别名select a.studentid 学号,a.sname 姓名,a.sex 性别 from TStudent as a;select a.studentid 学号,a.sname 姓名,a.sex 性别 from TStudent a; 二、多表连接查询 1、内连接查询 内连接(INNER JOIN)使用比较运算符根据每个表共有的列的值匹配两个表中的行并列出表中与连接条件相匹配的数据行组合成新的记录。在内连接查询中只有满足条件的记录才能出现在结果关系中。语句3隐式的内连接没有INNER JOIN形成的中间表为两个表的笛卡尔积。select a.StudentID, a.Sname, b.mark from TStudent a, TScore b where a.StudentIDb.StudentID;语句4显示的内连接一般称为内连接有INNER JOIN形成的中间表为两个表经过ON条件过滤后的笛卡尔积。select a.StudentID, a.Sname, b.mark from TStudent a inner joinTScore b on a.StudentIDb.StudentID;select a.StudentID,a.Sname,c.subJectName,b.mark from TStudent a join TScore b on a.StudentIDb.StudentID join TSubject c on b.subJectIDc.subJectID;如果某列只在一张表中就可以不用指明是哪个表中的列。select a.StudentID,a.Sname,subJectName,mark from TStudent a join TScore b on a.StudentIDb.StudentID join TSubject c on b.subJectIDc.subJectID; 2、外连接查询 外连接分为左连接、右连接、全连接。外连接返回到查询结果集合中的不仅包含符合连接条件的行而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。外连不但返回符合连接和查询条件的数据行还返回不符合条件的一些行。外连接分三类左外连接LEFT OUTER JOIN、右外连接RIGHT OUTER JOIN和全外连接FULL OUTER JOIN。三者的共同点是都返回符合连接条件和查询条件即内连接的数据行。不同点如下左外连接还返回左表中不符合连接条件单符合查询条件的数据行。右外连接还返回右表中不符合连接条件单符合查询条件的数据行。全外连接还返回左表中不符合连接条件单符合查询条件的数据行并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集去掉重复即“全外左外 UNION 右外”。左连接包含左边表的全部行不管右边的表中是否存在与它们匹配的行以及右边表中全部匹配的行。左连接的结果集包括 LEFT OUTER子句中指定的左表的所有行而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行则在相关联的结果集行中右表的所有选择列表列均为空值。 select a.StudentID, a.Sname, b.mark from TStudent a left join TScore b on a.StudentIDb.StudentID;右连接右连接包含右边表的全部行不管左边的表中是否存在与它们匹配的行以及左边表中全部匹配的行。右连接是左连接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行则将为左表返回空值。 select a.StudentID, a.Sname, b.mark from TScore b right join TStudent a on a.StudentIDb.StudentID;全连接全连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时则另一个表的选择列表列包含空值。如果表之间有匹配行则整个结果集行包含基表的数据值。MySQL不支持全外连接。可以通过左外和右外求合集来获取全外连接的查询结果。select a.StudentID, a.Sname, b.mark from TStudent a left joinTScore b on a.StudentIDb.StudentIDunion select b.StudentID, c.subJectName, b.mark from TScore b right joinTSubject c on b.subJectIDc.subJectID; 3、交叉连接 交叉连接返回左表中的所有行左表中的每一行与右表中的所有行组合。交叉连接有显式的和隐式的不带ON子句返回的是两表的乘积也叫笛卡尔积。FROM子句中的表或视图可通过内连接或全连接按任意顺序指定但是用左或右向外连接指定表或视图时表或视图的顺序很重要。隐式交叉连接没有cross joinselect a.StudentID, a.Sname, b.mark from TStudent a,TScore b where a.StudentID 4;显示交叉连接有cross joinselect a.StudentID, a.Sname, b.mark from TStudent a cross join TScore b where a.StudentID 4; 4、SQL查询的原理 第一、单表查询根据WHERE条件过滤表中的记录形成中间表然后根据SELECT的选择列选择相应的列进行返回最终结果。第二、两表连接查询对两表求积笛卡尔积并用ON条件和连接连接类型进行过滤形成中间表然后根据WHERE条件过滤中间表的记录并根据SELECT指定的列返回查询结果。实例如下select a.StudentID, a.Sname, b.mark from TStudent a left join TScore b on a.StudentIDb.StudentID where a.StudentID 10;第三、多表连接查询先对第一个和第二个表按照两表连接做查询然后用查询结果和第三个表做连接查询以此类推直到所有的表都连接上为止最终形成一个中间的结果表然后根据WHERE条件过滤中间表的记录并根据SELECT指定的列返回查询结果。 5、过滤条件 ON条件过滤两个连接表笛卡尔积形成中间表的约束条件。WHERE条件在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。将WHERE条件移入ON后面是不恰当的。推荐的做法是ON只进行连接操作WHERE只过滤中间表的记录。 6、连接查询的适用场景 连接查询是SQL查询的核心连接查询的连接类型选择依据实际需求。如果选择不当非但不能提高查询效率反而会带来一些逻辑错误或者性能低下。两表连接查询选择方式的依据A、查两表关联列相等的数据用内连接。B、Col_L是Col_R的子集时用右连接。C、Col_R是Col_L的子集时用左连接。E、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全连接。F、求差操作的时候用联合查询。 三、对查询结果排序 MySQL中可以通过在SELECT使用ORDER BY子句对查询的结果进行排序。 1、单列排序 ASC代表结果会以由小往大的顺序列出而 DESC 代表结果会以由大往小的顺序列出。默认升序ASC排序。select from TStudent order by birthday asc;select from TStudent order by birthday desc; 2、多列排序 可以分别指定排序方向。select a.StudentID,a.Sname,subJectName,mark from TStudent a join TScore b on a.StudentIDb.StudentID join TSubject c on b.subJectIDc.subJectID where c.subJectID0001 order by mark desc,a.studentID desc; 四、分组查询 1、分组查询简介 分组查询是对数据按照某个或多个字段进行分组。// 分组查询格式SELECT columnFROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition] // 过滤条件为聚合函数使用having[ORDER BY column];聚合函数只能出现在SELECT列表、HAVING子句和ORDER BY子句中不能出现在WHERE子句中。如果要限制分组结果只能使用HAVING子句。使用group by关键字时在select列表中可以指定的项目是有限制的select语句中仅允许是被分组的列或是为每个分组返回一个值的表达式例如用一个列名作为参数的聚合函数。Where子句从数据源去掉不符合搜索条件的数据;GROUP BY子句分组使用统计函数聚合函数为每组计算统计值;HAVING子句在分好的组中去掉每组中不符合条件的数据行。 2、使用聚合函数查询 COUNT()函数select class,COUNT(*) from TStudent group by class;SUM()函数查询每个学生总分select concat(a.StudentID, ,a.sname) ss,SUM(b.mark) from TStudent a join TScore b on a.StudentIDb.StudentID group by ss;AVG()函数统计每个班平均分Select class,AVG(mark) from TStudent a join TScore b on a.StudentIDb.StudentID group by class; 3、多字段分组 统计每班每科平均分需要按两列分组class和subJectNameselect class,subJectName,AVG(mark) from TStudent a join TScore b on a.StudentIDb.StudentID join TSubject c on b.subJectIDc.subJectID group by class,subJectName; 4、使用HAVING过滤分组 查询平局分大于80的学生select concat(a.StudentID, ,a.sname) ss,avg(b.mark) m from TStudent a join TScore b on a.StudentIDb.StudentID group by ss having m80; 5、GROUP BY和ORDER BY使用 查找平均分大于80分按平均分排序。select concat(a.StudentID, ,a.sname) ss,avg(b.mark) m from TStudent a join TScore b on a.StudentIDb.StudentID group by ss having m80 order by m; 6、在GROUP BY子句中使用WITH ROLLUP 使用GROUP BY的WITH ROLLUP子句可以检索出更多的分组聚合信息不仅仅可以检索出各组的聚合信息还能检索出本组类的整体聚合信息。select class,subJectName,AVG(mark) from TStudent a join TScore b on a.StudentIDb.StudentID join TSubject c on b.subJectIDc.subJectID group by class,subJectName with rollup;能够统计每班每科的平均成绩每班的平均成绩也能统计全部班级的全部课程平均成绩也能统计。 五、子查询 1、带IN关键字的子查询 IN关键字进行子查询时内层查询语句仅仅返回一个数据列数据列里的值将提供给外层查询语句进行比较操作。 select * from TStudent where studentid in (select distinct studentid from TScore where mark98); 2、带EXISTS关键字的子查询 EXISTS关键字后面的参数是一个任意的子查询系统对子查询进行运算以判断子查询是否返回行如果至少返回一行那么EXISTS的结果为true此时外层查询语句将进行查询如果子查询没有返回任何行那么EXISTS返回的结果是false此时外层语句将不进行查询。select from TStudent where studentid01001 and exists (select from TScore where studentid01001); 3、带ANY、SOME关键字的子查询 ANY和SOME关键字是同义词表示满足其中任一条件允许创建一个表达式对子查询的返回值列表进行比较只要满足内层子查询中的任何一个比较条件就返回一个结果作为外层查询的条件。select from TStudent where studentidany (select distinct studentid from TScore where mark98)等价于select from TStudent where studentidsome (select distinct studentid from TScore where mark98);等价于select from TStudent where studentid in (select distinct studentid from TScore where mark98);子查询时还可以使用其他的比较运算符如、、、和!等。以下SQL语句子查询查出考试成绩大于98的学生的studentid比如查出的结果有三个‘00010’‘00021’‘00061’外查询将会查询比00010学号大的学生。select from TStudent where studentidsome (select distinct studentid from TScore where mark98)以下SQL语句子查询查出考试成绩大于98的学生的studentid比如查出的结果有三个‘00010’‘00021’‘00061’外查询将会查询比00061学号小的学生。select * from TStudent where studentidlt;some (select distinct studentid from TScore where markgt;98); 4、带ALL关键字的子查询 ALL关键字与ANY和SOME不同使用ALL时需要同时满足所有内层查询的条件。以下SQL语句子查询查出考试成绩大于98的学生的studentid比如查出的结果有三个‘00010’‘00021’‘00061’外查询将会查询比00010学号小的学生。select * from TStudent where studentidlt;all (select distinct studentid from TScore where markgt;98)以下SQL语句子查询查出考试成绩大于98的学生的studentid比如查出的结果有三个‘00010’‘00021’‘00061’外查询将会查询比00061学号大的学生。select * from TStudent where studentidall (select distinct studentid from TScore where mark98); 六、使用正则表达式查询 正则表达式作用是匹配文本将一个模式正则表达式与一个文本串进行比较。MySQL用WHERE子句对正则表达式提供了初步的支持允许指定用正则表达式过滤SELECT检索出的数据。在SQL查询语句中查询条件REGEXP后所跟的东西作为正则表达式处理。 1、查询以特定字符或字符串开头的记录 字符‘^’匹配以特定字符或者字符串开头的文本。select * from TStudent where sname regexp ^刘平; 2、查询以特定字符或字符串结尾的记录 字符‘$’匹配以特定字符或者字符串结尾的文本。select * from TStudent where cardid regexp 36$; 3、用符号.来替代字符串中的任意一个字符 字符‘.’匹配任意一个字符。select * from TStudent where sname regexp .康.; 4、使用*和来匹配多个字符 星号‘’匹配前面的字符任意多次包括0次。加号‘’匹配前面的字符至少一次。找出×××以19开始以6结束的学生select from TStudent where cardid regexp ^19.6$找出×××号中有123的学生select from TStudent where cardid regexp .123.; 5、匹配指定字符串 正则表达式可以匹配指定字符串只要匹配字符串在查询文本中即可如要匹配多个字符串多个字符串之间使用分隔符‘|’隔开。select * from TStudent where sname regexp 武|尹|罗; 6、匹配指定字符中的任意一个 方括号“[]”指定一个字符集合只匹配其中任何一个字符即为所查找的文本。不支持汉字。select from TStudent where email regexp [w-z];select from TStudent where cardid regexp ^[1-3,7]; 7、匹配指定字符以外的字符 “[^字符集合]”匹配不在指定集合中的任何字符。select * from TStudent where cardid regexp ^[^1-7]; 8、使用{M}或者{M,N}来指定字符串连续出现的次数 “字符串{n,}”表示至少匹配n次前面的字符。“字符串{n,m}”表示匹配前面的字符串不少于n次不多于m次。查找×××中出现138并且后面有8位0-9的数字的学生。select * from TStudent where cardid regexp 138[0-9]{15};转载于:https://blog.51cto.com/9291927/2092195
