1. 常用的显示命令
查看数据集: show character set;
查看排序规则:show collation;
查看已用的数据库:show databases;
查看表的结构:desc tb_name;
显示表的描述信息:show table status like ‘tb1’; 或者 show table status like ‘tb1’\G(以列的形式显示)
显示表的索引: show indexes from tb1;或者show indexes from tb1\G(以列的形式显示)
显示表的创建语句:show CREATE TABLE tb_name;
2 DDL (Database Definition Language)
2.1 创建数据库(help create database;)
格式:create DATABASE|SCHEMA IF NOT EXISTS nuist CHARACTER SET ‘gbk’ COLLATE ‘gbk_chinese_ci’;
创建数据库的本质就是在对应的Data目录下创建一个nuist目录,nuist目录下有一个db.opt的文件保存的就是指定的字符集和排序规则,内容如下:
default-character-set=gbk
default-collation=gbk_chinese_ci
2.2 修改数据库(修改字符集或者排序规则)(Help alter database;)
mysql> help alter database;
Name: 'ALTER DATABASE'
Description:
Syntax:
ALTER {DATABASE | SCHEMA} [db_name] alter_specification ...
ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name| [DEFAULT] COLLATE [=] collation_name
ALTER DATABASE enables you to change the overall characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need the ALTER privilege on the database. ALTER SCHEMA is a synonym for ALTER DATABASE.
2.3 删除数据库(help drop database;)
2.4 创建表
2.4.1 直接定义一张空表
create table tb1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,name char(20) not null,age TINYINT not null);
create table tb1(id INT UNSIGNED NOT NULL AUTO_INCREMENT ,name char(20) not null,age TINYINT not null, primary key(id),unique key(name),index(age));
唯一键、主键、索引的区别?键也称作约束,可用作索引,是特殊的索引(有特殊限定),都是B+Tree结构的形式,可以使用create index命令单独创建索引。
2.4.2 从其他表中查询数据,并以之创建新表
create table other_agents select * from agents where AgentNo > 100; //注意:这种方式创建表,不会继承之前表的属性。
2.4.3 以其他表为模板创建一张新表
create table other_agents2 like agents; //注意:表格式的属性完全和之前的一样
显示表的描述信息:show table status like ‘tb1’; 或者 show table status like ‘tb1’\G
显示表的索引: show indexes from tb1;或者show indexes from tb1\G
显示表结构: desc other_agents;
2.4.4 指定索引
① 单字段: PRIMARY KEY,UNIQUE KEY
② 单或多字段:PRIMARY KEY(col, …),UNIQUE KEY(col,…),INDEX(col,…)
2.5 修改表 (help alter table ;)
2.6 删除表 (help drop table;)
2.7 索引 (help drop table;)
索引只能不创建、查看和删除,不能被修改
- 创建索引:(help create index;)create index index_name on tb_name(col_name,…);
- 查看索引:show indexes from tb_name;
- 删除索引:(help drop index;),DROP INDEX index_name ON tbl_name
3. DML(Database Manipulation Language)
3.1 Select
mysql> help select;
Name: 'SELECT'
Description:
Syntax:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
3.1.1 简单查询语句
select field1,field2,field3 fromt tb_name; //投影
select * from tb_name where conditions; //选择
from子句:表示要查询的关系, 表、多个表、其他SELECT语句
where子句:布尔表达式,=,>,>=,<,<=,<>,!=,<=>
逻辑关系:AND,OR,NOT
特殊操作符
- between … and …, like (其中,%表示任意长度,任意字符;_表示任意单个字符)
- rlike,regexp,支持正则化比较;RLIKE ‘^[m,n,y].*$’,注意此时索引就失效了。
- in,作离散取值;
- is null,is not null:和null做比较;
- oder by [ASC|DESC],将查询后的结果进行排序
- as 字段别名
- limit 显示有限的行,limit NUMBER表示显示前NUMBER个;limit NUM1, NUM2,表示从NUM1开始取NUM2个。
聚合函数: AVG()、 MAX()、MIN()、SUM()、COUNT()
GROUP BY: 根据条件进行分组
Having qualification: 要Group by结合使用,用于对Group by的结果进行过滤。
3.1.2 多表查询
连接方式:
- 交叉连接,笛卡尔乘积
- 自然连接(内连接),仅仅保留具有等值关系的行。
- 外连接之左连接:tb_name1 left jion tb_name2 on conditions
- 外连接之右连接: tb_name1 right jion tb_name2 on conditions
- 自连接
3.1.3 子查询
- 在比较操作中使用子查询:子查询的结果一定要是一个单值,否则会出错;
- 在in中使用子查询:子查询的结果可以是一个集合;
- 在from中使用子查询;
- 联合查询:union;
3.1.4 视图
-
视图
- 视图就是存储下来的Select语句,基于基表的查询结果。一般不建议直接向视图中插入或者更新数据,视图一般称之为虚表。
创建视图: (help create view;)
删除视图: (help drop view;)
注意:MySQL不支持物化视图,也不建议在MySQL中使用视图;
3.2 Delete
mysql> help delete
Name: 'DELETE'
Description:
Syntax:
Single-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
清空表:truncate tb_name,能够清空表,并重置Autoincrement计数器。
3.3 Insert into
insert into tb_name (col1,col2,…) values(val1,val2,…),[,(val1,val2,…),…]
在有些场景下,批量插入可以提高性能;
注意插入的格式:字符型的值需要用单引号引起来,数值型不需要引号,日期时间型也不需要引号,空值要写成null,”。
查看插入帮助:help insert
mysql> help insert
Name: 'INSERT'
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
3.4 UPDATE
mysql> help update
Name: 'UPDATE'
Description:
Syntax:
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]