Mysql常用命令整理

字符函数

upper lower substr instr
select instr('杨不悔殷六侠爱上殷六侠','殷六侠') As out_put;
select length(trim('   张翠山   ')) as out_put;
select trim('aa' from 'aaaa张aaa翠山aaaaa') as out_put;
select lpad('殷素素',10,'*') as out_put;
select rpad('殷素素',10,'*') as out_put;
select replace()

数学函数

select round(-1.55,2);  # round 四舍五入
select ceil(1.2);# ceil 向上取整,返回>=该参数的最小整数
select floor(-9.99);# floor 向下取整,返回<=参数的最大整数
select truncate(1.6999,1);# truncate 截断
select mod(10,-3);# mod 取余
select 10%3;
rand 获取随机数

日期函数

select NOW();# now 返回当前系统日期+时间
select curdate();# curdate 返回当前系统日期，不包含时间
select curtime();# curtime 返回当前系统时间，不包含日期
select year(now())年；# 可以获取指定的部分，年、月、日、小时、分钟、秒
select year('1998-1-1')年;
select monthname(now())月；
select str_to_date(,)# str_to_date 将日期格式的字符转换成指定格式的日期
select date_format(now(),'%y年%m月%d日') out_put; # date_format:将日期转换为字符串
datediff:两个日期相差的天数
%Y 四位的年份
%y 二位的年份
%m 月份补零
%c 月份不补零
%d 日
%H 小时(24小时制)
%h 小时(12小时制)
%i 分钟
%s 秒

其他函数

select version();
select database();
select user();
select password('字符');# 返回字符的加密形式
select md5('字符');# 返回md5 的加密形式```

mysql 默认配置文件加载配置的顺序

mysql --verbose --help|grep -A 1 'Default options'

mysql 查看慢日志分析的开启情况

show variables like 'slow_query%';
show variables like 'long_query_time';

配置文件配置参数开启慢查询

log_output=file
slow_query_log=on
slow_query_log_file = /tmp/mysql-slow.log
log_queries_not_using_indexes=on
long_query_time = 1

慢查询日志分析器

mysqldumpslow

流程控制函数

# if 函数 
select if(10>5,'大','小');
# case函数的使用一：switch case的效果
/* 案例，查询员工的工资
	部门好=30，显示的工资为1.1倍
	部门号=40，显示的工资为1.2倍
*/
select salary 原始工资,department_id,
case department_id 
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary 
end as 新工资
from employees;
# case函数的使用二：类似于多重if
/*   
查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000，显示B级别
*/
select salary,
case 
when salary>20000 then 'a'
when salary>15000 then 'b'
else 'd'
end as '工资级别'
from employees;

分组函数

/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum求和，avg平均值，max最大值，min最小值,count计算个数
特点：
1.sum avg一般处理数值型
	max min count 可以处理字符型
2.都忽略null值
3.和distinct搭配
4.count(*)统计函数
*/

分组查询

分组中的查询分为两类
原始表 where
分组后 having

删除

1.delete 可以加where条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚

库的相关操作

create database if not exists books;
alter database books character set gbk;

表的修改

alter table 表名 add|drop|modify|change column [列类型，约束]
# 修改列表名
alter table book change column publishdate pubDate Datetime;
# 修改列的类型和约束
alter table book modify column pubdate timestamp;
# 添加新列
alter table author add column annual double;
# 删除列
alter table author drop column annual;
# 修改表明
alter table author rename to book_author;

表的复制

# 仅仅复制表的结构
create table copy like author;
# 复制表的结构+数据
create table copy2 select * from author;
# 只复制部分数据
create table copy3 select id,au_name

数据类型

一.整型
分类： tinyint smallint mediumint int/integer bigint
				1        2         3          4        8
特点
1.如果不设置无符号还是有符号，默认有符号，如果想设置无符号 需要添加unsigned
2.如果插入的数值超出了整型的范围，会根out of range异常，插入临界值
3.如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会在坐标用0填充，需要zerofill填充
二.小数
分类：float double decimal/
特点：
1.M:整数部位+小数部位
	D:小数部位
如果超过范围 则插入临界值
2.M和D都可以省略
如果是decimal,则M默认W为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
3.定点型的精确度较高，如果要求插入数值的精确度较高，如货币运算

列级约束和表级约束

# 列级约束
# 表级约束 主键、唯一、检查键(mysql没用)、外键有效
[constraint 约束名] 约束类型(字段名)
# 通用写法
create table if not exists stuinfo(
	id int primary key,
  stuname varchar(20) not null,
  age int default 18,
  seat int unique,
  majorid int,
  constraint fk_stuinfo_major foreign key(majorid) references major(id)
)

标识列更改步长和起始值

1.标识列必须和主键搭配吗？不一定，但要求是一个key
2.一个表可以有几个标识列？至多一个
3.标识列的类型只能是数值型
4.标识列可以通过设置
	show variables like '%auto_increment%';
	set auto_increment_increment=3;

事务

set autocommit=0;
start transaction; # 可以省略
savepoint a;
rollback to a; or commit

视图

# 创建视图
create or replace view xx as select xxx
# 视图的更新
和原先sql一样，但是不推荐，也不用
包含：distinct group by havng等不能对视图修改

变量

• 系统变量
  • 全局变量
    • 作用域：服务器每次启动将为所有的全局变量赋值，针对所有的会话，但不能跨重启
    • 查看所有系统的变量
      show global variables;
    • 查看满足条件的部分系统变量
      show global variables like '%char%';
    • 查看指定的某个系统变量的值
      select @@global.系统变量名；
    • 为某个系统变量赋值
      set global 系统变量名=值;
set @@global.系统变量名=值;
  • 会话变量
    • 作用域：仅仅针对当前的会话有效
    • 查看所有的会话变量
      show variables;
show session variables;
    • 查看部分的会话变量
      show variables like '%char%';
    • 查看指定的某个会话变量
      select @@tx_isolation;
    • 为某个会话变量赋值
      set @@tx_isolation='read-committed';
set session tx_isolation='read_committed';
• 自定义变量
  • 用户变量
    • 作用域：针对于当前会话有效，同于会话变量的作用域
    • 1.申明并初始化
      set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
    • 2.赋值(更新用户变量的值)
      • 方式一：通过set或select
        同第一步中的申明并初始化
      • 方式二：通过select into
        select 字段 into 变量名 from 表;
    • 3.使用(查看用户变量的值)
      select @用户变量名
  • 局部变量
    • 作用域：仅仅在定义它的begin end中有效
    • 1.申明
      declare 变量名 类型
declare 变量名 类型 default 值;
    • 2.赋值
      • 方式一：通过set或select
        set 局部变量名=值;
set 局部变量名:=值;
select @用户变量名:=值;
      • 方式二：通过select into
        select 字段 into 变量名 from 表;
    • 3.使用
      select 局部变量名

mysql种的sql语句性能测试
mysqlslap --no-defaults --create-schema=ihome1 -uitcast -p123456 -h127.0.0.1 -c 50 -i 100 --number-of-queries=1000 -q "select * from ih_facility_info;"