技術文章 » MySQL資料庫增刪改查常用語句詳解

MySQL資料庫增刪改查常用語句詳解

2020-09-22 14:00:14

MySQL資料庫增刪改查常用語句詳解

一 MySQL資料庫表結構

資料以表格的形式出現，每行為單獨的一條記錄，每列為一個單獨的欄位，許多的記錄和欄位組成一張表單（table）若干的表單組成（database）。

1.1 常見資料型別

字串型別（CHAR（O-255固定長度）
VARCHAR（O-255可變長度））
數值型別（INT（整數型）、FLOAT（浮點型）)
日期和時間型別（DATE（年月日）、TIME（時分秒））

1.2 常用約束型別

約束是一種限制，它通過對錶的行或到的資料做出限制，來確保表的資料的完整性、唯一性。

主鍵約束 primary key:
主鍵約束相當於唯一約束+非空約束的組合，主鍵約束列不允許重複，也
不允許出現空值。每個表最多隻允許一個主鍵，建立主鍵約束可以在列級別建立，也可以在表級別建立。當建立主鍵的約束時，系統預設會在所在的列和列組合上建立對應的唯一索引。

外來鍵約束foreign key:
外來鍵約束是保證一個或兩個表之間的參照完整性，外來鍵是構建於一個表的
兩個欄位或是兩個表的兩個欄位之間的參照關係。

唯一約束unique:
唯一約束是指定table的列或列組合不能重複，保證資料的唯一性。唯一約束不允許出現重複的值，但是可以為多個null。同一個表可以有多個唯一約束，多個列組合的約束。在建立唯一約束時，如果不給唯一約束名稱，就預設和列名相同。唯一約束不僅可以在一個表內建立，而且可以同時多表建立組合唯一約束。

非空約束not null與預設值default:
非空約束用於確保當前列的值不為空值，非空約束只能出現在表物件的列上。Null型別特徵:所有的型別的值都可以是null，包括int、float等資料型別。

1.3 MySQL儲存引擎

儲存引擎就是儲存資料，建立索引，更新查詢資料等等技術的實現方式。儲存引擎是基於表的，而不是基於庫的。所以儲存引擎也可被稱為表型別。Oracle，SqlServer等資料庫只有一種儲存引擎。MySQL提供了外掛式的儲存引擎架構。所以MySQL存在多種儲存引擎，可以根據需要使用相應引擎，或者編寫儲存引擎。

MYISAM: 預設引擎、插入和查詢速度較快，支援全文索引，不支援事務、行級鎖和外來鍵約束等功能。
INNODB: 支援事務、行級鎖和外來鍵約束等功能。
MEMORY: 工作在記憶體中，通過雜湊欄位儲存資料，速度快、不能永久儲存資料。

二 MySQL前置基本操作

2.1 修改資料庫密碼

MySQL登入及退出命令:
設定密碼: mysqladmin -uroot password’123’

登入:mysql -u使用者名稱 -p密碼 -P埠 -S通訊端檔案
-p 使用者密碼.
-h 登陸位置（主機名或ip地址）
-P 埠號(3306改了就不是了）
-S 通訊端檔案（/var/lib/mysql/mysql.sock)
退出命令:exit或ctrl+d

2.1.1 建立登入使用者

mysql>create user zhangsan@'%'identified by'123';
%:指任意的遠端終端

2.1.2 給使用者授權登入

GRANT all ON *.* TO 'zhangsan'@'192.168.1.149' IDENTIFIED BY '123';

2.1.3 測試使用者登入

mysql -uzhangsan -p123 -h 192.168.1.149

2.1.4 修改使用者自身密碼

mysql>set password=password ('123456') ;

2.1.5 root使用者更改其他使用者密碼

mysql>set password for zhangsan'%'=password('123123');

2.1.6 root找回密碼及修改

關閉資料庫，修改主組態檔(/etc/my.cnf）新增: skip-grant-tables
systemctl stop mysqld 
#vim /etc/my.cnf
skip-grant-tables

啟動資料庫，空密碼登入並修改密碼
systemctl start mysqld
update mysql.user set password=password(‘新密碼’) where user='root';
或者 update mysql.user set authentication_string=password('123') where user='root';  ###centos7
刪除skip-grant-tables,重新啟動資料庫驗證新密碼

2.2 檢視資料庫結構

show databases;

2.3 建立和刪除資料庫

create database auth；
drop database auth;

2.4 使用資料庫

use auth;

2.5 檢視表

mysql> show tables;
+----------------+
| Tables_in_auth |
+----------------+
| users          |
+----------------+

2.6 檢視資料表的結構

mysql> describe users;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| user_name   | char(16) | NO   | PRI | NULL    |       |
| user_passwd | char(48) | YES  |     |         |       |
+-------------+----------+------+-----+---------+-------+

三 DML語句

對錶中資料進行管理

3.1 INSERT插入新資料

insert into users(user_name,user_passwd) values('peiqi',password('250'));
insert into users(user_name,user_passwd) values('qiaozhi','250');
insert into users values('hapi',password('360'));

3.2 UPDATE更新表中原有資料

update users set user_passwd=password('250') where user_name='qiaozhi';
update users set user_name='peihua' where user_name='qiaozhi';

直接修改資料庫來實現資料庫管理員密碼的修改

update mysql.user set authentication_string=password('123') where user_name='root';

3.3 DELETE刪除不需要的資料

DELETE刪除不需要的資料
delete from users where user_name='hapi';
刪除系統使用者
delete from mysql.user where user='yiqi';
刪除表中所有記錄
delete from auth.users;

四 DQL語句：資料查詢語句

4.1 基礎查詢語句

select * from auth.users where user_name='peiqi';		#查詢使用者名稱為佩奇的所有記錄資訊
select user_passwd from auth.users where user_name='peiqi';	#查詢使用者名稱為佩奇的密碼資訊

4.2 進階查詢語句

4.2.1準備工作：建立資料庫player，表player

create database player;
use player;
create table player (id int(4) not null,name varchar(10) not null,level int(3) not null,primary key (`id`));
insert into player (id,name,level) values ('30','搶寶真多呀',47);
insert into player (id,name,level) values ('15','新五皇·白鬍子',46);
insert into player (id,name,level) values ('63','新五皇–敬神',46);
insert into player (id,name,level) values ('199','D 丶狙擊王',46);
insert into player (id,name,level) values ('298','唐三',46);
insert into player (id,name,level) values ('51','新五皇·暴雪',45);
insert into player (id,name,level) values ('272','D 丶搶人頭輔助',45);

4.2.2 按等級降序查詢level大於45的記錄

mysql> select id,name,level from player where level>=45 order by level desc;
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 搶寶真多呀             |    47 |
|  15 | 新五皇·白鬍子          |    46 |
|  63 | 新五皇–敬神            |    46 |
| 199 | D 丶狙擊王            |    46 |
| 298 | 唐三                 |    46 |
|  51 | 新五皇·暴雪           |    45 |
| 272 | D 丶搶人頭輔助        |    45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)

4.2.3 ORDER BY 語句多欄位排序

查詢等級在 45 級及以上的使用者，並以 level 降序排列和 id 降序排列。

mysql> select id,name,level from player where level>=45 order by level desc,id desc;
+-----+----------------------+-------+
| id  | name                 | level |
+-----+----------------------+-------+
|  30 | 搶寶真多呀           |    47 |
| 298 | 唐三                 |    46 |
| 199 | D 丶狙擊王           |    46 |
|  63 | 新五皇–敬神          |    46 |
|  15 | 新五皇·白鬍子        |    46 |
| 272 | D 丶搶人頭輔助       |    45 |
|  51 | 新五皇·暴雪          |    45 |
+-----+----------------------+-------+
7 rows in set (0.00 sec)

注意！
ORDER BY 後面跟多個欄位時，欄位之間使用英文逗號隔開，
優先順序是按先後順序而定。下面以A和B分別表示兩個欄位。
ORDER BY A,B desc 指A用升序，B用降序；
ORDER BY A asc,B desc 指A用升序，B用降序；
ORDER BY A desc,B desc 指A用降序，B用降序；

4.2.4 GROUP BY語句

通過 SQL 查詢出來的結果，還可以對其進行分組，使用 GROUP BY 語句來實現。
GROUP BY 從字面上看，是以 BY 後面的內容對查詢出的資料進行分組，就是將一個「資料集」
劃分成若干個「小區域」，然後針對這些個「小區域」進行資料處理。
GROUP BY通常都是結合聚合函數一起使用的，常用的聚合函數包括：
計數（COUNT）、求和（SUM）、求平均數（AVG）、最大值（MAX）、最小值（MIN），
這些聚合函數的用法在後面函數小節會有更詳細的講解。GROUP BY 分組的時候可以按一個或多個欄位對結果進行分組處理。

mysql> select count(name),level from player where level>=45 group by level;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           2 |    45 |
|           4 |    46 |
|           1 |    47 |
+-------------+-------+
3 rows in set (0.00 sec)

mysql> select count(name),level from player where level>=45 group by name;
+-------------+-------+
| count(name) | level |
+-------------+-------+
|           1 |    45 |
|           1 |    46 |
|           1 |    46 |
|           1 |    47 |
|           1 |    45 |
|           1 |    46 |
|           1 |    46 |
+-------------+-------+
7 rows in set (0.00 sec)

4.2.5 限制結果條目

LIMIT 的第一個引數是位置偏移量（可選引數），是設定 MySQL 從哪一行開始顯示。
如果不設定第一個引數，將會從表中的第一條記錄開始顯示。需要注意的是，
第一條記錄的位置偏移量是 0，第二條是 1，以此類推。第二個引數是設定返回記錄行的最大數目。
插入新的欄位

insert into player (id,name,level) values ('1','修尤拉卡',10);
insert into player (id,name,level) values ('2','起風了',10);
insert into player (id,name,level) values ('3','吊打低V',15);
insert into player (id,name,level) values ('4','小花',14);
insert into player (id,name,level) values ('5','小舞',35);

mysql> select id,name,level from player limit 3;
+----+--------------+-------+
| id | name         | level |
+----+--------------+-------+
|  1 | 修尤拉卡     |    10 |
|  2 | 起風了       |    10 |
|  3 | 吊打低V      |    15 |
+----+--------------+-------+
3 rows in set (0.00 sec)

LIMIT 子句的使用也可以結合 ORDER BY：先進行排序，然後再LIMIT限制固定的記錄。
也就是說LIMIT是放在最後的，將處理好的結果集按要求選出幾行來。
例如，將查詢記錄按等級 level 降序排列，只取前三條記錄。

mysql> select id,name,level from player order by level desc limit 3;
+----+----------------------+-------+
| id | name                 | level |
+----+----------------------+-------+
| 30 | 搶寶真多呀           |    47 |
| 15 | 新五皇·白鬍子        |    46 |
| 63 | 新五皇–敬神          |    46 |
+----+----------------------+-------+
3 rows in set (0.00 sec)

在顯示結果的時候也可以不從第一行開始，引入 offset 引數。
例如，執行以下操作即可從第 3 條記錄開始顯示之後的 3 條資料。

mysql> select id,name,level from player limit 2,3;
+----+------------+-------+
| id | name       | level |
+----+------------+-------+
|  3 | 吊打低V    |    15 |
|  4 | 小花       |    14 |
|  5 | 小舞       |    35 |
+----+------------+-------+
3 rows in set (0.00 sec)

4.2.6 設定別名

在MySQL查詢時，當表的名字比較長或者表內某些欄位比較長時，為了方便書寫或者多次使用相同的表，可以給欄位列或表設定別名。使用的時候直接使用別名，簡潔明瞭，增強可讀性。設定別名使用AS語句。
在使用 AS 後，可以用 alias_name 代替 table_name，其中 AS 語句是可選的。AS之後的別名，主要是為表內的列或者表提供臨時的名稱，在查詢過程中使用，庫內實際的表名或欄位名是不會被改變的。
例如，在統計表內所有記錄共有多少條時，使用 count（*）， 這麼寫不便於識別，可以將其別名設定為 number。

mysql> select count(*) as number from player;
+--------+
| number |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

mysql> select count(*) number from player;		#省略as是一樣的結果
+--------+
| number |
+--------+
|     12 |
+--------+
1 row in set (0.00 sec)

此外，AS還可以作為連線語句的操作符。例如，執行以下操作即可實現用一條 SQL語句完成在建立表tmp的時候將player表內的資料寫入 tmp 表。

mysql> create table tmp as select * from player;
Query OK, 12 rows affected (0.02 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select count(*) from tmp;
+----------+
| count(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

4.2.7 萬用字元

萬用字元主要用於替換字串中的部分字元，通過部分字元的匹配將相關結果查詢出來。
通常萬用字元都是跟 LIKE 一起使用的，並協同WHERE子句共同來完成查詢任務。
常用的萬用字元有兩個，分別是：
%：百分號表示零個、一個或多個字元
_：下劃線表示單個字元

mysql> select id,name,level from player where name like 's%';		##name 欄位以 s 開頭的記錄
+------+--------------+-------+
| id   | name         | level |
+------+--------------+-------+
|  238 | sagou 轟總   |     7 |
|  795 | senoku       |    15 |
| 2460 | shirley      |     1 |
+------+--------------+-------+
3 rows in set (0.00 sec)

mysql> select id,name,level from player where name like '_uess';	#替換開頭的一個字元
+-----+-------+-------+
| id  | name  | level |
+-----+-------+-------+
| 713 | guess |    25 |
+-----+-------+-------+
1 row in set (0.00 sec)

mysql> select id,name,level from player where name like 'use____';		#替換結尾的四個字元
+-----+---------+-------+
| id  | name    | level |
+-----+---------+-------+
| 448 | useless |     1 |
+-----+---------+-------+
1 row in set (0.00 sec)

4.2.8 子查詢

子查詢也被稱作內查詢或者巢狀查詢，是指在一個查詢語句裡面還巢狀著另一個查詢語句子查詢語句是先於主查詢語句被執行的，其結果作為外層的條件返回給主查詢進行下一步的查詢過濾。子查詢不僅可以在 SELECT 語句中使用，在 INERT、UPDATE、DELETE中也同樣適用。在巢狀的時候，子查詢內部還可以再次巢狀新的子查詢，也就是說可以多層巢狀。

mysql>  select name,level from player where id in (select id from player where level>=45);
+----------------------+-------+
| name                 | level |
+----------------------+-------+
| 新五皇·白鬍子        |    46 |
| 搶寶真多呀           |    47 |
| 新五皇·暴雪          |    45 |
| 新五皇–敬神          |    46 |
| D 丶狙擊王           |    46 |
| D 丶搶人頭輔助       |    45 |
| 唐三                 |    46 |
+----------------------+-------+

五 DCL語句：設定使用者許可權

GRANT 許可權列表 ON 資料庫名.表名 TO 使用者名稱@來源地址 IDENTIFIED BY ‘密碼’

mysql> GRANT all ON *.* TO 'root'@'%' IDENTIFIED BY '123';

mysql> GRANT all ON *.* TO 'root'@'localhost' IDENTIFIED BY '123';

flush privileges;		#重新整理

使用者不存在時新建使用者

grant select on auth.* to 'yiqi'@'localhost' identified by '123';	#只授予查詢許可權

復原使用者的許可權

revoke all on auth.* from 'yiqi'@'20.0.0.1';

檢視使用者許可權

show grants for 'yiqi'@'20.0.0.1';

清空表資料

truncate table tmp;

在已有表中新增欄位

alter table users add wocao  varchar(50) not null;

遠端登入其他使用者的資料庫

mysql -ulph -p123 -h192.168.1.60

六 臨時表

CREATE TEMPORARY TABLE `mytmp` (`id` int(10) NOT NULL AUTO_INCREMENT,`NAME` varchar(32) CHARACTER SET utf8 COLLATE utF8_bin NOT
NULL, `level` int(10) NOT NULL,PRIMARY KEY (id) ) ENGINE InnoDB DEFAULT CHARSET=utf8;

七 克隆表

7.1 方法一：通過like方法，複製ky表生成test表

方法一：
mysql> create table test like ky;             
Query OK, 0 rows affected (0.01 sec)

mysql> show create table ky\G
*************************** 1. row ***************************
       Table: ky
Create Table: CREATE TABLE "ky" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from ky;           ##like方法複製表結構，不復制資料
Empty set (0.00 sec)

mysql> insert into test select * from users;      ##將ky表的資料寫入test表
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

7.2 方法二：通過建立表的方法克隆表

mysql> show create table ky\G         #獲取源表結構，，索引等資訊
*************************** 1. row ***************************
       Table: ky
Create Table: CREATE TABLE "ky" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  "level" char(16) NOT NULL,
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


CREATE TABLE "test3" (
  "user_name" char(16) NOT NULL,
  "user_passwd" char(48) DEFAULT '',
  "level" char(16) NOT NULL,
  PRIMARY KEY ("user_name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8;         #改名後建立與源表一樣的表結構


mysql> insert into test3 select * from ky;                          #匯入源表資料
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from test3;                                                 #檢視新建立表的資料
+-----------+-------------------------------------------+-------+
| user_name | user_passwd                               | level |
+-----------+-------------------------------------------+-------+
| lisi      | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 10    |
| lisi1     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 10    |
| lisi2     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 40    |
| lisi3     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 50    |
| lisi4     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 60    |
| lisi5     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 70    |
| lisi6     | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | 80    |
+-----------+-------------------------------------------+-------+