數(shù)據(jù)庫技能實戰(zhàn)進階之常用結構化sql語句（上）

      常用的結構化查詢語言主要分為數(shù)據(jù)定義語言（DDL）、數(shù)據(jù)操作語言（DML）、數(shù)據(jù)控制語言（DCL）和數(shù)據(jù)查詢語言（DQL）。特別在關系型的數(shù)據(jù)庫例如（mysql、mariadb、 percona、DB2、Oracle、SQL server）等都是采用共同的SQL語句來實現(xiàn)增刪改查等數(shù)據(jù)的管理。本文會針對以下的四種類型的結構化SQL來進行介紹。

DDL 數(shù)據(jù)定義語言  create     drop     alter
DML 數(shù)據(jù)操作語言  insert    delete   update
DCL 數(shù)據(jù)控制語言  grant    commit   rollback
DQL 數(shù)據(jù)查詢語言  select

  一、  create與drop 簡介

    首先create 與drop 是直接可以對數(shù)據(jù)庫進行操作，例如創(chuàng)建數(shù)據(jù)庫、刪除數(shù)據(jù)庫。簡單示例如下，

CREATE  DATABASE  IF  NOT EXISTS   DBMS  default  character set utf8; 
#創(chuàng)建一個名為DBMS默認字符集為UTF8的數(shù)據(jù)庫
SHOW databases; #顯示當前數(shù)據(jù)庫管理系統(tǒng)里面所有的數(shù)據(jù)庫，就可以看到我們當前創(chuàng)建的DBMS庫
Drop  database  DBMS; #刪除名為DBMS的數(shù)據(jù)庫

其次，create 和drop能對表進行操作，和對庫的操作一致 將database 更改為table，即可創(chuàng)建和刪除操作。后面我們要聯(lián)系增刪改查，所以創(chuàng)建以下表SQL如下：

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。其實原理和表結構是一樣，只是呈現(xiàn)方式不一樣
   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

二、插入數(shù)據(jù) insert

    insert 是我們將數(shù)據(jù)寫進數(shù)據(jù)庫最用的方法之一，常見的語法如下：

 insert  into  tablename  (COLUMN ) VALUEs( );
 
 #tablename 是我們要插入數(shù)據(jù)的目標表名，column是列名，values里面對應的是要插入的值，需要強
 調的當我們對所有列插入即values（）中對應列與創(chuàng)建表結構的列順序對應時，可以省略(COLUMN )
 insert  into  tablename   VALUEs('','','' );
 例如：
 insert  into  user    VALUEs(1,'linux','linux' );但是給id設置了主鍵自增所以就沒必須做，
 使用上面的列名對應就可以了
 優(yōu)化后： insert  into  user (username,password )   VALUE('python','java' )

 #批量插入數(shù)據(jù)
 我們還可以用 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是×××，也會插入成功，這里是將×××轉換成字符串類型。

     將數(shù)據(jù)插入到數(shù)據(jù)庫的方法除了insert外，還有l(wèi)oad data infile （加載文件），以及從其他表查詢插入的方法，可以參考之前的文章，甚至可以用navicat之類客戶端工具導入。

三、刪除數(shù)據(jù)

   刪除數(shù)據(jù)可以分為刪除部分和刪除全部，刪除全部里面又可以分為只刪除數(shù)據(jù)和表和數(shù)據(jù)一起刪除，刪除表和數(shù)據(jù) ，可以使用上面提到的drop table  tablename ，刪除全部數(shù)據(jù)可以用delete   from tablename   或者tuncate  table。

    delete   from table  和tuncate table 的區(qū)別：

    delete  刪除后不會刷新主鍵的值  例如你刪除主鍵ID（3-5），那么下次插入數(shù)據(jù)就是從5開始。

delete     from user  #刪除所有數(shù)據(jù)
  insert  into  user (username,password )   VALUE('pythonlinux','javadocker' ),('LInuxmysql','redis')
select  *  from  user;#插入數(shù)據(jù)再查詢
查詢所有數(shù)據(jù)，檢查我們的id:
select   * from user 
11    python    java
12    LInuxmysql    777
13    pythonlinux    javadocker
14    LInuxmysql    redis
 觀察表里面的數(shù)據(jù)，可以發(fā)現(xiàn)第一行的id是從11開始的，這就說明delete刪除數(shù)據(jù)的時候不會刷新自增值
 truncate  user 再插入數(shù)據(jù)觀察
 truncate  user#清空所有數(shù)據(jù)
 insert  into  user (username,password )   VALUE('pythonlinux','javadocker' ),('LInuxmysql','redis')
select  *  from  user;#再插入數(shù)據(jù)，再查詢
1    pythonlinux    javadocker
2    LInuxmysql    redis
 truncate 清空表后 再插入數(shù)據(jù)，自增列從0開始。
刪除部分數(shù)據(jù)先插入多條，檢查插入后的數(shù)據(jù)，
 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; 然后再插入數(shù)據(jù)，查詢結果如下：
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不連續(xù)。

四、條件判斷與限制

  條件判斷主要根據(jù)數(shù)學運算符、邏輯運算符以及比較運算符等完成。

1) 數(shù)學運算符:
  +，-，*，% 
2)邏輯運算符
 &&，||，and,or ,in, between  and
3)比較運算符：
 =,!=,>=,<=,>,<,<>

五、更改數(shù)據(jù)或者表結構

   數(shù)據(jù)的更改使用update完成，而表結構的更改時依靠alter完成，接下來先介紹update更改數(shù)據(jù)，update和delete一樣，在條件限制不夠嚴密的情況下會導致數(shù)據(jù)全部改寫，是比較危險的操作，所以一定要細心。

基本的語法：update  table   set column=" "  where  判斷條件;

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語句一般是先執(zhí)行條件部分先鎖定行再鎖定列）
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

六、查詢數(shù)據(jù)

  查詢數(shù)據(jù)的sql是我們使用頻率最高的sql,而這些SQL的優(yōu)化程度代表著一個人對數(shù)據(jù)庫的熟悉程度，所以我們在查詢部分會涉及到很多的知識點。

1） 查詢所有與查詢個別字段
  

 select username,password  form  user;

 2）對部分列起別名

select  username as loginname  form  user;
  select  username  loginame ,password  from user; # 注意將新的列名和舊列名空格隔開

 
 3）去掉重復值 distinct

 select distinct  username from  user;
 select   distinct  username  loginame ,password  from user;

 4）使用where條件查詢
 

select * from user  where  **** ;

 
 5）對空值的查詢
  雖然我們在創(chuàng)建表結構的時候一般設置 not null 但是也有些表會出現(xiàn)null的情況，例如我們將user表更改一下，表里面的數(shù)據(jù)如下：

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值還有‘ ’值，我們現(xiàn)在研究一下為NULL和為' '的查詢,對于NULL一般不用，×××默認為0，字符串模式為" "

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匹配任意多個字符  _  下劃線匹配一個字符

select * from user where username like '%linux%'  #查詢用戶中包含linux的用戶，不區(qū)分大小寫
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是非常牛的一個數(shù)據(jù)庫，不僅函數(shù)多，而且支持正則表達式，接下來我們研究一下正則表達式的匹配。 regexp  效率會比like差一點

regexp 正則里面

select * from user where username regexp '^linux'  #以linux開頭的
結果：不區(qū)分大小寫
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

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方式

     常用的結構化查詢語上上篇內容基本結束，在下篇中會從續(xù)查詢部分內容，完成我們常使用的結構化查詢語句的介紹，由于本人水平有限，內容缺乏精彩片段，但是愿意和各位朋友一起分享學習，請各位朋友多指導！