<MySQL>MySQL基本操作补充( 查询)
show create table 表名 \G;(查看创建的属性) alter table 表名 auto_increment=xx;(修改自增起始值) set session auto_increment_offset=xx;(修改步长) 索引的目的:加速查找 约束: 主键 外键 唯一索引:unique 名字 (列名) ----不允许重复(可以为空) 联合唯一:unique 名字 (列名1,列名2)---不允许一起重复 sql语句补充 select * from 表名; select 列名 as 别名 from 表名 where 条件; select 列名 as 别名,数字 from 表名 where 条件;(加额外的数字列) select * from 表名 where 列名 != x;(不等于) select * from 表名 where 列名 in (x,y,z);(in(在):x,y,z) select * from 表名 where 列名 not in (x,y,z);(不在) select * from 表1名 where 列名 in (select 列名 from 表2);(和表2中相同的) select * from 表名 where 列名 between x and y; (介于x-y,包含边界) select * from 表名 where 列名 like "a%";(%代表所有的值,数量:0-无限多) select * from 表名 where 列名 like "a_";(_代表所有的值,数据1个) select * from 表名 limit x;(前x条) select * from 表名 limit x,y;(起始位置从x起,往后取y条) select * from 表名 order by 列名 desc; #大到小 select * from 表名 order by 列名 asc; #小到大 select * from 表名 order by 列名1 desc,列名2 desc;分开排序 取后y条数据(先排序在取值) select * from 表名 order by 列名 desc limit y; 分组: select count(列名),max(列名),part_id from 表名 group by 列名;(sum:求和,avg:求平均值) 连表: select * from 表1,表2 where 条件;
select * from 表1 left join 表2 on 列1=列2;
消除重复的行
select distinct 列名 from 表名;
模糊查询
%匹配任意多个字符,_匹配一个字符
select * from students where sname like '郭%';
范围查询
in 表示在一个非连续的范围内查询,
select * from 表名 where id in(1,3);
between ... and ...
select * from students where id between 2 and 4;
判断空
select * from students where isnull is null; select * from students where isnull is not null;
优先级
小括号,not,比较运算符,逻辑运算符;
and比or先运算,如果希望先and要结合小括号
聚合(函数)
count(*)用于统计总行数
select count(*) from students;
max(列)找出最大值,min(列)找出最小值
select max(year) from students;
select min(year) from students;
sum(列)求和
select sum(year) from students;
avg(列)求平均值
select avg(year) from students;
子查询,得到聚合处理后的结果
select * from students where year=(select min(year) from students);
分组
group by相同的值分为一组
select year as 年龄,count(*) from students group by year;
分组之后进行筛选
where对from后的结果进行筛选,having是对分组后的结果进行筛选
select year as 年龄,count(*) from students group by year having count(*)=3 or 年龄=22;
分页
select * from students order by id desc limit 2,5;
完整的查询语句
关系
decimal(a,b)
参数说明:
a:指定小数点左边和右边可以存储的十进制数字的最大个数,最大精度为38.
b:指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从0~a之间的值,默认小数位数是0.
添加外键
alter table scores add foreign key(stuid) references students(id);
如果直接写在创建表语句里面
foreign key(subid) references subject(id);
连接查询
select students.sname,subject.title,scores.score from scores inner join students on scores.stuid=students.id inner join subject on scores.subid = subject.id;
视图
复杂的查询语句,多次使用后维护非常麻烦,解决办法就是定义视图,
视图的本质查询语句进行封装,用它代表复杂的select语句,相当于快捷方式,别名
创建视图
create view stuscore as select students.sname,subject.title,scores.score from scores inner join students on scores.stuid=students.id inner join subject on scores.subid = subject.id;
使用视图
select * from stuscore;
事务
当你通过sql语句对数据进行影响变更的时候,
如果某个sql语句出错,你希望整个操作都进行回退
目的:保证一个业务逻辑的操作有效
也就是你操作数据只存在2种情况,成功或失败,成功才会改变数据
ACID
原子性:不可拆分
一致性:不会因为顺序影响结果,结果一致
隔离性:不会因为别的数据干扰,
持久性:不会因为特殊的情况造成数据丢失
引擎必须是innodb和bdb
步骤:
1.开始begin
begin;
2.提交commit
sql语句
commit;
(到这才会更改)
3.回滚rollback
rollback;
放弃整个begin之后的操作
索引
数据默认是按照主键存的
等尽量往前写,范围尽量往后写
优化:优化where后面的语句建立索引,可以有效提高查询速度
查看索引
show index from students;
创建索引
create index ztitleindex on maoyantop100(ztitle(20));
删除索引
drop index ztitleindex on maoyantop100;
性能分析
1.开启运行时间检测
set profiling=1;
2.执行查询语
select * from maoyantop100 where ztitle='触不可及';
3.查看执行时间
show profiles;
4.建立索引
create index ztitleindex on maoyantop100(ztitle(20));
5.重新执行sql语句
select * from maoyantop100 where ztitle='触不可及';
6.查看执行时间
show profiles;
差距明显