侧边栏

初识MySQL

发布于 2016-10-26 14:52:10| 分类于后端/数据库

最近在练习一个后台小项目，随便规划了几个需求之后开始搭环境（好吧其实就是写了几个页面然后上ThinkPHP），当正儿八经的开始写后台项目的时候才发现，我对于MySQL的了解远远不够，赶紧恶补一番。

数据类型

参考资料：

MySQL建表时选择最佳的数据类型

选择合适的数据类型，最主要的目的是为了用较小的存储代价换来较高的数据库性能。此外，临时更改字段的数据类型可能会造成数据的丢失。

MySQL主要有下面几种类型的数据

整形
- tinyint 1字节
- smallint 2字节
- int 4字节
- BIGINT 8字节
浮点型
- float 4字节
- double 4字节
- DECIMA[m,d] 精度小数 m 总位数 d 小数点右边的位数
字符串
- char(位数) 定常字符
- varchar(位数) 变长字符
- text 65532个字符
- MEDIUMBLOB 2^24个字符
- enum('1'[,'2']) 枚举
日期
- date 日期
- time 时间
- datetime 日期时间

一个常见的SQL数据类型的例子

sql

create table stu(
    id int(6) auto_increment primary key,
    stuNUm varchar(6),
    stuName varchar(20),
    stuAge tinyint(2),
    stuSex enum("1","2"),
    stuTel varchar(20)
)

desc stu;

数字类型

数字类型又可以分为整数类、小数类和数字类。

数字类

所谓的“数字类”，就是指 DECIMAL 和 NUMERIC，它们是同一种类型（在phpmyadmin中并没有看见NUMERIC），这种类型又叫做定点数，指的是小数的位数固定，整数部分最大为65，小数部分最大为30。实际上这种数据类型是将数字以字符串形式保存，在运算中不会失真，比较适合用于“价格”、“金额”这样对精度要求不高但准确度要求非常高的财务数据字段。

小数类

小数即为浮点数，包括FLOAT（占4个字节）和DOUBLE（占8个字节）类两种。浮点数的优点在于表达的数值范围十分大，可以表示很小或者很大的数值。缺点是浮点数存在误差问题，需要尽量避免做浮点比较。实际上，平常项目所用到的数据哪里会需要那么大的数值范围呢。

整数类

实际上数据库用到最多的数据类型就是整数类，根据所占内存大小分为了TINYINT,SMALLINT,MEDIUMINT,INT和BIGINT这几种，分别占1，2，3，4，8个字节。

字符串类型

字符串类型又包括了字符和文本。

字符

CHAR：指定字节长度，并且在存储时如果完全占据指定内存（即使内容数据实际大小比指定大小要小）；

VARCHAR：如果并不确定数据的实际长度，只知道他的阈值（即最大长度），则使用可变长度的VARCHAR类型更合适，这会根据数据的长度自动调节变量所占内存大小（为数据长度+1，多余的一个字节用来存储长度）；

长文本

如果需要存储很大字节的字符串（比如博客，新闻等）可以使用TEXT来存储，此外还有TINYTEXT,MEDIUMTEXT,LONGTEXT。

大数据

如果要在数据库存储二进制内容，比如图片，可以使用BLOB数据类型。

不过在实际场景中，一般文件都是放在OSS或者磁盘上面，数据库里面只通过字符串保存对应的url地址。

枚举和集合

ENUM：最多可以定义 65535 种不同的字符串从中做出选择，只能并且必须选择其中一种，占用存储空间是一个或两个字节

SET：最多可以有 64 个成员，可以选择其中的零个到不限定的多个，占用存储空间是一个到八个字节。

日期和时间

DATE：如果数据对日期敏感，而对时间没有要求则可以使用DATE类型，需要8个字节

TIME：在某些情况下可能只需要单独关注时间则使用TIME类型，需要8个字节

DATETIME:包含日期和时间，需要8个字节

TIMESTAMP：用来保存时间戳，只需要4个字节，且可以灵活转换为日期时间，因此使用最为频繁，需要注意的时间戳可能受时区影响。

如何选择合适的数据类型

选择小数据类型

通常情况下，使用占用内存较小的数据类型来存储和读取数据更好：数据类型越小，在磁盘，内存和处理器缓存中占据的空间很小，只需要很小的CPU处理消耗。

然而，必须保证没有低估需存储数据的数值范围，否则就是“捡了芝麻丢了西瓜”，毕竟数据库就是用来存储并读取数据的。

避免使用NULL

如果有需要，最好将字段设置为NOT NULL,空值字段的查询是一件很难的事情，因为空值使得索引、索引分析以及数值比较更加复杂，如果确实需要使用NULL，考虑使用0、特殊值或者一个空的字符串来代替更为合适。

常见的数据对应的类型

其中有的数据前面已经提到过了，这里再简单总结一下：

定点数字类型尽量只在对小数精确计算时才使用、如存储财务数据
数据量较大时、建议把实数类型转为整数类型，因为浮点数不精确，而定点数的计算代价昂贵
如果没有需要，不必对浮点数进行精度指定
很短的字符串或所有值都接近同一个长度，使用CHAR，如存储密码的MD5值
经常变更的列最好使用CHAR，因为CHAR产生的碎片少
对于Unicode编码的字符集，最好使用VARCHAR
很大的数据，比如BOLB和TEXT最好存储在单独的表中
如果没有意外，最好使用TIMESTAMP来保存时间，占用内存较小

CRUD

使用数据库主要就是围绕增删查改而来的。

查询

SELECT 是数据库中最重要的操作！我们将数据放在数据库中，并在需要的时候通过某种方式查询得到正确的数据.

普通查询
整理结果
子查询
联结
组合

详情语法如下：

sql

select 字段列表 from 表名 [where 条件] [order by 字段 asc|desc] [limit 起始位置，长度] [group by 字段名称（分组）]

查询部分字段

sql

# 多个字段使用逗号分隔
select id,stuName from stu;
# 使用as为字段新增别名
select stuName as name from txm_test;

order by 排序：asc升序，desc降序

sql

select * from stu order by id desc;

截取

sql

# 索引值是从0开始的
select * from stu limit 1,3;

分组

sql

# 显示的是每个组的一条数据记录
select * from stu group by stuSex;

where条件

比较符号 > < >= <= = <>（不等于）

sql

select * from stu where stuSex = 1;

逻辑运算 and or

sql

select * from stu where id > 1 and id < 3;

模糊搜索：字符串中含有某个关键词，就能找到

sql

# %占位符表示0个或多个字符，_表示1个字符
select * from stu where stuName like '%ta%';
# between ... and ...连续区间
# in 不连续范围，相当于连续使用or

增加

sql

insert [into] 表名 [(列名...)] values (值...);

删除

sql

delete from 表名称 [where 删除条件];

修改

sql

update 表名称 set 列名称=新值 [where 更新条件];

常见情形与对应语句

分组

可以使用聚合函数返回某个供应商提供的产品数目，如果需要返回每个供应商的产品数目，则需要使用分组。

sql

SELECT vend_id, COUNT(*) AS num FROM products GROUP BY vend_id

但是：分组并不是用来返回二维数组的，mysql也并不能实现这样的功能。换个角度看，某个分组内部各行应该具有某些共同点才行，这个共同点就是有GROUP BY指定的，如果group子句的字段都不相同，则肯定会划分到不同的分组中去，造成一些困惑。

针对分组一个常见的需求是：返回每个分组包含的行，有一个折中的办法就是使用GROUP_CONCAT函数。

sql

SELECT GROUP_CONCAT(prod_id), GROUP_CONCAT(prod_name) FROM products GROUP BY vend_id;

使用分组的一个明显标志就是：如果与聚集函数一起使用列或表达式，则必须使用分组

子查询

如果某次查询的条件依赖于另外的查询结果，则可以使用子查询，如：筛选购买了某件商品的所有顾客

sql

SELECT cust_id FROM orders WHERE order_num IN (
    SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

子查询的嵌套并没有限制，如果还需要根据cust_id查询对应顾客的详细信息，则可以将上面这条语句的查询结果再次作为从customer表中查询的筛选条件。

子查询的另外一个用处是计算字段，如：查询customers表中每个顾客的订单，订单保存在ordesr表中（尽管这种情况更合适的做法是使用联结）

sql

SELECT 
    cust_id, 
    (SELECT COUNT(*) FROM orders WHERE customers.`cust_id` = orders.`cust_id`) AS orders FROM customers;

联表

把数据拆分在不同的表中很有必要，表之间通过外键相连。比如要查询供应商和供应商对应的产品

sql

SELECT	vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.`vend_id` = products.`vend_id`;

实际上联结更标准的写法是

sql

SELECT	vend_name, prod_name, prod_price FROM vendors INNER JOIN  products ON vendors.`vend_id` = products.`vend_id`;

预处理语句

预处理语句（Prepared Statements)，是一种编译过的要执行的SQL语句模板，可以使用不同的变量参数定制它。

各种语言的数据库SDK一般提供了预处理SQL语句的功能，其优点有

查询语句只需要被解析一次，就可以使用不同的参数执行多次，通过使用预处理语句可以避免重复分析、编译、优化的环节，也就是说使用预处理语句可以让数据库服务器的效率提升
预处理语句会减少SQL注入情形的发生

php

// 占位符 :name
$sql = 'SELECT * FROM shop_admin WHERE id < :id';
$sth->bindParam(':id',$id); // 绑定数据
$sth->execute();

// 占位符 ?
$sql = 'INSERT INTO shop_admin (username, password) VALUES (?, ?)';
$sth->bindParam(1,$username); // 1 表示第一个问号占位符
$sth->bindParam(2,$password); // 2 表示第二个问号占位符
$sth->execute();

// 如果不想挨个绑定数据，也可以在执行时传入数组
$sth->execute(array('xxx','ooo'))

给出的建议是：尽量不要在后台代码中根据前端传过来的参数自己手动拼接SQL语句，而是进行使用预处理语句来进行数据库操作。

远程连接MySQL

在本地开发中，往往需要链接远程的MySQL服务器，MySQL默认是关闭了远程连接的，需要做一些配置。

mysql8及之前的版本

参考：

MySql创建本地用户和远程用户并赋予权限

bash

# vim /etc/mysql/my.cnf
vim /etc/mysql/mysql.conf.d/mysqld.cnf

注释掉绑定的本地地址

bash

bind-address=127.0.0.1 ==> #bind-address=127.0.0.1

开放远程用户的访问权限

bash

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456";

其中%代表任意ip，也可以指定其他ip地址，IDENTIFIED后面接登录密码，这里不需要跟本地的密码一样，注意密码强度。

刷新缓冲，重启mysql

bash

flush privileges;
service mysql restart

现在就可以在本地使用SQLyog来访问外部数据库了

bash

mysql -h IPaddress -uROOT -pPASSWARD

mysql8之后的版本

参考： https://blog.csdn.net/skyejy/article/details/80645981

mysql8之后，需要先创建账号，再赋予权限

bash

# 先创建
create user 'remote_admin'@'%' identified by '123456';

# 再赋予权限
GRANT ALL PRIVILEGES ON *.* TO remote_admin@"%" WITH GRANT OPTION;

如果是想要创建原始的账号密码，可以使用

bash

create user 'native_root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '123456';

#MySQL

你要请我喝一杯奶茶？

本站文章均为本人原创，参考文章我都会在文中进行声明，也请您转载时附上署名。

初识MySQL

相关概念

创建流程

主键/空/默认值

数据类型

数字类型

字符串类型

日期和时间

如何选择合适的数据类型

常见的数据对应的类型

CRUD

查询

增加

删除

修改

常见情形与对应语句

分组

子查询

联表

预处理语句

远程连接MySQL

mysql8及之前的版本

mysql8之后的版本

你要请我喝一杯奶茶？

初识MySQL

相关概念 ​

创建流程 ​

主键/空/默认值 ​

数据类型 ​

数字类型 ​

字符串类型 ​

日期和时间 ​

如何选择合适的数据类型 ​

常见的数据对应的类型 ​

CRUD ​

查询 ​

增加 ​

删除 ​

修改 ​

常见情形与对应语句 ​

分组 ​

子查询 ​

联表 ​

预处理语句 ​

远程连接MySQL ​

mysql8及之前的版本 ​

mysql8之后的版本 ​

你要请我喝一杯奶茶？

相关概念

创建流程

主键/空/默认值

数据类型

数字类型

字符串类型

日期和时间

如何选择合适的数据类型

常见的数据对应的类型

CRUD

查询

增加

删除

修改

常见情形与对应语句

分组

子查询

联表

预处理语句

远程连接MySQL

mysql8及之前的版本

mysql8之后的版本