Mysql（三）

Mysql语法进阶

沿用之前Mysql（一）中的stu1表

create table stu1(
id int(10) auto_increment primary key comment "id",
name varchar(20) ,
score int(10) 
)engine=innodb default charset=utf8;

表结构如下

Field	Type	Null	Key	Default	Extra
id	int(10)	NO	PRI	NULL	auto_increment
name	varchar(20)	YES		NULL
score	int(10)	YES		0
age	int(10)	YES		NULL

表数据如下

id	name	score	age
1	zxc	90	13
2	zxc	23	43
3	zdy	0	12

order by

select * from stu1 order by  age;
按年龄升序排列
select * from stu1 order by  age desc ;
按年龄降序排列
select * from stu1 order by  name,score;
也可按照年龄，分数一起排序

distinct

select distinct name from stu1;
去掉重复的名字
select distinct name score from stu1;
去掉重复的名字和分数

group by

select name,count(*) from stu1 group by name;
按姓名分组，并统计每个用户的数量
select name,count(*) from stu1 group by name having count(*) >=2;
统计用户数量大于2的人

like

通配符	描述
%	代替一个或多个字符
_	代替单个字符
[charlist]	中括号中的任何一个字符
[^charlist] 或者 [!charlist]	不在中括号中的任何单一字符

select * from stu1 where name like '%y';
代替一个或多个字符
select * from stu1 where name like 'z_y';
代替单个字符

limit，offset

select * from stu1 limit 1 offset 2;
从第二行开始显示一条数据
offset后的值不建议太大，需要消耗的IO较大

case when

select  case when score=0 then name else score end from stu1;
成绩为0的输出姓名，，其他的人输出分数

mysql内置函数

函数	描述
AVG()	返回列的平均值
COUNT(DISTINCT)	返回列去重后的行数
COUNT()	返回列的行数
MAX()	返回列的最大值
MIN()	返回列的最小值
SUM()	返回列的总和

select sum(score) from stu1;
返回分数的最大值

行列转换

DROP TABLE IF EXISTS `tbl_test1`;
CREATE TABLE `tbl_test1` (
  `user` varchar(255) NOT NULL COMMENT '主键',
  `leixing` varchar(255) NOT NULL,
  `score` varchar(255) NOT NULL,
  PRIMARY KEY (`user`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='行列转换测试';

-- ----------------------------
-- Records of tbl_test1
-- ----------------------------
INSERT INTO tbl_test1 VALUES ('li', 'age', '18');
INSERT INTO tbl_test1 VALUES ('li', 'dep', '2');
INSERT INTO tbl_test1 VALUES ('li', 'sex', 'male');
INSERT INTO tbl_test1 VALUES ('sun', 'age', '44');
INSERT INTO tbl_test1 VALUES ('sun', 'dep', '3');
INSERT INTO tbl_test1 VALUES ('sun', 'sex', 'female');
INSERT INTO tbl_test1 VALUES ('wang', 'age', '20');
INSERT INTO tbl_test1 VALUES ('wang', 'dep', '3');
INSERT INTO tbl_test1 VALUES ('wang', 'sex', 'male');

select user,max(case when leixing='age' then score end) as age ,
max(case when leixing='dep' then score end) as dep,
max(case when leixing='sex' then score end) as sex
from table_test
group by user;

触发器

触发器是加在表上的一个特殊程序，当表上出现特定的事件(INSERT/UPDATE/DELETE)时触发该程序执行。

create trigger insert_score
before insert on `stu1`
for each row 
begin
if new.score<0 then
set new.score =0;
elseif new.score >100 then
set new.score =100;
end if ;
end;

索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引的意义 —— 快速定位要查找的数据

• 单列索引

create index idx_test1 on stu1(name);
或者
alter table stu1 add index(name);

• 联合索引

create index idx_test2 on stu1(name,age);