前言:由于工作中的原因,平时很少能写SQL. 导致现在SQL忘了差不多, 现准备写一些简单的语法,勾起自己的记忆,还有跟我一样的人,有基础,但是忘记了 (零基础不建议看哈!) 参考文献: 《SQL必知必会》
一.简单查询
假设有 student 表, 有 id(主键), name,age ,sex四列.
1.查询单列
select name from student
2.查询多列
列名之间使用 , 分割 最后一列后不需加 逗号
select name,age from student
3.查询所有列
select * from student
4.去重查询(去除某一列,,多次出现的结果)
假设: student表中 两条记录的name 都为 张三 . 此时如果需要去重 则使用以下方式
select distinct name from student
5.限制查询的结果集 LIMIT
语法格式 : LIMIT start , number
start 表示从第几行数据开始 ,(start = 0 表示从第一行开始,以此类推)
number 表示要显示结果条数 ,number = 3 表示显示 3 条记录
start 非必需,如果 limit num 则表示 从第一条记录开始, 显示num行数据
select * from student limit 1,3 # 从student表中 显示 从第二条记录开始,往后 3条的记录 , 即 显示 第 2,3,4 行记录
6.对查询结果集排序
语法格式: order by 列名1,列名2 ,... [ASC OR DESC]
默认排序规则是升序排列 即 ASC ,DESC 表示降序排列
select * from student order by age # 按照年龄升序
select * from student order by age desc# 按照年龄降序
select * from student order by id ,age desc# 优先按照id升序 ,再按照年龄降序
二.条件(过滤)查询
1.单条件查询
Q:值何时 需要 使用 "" 引起 ?
A:如果值为字符串则需要使用 '' 或 "" 引起, 如果为数字类型 则不需
1.1 完全匹配 =
select * from student where name = "张三"
1.2 不匹配 <>
select * from student where age<> 18
1.3 范围查询 between start and end 固定格式between .. and ...
#betweent 不仅使用与数值类型 ,同样适用于 日期类型
select * from student where age between 18 and 25
1.4空值匹配 is null
select * from student where age is null
1.5非空匹配 is not null
select * from student where age is not null
2.多条件查询
多条件查询条件之间 使用 AND(表示并且) OR (或者)进行连接
select * from student where name = "张三" and age = 18 # 查找 姓名为张三 并且 年龄为18的记录
select * from student where name = "张三" or age = 18 # 查找 姓名为张三 或者 年龄为18的记录
*注意 : AND的优先级 大于 OR ,因此 如果存在多个条件,则需合理的使用( )
3.满足范围的条件
** 使用 IN 可以完成的需求,使用OR 也可以实现 ,但优先选择 IN 优点:语义清晰, 可以跟字句 ,速度更快
select * from student where name IN ("张三","李四","王五") # 查找姓名为 张三或李四或王五 的记录
select * from student where name NOT IN ("张三","李四","王五") # 查找姓名不为 张三或李四或王五 的记录
三.通配符 与 正则查询
1.like关键字
%: 表示匹配任意个数的字符
_:仅匹配一个字符
实例:
select * from student where name like '李%' # 查找姓名是李开头的记录
select * from student where name like '%阳' # 查找姓名是阳结尾的记录
select * from student where name like '%阳%' # 查找姓名中包含阳的记录
select * from student where name like '李_' # 查找姓名为2个字,并且行李的记录
#注意:
1. like '%阳' 时 , 如果需要匹配以阳结尾的词,则结尾不能加 空格, 因为空格也被算进了匹配的一部分
2.like '%' 可以匹配任意字符, 除了NULL (自带去NULL 功能,了解一下)
3.如果其他方式可以满足的需求,尽量不使用 通配符 搜索
4.like "%" 尽量不要以%开头,搜索会很慢
2.regexp 关键字
#使用 regexp关键字后的 内容中,如果存在特殊字符,会被解释
select * from student where name regexp '李' #查找name中包含 李的记录
select * from student where name like '李' #查找name 为 李的记录
#上述区别 : regexp 包含匹配, like 为完全匹配 此时 name like '李' 等价于 name = '李'
#2.1特殊字符:
. : 表示任意一个字符
$: value$ 表示匹配以value结尾的字符
^ : ^value 表示匹配以value开头的字符
| : 表示或的关系 val1 | val2 表示匹配 val1或者val2
[] : [123]val []内的内容 ,任意1个都可以 即 : 1val 或 2val 或 3val
[123] 等价于 [1|2|3]
[^]:查找非集中中的内容
[0-9] : 范围匹配 ,匹配 0-9之间的任意数字
[a-z]:匹配 a-z之间的任意字母
[A-Z]:匹配 A-Z之间的任意字母
#2.2 内置的规则字符集
[:alnum:] 任意字母数字
[:alpha:] 任意字母
[:blank:] 空格或制表符
[:cntrl:] ASCII 控制字符 (0-31 和 127)
[:digit:] 任意数字
[:print:] 任意可打印字符
[:graph:] 任意可打印字符,不包含空格
[:lower:] 任意小写字母
[:upper:] 任意大写字母
[:punct:] 非[:alnum:] 和 非 [:cntrl:]
[:space:] 包含空格在内的任意空白字符
[:xdigit:] 任意十六进制数字
使用内置规则集, 需要在规则集外再加上一层 [],否则会 按照 []的规则进行处理
例如: select * from student where age regexp '[[:digit:]]{2}'
#2.3表示数量的元字符
? : 0 或 1次
* : 0 或 多次
+ : 1 或 多次
{n}:指定数目匹配
{n,}:不少于指定数目匹配
{n,m}:指定数目出现次数在 n - m 之间匹配 (m不超过 255)
查找特殊字符 ,则需要取消特殊字符的含义,即转义
使用\: 例如 \$ 取消了 $的特殊含义 ; 同理如果匹配 则需要 \
#2.4 书写SQL完成正则简单测试
select str regexp '规则' # 如果匹配成功则返回1, 匹配失败返回0
3.like 与 regexp 的区别
like 是会匹配整个字符串 ,regexp是匹配子串
四.计算字段
何时使用计算字段?
当存储在表中的单列字段不能满足查询的要求,需要进行额外的计算,转换,拼接才可以得到想要的效果 例如:
如果想在查询结果中,1个字段就能显示某个公司的名字(公司地址) , 但是实际上,名字与地址 在表中是两个字段,此时就需要对字段进行拼接
1.字段拼接
concat(column1,column2,str1,...)
注意:在mysql中 字段拼接是 concat关键字 ,其他SQL语言 主要是 || 或 +
select concat(name,'(',address,')') from company # 以最终查询出的字段是 name(address) 这种形式
2.列中 空格的处理
rtrim(column) 忽略列右侧空格
ltrim(column) 忽略列左侧空格
3.对列使用别名(alias)
关键字: as 别名 (有时也成为导出列,实质是一个东西)
as 也可以省略,但不建议省,会影响阅读,不够直观
select concat(name,'(',address,')') as name_address from company
4.字段间计算
假设存在商品表 products ,包含 pid(产品id),pname(产品名称),price(表示单价),num(表示数量)
需求:需要查询出所有信息的基础上, 再额外显示商品的总价格 ( 单价 * 数量)
此时可以使用计算字段 (+ , - , * , /) 使用括号()可以改变优先级
select *,price * num as totalCharge from products
5.SELECT 提供很好的测试
如果省略 from 后面的内容,可以直接进行测试
例如:
select 3+5 ==> 8
select rtrim('rye ') ==> rye
五.函数
函数是 mysql 内置的封装好的工具,为我们处理数据提供了遍历, 例如:rtrim()就是内置的函数
不同DBMS的内容函数 实现差别过大,因此函数的可移植性就相对差了很多. 因此对于移植比较多的程序中,应当多写好注释;
5.1 文本处理函数
upper(str) 文本全部转为大写
lower(str) 文本全部转为小写
right(str,length) 从字符串右端开始,截取length长度个字符串 例如: select right('hello',3) ==> llo
left(str,length) 从字符串左端开始,截取length长度个字符串 例如: select left('hello',3) ==> hel
length(str) 返回字符串长度
locate(substr,str) 返回substr 在 str 中第一次出现的 索引值 (此时索引从1开始),类似java中的indexof(sub) 例如 :SELECT locate("lo",'hello') ==> 4
substring(str,start,lenth) 从str中返回一个子字符串 ,start表示开始位置的索引(从1开始), length表示 截取长度 ,如果length不填,则默认截到str最尾部
5.2 日期处理函数
5.2.0 NOW() ,CurDate() ,CurTime()
NOW() :以 yyyy-MM-dd HH:mm:ss 格式返回当前时间
CurDate() :yyyy-MM-dd 格式 返回当前日期
CurTime():HH:mm:ss 格式返回当前时间
5.2.1 AddDate
AddDate(date,INTERVAL expr unit) 其中:date表示符合日期格式的数据 ,INTERVAL 固定写死 ,expr : 正值 或 负值 例如 :1 / -1 ,unit:时间单位,常用值有:year,month,day,hour,minute,second,非常用值需自行百度
举个例子:
select adddate(now(),interval 1 year) #在当前日期的基础上 , 年+1
select adddate(now(),interval -1 year) #在当前日期的基础上 ,年-1
AddDate(date,amount) # 如果第二个参数不是上述形式, 默认是对 day 的增减
5.2.2 AddTime
AddDate(date,time) 对时间操作
举个例子:
AddDate(now(),1) #当前时间加1S
AddDate(now(),'1:1:1') #当前时间加 1 h ,1m,1s
AddDate(now(),'-1:1:1') #当前时间减 1 h ,1m,1s
AddDate(now(),'-1:0:0') #当前时间减 1 h ,0m,0s
5.2.3 Date()
返回1个时间的 日期部分 ,格式为 yyyy-MM-dd
select date(now())
5.2.4 datediff(date1,date2)
返回两个日期之间间隔的天数,天数的正负由 date1 - date2的正负号决定
select DATEDIFF(NOW(),ADDDATE(NOW(),INTERVAL -1 YEAR)) # 365
select DATEDIFF(NOW(),ADDDATE(NOW(),INTERVAL 1 YEAR)) # -365
5.25 date_add(date,INTERVAL expr unit)
用法与addDate()第一种形式, 完全一致
5.26 date_format(date,format)
以自定义格式,格式化日期 date ,mysql自定的格式信息如下:
例子:select DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
5.27 返回某个日期的 指定部分
举例: 假设 now() = 2018-07-24 12:30:50
dayofweek(date) :返回date 表示的是一周的第几天 , 返回值为: 1-7 , 对应星期值为; 周日 - 周六 ; 即如果返回 3 ,则表示那天是 周二
year(date):返回date 的年部分 即2018
month(date):返回date 的月份 即07
day(date) :返回date 的天数部分 (1 -31) 即24
hour(date) :返回date 的小时部分 即12
minute(date):返回date 的分钟部分 即30
second(date):返回date 的秒部分 即 50
date(date):返回date 的日期部分 即 2018-07-24
time(date):返回date 的时间部分 . 即 12:30:50
5.3 数值处理函数
5.3.1 abs(num) #返回一个数的绝对值
5.3.2 mod(num1,num2) # 返回 : num1 / num2 的余数 如果 num1 < num2 则始终返回num1
5.3.3 select PI() #返回圆周率
5.3.4 select RAND() #返回一个随机数
5.3.5 SELECT SQRT(num)#返回一个数的平方根