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