SQL编程

CREATE　DATABASE menagerie;

CREATE TABLE pet (
		name VARCHAR(20),
		owner VARCHAR(20),
		species VARCHAR(20),
		sex CHAR(1),
		birth DATE,
		death DATE
		);

SELECT * FROM pet WHERE birth >= '1998-1-1';

SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f')

SELECT DISTINCT owner FROM pet;

SELECT name, birth FROM pet ORDER BY birth DESC;

SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;

SELECT name, birth, death, TIMESTAMPDIFF(YEAR, birth, death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;

>NULL是个特殊值不能用于比较操作，所以使用"IS NOT NULL" 而不是 "<> NULL"

SELECT name, birth, NONTH(birth) FROM pet;

SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))

SELECT name,birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

SELECT * FROM pet WHERE name LIKE '_____';

SELECT * FROM pet WHERE name REGEXP '^b';   --大小写不敏感

SELECT * FROM pet WHERE name REGEXP BINARY '^b';  --大小写敏感

SELECT * FROM pet WHERE name REGEXP 'fy$';

SELECT * FROM pet WHERE name REGEXP 'w';

SELECT * FROM pet WHERE name REGEXP '^.....$';

SELECT * FROM pet WHERE name REGEXP '^.{5}$';

SELECT owner, COUNT(*) FROM pet GROUP BY owner;

SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;

CREATE TABLE event (
		name VARCHAR(20),
		date DATE,
		type VARCHAR(15),
		remark VARCHAR(255)
		);

SELECT pet.name, 
	   (YEAR(date)-YEAR(birth)) - (RIGHT(date, 5)<(RIGHT(birth,5)) AS age,     /* RIGHT, 取出右边的5个字符*/ 
		remark
		FROM pet INNER JOIN event ON pet.name = event.name    /* 内连接 */
		WHERE event.type = 'litter';

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1 INNER JOIN pet AS p2
	ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

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));

SELECT article, dealer, price 
FROM shop
WHERE price = (SELECT MAX(price) FROM shop);

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price    
WHERE s2.article IS NULL;         

	将同一个表进行左连接, 如果s2.article IS NULL, 说明s1中的这条记录的price不小于s2中的所有price
	下面语句的执行结果可以辅助理解:
		SELECT s1.article, s1.dealer, s1.price,s2.article,s2.price
		FROM shop s1
		LEFT JOIN shop s2 ON s1.price < s2.price;

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);

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN(
	SELECT article, MAX(price) AS price
	FROM shot
	GROUP BY article) AS s2
ON s1.article = s2.article and s1.price = s2.price;

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

SELECT @min_price:=MIN(price), @max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

CREATE TABLE t1(
		year YEAR(4),
		month INT(2) UNSIGNED ZEROFILL,
		day INT(2) UNSIGNED ZEROFILL);

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days 
FROM t1 GROUP BY year,month;

mysqladmin -u root -p  status

mysqlcheck    //mysqld运行时使用
myisamchk     //mysqld关闭时使用

mysqldump

show variables;
set wait_timeout = 750;    //设置修改会话变量