# 语句规范
- 关键字与函数名称全部大写
- 数据库名称,表名称,字段名称小写
- SQL语句必须以分号结尾
sql: 基本的增删改查，分页查询，范围查询，模糊搜索，多表联查
# 语句
## 基本常用语句
查看当前版本
```
SELECT VERSION();
```
查看当前用户
```
SELECT USER();
```
查看当前时间
```
SELECT NOW();
```
显示乱码
```sql
set names gbk;
set names utf-8;
# 选择正确的编码就行了
```
查看变量
```sql
show variabl;
show variable like "%mode%";
```
设置变量
```sql
set global variables var_name=var_value; # var_name 为变量名称, var_value为设置的值
```
慢查询
```sql
# 查看
show variables like '%slow_query_log%';
show variables like '%long_query_time%';
# 设置慢查询开启
set global slow_query_log='ON';
# 把慢查询设置为1s, 查过1s的sql将被记录下来
set global long_query_time=1;
```
查看数据库模式
```sql
show variables like '%sql_mode%';
```
神奇的desc/explain, c
```sql
desc tb_name;
desc select * from tb_name where id > 111 and sex=1 order by id desc;
```
# 数据库
## 连接数据库
```
mysql -uroot -p123456 -P3306 -h127.0.0.1
mysql -uroot -p123456
```
更多命令使用`mysql -?`查看,
## 查看所有数据库
```
SHOW DATABASES;
```
## 创建数据库
```
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
```
新建一个数据库
```
CREATE DATABSE t1;
```
仍然是新建一个数据库,加上`IF NOT EXISTS`
```
mysql> CREATE DATABSE IF NOT EXISTS t1;
Query OK, 1 row affected, 1 warning (0.00 sec)
```
这里有一个警告, `查看警告`
```
mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1007 | Can't create database 't1'; database exists |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)
```
加上`编码character set`
```
mysql> CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
```
## 查看数据库创建信息
```
mysql> SHOW CREATE DATABASE t1;
+----------+------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------+
| t1 | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)
```
## 修改数据库
### 编码格式
命令规范
```
ALTER {DATABASE|SCHEMA} [sb_name] [DEFAULT] CHARACTER SET [=] charset_name
```
实例
```
mysql> ALTER DATABASE t1 CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
```
## 删除数据库
命令规范
```
DROP {DATABASE|SCHEMA} [IF EXISTS] db_name
```
实例
```
mysql> DROP DATABASE IF EXISTS t1;
Query OK, 0 rows affected (0.07 sec)
```
## 打开数据库
命令规范
```
USE db_name
```
实例
```
mysql> USE t2;
Database changed
```
## 查看打开的数据库
```
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| t2 |
+------------+
1 row in set (0.00 sec)
```
# 数据表
关系型数据库是一张二维的表, 设计尽量满足3范式
数据表的创建不能脱离项目
## 创建数据表
语法规范
```
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
....
)
```
实例
```
mysql> CREATE TABLE tb1 (
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(7,2) UNSIGNED
-> );
Query OK, 0 rows affected (0.08 sec)
```
## 查看数据表
命令规范
```
SHOW TABLES [FROM db_name]
```
实例-查看当前数据库的表
```
mysql> SHOW tables;
+--------------+
| Tables_in_t2 |
+--------------+
| tb1 |
+--------------+
1 row in set (0.00 sec)
```
实例-查看其他库的表
```
mysql> SHOW TABLES FROM test;
Empty set (0.00 sec)
```
## 删除数据表
```
DROP TABLE IF EXISTS [tb_name]
TRUNCATE TABLE IF EXISTS [tb_name]
DELETE TABLE [tb_name]
```
实例
```
DROP TABLE IF EXISTS `hd_adv`;
TRUNCATE TABLE IF EXISTS `hd_adv`;
DELETE TABLE `hd_adv`;
```
DROP删除整个表
TRUNCATE 清空表, 速度快, 但是不可回滚操作
DELETE 清空表, 速度慢, 但是可回滚操作
## 查看数据表结构
命令规范
```
SHOW COLUMNS FROM tb_name
SHOW FULL COLUMNS FROM tb_name;
desc / explain tb_name;
```
实例
```
mysql> SHOW COLUMNS FROM tb1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(7,2) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
```
## 查看DDL, 数据库的创建信息
```sql
show create table tb_name;
```
### 实例
```sql
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`name` varchar(5) NOT NULL DEFAULT '',
`sex` tinyint(3) unsigned NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
```
## 修改数据表结构
### 添加一列
命令规范
```
ALTER TABLE tb_name ADD col_name col_definition [FIRST|AFTER col2_name]
```
> 不加[FIRST|AFTER col2_name] 默认在最后一列加
> 实例
```
mysql> show columns from tb5;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| sex | enum('1','2','3') | YES | | 3 | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.09 sec)
mysql> ALTER TABLE tb5 ADD password VARCHAR(30) NOT NULL AFTER username;
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM tb5;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | varchar(30) | NO | | NULL | |
| sex | enum('1','2','3') | YES | | 3 | |
+----------+----------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
```
### 添加多列
命令规范
```
ALTER TABLE tb_name ADD (col_name col_name col_definition, ...);
```
### 修改表编码
修改默认的字符集
```
alter table tb1 default character set utf8;
```
```
mysql> ALTER TABLE tb1 CONVERT TO CHARACTER SET utf8;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
## 删除列
命令规范
```
ALTER TABLE tb_name DROP col_name;
```
实例
```
mysql> ALTER TABLE tb5 DROP password;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
```
## 修改列名称
```
mysql> ALTER TABLE tb1 CHANGE username username0 VARCHAR(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
## 插入记录
命令规范
```
INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...)
```
实例
```
mysql> INSERT tb1 VALUES('zxc', 50, 9527);
Query OK, 1 row affected (0.02 sec)
```
实例
```
mysql> INSERT tb1(username, age) VALUES('Tom', 20);
Query OK, 1 row affected (0.00 sec)
```
## 索引
### 查看索引
```sql
show index from tb_name;
```
### 创建索引
```sql
ALTER TABLE tb_name ADD INDEX index_name (column list)
ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column list);
ALTER TABLE tbl_name ADD UNIQUE index_name (column list);
```
### 删除索引
```sql
ALTER TABLE tb_name DROP INDEX index_name ;
ALTER TABLE tbl_name DROP PRIMARY KEY index_name;
ALTER TABLE tbl_name DROP UNIQUE index_name;
```
### 重建索引
```sql
REPAIR TABLE tbl_name QUICK;
```
## 查看记录
精简版语法规范
```
SELECT expr,... FROM tb_name
```
实例
```
mysql> SELECT * FROM tb1;
+----------+------+---------+
| username | age | salary |
+----------+------+---------+
| zxc | 50 | 9527.00 |
| Tom | 20 | NULL |
+----------+------+---------+
2 rows in set (0.02 sec)
```
## 记录约束简介
## 非空约束
创建一个新表
```
mysql> CREATE TABLE tb2(
-> username VARCHAR(20) NOT NULL,
-> age TINYINT UNSIGNED
-> );
Query OK, 0 rows affected (0.08 sec)
```
查看表结构是否正确
```
mysql> show columns from tb2;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
```
插入一个空值
```
mysql> INSERT tb2 VALUES(NULL, 25);
ERROR 1048 (23000): Column 'username' cannot be null
```
设置为非空之后, 不能设置为null
但是这样却不会报错,只会有警告
```
mysql> INSERT tb2(age) VALUES(12);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1364 | Field 'username' doesn't have a default value |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)
```
### 增加非空约束
```
mysql> ALTER TABLE tb1 MODIFY username VARCHAR(20) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
## 主键约束
PRIMARY KEY
每张表只能存在一个主键
为了保证记录的唯一性
主键自动为`NOT NULL`
```
mysql> CREATE TABLE tb3(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.04 sec)
```
> 注意这里的`AUTO_INCREMENT`, 主键可以不是`AUTO_INCREMENT`, 但是`AUTO_INCREMENT`一定会是主键
表结构
```
mysql> show columns from tb3;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
```
表记录
```
mysql> insert tb3(username) VALUES('TOM');
Query OK, 1 row affected (0.01 sec)
mysql> insert tb3(username) VALUES('Jaime');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb3;
+----+----------+
| id | username |
+----+----------+
| 1 | TOM |
| 2 | Jaime |
+----+----------+
2 rows in set (0.00 sec)
```
### 增加主键约束
```
mysql> ALTER TABLE tb1 ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE tb1 CHANGE id id INT AUTO_INCREMENT;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
或者合在一起
```
mysql> ALTER TABLE tb1 CHANGE id id INT PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
### 删除主键约束
```
mysql> ALTER TABLE tb1 CHANGE id id INT;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE tb1 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
## 唯一性约束
UNIQUE KEY
仍然是保证记录唯一性, 但是允许记录为空(有且仅有一个，感觉有点怪怪的哈)
```
mysql> CREATE TABLE tb4(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> age TINYINT UNSIGNED
-> );
Query OK, 0 rows affected (0.04 sec)
```
查看表结构
```
mysql> show columns from tb4;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
```
插入重复的记录
```
mysql> INSERT tb4(username, age) VALUES('Jaime', 24);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT tb4(username, age) VALUES('Jaime', 25);
ERROR 1062 (23000): Duplicate entry 'Jaime' for key 'username'
```
### 添加唯一性约束
```
mysql> ALTER TABLE `tb1` ADD unique(`username`);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
### 删除唯一性约束
只要设置唯一性约束，就会建立索引，删除唯一性约束只需要删除索引就行了
```
mysql> ALTER TABLE tb1 DROP INDEX username;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
## 默认值约束
默认值
插入记录时候如果没有明确赋值, 则自动赋默认值
```
mysql> CREATE TABLE tb5(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> sex ENUM('1', '2', '3') DEFAULT '3'
-> );
Query OK, 0 rows affected (0.04 sec)
```
表结构
```
mysql> show columns from tb5;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| sex | enum('1','2','3') | YES | | 3 | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
```
插入记录
```
mysql> INSERT tb5(username) VALUES('Jaime');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb5;
+----+----------+------+
| id | username | sex |
+----+----------+------+
| 1 | Jaime | 3 |
+----+----------+------+
1 row in set (0.00 sec)
```
## 更新记录
命令规范
```
UPDATE tb_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
```
实例
```
mysql> UPDATE tb1 SET username='Jaime' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
```
# 查询记录
## ORDER BY 排序
## GROUP BY 分组
## LIMIT 分页
## 正则表达式
以st开头的记录
```
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
```
包含某个字符串的记录
```
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
```
## 判断字段是否包含某个字符串
1. like
```
SELECT * FROM member WHERE email like "%@gmail.com%";
```
2.find_in_set();
```
SELECT * FROM member WHERE find_in_set("@gmail.com", email);
```
# 多实例运行
找到mysql安装目录(linux的etc目录)下的my.conf或者my.ini
打开配置
```ini
[client]
port=3307
[mysqld]
port=3307
datadir="E:/db/"
```
修改以上几个配置, port和datadir就行了,把配置保存为`my3307.ini`
运行实例
```ini
mysqld --defaults-file=my3307.ini
```
# 导入/导出
数据和结构就差一个-d参数
## 导出结构
把整个数据库结构一起导出
``` sql
mysqldump.exe -uroot -proot -h192.168.1.99 -d cashier>e:\cashier.sql
```
导出表(cashier.node表 cashier.order表)
``` sql
mysqldump.exe -uroot -proot -h192.168.1.99 -d cashier node order>e:\no.sql
```
## 导出表数据(包含结构)
``` sql
mysqldump.exe -uroot -proot -h192.168.1.99 cashier>e:\cashier.sql
```
导出表(cashier.node表 cashier.order表)
``` sql
mysqldump.exe -uroot -proot -h192.168.1.99 cashier node order>e:\no.sql
```
## 导入结构
```
mysql -uroot -p123456 cashier< haidao.sql
```
## 导入表数据
这种速度极快, 极快, 我1kw的数据, 10来s左右就全部导入完了
```
use test; # 切换到test库
LOAD DATA local INFILE 'D:/xsu/Desktop/test mysql/insert.sql' INTO TABLE hd_order; # 导入数据
```
## 导入表结构和数据
```sql
use database_name;
source /home/datafilename.sql
```
# 其他
## CHANGE 和 MODIFY
change可以更改的属性包括名称
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
modify可以更改不包括名称属性
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
## STRING
编程语言中能想到的字符串处理, sql一般都有
