MySQL-常用查询

一、简介

1.1 内容

将通过一个经销商数据库的实例来学习并实践多种常用的在数据库中查找记录的方法

1.2 知识点

  • 表查询操作

二、操作步骤

在下面的例子中使用数据表shop来存储商人（经销商）的每件物品（物品号）对应的价格。假如每个商人对应的每个物品都有一个固定的价格，那么该表中的关键字就是物品和商人。

1
2
create DATABASE TEST;
use TEST;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)
); 

insert into shop values
(1,'A',3.45),(1,'B',3.99),
(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),
(4,'D',19.95);

select * from shop

2.1 寻找列的最大值

使用MAX()函数计算物品号article的最大值

1
select max(price) as article from shop;

2.2 查询某列的最大值所在的行

1
2
3
4
5
6
7
8
9
10
11
select article,dealer,price
from shop
where price=(select max(price) from shop); 

# 另外一个方法是对所有行进行价格的降序排序，
# 然后使用MySQL特有的LIMIT子句显示其中一行
#（这里是指定第一行显示最高价格） 1;
select article,dealer,price
from shop
order by price DESC
limit 1;

2.3 按组显示列的最大值

1
2
3
select article, max(price) as price
from shop
group by article;

2.4 使用用户用户变量

通过使用用户变量找出价格最高或者最低的物品

1
select @min_price:=MIN(price),@max_price:=MAX(price) from shop;

1
select * from shop where price=@min_price or price=@max_price;

2.5 使用外键

在MySQL中，InnoDB表支持外键约束的检查。

在连接两个表的时候并不需要外键约束。对于除InnoDB表以外的表，可以使用REFERENCES tbl_name(col_name)语句定义将它的列设置为外键，但是该语句并没有实际的作用，只是作为备注来提醒你现在正在定义的列指向另外一个表的列。在执行该语句的时候需要注意以下几点：

  • MySQL并不会执行任何操作检查列col_name是否存在于表tbl_name中（它甚至不会检查tbl_name这个表是否真正存在）。
  • MySQL不执行表tbl_name中的操作，例如对你正定义的行的进行更改行为，致使ON DELETE或ON UPDATE语句在该行上不会生效（意思是如果你在REFERENCES子句中写入ON DELETE或ON UPDATE子句，将被忽略）。
  • 该句法可以创建一个列；但不创建任何索引或关键字。

你可以使用下面的语句建立两个表并定义外键连接两个表中的列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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)
);

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, 'Lilliana Angelovska');
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);

尝试查询person和shirt这两个的内容

**按照以上方式建立表，并使用语句SHOW CREATE TABLE或者DESCRIBE查看输出结果，你会发现REFERENCES子句并不会出现在结果中 **

1
2
desc person
desc shirt

2.6 计算每月的访问量

下面使用BIT_COUNT函数计算每个月中某用户访问网页的天数

1
2
3
4
5
6
7
8
9
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);

上述建立的表中有用户访问网页的年月日，可以使用以下语句查询每个月的访问天数

1
2
3
select year, month,BIT_COUNT(BIT_OR(1<<day)) as days 
from t1 
group by year, month;

2.7 使用AUTO_INCREMENT语句

在定义列属性的时候添加AUTO_INCREMENT语句可以使得每条记录都能被唯一标识

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES 
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');

SELECT * FROM animals;

查看以上语句的返回结果，你会发现id是自动生成的。

要想AUTO_INCREMENT语句生成的起始值不是1，可以通过CREATE TABLE或ALTER TABLE来设置该值，如下所示

1
ALTER TABLE tbl AUTO_INCREMENT = 100;

更多关于AUTO_INCREMENT语句的学习：