mysql参考资料_MySQL参考资料

最新推荐文章于 2024-06-28 15:42:05 发布
最新推荐文章于 2024-06-28 15:42:05 发布
阅读量240 收藏
点赞数
文章标签： mysql参考资料
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_29786123/article/details/113651894
版权

参考资料

安装之后，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

也可以参考本文前面的参考资料，查阅本地的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>

朱太帅
关注
MYSQL参考资料
08-21
MYSQL参考资料:MySQL入门学习，5.0新特性，全面接触SQL语法 chm和pdf的文件。
PHP操作MySQL的mysql_fetch_* 函数的常见用法教程
10-23
主要介绍了PHP中操作MySQL的mysql_fetch函数的常见用法教程,文中提到了其下fetch_array和mysql_fetch_row以及mysql_fetch_object函数的使用,需要的朋友可以参考下
MySQL 参考资料合集
weixin_33991418的博客
06-23 203
2019独角兽企业重金招聘Python工程师标准>>> ...
MySQL基础大全（看这一篇足够！！！）
最新发布
weixin_35856285的博客
06-28 7569
自己看MySQL的总结，一方面加深自己的记忆，一方面与君共享
MySQL优质参考资料、
一蓑烟雨任平生
11-15 351
文档 1.MySQL官方文档：https://dev.mysql.com/doc/refman/5.7/en/ MySQL官方文档是写作本书时参考最多的一个资料。说实话，文档写的非常通俗易懂，唯一的缺点就是太长了，导致大家看的时候无从下手。 2.MySQL Internals Manual：https://dev.mysql.com/doc/internals/en/ 介绍MySQL如何实现各种功能的文档，写的比较好，但是太少了，有很多章节直接跳过了。 何登成的github：http
MySQL参考资料
A Flying Bird
01-19 794
MySQL中文参考手册
学习MySQL的参考资料
iongzhidun1的博客
05-20 550
以下是一些参考资料 MySQL Architecture 1.      UseMySQL client programs to interface with the MySQL Server interactively and inbatch o   http://dev.mysql.com/doc/refman/5.6/en/programs-client.html 2
PHP+MySQL使用mysql_num_rows实现模糊查询图书信息功能
10-18
主要介绍了PHP+MySQL使用mysql_num_rows实现模糊查询图书信息功能,涉及php使用mysql的like查询语句进行模糊查询以及mysql_num_rows进行结构统计的相关操作技巧,需要的朋友可以参考下
wechat_mysql.rar_wechat_wechat mysql_微信 php_微信php
09-21
微信公众号的后台程序，php源码。微信连mysql的示范，可以参考。
mysql5.1_zh_chm开发手册
12-06
3. "[MySQL]MySQL.chm"：这个可能是通用的MySQL参考资料，涵盖多个版本的基础知识，包括SQL语法、数据类型、查询操作、索引、事务处理等。 4. "[MySQL]MySQL中文参考手册_.chm"：这个名字可能表示这是一个更全面的...
MySQL_5.5中文参考手册.pdf
08-20
MySQL 5.5中文参考手册.pdf MySQL 5.5中文参考手册是 MySQL 官方提供的中文参考手册，涵盖 MySQL 5.5 至 5.5.2-alpha 版本的所有功能和特性。本手册由 MySQL 合作伙伴 GreatLinux 翻译，提供了详细的 MySQL 参考...
MySQL++资料
11-02
搜集并整理的相关资料: 1 《MySQL++ V3.1.0 用户手册(中文).txt》 自由骑士笃志翻译的来自http://freeknight.jimdo.com/ 2 《mysql++基本用法报告及使用规范.pdf》 作者 常兴龙 2007-6-1 3 《利用MYSQL ++进行数据库开发.doc》 作者未知，时间较早，讲解了vc6的配置，以及一个简单封装
mysql 学习资料
05-30
mysql的相关学习资料
MySQL最全资料
11-08
最新的mysql数据库知识，选择这个文档是没有错误的
安装MySQL参考资料
Hultron的博客
02-08 496
mysql-5.7.10-winx64 MySQL服务无法启动，服务没有报告任何错误，请键入 NET HELPMSG 3534 以获得更多的帮助MySQL 5.6 for Windows 解压缩版配置安装ps. 如果安装过程中出错，想要重新安装，只需要删除所有相关文件，然后在控制面板中禁用MySQL。然后，重启即可。
【MySQL】学习资料
sunriver2000的专栏
07-15 1318
MYSQL数据库相关学习资料整理： MYSQL 5.7官方文档：https://dev.mysql.com/doc/refman/5.7/en/ MYSQL 8.0官方文档：https://dev.mysql.com/doc/dev/mysql-server/8.0.0/ MYSQL 5.1中文参考手册网页版：http://tool.oschina.net/apidocs/apidoc...
mysql的参考资料
zhenjw
09-03 102
[url]http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#char[/url]
MySQL版本升级参考资料【转】
weixin_33859504的博客
05-04 104
MySQL升级参考资料 MySQL 升级的最佳实践 - 技术翻译 - 开源中国社区https://www.oschina.net/translate/mysql-upgrade-best-practices?lang=chs&page=2# MySQL 5.6升级至MySQL 5.7--------版本升级最佳实战-sea-51CTO博客http://blog.51cto.com/li...
mysql参考
junior_programmer的博客
10-22 106
insert into // insert into ... onduplicate key update 重复则执行update为mysql特性语法 insert into table(col1,col2,...) values(val1-1,val1-2,...),(val2-1,val2-2),... on duplicate key update (col1,....
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值