数据库技能实战进阶之常用结构化sql语句(上)

  1. 云栖社区>
  2. 博客>
  3. 正文

数据库技能实战进阶之常用结构化sql语句(上)

技术小阿哥 2017-11-27 15:53:00 浏览601
展开阅读全文

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

网友评论

登录后评论
0/500
评论
技术小阿哥
+ 关注