欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql基础操作、sql技巧和sql的常见优化_MySQL

程序员文章站 2022-05-01 12:56:24
...
一、常见操作

1、复制表结构create table t2 like t1

复制表数据insert into t2 select * from t1
2、mysql索引
alter table用来创建普通索引、unique索引或primary key索引
alter table t add index index_name(column_list)
alter table t add unique(column_list)
alter table t add primary key(column_list)

create index index_name on table (column_list)
create unique index index_name on table(column_list)
drop index index_name on t
alter table t drop index index_name
alter table t drop primary key
alter table t modify id int not null
show index form t
3、视图
create view v as select * from t where id>5
insert into t(name) values("user"),("user1")
? view | ? index
alter view | create view | drop view
一旦与视图关联的表损坏,则视图会发生错误
4、内置函数
字符串:
concat("hello","world") as hw 链接字符串
lcase("GAFG")//转小写
ucase("jjj")
length(string) //长度
ltrim(string) //去掉前端空格
rtrim(string) //去掉右端空格
repeat(string,count)//重复count次
replace(string,s1,s2)//在string中把s1替换成s2
substring(str,position,[length])//在str中,从position开始取length个字符
space(count) 生成count个空格
数学函数:
bin(number) //十进制转二进制
ceiling(number)//向上取整
floor(number) //向下取整
max(num1,num2)//取最大值
min(num1.num2)
sqrt(number)//开平方
rand() //返回0-1内的随机值
日期函数:
curdate()//返回当前日期
curtime()
unix_timestamp(date)//返回当前date的
week(date)//返回date为一年中的第几周
year(date)//返回日期date的年份
datediff(expr,expr2)//两个时间之间的间隔天数
5、mysql预处理语句
设置stmt1预处理:
prepare stmt1 from 'select * from t where id>?'
设置一个变量: set @i = 1;
执行stmt1预处理:execute stmt1 using @i
set @i=5
execute stmt1 using @i
删除预处理:drop prepare stmt1

6、 mysql事务处理
关闭自动提交功能:set autocommit=0;
delete from t where id = 11;
savepoint p1;
delete from t where id = 12;
savepoint p2;
rollback to p1;
rollback;//还原到最原始的还原点
alter table t engine=innodb;//事务控制只对innodb引擎有用
7、mysql存储procedure
create procedure p2()
begin
set @i = 3;
while @i insert into t(name) values(concat('hh',@i));
set @i=@i+1;
end while;
end;
shwo create procedure p2;
call p2;
8、mysql 触发器
create trigger tg before insert on t for each row
begin insert into t2(name) values(new.name)
end
create trigger tg before delete on t for each row
begin delete from t2 where name = old.name
end
9、重排auto_increment
清空表的时候用truncate
alter table t auto_increment = 1;
二、常见sql技巧
1、正则表达式的使用(和其他语言一样)
select "linux is very good!" regexp ".*"//匹配所有
select "linux is very good!" regexp "^linux"
select email from user where emial regexp "@163[.,]com$"
=
select email from user where emial like '%@163.com$' or emial like '%@163,com$'
2、巧用rand()提取随即行
0-1之间的随机数函数 select rand()*100
select * from t order by rand();//随即排列表数据
select * from t order by rand() limit 3;//取出三条随即样本
3、利用group by的with roolup字句统计,不可以和order by使用
可以检索出更多的分组聚合信息
select * from t;
select * ,count(*)from t group by c1,c2//先对c1聚合
select * ,count(*)from t group by c1,c2 with roolup
4、用bit group functions做统计
在使用group by语句时可以使用bit_and、bit_or函数来完成统计工作。这两个函数的作用主要是做数值
之间的逻辑位运算。
select id,bit_or(kind) from t group by id
5、使用外键需要注意的问题
create table t (id ,name,foreign key(id) references t1(id) on delete cascade on update cascade)
innodb类型的表支持外键,myisam类新的表虽然创建外键可以成功,但是不起作用,主要原因是不支持外键。
6、mysql中help的使用
? %
? create
? opti%
? reg%
? contents mysql中所有的帮助信息
三、sql优化
1、优化sql语句的一半步骤
show status了解各种sql的执行频率
show [session|globe] status;
show global status;
show status like 'com_%'
show global status like "com_%"
只针对innodb存储引擎的:
innodb_rows_read 执行select操作的次数
innodb_rows_updated/inserted/deleted
show variables like 'slow%'
show variables like 'long%'
show status like connections链接mysql的数量
show status like uptime 服务器已经工作的秒数
show status like slow_queries慢查询的次数
定位执行效率低的sql语句
explain/desc select * from table where id>10
desc select * from table where id>10/G
2、索引问题
常见优化手段之一
myisam存储引擎的数据和索引时自动分开存储的。各自是独一的一个文件
innodb存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成
mysql不支持函数索引,但是能对列的前面某一部分进行索引,例如name字段,可以对name的
前4个字符进行索引,这个特性可以大大缩小索引文件的大小
create index index_name on t (name(4))
mysql索引用于快速查找出某一个列中有定特定值的行,对相关列使用索引时是提高select操作性能的最佳途径
1、使用索引
对于创建多列索引,只要查询的条件中用到最左边的列,索引一般会被使用
create index iname on t (name,age)//符合索引
2、存在索引但是不使用索
如果mysql估计使用索引比全表扫描慢,则不使用索引,例如keypart均匀分布在1-100之间,查询时使用索引就不是很好
eg:select * from t where keypart>1 and keypart 如果使用t表并且where条件中不使用=进行索引列,则不使用到索引。
用or分隔开的条件如果or前的条件中的列有索引,后面的列中没有索引,
则涉及到的索引都不会用到,索引都需要用索引
如果列类型是字符串,但是在查询的时候把一个整数型常量付给了字符型的列name,那么在name列上有索引也不会用到

3、常用sql的优化
大批量插入数据:infile outfile
当用load命令导入数据的时候,适当设置可以提高导入的速度,
4、常用的sql优化
insert语句,尽量使用多个值表的insert语句,这样可以大大缩短客户与数据库连接关闭的消耗
可以使用insert delayed语句得到更高的效率
相关标签: mysql 技巧