SELECT 语句
数据库的核心使用是检索数据.所以先来学习检索数据,最后再学习插入和删除数据.
SQL语句是由关键字构成的.关键字类似于python里的系统保留字,自定义的标识符不能够与关键字相同.检索语句用到的关键字是SELECT以及一系列子句.
SQL语句不区分大小写,SQL语句最前边的是指令,之后的带有参数的关键字也被称作子句.
简单查询与排序
| 基本数据检索 | ||
| SELECT | SELECT col_name FROM table_name; | 从指定的表中检索一个列 |
| SELECT col_name1,col_name2 FROM table_name; | 检索多个列 | |
| SELECT * FROM table_name; | 使用通配符 * 检索全部列,*表示任意字符出现任意次数 | |
| SELECT DISTINCT col1,col2 FROM table_name; | 如果列内有相同数值,只返回不同值构成的列.DISTINCT同时作用于后边跟的所有列名. | |
| SELECT table_name.col_name FROM database_name.table_name; | 限定操作,指定表内的列和数据库内的表,相当于绝对路径,即使当前在use 其他库,也可以直接获取查询结果 | |
| LIMIT子句 | SELECT col_name FROM table_name LIMIT 5; | LIMIT后边加单独参数,表示返回不多于这个数目的行. |
| SELECT col_name FROM table_name LIMIT 5,5; | LIMIT后边加两个参数,第一个表示从哪一行起(这里的索引是从0)开始,后边的数字表示包含起始行开始的行数.这个例子就表示从第6行到第10行 | |
| SELECT col_name FROM table_name LIMIT 3 OFFSET 4; | 这么写与LIMIT 4 ,3的效果一样.LIMIT子句可以用在数据库查询的分页功能中. | |
| 数据检索-排序数据 | ||
| ORDER BY子句 | SELECT col_name FROM table_name ORDER BY col2_name; | 以单个列进行排序然后输出结果,注意,这个列未必是要输出的列.默认的排序是按照升序1-9,A to Z的顺序来. |
| SELECT col_name FROM table_name ORDER BY col2_name,col3_name; | 以多个列进行排序,注意,会按照参数的顺序,先排第一个列,再排第二个列,只有第一列有相同数据时,才会按照第二列排序.如果第一列数据完全不同,则不会按照第二列及以后的列进行排序. | |
| SELECT col_name FROM table_name ORDER BY col2_name DESC; | DESC用在列名后,表示该列按照降序排列 | |
| SELECT col_name FROM table_name ORDER BY col2_name DESC,col3_name; | 多个列排序时,DESC仅对它之前的列名产生效力.这个例子表示先以col2降序排列,再以col3升序排列 | |
| 数据检索-过滤数据 | ||
| WHERE子句 | SELECT col1_name,col2_name FROM table_name WHERE col2 = 2.5 | 检索两个列,但是只返回col2内等于2.5的行 |
| 操作符 | 与python基本相同,其中”<>“和”!=”都可以表示不相等,推荐使用”!=”.还有BETWEEN a AND b关键字,表示[a,b]闭区间 | |
| WHERE的位置 | 需要出现在FROM 子句之后,ORDER BY 子句之前 | |
| 字符串比较 | 如果比较字符串,需要加单引号,字符串不区分大小写 | |
| WHERE col_name IS NULL | IS NULL是WHERE语句的特殊子句,用于判断值是否为NULL.注意,在采用不是IS NULL的过滤的时候,数据库并不会返回具有NULL值的行.涉及到NULL,需要用IS NULL来判断 | |
| WHERE-逻辑运算符 | AND | 表示逻辑与,用一个WHERE子句带多个条件,需要同时满足AND的条件. |
| OR | 表示逻辑或,只需要满足各个条件之一 | |
| AND 和 OR 一起使用 | AND的优先级要高于OR,如果不清楚,可以加圆括号来优先进行运算 | |
| IN | IN表示条件是否在一个序列内,这个序列可以是一个圆括号括起来,由逗号分割的值,也可以是一张表的一列(仅能一列).IN操作符还能够包含其他SELECT语句,后边详述. | |
| NOT | NOT操作符表示取反,MySQL里支持对IN, BETWEEN和 EXISTS子句取反. | |
| WHERE_通配符%与_ | LIKE操作符 | 像之前的比较符号类似,在使用通配符的时候,操作符是LIKE关键字.通配符可以在WHERE子句中任意使用,多个使用也可以 |
| SELECT col_name FROM table_name WHERE col_name LIKE ‘cony%’; | %表示任意字符出现任意次数,这个例子表示col_name列里任何以cony开头的行.注意,MySQL是可以被设置成大小写是否敏感的,会影响搜索结果.此外,单个%无法匹配NULL | |
| SELECT col_name FROM table_name WHERE col_name LIKE ‘je_ny’; | 下划线表示只匹配单个字符,注意,_只能匹配1个字符,不能多也不能少,例子中可以匹配jenny,但无法匹配jeny,使用je%ny才能匹配jeny | |
| REGEXP正则表达式子句 | REGEXP出现在WHERE之后,表示其后是一个正则表达式 | MySQL支持的正则表达式并不是完整的正则表达式,此外需要注意,通配符是匹配整个内容才会返回找到,而正则只需要匹配部分内容,便可以认为是匹配成功.此外,正则表达式默认不区分大小写,可在REGEXP后加上BINARY用于区分大小写 |
| . | .用来匹配任意一个字符,不能多也不能少. | |
| | | 表示OR,即搜索多个串,可以连用 | |
| [] | 匹配几个字符之一,比如[adfs],表示匹配1个字符,这1个字符可以是adfs之一.在方括号的开头加^表示取反,例如[^ad]表示除了ad之外的1个字符. | |
| 转义符号\ | 与python原始字符串传2个\\类似,MySQL也需要传2个\\给正则表达式.其他与标准正则一样 | |
| 重复元字符 | 和标准正则相同,*表示0-无穷,+表示1到无穷,?表示0或者1次匹配,还能用大括号表示重复次数. | |
| 字符类 | MySQL里使用了特殊的字符类来表示 | |
| 定位元字符 | 与标准正则类似,^表示开头是,$表示结尾是.但是标准正则里表示锚点的\b,在MySQL里用[[:<:]]表示单词开始,用[[:>:]]表示单词结束 | |
| 预定义字符类 | MySQL里采用了独有的预定义字符类,都是用来表示匹配1个字符的某一类字符,详细字符类看这里 | |
| 单独测试正则 | 在MySQL里可以不操作数据库,单独使用SELECT ‘string’ REGEXP ‘expession’测试正则表达式,如果返回1表示找到匹配内容,返回0表示找不到匹配内容 | |
| 数据检索-分组数据 | ||
| GROUP BY 子句 | GROUP BY col_name | 按照某个列的数据进行分组,会先按照某个列,将所有的数据进行分组,去掉重复内容,然后对每个组进行运算,一般要和聚集函数一起使用. |
| GROUP BY 的特点 | 如果要使用GROUP BY,SELECT之后的除了聚集函数之外,所有的列都要出现在GROUP BY 子句后边.GROUP BY 指定多个列时,按照最后一列进行分组.分组列中有NULL值的话会归为一列. | |
| GROUP BY 出现顺序 | WHERE–GROUP BY–ORDER BY,一般使用GROUP BY 之后,最好使用ORDER BY以保证输出的结果有意义 | |
| WITH ROLLUP | 用在GROUP BY 之后,例如 GROUP BY col_name WITH ROLLUP,会再上升一个层级,显示一个大组有多少汇总结果 | |
| HAVING 关键字 | HAVING | HAVING 与WHERE 二者语法规则相同,HAVING过滤分组结果(聚集函数),WHERE只能按行过滤 |
| 工作顺序 | 可以这么理解:WHERE先按行第一次过滤所有数据,GROUP BY对第一次过滤后的数据进行分组,聚集函数对分组后数据进行运算,HAVING对聚集结果进行第二次过滤.WHERE工作在GROUP BY 之前,HAVING工作在GROUP BY 之后 | |
| SELECT及子句的顺序 | SELECT –> FROM –> WHERE –> GROUP BY –> HAVING –> ORDER BY –> LIMIT | |
高级检索-函数
之前的检索,都是基于每个表内的数据本身.可能会想到,如果按照要求取出了数字,可以放到其他的编程软件内进行数据操作.实际上,MySQL不只能返回实际存储的值,还能够返回经过处理后的值(这些值不实际存储在原始的表里),这可能比通过其他应用程序要快很多,因为DBMS本来就是为此项工作而生的.计算字段:是SELECT语句的过程中(可以认为类似内置函数),根据各种函数返回一个以其他列数据为基础,经过处理后的新列.
| 数据检索-函数 | ||
| 文本处理函数 | LEFT(str,len) | 返回左边指定长度的字符串 |
| RIGHT(str,len) | 返回右边指定长度的字符串 | |
| CHAR_LENGTH(str) | 按照实际字符长度返回长度,一个汉字的长度是1,和LENGTH函数不同 | |
| LENGTH(str) | 返回字符串按照字节存储的长度,比如utf-8下,一个汉字的长度为3 | |
| CONCAT(str1,str2,col_name,…) | 拼接字符串,参数也可以是列名来拼接数据,如果有一个数据为NULL,结果就是NULL | |
| CONCAT_WS(separator,str1,str2,…) | 可以自定义分隔符,此外不会忽略任何空字符串,但会忽略NULL | |
| CONV(N,from_base,to_base) | 进制转换,例如SELECT CONV(10,16,2),表示将16进制的10(十进制的16)转换成2进制,结果是10000 | |
| FORMAT(X,D) | 将X的形式表示为标准逗号分隔的数字形式,D为小数位数,如果D=0,返回结果不带有小数部分 | |
| INSERT(str,pos,len,newstr) | 在str的指定位置插入字符串,pos为起始索引,超过str长度则返回str,len为替换的长度,如果len大于str长度,则返回newstr,newstr为新字符串 | |
| INSTR(str,substr) | 返回str中第一个出现substr子串的位置 | |
| LOWER(str) | 小写 | |
| UPPER(str) | 大写 | |
| LTRIM(str) RTRIM() TRIM() | 分别去掉左侧,右侧和两侧空白,类似python字符串的strip方法 | |
| LOCATE(substr,str) | 从str中位获取子序列索引位置,索引从1开始 | |
| REPEAT(str,count) | 返回一个重复count次的str,如果count<=0,则返回空字符串,如果str或者count为NULL,则返回NULL | |
| REPLACE(str,from_str,to_str) | 返回str中from_str子串被替换为to_str之后的字符串 | |
| REVERSE(str) | 返回反转的字符串 | |
| SUBSTRING(str,pos,len) | 从str中取一个子串,用法比较灵活见下面 | |
| 数据检索-聚集函数 | ||
| 基本聚集函数 | AVG(col_name) | 返回特定列或行的平均值会忽略NULL值 |
| COUNT() | COUNT(*)对所有满足条件的寒假计数,不管是NULL还是空值.COUNT(col_name)会忽略NULL值. | |
| MAX(col_name) | 返回指定列的最大值,注意,对于文本也适用 | |
| MIN(col_name) | 返回指定列的最小值,对于文本也适用 | |
| SUM(col_name or expression) | 可以指定列名来求和,也可以跟一个表达式,来计算各个表达式的和 | |
| 其他使用方法 | 聚集不同值 | 上面的5个函数,如果在参数之前加上DISTINCT,表示只考虑参数中不同的项.注意,DISTINCT无法使用在COUNT(*)上,DISTINCT后边必须使用列名,不能用于表达式. |
| 一次性使用多个聚集函数 | SELECT函数后边用逗号 分割多个聚集函数即可 | |
其他常用的日期和时间函数,以及运算函数可以看官方文档.在了解了函数之后,再来看一类聚集函数,这些函数并不是像上边的函数一样作用于列的每一行数据得到一个新的列结果,而是用于汇总.这些聚集函数都在WHERE之后才发生作用.
高级检索-连结表
所谓关系型数据库,就是除了按照表来存放数据以外,还可以存储数据之间的关系,以避免相同的数据出现太多次数.关系型数据库的核心就是把信息分解为多个类,一类信息一个表,然后存储这些信息之间的互相关联关系.表的关系通过外键来建立.外键就是某个表中的一列,引用到另外一个表的主键值(如果不是通过外键自动建立关系,则插入数据时很可能对应到无效的引用).通过外键,即可实现维护引用完整性.
创建外键有两种方式,一种是在创建表的时候指定,一种是在创建表之后,用ALTER命令修改某列为外键.不推荐ALTER修改,应该在表设计的时候想好.
CREATE TABLE tb1(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(10),
department_id INT,
p_id INT,
CONSTRAINT fk_1 FOREIGN KEY (department_id,p_id) REFERENCES tb2(tid,xid)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
指定主键,在数据类型之后用PRIMARY KEY,主键必须不为空,所以前边要指定NOT NULL,此外,一般可以设置为自增列,一个表内只能有一个自增列.如果指定了AUTO_INCREMENT,则必须将此列设置为主键.
当然,主键也可以不自增,可以任意控制主键,这在高并发场景下比较常见.主键也可以指定多个列,要求多个列的组合不能够重复.
指定外键的时候不像在新增列的时候一起指定,需要先创建某列及数据类型,然后在最后,通过 CONSTRAINT 增加一个约束,然后起一个外键的名称,之后指定本表内的一个或多个列引用到其他表格的对应列即可.外键一定要引用到其他表的主键.
提到了主键和外键后,还有一种约束叫唯一键,一张表内未必只有主键是不能重复的.可以通过UNIQUE (列名,列名)来指定某一列不可重复,与主键不同的是,唯一键可以为NULL.
连结是指的在引用完整性生效的情况下,根据创建的关系,生成一个经过引用之后的新表,这个表并不存在数据库内,而是根据表的数据以及之间的关系,在查询的时候生成的新表.
| 连结查询 | ||
| 选取不相关列 | SELECT col1_name col2_name FROM table1_name,table2_name; | 用SELECT选取不相关的表的多列,会得到一个笛卡尔积,即这两列数据个数相乘的结果 |
| 用WHERE加上限定可以指定表的连结关系 | WHERE可以通过指定两个表的某两列匹配的关系来连结表,注意,这并不一定需要两列是外键和主键,这叫做内部连结 | |
| 选取不相关列 | JOIN 关键字使用方法 | SELECT col_name FROM table1 LEFT|RIGHT|INNER JOIN table2 ON table1.col = table2.col,可以连用,一般写成一行一行,不需要加逗号分割语句 |
| LEFT JOIN | 表示左边的表显示全部内容 | |
| RIGHT JOIN | 表示右边的表显示全部内容 | |
| INNER JOIN | 表示其中不显示NULL行 | |
高级检索-子查询
简单的说,就是查询的结果返回给另外一个查询使用,也就是SELECT语句嵌套,一般用在WHERE子句内的条件判断 或者 INSERT SELECT 语句上.在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。子查询注意如果有重名列,一定要使用限定名.
在子查询的时候,可以将查询到的结果外边加括号,然后用AS起个别名,就可以在子查询的时候使用该表和该表的列,经常可以用来反复在一个表内查询数据.
子查询的效率比较低下,在进行子查询的时候,可以考虑优先使用连结表,将所需对应的数据放到同一个表上来,然后再通过条件查询获得结果.
这里的17题有一个例子:
select sc.student_id,
(select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
(select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
(select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
count(sc.course_id),
avg(sc.num)
from score as sc
group by student_id desc
其实将SELECT …. FROM table as alias_name 当做外层循环,其中的引用是内层循环,外层的别名循环实际上是可以传递到内层去的.
这样从同一个表反复抓取属于同一个id的数据特别方便.
还有一个要点是case条件,可以直接在SELECT之后处理数据:
select course_id,max(num),min(num),case when min(num) < 10 THEN 0 ELSE min(num) End as other from score GROUP BY course_id;
与case类似的还有三元 if (isnull(score.num),0,score.num) 语句
同一个表内查询有相等数据的时候,也可以先进行笛卡尔积,然后再进行筛选,会比较有意思.
高级检索-UNION
UNION的作用是将查询结果上下连接,并且自动去重.UNION可以连续使用,UNION ALL则表示不去重,只是将各个查询结果上下并到一起.
UNION连接的各个查询必须有相同的列,表达式或者聚集函数,不过列的顺序可以不同.此外数据类型也必须互相兼容
如果使用ORDER BY 子句,必须放在UNION所连接的最后一个SELECT语句之后
