MySQL-SQL

1.客户端内置命令

客户端内置命令客户端独有，可能不同数据库产品的客户端内置命令存在很大差异，不像SQL命令有标准规范。

help

\h

?

\?

这四个命令都可以输出帮助文档查看客户端内置命令 

？（\？）“帮助”的同义词。
clear（\c）清除当前输入语句。
connect（\r）重新连接到服务器。可选参数是db和host。
delimiter（\d）设置语句分隔符，默认是;。
edit（\e）使用$EDITOR执行编辑命令。
ego（\G）向mysql服务器发送命令，垂直显示结果。
exit（\q）退出mysql。和quit一样。
go（\g）向mysql服务器发送命令。
help（\h）显示此帮助。
nopage（\n）禁用分页器，打印到stdout。
notee（\t）不要在outfile中书写。
pager（\P）设置pager[to_pager]。通过PAGER打印查询结果。
print（\p）打印当前命令。
prompt（\R）更改mysql提示符。
quit（\q）退出mysql。
rehash（\#）重建完成哈希。
source（\.）执行SQL脚本文件。将文件名作为参数。
status（\s）从服务器获取状态信息。
system（\！）执行系统shell命令。
tee（\T）设置输出文件[to_outfile]。把所有东西都添加到给定的输出文件中。
use（\u）使用另一个数据库。将数据库名称作为参数。
charset（\C）切换到另一个字符集。可能需要处理具有多字节字符集的binlog。
warnings（\W）在每条语句后显示警告。
nowarning（\w）不要在每个语句后显示警告。
resetconnection（\x）清理会话上下文。

需要注意的是这些命令设置只对当前会话有效，想要永久生效需要设置配置文件。

我们常用的客户端内置命令有

2.服务端内置命令

当我们输入客户端内置命令help时可以看到后面有提示

For server side help, type 'help contents'

输入 help contents可以看服务端内置命令

(root@localhost) [(none)]> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:Account ManagementAdministrationComponentsCompound StatementsContentsData DefinitionData ManipulationData TypesFunctionsGeographic FeaturesHelp MetadataLanguage StructureLoadable FunctionsPluginsPrepared StatementsReplication StatementsStorage EnginesTable MaintenanceTransactionsUtility

Account Management  账户管理
Administration  管理
Components  组件
Compound Statements  复合声明
Contents  目录
Data Definition  数据定义
Data Manipulation  数据操纵
Data Types  数据类型
Functions  功能
Geographic Features  地理特征
Help Metadata  帮助元数据
Language Structure  语言结构
Loadable Functions  可加载功能
Plugins  插件
Prepared Statements  准备好的报表
Replication Statements  复制声明
Storage Engines  存储引擎
Table Maintenance  表维护
Transactions  事务
Utility  公用事业

怎么使用？

假如有天忘记了插入数据的SQL语法了，可以这样使用

(root@localhost) [(none)]> help Data Manipulation
You asked for help about help category: "Data Manipulation"
For more information, type 'help <item>', where <item> is one of the following
topics:CALLDELETEDODUALHANDLERIMPORT TABLEINSERTINSERT DELAYEDINSERT SELECTJOINLOAD DATALOAD XMLPARENTHESIZED QUERY EXPRESSIONSREPLACESELECTTABLEUNIONUPDATEVALUES STATEMENT(root@localhost) [(none)]> help INSERT
Name: 'INSERT'
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE][INTO] tbl_name[PARTITION (partition_name [, partition_name] ...)][(col_name [, col_name] ...)]{ {VALUES | VALUE} (value_list) [, (value_list)] ...|VALUES row_constructor_list}[AS row_alias[(col_alias [, col_alias] ...)]][ON DUPLICATE KEY UPDATE assignment_list]

3.sql_mode

sql_mode会影响 SQL 语法和数据验证 

8.0默认sql_mode查询

(root@localhost) [(none)]> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

官方参考：https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

 4.常用数据类型 

4.1数值类型

4.1.1整数类型

类型	占用字节	无符号范围	有符号范围	最大数据长度
tinyint	1	0-255	-128-127	3
smallint	2	0-65535	-32768-32767	5
mediumint	3	0-16777215	-8388608-8388607	8
int	4	0-4294967295	-2147483648-2147483647	10
bigint	8	0-2^64-1	-2^63-2^63-1	20

4.1.2浮点类型 

类型
float	单精度浮点型
double	双精度浮点型
decimal	定点型

4.2字符串类型

类型	长度	特性
char	0-255字符	固定长度
varchar	理论0-65535字符（存储中文会少于这个最大值）	可变长度，需要额外1个或者2个字节存储实际存储字节长度
text

4.3时间类型 

类型	大小字节	范围	说明
datetime	8	'1000-01-01 00:00:00'-'9999-12-31 23:59:59'	日期+时间
datestamp	4	'1970-01-01 00:00:01'-'2038-01-19 03:14:07'	日期+时间的时间戳

5.约束和属性

primary key:主键约束，保证唯一性和非空，一个表只能有一个主键，一个主键可以是一个列或者多列组合

foreign key: 外键约束，用来关联两张表，但大型项目开发建议不用外键约束，而是使用业务层面逻辑约束

not null: 非空约束

default:默认约束

unique：唯一约束

auto_increment:自增长列

unsigned:无符号，比如不让数字类型为负数

comment: 注释

6.DDL

库定义

创建数据库

create database if not exists testdb1 charset utf8mb4 collate utf8mb4_0900_ai_ci;

修改库定义（不能改库名）

alter database testdb1 charset utf8;

 添加或者修改索引

alter table student add index i_sname(sname);

 删除库

drop database if exists testdb1;

表定义

创建表

create table if not exists student(
sid int unsigned not null primary key auto_increment comment '学号',
sname varchar(64) not null comment '姓名',
aage tinyint unsigned not null default 18 comment '年龄',
sgender char(1) not null default '1' comment '性别,1男0女',
saddr enum('广东','广西','福建') not null comment '地址省',
stel char(11) not null unique key comment '手机号',
stime datetime not null default now() comment '入学时间'
)engine=innodb charset=utf8mb4 comment='学生表';

通过复制表创建表方式（不会复制数据）

create table copy_stu like student;

查询表

show tables;
show create table student;

修改表

可修改表名

rename table copy_stu to stu1;

修改表使用的引擎 

(root@localhost) [test]> alter table user engine=innodb;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表结构

添加列is_deleted标识是否被删除，1标识已被删除0则没有被删除

(root@localhost) [test]> alter table user add column is_deleted tinyint not null default 0 comment '1标.已被删除0则没有被删除';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@localhost) [test]> desc user;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| uid        | int         | YES  |     | NULL    |       |
| uname      | varchar(64) | YES  |     | NULL    |       |
| is_deleted | tinyint     | NO   |     | 0       |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

指定列后面添加列

(root@localhost) [test]> alter table user add bbbb int after uid;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

 删除列

(root@localhost) [test]> alter table user drop bbbb;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改列数据类型（会锁表，锁时间和数据量有关）

(root@localhost) [test]> alter table user modify uname varchar(100) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改列名字

(root@localhost) [test]> alter table student change column aage sage tinyint unsigned not null default 18 comment '年龄';
Query OK, 0 rows affected (0.07 sec)

需要注意修改数据类型长度时一般是提高长度，约束也要完整添加 

修改列添加索引

(root@localhost) [test]> alter table user add index i_uname(uname);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0(root@localhost) [test]> desc user;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| uid        | int          | YES  |     | NULL    |       |
| uname      | varchar(100) | NO   | MUL | NULL    |       |
| is_deleted | tinyint      | NO   |     | 0       |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除表

(root@localhost) [test]> drop table user;
Query OK, 0 rows affected (0.01 sec)

清空表（清空所有行数据，其他不变）

(root@localhost) [test]> select * from user;
+------+----------+
| uid  | uname    |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.01 sec)(root@localhost) [test]> truncate table user;
Query OK, 0 rows affected (0.00 sec)(root@localhost) [test]> select * from user;
Empty set (0.00 sec)