MySQL 常用SQL 技巧

 

1.检索包含最大或最小值的行。

MIN([distinct] expr) , MAX([distinct] expr)

 

select student_name, min(score),max(score) from student group by student_name;

 

2. rand & rand (n) 提取随机行。

 

select * from t1 order by rand();   随机检索表数据。

select * from t1 order by rand() limit 20; 随机提取一组20个行的数据。

 

3. 利用group by的with rollup子句做统计

 

create table sales

(

year int not null,

country varchar(20) not null,

product varchar(20) not null,

profit int

);

 

select year,sum(profit)from sales group by year;

select year,sum(profit)from sales group by year with rollup;

 

select year,country,product,sum(profit) from sales group by year,country,product;

select year,country,product,sum(profit) from sales group by year,country,product with rollup;

 

insert into sales values(2004,'china','tnt2004',2001);

insert into sales values(2004,'china','tnt2004',2002);

insert into sales values(2004,'china','tnt2004',2003);

insert into sales values(2005,'china','tnt2005',2004);

insert into sales values(2005,'china','tnt2005',2005);

insert into sales values(2005,'china','tnt2005',2006);

insert into sales values(2005,'china','tnt2005',2007);

insert into sales values(2005,'china','tnt2005',2008);

insert into sales values(2005,'china','tnt2005',2009);

insert into sales values(2006,'china','tnt2006',2010);

insert into sales values(2006,'china','tnt2006',2011);

insert into sales values(2006,'china','tnt2006',2012);

 

select year,country,product,sum(profit) from sales group by year,country,product;

select year,country,product,sum(profit) from sales group by year,country,product with rollup;

 

SELECT year,country,product,SUM(profit) FROM sales GROUP BY year,country,product WITH ROLLUP LIMIT 5;

 

rollup 与 order by互斥。

 

 

数据库对象名的大小写问题

 

在MySQL中，数据库对应的是数据目录中的一个目录，而表对应的是一个或多个文件。因此会根据操作系统对大小写的敏感度不同而不同。

列，索引，存储子程序和触发器在任何平台上对大小写都不敏感，列别名也不敏感。默认情况下，表别名在unix中是敏感的。

 

lower_case_tables_name

0：使用create table或create database。

按指定的大小写在硬盘上保存。对大小写敏感。（Unix默认）。如果再对大小写不敏感的FS上强制将此参数设置为0，并且使用不同大小写访问MyISAM表，会导致索引损坏。

 

1：表名在硬盘上以小写保存。大小写敏感。（Windows和 MaxOS默认）

 

2：按指定的大小写保存。但是MySQL将他们转换成小写，便于查找。大小写敏感。

 

此参数只在需要在不同平台上转移表使用。

在任何操作系统上都可以使用 =1，不利之处是show tables/show databases的时候看不出原本的大小写。

在unix上=0，在Windows上=2，可以保留数据库名的大小写，不利之处是要确保始终使用正确的大小写查询。

 

例外，如果是InnoDB表，在任何平台上均应设置为1，强制转换为小写。

在Unix中，将此参数设置为1，重启mysqld之前，必须将旧的数据库名和表名改为小写。

 

 

 

使用外键需要注意的地方

 

 

在MySQL中， InnoDB表支持对外部关键字约束条件的检查。

对于除InnoDB类型的表，当使用REFERENCES tbl_name(col_name)子句定义列时可以使用外部关键字，该子句没有实际的效果，只作为备忘录或注释来提醒，你目前正定义的列指向另一个表中的一个列。执行该语句时，实现下面很重要：

MySQL不执行表tbl_name中的动作， 例如作为你正定义的表中的行的动作的响应而删除行；换句话说，该句法不会致使ON DELETE或ON UPDATE行为（如果你在REFERENCES子句

中写入ON DELETE或ON UPDATE子句，将被忽略）。

该句法可以创建一个column；但不创建任何索引或关键字。

如果用该句法定义InnoDB表，将会导致错误。