快速學習MySQLSQL語句

須知：

SQL語言：結構化查詢語言，是關係型資料庫查詢語言的標準，不同的資料庫雖然有自己私有擴充套件，但關鍵詞都支援：（select、update、delete、insert、where）

SQL語句分類：像Oracle、MSSQL都是通用的

DDL：資料定義語言（create、alter、drop、rename）

DML：資料庫維護語言（select、insert、update、delete）

DCL：資料庫控制語言，許可權（Grant、revoke）

TCL：事務控制語言（commt、sarepqint）：新型的語句

一、資料庫增刪改查

根據這個表結構以下來操作:

mysql> select * from user;

+—-+———-+——+

| id | name     | pass |

+—-+———-+——+

基本操作

1.建立資料庫

create database name;

2.刪除資料庫

drop database name;

3.建立一個使用者表

create table user(id int,name varchar(30),pass varchar(30));

4.插入資料

insert into user(id,name,pass) values(“1″,”zhangsan”,”123″);

5.刪除一個表

drop table user;

6.檢視錶欄位

desc table user;

7.檢視錶資料

select * from user;

8.修改表名

rename table user to tab1;

9.更新資料

update user set pass=”newpass” where pass=123;  #把密碼更新為newpass

update user set id=10,name=`lisi` where id=1;   #把id記錄的name更新為id10和name為lisi

10.重新命名欄位名

alter table user change pass newname varchar(30); 

11.表中新增一個欄位

alter table user add age int;

12.修改欄位

alter table user modify age int no null default 20; #當年齡輸入為空時，預設為20歲

13.刪除表中的一個欄位

alter table user drop age;

14.刪除一條記錄

delete from user where id=1;

15.查詢一條記錄

select id,name,pass from user where id=1;

16.刪除ID記錄

delete from user where id>=3 id<=5;             #刪除大於3小於5的id

delete from user where id in(1,3,5);            #刪除1,3,5的id

delete from user where id=1 or id=3 or id=5;       #刪除1,3,5的id

delete from user where id between 1 and 5;       #刪除1至5的id

高階查詢

1.返回結果刪除重複項

select distinct id from user;

2.查詢欄位中為NULL

select * from where pass is null;  

select * from where pass is not null; #查詢不為NULL的

3.like模糊查詢,包含zhang的列出來

select * from user where name like `%zhang%`;

select * from user where name like `%zhang%` or name like `%li%`;#查詢包含zhang或li的列出來

也可以使用正規表示式查詢,生產環境一般不用的，因為查詢慢，效果一樣

select * from user where name regexp `li`;
4.使用order by對查詢結果排序(升序/降序)，預設是升序

select id,name,pass from user order by id asc/desc;

5.使用limit取出排名前三個

select * from user  order by id desc limit 3;

6.concat函式使用-字串連線符

mysql> select id,name,pass,concat(id,`_`,name) idname from user;

+—-+———-+——+————+

| id | name     | pass | idname     |

+—-+———-+——+————+

|  1 | zhangsan | 123  | 1_zhangsan |

|  2 | lisi     | 123  | 2_lisi     |

|  3 | zhaowu   | 123  | 3_zhaowu   |

+—-+———-+——+————+

7.rand函式隨機排序，如隨機抽取前三名

select * from user order by rand() limit 3;

8.count統計,如統計多少個id記錄

select count(*) count from user; 

#count為自定義顯示查詢結果後欄位名，*為mysql優化後的查詢方法，要比直接寫入id效率高

select count(id) from user where name=`zhangsan`; #統計zhangsan多少條記錄

9.sum求和，如統計同消費了多少錢

select sum(id) from user where name=`lisi`;  #所有id數加在一起

10.avg平均數,如求班級平均分

select avg(id) from user;  

11.max最大值，如得到一個最高分

select max(id) from user;

12.min最小值

select min(id) from user;

13.group by分組聚合

select name,count(id) from user group by name order by desc;  

#使用count來聚合，基於name分組，再order by排序下（一般排名都使用分組聚合）

select name,count(id) count from user group by name having count>=3;

#查詢前三名，having是對分組的結果進行篩選，這不能用where，count代表查詢結果後顯示的欄位名

14.普通多表查詢（前提：兩個表必須有關係）

先建立兩個有關係的表：

create table user(id int unsigned auto_increment primary key,name varchar(30),age int);

create table post(id int unsigned auto_increment primary key,uid int,title varchar(200),content text);

結果一：查詢使用者發的所有記錄

select user.name,post.title,post.content from user,post where user.id=post.uid;

結果二：統計每個使用者有多少個記錄

select user.name,post.title,post.content count(id) from user,post where user.id=post.uid group by user.name;

15.聯表查詢（查詢成績）

mysql> select * from tb1;

+——+———-+

| id   | name     |

+——+———-+

|    1 | zhangsan |

|    2 | lisi     |

+——+———-+

mysql> select * from tb2;

+——+——-+

| id   | score |

+——+——-+

|    1 |    80 |

|    2 |    81 |

+——+——-+

mysql> select tb1.name,tb2.score from tb1,tb2 where tb1.id = tb2.id;

+———-+——-+

| name     | score |

+———-+——-+

| zhangsan |    80 |

| lisi     |    81 |

+———-+——-+

二、表欄位型別

1.數值

int(size)：整型，只能存整數數字，不能為空，預設允許輸入null，也可以設定不允許寫（not null）

float：浮點型，可以寫入整數或浮點數

1.1欄位屬性

unsigned：無符號，全是整數

zerofill：與長度無關，不夠3位時前面補0，預設看不見

null與not null：允許輸入null和不允許輸入

default：不允許null情況下，當輸入空時，則使用預設值

auto_increment：一般自增ID

1.2示例

例如，設定id為自增：

create table user(id int unsigned auto_increment primary key,name varchar(30),pass varchar(30));

primary key：有auto_increment必須設定為主鍵索引，提供查詢速度，再插入就不用寫id列了，如果你寫了，就以你寫的為準，繼續自增。

例如，建立一個表，當輸入性別為空時，預設則為男：

create table user2(id int unsigned auto_increment primary key,name varchar(30),sex varchar(5) not null default “nan”);

檢視欄位資訊：

mysql> desc user2;

+——-+——————+——+—–+———+—————-+

| Field | Type             | Null | Key | Default | Extra          |

+——-+——————+——+—–+———+—————-+

| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

| name  | varchar(30)      | YES  |     | NULL    |                |

| sex   | varchar(5)       | NO   |     | nan     |                |

+——-+——————+——+—–+———+—————-+

3 rows in set (0.00 sec)

2.字串

char(size）：佔用size的位元組，但查詢速度快，最大支援255個字元

varchar(size)：存多少佔多少，剩點空間，最大支援65535個字元

text：支援65535位元組

longtext：支援42億位元組

3.日期型別（有的會使用int型別來存時間戳）

以下是官方說法佔用的位元組，但實際會有不符。

date：年月日，佔用3個位元組

time：時分秒，佔用3個位元組

datetime：年月日時分秒，佔用8個位元組

year：年，佔用1個位元組

三、使用者管理

1.設定Mysql密碼

方法一：set password=password（`newpass`）;

方法二：update user set password=password（`newpass`） where user=`root`;

方法三：grant all on *.* to `root`@`localhost` identified by `newpass`;

再重新整理：flush privileges;

2.建立使用者並授權

grant all privileges on *.* to `user`@`localhost` identified by `pass`;

*.*：對應的是資料庫/表的許可權

localhost：只允許本地訪問，也可以設定%為所有訪問

3.撤銷使用者許可權

revoke all privileges on *.* from `root`@`localhost` identified by `pass`;

四、資料庫字符集

1.檢視伺服器基本資訊s

可以看到四種預設字符集是latin1，修改四種預設字符集，client和connection兩個要是一樣的。

set character_set_server=utf8;

set character_set_database=utf8;

set character_set_client=utf8;

set character_set_connection=utf8;

也可以修改my.cnf預設字符集

[mysqld]

character_set_server=utf8

[mysql]

default-character-set=utf8

重啟mysql！

2.檢視建立資料庫時使用的字符集

show create database mydb;

五、建立表欄位索引

1.主鍵索引

 1.1建立一個主鍵索引，一般建立表時已經設定

 alter table user add primary key(id);

 1.2刪除一個主鍵索引

 alter table user drop primary key;

2.普通索引

 2.1建立一個普通索引

 alter table user add index index_name(name);

 2.2刪除一個普通索引

 alter table user drop index index_name;

 2.3檢視主鍵索引和普通索引

 show index from user;

 show keys from user;

注：修改欄位名不影響索引。

3.檢視錶中建立的索引

mysql> desc user2;

+——-+——————+——+—–+———+—————-+

| Field | Type             | Null | Key | Default | Extra          |

+——-+——————+——+—–+———+—————-+

| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |

| name  | varchar(30)      | YES  | MUL | NULL    |                |

| pass  | varchar(30)      | YES  |     | NULL    |                |

+——-+——————+——+—–+———+—————-+

3 rows in set (0.00 sec)

上面key中的PRI代表是主鍵索引，MUL代表是普通索引。

4.檢視索引資訊

mysql> show index from user2;

+——-+————+————+————–+————-+———–+————-+———-+——–+——+————+———+—————+

| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+——-+————+————+————–+————-+———–+————-+———-+——–+——+————+———+—————+

| user2 |          0 | PRIMARY    |            1 | id          | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |

| user2 |          1 | index_name |            1 | name        | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |

+——-+————+————+————–+————-+———–+————-+———-+——–+——+————+———+—————+

2 rows in set (0.00 sec)

可以看到user表，做了PRIMARY主鍵索引和index_name名字的普通索引。

5.測試是否使用索引

mysql> desc select id,name from user2 where name=`zhangsan`;

+—-+————-+——-+——+—————+————+———+——-+——+————————–+

| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra                    |

+—-+————-+——-+——+—————+————+———+——-+——+————————–+

|  1 | SIMPLE      | user2 | ref  | index_name    | index_name | 33      | const |    1 | Using where; Using index |

+—-+————-+——-+——+—————+————+———+——-+——+————————–+

1 row in set (0.00 sec)

用desc檢測語句，可以看到查詢一條張三的記錄，使用了普通索引index_name這個名字，檢索了一行(rows)就找到了這條記錄。