MySQL常用文法

標籤：

建立資料庫：CREATE DATABASE mysql_db;
刪除資料庫：DROP DATABASE mysql_db;
查看資料庫：SHOW DATABASES;
使用資料庫：USE mysql_db;
查看資料庫中的表:SHOW TABLES;

建立表：
CREATE TABLE user(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) UNIQUE NOT NULL,
sex ENUM(‘1‘,‘2‘,‘3‘) NOT NULL DEFAULT 3
);
查看錶結構：①SELECT COLUMN FROM user;
　　　　　　②DESC user;

查看錶建立語句(可查看各種自動產生的名字)：SHOW CREATE TABLE user;
插入表記錄：
①INSERT INTO user(id,username,sex) VALUES(1,‘Tom‘,1);
②INSERT user(username) VALUES (‘John‘);
尋找表：SELECT * FROM user;
刪除表中的資料(保留表結構)：TRUNCATE TABLE user;
將查詢結果寫入新表:
INSERT [INTO] privinces(sex) SELECT sex FROM user GROUP BY sex;

(若表中有資料，請不要隨意更改表名、列名)
修改表名：
①ALTER TABLE user RENAME [AS|TO] users;
②RENAME TABLE user TO users;
添加單列：
ALTER TABLE user 
 ADD [COLUMN] age SMALLINT NOT NULL UNSIGNED DEFUALT 18
 [FIRST | AFTER sex];
刪除列：ALTER TABLE user DROP sex[,DROP age];
修改列名稱和定義：
ALTER TABLE user
 CHANGE [COLUMN] age a_ge TINYINT NOT NULL UNSIGNED AFTER id;

單表更新：UPDATE user SET age = age + 5,sex = 1 [WHERE id = 2];
單表刪除：DELETE FROM user [WHERE id =3];
單(多)表串連：
SELECT p.userid,p.username FROM user AS p LEFT JOIN user AS s 
 ON p.userid = s.age;
單(多)表刪除：
DELETE * FROM user [AS] u1 LEFT JOIN (
 SELECT p.userid FROM user AS p LEFT JOIN user AS s
 ON p.userid =s.age GROUP BY p.userid HAVING COUNT(s.age)>1 ) [AS] u2
 ON u1.userid = u2.age
WHERE u1.userid > u2.userid;

尋找記錄：
SELETE select_expr [,select_expr]
 [
 FROM table_references
 [WHERE where_condition]
 [GROUP BY {col_name|position}[ASC|DESC],...] //查詢結果分組
 [HAVING where_condition] //設定分組條件
 [ORDER BY {col_name|expr|position}[ASC|DESC]] //對結果排序
 LIMIT {[offset,]row_count|row_count OFFSET offset}]//限制記錄數量
 ]

記錄為2、3行：SELECT * FROM user LIMIT 1,2;

別名使用(在項目中一般都使用別名)AS alias_name:
  SELECT id AS uid FROM user AS u ;

子查詢：
SELECT AVG(age) FROM user WHERE userid = 
 [ALL|SOME|ANY|[NOT] IN|[NOT] EXISTS](SELECT uid FROM privinces WHERE pname = ‘河南‘);

外鍵約束：
(父表為user表,必須先在父表中插入資料，才能在子表中插入資料）
CASEADE：從父表中刪除或更新行 且 自動串聯刪除或更新子表中匹配的行；
SET NULL:從父表刪除或更新行，並設定子表中的外鍵列為NULL;
RESTRICT:拒絕對父表的更新或刪除操作；
CREATE TABLE privinces(
  pid SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  pname VARCHAR(20) NOT NULL ,
  uid SMALLINT,
  FOREIGN KEY (uid) REFERENCES user (id)    //privinces的欄位uid與user表的欄位id外鍵約束
ON DELETE CASEADE   //刪除時 串聯刪除
);

增加主鍵、唯一、外鍵約束：
ALTER TABLE privinces 
  ADD [CONSTRAINT uq_pname] UNIQUE(pname);
添加/刪除預設約束：
ALTER TABLE privinces 
  ALTER pname {SET DEFAULT literal | DROP DEFAULT}; 
刪除主鍵約束：
ALTER TABLE privinces DROP PRIMARY KEY;
刪除唯一(索引)約束：
     （查看約束名字key_name: SHOW INDEXS FROM privinces;）
ALTER TABLE privinces DROP {INDEX | KEY} 約束名;
刪除外鍵約束：
     （查看約束名字：SHOW CREATE TABLE privinces;）
ALTER TABLE privinces DROP FOREIGN KEY 約束名;

多表更新：
UPDATE 表的參照關係 SET col_name = {[expr | value]};

表的參照關係：
tab1_reference {[INNER|CROSS] JOIN |{LEFT|RIGHT}[OUTER] JOIN} tab2_refenence ON conditional_expr;
內串連：INNER JOIN
左外串連：LEFT JOIN
右外串連：ROGHT JOIN
UPDATE user INNER JOIN privinces ON user_privince = pname
  SET user_privince = pid;//(更新user內串連privince,條件是user_privince.=pname,設定user_privince=pid)

建立資料表的同時將查詢結果寫入資料表：
CREATE TABLE table_name
  [create_definition, ...]   //建立語句的定義
  select_statement;   //要插入查詢結果的 查詢語句

字元函數：
CONCAT()字元串連；CONCAT(‘I‘,‘LOVE‘,‘YOU‘);
CONCAT_WS()使用指定的分隔字元進行字元串連；
CONCAT_WS(‘|‘,‘A‘,‘B‘);第一個為分割符
FORMAT();數字格式化;
FORMAT(99999.99,1);將數字格式化，並保留1位小數
LOWER();轉換成小寫字元
UPPER();轉換成大寫字元
LEFT();擷取左側字元
RIGHT();擷取右側字元
LENGTH();擷取字元長度；
LTRIN();刪除前置字元；
RTRIM();刪除後續字元
TRIM();刪除前置、後續字元；
SUBSTRING();字串的截取；
SUBSTRING(‘MYSQL‘,1,2);從第一位中截取2位；（mysql不允許為負值）
[NOT] LIKE;模式比對
SELECT * FROM user WHERE username LIKE ‘%1%%‘ ESCAPE ‘1‘;
REPLACE();替換字串中的字元
REPLACE(‘M??Y??SQL‘,‘?‘,‘*‘);將M??Y??SQL中的？替換成*;

數值運算子：
CEIL();進一去整；
DIV;整數除法；
FLOOR();舍一去整；
MOD;取餘數（模）；
POWER();冪運算；
ROUND();四捨五入；ROUND(3.562,1);取一位小數
TRUNCATE();數字截取；TRUNCATE(125.89,1);小數後截取一位；

日期時間：
NOW();目前時間；
CURDATE();當前日期；
CURTIME();目前時間；
DATE_ADD();日期變化；
DATE_ADD(‘2015-6-23‘,INTERVAL 365 DAY);
DATEDIFF();倆日期之間的差值；
DATE_FORMAT();進行日期格式化；
SELECT DATE_FORMAT(‘2015-6-26‘,‘%m/%d/%Y‘);

MySQL常用文法