数据库
之优化
全称 :结构化查询语言
structured Query Language
ctrl+H 查看navicate 历史日志
sql语句
1.select max(字段名) from 表名 查询字段中最大的
select MD5('字符串') 给字符串进行md5加密
2.如果想要查询两个字段的个数 : count
select count(字段名=‘值’ or null) as '值',count(字段名=‘值’ or null) as '值' from 表名
select sex,count(sex) from user group by sex//男女生的个数
3.SQL语句去重函数:distinct
如:
select distinct ‘字段名’ from 表名
4.可以使用
int存储时间
格式
unix_timestamp() 将年月日 时分秒日期函数 转换为int 时间戳
from_unixtime() 将时间戳转换为年月日
5
.ip存储
可以使用bigint 一般程序员会把它定义为varchar(15)
但是使用bigint(8)只需要8个字节,相差了7个字节;
inet_aton是存ip时用到的函数,inet_ntoa()是取ip时用到的函 数;
来存储IP地址效率很高,效率高很多。
如:insert into 表名 (time,ip) values (unix_timestamp(‘2017-08-29 17:02:03’),inet_aton(‘192.168.1.200’));
查看:select from_unixtime('time') from 表名; 时间的时候
查看:select inet_ntoa('ip') from 表名; ip的时候
同时查看:
select from_unixtime(time),inet_ntoa('ip') from 表名;
sql循环添加
insert into goods_text(name,type,store) select name,type,store from goods_text
三门成绩大于90分的学生的所有成绩
select name,score from chengji where name in(select name from chengji where score>90 group by name having count(*)>=3);
//这是一条sql 查询年龄在15-20 20-25 25-30 30- 区间段的人数是多少
select case when (tel >=15 and tel <=20) then '15-20' when (tel >=21 and tel <=25) then '21-25' when (tel >=25 and tel <=30) then '25-30' else '30-' end '区间', count(*) c from students group by
case when (tel >=15 and tel <=20) then '15-20' when (tel >=21 and tel <=25) then '21-25' when (tel >=25 and tel <=30) then '25-30' else '30-' end
order by 1
MySQL中的INT类型
1、bigint
BIGINT(M),M默认为20.
从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据(所有数字),无符号的范围是0到
18446744073709551615。一位为 8 个字节。
2、int
一个正常大小整数。有符号的范围是-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据(所有数字),无符号的范围是0到4294967295。一位大小为 4 个字节。
int 的 SQL-92 同义词为 integer。
INT(M),M默认为11;
3、mediumint
一个中等大小整数,有符号的范围是-8388608到8388607,无符号的范围是0到16777215。 一位大小为3个字节。
MEDIUMINT(M), M默认为9;
4、smallint
一个小整数。有符号的范围是-2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据,无符号的范围是0到65535。一位大小为 2 个字节。MySQL提供的功能已经绰绰有余,而且由于MySQL是开放
源码
软件,因此可以大大降低总体拥有成本。
SMALLINT(M), M默认为6;
5、tinyint
tinyint(M),
M默认为4;
(保存时的默认值)
有符号的范围是-128 - 127,无符号的范围是 从 0 到 255 的整型数据。一位大小为 1 字节。
mysql
获取当前日期 now();
日期格式化 date_format();
连接字符串 concat();
四舍五入 round();
把文件里的sql导入数据库 source 路径 如 source F:/a.sql
导入失败如何调优 即数据库包大小限制 max_allowed_packet
char与varchar的区别
char的长度是固定的,而varchar的长度是可以变化的
比如,存储字符串 'abc',对于char(10),表示你存储的字符将占10个字节(包括7个空字符),而同样的VARCHAR2 (10)则只占用3个字节的长度,10只是最大值,当你存储的字符小于10时,按实际长度存储。
explain主要用于sql语句中的select查询,可以显示的查看该sql语句索引的命中情况
cmd命令连接mysql mysql -uroot -proot
后面不能 跟分号
cmd命令断开mysql exit 或者quit;
①
创建数据库:create database '数据库名'
查看数据库:show databases
选择数据库:use '数据库名'
删除数据库:drop database '数据库名'
查看当前库所拥有的表:show tables;
表
②
创建
表 create table '表名'
create table '表名'(
id int (11) not null auto_increment primary key,(有逗号)
name varchar(30) not null(注意:没有逗号)
)engine=innodb/myisam default charset=utf8;
查看
表结构 desc '表名' 或者 describe '表名'
or show columns from '表名';
or show create table '表名'\G
查看表中某一个字段 desc '表名' '字段名'
修改
表字段长度:alter table '表名' modify '表中字段名' '新长度';
给表进行重命名
:rename table '当前表名' to '新的表名';
给表增加一个字段:
alter table '表名' add '字段名' char(32) not null;
alter table '表名' add '字段名' char(32) not null first;[把新列 放在最前面]
alter table '表名' add '字段名' char(32) not null after '字段 名';把新列放在某列后面;
如何修改字段名:alter table tab_name change old_col new_col
varchar(40)
; //必须为新字段指定数据类型等属性,否则不能修改
删除
表中某字段
alter table 表名 drop column 字段名;
删除这个表
drop table 表名
表数据
③
增加表数据
insert into 表名 (字段名) values (新值)
增加多条表数据
insert into 表名 (字段名)values (新值),(新值),(新值)
删除表数据
delete from 表名 where 条件
清空表数据 truncate '表名'
update 表名 set 字段名='新值' where 条件
查看表数据
select * from '表名'
④
//对数据库数据进行备份
mysqldump -uroot -proot laravel(数据库名) >路径(如:F:\laravel.txt);
//对数据库数据进行恢复
mysql -uroot -proot 数据库名 <路径(如:F:\laravel.txt);
MYSQL数据库优化
:几个方面
硬件 (成本最高)
系统配置
数据库表结构
SQL及索引(效率最高)很重要
A---
1.使用mysql慢查日志对有效率问题的sql进行监控
可以通过慢查日志查询有问题的sql
慢查询日志
:记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
select @@version 查看(mysql)版本
show variables like version 查看(mysql)版本
show variables like 'slow_query_log' 查看是否开启慢查询日志
show variables like '%log%' 查询log变量设置
记录
未使用索引的查询
set global log_queries_not_using_indexes=on;
开启慢查询日志 set global slow_query_log=on;
超过多少秒的查询记录到慢查询日志中。
set global long_query_time=1;
慢查询日志位置:show variables like 'slow%'
2.
使用explain 及sql语句
extra列需要注意返回值:
Using filesort:看到这个的时候,查询需要优化了。
Using temporary 看到这个的时候,查询需要 优化了。
(这个通常发生在ordey by 上,而不是group by 上)
3.
sql优化之 Count() 和Max();
时间戳越大,时间就越往后。
①select max('字段名') from 表名;
优化的时候可以给查询的字段加索引,就会速度很快。
大大优化了sql执行 的效率。
给字段加索引:
create index 索引名 on 表名(字段名);创建普通索引
删除索引
drop index 索引名 on 表名;
create unique index 索引名 on 表名(字段名);创建唯一索引
alter table 表名 add primary key 索引名(字段名);创建主键索引
②.如果想要查询两个字段的个数 : count
select count(字段名=‘值’ or null) as '值',count(字段名=‘值’ or null) as '值' from 表名
select sex,count(sex) from user group by sex//男女生的个数
count(*) 与count('字段')区别
count(*)会查询出空值,count('字段') 不会查询出空值。
4.
子查询的优化:
通常情况下,需要把子查询优化为join 查询,但是在优化的时候要注意关联建是否有一对多的关系,要注意
重复
数据。
可以使用distinct() 进行去重。
5.
limit的优化:
使用有索引的列或主键进行order by 操作。
select 字段 from 表名 order by 主键 limit 50.
B--- 索引优化
如何选择合适的列建立索引?