MySQL常用基礎語句

cmd登入

mysql -h localhost -u root -p

SHOW語句

返回可用資料庫列表
show databases;

返回當前選擇資料庫內可用表的列表
show tables；

顯示錶中的所有列（xxx：表名）
show columns  from xxx; or describe xxx;

顯示伺服器狀態資訊
show status;

顯示建立特定資料庫（xxx：資料庫名）
show create database xxx;

顯示建立特定表（xxx：表名）
show create table xxx;

顯示授予使用者（所有使用者或特定使用者）的安全許可權
show grants;

顯示伺服器錯誤資訊
show errors;

顯示伺服器警告資訊
show warnings;

關於選擇資料庫進行表的操作（xxx：資料庫名）
use xxx;

SELECT語句

檢索表中的列 SELECT column... FROM table

select username from user;
select username,age from user;
select * from user;

DISTINCT 去除列中的重複值（必須放在列名的前面）一般用來查詢不重複的欄位和條數(count(distinct username)) 如果查詢不重複的記錄用group by

select distinct addr from user;

需要過濾不止一條記錄
select distinct age,addr from user;
過濾表中age和addr都重複的記錄

LIMIT限制查詢

select username from user limit 2;

表示從第三列開始返回一行
select username from user limit 2,1;
select username from user limit 1 offset 2;

使用完全限定的表名和列名（user為表名 test為資料庫名）

select user.username from test.user

ORDER BY排序檢索資料（用非檢索的列排序資料是合法的）

select username from user order by username;

多個排序時按順序進行，如果username都是唯一，則按照age排序
select username,age from user order by username,age;

按指定排序方法 升序（ASC 預設）降序DESC（只用用到直接位於其前面的列名）
select username,age from user order by username,age;

使用order by和limit組合（order by必須位於from之後 limit必須位於order by之後）

eg：找出年齡最大的前三個人
select username,age from user order by age desc limit 3;

WHERE過濾資料

select username,age from user where age=20;
select username,age from user where addr='wuhan';

空值檢查 IS NULL

select username,age,addr from user where addr is null;

操作符 AND和OR（and優先順序高於or，所以組合使用時優先執行and）

select username,age,addr from user where age=20 and addr='beijing';
select username,age,addr from user where age=10 or addr='wuhan';
select username,age,addr from user where username='zhangsan' or addr='beijing' and age>10;

IN操作符（用來指定條件範圍）

select username,age from user where username in ('zhangsan','lisi');

in和or的功能相同，使用in有什麼好處

在使用長的合法選項清單時，in操作符的語法更清楚且更直觀
計算的次序更容易管理（使用的操作符更少）
一般情況下比or清單執行更快
可以包含其他select語句，使得更動態的建立where語句

NOT（否定它之後所跟的任何條件，mysql支援not對in，between和exists子句取反）

select username,age from user where username not in ('zhangsan','lisi');

百分號（%）萬用字元（可以區分大小寫，跟mysql的設定有關，預設是不區分） %不能匹配NULL
select username, age from user where username like 'z%';
select username, age from user where username like '%a%';
select username, age from user where addr like '%';

下劃線萬用字元只能匹配一個字元，功能與%萬用字元相同

select username,age from user where username like '_hansan'

萬用字元使用技巧

不要太依賴萬用字元。如果其他操作符能達到相同的目的，應該使用其他操作符
在確實需要使用萬用字元時，除非絕對有必要，否則不要把它們用在搜尋模式的開始處。把萬用字元置於搜尋模式的開始處，搜尋起來是最慢的
仔細注意萬用字元的位置。如果放錯地方，可能不會返回想要的資料

REGEXP正規表示式進行搜尋

select username,age from user where username regexp 'z';

BINARY 區分大小寫
select username,age from user where username regexp binary 'z';

'.'表示匹配任意一個字元
select username,age from user where addr regexp '.';

or匹配
select username,age from user where username regexp 'z|l';

[123]=[1|2|3]
select username,age from user where username regexp '[123] z';

集合中使用^匹配除這些字元以外的，否則指字串的開始處
select username,age from user where username regexp '^[123] z';

^字串的開始處
select username,age from user where username regexp '^[0~9]';

範圍匹配相當於[123456]
select username,age from user where username regexp '[1-6] z';

匹配特殊字元用\\
select username,age from user where username regexp '\\.'

CONCAT 拼接串

AS：別名
select concat(username,'(',age,')') AS info from user;

文書處理常式

select upper(username),age from user;

常用文書處理常式

函數	說明
Left()	返回串左邊的字元
Length()	返回串的長度
Locate()	找出串的一個子串
Lower()	將串轉換為小寫
LTrim()	去掉串左邊的空格
Right()	返回串右邊的字元
RTrim()	去掉串右邊的空格
Soundex()	返回串的SOUNDEX值根據發音字元和音節進行比較
SubString()	返回子串的字元
Upper()	將串轉換為大寫

日期與時間處理常式

按日查詢
select username,age,createdate from user where date(createdate)='2004-11-11';
按月查詢
select username, age, createdate from user where date_format(createdate,'%Y-%m') = '2004-11'; 
按年查詢
select username,age,createdate from user where date_format(createdate,'%Y')='2004'

數值處理常式

函數	說明
Abs()	返回一個數的絕對值
Cos()	返回一個角度的餘弦
Exp()	返回一個數的指數值
Mod()	返回除操作的餘數
Pi()	返回圓周率
Rand()	返回一個亂數
Sin()	返回一個角度的正弦
Sqrt()	返回一個數的平方根
Tan()	返回一個角度的正切

聚集函數

函數	說明
AVG()	返回某列的平均值忽略NULL
COUNT()	返回某列的行數如果為*則不忽略NULL，為列時忽略
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

資料分組 GROUP BY 和 HAVING
GROUP BY子句經常在聚合函數中使用，而HAVING配合GROUP BY使用

select count(age) AS a_age from user group by age;

select age,count(age) AS a_age from user group by age having count(age)>1; having：過濾分組

子句

子句	說明	是否必須使用
SELECT	要返回的列或表示式	是
FROM	從中檢索資料的表	僅在從表選擇資料時使用
WHERE	行級過濾	否
GROUP BY	分組說明	僅在按組計算聚集時使用
HAVING	組級過濾	否
ORDER BY	輸出順序排序	否
LIMIT	要檢索的行數	否

子查詢

select * from user where age=(select age from user_1 where addr='beijing');

聯結

聯結是一種機制，用來在一條SELECT語句中關聯表
內部聯結

select cust_name, cust_contact from customers, orders, orderitems where customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num AND prod_id = 'TNT2';

INNER JOIN ON

select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

INNER JOIN ON 連線三個資料表的用法：

SELECT * FROM (表1 INNER JOIN 表2 ON 表1.欄位號=表2.欄位號) INNER JOIN 表3 ON 表1.欄位號=表3.欄位號

INNER JOIN 連線四個資料表的用法：

SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.欄位號=表2.欄位號) INNER JOIN 表3 ON 表1.欄位號=表3.欄位號)

INNER JOIN 表4 ON Member.欄位號=表4.欄位號

INNER JOIN 連線五個資料表的用法：

SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.欄位號=表2.欄位號) INNER JOIN 表3 ON 表1.欄位號=表3.欄位號)

INNER JOIN 表4 ON Member.欄位號=表4.欄位號) INNER JOIN 表5 ON Member.欄位號=表5.欄位號

自聯結（相同的表查詢兩次）

select p1.prod_id, p1.prod_name from products AS p1, products AS p2 where p1.vend_id = p2.vend_id AND p2.vend_id = "DTNTR";

自然連結

排除多次出現，使每個列只返回一次

外部聯結LEFT | RIGHT OUTER JOIN ON 返回包括沒有的列

select vendors.vend_name, products.prod_name from vendors left outer join products ON vendors.vend_id = products.vend_id;

帶聚集函數的聯結
例：檢索所有客戶及每個客戶所下的訂單數

select customers.cust_name, customers.cust_id, count(orders.num) AS num_ord FROM customers inner join orders on customers.cust_id = orders.cust_id group by customers.cust_id;

組合函數 union

select vend_id, prod_id, prod_price from products where prod_price <= 5 union select vend_id, prod_id, prod_price from products where vend_id IN (1001, 1002);

UNION使用規則

必須由兩條或兩條以上的SELECT語句組成，語句之間用UNION分隔
每個查詢必須包含相同的列、表示式或聚集函數（次序可以不同）
列資料型別必須相容：型別不必完全相同，但必須是DBMS可以隱含地轉換型別
UNION預設自動取消重複的行，如果想返回所有匹配的行使用UNION ALL

插入資料 INSERT INTO VALUES

insert into user(username, age, addr) values ('zhangba', 17, 'shanghai');

插入多條
insert into user(username, age, addr) values ('zhangjiu', 18, 'shanghai'), ('lishi', 20, 'beijing'), ('wangyi', 17, 'shanghai');

LOW_PRIORITY 降低INSERT語句的優先順序
insert low_priority into user(username, age, addr) values ('zhangba', 17, 'shanghai');

插入資料INSERT INTO VALUES

insert into user(username,age,addr) values ('zhangsan',17,'beijing');

插入多條
insert into user(username,age,addr) values ('zhangjiu', 18, 'shanghai'), ('lishi', 20, 'beijing'), ('wangyi', 17, 'shanghai');

更新資料 UPDATE SET

update user SET age = 20 where username = 'zhangsi';

IGNORE 即使發生錯誤，也繼續進行更新
update ignore user set createdate = '2019-07-15' where addr = 'beijing';

刪除資料 DELETE FROM

delete FROM t_user where username = 'liujiu';

刪除表中所有資料，刪除原來的表並重新建立一個表，而delete是逐行刪除，比delete快
TRUNCATE TABLE user;

建立表

 create table if not exists tb_user(          
    -> id int auto_increment primary key,//自增id從0開始
    -> user varchar(15) not null,
    -> password varchar(15) not null);

修改表 ALTER TABLE

向表中新增欄位
alter table user add createdate datetime;

從表中刪除欄位
alter table user drop column createdate;

修改表中列的型別
alter table user modify createdate char(20);

修改表中列名
alter table user change createdate starttime datetime;

新增外來鍵
alter table user add constraint fk_t_user_test_one foreign KEY (外來鍵名) references t_new_user (主鍵名); 

on delete CASCADE on update CASCADE 聯合刪除 更新
alter table test_one add constraint fk_t_user_test_one foreign KEY (n_id) references t_new_user (n_id) on delete CASCADE on update CASCADE;

刪除外來鍵
alter table test_one drop FOREIGN KEY fk_t_user_test_one;

刪除表DROP TABLE

drop table user

重新命名錶RENAME TABLE 原表名 TO 新表名

rename table user to test;
alter table user rename test

檢視

建立檢視
create view v_getuser as select t_user.username, t_user.age from t_user;
select * from v_getuser where age > 20;

檢視建立檢視的語句
show create view v_getuser;

刪除檢視
drop view v_getuser;

更新檢視 也可先drop再create
create or replace view;

儲存過程

CREATE PROCEDURE getavg()
  BEGIN
    SELECT AVG(DISTINCT age) AS age_avg FROM t_user;
  END;

呼叫儲存過程
CALL getavg();

刪除儲存過程
DROP PROCEDURE IF EXISTS getavg;

顯示儲存過程的語句
SHOW CREATE PROCEDURE getavg;

顯示所有儲存過程
SHOW PROCEDURE STATUS;

過濾顯示所有儲存過程
SHOW PROCEDURE STATUS LIKE '%user%';

// 帶輸出引數
CREATE PROCEDURE procedure_age(OUT age_min DECIMAL, OUT age_max DECIMAL)
  BEGIN
    SELECT MIN(DISTINCT age) INTO age_min FROM t_user;
    SELECT MAX(DISTINCT age) INTO age_max FROM t_user;
  END;

CALL procedure_age(@ageMin, @ageMax);
SELECT @ageMin as minage, @ageMax as maxage;

遊標

CREATE PROCEDURE p_username()
  BEGIN
    -- 定義區域性變數
    DECLARE u CHAR(20);
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE cur_user CURSOR FOR SELECT username FROM user;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    -- 開啟遊標
    OPEN cur_user;
    --迴圈每一行
    REPEAT
    -- 檢索當前行的列
    FETCH cur_user INTO u;
      INSERT INTO p_users VALUES(u);
    --結束迴圈
    UNTIL done END REPEAT;
     -- 關閉遊標
    CLOSE cur_user;
  END;

觸發器

MySQL響應 DELETE、UPDATE 和 INSERT 而自動執行的一條MySQL語句（或位於 BEGIN 和 END 語句之間的一組語句）
只有表才支援觸發器，檢視不支援，臨時表也不支援

建立觸發器
CREATE TRIGGER testtrigger AFTER INSERT ON user FOR EACH ROW SELECT 'add';

刪除
DROP TRIGGER testtrigger;

insert觸發器
CREATE TRIGGER insertuser AFTER INSERT ON user FOR EACH ROW SELECT NEW.id INTO @insert_id;
INSERT INTO user(username, age, addr) VALUES('zhangyiyi', 23, 'tianjin');
SELECT @insert_id;

delete觸發器 
-- begin end 塊的好處是觸發器能容納多條sql語句
CREATE TRIGGER deleteuser BEFORE DELETE ON user FOR EACH ROW 
BEGIN
SELECT OLD.id INTO @delete_id;
END;

update觸發器 OLD虛擬表存取更新之前的值，NEW存取更新之後的值
CREATE TRIGGER updateuser BEFORE UPDATE ON user FOR EACH ROW SET NEW.username = UPPER(NEW.username);

事務

用來維護資料庫的完整性，保證成批的mysql操作要麼完全執行，要麼完全不執行

回滾ROLLBACK

SELECT * FROM user;
開啟事務
START TRANSACTION;
DELETE FROM user;
SELECT * FROM user;
回滾
ROLLBACK;
SELECT * FROM user;

提交COMMIT

開啟事務之後，只有都成功才會執行commit，出錯都會復原
START TRANSACTION;
DELETE FROM user WHERE id = 10;
DELETE FROM user WHERE id = 11;
提交
COMMIT;

保留點SAVEPOINT

START TRANSACTION;
INSERT INTO user(username, age, addr) VALUES ('zhangyier', 19, 'tianjin');
SAVEPOINT insesrt_user;
DELETE FROM user WHERE addr = 'tianjin';
ROLLBACK TO insesrt_user;

安全管理

USE mysql;
SELECT user FROM user;

建立賬號
CREATE USER zyw IDENTIFIED BY 'zywrxq1224';

重新命名
RENAME USER zyw TO zyw;

刪除賬號
DROP USER zyw1;

顯示賬號許可權
SHOW GRANTS FOR zyw;

授權
GRANT SELECT, INSERT ON test_daily.* TO zyw;

取消授權
REVOKE SELECT ON test_daily.* FROM zyw;

整個伺服器
GRANT ALL ON *.* TO zyw;
整個資料庫
GRANT ALL ON test_daily.* TO zyw;
整個表
GRANT ALL ON test_daily.t_user TO zyw;

修改賬號密碼
ALTER user 'root'@'localhost' IDENTIFIED BY '新密碼';

MYSQL語句

MySQL常用基礎語句