MYSQL 笔记

连接数据库

shell> mysql -h host -u user -p
Enter password:

断开数据库

mysql> QUIT

查询版本号和当前日期

mysql> SELECT VERSION(), CURRENT_DATE;

将mysql用作一个简单的计算器：

mysql> SELECT SIN(PI()/4), (4+1)*5;

MYSQL提示符含义

|提示符|含义|

mysql>	准备好接受新命令。
->	等待多行命令的下一行。
'>	等待下一行，等待以单引号(“'”)开始的字符串的结束。
">	等待下一行，等待以双引号(“"”)开始的字符串的结束。
`>	等待下一行，等待以反斜点(‘`’)开始的识别符的结束。
/*>	等待下一行，等待以/*开始的注释的结束。

使用SHOW语句找出服务器上当前存在什么数据库：

mysql> SHOW DATABASES;

访问数据库

mysql> USE test

管理员赋予一个账号访问和操作数据库的所有权限

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
同意数据库menagerie所有权限给予your_client_host主机上的your_mysql_name用户

创建数据库

mysql> CREATE DATABASE menagerie;

使用数据库

mysql> USE menagerie

查看表

mysql> SHOW TABLES;

创建表

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

查看表的结构

mysql> DESCRIBE pet;

输入表的内容

例如 文本文件pet.txt有如下内容

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04	N/A
Claws	Gwen	cat	m	1994-03-17	N/A
Buffy	Harold	dog	f	1989-05-13	N/A
Fang	Benny	dog	m	1990-08-27	N/A
Bowser	Diane	dog	m	1979-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11	N/A
Whistler	Gwen	bird	1997-12-09	N/A
Slim	Benny	snake	m	1996-04-29	N/A

要想将文本文件“pet.txt”装载到pet表中，使用这个命令：

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
请注意如果用Windows中的编辑器（使用\r\n做为行的结束符）创建文件，应使用：
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
（在运行OS X的Apple机上，应使用行结束符'\r'。）

新增一条记录

mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

从表中检索信息

SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;

选择所有数据

mysql> SELECT * FROM pet;

删除表

mysql> DELETE FROM pet;

修正错误记录：

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

从表中选定特定行：

mysql> SELECT * FROM pet WHERE name = 'Bowser';

查询1998年以后出生的：

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 = '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

如果按照降序排列，需要在列名后面加上DESC(降序)关键字

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;

按照多个列进行排序

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
mysql> SELECT name, birth, CURDATE(), (YEAR(CURDATE())-YEAR(birth)) - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age FROM pet;

查询动物死亡时的年龄

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;

查询五月份出生的动物

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SQL模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。在 MySQL中，SQL的模式默认是忽略大小写的。下面给出一些例子。注意使用SQL模式时，不能使用=或!=；而应使用LIKE或NOTLIKE比较操作符。

要想找出以“b”开头的名字：

mysql> SELECT * FROM pet WHERE name LIKE 'b%';

要想找出以“fy”结尾的名字：

mysql> SELECT * FROM pet WHERE name LIKE '%fy';

要想找出包含“w”的名字：

mysql> SELECT * FROM pet WHERE name LIKE '%w%';

要想找出正好包含5个字符的名字，使用“_”模式字符：

mysql> SELECT * FROM pet WHERE name LIKE '_____'; #五个下划线

正则表达式

‘.’匹配任何单个的字符。
字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范
围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
“ * ”匹配零个或多个在它前面的字符。例如，“x”匹配任何数量的“x”字符，“[0-9]”匹配任何数量的
数字，而“.*”匹配任何数量的任何字符。
如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹
配，模式才匹配)。
为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。

找出以“b”开头的名字，使用“^”匹配名字的开始：

mysql> SELECT * FROM pet WHERE name REGEXP '^b';

匹配名字首字母小写‘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 '^.....$';
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

计算行数

mysql> SELECT COUNT(*) FROM pet;

想要知道每个主人有多少宠物，你可以使用COUNT( )函数：

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
使用GROUP BY对每个owner的所有记录分组，没有GROUP BY 会显示错误信息。

每种动物的数量：

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

每种性别的动物数量：

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

按种类和性别组合的动物数量：

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

当只对狗和猫进行时查询

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;

使用一个以上的表

pet表追踪你有哪个宠物。如果你想要记录其它相关信息，例如在他们一生中看兽医或何时后代出生，你需要
另外的表。这张表应该像什么呢？需要：
· 它需要包含宠物名字以便你知道每个事件属于哪个动物。
· 需要一个日期以便你知道事件是什么时候发生的。
· 需要一个描述事件的字段。
· 如果你想要对事件进行分类，则需要一个事件类型字段。

event表的CREATE TABLE语句应为：

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

name	date	type	remark
Fluffy	1995-05-15	litter 4 kittens, 3 female, 1 male
Buffy	1993-06-23	litter 5 puppies, 2 female, 3 male
Buffy	1994-06-19	litter 3 puppies, 3 female
Chirpy	1999-03-21	vet needed beak straightened
Slim	1997-08-03	vet broken rib
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday Gave him a new chew toy
Claws	1998-03-17	birthday Gave him a new flea collar
Whistler	1998-12-09	birthday First birthday

上传到数据库

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';
上面语句中，把表pet 分别起名为p1和p2，表面上看是对两个表进行检索查询。

运行脚本

shell> mysql < batch-file
shell> mysql -h host -u user -p < batch-file
windows下用
C:> mysql -e "source batch-file"

脚本中出现错误强制执行

--force

把输出保存在文件中

shell> mysql < batch-file > mysql.out

从MYSQL提示符运行脚本

mysql> source filename;
mysql> . filename

启动命令行并选择数据库

shell> mysql your-database-name

创建列表

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

列的最大值

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

每项物品的的最高价格

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;

根据两个键搜索

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'

使用UNION将两个单独的SELECT语句的输出合成到一起来更有效地解决问题

SELECT field1_index, field2_index
FROM test_table WHERE field1_index = '1' UNION SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';