12.sql語句增删改查快速回顧

一、對資料庫進行操作（DDL語句）。

1.建立資料庫。

create database [if not exists] 資料庫名稱 character set 字元編碼；

例：建立一個資料庫，名字為db_1，字元編碼為utf8

create database if not exists db_1 character set utf8;

2.檢視資料庫。

2.1檢視所有資料庫：show databases；

2.2 檢視一個資料庫的建立方式：show create database 資料庫名稱；

例：

show create database db_1;

3.修改資料庫。

alter database db_name [character set xxx] 

4.切換目前操作的資料庫。

use 所要切換到的資料庫名稱。

例：use db_1

若要檢視目前在哪個資料庫下：

select database();

二、mysql中的資料類型。

MySQL支援多種類型，大緻可以分為三類：數值、日期/時間和字元串(字元)類型。

數值類型：

<a href="https://s4.51cto.com/wyfs02/M01/97/DC/wKiom1k0Gn2zlxGKAARiHZvuIm8342.png" target="_blank"></a>

日期和時間類型：

<a href="https://s4.51cto.com/wyfs02/M02/97/DC/wKiom1k0GyzwULxGAAHkv3dPG9Y002.png" target="_blank"></a>

字元串類型：

<a href="https://s1.51cto.com/wyfs02/M00/97/DC/wKiom1k0HGDz_6JYAAK-lMI4M-M273.png" target="_blank"></a>

CHAR和VARCHAR類型類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。

BINARY和VARBINARY類類似于CHAR和VARCHAR，不同的是它們包含二進制字元串而不要非二進制字元串。也就是說，它們包含位元組字元串而不是字元字元串。

BLOB是一個二進制大對象，可以容納可變數量的資料。有4種BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們隻是可容納值的最大長度不同。

有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB類型，有相同的最大長度和存儲需求。

三、資料庫中的表操作。

1.在資料庫中建立表。

create table 表名 （字段以及每個字段的屬性，每個字段以逗号分隔）

create table tb1( id int primary key auto_increment ,name char(20),gender bit default 1, birthday date,entry_date date,job char(20),salary double(4,2) unsigned, resume text );

！！這裡的primary key 屬性，就是主鍵了，那麼什麼是主鍵呢？

主鍵有兩個特點，就是非空并且值唯一。

auto_increment這個屬性是自增的意思，當主鍵字段是數字類型時可以使用，它可以讓數值自增。

2.檢視資料庫中某個表的結構以及資訊。

2.1檢視一張表的表結構：desc tab_name;

例如：

desc tb1;

2.2 檢視目前資料庫中所有的表：

show tables;

2.3 檢視建立表時使用的sql語句：

show create table 表名;

show create table tb1 \G;

3.修改表結構。

3.1 給表增加一個字段：

格式：  alter table tab_name add [column] 列名 類型［完整性限制條件］［first｜after 字段名］;

#其中的first和after是可選參數（可以不用，這兩個參數，如果不寫，預設添加的字段是在最後面）

#first是指添加到第一個字段的位置。

#after 是添加到某個字段的後面。

alter table user add addr varchar(20) not null unique first/after username

3.2 添加多個字段的示例：

alter table users2 

            add addr varchar(20),

            add age  int first,

            add birth varchar(20) after name;

3.3 修改表中某個字段的類型：

 alter table tab_name modify 列名 類型 [完整性限制條件]［first｜after 字段名］;

alter table users2 modify age tinyint default 20;

alter table users2 modify age int  after id;

修改表中字段的名字：

 alter table tab_name change [column] 列名 新列名 類型 [完整性限制條件]［first｜after 字段名］;

 alter table users2 change age Age int default 28 first;

3.4删除一個字段：

alter table tab_name drop [column] 列名;

3.5修改一個表名：

rename table 表名 to 新表名;

3.6修改表字元集：

alter table student character set utf8;

3.7 删除整張表：

drop table 表名;

3.8 為表中字段增加主鍵限制：

alter table tab_name add primary key(字段名稱,...)

3.9 删除字段中的主鍵限制：

先删除自增長在删除主鍵

Alter table tb1 change id id int(11);//删除自增長

Alter table tb1 drop primary key;//删除主建

3.10 添加唯一索引：

格式：

alter table tab_name add unique [index|key] [索引名稱](字段名稱,...) 

alter table users add unique(name)

alter table users add unique key user_name(name);

3.11 添加聯合索引：

alter table users add unique index name_age(name,age);#show create table users;

3.12 删除唯一索引：

alter table tab_name drop {index|key} index_name;

四.對表中的記錄進行操作。

insert 在表中插入記錄：

格式：insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......)

insert into employee_new (id,name,birthday) values(1,'ayumi','1978-10-02');

同時插入多條資料：

insert into employee_new values   (4,'alvin1','1993-04-20'),   (5,'alvin2','1995-05-12);

以鍵值對的方式插入資料：

insert ［into］ tab_name set 字段名=值

insert into employee_new set id=130,name="bitch jolin"

    2. update 修改表中記錄：

    格式：

    update tab_name set field1=value1,field2=value2,......[where 語句]

     例:

    update employee_new set birthday="1995-09-05" WHERE id=1;

  #PDATE文法可以用新值更新原有表行中的各列。

 #SET子句訓示要修改哪些列和要給予哪些值。

 #WHERE子句指定應更新哪些行。如沒有WHERE子句，則更新所有的行.

另外一個用法示例：

 --- 将suhaozhi的薪水在原有基礎上增加130元。

    update employee_new set salary=salary+130 where name='suhaozhi';

3.delete 删除表中的一個記錄：

delete from tab_name [where ....]

#delete語句如果不跟where語句則删除整張表中的資料

#delete隻能用來删除一行記錄

#delete語句隻能删除表中的内容，不能删除表本身，想要删除表，用drop。

#TRUNCATE TABLE也可以删除表中的所有資料，詞語句首先摧毀表，再建立表。此種方式删除的資料不能在事務中恢複。

删除tb1這個表中，name字段為suhaozhi的記錄。

delete from tb1 where name='suhaozhi';

删除表中的所有記錄：

delete from tb1;

使用truncate删除表中記錄:

truncate table tb1;

4.select 查詢語句：

select 查詢語句的基本格式：

   SELECT *|field1,filed2 ...   FROM tab_name

                  WHERE 條件

                  GROUP BY field

                  HAVING 篩選

                  ORDER BY field

                  LIMIT 限制條數

下面是關于select 查詢語句的示例：

create table tb2( id int primary key auto_increment, name varchar(20), yuwen DOUBLE, shuxue DOUBLE, yingyu double );

insert into tb2 values( 1,"aaa",98,98,99), (2,"bbb",35,98,66), (3,"ccc",59,59,62), (4,"ddd",88,89,87), (5,"eee",100,65,88);

現在有一張成績表，表裡有五條記錄：

select * from tb2;

+----+------+-------+--------+--------+

| id | name | yuwen | shuxue | yingyu |

|  1 | aaa  |    98 |     98 |     99 |

|  2 | bbb  |    35 |     98 |     66 |

|  3 | ccc  |    59 |     59 |     62 |

|  4 | ddd  |    88 |     89 |     87 |

|  5 | eee  |   100 |     65 |     88 |

5 rows in set (0.03 sec)

檢視整張表：

檢視name字段為ddd的國文和數學成績：

select yuwen,shuxue from tb2 where name="ddd";

列出所有學員的國文和英語的成績：

select yuwen,yingyu from tb2;

#其中from指定從哪張表篩選，*表示查找所有列，也可以指定一個列

#表明确指定要查找的列，distinct用來剔除重複行。

select 與數學計算表達式與as别名：

select name,yuwen+shuxue+yingyu from tb2; #顯示每個學生，國文數學英語的總和。

+------+---------------------+

| name | yuwen+shuxue+yingyu |

| aaa  |                 295 |

| bbb  |                 199 |

| ccc  |                 180 |

| ddd  |                 264 |

| eee  |                 253 |

使用字段别名後：

select name as '姓名' ,yuwen+shuxue+yingyu as '總成績' from tb2;

+--------+-----------+

| 姓名   | 總成績    |

| aaa    |       295 |

| bbb    |       199 |

| ccc    |       180 |

| ddd    |       264 |

| eee    |       253 |

使用where語句，進行查詢過濾：

例1：找出英語成績大于90分的學員：

select * from tb2 where yingyu &gt; 90;

例2：找出總成績大于200分的所有學員的名字和成績：

 select name as '姓名' ,yuwen+shuxue+yingyu as '總成績' from tb2 where yuwen+shuxue+yingyu &gt; 200;

where語句中可以使用的比較運算符補充：

 &gt; &lt; &gt;= &lt;= &lt;&gt; !=

between 80 and 100 值在10到20之間

in(80,90,100) 值是10或20或30

like 'yuan%'

like 模糊比對

在多個條件直接可以使用邏輯運算符 and or not

例1：顯示出國文成績在70~100分之間的學員姓名以及國文成績。

select name,yuwen from tb2 where yuwen between 70 and 100;

+------+-------+

| name | yuwen |

| aaa  |    98 |

| ddd  |    88 |

| eee  |   100 |

例2：顯示出數學成績為98，65，59的學員分之間的學員姓名以及數學成績。

select name,shuxue from tb2 where shuxue in (98,65,59);

+------+--------+

| name | shuxue |

| aaa  |     98 |

| bbb  |     98 |

| ccc  |     59 |

| eee  |     65 |

例3：顯示所有name為a開頭的成績資訊。

select * from tb2 where name like 'a%';

例4：找出所有國文成績和數學成績都小于90分的學員資訊。

select * from tb2 where yuwen &lt; 90 and shuxue &lt; 90;

例5：找出成績為空的學員。

select name from tb2 where yuwen is null

!排序：Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 語句後指定的别名。

基本格式：

select *|field1,field2... from tab_name order by field [Asc|Desc]

#Asc 升序、Desc 降序，其中asc(升序)為預設值 ORDER BY 子句應位于SELECT語句的結尾。

例：以數學成績為基準排列。

select * from tb2 order by shuxue;

例2：把所有學生的總成績，從高到低輸出：

select name as '姓名',yuwen+shuxue+yingyu as '總成績' from tb2 order by  總成績 desc;

分組查詢：

示例準備：

create table test_menu( id int primary key auto_increment, product_name varchar(20), price float(6,2), born_date DATE, class varchar(20) );

INSERT INTO test_menu (product_name,price,born_date,class) VALUES

                                             ("蘋果",20,20170612,"水果"),

                                             ("香蕉",80,20170602,"水果"),

                                             ("水壺",120,20170612,"電器"),

                                             ("被罩",70,20170612,"床上用品"),

                                             ("音響",420,20170612,"電器"),

                                             ("床單",55,20170612,"床上用品"),

                                             ("草莓",34,20170612,"水果");

#注意,按分組條件分組後每一組隻會顯示第一條記錄.

#group by字句，其後可以接多個列名，也可以跟having子句,對group by 的結果進行篩選。

現在有一張表：

+----+--------------+--------+------------+--------------+

| id | product_name | price  | born_date  | class        |

|  1 | 蘋果         |  20.00 | 2017-06-12 | 水果         |

|  2 | 香蕉         |  80.00 | 2017-06-02 | 水果         |

|  3 | 水壺         | 120.00 | 2017-06-12 | 電器         |

|  4 | 被罩         |  70.00 | 2017-06-12 | 床上用品     |

|  5 | 音響         | 420.00 | 2017-06-12 | 電器         |

|  6 | 床單         |  55.00 | 2017-06-12 | 床上用品     |

|  7 | 草莓         |  34.00 | 2017-06-12 | 水果         |

按照字段的位置進行分組：

select * from test_menu group by 5;

#這個5代表了目前表從左數第五個字段。

#從左數第5個字段是class，按分組條件分組後每一組隻會顯示第一條記錄。

對購物表按類名class字段分組後顯示每一組商品的價格總和:

select class,sum(price) from test_menu group by class;

+--------------+------------+

| class        | sum(price) |

| 床上用品     |     125.00 |

| 水果         |     134.00 |

| 電器         |     540.00 |

對購物表按類名class字段分組後顯示每一組商品價格總和超過150的商品。

select class,sum(price) from test_menu group by class having sum(price) &gt; 150;

+--------+------------+

| class  | sum(price) |

| 電器   |     540.00 |

#!!!! 在這裡特别強調一點！having和where雖然都可以對查詢結果做過濾！！但是它們是有差別的！

where隻能用于分組前的篩選！！ 而having則可以用于分組後的篩選！這是因為having可以使用聚合函數！！where中不可以！！！！！！這點牢記！

常用的聚合函數補充：

一般情況下，聚合函數會配合分組查詢去使用。

#把要求的内容查出來再包上聚合函數即可。

count 統計行個數。

例1：統計這張表一共有多少行記錄

 select count(*) from test_menu;

例2：統計數學成績大于70的有多少條記錄。

select count(*) from tb2 where shuxue &gt; 70;

例3：統計總分大于200的記錄有多少？

select count(*) from tb2 where yuwen+shuxue+yingyu &gt; 200;

    2.sum 統計滿足條件的行的和。

    例1：統計這個班的國文總成績。

    select sum(yuwen) as '國文總成績' from tb2;

 +-----------------+

| 國文總成績      |

+-----------------+

|             380 |

    例2：統計這個班各科的總成績。

    select sum(yuwen),sum(shuxue),sum(yingyu) from tb2;

    例3：求班級國文成績的平均值。

    select sum(yuwen)/count(*) from tb2;

  3.avg求平均值。

  例1: 求班級國文成績的平均值。

   select avg(yuwen) from tb2;

  例2： 求班級總和的平均分。

    select avg(yuwen+shuxue+yingyu) from tb2;

4.max&amp;min 求最高值或者最低值。

 例：

select max(yuwen+shuxue+yingyu) from tb2;

select min(yuwen+shuxue+yingyu) from tb2;

5.ifnull 将空值轉換為指定的值。

注意！！！！！null 和所有的數計算都是null，是以需要用ifnull将null轉換為0！！！

例：ifnull(yuwen,0)

6.limit 指定查找出記錄的數量。

select * from tb2 limit 1; #隻顯示出結果的第一行。

select * from tb2 limit 2,5; #跳過前兩行，顯示接下來的後5行。

7.REGEXP 使用正規表達式進行查詢。

 select * from tb2 where name regexp '^a';

      本文轉自蘇浩智 51CTO部落格，原文連結：http://blog.51cto.com/suhaozhi/1932638，如需轉載請自行聯系原作者