數據庫技能實戰進階之常用結構化sql語句（上）

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

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

  一、  create與drop 簡介

    首先create 與drop 是直接可以對數據庫進行操作，例如創建數據庫、刪除數據庫。簡單示例如下，

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

其次，create 和drop能對表進行操作，和對庫的操作一致 將database 更改為table，即可創建和刪除操作。后面我們要聯系增刪改查，所以創建以下表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。其實原理和表結構是一樣，只是呈現方式不一樣
   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 是我們將數據寫進數據庫最用的方法之一，常見的語法如下：

 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開始。

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)邏輯運算符
 &&，||，and,or ,in, between  and
3)比較運算符：
 =,!=,>=,<=,>,<,<>

五、更改數據或者表結構

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

基本的語法：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語句一般是先執行條件部分先鎖定行再鎖定列）
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） 查詢所有與查詢個別字段
  

 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）對空值的查詢
  雖然我們在創建表結構的時候一般設置 not null 但是也有些表會出現null的情況，例如我們將user表更改一下，表里面的數據如下：

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，字符串模式為" "

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的用戶，不區分大小寫
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 正則里面

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

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

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