覆盖在学习和工作中的常用 SQL 命令：Comment / Script / Database / Table / Column / Index
目录 Table of Contents
Script
1
2
3
4
5
|
mysql -u ${username} -p ${password} -D ${database} < ${file}
source ${file}
\. ${file}
|
Database
查询数据库
创建数据库
1
| CREATE DATABASE ${db_name};
|
切换数据库
删除数据库
1
| DROP DATABASE ${db_name};
|
Table
查看表
1
2
3
4
5
6
|
SHOW TABLES;
DESC ${table_name};
SHOW CREATE TABLE ${table_name};
|
创建表
1
2
3
4
| CREATE TABLE `${table_name}` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
修改表
1
2
3
4
5
6
|
ALTER TABLE ${table_name} ADD COLUMN id bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE ${table_name} CHANGE COLUMN id ID bigint(20) NOT NULL AUTO_INCREMENT;
ALTER TABLE ${table_name} DROP COLUMN id;
|
删除表
1
| DROP TABLE ${table_name};
|
Column
条件查询
1
| SELECT name FROM ${table_name} WHERE id=1;
|
分页查询
1
2
3
4
|
SELECT * FROM ${table_name} LIMIT 3 OFFSET 0
|
模糊查询
1
2
3
4
5
6
|
SELECT * FROM ${table_name} WHERE name Like ${cond};
|
排序
1
| SELECT * FROM ${table_name} ORDER BY id DESC;
|
计数
1
| SELECT COUNT(*) FROM ${table_name};
|
插入记录
1
| INSERT INTO ${table_name} (id, name) VALUES (1, "test");
|
更新记录
1
| UPDATE ${table_name} SET id=1, name="test";
|
删除记录
1
| DELETE FROM ${table_name} WHERE id=1 AND name="test";
|
Index
查询索引
1
| SHOW INDEX FROM ${table_name} FROM ${db_name}
|
创建索引
1
| CREATE [UNIQUE] INDEX ${index_name} ON ${table_name} (${column_name})
|
删除索引
1
| ALTER TABLE ${table_name} DROP INDEX ${index_name}
|
