MySQL参考资料

最新推荐文章于 2022-09-21 16:35:13 发布

lazying_bird

最新推荐文章于 2022-09-21 16:35:13 发布

阅读量767

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/a_flying_bird/article/details/50543266

版权

SQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

参考资料

MySQL中文参考手册
- 官网：http://dev.mysql.com/doc/refman/5.7/en/

High Performance MySQL(En-2nd):
- http://www.chinastor.org/upload/2013-12/13120411042214.pdf

High Performance MySQL(En-3nd):
- http://download.csdn.net/download/wangxm1983/4398636
- http://vdisk.weibo.com/s/iTmslx-miPU?from=page_100505_profile&wvr=6

Go－MySQL
- http://go-database-sql.org
- https://github.com/go-sql-driver/mysql/

安装之后，MySQL Workbench的Help Index会打开online help，而不是转到本地帮助文档。为此，在安装目录下搜索*.pdf，可以找到帮助文档的位置：C:\Program Files (x86)\MySQL\MySQL Documentation 5.7\refman-5.7-en.pdf

软件安装

不需要安装一个个的connect，直接安装mysql-installer-community-5.7.10.0.msi。安装过程中，如果某个组件安装失败，就先通过控制面板把已经安装的全部卸载掉，再重新安装。选择Custom，把无法安装的取消掉。

创建数据库

启动mysql.exe

参考：http://dev.mysql.com/doc/refman/5.7/en/connecting-disconnecting.html

也可以参考本文前面的参考资料，查阅本地的pdf。

C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE>mysql.exe -u admin -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

执行一些查询命令

完全按照pdf中操作。

mysql> select version(), current_date;
+------------+--------------+
| version()  | current_date |
+------------+--------------+
| 5.7.10-log | 2016-01-19   |
+------------+--------------+
1 row in set (0.00 sec)

mysql> select sin(pi()/4), (4+1)*5;
+--------------------+---------+
| sin(pi()/4)        | (4+1)*5 |
+--------------------+---------+
| 0.7071067811865475 |      25 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> select version(); select now();
+------------+
| version()  |
+------------+
| 5.7.10-log |
+------------+
1 row in set (0.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2016-01-19 20:06:03 |
+---------------------+
1 row in set (0.00 sec)

mysql> select user();
+-----------------+
| user()          |
+-----------------+
| admin@localhost |
+-----------------+
1 row in set (0.00 sec)

mysql>

创建数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql> create database menagerie;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> use menagerie
Database changed
mysql> quit
Bye

C:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE>mysql.exe -u admin -p menagerie
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

创建table

从现在开始，遵循SQL的传统，即命令用大些字母。

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATA, death DATE);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
 for the right syntax to use near 'DATA, death DATE)' at line 2
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.25 sec)

mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet                 |
+---------------------+
1 row in set (0.00 sec)

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    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql>

添加数据

从文件导入

首先创建pet.txt，内容如下：

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

然后执行命令：

mysql> LOAD DATA LOCAL INFILE 'd:/pet.txt' INTO TABLE pet;
Query OK, 8 rows affected, 6 warnings (0.04 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 6

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

mysql>

INSERT命令

mysql> INSERT INTO pet
    -> VALUES ('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL);
Query OK, 1 row affected (0.04 sec)

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

mysql>

更新数据

mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)

mysql>

WHERE部分

mysql> SELECT * FROM pet WHERE species = 'dog';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Fang   | Benny  | dog     | m    | 1990-08-27 | 0000-00-00 |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'm';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Fang   | Benny | dog     | m    | 1990-08-27 | 0000-00-00 |
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE species = 'dog' or species = 'cat';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | 0000-00-00 |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | 0000-00-00 |
| Buffy  | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Fang   | Benny  | dog     | m    | 1990-08-27 | 0000-00-00 |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE ( species = 'dog' or species = 'cat' )
    -> AND ( sex = 'm' );
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Claws  | Gwen  | cat     | m    | 1994-03-17 | 0000-00-00 |
| Fang   | Benny | dog     | m    | 1990-08-27 | 0000-00-00 |
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)

mysql>

显示部分列

mysql> SELECT name, owner, birth FROM pet WHERE ( species = 'dog' or species = 'cat' )
    -> AND ( sex = 'm' );
+--------+-------+------------+
| name   | owner | birth      |
+--------+-------+------------+
| Claws  | Gwen  | 1994-03-17 |
| Fang   | Benny | 1990-08-27 |
| Bowser | Diane | 1989-08-31 |
+--------+-------+------------+
3 rows in set (0.00 sec)

mysql> SELECT species FROM pet WHERE ( species = 'dog' or species = 'cat' )
    -> AND ( sex = 'm' );
+---------+
| species |
+---------+
| cat     |
| dog     |
| dog     |
+---------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT species FROM pet WHERE ( species = 'dog' or species = 'cat' )
    -> AND ( sex = 'm' );
+---------+
| species |
+---------+
| cat     |
| dog     |
+---------+
2 rows in set (0.00 sec)

mysql>

排序

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

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

mysql> SELECT name, owner, species FROM pet ORDER BY name;
+----------+--------+---------+
| name     | owner  | species |
+----------+--------+---------+
| Bowser   | Diane  | dog     |
| Buffy    | Harold | dog     |
| Chirpy   | Gwen   | bird    |
| Claws    | Gwen   | cat     |
| Fang     | Benny  | dog     |
| Fluffy   | Harold | cat     |
| Puffball | Diane  | hamster |
| Slim     | Benny  | snake   |
| Whistler | Gwen   | bird    |
+----------+--------+---------+
9 rows in set (0.00 sec)

mysql>

函数

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age
    -> FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2016-01-19 |   22 |
| Claws    | 1994-03-17 | 2016-01-19 |   21 |
| Buffy    | 1989-05-13 | 2016-01-19 |   26 |
| Fang     | 1990-08-27 | 2016-01-19 |   25 |
| Bowser   | 1989-08-31 | 2016-01-19 |   26 |
| Chirpy   | 1998-09-11 | 2016-01-19 |   17 |
| Whistler | 1997-12-09 | 2016-01-19 |   18 |
| Slim     | 1996-04-29 | 2016-01-19 |   19 |
| Puffball | 1999-03-30 | 2016-01-19 |   16 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age
    -> FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Puffball | 1999-03-30 | 2016-01-19 |   16 |
| Chirpy   | 1998-09-11 | 2016-01-19 |   17 |
| Whistler | 1997-12-09 | 2016-01-19 |   18 |
| Slim     | 1996-04-29 | 2016-01-19 |   19 |
| Claws    | 1994-03-17 | 2016-01-19 |   21 |
| Fluffy   | 1993-02-04 | 2016-01-19 |   22 |
| Fang     | 1990-08-27 | 2016-01-19 |   25 |
| Buffy    | 1989-05-13 | 2016-01-19 |   26 |
| Bowser   | 1989-08-31 | 2016-01-19 |   26 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

mysql>

函数用在WHERE子句中：

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

mysql> SELECT * FROM pet WHERE MONTH(birth) = 3;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | 0000-00-00 |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL       |
+----------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)

mysql>

NULL

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

mysql> SELECT * FROM pet WHERE death is NULL;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.01 sec)

mysql>

LIKE&模式匹配

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

mysql> SELECT * FROM pet WHERE species LIKE '%a%';
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | 0000-00-00 |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | 0000-00-00 |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE name LIKE '%y';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | 0000-00-00 |
| Buffy  | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Chirpy | Gwen   | bird    | f    | 1998-09-11 | 0000-00-00 |
+--------+--------+---------+------+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE name LIKE 'c%';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Claws  | Gwen  | cat     | m    | 1994-03-17 | 0000-00-00 |
| Chirpy | Gwen  | bird    | f    | 1998-09-11 | 0000-00-00 |
+--------+-------+---------+------+------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM pet WHERE species LIKE '___';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | 0000-00-00 |
| Claws  | Gwen   | cat     | m    | 1994-03-17 | 0000-00-00 |
| Buffy  | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Fang   | Benny  | dog     | m    | 1990-08-27 | 0000-00-00 |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
5 rows in set (0.00 sec)

mysql>