MySQL基本命令

mysql目录结构

bin目录，存储可执行文件

data目录，存储数据文件

docs，文档

include目录，存储包含的头文件

lib目录，存储库文件

share，错误消息和字符集文件

 

启动/停止mysql服务：net start/stop mysql

 

mysql初步操作

mysql登陆

mysql参数

参数	描述
-D, --database=name	打开指定数据库
--deliniter = name	指定分隔符
-h, --host=name	服务器名称
-p, --password[=name]	密码
-P, --port=#	端口号
--prompt=name	设置提示符
-u, --user=name	用户名
-V, --version	输出版本信息并退出

mysql退出

mysql > exit;

mysql > quit;

mysql > \q;

修改mysql提示符

连接客户端时通过参数指定

shell > mysql -uroot -proot --prompt 提示符

连接上客户端后，通过prompt命令修改

mysql > prompt 提示符

mysql提示符：

参数	描述
\D	完整的日期
\d	当前数据库
\h	服务器名称
\u	当前用户

mysql常用命令

显示当前服务器版本：SELECT VERSION();

显示当前日期时间：SELECT NOW();

显示当前用户：SELECT USER();

mysql语句的规范

关键字与函数名称全部大写

数据库名称、表名称、字段名称全部小写

SQL语句必须以分号结尾

 

数据库操作

{}中的是必选项，|表示选择，[]表示可选项

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

查看当前服务器下的数据库列表

SHOW {DATABASES | SCHEMAS}

[LIKE 'pattern' | WHERE expr]

修改数据库

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTR SET [=] charset_name

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

 

数据类型与操作数据表

数据类型

数值类型（整型、浮点型（单精度浮点型、双精度浮点型））、日期和时间类型、字符串类型

数据表

打开数据库

USE 数据库名称；

创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(

column_name1 data_type,

column_name2 data_type,

....

)

CREATE...SELECT

创建数据表同时间查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

select_statement

查看数据表列表

SHOW TABIES [FROM db_name]

[LIKE 'pattern' | WHERE expr]

查看数据表结构

SHOW COLUMNS FROM tbl_name

插入记录

INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...)

记录查找

SELECT expr,... FROM tbl_name

 

约束

空值与非空(创建column时可明确)

NULL，字段值可以为空（默认）

NOT NULL,字段值禁止为空

PRIMARY KEY

主键约束

每张数据表只能存在一个主键

主键保证记录的唯一性

主键自动为NOT NULL

AUTO_INCREMENT

自动编号，且必须与主键结合使用(AUTO_INCREMENT必须与主键一起使用，但主键不一定要和AUTO_INCREMENT一起使用)

默认情况下，起始值为1，每次的增量为1

UNIQUE KEY

唯一约束

唯一约束可以保证记录的唯一性

唯一约束的字段可以为空值（NULL），为保证唯一性，空值只能有一个

每张数据表可以存在多个唯一约束

DEFAULT

默认值

当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

 

约束以及修改数据表

约束

1.约束保证数据的完整性和一致性

2.约束分为表级约束和列级约束

3.约束类型包括：NOT NULL（非空约束）

PRIMARY KEY（主键约束）

UNIQUE KEY（唯一约束）

DEFAULT（默认约束）

FOREIGN KEY（外键约束）

DOREIGN KEY

保持数据一致性，完整性

实现一对一或一对多关系

外键约束的要求

1.父表和子表必须使用相同的存储引擎，而且禁止使用临时表

2.数据表的存储引擎只能为InnoDB

3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同

4.外键列和参照列必须创建索引。如果参照不存在索引的话，MySQL将自动创建索引

编辑数据表的默认存储引擎

MySQL配置文件

default-storage-engine=INNODB

查看数据表创建信息： SHOW CREATE TABLE tbl_name；

外键约束的参照操作

1.CASCADE:从父表删除或更新时自动删除或更新子表中匹配的行

2.SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

3.RESTRICT:拒绝对父表的删除或更新操作

4.NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同

表级约束与列级约束

对一个数据列建立的约束，称为列级约束

对多个数据列建立的约束，称为表级约束

列级约束既可以在列定义时声明，也可以在列定义后声明

表级约束只能在列定义后声明

修改数据表

添加单列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

添加多列

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,...)

添加多列无法指定位置关系，只能在原来字段的下方

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...)

添加外键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

添加/删除默认约束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

删除唯一约束

ALTER TABLE tbl_name DROP PRIMARY KEY

删除主键约束

ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

修改列定义

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

tips:当数据类型从大改为小时要考虑数据存储的可靠性

修改列名称

ALTER TABLE bl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

数据表更名

方法1

ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name

方法2

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

tips:不要随意更改数据表名、数据列名

 

操作数据表中的记录

插入记录（INSERT）

方法1

INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

为自动编号的字段赋值可以采用 NULL|DEFAULT 默认递增

设置默认约束的字段可以赋值DEFAULT传入默认值

方法2

INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...

说明：与第一种方式的区别在于，此方法可以使用子查询（SubQuery）

每次只能插入一条记录

方法3

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...

说明：此方法可以将查询结果插入到指定数据表

更新（修改）记录（UPDATE）

单表更新

UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1 | DEFAULT}

[, col_name2={expr2 | DEFAULT}] ... [WHERE where_conditon]

删除记录（delete）

单表删除

DELETE FROM tbl_name [WHERE where_condition]

删除row后执行插入会在最大的id+1，不会填充删除的id

查找记录（SELECT）

SELECT select_expr [, select_expr ...]

[

FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | position} [ASC | DESC], ...]

[HAVING where_condition]

[ORDER BY {col_name | expr | position} [ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

]

查询表达式

每一个表达式表示想要的一列，必须至少有一个

多个列之间以英文逗号分隔

星号(*)表示所有列，tbl_name.* 可以表示命名表的所有列（多表连接时字段名称可能重复）

查询表达式可以使用 [AS] alias_name 为其赋予别名

别名可用于 GROUP BY , ORDER BY 或 HAVING 子句

说明：SELECT语句查找字段的顺序将影响结果集出现的顺序，字段的别名也将影响结果集当中的字段名。

 

WHERE

条件表达式：对记录进行过滤，如果没有指定WHERE子句，则显示所有记录。在WHERE表达式中，可以使用MySQL支持的函数或运算式。

GROUP BY

查询结果分组

[GROUP BY {col_name | position} [ASC | DESC], ...] ASC升序 DESC降序 默认ASC

HAVING

分组条件

[HAVING where_condition]

ORDER BY

对查询结果进行排序

[ORDER BY {col_name | expr | position}] [ASC | DESC], ...]

第一种排序结果相同的部分按照第二种排序进行，以此类推

LIMIT

限制查询结果返回的数量

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

 

子查询与连接

子查询

子查询（Subquery）是指出现在其他SQL语句内的SELECT子句。

例如：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement),SELECT column1 FROM t2 称为Sub Query[子查询]。

说明：

子查询是嵌套在外查询内部，而事实上它有可能在子查询内部再嵌套子查询

子查询必须出现在圆括号之间

子查询可以包含多个关键字或条件，如DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等

子查询的外层查询可以使：SELECT,INSERT,UPDATE,DELETE,SET或DO

子查询返回值

子查询可以返回标量、一行、一列或子查询

 

使用比较运算符的子查询

比较运算符：= > < >= <= <> != <=>

语法结构： operand comparison_operator subquery

用ANY、SOME或ALL修饰的比较运算符

operand comparison_operator ANY subquery

operand comparison_operator SOME subquery

operand comparison_operator ALL subquery

使用[ NOT ] IN 的子查询

语法结构

operand comparison_operator [NOT] IN (subquery)

=ANY运算符与IN等效

！=ALL或<>ALL运算符与NOT IN等效

使用[NOT] EXISTS的子查询

如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE。

 

 

连接

MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

连接类型

INNER JOIN 内连接

内连接显示左表及右表符合连接条件的记录

在MySQL中，JOIN、CROSS JOIN和INNER JOIN是等价的

LEFT [OUTER] JOIN 左外连接

左外连接显示左表的全部记录及右表符合连接条件的记录

RIGHT [OUTER] JOIN 右外连接

右外连接显示右表的全部记录及左表符合连接条件的记录

连接条件

通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤。

关于连接的说明

外连接

A LEFT JOIN B join_condition

数据表B的结果集依赖数据表A

数据表A的结果集根据左连接条件依赖所有数据表（B表除外）

左外连结条件决定如何检索数据表B（在没有指定WHERE条件的情况下）

如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行

如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接条件的记录后停止搜索更多的行

语法结构

table_reference

{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}

table_reference

ON conditional_expr

数据表参照

table_reference

tbl_name [[AS] alias] | table_subquery [AS] alias

数据表可以使用tbl_name [AS] alias_name赋予别名

table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名

多表更新

UPDATE table_reference

{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}

table_reference

ON conditional_expr

SET col_name1={expr1 | DEFAULT}

[, col_name2={expr2 | DEFAULT}] ...

[WHERE where_conditon]

 

无限分类的数据表设计

例如：

CREATE TABLE tdb_goods_types(

type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

type_name VARCHAR(20) NOT NULL,

parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0

);

自身连接

同一个数据表对其自身进行连接

多表删除

DELETE tbl_name[.*] [, tbl_name[.*]]...

FROM table_reference

[WHERE where_condition]