Mysql入门实战中

> 前面一章主要讲解了mysql的入门学习，包括数据库，表的管理，以及对数据的增删改，本章主要介绍mysql最重要的语句select的用法，将select的大部分用法进行分别讲解。 全部代码下载（csdn）：[链接](http://download.csdn.net/detail/peace1213/9398233) Github链接：[链接](https://github.com/wpeace1212/Mysql) ### 1.select语句简单介绍： select语句从语义上就可以知道主要是用来进行查询的 1. 数据表都已经创建起来了，我们已经插入了许多的数据，我们可以用自己喜欢的方式对数据表里面的信息进行检索和显示了。比如说显示我们建立的表student所有的数据： `select * from student;` 显示如下：  1. select语法格式： ~~~ 1. 简单语法： select [字段] from 表名 where 条件判断 2. 复杂的语法： SELECT select_list --描述结果集的列 INTO new_table_name --指定使用结果集来创建新表 FROM table_list --包含从中检索到结果集数据的表的列表[返回结果集的对象]。 [ WHERE search_conditions ] --WHERE 子句是一个筛选，它定义了源表中的行要满足 SELECT 语句的要求所必须达到的条件 [ GROUP BY group_by_list ] --根据 group_by_list 列中的值将结果集分成组 [ HAVING search_conditions ] --结果集的附加筛选 [ ORDER BY order_list [ ASC | DESC ] ] --结果集的附加筛选 ~~~ ### 2.简单查询： ### 2.1查询指定字段 第一部分介绍的 select * from student;中的*号是通配符，意思查询的全部列，平时我们经常要查询的可能只是名字或者分数，这是就可以指定字段进行查找 1. 语法格式： `SELECT 字段名 FROM 表名；` 1. 演示如下：选择指定字段为sname，mark的两列 ~~~ select sname,mark from student; ~~~ 1. 可以使用as关键字为字段定义别名： 如： ~~~ -- 查询指定的列 并指定别名as-- select sname,sid as id from student; ~~~ 显示如下：  1. 查询时可以添加常量列，比如某个学生都属于2014级自动化班： ~~~ -- 增加一个班级列，内容为：自动化班 字段名称为2014级 -- select sid,sname,'自动化班' as '2014级' from student; ~~~ ### 2.2限制查询行数：也叫分页查询； 当我们数据库很大时为了显示或处理方便可以使用分页查询： - **1.语法格式**： ~~~ select * from student limit [index],[count];行数不足，返回行数为实际值 或者： select * from student limit [count] OFFSET [index]; ~~~ index数为开始位置的前一行，count数为要检索的行数； - **2. 演示如下**：选出student的从第二行开始的3行 ~~~ -- 限制查询行数 -- select sid,sname from student limit 1,3; ~~~ - **3**. 还可以选择倒叙或者升序：desc降序，asc升序 ~~~ -- 查询排序 Desc降序排序 -- select * from student order by sid Desc limit 1,3; ~~~ - **4. 注意**： 在给出order by子句时，应该保证他位于 from子句之后。如果使用limit，他必须位于order by子句之后，使用子句的次序不对将产生错误消息 ### 3.复杂查询： ### 3.1查询之过滤： 数据库表一般包含大量的数据，很少需要检索表中所有行。通常会根据特定操作或报告的需要提取表数据的子集。 - **1.语法格式**： `select * from student where 条件;` 如：查询sid=2的学生： ~~~ -- 检查单个值-- select * from student where sid=002; select * from student where sid<>002; ~~~ - **2.支持的子句操作符**：  - **3.范围过滤**： 为了检查某个范围的值，可使用between操作符： `语法：select from***where***between***and***` 如：sid在002到004之间 `演示：select * from student where sid between 002 and 004;` - **4.空值检查**： 在创建表时，表设计人员可以指定其中的列是否可以不包含值。在一个列不包含值时，被称作包含空值null： `语法： select ***from***where***is***null` 如： `演示：select * from student where sgender is null;` - **5.条件逻辑过滤**： 为了进行更强的过滤控制，MySQL允许给出多个where子句。这些子句可以两种方式使用：以and子句的方式或or子句的方式使用: `语法：select * from student where 条件1 and（or） 条件2;` 如：选择sid==001和002并且mark大于1 `演示：select * from student where (sid=001 or sid=002) and mark>1;` 注意：and和or的组合带来了一个问题——计算次序：and操作符的优先级高于or.所以上面演示的时候将or语句用括号包围了 - **6.In操作符**： in操作符用来指定范围，范围中的每个条件都可以进行匹配。in取合法值的由逗号分隔的清单，全部括在圆括号中 `语法：select ***from***where in (清单)` 如：选择出sid在(001,002,003)中 `演示：select * from student where sid in (001,002,003);` 为什么要使用IN操作符？其优点如下 在使用长的合法选项清单时，In操作符的语法更加清楚而且更直观  在使用IN时计算的次序更容易管理 ,IN操作符一般比OR操作符清单执行更快 IN的最大优点是可以包含其他select语句，似的能够更动态的建立where子句 - **7.not操作符**： WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后的任何条件。 如否定刚刚的in条件： `select * from student where sid not in (001,002,003);` - **8.使用通配符**： 用来匹配值的一部分的特殊字符， 为在搜索句子中使用通配符，必须使用like操作符。like指示mysQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。 在搜索串中，%表示任何字符出现任意次数 下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符 演示如下： ~~~ -- 用通配符进行过滤 like % _-- -- 选出名字中含有eac的词-- select * from student where sname like '%eac%'; -- 选出第一个字符任意，后面字符为eace的词-- select * from student where sname like '_eace'; ~~~ - **9.用正则表达式**： 正则表达式的作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。关键字为regexp；正则表达式搜索与like比较相似；此去只做简单演示； ~~~ -- 正则表达式，不同于like，使用的操作符是regexp，匹配的也是含有-- -- 基本字符匹配-- select sname from student where sname regexp 'ong';-- 此去如果用like不会返回结果，因为不加通配符时，like时完全匹配； -- 正则书写部分基本与其他正则语言一致-- select sname from student where sname regexp '[peac,ron]'; select * from student where sid regexp '[[:digit:]]'; ~~~ ### 3.2查询之计算字段： 查询可以对一些字段进行算术运算，包括加减乘除； 1. concat（）函数来拼接两个列。 ~~~ -- 拼接-- select concat(sname,'(',sid,')') as sname from student; ~~~ 1. +,-,×，/用来计算字段： ~~~ -- 执行算法计算-- select sid,sname,sid*mark as imark from student; ~~~ ### 3.3使用函数： 对获得的数据进行处理比如对获得的字符串处理，对日期处理，对数值处理； - **1.支持的函数**：    - **2.简单演示**： ~~~ -- 文本处理函数-- select sname,upper(sname) from student; -- 日期和时间处理函数-- student -- select c_id,order_num from orders where date(order_date)='2015-11-4'; -- 数值处理函数-- select sid,sname from student where mod(sid,2)=0; ~~~ ### 3.4汇总数据（合计函数）： - **1.avg max, min, sum合计函数** Sum函数返回满足where条件的行的和 AVG函数返回满足where条件的一列的平均值 Max/min函数返回满足where条件的一列的最大/最小值 演示： ~~~ select avg(sid) as avg, min(sid) as min, max(sid) as max, sum(sid) as sum from student; -- 聚集不同的值 --distinct不相等的值 select avg(distinct mark) from student; ~~~ - **2.count函数**： Count(列名)返回某一列，行的总数 演示： ~~~ select count(*) from student; select count(sage) from student; ~~~ ### 3.5数据分组： 在SQL的语法里，GROUP BY和HAVING子句用来对数据进行汇总。GROUP BY子句指明了按照哪几个字段来分组，而将记录分组后，用HAVING子句过滤这些记录。 - **1.语法**： ~~~ #以column1 为一组计算 column2 的平均值 SELECT column1, column2. column3.. FROM table group by column1 使用having 子句过滤: SELECT column1, column2. column3..FROM table group by column1 having ... ~~~ **注意**：Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。 - **2. 简单演示**: ~~~ -- 数据分组 group by-- select mark,count(*) as num from student group by mark; -- 过滤分组having num>2-- select mark,count(*)as num from student where sid>1 group by mark having num>2; -- 注意having有where的功能，但where不可以替代having，where用于过滤结果，having用于过滤分组结果-- ~~~ ### 3.6子句顺序： – select子句顺序– – select–from–where–group by–having–order by–limit– ### 4.多表查询： 多表查询规则：1）确定查询哪些表 2）确定哪些哪些字段 3）表与表之间连接条件 (规律：连接条件数量是表数量-1) ### 4.1子查询： 子查询可以理解为 套查询.子查询是一个Select语句. - **1.分为三种方式**： 1. 表达式的值与子查询返回的单一值做比较 表达式 comparision [ANY|ALL|SOME](#) 1. 检查表达式的值是否匹配子查询返回的一组值的某个值 [NOT]IN(子查询) 1. 做为计算字段使用子查询 - **2.演示如下**： ~~~ ##使用子查询 ###1. 使用表达式的值与子查询返回的单一值做比较 --主查询返回单价比任何一个折扣大于等于25%的产品的单价要高的所有产品 Select * FROM Products Where UnitPrice 〉ANY (Select UnitPrice FROM[Order Details] Where Discount〉0.25) ###2. 检查表达式的值是否匹配子查询返回的一组值的某个值 -- 1检查detil的所有goods编号 -- 2检查具有前一步骤列出的编号的所有goods商品 select * from goods where goods_id in (select goods from detil); ###3.做为计算字段使用子查询 -- 1从goods检索商品列表 -- 2对于检索的每个商品id，统计其出现在detil的次数： select *,(select count(*) from detil where detil.goods=goods_id) as id_count from goods; #必须写全表名，防止混淆 ~~~ ### 4.2内连接查询： 通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。 - **1.定义**： 内连接（inner join或者join，也可以使用逗号）只返回两个表中连接字段相等的行； ~~~ ##内连接连接二表的例子： select * from 表1 inner join 表2 on 表1.字段号=表2.字段号 或：select * from 表1 ，表2 where 表1.字段号=表2.字段号 ##内连接连接三表的例子： select * from (表1 inner join 表2 on 表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号 ##内连接四表的例子： select * from ((表1 inner join 表2 on 表1.字段号=表2.字段号）inner join 表3 on表1.字段号=表3.字段号）inner join 表4 on 表1.字段号=表4.字段号 ~~~ - **2.演示如下**： ~~~ -- 使用内连接 -- 1 列出goods_name，goods_price客户够买的数量 ,使用逗号写 select detil.customer_id as id,goods.goods_name as g_name,goods.gooods_price as price,detil.count as count,(goods.gooods_price * detil.count) as expensive from goods,detil where goods_id=detil.goods order by id;#这种通过测量相等的为内部联接； -- 2.使用内部方式写 select detil.customer_id as id,goods.goods_name as g_name,goods.gooods_price as price,detil.count as count,(goods.gooods_price * detil.count) as expensive from goods inner join detil on goods_id=detil.goods order by id; ---- 自联接，与多表内连接一样 -- 1.先找出有问题的饼干4对应的商品id -- 2.通过商品id找到对应id生产的所有商品； select p1.goods_name,p1.goods_maker from goods as p1,goods as p2 where p1.goods_id=p2.goods_id and p2.goods_name='饼干4'; ~~~ ### 4.3外接查询： MySQL中的外连接，分为左外连接和右连接，即除了返回符合连接条件的结果之外，还要返回左表(左连接)或者右表(右连接)中不符合连接条件的结果，相对应的使用NULL对应。 - **1.左右连接**: 左连接（left join）返回左表中所有记录和右表中连接字段相等的记录 右连接（right join）返回右表中所有记录和左表中连接字段相等的记录 如果两个表中字段并不完全一一对应，想要那些没有对应的字段也显示出来就可以使用左连接和右连接查询，一个是包括左边所有，一个是包括右边； ~~~ ##左连接两表的例子： select * from 表1 left join 表2 on 表.字段号=表2.字段号; ##左连接三表查询的例子： select * from （表1 left join 表2 on 表1.字段号=表2.字段号） left join 表3 on 表2.字段号=表3.字段号 ##右连接两表的例子： select * from 表1 right join 表2 on 表.字段号=表2.字段号; ##右连接三表查询的例子： select * from （表1 right join 表2 on 表1.字段号=表2.字段号）right join 表3 on 表2.字段号=表3.字段号 ~~~ - **2.演示如下**： ~~~ -- 外部连接： 将没有关联的数据页查询出来；left查询出左边表（goods）所有的，right将右边表的所有查询； select detil.customer_id as id,goods.goods_name as g_name,goods.gooods_price as price,detil.count as count,(goods.gooods_price * detil.count) as expensive from goods left outer join detil on goods_id=detil.goods order by id; ~~~ ### 4.4组合（联合）查询： - **1.定义**： 把两次或多次的查询结果合并起来，要求查询的列数一致，推荐查询的对应的列类型一致，可以查询多张表，多次查询语句时如果列名不一样，则取第一次的列名！如果不同的语句中取出的行的每个列的值都一样，那么结果将自动会去重复，如果不想去重复则要加all来声明，即union all。 简单理解：union 跟where的多个or条件一样； 语法： ~~~ ##同一张表： select 字段1，字段2 from table1 where 条件 union select 字段1，字段2 from table1 where 条件 ##不同的表：要求字段1，2和字段3,4类型一致 select 字段1，字段2 from table1 where 条件 union select 字段3，字段4 from table2 where 条件 ~~~ - **2.演示如下**： ~~~ ##组合查询 select goods_id,goods_name from goods where goods_id>2 union select goods_id,goods_name from goods where goods_name in ('饼干1','饼干3') order by goods_id; ~~~ ### 4.5 笛卡尔积(交叉连接)查询： - **1.定义**： 笛卡尔积返回的结果为被连接的两个数据表的乘积: 假设现在有两张表A和B：如下 A表：  B表：  则查询的笛卡尔积如下：select * from A,B;  - **2.语法**： ~~~ ##在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用',' 如： SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table2 ~~~ - **3.演示如上**： 好的本章介绍到这里 来自一条小鲨鱼wpeace（rlovep.com）