MySQL基本用法

MySQL

MySQL配置

SHOW [GLOBAL] VARIABLES LIKE <>;

SHOW DATABASES;  
SHOW CREATE DATABASE <db>; 
SHOW TABLES;
SHOW CREATE TABLE <tb>;

导入csv

LOAD DATA INFILE 'D:/MySQL/data/Uploads/<file>.csv'
    INTO TABLE <tb>  
    FIELDS TERMINATED BY ','    -- 字段分割
    OPTIONALLY ENCLOSED BY '"'  -- 双引号包围的，视为一个字段
    ESCAPED BY '"'              -- 引号字段之内可使用另一个引号转义
    LINES TERMINATED BY '\r\n'; -- 元组分割

导出csv

<select语句>
    INTO OUTFILE 'D:/MySQL/data/Uploads/Downloads/<file>.csv' 
    FIELDS ENCLOSED BY '"' 
    TERMINATED BY ',' 
    ESCAPED BY '"' 
    LINES TERMINATED BY '\r\n';

备份与恢复

sqldump -u <user> -p <db> > <db_dump>.sql
sql -u <user> -p <db> < <db_dump>.sql

sql语法

db - database 数据库
scm - schema 模式
tb - table 表
v - view 视图
col - column 列
tp - type 数据类型
ck - check 检查
cst - constraint 约束
idx - index 索引
tg - trigger 触发器

数据库和模式 DATABASE SCHEMA

USE <db> 
CREATE DATABASE <db> [CHARACTER SET gbk COLLATE gbk_chinese_ci]; 
ALTER DATABASE <db> CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
DROP DATABASE <db>;

CREATE SCHEMA <scm> AUTHORIZATION <user> [<tb>|<v>|<授权定义子句>]
DROP SCHEMA <scm>[CASCADE│RESTRICT];

表 TABLE

create

CREATE TABLE <tb>(
    <col> <tp> <col_cst>,
    CONSTRAINT <cst_name> <tb_cst>
);

-- example
CREATE TABLE Student( 
    Sno CHAR(6) PRIMARY KEY,
    Sname CHAR(8) NOT NULL UNIQUE,
    Sage INT DEFAULT 20,  
    Ssex CHAR(2),
    Sdept CHAR(12) DEFAULT NULL,
    CONSTRAINT C1 CHECK (Ssex IN('男','女')),
);
CREATE TABLE SC(
    Sno CHAR(6) NOT NULL,
    Cno CHAR(6) REFERENCES Course(Cno), 
    Grade INT CHECK (Grade BETWEEN 0 AND 100),
    Sdate DATE,
    CONSTRAINT SC_PK PRIMARY KEY(Sno,Cno),
    CONSTRAINT SC_FK FOREIGN KEY (Sno) REFERENCES Student(Sno)
);

alter

ALTER TABLE <tb> ADD <col> <tp>; -- 新列NULL
ALTER TABLE <tb> ALTER Sname CHAR(20); -- 修改原列，可能破坏数据 MySQL失败了
ALTER TABLE <tb> MODIFY COLUMN <col> <new_tp> [<dft> <not_null> <ck>]; -- 修改类型
ALTER TABLE <tb> CHANGE <old_col> <new_col> <tp>;

-- 添加约束
ALTER TABLE <tb> ADD PRIMARY KEY (<col>);
ALTER TABLE <tb> ADD [constraint <key_name>] FOREIGN KEY (Sno) REFERENCES <other_tb>(<pk>);
ALTER TABLE <tb> ADD CONSTRAINT <ck_name> CHECK (<ck>);
ALTER TABLE <tb> ALTER COLUMN <col> SET DEFAULT <dft>;
-- 删除约束
ALTER TABLE <tb> DROP PRIMARY KEY;
ALTER TABLE <tb> DROP FOREIGN KEY <key_name>;
ALTER TABLE <tb> DROP CONSTRAINT <cst>;
ALTER TABLE <tb> ALTER COLUMN <col> DROP DEFAULT;

drop

DROP TABLE <tb> [RESTRICT｜CASCADE];

index

CREATE [UNIQUE] [CLUSTER] INDEX <idx> 
    ON <tb> (<col>[ASC|DESC][,<col2>[A|D] ]…);

DROP INDEX <idx>;

数据操纵

数据查询

1. 单表查询

SELECT [ALL|DISTINCT] <cols> FROM <tb/v>
    WHERE <> 
    GROUP BY <col> HAVING <>
    ORDER BY <col> [ASC|DESC];

% 任意长度， _ 单个字符
要查询含%或_字符串时，使用ESCAPE ‘<换码字符>’ 转义：
以"DB_"开头，且倒数第3个字符为i:Cname LIKE 'DB\_%i__' ESCAPE '\'

集函数

COUNT/SUM/AVG/MAX/MIN ([ALL|DISTINCT] <col>)

2. 连接查询

SELECT <cols> FROM <tb1>, <tb2> WHERE <>;
SELECT <cols> FROM <tb1> INNER JOIN <tb2> ON <>;
SELECT <cols> FROM <tb1> INNER JOIN <tb2>;
SELECT <cols> FROM <tb1> LEFT JOIN <tb2> ON <>;

3. 嵌套查询

SELECT Sname FROM Student 	-- 外层查询/父查询
WHERE Sno IN
  (SELECT Sno FROM SC WHERE Cno= '2') --内层查询/子查询

SELECT Sname FROM Student
WHERE EXISTS -- 相关子查询
  (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = '1'); 

4. 集合查询

<查询块>
UNION [ALL] -- INTERSECT MINUS
<查询块>
[ORDER BY]

数据更新

1. 插入数据

INSERT INTO <tb> [(<cols>)] VALUES (<vals>);
INSERT INTO <tb> [(<cols>)] <select>;

2. 修改数据

UPDATE <tb> SET <cols> = <col_vals> [WHERE <>];

3. 删除数据

DELETE FROM <tb> WHERE <>;
TRUNCATE TABLE <tb>; -- 无日志

视图 VIEW

1. create

CREATE VIEW <v> [(<cols>)] AS <select> [WITH CHECK OPTION];

-- example
CREATE VIEW dbo.ShipStatusView 
AS SELECT OrderID, ShippedDate, ContactName FROM Customers c, Orders o
   WHERE c.CustomerID=O.CustomerID and RequiredDate<ShippedDate;

CREATE VIEW D-Sage (Sdept, Avgage)
AS SELECT Sdept, AVG(Sage) FROM Student GROUP BY Sdept;

2. drop

DROP VIEW <v>;

触发器 TRIGGER

CREATE TRIGGER <触发器名>  
{BEFORE | AFTER} <INSERT|UPDATE|DELETE> ON <表名>
FOR EACH ROW
[WHEN <触发条件>]
<触发动作体>

-- example
drop trigger if exists <tg>;
DELIMITER // 
CREATE TRIGGER check_name_update
BEFORE UPDATE ON address
FOR EACH ROW
BEGIN 
    IF NOT EXISTS 
        (SELECT name FROM salaried_worker WHERE name = NEW.name) 
    THEN 
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'THE NAME DOSE NOT EXIST.';
    END IF;
END//
DELIMITER ; -- 分隔符

• NEW：新插入的记录
• OLD：刚删除的记录

授权

创建用户

CREATE USER 'BIT'@'localhost' IDENTIFIED BY 'password';

授权

GRANT {<权限1>, <权限2>, …}
  ON <表名或视图名>
  TO {<用户名1>,<用户名2>, … | PUBLIC}
  [WITH GRANT OPTION];

-- example
GRANT UPDATE (折扣) ON Lineitem TO 'BIT'@'localhost';

撤销

REVOKE {<权限1>, <权限2>, …} 
  ON TABLE <表名或视图名>
  FROM {<用户名1>,<用户名2>, … | PUBLIC}
  [RESTRICT|CASCADE];

-- example
REVOKE ALL PRIVILEGES ON Lineitem FROM 'BIT'@'localhost';

查看

SHOW GRANTS FOR CURRENT_USER(); -- 当前用户
SHOW GRANTS FOR 'username'@'localhost'; --特定用户
SELECT User, Host FROM mysql.user; -- 所有用户列表

删除

DROP USER 'username'@'localhost';

type

CREATE DOMAIN <域名> <数据类型> -- MySQL不支持

• 数值类型:
  INT: $-2^{31}\to 2^{31}-1$
  TINYINT: $-128\to 127$
  SMALLINT: $-2^{15}\to 2^{15}-1$
  BIGINT: $-2^{63}\to 2^{63}-1$
  FLOAT: 4个字节，精度大约为7位小数位
  DOUBLE: 8个字节，精度大约为15位小数位
  DECIMAL（或NUMERIC）: 定点数，可以指定精度和小数位数。
  可以通过在类型后面添加UNSIGNED关键字来指定无符号类型。

• 日期和时间类型:
  DATE: 日期，格式为 YYYY-MM-DD。
  TIME: 时间，格式为 HH:MM:SS。
  DATETIME: 日期和时间组合，格式为YYYY-MM-DD HH:MM:SS。
  TIMESTAMP: 时间戳，与DATETIME类似，但范围较小。

• 字符串类型:
  CHAR(n): 固定长度字符串，最大长度为 255 个字符。
  VARCHAR(n): 可变长度字符串，最大长度为 65535 个字符。
  TEXT: 长文本数据，最大长度为 65535 个字符。
  BLOB: 二进制大对象，用于存储大型二进制数据，如图片或文件。

• 空间数据类型:
  POINT: 点。
  LINESTRING: 线。
  POLYGON: 多边形。
  GEOMETRY: 任意类型的空间对象。

• JSON数据类型:
  JSON: 用于存储JSON（JavaScript Object Notation）数据。