常用的结构化查询语言主要分为数据定义语言(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,如需转载请自行联系原作者
网友评论