教程
SELECT最简单的形式是从一个表中检索所有记录:
mysql> SELECT * FROM pet;
至少有两种修正方法:
· 编辑文件“pet.txt”改正错误,然后使用DELETE和LOAD DATA清空并重新装载表:
· mysql> DELETE FROM pet;
· mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;
然而, 如果这样操做,必须重新输入Puffball记录。
· 用一个UPDATE语句仅修正错误记录:
· mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
UPDATE只更改有问题的记录,不需要重新装载数据库表。
mysql> SELECT * FROM pet WHERE name = 'Bowser';
字符串比较时通常对大小些不敏感,因此你可以将名字指定为"bowser"、"BOWSER"等,查询结果相同。
mysql> SELECT * FROM pet WHERE birth > '1998-1-1';
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
AND和OR可以混用,但AND比OR具有更高的优先级。如果你使用两个操作符,使用圆括号指明如何对条件进行分组是一个好主意:
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f');
如果你不想看到表中的所有行,就命名你感兴趣的列,用逗号分开。
mysql> SELECT name, birth FROM pet;
mysql> SELECT owner FROM pet;
mysql> SELECT DISTINCT owner FROM pet;
mysql> SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';
mysql> SELECT name, birth FROM pet ORDER BY birth;
在字符类型列上,与所有其他比较操作类似,分类功能正常情况下是以区分大小写的方式执行的。这意味着,对于等同但大小写不同的列,并未定义其顺序。对于某一列,可以使用BINARY强制执行区分大小写的分类功能,如:ORDER BY BINARY col_name.
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC;
注意DESC关键字仅适用于在它前面的列名(birth);不影响species列的排序顺序。
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet;
此处,YEAR()提取日期的年部分,RIGHT()提取日期的MM-DD (日历年)部分的最右面5个字符。比较MM-DD值的表达式部分的值一般为1或0,如果CURDATE()的年比birth的年早,则年份应减去1。整个表达式有些难懂,使用alias (age)来使输出的列标记更有意义。
mysql> SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM pet ORDER BY name;
mysql> SELECT name, birth, death,
-> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
-> AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
查询使用death IS NOT NULL而非death != NULL,因为NULL是特殊的值,不能使用普通比较符来比较,以后会给出解释。
mysql> SELECT name, birth, MONTH(birth) FROM pet;
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
请注意在MySQL中,0或 NULL意味着假而其它值意味着真。布尔运算的默认真值是1。
在GROUP BY中,两个NULL值视为相同。
执行ORDER BY时,如果运行 ORDER BY ... ASC,则NULL值出现在最前面,若运行ORDER BY ... DESC,则NULL值出现在最后面。
SQL模式匹配允许你使用“_”匹配任何单个字符,而“%”匹配任意数目字符(包括零字符)。
在 MySQL中,SQL的模式默认是忽略大小写的。下面给出一些例子。注意使用SQL模式时,不能使用=或!=;而应使用LIKE或NOT LIKE比较操作符。
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
mysql> SELECT * FROM pet WHERE name LIKE '_____';
由MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时,使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE,它们是同义词)。
扩展正则表达式的一些字符是:
· ‘.’匹配任何单个的字符。
· 字符类“[...]”匹配在方括号内的任何字符。例如,“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围,使用一个“-”。“[a-z]”匹配任何字母,而“[0-9]”匹配任何数字。
· “ * ”匹配零个或多个在它前面的字符。例如,“x*”匹配任何数量的“x”字符,“[0-9]*”匹配任何数量的数字,而“.*”匹配任何数量的任何字符。
如果REGEXP模式与被测试值的任何地方匹配,模式就匹配(这不同于LIKE模式匹配,只有与整个值匹配,模式才匹配)。
为了定位一个模式以便它必须匹配被测试值的开始或结尾,在模式开始处使用“^”或在模式的结尾用“$”。
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
如果你想强制使REGEXP比较区分大小写,使用BINARY关键字使其中一个字符串变为二进制字符串。该查询只匹配名称首字母的小写‘b’。
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
为了找出以“fy”结尾的名字,使用“$”匹配名字的结尾:
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
为了找出包含一个“w”的名字,使用以下查询:
mysql> SELECT * FROM pet WHERE name REGEXP 'w';
为了找出包含正好5个字符的名字,使用“^”和“$”匹配名字的开始和结尾,和5个“.”实例在两者之间:
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
你也可以使用“{n}”“重复n次”操作符重写前面的查询:
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
mysql> SELECT COUNT(*) FROM pet;
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
COUNT( )和GROUP BY以各种方式分类你的数据。
下列例子显示出进行动物普查操作的不同方式。
每种动物的数量:
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
每种性别的动物数量:
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
(在这个输出中,NULL表示“未知性别”。)
按种类和性别组合的动物数量:
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
若使用COUNT( ),你不必检索整个表。例如, 前面的查询,当只对狗和猫进行时,应为:
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = 'dog' OR species = 'cat'
-> GROUP BY species, sex;
或,如果你仅需要知道已知性别的按性别的动物数目:
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
mysql> SELECT pet.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
-> remark
-> FROM pet, event
-> WHERE pet.name = event.name AND event.type = 'litter';
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
你已经见到了SHOW DATABASES,它列出由服务器管理的数据库。为了找出当前选择了哪个数据库,使用DATABASE( )函数:
mysql> SELECT DATABASE();
如果你还没选择任何数据库,结果是NULL。
为了找出当前的数据库包含什么表(例如,当你不能确定一个表的名字),使用这个命令:
mysql> SHOW TABLES;
如果你想要知道一个表的结构,可以使用DESCRIBE命令;它显示表中每个列的信息:
mysql> DESCRIBE pet;
Field显示列名字,Type是列的数据类型,Null表示列是否能包含NULL值,Key显示列是否被索引而Default指定列的默认值。
如果表有索引,SHOW INDEX FROM tbl_name生成有关索引的信息。
在批处理模式下使用mysql
启动命令行工具mysql并选择数据库:
shell> mysql your-database-name
(在大多数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;
SELECT MAX(article) AS article FROM shop;
任务:找出最贵物品的编号、销售商和价格。
这很容易用一个子查询做到:
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
另一个解决方案是按价格降序排序所有行并用MySQL特定LIMIT子句只得到第一行:
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
注:如果有多项最贵的物品( 例如每个的价格为19.95),LIMIT解决方案仅仅显示其中一个!
任务:每项物品的的最高价格是多少?
SELECT article, MAX(price) AS price
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);
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;