常用语法
查询数据库信息
添加运行函数插入数据：
delimiter ;;
create procedure idata3()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into `t**********l` ( `operate_code`, `operate_name`, `sign_id`) values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata3();
查询MySQL的状态变量的值，结果一样，查询方式：(1)SHOW GLOBAL STATUS;
mysql 修改字段长度
alter table news modify column title varchar(130);
alter table 表名 modify column 字段名 类型;
如：news 表里的title 字段 原来长度是 100个字符，现长度要改成130个字符
alter table news modify column title varchar(130);
只返回不同的值
SELECT DISTINCT a FROM test1 GROUP BY a
select distinct id from products；
限制结果
从行5开始的第5行，第一个数位置，第二个为要检索的行数；
select name from pr limit 5,5
从mysql5开始，从第三行开始取4行
limit 4 offset 3
排序：
默认ASC升序，DESC降序，先降序排列，相同的升序排列如下，找出最贵的两个，limit要放在orderby后面
select * from table order by prod DESC ,name limit 2
过滤数据
表范围 between and
组合 语句用and或者or，and优先权更高。
in更好
列出除了2012,2013两个价格的产品
where price not in（2012,2013）
常用通配符%
where table like ‘jet%’
%anvi%匹配任意位置
s%e s开头e结尾
下划线匹配单个字符而不是多个字符。
正则表达式也可以应用于搜索。
联结表
外键：某表中的一列包含另一张表的主键
简单sql
SELECT operator_no,operator_name,transit_depot_no,operator_role,transit_depot_name
FROM
tdop_operator_info WHERE transit_depot_no='755WF' LIMIT 0,10
SELECT operator_no,operator_name,transit_depot_no,operator_role,transit_depot_name
FROM
tdop_operator_info WHERE operator_no='955'
UPDATE tdop_operator_info SET operator_role='W' WHERE operator_role=NULL
DELETE FROM tdop_operator_info WHERE operator_no='120'
INSERT INTO tdop_operator_info(operator_no,operator_name,transit_depot_no,operator_role,transit_depot_name)
VALUES
('9557','zhanghang','755WF','W','黄田')
REPLACE INTO tdop_operator_info(operator_no,operator_name,transit_depot_no,operator_role,transit_depot_name)
VALUES
('9557','zhanghan','755WF','W','黄田')
修改表：
alter TABLE tdop_operator_info add operator_role VARCHAR(16) not NULL DEFAULT 'W'
alter TABLE tdop_operator_info add del_flag VARCHAR(8) DEFAULT '0'
建表语句：
CREATE TABLE tdop_crate_flight_match
(
id int identity(1,1) PRIMARY KEY COMMENT 'id',
crate_type varchar(16) COMMENT '舱位类型（1主舱，2下前舱，3下后舱）',
plane_type varchar(16) COMMENT '飞‘类型',
is_special_crate varchar(8) COMMENT'(1是，0不是)',
special_crate_name varchar(16) COMMENT '特殊板位名称',
crate_position varchar(8) COMMENT'板箱位置',
crate_type VARCHAR(8) COMMENT '板箱类型'
)
COMMENT='板箱机型匹配表'
ALTER TABLE tdop_crate_task DROP COLUMN crate_type;
ALTER table tdop_crate_task add `crate_type` varchar(16) DEFAULT NULL COMMENT '板箱类型';
ALTER table tdop_crate_task add `seat_type` varchar(2) DEFAULT NULL COMMENT '舱位类型{1:板箱、2：腹舱、3：尾箱}';
ALTER TABLE tdop_flight_task add `final_load_date` varchar(32) DEFAULT NULL COMMENT '截载日期';
ALTER TABLE tdop_flight_task add `final_load_time` varchar(16) DEFAULT NULL COMMENT '截载时间';
ALTER TABLE tdop_crate_flight_match DROP COLUMN seat_type ;
ALTER TABLE tdop_crate_flight_match ADD `seat_type` varchar(2) DEFAULT NULL COMMENT '舱位类型（1:主舱，2:下前舱，3:下后舱）' ;
ALTER TABLE tdop_crate_flight_match auto_increment=1
CREATE TABLE 'dds_appear_icon' {
'id' int(10) NOT NULL AUTO_INCREMENT,
'src_deptCode' VARCHAR(16) NOT NULL COMMENT '当前网点',
'start_time' VARCHAR(32) NOT NULL COMMENT '显示按钮开始时间',
'end_time' VARCHAR(32) NOT NULL COMMENT '显示按钮结束时间',
'create_time' timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
'modify_time' timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间'
PRIMARY KEY ('id')
}ENGINE=INNODB AUTO_INCRAMENT=17 DEFAULT CHARSET=utf8 COMMENT ='按钮显示时间段表'
索引操作：
1.添加PRIMARY KEY（主键索引）
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE (
`column`
)
alter table tc_dds_appear_icon drop index `src_deptCode`;
alter table tc_dds_appear_icon add unique key `srcDeptCode` (`src_deptCode`);
alter table table_name add unique key `new_uk_name` (`col1`,`col2`);
3.添加INDEX(普通索引)
3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT (
`column`
)
5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
删除：
alter table student drop index idx_name;
查看索引 show index from student\G
replace用法：
replace INTO tt(gg) VALUES ('WW')
（1）replace into原理
replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中，
如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则没有此行数据的话，直接插入新数据。
（2）replace into的应用注意事项
1）插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
2）如果数据库里边有这条记录，则直接修改这条记录；如果没有则，则直接插入，在有外键的情况下，对主表进行这样操作时，因为如果主表存在一条记录，被从表所用时，直接使用replace into是会报错的，这和replace into的内部原理是相关（ps.它会先删除然后再插入）。
3）正确做法是- 即先删除该条存在的数据，然后再次插入这条数据，这和外键约束相悖呢，因此只能采用update和insert这样的组合，来应对外键约束
原文：http://blog.csdn.net/helloxiaozhe/article/details/77427266
SQL中的DML、DDL以及DCL是什么？
DML（data manipulation language）是数据操纵语言：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。
DDL（data definition language）是数据定义语言：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。
DCL（DataControlLanguage）是数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。
查询数据库所有表
select table_name
from information_schema.tables
where table_schema='db1'
select table_name
from information_schema.tables
有就不管，没有就插入
insert ignore into
有就修改，没有就插入
replace into
如果存在就修改，不存在就插入
INSERT INTO t_pro_gift
( _id,
name,
age )
VALUES
( 1,
'hello',
24 )
ON DUPLICATE KEY UPDATE
id = 1,
name = 'world',
age = 55
连接查询
SELECT a.id as id1,b.id as id2 FROM (SELECT * FROM tc_unmanned_store_grid WHERE id>565) a INNER JOIN tc_unmanned_store_grid_append b ON a.uniqueId=b.unique_id
sql优化
引自 公众号 业余草 欢迎加，很受益： https://mp.weixin.qq.com/s/PxXs3xOeUbMbQ0Vh2Cjj3g
查看状态
show status
查看进程
show processlist
-----------------------慢日志配置----------------------------
是否开启慢查询日志，1表示开启，0表示关闭。
slow-query-log = 1
MySQL数据库慢查询日志存储路径
slow-query-log-file =D:\tool\mysql\mysql-5.7.26\log\slow\mysql-slow.log
慢查询阈值，当查询时间多于设定的阈值时，记录日志。单位:秒
long_query_time = 0.01
开启 记录没有使用索引查询语句
log-queries-not-using-indexes = on
慢日志配置查询
show variables like ‘slow_query%’;
show variables like ‘long_query_time’;
MySQL 提供 mysqldumpslow 工具对日志进行分析。我们可以使用 mysqldumpslow --help 查看命令相关用法。
常用参数如下：
-s：排序方式，后边接着如下参数
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录书
at：平均查询时间
-t：返回前面多少条的数据
-g：翻遍搭配一个正则表达式，大小写不敏感
---------------分析sql-------------
1
explain + sql
exlain解释
字段解释：1) id：select 查询序列号。id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行
- select_type：查询数据的操作类型，其值如下：
simple：简单查询，不包含子查询或 union
primary:包含复杂的子查询，最外层查询标记为该值
subquery：在 select 或 where 包含子查询，被标记为该值
derived：在 from 列表中包含的子查询被标记为该值，MySQL 会递归执行这些子查询，把结果放在临时表
union：若第二个 select 出现在 union 之后，则被标记为该值。若 union 包含在 from 的子查询中，外层 select 被标记为 derived
union result：从 union 表获取结果的 select
-
table：显示该行数据是关于哪张表
-
partitions：匹配的分区
-
type：表的连接类型，其值，性能由高到底排列如下：
system：表只有一行记录，相当于系统表
const：通过索引一次就找到，只匹配一行数据
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列
range：只检索给定范围的行，使用一个索引来选择行。一般使用between、>、<情况
index：只遍历索引树
ALL：全表扫描，性能最差
注：前5种情况都是理想情况的索引使用情况。通常优化至少到range级别，最好能优化到 ref
-
possible_keys：指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能
-
key：显示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引查询
-
key_len：表示索引中使用的字节数，通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好 显示的是索引字段的最大长度，并非实际使用长度
-
ref：显示该表的索引字段关联了哪张表的哪个字段
-
rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
-
filtered：返回结果的行数占读取行数的百分比，值越大越好
-
extra：包含不合适在其他列中显示但十分重要的额外信息，常见的值如下：
using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该值，应该优化 SQL
using temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现该值，应该优化 SQL
using index：表示相应的 select 操作使用了覆盖索引，避免了访问表的数据行，效率不错
using where：where 子句用于限制哪一行
using join buffer：使用连接缓存
distinct：发现第一个匹配后，停止为当前的行组合搜索更多的行
注意：出现前 2 个值，SQL 语句必须要优化。
2
profile
查询是否启用
select @@profiling
启用
set profiling = 1;
show profiles
选择具体query id
show profile for query 209
获取 CPU、 Block IO 等信息
show profile block io,cpu for query 209;
show profile cpu,block io,memory,swaps,context switches,source for query 160;
show profile all for query 240;
优化
减少中间结果，索引
查所需字段，字段类型尽量简约，减少中间结果 AB查询，B小用IN A小用with，部分场景，join代替子查询，join不会创建临时表
添加冗余，减少跨表，增加索引，减少回表和filesort，join 被驱动表加索引（走Index Nested-Loop Join），避免索引失效% or…
分库分表，读写分离
相关参数：
服务器参数：
1 内存相关
sortbuffersize 排序缓冲区内存大小
joinbuffersize 使用连接缓冲区大小
readbuffersize 全表扫描时分配的缓冲区大小
2 IO 相关
Innodblogfile_size 事务日志大小
Innodblogfilesingroup 事务日志个数
Innodblogbuffer_size 事务日志缓冲区大小
Innodbflushlogattrx_commit 事务日志刷新策略 ，其值如下：
0：每秒进行一次 log 写入 cache，并 flush log 到磁盘
1：在每次事务提交执行 log 写入 cache，并 flush log 到磁盘，符合持久化原则！！
2：每次事务提交，执行 log 数据写到 cache，每秒执行一次 flush log 到磁盘
3 安全相关
expirelogsdays 指定自动清理 binlog 的天数
maxallowedpacket 控制 MySQL 可以接收的包的大小
skipnameresolve 禁用 DNS 查找
read_only 禁止非 super 权限用户写权限
skipslavestart 级你用 slave 自动恢复
4 其他
max_connections 控制允许的最大连接数
tmptablesize 临时表大小
maxheaptable_size 最大内存表大小
其他：
1 内存相关
内存的 IO 比硬盘的速度快很多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的 IO
2 磁盘 I/O 相关
- 使用 SSD 或 PCle SSD 设备，至少获得数百倍甚至万倍的 IOPS 提升
- 购置阵列卡同时配备 CACHE 及 BBU 模块，可以明显提升 IOPS
- 尽可能选用 RAID-10，而非 RAID-5
3 配置 CPU 相关
在服务器的 BIOS 设置中，调整如下配置：
- 选择 Performance Per Watt Optimized（DAPC）模式，发挥 CPU 最大性能
- 关闭 C1E 和 C States 等选项，提升 CPU 效率
- Memory Frequency（内存频率）选择 Maximum Performance
扫一扫
2209
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
