MySQL基本用法

文章目录

- MySQL
- - MySQL配置
  - 导入csv
  - 导出csv
  - 备份与恢复
- sql语法
- - 数据库和模式 DATABASE SCHEMA
  - 表 TABLE
  - 数据操纵
  - - 数据查询
    - 数据更新
  - 视图 VIEW
  - 触发器 TRIGGER
  - 授权
- type

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>;

数据操纵

数据查询

单表查询

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

`WHERE`子句	查询条件
比较表达式	[NOT] =、>、>=、<、<=、<>(或!=)
逻辑表达式	AND、OR、NOT
BETWEEN	<列名>[NOT]BETWEEN <常> AND <>
IN	<列名>[NOT]IN (常量表列或 SELECT语句)
LIKE	<列名>[NOT]LIKE ‘匹配字符串’
NULL	<列名> IS[NOT] NULL
EXISTS	[NOT]EXISTS (SELECT语句)

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

集函数

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

连接查询

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 <>;

嵌套查询

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');

集合查询

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

数据更新

插入数据

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

修改数据

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

删除数据

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

视图 VIEW

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;

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: $\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）数据。