常用查询

创建表

create table shop(
article int(4) unsigned zerofill default '0000' not null,dealer char(20) default '' not null,
price double(16,2) default '0.00' not null, primary key(article,dealer));

ZEROFILL 零填充

UNSIGNED 无符号

列最大值

MAX

SELECT MAX(ARTICLE) AS article from shop;

ORDER BY

SELECT ARTICLE,DEALER,PRICE FROM SHOP ORDER BY PRICE DESC LIMIT 1;

LINT START,LENGTH 截取记录

GROUP BY

SELECT ARTICLE,MAX(PRICE) AS PRICE FROM SHOP GROUP BY ARTICLE;
SELECT ARTICLE,COUNT(*) AS COUNT FROM SHOP GROUP BY ARTICLE;

每组最大值

SELECT ARTICLE,DEALER,PRICE FROM SHOP S1 WHERE PRICE =
(SELECT MAX(S2.PRICE) FROM SHOP S2 WHERE S1.ARTICLE = S2.ARTICLE );
SELECT ARTICLE,DEALER ,MAX(PRICE) AS PRICE FROM SHOP GROUP BY ARTICLE;

分组 （聚合的列）和作为键的列（多个列）

group by 例子

1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名

name kecheng fenshu

张三 语文 81

张三 数学 75

李四 语文 76

李四 数学 90

王五 语文 81

王五 数学 100

王五 英语 90

创建表

CREATE TABLE COURSE（name varchar(20) , course_name varchar(15), score int）;

插入数据

insert into course values
-> ('张三','语文',81),
-> ('张三','数学',75),
-> ('李四','语文',76),
-> ('李四','数学',90),
-> ('王五','语文',81),
-> ('王五','数学',100),
-> ('王五','英语',90);

查询

1. 分组查询每个人最低分数的课程，是否大于80

select * from (
SELECT name, course_name, min(score)
as s from course group by name) T
where T.s >80;
SELECT name, course_name, min(score) as s from course group by name
having min(score)>80;

Having 聚合函数过滤 -先聚合，再分组，再过滤

使用用户变量

SELECT @X:= min(score),@Y:= max(score) from course;
SELECT * FROM COURSE WHERE SCORE = @X OR SCORE =@Y;

外键

创建表

create table person(
-> id smallint unsigned not null auto_increment,
-> name char(60) not null,
-> primary key(id));
create table shirt(
-> id smallint unsigned not null auto_increment,
-> style enum('t-shirt','polo','dress') not null,
-> color enum('red','blue','orange','white','black') not null,
-> owner smallint unsigned not null references person(id),
-> primary key(id));

插入数据

插入主表记录-记录ID-利用变量插入从表

insert into person values(NULL,'Antonio Paz');
select @last = LAST_INSERT_ID();
INSERT INTO shirt values
-> (NULL,'polo','blue',@last),
-> (NULL,'dress','white',@last),
-> (NULL,'t-shirt','blue',@last);
insert into person values(NULL,'Lilianna angellovska');
select @last:= LAST_INSERT_ID();
INSERT INTO shirt values
-> (NULL,'dress','orange',@last),
-> (NULL,'polo','red',@last),
-> (NULL,'dress','blue',@last),
-> (NULL,'t-shirt','white',@last);

查询

SELECT s.* FROM person p,shirt s
where p.name like '%lili%' and s.owner = p.id and s.color <> 'white';

显示表 SQL

show create table shirt;

UNION

SELECT * FROM SHIRT UNION SELECT * FROM SHIRT;
SELECT * FROM SHIRT WHERE color ='blue' UNION SELECT * FROM SHIRT where style='dress';

根据天查询访问量

创建表

create table t1
-> (
-> year year(4),month int(2) unsigned zerofill,day int(2) unsigned zerofill);

插入数据

insert into t1 values
-> (2000,1,1),
-> (2000,1,20),
-> (2000,1,30),
-> (2000,2,2),
-> (2000,2,23),
-> (2000,2,23);

查询

select year,month,bit_count(bit_or(1<

支付宝打赏

微信打赏

命令   基础   MYSQL

发表评论

留言与评论（共有 0 条评论） “”

验证码：