一、資料庫基本操作

1.1 檢視已存在資料庫

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| fy                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
#大部分SQL操作命令必須以；結束

1.1.1 mysql預設4個資料庫

● information_schema：定義訪問資料庫後設資料的方式。資料庫名和表名，列的資料型別、訪問許可權等。
● mysql：核心資料庫，負責儲存資料庫使用者、許可權、關鍵字等使用者需要使用的控制和管理資訊。
● performance_schema：資料庫的效能引數，儲存引擎等。
● sys：sys系統庫下包含許多檢視，它們以各種方式對performance_schema表進行聚合計算展示。

1.2 檢視當前資料庫中有哪些表

mysql> use mysql;
mysql> show tables;

1.3 檢視錶的結構

mysql> use mysql;
mysql> describe user;#user為表

1.4 SQL資料庫語音的分類

● DDL（Data Definition Language，資料定義語音）：用來建立資料庫、資料庫物件和定義欄位，如CREATE、ALTER、DROP。
● DML（Data Manipulation Language，資料操縱語言）：用來插入、刪除和修改資料庫中的資料，如INSERT、UPDATE、DELETE。
● DQL（Data Query Language，資料查詢語言）：用來查詢資料庫中的資料，如SELECT。
● DCL（Data Control Language，資料控制語言）：用來控制資料庫元件的存取許可、存取許可權等，如COMMIT、ROLLBACK、GRANT、REVOKE。

1.4.1 DDL（建立資料庫和表）

1.建立新的資料庫

mysql> create database auth;#新建庫auth

2.建立新的表

#CREATE TABLE 表名（欄位1名稱型別，欄位2名稱型別，...，PRIMARY KEY（主鍵名））
mysql> create table users (user_name char(16) not null, user_passwd char(48)default '', primary key(user_name));

欄位名稱、型別、約束解釋：
欄位1名稱：屬性名稱，自定義
欄位1型別：int（4） 整型 代表0000-9999
double 浮點型
decimal（5,2）有效數字是5位，小數點後面保留2位 100.00；099.50
float 單精度浮點 4位元組
char 字元
char （10）固定長度字串，字串要用單引號引起來
varchar（50）可變長度字串
欄位1約束：
非空約束：內容不允許為空 not null
主鍵約束：非空且唯一 標識 primary key（主鍵）
預設約束：假如沒有填資料，預設預先設定的值填寫 default ‘未知’
自增特性：id 1 2 3 4 auto_increment（自動增長）
儲存引擎：myisam innodb
字符集：UTF-8

3.刪除一個資料表

mysql> drop table users;

4.刪除一個資料庫

mysql> drop database auth;

1.4.2 DML（管理資料表中的資料）

1.插入資料記錄

#INSERT INTO 表名（欄位1，欄位2，...） VALUES（欄位1的值，欄位2 的值，...）
mysql> use auth;
mysql> insert into users(user_name,user_passwd) values('zhangsan', password ('123456'));

2.查詢資料記錄

#select 欄位名1，欄位名2，...from 表名 where 條件表示式
mysql> mysqlt * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select user_name,user_passwd from auth.users where user_name='zhangsan';
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)

3.修改資料記錄

#update 表名 set 欄位名 1=欄位值 1 [，欄位名 2=欄位值 2] where 條件表示式
mysql> update auth.users set user_passwd=password('') where user_name='lisi';
mysql> select * from auth.users;

4.修改資料庫密碼

mysql> update mysql.user set authentication_string=password('123457') where user='root';
[root@server1 ~]# mysqladmin -u root -p'123457' password '123456'

5.刪除資料庫記錄
● delete from 表名 where 條件表示式

mysql> delete from auth.users where user_name='lisi';
mysql> select * from auth.users;

1.5 資料庫高階操作

1.5.1 清空表

● delete from 語句，可以刪除表內的資料，除此之外還可以使用truncate table 語句實現清空表內記錄。delete from 語句可以使用where子句對刪除的結果集進行過濾選擇，這樣更方便、更靈活。truncate table語句是刪除表中所有記錄資料，沒法定製，靈活性稍差。

mysql> create table tmp like player;#通過like方法，複製player表生成tmp表
mysql> insert into tmp select * from player;#通過player表生成tmp表內資料記錄
mysql> delete from tmp where level >= 45;
mysql> delete from tmp;
mysql> insert into tmp select * from player;
mysql> truncate table tmp;
mysql> select count(*) from tmp;

1.5.2 臨時表

● 是臨時建立的表，並不會長期存在，主要用於儲存一些臨時資料。臨時表有個特性，就是隻在當前連線可見，當前連線下可執行增刪改查等操作，當連線被關閉後，臨時表就會被MySQL刪除，相關的資源也會被釋放。

mysql> select * from mytmp;#檢視mytmp表是否存在
mysql> 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=lnnoDB default charset=utf8;
mysql> insert into mytmp(name,level) values('aa',10);
mysql> select * from mytmp;#退出前
mysql> quit
mysql> select * from mytmp;#退出後

1.5.3 克隆表

mysql> drop table tmp;
mysql> create table tmp as select * from player;

方法一：通過like方式克隆表

mysql> create table test like mytmp;#通過like方法，複製mytmp表生成test表
mysql> show create table test\g
mysql> select * from test;
mysql> insert into test select * from mytmp;#將mytmp表的資料寫入test表
mysql> select * from test;

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

mysql> show create table mytmp\g
mysql> create table `test` (`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;
#改名後建立新表
mysql> select * from mytmp;

二、資料庫使用者授權

● grant：當使用者已存在時，直接提權。
● 當使用者不存在時，先建立使用者，再提權。
● revoke：只撤銷許可權，不刪除使用者

2.1 授予許可權

● grant 許可權列表 on 資料庫名.表名 to 使用者名稱@來源地址 [ identified by ‘密碼’ ]

mysql> grant select on auth.* to ' xiaoqi'@'localhost' identified by '123456';
[root@server1 ~]# mysql -u xiaoqi -p

2.2 檢視許可權

● show grants for 使用者名稱@來源地址

mysql> show grants; ##檢視當前使用者的許可權
mysql> show grants for xiaoqi@localhost; ##檢視從本地登入的xiaoqi使用者的許可權
mysql> select user from mysql.user; ##檢視當前系統中的使用者

2.3 撤銷使用者許可權

● revoke 許可權列表 on 資料庫名. 表名 from 使用者名稱@來源地址

mysql> revoke all privileges on auth.* from ‘xiaoqi’@‘localhost’;
mysql> show grants for 'xiaoqi'@'localhost';#確認撤銷對auth庫的許可權