MySQL从删库到跑路（三）——SQL语言

作者：天山老妖s

链接：http://blog.51cto.com/9291927

一、sql语言简介

1、sql语言简介

sql是结构化查询语言(structured query language)，是用于访问和处理数据库的标准的计算机语言。
sql语言的功能如下：
a、sql面向数据库执行查询
b、sql可从数据库取回数据
c、sql可在数据库中插入新的记录
d、sql可更新数据库中的数据
e、sql可从数据库删除记录
f、sql可创建新数据库
g、sql可在数据库中创建新表
h、sql可在数据库中创建存储过程
i、sql可在数据库中创建视图
j、sql可以设置表、存储过程和视图的权限
sql是一门ansi的标准计算机语言，用来访问和操作数据库系统。sql语句用于取回和更新数据库中的数据。sql可与数据库程序协同工作，比如ms access、db2、informix、ms sql server、oracle、mysql、sybase以及其他数据库系统。
每一种数据库有自己版本的sql语言，但是为了与ansi标准相兼容，sql必须以相似的方式共同地来支持一些主要的关键词（比如 select、update、delete、insert、where等等）。
除了sql标准之外，大部分sql数据库程序都拥有自己的私有扩展。

2、sql语言分类

sql语言分为数据定义语言、数据控制语言、数据操作语言、数据查询语言，分别实现对数据库数据操作。

二、sql语言基础

1、数据定义语言（ddl）

ddl：data definition language
用于定义和管理数据对象，包括数据库、数据表、函数、视图、索引、触发器等。例如：create、drop、alter等语句。
create table student
(
sid int,
sname varchar(20)
);
alter table student add age int default 20;
drop table student;
drop database student;

2、数据控制语言（dcl）

dcl：data control language
用来管理数据库的语言，包含授权用户访问、拒绝用户访问、撤销授予的权限。例如：grant、deny、revoke、commit、rollback等语句。
创建用户
create user 'wang'@'localhost' identified by 'a1!';
权限设置
grant select on db.student to 'wang'@'localhost';
撤销权限
revoke select on db.student from 'wang'@'localhost';

3、数据操作语言（dml）

dml：data manipulation language
用于操作数据库对象中所包含的数据，增、删、改。例如：insert、delete、update语句。

4、数据查询语言（dql）

dql：data query language
用于查询数据库对象中所包含的数据，能够进行单表查询、连接查询、嵌套查询，以及集合查询等各种复杂程度不同的数据库查询，并将数据返回到客户机中显示。例如：select语句。

三、常量和变量

1、常量

a、字符常量
字符串常量使用单引号或双引号，数值常量不用加引号。
如果字符串常量中需要换行、有单引号、双引号 % \
前面需要加转义字符\
\n 换行
\’ 一个单引号
\” 一个双引号
\ 一个\ 如果没有转义字符 就认为\是一个转义字符
\% 一个% 如果没有转义字符就认为这是一个通配符
_ 一个 如果没有转移字符 就认为是一个通配符

select 'hanli\'gang001'
select 'han\nligang001'
select 'han\nligang001'
select "han\"ligang001"

select "韩立刚001"
b、数值常量
数值常量不用添加引号，
select 100+100+200
c、布尔常量
布尔型常量取值 true 和 false
在sql中使用1和0表示
select true,false

在表达式中
select 100>200
select 100<200

2、变量

用户自定义变量使用@开始，使用set给变量赋值。
set @name='孙悟空';
select @name;
select * from student;
insert into student values (6,'孙悟空',20)；
insert into student values (8,@name,20)；
set @sid=9,@nid=10
insert into student values (@sid,@name,20)；
select @sid+@nid；
set @sid=@sid+1；
select @sid；
set @sname3=(select sname from student where sid=9)；
select @sname3；

3、系统变量

系统变量分为全局系统变量和会话系统变量。
全局系统变量：针对所有默认设置
会话系统变量：针对当前用户生效，用户登录mysql会使用全局系统变量，如果会话中更改了变量值，使用更改后的值，不过只针对当前用户生效。
show variables 显示会话系统变量
show global variables 显示全局系统变量
show session variables 显示会话系统变量
show global variables like 'sql_select_limit'；使用通配符显示匹配的变量设置
show session variables like 'sql_select_limit'；系统变量使用@@标识
select @@global.sql_select_limit 查看某个全局系统变量设置
select @@session.sql_select_limit 查看某个会话系统变量设置
set @@session.sql_select_limit=2 设置会话系统变量
全局系统变量需要在/etc/my.cnf配置文件中修改。

四、运算符好表达式

1、算术运算符

算术运算符包括：加（+）、减（-）、乘（）、除（/）、取模（%）。
select 4+3,5-3,58,20/4;
select 17%5;
select sid,sname,age+2 from s;

2、比较运算符

比较运算符包括等于（=）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）、不等于（!=或<>）
select 10=10,10<10,10<>10;
select binary 'a'='a','a'<'b';比较字符的二级制
select from s where sid<=2;
select from s where sname='孙悟空';

3、逻辑运算符

逻辑运算符包括与（and或&&）、或（or或||）、非（not或!）。
逻辑运算符的应用举例
select from s where sname like '杨%' and age>40;
select from s where sname like '孙%' && age>30;
select from s where sname like '孙%' or sid<2;
select from s where sname like '孙%' || sid<2;
select from s where not sname like '孙%';
select from s where !(sname like '孙%');

4、运算符优先级

算术运算符优先级：先乘除，后加减，括号优先
逻辑运算符优先级：先与操作，再或操作，括号优先
sname like '韩%' and age>40 or sid<3;
sname like '韩%' and (age>40 or sid<3);

5、表达式

表达式是常量、变量、运算符、函数等的组合。
1+2;
‘a’<’a’;
select * from s where age+4>40;

五、系统内置函数

1、数学函数

abs(x)
返回x的绝对值
select * from student where abs(age-45)<=2
查找和45岁相差不超过两岁用户

ceil(x),ceiling(x)
返回大于或等于x的最小整数（向上取整）
floor(x)
返回小于或等于x的最大整数（向下取整）
select ceil(5.4),floor(5.6);
select sid,sname,floor(age/10)*10 from s;

rand()
返回0~1的随机数
select floor(rand()1000);
产生一个0-1000的随机整数
select ,rand() random from s order by random;
使用随机函数随机排序查询结果
rand(x)
返回0~1的随机数，种子x值相同时返回的随机数相同。

sign(x)
返回x的符号，x是负数、0、正数分别返回-1、0、1
pi()
返回圆周率
truncate(x,y)
返回数值x保留到小数点后y位的值
select sign(-4),sign(434);
select pi();
select truncate(4.5454,2);
select truncate(avg(age),2) from s;

round(x)
返回离x最近的整数（四舍五入）
round(x,y)
保留x小数点后y位的值，但截断时要四舍五入
pow(x,y),power(x,y)
返回x的y次方
sqrt(x)
返回x的平方根
exp(x)
返回自然常数e的x次方。

2、字符串函数

char_length(s)
返回字符串s的字符数
length(s)
返回字符串s的占用的空间的大小。
select * from student where length(sname)/char_length(sname)!=3
找到姓名中有英文字符的学生

concat(s1,s2,.....)
将字符串s1,s2等多个字符串合并为一个字符串
concat_ws(x,s1,s2,....)
同coucat(s1,s2,.....)，但是每个字符串之间要加上x
select concat('韩立刚','河北师大','网络工程师')
select concat('学号',sid,'姓名',sname,'年龄',age) from s
select concat_ws(' ','学号',sid,'姓名',sname,'年龄',age) from s

insert(s1,x,len,s2)
使用字符串s2替换s1的x位置开始长度为len的字符串
select insert('hanligang',4,2,'zhi')
select insert(sname,2,0,' ') from s

upper(s),ucase(s)
将字符串s的所有字符都变成大写字母
lower(s),lcase(s)
将字符串s的所有字符都变成小写字母
select upper('hanligang')
select lower('hanligang')
select upper(sname) from s

left(s,n)
返回字符串s的前n个字符
right(s,n)
返回字符串s的后n个字符
select left('孙悟空',1)
select right('孙悟空',2)
lpad(s1,len,s2)
字符串s2来填充s1的开始处，使字符串长度达到len
rpad(s1,len,s2)
字符串s2来填充s1的结尾处，使字符串长度达到len
select lpad('1',4,'0')
select lpad(sid,4,0) from s
select rpad('han',10,'b')

ltrim(s)
去掉字符串s开始处的空格
rtrim(s)
去掉字符串s结尾处的空格
trim(s)
去掉字符串s开始处和结尾处的空格
trim(s1 from s)
去掉字符串s中开始处和结尾处的字符串s1
用法示例：
select char_length(ltrim(' han '))
select rtrim(' han ')
select char_length(rtrim(' han '))
select char_length(trim(' han '))
insert into s values(10,' 张志敏',32)
select * from s
update s set sname=ltrim(sname)
select trim('h' from 'hanliganghhhh')

repeat(s,n)
将字符串s重复n次
space(n)
返回n个空格
replace(s,s1,s2)
用字符串s2代替字符串s中的字符串s1
用法示例：
select repeat('han',4)
select sid,concat(left(sname,1),space(sid),right(sname,2)) from s
select replace('han li gang',' ','_')
select replace('han li gang',' ','')

strcmp(s1,s2)
如果s1小于s2，strcmp函数返回一个小于零的值。如果s1大于s2，函数返回一个大于零的值。如果两个字符串相等，函数就返回零。
substring(s,n,len)
获取从字符串s中的第n个位置开始长度为len的字符串
position (s1 in s)
从字符串s中获取s1的开始位置
instr(s,s1)
从字符串s中获取s1的开始位置
应用实例：
select strcmp('han','wang')
select strcmp('hlg','han')
select strcmp('han','an')
select substring('[e-mail]',11,7)
select sid,substring(sname,2,1) from s
select substring('[e-mail]',position('@' in '[e-mail]')+1,11)
select right('[e-mail]',char_length('[e-mail]')-position('@' in '[e-mail]'))

alter table s add email char(40)
select * from s
update s set email='[e-mail]' where sid=1
update s set email='[e-mail]' where sid=2
update s set email='[e-mail]' where sid=3；获取email字段中@后面的字符串，先截取邮箱字段的长度，在截取邮箱的@字符的位置，计算出邮箱@后面有多少字符，
select right(email,char_length(email)-position('@' in email)) from s
select right(email,char_length(email)-instr(email,'@')) from s

3、日期和时间函数

linux命令行查看：
clock
显示硬件时间
date
显示系统时间
date -s 11/03/15
更改系统日期
date -s 9:21:4
更改系统时间
hwclock --systohc
使用系统时间覆盖硬件时间
hwclock --hctosys
使用硬件时间覆盖系统时间
mysql命令行查看：
curdate()
current_date()
获取系统当前日期
curtime()
current_time()
获取系统当前时间
current_timestamp()
localtime()
now()
获取系统当前日期和时间

应用实例：
给表s增加一列，数据类型timestamp 默认值是当前时间。
alter table s add stime timestamp default now(); 
select * from s
insert into s (sid,sname,age,email) values (11,'张东',34,'[e-mail]')
将以前用户的stime列更改为当前时间
update s set stime=now() where sid<11
返回日期d中的月份值，范围是1~12 
month(d)
返回日期d中的月份名称，如january
monthname(d)
返回日期d是星期几，如monday
dayname(d)
返回日期d是星期几，1表示星期日，2表示星期1
dayofweek(d)
返回日期d是星期几，0表示星期一,1表示星期2
weekday(d)
计算日期d是本年的第几个星期，范围是0-53
week(d)

计算日期d是本年的第几个星期，范围是1-53
weekofyear(d)
计算日期d是本年的第几天
dayofyear(d)
计算日期d是本月的第几天
dayofmonth(d)
返回日期d中的年份值
year(d)
返回日期d是第几季度，范围1-4
quarter(d)
返回时间t中的小时值
hour(t)
返回时间t中的分钟值
minute(t)
返回时间t中的秒钟值
second(t)

应用实例：
select concat(year(now()),'年',month(now()),'月',day(now()),'日')
select concat(year(stime),'年',month(stime),'月',day(stime),'日') from s

计算日期d1到d2之间相隔的天数
datediff(d1,d2)

计算开始日期d加上n天的日期
adddate(d,n)

计算起始日期d加上一个时间段后的日期
adddate(d, interval expr type)

计算起始时间t加上n秒的时间
addtime(t,n)

应用实例：
select datediff(now(),'2015-2-3')
select adddate(now(),-1050)
select adddate(now(),1050)

select adddate(now(), interval 1000000 second)
select adddate(now(), interval 1000000 hour)
select adddate(now(), interval -10000 day)
select adddate(now(), interval '10 3' day_hour)
select adddate(now(), interval 3 year)

4、系统信息函数

系统信息函数用来查询mysql数据库的系统信息。例如查询数据库的版本，查询数据的当前用户等。
version()
返回数据库的版本号
connection_id()
返回服务器的连接数，也就是到现在为止mysql服务的连接次数
database(),schema()
返回当前数据库名
user()
返回当前用户的名称
charset(str)
返回字符串str的字符集
collation(str)
返回字符串str的字符排列方式
last_insert_id()
返回最后生成的auto_increment值

5、加密解密函数

加密函数是mysql中用来对数据进行加密的函数。
password(str)
对字符串str进行加密
md5(str)
对字符串str进行md5加密
encode(str,pswd_str)
使用字符串pswd_str来加密字符串str，加密结果是一个二进制数，必须使用blob类型来保存
decode(crypt_str,pswd_str)
解密函数，使用字符串pswd_str来为crypt_str解密

应用实例：
create table webuser 
(
logon varchar(20),
pw varchar(100)
);
insert into webuser values ('hanligang',md5('abc'));
insert into webuser values ('zhangql',md5('123'));
select from webuser where logon='hanligang' and pw=md5('abc');
alter table webuser modify column pw blob(1);
insert into webuser values ('zhangjing',encode('abcd','91xueit'));
select logon,decode(pw,'91xueit') from webuser;
select from webuser where logon='zhangjing' and decode(pw,'91xueit')='abcd';

6、聚合函数

count() 
统计满足条件的记录数量
select count(*) from s
统计表中有多少条记录
select count(email) from s
select count(all email) from s
统计表中email列有多少条有值的记录，包括重复值
select count(distinct email) from s
统计表中email列有多少条有值的记录 消除重复值

sum() 统计总数
select sum(age) from s where email is not null

avg() 求平均值
select avg(age) from s where email is not null
max() 求最大值
min() 求最小值
select avg(age),max(age),min(age) from s

group_concat() 将满足条件的记录，显示成一行，使用逗号分开
select group_concat(sname) from s where sid<5

7、条件判断函数

if(value,t,f) 如果value是true t 如果是false 返回值f 
select sname,if(age>40,'中年','青年') as age from s

ifnull(value1,value2) 如果value1不为空，返回value1，如果value1为空，返回value2
select sname,ifnull(email,'没有邮箱') as 邮箱 from s

case when value1 then result1 else default end 
当value1值是true 返回result1，否则返回default值
select sname,case when age>40 then '中年' else '青年' end from s

case expr when value1 then ‘result1’ when value2 then ‘result2’ when value3 then ‘result3’ else ‘result4’ end
根据expr表达式的值，返回不同的值
select sname, case floor(age/10) when 2 then '壮年' when 3 then '青年' else '中年' end from s

六、mysql支持数据类型

1、数值类型
mysql的数值数据类型只要分为整数和浮点数。

mysql支持的5个主要整型是tinyint，smallint，mediumint，int和 bigint。
mysql 以一个可选的显示宽度指示器的形式对 sql 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 int(6)，就可以保证所包含数字少于 6个的值从数据库中检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。
如果对一个字段存储了一个超出许可范围的数字，mysql会根据允许范围最接近的一端截短后再进行存储。mysql会在不合规定的值插入表前自动修改为0。
unsigned 修饰符规定字段只保存正值。因为不需要保存数字的正、负符号，可以在储时节约一个“位”的空间。从而增大字段可以存储的值的范围。
zerofill修饰符规定0可以用来真补输出的值，可以阻止mysql数据库存储负值。
应用实例：
create table tint
(
tid tinyint,
tid2 tinyint unsigned,
tid3 int(5)
)
alter table tint modify column tid3 int(5) zerofill
insert into tint values (21,23,4)
insert into tint values (11,21,233322)
如果存储的数据溢出显示警告
insert into tint values (221,-23,4)
show warnings
查看当前操作的警告信息
float、double和decimal类型
mysql支持的三个浮点类型是float、double和decimal类型。float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，
附加参数是一个显示宽度指示器和一个小数点指示器。比如语句float(7,3)规定显示的值不会超过7位数字，小数点后面带有3位数字。
对于小数点后面的位数超过允许范围的值，mysq 会自动将它四舍五入为最接近它的值，再插入它。
decimal数据类型用于精度要求非常高的计算中，允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数，而计数方法表示小数点后数字的位数。比如语句decimal(7,3)规定了存储的值不会超过 7位数字，并且小数点后不超过3位。
忽略decimal数据类型的精度和计数方法修饰符将会使mysql数据库把所有标识为这个数据类型的字段精度设置为 10，计算方法设置为 0。
unsigned和zerofill修饰符也可以被float、double和decimal数据类型使用。

2、字符串类型

mysql提供了8个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。

char类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。大小修饰符的范围从0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。
char类型可以使用binary修饰符。当用于比较运算时，binary修饰符使char以二进制方式参于运算，而不是以传统的区分大小写的方式。
char类型的一个变体是varchar类型，是一种可变长度的字符串类型，并且也必须带有一个范围在0-255之间的指示器。char和varchgar不同之处在于mysql数据库处理范围指示器的方式：char把范围大小视为值的大小，在长度不足的情况下就用空格补足；而varchar类型把范围指示器视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。短于指示器长度的varchar类型不会被空格填补，但长于指示器的值仍然会被截短。
因为varchar类型可以根据实际内容动态改变存储值的长度，所以在不能确定字段需要多少字符时使用varchar类型可以大大地节约磁盘空间、提高存储效率。
varchar类型在使用binary修饰符时与char类型完全相同。
对于字段长度要求超过255个的情况下，mysql提供了text和blob两种类型。根据存储数据的大小，都有不同的子类型。大型的数据用于存储文本块或图像、声音文件等二进制数据类型。
text和blob类型在分类和比较上存在区别。blob类型区分大小写，而text 不区分大小写。大小修饰符不用于各种blob和text子类型。比指定类型支持的最大范围大的值将被自动截短。

3、日期和时间类型

在处理日期和时间类型的值时，mysql带有5个不同的数据类型可供选择。分为简单日期、时间类型，混合日期、时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且mysql带有内置功能可以把多样化的输入格式变为一个标准格式。

mysql用date和year类型存储简单的日期值，使用time类型存储时间值。日期、时间类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串，date类型的值应该使用连字号作为分隔符分开，而time类型的值使用冒号作为分隔符分开。没有冒号分隔符的time类型值，将会被mysql理解为持续的时间，而不是时间戳。
mysql还对日期的年份中的两个数字的值，或是sql语句中为year类型输入的两个数字进行最大限度的通译。因为所有year类型的值必须用4个数字存储。mysql试图将2个数字的年份转换为4个数字的值。把在00-69范围内的值转换到2000-2069范围内。把70-99范围内的值转换到1970-1979之内。如果 mysql自动转换后的值并不符合需要，请输入4个数字表示的年份。
除了日期和时间数据类型，mysql还支持datetime和timestamp混合类型，可以把日期和时间作为单个的值进行存储。通常用于自动存储包含当前日期和时间的时间戳，并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。
如果对timestamp类型的字段没有明确赋值，或是被赋与了null值。mysql 会自动使用系统当前的日期和时间来填充它。 
create table dd
(
d date,
t time,
y year,
dt datetime,
ts timestamp
)
insert into dd values ('2015-11-05','15:48:34','2015',
'2015-11-05 15:48:34','2015-11-05 15:48:34')
不满足时间格式的，插入后变成
insert into dd values ('201511-05','15:78:34','15',
'2015-11-05 154834','2015-11-05 15:48:34')

4、复合类型

mysql支持两种复合数据类型enum和set，属于扩展sql规范。一个enum 类型只允许从一个集合中取得一个值；而set类型允许从一个集合中取得任意多个值。 
enum类型
enum类型只允许在集合中取得一个值。enum类型字段可以从集合中取得一个值或使用null值，如果输入不在集合中的值将会使mysql插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，mysql会自动使用插入值的大小写转换成与集合中大小写一致的值。
enum类型在系统内部可以存储为数字，并且从1开始用数字做索引。一个 enum类型最多可以包含65536个元素，其中一个元素被mysql保留，用来存储错误信息，错误值用索引0或者一个空字符串表示。
mysql认为enum类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。通过搜索包含空字符串或对应数字索引为0的行就可以很容易地找到错误记录的位置。
set类型
set类型可以从预定义的集合中取得任意数量的值，任何试图在set类型字段中插入非预定义的值都会使mysql插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，mysql将会保留合法的元素，除去非法的元素。
一个set类型最多可以包含64项元素。在set元素中值被存储为一个分离的“位”序列，set类型中不可能包含两个相同的元素。
从set类型字段中找出非法的记录只需查找包含空字符串或二进制值为0的行。
create table dd
(
sname char(10),
sex enum('男','女'),
hobby set('游泳','旅游','打球')
);
insert into dd values ('韩立刚','男','游泳,打球');
insert into dd values ('张京','难','看电影,打球');
插入一条有错误的记录，sex列没有插入成功，hobby列只能插入set中列出的值

七、数据类型转换函数

类型转换函数主要是cast(xxx as 类型), convert(xxx,类型)。可以转换的类型是有限制的，包括二进制、字符型、日期、时间、日期时间型、浮点数、整数、无符号整数。
select cast('00033ad' as signed)
select convert(44.334,signed)
select concat(convert(44.334,char(10)),'3000')
类型自动推导