MySQL常用查询
来源:互联网 发布:男士大衣品牌 知乎 编辑:程序博客网 时间:2024/06/05 00:56
一、实验介绍
1.1 实验内容
本节实验中我们将通过一个经销商数据库的实例来学习并实践多种常用的在数据库中查找记录的方法。
1.2 实验知识点
- 表查询操作
1.3 实验环境
课程使用的实验环境为 Ubuntu Linux 14.04 64 位版本。实验中会用到程序:
- Mysql 5.5.50
- Xfce终端
二、实验步骤
在下面的例子中使用数据表shop
来存储商人(经销商)的每件物品(物品号)对应的价格。假如每个商人对应的每个物品都有一个固定的价格,那么该表中的关键字就是物品
和商人
。
首先你需要启动服务器并连接到MySQL:
然后创建一个数据库TEST
,并切换到该数据库:
再通过如下代码创建一个示例表:
mysql> 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));# 插入数据mysql> 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);
执行以下语句查看表的内容:
mysql> SELECT * FROM shop;
2.1 寻找列的最大值
使用MAX()
函数计算物品号article
的最大值:
mysql> SELECT MAX(article) as article FROM shop;
2.2 查询某列最大值所在的行
# 显示price这一列最大值对应的行mysql> SELECT article, dealer, price -> FROM shop -> WHERE price=(SELECT MAX(price) FROM shop);
另外一个方法是对所有行进行价格的降序排序,然后使用MySQL特有的LIMIT子句显示其中一行(这里是指定第一行显示最高价格):
mysql> SELECT article, dealer, price -> FROM shop -> ORDER BY price DESC -> LIMIT 1;
大家可以输入上述语句查询结果是否一样。
注:如果有多项物品的价格都是19.95(最贵的物品不止一个),那么LIMIT的显示结果也只会显示其中一个
2.3 按组显示列的最大值
通过以下代码找出每项物品中对应出价最高价格
mysql> SELECT article, MAX(price) AS price -> FROM shop -> GROUP BY article;
2.4 使用用户变量
通过使用用户变量找出价格最高或者最低的物品:
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;mysql> 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子句,将被忽略)。 - 该句法可以创建一个列;但不创建任何索引或关键字。
你可以使用下面的语句建立两个表并定义外键连接两个表中的列:
mysql> CREATE TABLE person ( -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> name CHAR(60) NOT NULL, -> PRIMARY KEY (id) -> );mysql> 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) -> );mysql> INSERT INTO person VALUES (NULL, 'Antonio Paz');mysql> SELECT @last := LAST_INSERT_ID();mysql> INSERT INTO shirt VALUES -> (NULL, 'polo', 'blue', @last), -> (NULL, 'dress', 'white', @last), -> (NULL, 't-shirt', 'blue', @last);mysql> INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');mysql> SELECT @last := LAST_INSERT_ID();mysql> 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
子句并不会出现在结果中:
SHOW CREATE TABLE shirt\G
看看你的输出结果吧~
2.6 使用两个关键字进行搜索
充分利用OR
连接两个关键字(AND
也是一样的道理)
# 这里面的test_table可以是任何一个表,关键词也是类似SELECT field1_index, field2_index FROM test_tableWHERE field1_index = '1' OR field2_index = '1'
还可以使用UNION
将两个表里面的关键字一起使用进行搜索
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' UNIONSELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
2.7 计算每月的访问量
下面使用BIT_COUNT
函数计算每个月中某用户访问网页的天数:
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<<day)) AS days FROM t1 GROUP BY year,month;
2.8 使用AUTO_INCREMENT
语句
在定义列属性的时候添加AUTO_INCREMENT
语句可以使得每条记录都能被唯一标识:
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
来设置该值,如下所示:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
更多关于AUTO_INCREMENT
语句的学习:
- 如何为列指定
AUTO_INCREMENT
属性:CREATE TABLE语法和ALTER TABLE语法。 - 找出含有最新的
AUTO_INCREMENT
值的行:比较函数和操作符。
- MYSQL常用查询语句
- 常用 mysql函数查询
- MYSQL 常用数据查询
- mysql 常用查询
- mysql常用查询语句
- MySQL 常用查询命令
- MySQL常用查询语句
- mysql常用查询语句
- mysql常用查询语句
- Mysql 常用查询函数
- MySQL常用查询语句
- mysql常用查询语句
- Mysql常用查询语句
- mysql数据库查询常用
- Mysql常用查询
- mysql 常用查询
- mysql的常用查询
- MySQL常用查询语句
- django初体验
- 新浪微博Oauth授权认证登录
- 已知dsc 和fdf ,编译bios fd
- 理解SVM比较好的几篇博文
- 斯坦福机器学习第四周(神经网络及其应用)
- MySQL常用查询
- Hive UDF开发
- poj1228-Grandpa's Estate 带边上节点的凸包(稳定凸包)问题
- AppCompatActivity全屏/状态栏设置颜色
- Hive UDAF开发
- 集群
- 键盘遮挡输入框
- com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
- generatorConfig.xml