创建/删除 数据库

Create / Delete Database

CREATE DATABASE dbNameYouWant -- 创建数据库
CREATE DATABASE dbNameYouWant CHARACTER SET utf8 -- 创建并设定字符
DROP DATABASE dbNameYouWant -- 删除
ALTER DATABASE dbNameYouWant CHARACTER SET utf8 -- 修改
USE dbNameYouWant -- 使用数据库作为默认操作对象，如果要操作数据表

备份数据库到 sql 文件

Backup Database to SQL File

mysqldump -u Username -p dbNameYouWant > databasename_backup.sql

从备份中恢复数据

如果是要导入数据，需要先确认数据库的存在，然后导入数据。

Restore from backup SQL File

mysql - u Username -p dbNameYouWant < databasename_backup.sql

修复数据表

Repair Tables After Unclean Shutdown

mysqlcheck --all-databases
mysqlcheck --all-databases --fast

服务数据

-- 注释
SHOW DATABASES -- 显示数据库
SHOW TABLES -- 显示数据表
SHOW FIELDS FROM table / DESCRIBE table -- 显示表结构
SHOW PROCESSLIST -- 显示连接的用户进程
KILL process_number -- 删除连接

数据表元操作

Create / Delete / Modify Table

创建表

-- 创建表格
CREATE TABLE table (field1 type1, field2 type2, ...)
-- 设定 index 索引
CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field))

-- 设定主键
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1,
field2))


CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
    FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA))
    [ON UPDATE|ON DELETE] [CASCADE|SET NULL]


CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
    FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))


CREATE TABLE table IF NOT EXISTS (...)


CREATE TEMPORARY TABLE table (...)

删除表

DROP TABLE table
DROP TABLE IF EXISTS table
DROP TABLE table1, table2, ...

修改表

ALTER TABLE table MODIFY field1 type1
ALTER TABLE table MODIFY field1 type1 NOT NULL ...
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT
ALTER TABLE table ADD new_name_field1 type1
ALTER TABLE table ADD new_name_field1 type1 FIRST
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
ALTER TABLE table DROP field1
ALTER TABLE table ADD INDEX (field);

修改字段顺序

ALTER TABLE table MODIFY field1 type1 FIRST
ALTER TABLE table MODIFY field1 type1 AFTER another_field
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER
    another_field

键值 Keys

CREATE TABLE table (..., PRIMARY KEY (field1, field2))
CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2
(t2_field1, t2_field2))

增删改查

查询 Select

SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY field
SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

联合查询 Select - Join

SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...

条件表达式 Conditions

field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IS IN (value1, value2)
field1 IS NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2

插入数据 Insert

INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)

删除数据 Delete

DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
DELETE FROM table1, table2 FROM table1, table2 WHERE table1.id1 =
    table2.id2 AND condition

更新数据 Update

UPDATE table1 SET field1=new_value1 WHERE condition
UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... WHERE
    table1.id1 = table2.id2 AND condition

插入存在则更新

insert-on-duplicate

INSERT INTO table1 (field1,field2,field3) VALUES (value1,value2,value3)
  ON DUPLICATE KEY UPDATE field1=VALUES(field1);

INSERT INTO table1 (field1,field2,field3) VALUES (value1,value2,value3)
  ON DUPLICATE KEY UPDATE field1=value1;

使用 UNION 合并处理

INSERT INTO table1 (field1, field2)
SELECT * FROM
  (SELECT field3, field4 FROM table2
   UNION
   SELECT field5, field6 FROM table3) AS dt
ON DUPLICATE KEY UPDATE field1 = field4 + field3;

用户和权限

Users and Privileges

GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions


SET PASSWORD = PASSWORD('new_pass')
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass')
SET PASSWORD = OLD_PASSWORD('new_pass')


DROP USER 'user'@'host'

Host ‘%’ indicates any host.

主要的数据类型

数字

TINYINT (1o: -217+128)
SMALLINT (2o: +-65 000)
MEDIUMINT (3o: +-16 000 000)
INT (4o: +- 2 000 000 000)
BIGINT (8o: +-9.10^18)

精确范围: -(2^(8*N-1)) -> (2^8*N)-1

INT(2) = “2 digits displayed” – NOT “number with 2 digits max”

FLOAT(M,D) -- 比如 float(11,2)
DOUBLE(M,D)
FLOAT(D=0->53)

8,3 -> 12345,678 – NOT 12345678,123!

时间

TIME (HH:MM)
YEAR (AAAA)
DATE (AAAA-MM-JJ)
DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)

文本

VARCHAR (single-line; explicit size) -- 默认255
TEXT (multi-lines; max size=65535)
BLOB (binary; max size=65535)

变体(Variants) for TEXT&BLOB: TINY (max=255), MEDIUM (max=~16000), and LONG (max=4Go). Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT

    ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)

重置密码

# 停止服务
$ /etc/init.d/mysql stop
# 开启安全模式
$ mysqld_safe --skip-grant-tables

# 从另一个终端 进入
$ mysql  
# 设置root密码
mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';
mysql > quit

## 返回终端,关闭安全模式(Control + \ 杀死进程)，启动服务
$ /etc/init.d/mysql start

操作系统不同情况下，命令执行路径和参数需要注意区别。