MySQL常见基础写法

本文档旨在为开发者提供一份全面且实用的MySQL常用操作指南。随着Docker容器化技术的普及，在容器环境下使用MySQL已成为一种主流选择。本文将从安装配置开始，系统性地介绍MySQL的各项基础功能，包括基本查询、数据操作、表管理等实践知识。

这篇文章适合以下读者：

初学MySQL的开发者
需要在Docker环境中使用MySQL的工程师
想要系统回顾MySQL基础知识的程序员
寻找MySQL常用操作速查手册的开发人员

安装与配置

使用Docker安装MySQL

拉取MySQL 8镜像
```
docker pull mysql:8
```

运行MySQL容器

docker run --name mysql8 \
  -e MYSQL_ROOT_PASSWORD=your_password \
  -p 3306:3306 \
  -v mysql_data:/var/lib/mysql \
  -d mysql:8

检查容器状态
```
docker ps
```

连接MySQL

使用命令行连接
```
mysql -h localhost -P 3306 -u root -p
```
使用Docker容器内连接
```
docker exec -it mysql8 mysql -u root -p
```

常见SQL语句

基本查询

选择数据

-- 选择所有列的数据
select * from `table`;

-- 从表中选择某一列
select `col` from `table`;

-- 当row数据重复时，只列出一条
select distinct `col` from `table`;

限制和排序

-- 限制查询多少条
select `col` from `table` limit 0, 2;

-- 排序数据 order by 字句优先级高
select `col` from `table` order by `col` [limit 0, 2];

-- 可通过指定DESC关键字指定为倒序（默认为升序）
select `col`, `col1` from `table` order by `col` desc, `col1`;

条件查询

WHERE子句

-- 基本过滤
select `col` from `table` where `col`='xxx';

-- 不匹配
select `col` from `table` where `col`!='xxx';

-- 空值
select `col` from `table` where `col` is NULL;

-- 范围
select `col` from `table` where `col` between a and b;

逻辑操作符

-- AND操作符
select `col1`, `col2`,`col3` from `table` where `col1` === 'xxx' and `colnam2` > 'xxx';

-- OR操作符
select `col1`, `col2`,`col3` from `table` where `col1` === 'xxx' or `colnam2` > 'xxx';

-- IN操作符
select `col` from `table` where `col` in (1002, 1003);

-- NOT IN
select `col` from `table` where `col` not in (1002, 1003);

IN操作符的优点：

语法更清楚直观

计算次序更容易管理

执行速度通常更快

可以包含其他SELECT语句

模式匹配

-- 使用LIKE和通配符
select `col` from `table` where `col` like 'xxx%';

-- 使用正则表达式
select `col` from `table` where `col` regexp '^test';
select `col` from `table` where `col` regexp '1$|2$';
select `col` from `table` where `col` regexp '[123] Ton';

计算字段

-- 拼接字段
select concat(`col1`, '(',`col2`, ')') as title from `table`;

-- 算术运算
select `price`, `num`, `price`*`num` as total from `table`;

-- 函数处理
select Upper(`col1`) from `table`;

聚集函数

-- 平均值
select avg(`col`) from `table`;

-- 计数
select count(*) as row_count from `table`;

-- 最大值
select max(`col`) as max_col from `table`;

-- 最小值
select min(`col`) as min_col from `table`;

-- 求和
select sum(`col`) as sum_col from `table`;

分组

-- 创建分组
select `col`, count(`*`) as group_count from `table` group by `col`;

-- 过滤分组
select `col`, count(*) as group_count from `table` group by `col` having group_count >= 2;

GROUP BY注意事项

可包含任意数目的列
嵌套分组时数据在最后规定的分组上汇总
每个列必须是检索列或有效的表达式
SELECT中的列必须在GROUP BY中给出
NULL值会作为一个分组返回
必须出现在WHERE之后，ORDER BY之前

表联结

-- 内联结
select `vend_name`, `prod_name`, `prod_price` 
from `vendors`, `products` 
where products.vend_id = vendors.vend_id;

联结注意事项

必须有WHERE子句，否则结果为笛卡尔积
多表联结规则保持一致
联结操作需要耗费资源
联结表越多，性能下降越快

组合查询

-- UNION示例
select `vend_id`, `prod_id`, `prod_price` 
from `products` 
where `prod_price` <= 5 
union
select `vend_id`, `prod_id`, `prod_price` 
from `products` 
where `vend_id` in (1001, 1002);

UNION规则

必须由两个或以上SELECT语句组成
每个查询必须包含相同的列
列数据类型必须兼容

全文本搜索

-- 创建全文本搜索
create table productnotes (
  note_id     int       NOT NULL AUTO_INCREMENT,
  prod_id     char(10)  NOT NULL,
  note_date   datetime  NOT NULL,
  note_text   text      NULL,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE = MyISAM;

-- 使用全文本搜索
select `note_text` 
from `productnotes` 
where match(`note_text`) against('rabbit');

-- 布尔文本搜索
select `note_text` 
from `productnotes` 
where match(`note_text`) against('heavy' in boolean mode);

注意：导入数据时不要使用FULLTEXT

数据操作

插入数据

-- 插入单条数据
insert into `customers`(
  `cust_name`,
  `cust_address`,
  `cust_city`,
  `cust_state`,
  `cust_zip`,
  `cust_country`
) values (
  'william',
  '4544 50rd Streat',
  'Montana',
  'AB',
  '12345',
  'USA'
);

-- 插入多条数据
insert into `customers`(
  `cust_name`,
  `cust_address`,
  `cust_city`,
  `cust_state`,
  `cust_zip`,
  `cust_country`
) values 
('william', '4544 50rd Streat', 'Montana', 'AB', '12345', 'USA'),
('ohayo', '123 50rd Streat', 'Beijing', 'AB', '12345', 'CN');

更新和删除数据

-- 更新数据
update `customers` 
set 
  `cust_name`='ohayo_update',
  `cust_address`='123 50rd Streat_update',
  `cust_city`='Beijing_update',
  `cust_state`='AB_up',
  `cust_zip`='1234',
  `cust_country`='CN_update' 
where `cust_name`='ohayo';

-- 删除数据
delete from `customers` where `cust_name`='william';

更新和删除的注意事项

必须带WHERE子句，否则会更新/删除所有行
保证表有主键，并在WHERE中使用
先用SELECT测试WHERE子句
使用引用完整性防止误删除

表操作

创建表

create table `customers_test` (
  `cust_id`       int           NOT NULL AUTO_INCREMENT,
  `cust_name`     char(50)      NOT NULL,
  `cust_address`  char(50)      NULL,  
  `cust_city`     char(50)      NULL,
  `cust_state`    char(8)       NULL,
  `cust_zip`      char(8)       NULL,
  `cust_country`  char(50)      NULL,
  `created_at`    timestamp     DEFAULT current_timestamp,
  `updated_at`    timestamp,
  primary key(`cust_id`)
) ENGINE=InnoDB;

修改表

-- 添加列
alter table `customers_test` add `cust_phone` char(20);

-- 删除列
alter table `customers_test` drop column `cust_phone`;

-- 重命名表
rename table `customers_test` to `customers_test1`;

-- 删除表
drop table `customers_test`;

视图

-- 创建视图
create view productcustomers as
select `cust_name`, `cust_contact`, `prod_id` 
from `customers`, `orders`, `orderitems`
where customers.cust_id = orders.cust_id 
and orderitems.order_num = orders.order_num;

-- 查看视图定义
show create view `productcustomers`;

-- 删除视图
drop view `productcustomers`;

-- 使用视图
select `cust_name`, `cust_contact` 
from productcustomers 
where prod_id = 'TNT2';

视图的用途

重用SQL语句
简化复杂的SQL操作
使用表的部分数据
保护数据访问权限
更改数据格式和表示

存储过程

存储过程的优势

简化复杂操作
保证数据完整性
防止错误
简化变动管理

数据类型

串数据类型

类型	说明
CHAR	1～255个字符的定长串
VARCHAR	长度可变，最多255字节
TEXT	最大64KB的变长文本
TINYTEXT	最大255字节
MEDIUMTEXT	最大16KB
LONGTEXT	最大4GB
ENUM	最多64K个串的集合
SET	最多64个串的集合

数值数据类型

类型	说明
BIT	位字段，1～64位
TINYINT	-128～127
SMALLINT	-32768～32767
MEDIUMINT	-8388608～8388607
INT	-2147483648～2147483647
BIGINT	-9223372036854775808～9223372036854775807
FLOAT	单精度浮点
DOUBLE	双精度浮点
DECIMAL	精度可变浮点

日期和时间类型

类型	说明
DATE	YYYY-MM-DD
TIME	HH:MM:SS
DATETIME	DATE和TIME的组合
TIMESTAMP	功能同DATETIME
YEAR	2位或4位年份

二进制数据类型

类型	最大长度
TINYBLOB	255字节
BLOB	64KB
MEDIUMBLOB	16MB
LONGBLOB	4GB

Ryo的小站

Ryo的全栈路的学习和生活记录

安装与配置

使用Docker安装MySQL

连接MySQL

常见SQL语句

基本查询

选择数据

限制和排序

条件查询

WHERE子句

逻辑操作符

模式匹配

计算字段

聚集函数

分组

GROUP BY注意事项

表联结

联结注意事项

组合查询

UNION规则

全文本搜索

数据操作

插入数据

更新和删除数据

更新和删除的注意事项

表操作

创建表

修改表

视图

视图的用途

存储过程

存储过程的优势

数据类型

串数据类型

数值数据类型

日期和时间类型

二进制数据类型