常用的结构化查询语言主要分为数据定义语言（DDL）、数据操作语言（DML）、数据控制语言（DCL）和数据查询语言（DQL）。特别在关系型的数据库例如（mysql、mariadb、 percona、DB2、Oracle、SQL server）等都是采用共同的SQL语句来实现增删改查等数据的管理。本文会针对以下的四种类型的结构化SQL来进行介绍。
|
1
2
3
4
|
DDL 数据定义语言
create
drop
alter
DML 数据操作语言
insert
delete
update
DCL 数据控制语言
grant
commit
rollback
DQL 数据查询语言
select
|
一、 create与drop 简介
首先create 与drop 是直接可以对数据库进行操作，例如创建数据库、删除数据库。简单示例如下，
|
1
2
3
4
|
CREATE
DATABASE
IF
NOT
EXISTS DBMS
default
character
set
utf8;
#创建一个名为DBMS默认字符集为UTF8的数据库
SHOW databases; #显示当前数据库管理系统里面所有的数据库，就可以看到我们当前创建的DBMS库
Drop
database
DBMS; #删除名为DBMS的数据库
|
其次，create 和drop能对表进行操作，和对库的操作一致 将database 更改为table，即可创建和删除操作。后面我们要联系增删改查，所以创建以下表SQL如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
create
table
if
not
exists
user
(
id
int
unsigned
not
null
auto_increment
primary
key
,
username
varchar
(30)
not
null
,
password
varchar
(30)
not
null
);
或者使用下方SQL。其实原理和表结构是一样，只是呈现方式不一样
CREATE
TABLE
if
not
exists `
user
` (
`id`
int
(10) unsigned
NOT
NULL
AUTO_INCREMENT,
`username`
varchar
(30)
NOT
NULL
,
`
password
`
varchar
(30)
NOT
NULL
,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB
DEFAULT
CHARSET=utf8
|
二、插入数据 insert
insert 是我们将数据写进数据库最用的方法之一，常见的语法如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
insert
into
tablename (
COLUMN
)
VALUEs
( );
#tablename 是我们要插入数据的目标表名，
column
是列名，
values
里面对应的是要插入的值，需要强
调的当我们对所有列插入即
values
（）中对应列与创建表结构的列顺序对应时，可以省略(
COLUMN
)
insert
into
tablename
VALUEs
(
''
,
''
,
''
);
例如：
insert
into
user
VALUEs
(1,
'linux'
,
'linux'
);但是给id设置了主键自增所以就没必须做，
使用上面的列名对应就可以了
优化后：
insert
into
user
(username,
password
) VALUE(
'python'
,
'java'
)
#批量插入数据
我们还可以用
insert
into
tablename (
COLUMN
)
VALUEs
( 值1)，（值2）...的方式批量插
入效率高很多。
isert
into
user
(username,
password
)
VALUEs
(
'python999'
,334989 ),(
'LInuxmysql'
,
'777'
)
或者
insert
into
user
(username,
password
) VALUE(
'python'
,
'java'
),(
'LInuxmysql'
,
'777'
)
注意：经测试
values
和value插入值时一样，需要注意的是，定义的密码是
varchar
()字符类型，
而3334989是整形，也会插入成功，这里是将整形转换成字符串类型。
|
将数据插入到数据库的方法除了insert外，还有load data infile （加载文件），以及从其他表查询插入的方法，可以参考之前的文章，甚至可以用navicat之类客户端工具导入。
三、删除数据
删除数据可以分为删除部分和删除全部，删除全部里面又可以分为只删除数据和表和数据一起删除，删除表和数据 ，可以使用上面提到的drop table tablename ，删除全部数据可以用delete from tablename 或者tuncate table。
delete from table 和tuncate table 的区别：
delete 删除后不会刷新主键的值 例如你删除主键ID（3-5），那么下次插入数据就是从5开始。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
delete
from
user
#删除所有数据
insert into user (username,password ) VALUE(
'pythonlinux'
,
'javadocker'
),(
'LInuxmysql'
,
'redis'
)
select
*
from
user;
#插入数据再查询
查询所有数据，检查我们的
id
:
select
*
from
user
11
python java
12
LInuxmysql
777
13
pythonlinux javadocker
14
LInuxmysql redis
观察表里面的数据，可以发现第一行的
id
是从
11
开始的，这就说明delete删除数据的时候不会刷新自增值
truncate user 再插入数据观察
truncate user
#清空所有数据
insert into user (username,password ) VALUE(
'pythonlinux'
,
'javadocker'
),(
'LInuxmysql'
,
'redis'
)
select
*
from
user;
#再插入数据，再查询
1
pythonlinux javadocker
2
LInuxmysql redis
truncate 清空表后 再插入数据，自增列从
0
开始。
删除部分数据先插入多条，检查插入后的数据，
insert into user (username,password ) VALUE(
'python'
,
'javadocker'
),(
'LInux'
,
'redis'
),(
'linux'
,
999
)
,(
'redis'
,
'name '
)
1
pythonlinux javadocker
2
LInuxmysql redis
3
python javadocker
4
LInux redis
5
linux
999
6
redis name
7
python javadocker
8
LInux redis
9
linux
999
10
redis name
11
python javadocker
12
LInux redis
13
linux
999
14
redis name
删除
id
>
10
的部分
delete
from
user where
id
>
10
; 然后再插入数据，查询结果如下：
1
pythonlinux javadocker
2
LInuxmysql redis
3
python javadocker
4
LInux redis
5
linux
999
6
redis name
7
python javadocker
8
LInux redis
9
linux
999
10
redis name
15
python javadocker
16
LInux redis
17
linux
999
18
redis name
与上面的结果相比
id
不连续。
|
四、条件判断与限制
条件判断主要根据数学运算符、逻辑运算符以及比较运算符等完成。
|
1
2
3
4
5
6
|
1) 数学运算符:
+，-，*，%
2)逻辑运算符
&&，||，
and
,
or
,
in
,
between
and
3)比较运算符：
=,!=,>=,<=,>,<,<>
|
五、更改数据或者表结构
数据的更改使用update完成，而表结构的更改时依靠alter完成，接下来先介绍update更改数据，update和delete一样，在条件限制不够严密的情况下会导致数据全部改写，是比较危险的操作，所以一定要细心。
基本的语法：update table set column=" " where 判断条件;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
1 pythonlinux javadocker
2 LInuxmysql redis
3 python javadocker
4 LInux redis
5 linux 999
6 redis
name
7 python javadocker
8 LInux redis
9 linux 999
10 redis
name
15 python javadocker
16 LInux redis
17 linux 999
18 redis
name
UPDATE
user
set
username=
'kailinux'
where
id
in
(1,7,9)
更改后：（sql语句一般是先执行条件部分先锁定行再锁定列）
1 kailinux javadocker
2 LInuxmysql redis
3 python javadocker
4 LInux redis
5 linux 999
6 redis
name
7 kailinux javadocker
8 LInux redis
9 kailinux 999
10 redis
name
15 python javadocker
16 LInux redis
17 linux 999
18 redis
name
|
六、查询数据
查询数据的sql是我们使用频率最高的sql,而这些SQL的优化程度代表着一个人对数据库的熟悉程度，所以我们在查询部分会涉及到很多的知识点。
1） 查询所有与查询个别字段
|
1
|
select
username,
password
form
user
;
|
2）对部分列起别名
|
1
2
|
select
username
as
loginname form
user
;
select
username loginame ,
password
from
user
; # 注意将新的列名和旧列名空格隔开
|
3）去掉重复值 distinct
|
1
2
|
select
distinct
username
from
user
;
select
distinct
username loginame ,
password
from
user
;
|
4）使用where条件查询
|
1
|
select
*
from
user
where
**** ;
|
5）对空值的查询
虽然我们在创建表结构的时候一般设置 not null 但是也有些表会出现null的情况，例如我们将user表更改一下，表里面的数据如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
1 kailinux javadocker
2 LInuxmysql
NULL
NULL
为字符
NULL
3 python
NULL
4 LInux redis
5 linux 0
6 redis
name
7 kailinux javadocker
8 LInux redis
9 kailinux #值为
' '
10 redis
name
15 python javadocker
16 LInux #值为
' '
17 linux 999
18 redis
name
19 ubuntu #值为默认的
NULL
20 centos #值为默认的
NULL
|
经分析得，表里面不仅有NULL值还有‘ ’值，我们现在研究一下为NULL和为' '的查询,对于NULL一般不用，整形默认为0，字符串模式为" "
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
select
*
from
user
where
password
=
' '
;
结果
id username
password
9 kailinux
16 LInux
#查找
password
列为
NULL
的方法
select
*
from
user
where
password
=
NULL
结果为空
select
*
from
user
where
password
=
'NULL'
结果为：
2 LInuxmysql
NULL
3 python
NULL
select
*
from
user
where
password
is
NULL
;
结果为：
19 ubuntu
20 centos
所以当我们要查询默认为
NULL
的时，需要用
is
NULL
查询
|
6）条件判断 in和 between...and...
where 条件判断
7）like 模糊查询
like中 %p匹配任意多个字符 _ 下划线匹配一个字符
|
1
2
3
4
5
6
7
8
9
10
|
select
*
from
user
where
username
like
'%linux%'
#查询用户中包含linux的用户，不区分大小写
1 kailinux javadocker
2 LInuxmysql
NULL
4 LInux redis
5 linux 0
7 kailinux javadocker
8 LInux redis
9 kailinux
16 LInux
17 linux 999
|
8）使用正则表达式
mysql是非常牛的一个数据库，不仅函数多，而且支持正则表达式，接下来我们研究一下正则表达式的匹配。 regexp 效率会比like差一点
regexp 正则里面
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
select
*
from
user
where
username regexp
'^linux'
#以linux开头的
结果：不区分大小写
2 LInuxmysql
NULL
4 LInux redis
5 linux 0
8 LInux redis
16 LInux
17 linux 999
select
*
from
user
where
username regexp
'python|redis'
; #用户名中包含redis和python的
3 python
NULL
6 redis
name
10 redis
name
15 python javadocker
18 redis
name
21 PYTHonmysql
22 rediswinner
23 PYthonmysql
24 winnerredis
select
*
from
user
where
username regexp
' python$|redis$|234$'
; 查询以python，redis
234结尾的用户名
6 redis
name
10 redis
name
18 redis
name
24 winnerredis
25 PYTHoREDIS
27 PYthon234
28 winner1234
|
9 对查询结果排序 order by desc asc
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
select
*
from
user
order
by
id; #默认升序
1 kailinux javadocker
2 LInuxmysql
NULL
3 python
NULL
4 LInux redis
5 linux 0
....... #部分结果省略
23 PYthonmysql
24 winnerredis
25 PYTHoREDIS
26 redispyhon
27 PYthon234
28 winner1234
select
*
from
user
order
by
id
desc
; #降序排列
desc
降序
28 winner1234
27 PYthon234
26 redispyhon
25 PYTHoREDIS
....... #部分结果省略
8 LInux
7 kailinux
6 redis
5 linux
4 LInux
3 python
2 LInuxmysql
1 kailinux
select
*
from
user
order
by
id
asc
;
结果：
1 kailinux javadocker
2 LInuxmysql
NULL
3 python
NULL
4 LInux redis
5 linux 0
....... #部分结果省略
23 PYthonmysql
24 winnerredis
25 PYTHoREDIS
26 redispyhon
27 PYthon234
28 winner1234
默认排序方式为
asc
方式
|
常用的结构化查询语上上篇内容基本结束，在下篇中会从续查询部分内容，完成我们常使用的结构化查询语句的介绍，由于本人水平有限，内容缺乏精彩片段，但是愿意和各位朋友一起分享学习，请各位朋友多指导！
本文转自 tianya1993 51CTO博客，原文链接：http://blog.51cto.com/dreamlinux/1908264，如需转载请自行联系原作者
