mysql 杂糅笔记

快速入门 #

docker启动 #

# 第一次启动
docker run  -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=12345678 -v ~/data-mysql:/data --name simple-mysql mysql:5.7
# 后续启动
docker start simple-mysql

概念 #

ACID:原子性 一致性 隔离 持久

PostgreSQL 最符合标准,但 mysql 一直火,原因是性能出众,在标准和性能之间选择了性能

使用 #

1. 安装 #

  • 软件包安装。略
  • 命令行安装。略
  • docker。见快速入门

2. 运行 #

开启关闭服务 #

Windows windows 可以通过系统服务查看。 在命令行中

net start mysql
net stop mysql

Linux

netstat --nlp

关闭启动

service mysql start
service mysql restart
service mysql stop

不如docker好使。

3. 配置 #

4. 可视化工具 #

  • workbench 官方免费工具
  • sequel pro Mac免费
  • navicat 大而全,但付费

以下进入正文。

语法 #

SQL 结构化查询语言。

  1. DDL data Definitionlanguage 数据定义语言。定义库 表 列 字段 索引等。create drop alter 这个 DBA 常用,开发人员很少用,定义内部结构。
  2. DML Manipulation 数据操纵语句。增删改查 insert update delete select
  3. DCL Control 数据控制语句。控制权限和安全级别

1. 数据定义 #

DDL data Definitionlanguage 数据定义语言。定义库 表 列 字段 索引等。

create drop alter 这个 DBA 常用,开发人员很少用,定义内部结构。

创建数据库 #

  • 数据库编码必须是 utf-8 or utf8mb4 二选一。后者支持emoji表情

  • 数据库引擎,必须是 InnoDB 支持事务

  • create语句创建

  • use db 选择默认数据库

  • desc 看表结构

create database db1 default charcter set utf8mb4;

查询 #

# one
SELECT ID as num, post_title from wp_posts;
# two
select *
from table
where 条件
group by 分组字段
having 分段之后,少了字段再进行过滤
order by desc
limit 5
  • 查询不重复的值
SELECT DISTINCT id from a;
  • 限制结果 limit offset
  • order by
  • desc
  • where 过滤
    • price between 5 and 10
    • price is null 检查空值
  • and or 不行就圆括号分组
  • in 推荐,更清晰,效率高,可嵌套select
  • not

通用匹配

  • like模糊查询。like能用,不滥用
    • % 任意字符出现任意次数
    • 不会匹配null
    • _ 匹配单个字符
like '六%'
like '%六'
like '%六%‘

计算字段 函数 函数在跨数据库时候,兼容不同,是否使用特定平台的函数需要做决定

  • rtrim() 去除右边的空格
  • upper(a) 大写
  • 时间处理函数。可移植很差
  • 数值函数比较统一,绝对值 sin 等
  • 聚集函数,汇总而不用检索
    • avg count max min sum

数据分组

  • group by
  • having 相比where,这个能分组

order by 和 group by 的区别 order by

  • 对产生的输出排序
  • 任意列

group by

  • 对行分组,但输出不一定分组顺序
  • 如果用到 having之类的必须带着它

子查询 #

跨表。

第一条select返回的结果,第二条需要用到。

select a from orders where b in (select c from d where e=1);
select a from orders where b in (1,2);
# join
select a from b,d,

不断嵌套sql语句,可能会影响性能,有更好的方式。后面的join

联结 #

联结。分表的好处,减少不必要的修改,可伸缩性好, scale well 信息不重复,不会浪费时间和空间。关系数据有效存储和处理,因此关系型数据库的可伸缩性远比非关系数据库要好。

select a, b, c from A,B where
A.a = B.c;

如果 列名字有歧义,应当使用完全限定名。 这个叫 equijoin 等值 联结,也叫 inner join 内连接。基于两个表之间的相等测试。

select a,b,c from A inner join B on A.id=B.id;

和刚才from俩表效果一样,但这个是一个表。用 inner join 指定部分from

用哪种都正确

select 商品名, 品牌名, 价格, 质量 from OrderItem, Products, Vendors  where Products.vend_id = Products.prod_id and A.bID = B.bID and e=1;

select ID,post_title, post_content from wp_posts where post_status='publish' and ID in
	 (select object_id from wp_term_relationships where term_taxonomy_id=
	 	(select term_id from wp_terms where name='企业简介'));

select ID,post_title from  wp_posts, wp_term_relationships,wp_terms 
where (
	wp_posts.post_status='publish' and 
	ID = wp_term_relationships.object_id and
	wp_term_relationships.term_taxonomy_id = wp_terms.term_id  and
	wp_terms.name='企业简介'
)

显示订单e=1中的物品。物品不再

高级联结 #

as 可以起别名 除了 inner join ,还有三种

  • self-join
  • natural join
  • outer join

给小张同一个公司的人推送信息

-- 子查询
select id,name,contact from A where name=(select name from A where name='小张');
-- 联结,自己查自己,self-join 性能考虑一般选这个
select c1.id,c1.name,c1.contact frome A as c1, A as c2 where c1.name=c2.name and c2.concat='小张'
-- 自然联结 ??
select C.*, O.order_num, O.order_date, OI.prod_id, OI.quantiy, OI.item_price 
from Customers as C, Orders as O, OrderItems as OI
where x
-- 外联结
-- 连接的表中没有关联行,这叫外联结
select Customers.id, Orders.num from Customers left outer join
Orders on Customers.id = Orders.id -- 左,左边表C选中所有行
select Customer.cust_id,Orders.order_num from Customers right outer join Orders on Orders.cust_id = Customers.cust_id; -- 右
-- 全外联结 full outer join
-- mysql 不支持

outer join 指定连接类型。提供left right 指定所有行的表

组合查询 #

union 组合数条sql查询

数据插入 #

存储过程 #

事务 #

要么完全执行,要么不执行

  • 事务transaction 一组sql语句
  • 回退rollback 撤销执行的sql。撤销
  • 提交 commit 为存储的sql结果写入数据库。保存
  • 保留点savepoint 设置的临时占位符,可以对他回退

回退的是 insert update delete

start transaction -- 只有开始,没有结束,会一直存在直到终端
--delete from Orders;
--rollback;

delete a where x 
commit transaction

简单的 rollback 和 commit 可以写入或撤销整个书屋

复杂的事务,可能需要部分提交或回退,这就是 savepoint了

外键 #

外键是一列,值必须在另一个表的主键中。

索引 #

切当的排序。空间换时间

数据类型 #

varchar 可变长度,比较灵活

内置函数 #

数学

  • ceil 向上取整
  • floor 向下取整
  • div 取整数
  • mod取余
  • round 四舍五入

截断 #

如果想清空计数器, truncate

建模要求 #

设计库 #

  • 数据库编码必须是 utf-8 or utf8mb4 二选一。后者支持emoji表情
  • 数据库引擎,必须是 InnoDB 支持事务

设计表 #

  • 所有字段都必须是 not null 设定default,方便未来数据迁移
  • 表里的blob text 大字段垂直拆分到其他表,只需要读的时候才select
  • 反范式设计。经常join的,再其他表里荣誉一份。比如 user_name 再 user-account 里荣誉,减少join

设计列 #

  • 自增ai,最大值在21亿左右,int注意范围
  • 选择状态少的,建议 tinytint smallint 节省空间
  • ip可以转换后方int 比如ip2long 这样省空间
  • 金钱有分,int,使用100放大缩小。
  • varchar 不建议超过 2700
  • 时间类型尽量选 timestap 4字节。也可以用int存时间,用 unix_timestamp() from_unixtime() 来转换

连接池 #

直接访问数据库,改为从pool里获取连接,连接已经预先创建好,直接分分配使用,减少创建新连接消耗的资源

假设我们不考虑磁盘 IO 和网络 IO,就很好定论了,在一个 8 核的服务器上,数据库连接数/线程数设置为 8 能够提供最优的性能,如果再增加连接数,反而会因为上下文切换导致性能下降。 考虑磁盘,ssd无需寻址和没有旋回耗时的确意味着更少的阻塞,所以更少的线程(更接近于CPU核心数)会发挥出更高的性能。只有当阻塞密集时,更多的线程数才能发挥出更好的性能。 连接数 = ((核心数 * 2) + 有效磁盘数) 如果说你的服务器 CPU 是 4核 i7 的,连接池大小应该为 ((4*2)+1)=9。 你的系统同时混合了长事务和短事务,这时,根据上面的公式来计算就很难办了。正确的做法应该是创建两个连接池,一个服务于长事务,一个服务于"实时"查询,也就是短事务


https://www.yiibai.com/mysql/basic-mysql.html https://www.w3cschool.cn/sql/ https://app.bytescout.com/sql-trainer/index.html