目录
1、DELETE和TRUNCATE语句
语法格式:
DELETE FROM table_name [where 条件];
TRUNCATE TABLE table_name
DROP、TRUNCATE、DELETE的区别:
delete:删除数据,保留表结构,可以回滚,如果数据量大,很慢
truncate:删除所有数据,保留表结构,不可以回滚,一次全部删除所有数据,速度相对很快
drop: 删除数据和表结构,删除速度最快。
2、REPLACE语句
语法格式:
语法格式1:replace into 表名 [(字段列表)] values (值列表)
语法格式2:
replace [into] 目标表名[(字段列表1) select (字段列表2) from 源表 where 条件表达式
语法格式3:
replace [into] 表名 set 字段1=值1, 字段2=值2
REPLACE与INSERT语句区别:
replace语句的功能与insert语句的功能基本相同;
不同之处在于:
使用replace语句向表插入新记录时,如果新记录的主键值或者唯一性约束的字段值与已有记录相同,则已有记录先被删除(注意:已有记录删除时也不能违背外键约束条件),然后再插入新记录。
使用replace的最大好处就是可以将delete和insert合二为一(效果相当于更新),形成一个原子操作,这样就无需将delete操作与insert操作置于事务中了
3、SELECT语句中的算术表达式
对数值型数据列、变量、常量可以使用算数操作符创建表达式(+ - * /)
对日期型数据列、变量、常量可以使用部分算数操作符创建表达式(+ -)
运算符不仅可以在列和常量之间进行运算,也可以在多列之间进行运算。
语法格式:SELECT last_name, salary, salary*12 FROM employees;
MySQL的 + 默认只有一个功能:运算符
SELECT 100+80; 结果为180
SELECT '123'+80; 只要其中一个为数值,则试图将字符型转换成数值,转换成功做预算,结果为203
SELECT 'abc'+80; 转换不成功,则字符型数值为0,结果为80
SELECT 'This'+'is'; 转换不成功,结果为0
SELECT NULL+80; 只要其中一个为NULL,则结果为NULL
4、NULL值的使用
空值是指不可用、未分配的值
空值不等于零或空格
任意类型都可以支持空值
包括空值的任何算术表达式都等于空
字符串和null进行连接运算,得到也是null.
安全等于 <=>
可作为普通运算符的=
也可以用于判断是否是NULL如:where salary is NULL/(is not NULL) ->where salary<=>NULL
示例1:查询emp表奖金为空的员工信息。
select * from emp where comm <=> NULL;示例2:查询emp表奖金为50000的员工信息
select * from emp where comm <=> 50000;
5、定义字段的别名:
改变列的标题头
用于表示计算结果的含义
作为列的别名
如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都可以通过为别名添加加双引号实现。
SELECT last_name as “姓名”, salary “薪水” FROM employees;
SELECT last_name, salary*12 “年薪” FROM employees;
6、重复记录
缺省情况下查询显示所有行,包括重复行
SELECT department_id FROM employees;
使用DISTINCT关键字可从查询结果中清除重复行
SELECT DISTINCT department_id FROM employees;
DISTINCT的作用范围是后面所有字段的组合
SELECT DISTINCT department_id , job_id FROM employees;
7、限制所选择的记录
使用WHERE子句限定返回的记录
WHERE子句在FROM 子句后
SELECT[DISTINCT] {*, column [alias], ...} FROM table–[WHEREcondition(s)];
WHERE中的字符串和日期值
字符串和日期要用单引号扩起来
字符串是大小写敏感的,日期值是格式敏感的
SELECT last_name, job_id, department_id FROM employeesWHERE last_name = "king";
where后可跟的内容:
比较运算符:>, >= , < , <= , = , <=>
逻辑运算符:and or not
成员运算符:in , not in
身份运算符:is ,is not
模糊:like '_%'
正则表达式:
regexp '正则表达式'
between … and …
8、元字符(正则表达式)
. :单个的任意字符
\d :数字(0-9)
\w :ascii(数字,大写字母,小写字母,以及下划线)
Unicode(数字,大写字母,小写字母,以及下划线,以及大部分的汉字)
补集:\w
\s :空白字符(空格,换行,制表符)
补集:\s
\A :代表字符的开始(但是不匹配任何实际的东西)
\Z :匹配字符的结尾(不匹配任何实际的东西)
^ :匹配开始
$ :匹配结尾
[abc] :匹配单个字符,匹配a或者b或者c,集合
[^abc] :匹配单个字符,取的是补集,除了abc之外
量词 :贪婪和非贪婪(前提——匹配成功)
贪婪 :尽可能多的
非贪婪:尽可能少的
贪婪模式:
* :0次到任意多次,尽可能多的(a* --> a,aa,aaa,aaaaaaa;
ab* 会匹配 'a','ab',或者 'a' 后面跟随任意个 'b'。)
+ :1次到多次,尽可能多的
? :0次或1次,尽可能多的
{m,n} :指定重复的上限和下限
{m,}
{,n}
*?, +?, ??
'*', '+' 和 '?' 数量限定符都是 贪婪的;它们会匹配尽可能多的文本。 有时这种行为并不被需要;如果 RE <.*> 针对 '<a> b <c>' 进行匹配,它将匹配整个字符串,而不只是 '<a>'。 在数量限定符之后添加 ? 将使其以 非贪婪 或 最小 风格来执行匹配;也就是将匹配数量尽可能 少的 字符。 使用 RE <.*?> 将只匹配 '<a>'。
9、数据分组(GROUP BY)
GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。
分组的含义是:把该列具有相同值的多条记录当成一组记录处理,最后只输出一条记录。
分组函数忽略空值,。
结果集隐式按升序排列,如果需要改变排序方式可以使用Order by 子句。
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
#每个部门的平均工资
SELECT deptno,AVG(sal) FROM TB_EMP GROUP BY deptno
#查每个部门的整体工资情况
#如果select语句中的列未使用组函数,那么它必须出现在GROUP BY子句中
#而出现在GROUP BY子句中的列,不一定要出现在select语句中
SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM TB_EMP
GROUP BY deptno #根据部门编号分组
#每个部门每个职位的平均工资
SELECT deptno,job,AVG(sal) FROM TB_EMP GROUP BY deptno,job
分组函数重要规则
如果使用了分组函数,或者使用GROUP BY 的查询:出现在SELECT列表中的字段,要么出现在组合函数里,
要么出现在GROUP BY 子句中。
GROUP BY 子句的字段可以不出现在SELECT列表当中。
使用集合函数可以不使用GROUP BY子句,此时所有的查询结果作为一组。
10、HAVING子句(数据分组--限定组的结果)
HAVING子句用来对分组后的结果再进行条件过滤。
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BYcolumn];
#查询部门平均工资大于2000的
#分组后加条件 使用having
#where和having都是用来做条件限定的,但是having只能用在group by之后
SELECT deptno,AVG(sal),MAX(sal),MIN(sal),SUM(sal),COUNT(1)
FROM TB_EMP
GROUP BY deptno
HAVING AVG(sal) > 2000
HAVING与WHERE的区别
WHERE是在分组前进行条件过滤, HAVING子句是在分组后进行条件过滤,WHERE子句中不能使用聚合函数,HAVING子句可以使用聚合函数。
组函数的错误用法:
不能在WHERE 子句中限制组.
限制组必须使用HAVING 子句.
不能在WHERE 子句中使用组函数
补充:MySQL 多行数据合并 GROUP_CONCAT
Syntax: GROUP_CONCAT(expr)
11、对结果集排序
查询语句执行的查询结果,数据是按插入顺序排列
实际上需要按某列的值大小排序排列
按某列排序采用order by 列名[desc],列名…
设定排序列的时候可采用列名、列序号和列别名
如果按多列排序,每列的asc,desc必须单独设定
12、联合查询
举例:
中国或美国城市信息
SELECT * FROM city
WHERE countrycode IN ('CHN' ,'USA');
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'
说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION 去重复
UNION ALL 不去重复
13、查询结果限定
在SELECT语句最后可以用LIMLT来限定查询结果返回的起始记录和总数量。MySQL特有。
SELECT … LIMIT offset_start,row_count;
offset_start:第一个返回记录行的偏移量。默认为0.
row_count:要返回记录行的最大数目。
例子:
SELECT * FROM TB_EMP LIMIT 5;/*检索前5个记录*/
SELECT * FROM TB_EMP LIMIT 5,10;/*检索记录行6-15*/
14、MySQL中的通配符
MySQL中的常用统配符有三个:
%:用来表示任意多个字符,包含0个字符
_ : 用来表示任意单个字符
escape:用来转义特定字符