MySQL参考资料
来源：互联网 发布：php 反射查看类 编辑：程序博客网 时间：2024/06/04 21:52
参考资料
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 -pEnter password: *****Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 12Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 menagerieDatabase changedmysql> quitByeC:\Program Files (x86)\MySQL\MySQL Workbench 6.3 CE>mysql.exe -u admin -p menagerieEnter password: *****Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 13Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 2mysql> 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 \NClaws Gwen cat m 1994-03-17 \NBuffy Harold dog f 1989-05-13 \NFang Benny dog m 1990-08-27 \NBowser Diane dog m 1979-08-31 1995-07-29Chirpy Gwen bird f 1998-09-11 \NWhistler Gwen bird \N 1997-12-09 \NSlim 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: 6mysql> 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: 0mysql> 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> 0 0
- MySQL参考资料
- 学习MySQL的参考资料
- 安装MySQL参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- 参考资料
- MySQL数据库的优化
- MyBatis中Like语句使用方式
- Markdown webstorm编辑初试
- EXT.NET中Store的AutoLoad问题
- AndroisStudio_libpng warning: iCCP: Not recognizing known sRGB profile that has been edited解决办法
- MySQL参考资料
- request各个方法，获取各种路径
- HDU 1420 Prepared for New Acmer
- hdu2544 最短路（三种基本最短路算法）
- eclipse导入系统签名
- c#操作xml文件
- 数据分析---SVD，特征值和特征向量
- ./configure,make,make install的作用
- 【百金轻】：心有弱禽怎挡寒风
