mysql笔记（一）

一、mysql服务器连接

假设你已经在自己的机器上安装了mysql，并且mysql服务器已经开始运行，则可以使用如下命令连接mysql服务器

shell>mysql -h localhost -u user -p
Enter password:*********

其中参数h制定需要连接的服务器所在的机器的地址，本机为localhost，u参数制定用户名，p用于制定用户密码，在输入正确的密码后会提示连接成功，并出现mysql shell提示符，提示输入mysql sql指令，如下所示：

shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 5.1.2-alpha-standard
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

成功地连接后，可以在mysql>提示下输入QUIT (或\q)随时退出：

mysql> QUIT
Bye

一些MySQL安装允许用户以匿名（未命名）用户连接到本地主机上运行的服务器。如果你的机器是这种情况，你应该能不带任何选项地调用mysql与该服务器连接：

shell> mysql

二、创建并使用数据库

首先使用SHOW语句找出服务器上当前存在的数据库：

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

你的机器上的数据可能不同，但是mysql和test数据库通常是必须的，mysql存储用户访问权限，test数据库经常用于用户学习的工作区。当然要看到这些信息需要数据库显示权限。

如果所需的数据库存在则在访问其中的表格前需要先选择它：

mysql> USE test
Database changed

USE语句可以不使用‘；’，并且需要在一行上给出。

对于特定数据库特定用户的权限的设置可以执行这样一条命令：

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

其中grant命令用来修改用户权限，menagerie为制定的数据库，其后的‘.’可以制定固定的表格，*表示所以表格，your_mysql_name是需要修改权限的用户名，your_client_host为mysql服务器所在机器地址。

以上我们选择了需要的数据库，但如果所需的数据库不存在呢？我们需要先创建它：

mysql> CREATE DATABASE menagerie;

此命令将创建数据库menagerie，然后用USE语句选择使用此数据库。数据库只需创建一次，但在每次连接数据库时都需要选择它才可以使用，可以用如下命令在连接数据库时直接制定需要使用的数据库：

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

三、创建表

创建数据库是很容易的部分，但是在这时它是空的，正如SHOW TABLES将告诉你的：

mysql> SHOW TABLES;
Empty set (0.00 sec)

要输入数据必须先创建表格，可以使用CREATE TABLE语句制定你的数据库的布局，并且创建表格：

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

以上语句创建了一个表格，表格命为pet，其中定义了name，owner，species，sex，birth，death字段，字段类型也如语句中定义。

我们再用SHOW语句则产生了不同的输入

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+

为了签证pet是否如我们所愿的正确创建，可以使用DESCRIBE语句

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

DESCRIBE可以随时使用。

四、向表格中插入数据

可以使用LOAD和INSERT语句完成插入数据的目的。

假设我们有如下一组数据

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17

要将这些数据插入数据表，可以使用INSERT语句

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

如果要用LOAD语句，则需要创建一个pet.txt文件，每行包含一行数据，用定位符（TAB）把值分开，并且值要与TABLE中的个字段相对应，对于丢失的值可以用NULL值，然后就可以用LOAD语句

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

对于用windows下的编辑器创建的文件，需要使用

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';

因为windows下的编辑器使用\r\n作为行的结束符。

五、检索信息

SELECT 语句的一般格式是：

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

what_to_select用于指定要选择的内容，*表示所有列。which_table指出你想要从哪个表中检索数据。WHARE子句是可选项，如果选择该项，conditions_to_satisfy为每行需要满足的条件。

还是用以上示例，我们展示各种情况的用法：

1、选择所有数据

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

2、选择特殊行

mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

WHERE字段规定选择的每行的name应该都为Bowser。在mysql中大小写不敏感。

还可以有如下示例：

mysql> SELECT * FROM pet WHERE birth > '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
    -> OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

以上语句中AND，OR是逻辑操作符。

3、选择特殊列

如果你不想看到表中的所有行，就命名你感兴趣的列，用逗号分开。例如，如果你想要知道你的动物什么时候出生的，选择name

mysql> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

可以看出输出结果中有出现重复字段，如果想要只检索出每一个唯一的数据记录可以使用DISTNCT语句

mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

也可以使用WHERE子句结合行选择和列选择。

 4、分类行

ORDER子句用于对行进行排序

mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

其中ORDER BY用于制定排序的字段，默认排序为升序，也可以用DESC指定为降序排列。注意DESC关键字仅适用于在它前面的列名(birth)；不影响species列的排序顺序。

5、日期计算

CURDATE()获取当前如期，YEAR()取DATE中的年份，RIGHT()去DATE中的右面的N个字符。如DATE为YYYY-MM-DD，则RIGHT(Date，5)获取了日月。

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

AS用于指定别名。

6、NULL值操作

不能关系运算符判断是否值为NULL，而应该用IS NULL, IS NOT NULL。

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

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

在GROUP BY中，两个NULL值视为相同。

执行ORDER BY时，如果运行 ORDER BY ... ASC，则NULL值出现在最前面，若运行ORDER BY ... DESC，则NULL值出现在最后面

NULL操作的常见错误是不能在定义为NOT NULL的列内插入0或空字符串，但事实并非如此。在NULL表示"没有数值"的地方有数值。