1小时回顾MySQL语法(上)

前言:由于工作中的原因,平时很少能写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)#返回一个数的平方根