最基础的mysql笔记

    我们公司是游戏公司，数据库用的就是mysql。我之前写程序没有特别深入学习过数据库，都是小打小闹，最基本的select语句。项目中有封装一个类用于访问数据库，另外就是存储过程了。在项目中，涉及到数据库时我做日常的开发没有问题，但是总感觉少了一点自信，所以要整理一些mysql笔记。于是从mysql中文手册入手，下面是最基础的操作了。试验中用的数据库和表都在笔记中详细说明了（原手册本身很详细，我就是按照自己喜欢的方式整理一下）。一边阅读一边操作即可。

        基础概念。    

1)一个命令通常由SQL语句组成，随后跟着一个分号。

2)mysql用表格(行和列)方式显示查询输出。第一行包含列的标签，随后的行是查询结果。通常，列标签是你取自数据库表的列的名字。

    基础命令。

show databases; /*查看当前服务器上有哪些数据库*/

use <db_name>; /*选择某个数据库*/ 

show tables; /*查看当前数据库中有哪些表*/

describe <table_name>; /*查看表定义*/

    基础函数。

select database(); /*查看当前选择的数据库*/

select user();

select version(); /*查看数据库服务器版本*/

select now(); /*当前时间*/

select sin(pi()/2); /*mysql可以作为计算器*/

---

    

    连接与断开服务器。

shell> mysql -h host -u user -p /*-h指定主机地址若在本机则不需要，-u指定用户名，-p指定密码*/

Enter password: ********

...

mysql> /*表示连接mysql服务器成功*/

mysql> 提示符告诉你mysql准备为你输入命令。

shell> mysql /*本机mysql服务器没有设置用户名和密码，可以直接登陆*/

mysql>quit /*成功地连接后，输入quit (或\q)随时退出*/

---

    输入查询。

 如果你决定不想执行正在输入过程中的一个命令，输入\c取消它 。

mysql> SELECT

    -> USER()

    -> \c

mysql>

---

    创建并使用数据库 。

数据库只需要创建一次，但是在每次启动mysql会话时必须在使用前先选择它，使用USE语句来选择某个数据库。

mysql> CREATE DATABASE menagerie; /*创建数据库，描述动物园宠物*/

mysql> USE menagerie /*选择数据库*/

shell> mysql -h host -u user -p menagerie /*在登陆时，选择menagerie数据库*/

---

    创建表。

首先创建记录宠物信息的表pet，表中包含：名字、主人、种类，性别、出生和死亡日期。

使用一个CREATE TABLE语句指定你的数据库表的布局：

/*VARCHAR适合于name、owner和species列，因为列值是变长的。这些列的长度不必都相同，而且不必是20。你可以挑选从1到65535的任何长度。

很显然，birth和death列应选用DATE数据类。*/

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

mysql> SHOW TABLES; /*此时已经创建了表pet*/

mysql> DESCRIBE pet; /*查看表定义*/

---

    将数据载入表中。

1)使用语句将文件内容装载到表中。

你可以创建一个文本文件“pet.txt”，每行包含一个记录，用定位符(tab)把值分开，并且以CREATE TABLE语句中列出的列次序给出。对于丢失的值(例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用NULL值，在文件文件中使用\N（反斜线，字母N）表示。

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; /*将文件pet.txt装载到表pet*/

下面是文本文件内容（设置mysql日期格式为YYYY-MM-DD ）：

Fluffy Harold cat f 1993-02-04 \N

Claws Gwen cat m 1994-03-17 \N

Buffy Harold dog f 1989-05-13 \N

Fang Benny dog m 1990-08-27 \N

Bowser Diane dog m 1979-08-31 1995-07-29

Chirpy Gwen bird f 1998-09-11 \N

Whistler Gwen bird \N 1997-12-09 \N

Slim Benny snake m 1996-04-29 \N

假设在Windows平台，需要指定'\r\n'为行结束符。

mysql> LOAD DATA LOCAL INFILE 'D:\\pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';

2)使用INSERT语句插入记录到表中。最简单的形式是，提供每一列的值，其顺序与CREATE TABLE语句中列的顺序相同。

mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); /*这里字符串和日期值均为引号扩起来的字符串。另外，可以直接用INSERT语句插入NULL。*/

---

    从表中检索信息。

SELECT语句用来从数据表中检索信息。语句的一般格式是：

SELECT what_to_select

FROM which_table

WHERE conditions_to_satisfy;

what_to_select指出你想要看到的内容，可以是表中若干列，或*表示“所有的列”。which_table指出你想要检索数据的表。WHERE子句是可选项，如果选择该项，conditions_to_satisfy指定行必须满足的检索条件。

    1)选择所有数据。

SELECT最简单的形式是从一个表中检索所有记录：

mysql> SELECT * FROM pet;

使用UPDATE语句，修改Bowser的生日是1989。

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

    2)选择特殊行。

如果你想要验证你对Bowser的生日所做的更改，按下述方法选择Bowser的记录。

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

如果你想要知道哪个动物在1998以后出生的，测试birth列。

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

可以组合条件，例如，找出雌性的狗。

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

使用OR操作符，找出所有的snake和bird。

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

    3)选择特殊的列。

如果你不想看到表中的所有行，就命名你感兴趣的列，用逗号分开。

例如，如果你想要知道你的动物什么时候出生的，选择name和birth列。

mysql> SELECT name, birth FROM pet;

找出谁拥有宠物，使用这个查询。

mysql> SELECT owner FROM pet;

找出谁拥有宠物，使用DISTINCT检索出每个唯一的输出记录。

mysql> SELECT DISTINCT owner FROM pet;

可以使用一个WHERE子句结合行选择与列选择。例如，要想查询狗和猫的出生日期，使用这个查询。

mysql>SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';

    4)排序行。

当行按某种方式排序时，查询输出通常更容易。为了排序结果，使用ORDER BY子句。

动物生日，按日期排序。

mysql> SELECT name, birth FROM pet ORDER BY birth; /*默认排序是升序（ASC是升序）*/

字符类型列上，与所有其他比较操作类似，分类功能正常情况下是以区分大小写的方式执行的。对于某一列，可以使用BINARY强制执行区分大小写的分类功能，如：ORDER BY BINARY col_name。

mysql> mysql> SELECT name, birth FROM pet ORDER BY BINARY owner;

默认排序是升序（ASC是升序），最小的值在第一。要想以降序排序，在你正在排序的列名上增加DESC（降序 ）关键字。

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

可以对多个列进行排序，并且可以按不同的方向对不同的列进行排序。例如，按升序对动物的种类进行排序，然后按降序根据生日对各动物种类进行排序（最年轻的动物在最前面），使用下列查询。

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC; /*DESC不影响species列*/

    5)日期计算。

要想确定每个宠物有多大，可以计算当前日期和出生日期之间的差。若当前日期月份比出生日期小，则减去一年。以下查询显示了每个宠物的出生日期、当前日期和年龄。

mysql> SELECT name, birth, CURDATE(),

( YEAR(CURDATE()) - YEAR(birth) ) - 

( RIGHT(CURDATE(),5) < RIGHT(birth,5) ) /*取值为1或0*/

AS age

FROM pet;

YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD部分共5个字符。比较MM-DD值的表达式部分的值一般为1或0，整个表达式有些难懂，使用alias (age)来描述年龄这一列。

显示宠物的年龄后，按name进行排序显示。

mysql> SELECT name, birth, CURDATE(),

( YEAR(CURDATE()) - YEAR(birth) ) - 

( RIGHT(CURDATE(),5) < RIGHT(birth,5) ) /*取值为1或0*/

AS age

FROM pet ORDER BY name;

显示宠物的年龄后，按age进行排序显示。

mysql> SELECT name, birth, CURDATE(),

( YEAR(CURDATE()) - YEAR(birth) ) - 

( RIGHT(CURDATE(),5) < RIGHT(birth,5) ) /*取值为1或0*/

AS age

FROM pet ORDER BY age;

可以使用一个类似的查询来确定已经死亡动物的死亡年龄。你通过检查death值是否是NULL来确定是哪些动物，然后，对于那些非NULL值的动物，需要计算出death和birth值之间的差。

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的行则使用death IS NULL），因为NULL是特殊的值，不能使用普通比较符来比较。

mysql> SELECT * FROM pet WHERE death IS NOT NULL; /*查询不为NULL的行*/

mysql> SELECT * FROM pet WHERE death IS NULL; /*查询为NULL的行*/

如果你想要知道哪个动物下个月过生日，怎么办？对于这类计算，你只需要提取birth列的月份部分。MySQL提供几个日期的部分提取函数，例如YEAR( )、MONTH( )和DAYOFMONTH( )。在这里MONTH()是适合的函数。

最简单的查询。

mysql> SELECT name, birth, MONTH(birth) FROM pet; /*打印出生日期月份*/

查询下个月生日的动物（不关心当前月，不用考虑跨年的影响）。

DATE_ADD( )允许在一个给定的日期上加上时间间隔。如果在NOW( )值上加上一个月，然后用MONTH()提取月份，结果产生生日所在月份。

mysql> SELECT name, birth FROM pet 

WHERE MONTH(birth) = MONTH( DATE_ADD(CURDATE(), INTERVAL 1 MONTH) );

完成该任务的另一个方法是加1以得出当前月份的下一个月(在使用取模函数(MOD)后。

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

    6)NULL值操作。

NULL值可能令人感到奇怪直到你习惯它。概念上，NULL意味着“没有值”或“未知值”，且它被看作与众不同的值。为了测试NULL，你不能使用算术比较 操作符例如=、<或!=，而应该使用IS NULL和IS NOT NULL操作符。

请注意在MySQL中，0或 NULL意味着假而其它值意味着真。布尔运算的默认真值是1。

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;

NULL操作的常见错误是不能在定义为NOT NULL的列内插入0或空字符串，但事实并非如此，完全可以在定义为NOT NULL的列内插入0或空字符串，实际是NOT NULL。

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

    7)模式匹配。

SQL模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。在 MySQL中，SQL的模式默认是忽略大小写的。注意使用SQL模式时，不能使用=或!=；而应使用LIKE或NOT LIKE比较操作符。

要想找出以“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 '_____';

由MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。 

扩展正则表达式的一些字符是： 

“.”匹配任何单个的字符。\

字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

“ * ”匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符。

如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。 

为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。 

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

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}$';

---

    8)计数行。

计算你拥有动物的总数目与“在pet表中有多少行?”是同样的问题，因为每个宠物有一个记录。COUNT(*)函数计算行数，所以计算动物数目的查询应为。

mysql> SELECT COUNT(*) AS petnum FROM pet; /*多少行宠物*/

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

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;/*使用GROUP BY对每个owner的所有记录分组*/

COUNT( )和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;

若使用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;

---

    9)使用1个以上的表。

pet表追踪你有哪个宠物。如果你想要记录其它相关信息，例如在他们一生中看兽医或何时后代出生，你需要另外的表。这张表应该像什么呢？需要：

它需要包含宠物名字以便你知道每个事件属于哪个动物。

需要一个日期以便你知道事件是什么时候发生的。

需要一个描述事件的字段。

如果你想要对事件进行分类，则需要一个事件类型字段。

综合上述因素，event表的CREATE TABLE语句应为：

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

对于pet表，最容易的方法是创建包含信息的用定位符（\t）分隔的文本文件来装载初始记录。

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

假设在Windows平台，需要指定'\r\n'为行结束符。

mysql> LOAD DATA LOCAL INFILE 'D:\\event.txt' INTO TABLE event LINES TERMINATED BY '\r\n';

当他们有了一窝小动物时，假定你想要找出每只宠物的年龄。我们前面看到了如何通过两个日期计算年龄。event表中有母亲的生产日期，但是为了计算母亲的年龄，你需要她的出生日期，存储在pet表中。说明查询需要两个表。

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

关于该查询要注意的几件事情：

FROM子句列出两个表，因为查询需要从两个表提取信息。 

当从多个表组合(联结)信息时，你需要指定一个表中的记录怎样能匹配其它表的记录。这很简单，因为它们都有一个name列。查询使用WHERE子句基于name值来匹配2个表中的记录。 

因为name列出现在两个表中，当引用列时，你一定要指定哪个表。把表名附在列名前即可以实现。

你不必有2个不同的表来进行联结。如果你想要将一个表的记录与同一个表的其它记录进行比较，可以将一个表联结到自身。例如，为了在你的宠物之中繁殖配偶，你可以用pet联结自身来进行相似种类的雄雌配对：

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

---

    获得数据库和表信息。

    如果你忘记数据库或表的名字，或给定的表的结构是什么(例如，它的列叫什么)，怎么办？MySQL通过提供数据库及其支持的表的信息的几个语句解决这个问题。

mysql> SHOW DATABASES; /*列出服务器管理的数据库*/

mysql> SELECT DATABASE();/*服务器当前选择的数据库*/

mysql> SHOW TABLES; /*查看当前数据库有哪些表*/

mysql> DESCRIBE pet; /*查看表结构*/

mysql> SHOW INDEX FROM tbl_name; /*查看表索引*/

---

    在批处理模式下使用mysql。

在前面的章节中，你交互式地使用mysql输入查询并且查看结果。你也可以以批模式运行mysql。为了做到这些，把你想要运行的命令放在一个文件中，然后告诉mysql从文件读取它的输入。

shell> mysql < batch-file

C:\> mysql -e "source batch-file"

如果你需要在命令行上指定连接参数，命令应为：

shell> mysql -h host -u user -p < batch-file

shell> mysql < batch-file | more /*翻页输出*/

shell> mysql < batch-file > mysql.out /*重定向输出到文件中*/

当你以批模式运行mysql时，比起你交互地使用它时，其默认输出格式是不同的(更简明些)。如果你想要在批模式中得到交互输出格式，使用mysql -t。为了回显以输出被执行的命令，使用mysql -vvv。

下面是写的简单的批处理query.txt。

USE menagerie;

show tables;

DESCRIBE pet;

SELECT * FROM pet;

SELECT * FROM event;

shell> mysql -u root -p -e "source d:\\query.txt" -t -vvv

mysql> source d:\\query.txt