mysql基础操作文档

1、数据库介绍篇

1.1什么是数据库

数据库：保存数据的仓库。它体现我们电脑中，就是一个文件系统。然后把数据都保存这些特殊的文件中，并且需要使用固定的语言（SQL语言）去操作文件中的数据。

技术定义：

数据库(Database)是按照数据结构来组织、[存储和管理数据的建立在计算机存储设备上的仓库。

1.2数据库介绍

 

我们开发应用程序的时候，程序中的所有数据，最后都需要保存到专业软件中。这些专业的保存数据的软件我们称为数据库。

我们学习数据库，并不是学习如何去开发一个数据库软件，我们学习的是如何使用数据库以及数据库中的数据记录的操作。而数据库软件是由第三方公司研发。

1.3数据库的分类

关系型、非关系型的数据库

常见的数据库软件：

Oracle：它是Oracle公司的大型关系型数据库，它是收费的。

DB2：IBM公司的数据库，它是收费的。

SqlServer：微软数据库。收费

Sybase：Sybase公司的。 工具PowerDesign 数据库建模工具。

MySql：早期瑞典一个公司发明，后期被sun公司收购，后期被Oracle。

Java开发应用程序主要使用的数据库：

MySQL（5.5）、Oracle、DB2。

1.4什么是关系型数据库

在开发软件的时候，软件中的数据之间必然会有一定的关系存在，需要把这些数据保存在数据库中，同时也要维护数据之间的关系，这时就可以直接使用上述的那些数据库。而上述的所有数据库都属于关系型数据库。

描述数据之间的关系，并保存在数据库中，同时学习如果根据这些关系查询数据库中的数据，

关系型数据：设计数据库的时候，需要使用E-R图来描述。实体关系

E-R：实体关系图。

实体：可以理解成我们Java程序中的一个对象。在E-R图中使用 矩形(长方形) 表示。

针对一个实体中的属性，我们称为这个实体的数据，在E-R图中使用 椭圆表示。

实体和实体之间的关系：在E-R图中使用菱形表示。

 

2、mysql在linux-安装篇

##2.1、vmware中安装linux注意事项

###2.1.1、记得关闭防火墙

service iptables stop
chkconfig iptables off（关闭开机自启：所谓的永久关闭防火墙）

###2.1.2、创建统一的管理目录

mkdir -p /exprot/software
​
mkdir -p /export/servers

2.1.3软件环境

VMware、crt、centos6.9

 

2.1.4安装环境

1、VMware软件安装
​
    2、构建虚拟机
​
    3、需要配置Linux（ip,mac地址，hostname，防火墙），就可以通过crt这个客户端连接进行操作
​
    4、在linux操作系统进行安装msyql-5.6
    
    说明：因为在linux操作系统中，安装软件的方式主要有3种：1、源码安装（redis）2、rpm安装      
​
3、yum在线安装（安装MySQL为例）---linux联网（）
​

2.2、centos6.9安装mysql

 

###2.2.1、检查是否有自带的mysql

[root@hadoop-01 servers]# rpm -qa |grep mysql 
mysql-libs-5.1.73-8.el6_8.x86_64

###2.2.2、卸载自带的mysql

[root@hadoop-01 servers]# rpm -e --nodeps mysql-libs-5.1.73-8.el6_8.x86_64
[root@hadoop-01 servers]# 

###2.2.3、下载mysql安装包

###2.2.4、上传安装包到linux服务器

rz 上传文件到指定的目录（yum install lrzsz）
/export/software/mysql

###2.2.5、安装

rpm -ivh *.rpm

2.2.6、查看初始化密码

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

[root@mysql ~]# cat /root/.mysql_secret
# The random password set for the root user at Wed Aug  8 22:19:00 2018 (local time): xQkcU3kbyuZby1_V
​
[root@mysql ~]# 

###2.2.7、启动mysql并登录

#启动mysql
service mysql start

 

#登录mysql
mysql -uroot -p
(粘贴密码：xQkcU3kbyuZby1_V)

###2.2.8、修改密码

set PASSWORD=PASSWORD('123456');

###2.2.9、退出mysql客户端

mysql>quit

###2.2.10、用新密码进行登录

mysql -uroot -p
123456（新密码）

###2.2.11、远程授权

grant all privileges on *.* to 'root' @'%' identified by '123456'; 
flush privileges;

###2.2.12、验证远程授权是否成功

通过windows的mysql客户端工具连接，是否能连接上，能连接上就授权成功，没有连接上，说明没有授权成功！

3、mysql-基础操作篇

3.1、登录mysql

mysql -uroot -p
123456

3.2、退出mysql

mysql>quit

3.3、输入查询

• 查看当前mysql的版本号及当前时间

  SELECT VERSION(), CURRENT_DATE;

  mysql> SELECT VERSION(), CURRENT_DATE;
  +-----------+--------------+
  | VERSION() | CURRENT_DATE |
  +-----------+--------------+
  | 5.6.25    | 2018-08-08   |
  +-----------+--------------+
  1 row in set (0.32 sec)

• mysql中sql语句不区分大小写

  mysql> SELECT VERSION(), CURRENT_DATE;
  mysql> select version(), current_date;
  mysql> SeLeCt vErSiOn(), current_DATE;

  mysql> SELECT VERSION(), CURRENT_DATE;
  +-----------+--------------+
  | VERSION() | CURRENT_DATE |
  +-----------+--------------+
  | 5.6.25    | 2018-08-08   |
  +-----------+--------------+
  1 row in set (0.00 sec)
  ​
  mysql> select version(), current_date;
  +-----------+--------------+
  | version() | current_date |
  +-----------+--------------+
  | 5.6.25    | 2018-08-08   |
  +-----------+--------------+
  1 row in set (0.00 sec)
  ​
  mysql> SeLeCt vErSiOn(), current_DATE;
  +-----------+--------------+
  | vErSiOn() | current_DATE |
  +-----------+--------------+
  | 5.6.25    | 2018-08-08   |
  +-----------+--------------+
  1 row in set (0.00 sec)
  ​
  mysql> 

• 可以进行简单的计算（如下所示）

  mysql>SELECT SIN(PI()/4), (4+1)*5;
  ​
  mysql> SELECT SIN(PI()/4), (4+1)*5;
  +--------------------+---------+
  | SIN(PI()/4)        | (4+1)*5 |
  +--------------------+---------+
  | 0.7071067811865475 |      25 |
  +--------------------+---------+
  1 row in set (0.34 sec)

• 多条语句比较短，可以写在一行

  mysql>SELECT VERSION(); SELECT NOW();
  ​
  mysql> SELECT VERSION(); SELECT NOW();
  +-----------+
  | VERSION() |
  +-----------+
  | 5.6.25    |
  +-----------+
  1 row in set (0.00 sec)
  ​
  +---------------------+
  | NOW()               |
  +---------------------+
  | 2018-08-08 23:11:11 |
  +---------------------+
  1 row in set (0.00 sec)

• 多个字段之间可以用逗号分隔，多行组成一条语句结束以分号结束

  mysql> SELECT
  -> USER()
  -> ,
  -> CURRENT_DATE;
  +---------------+--------------+
  | USER() | CURRENT_DATE |
  +---------------+--------------+
  | jon@localhost | 2010-08-06 |
  +---------------+--------------+

• sql语句写了一半，又不想执行可以在语句末尾加上'\c'

  mysql> select 
      -> user()
      -> \c
  mysql> 

##3.4、创建和使用数据库

• 查看当前有哪些数据库

  mysql>show databases;
  ​
  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | mysql              |
  | performance_schema |
  | test               |
  +--------------------+
  4 rows in set (0.07 sec)

• 创建数据库

  mysql> CREATE DATABASE menagerie;

• 使用及切换数据库

  mysql> USE menagerie
  Database changed

3.5、创建表及使用

• 查看当前数据库有哪些表

  mysql>show tables;

• 创建一个表

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

• 校验创建表语句是否和执行的一致

  mysql>show create table pet;
  ​
  +-------+--------------------------------
  | Table | Create Table                   
  +-------+--------------------------------
  | pet   | CREATE TABLE `pet` (
    `name` varchar(20) DEFAULT NULL,
    `owner` varchar(20) DEFAULT NULL,
    `species` varchar(20) DEFAULT NULL,
    `sex` char(1) DEFAULT NULL,
    `birth` date DEFAULT NULL,
    `death` date DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
  +-------+--------------------------------

• 查看表详情

  mysql> desc 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    |       |
  +---------+-------------+------+-----+---------+-------+

• 准备数据

Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-04-29

3.6、表中导入数据

在表中导入数据的方式有两种

• 第一种：将以上数据整理成SQL语句，insert into pet....

• 第二种：通过加载文件的方式将数据导入到表中

  1、创建一个pet.txt的文件（注：每个字段中用tab键隔开，字段没有值得记录用\N代替）

  Fluffy  Harold  cat     f       1993-02-04
  Claws   Gwen    cat     m       1994-03-17
  Buffy   Harold  dog     f       1989-05-13
  Fang    Benny   dog     m       1990-08-27
  Bowser  Diane   dog     m       1979-08-31      1995-07-29
  Chirpy  Gwen    bird    f       1998-09-11
  Whistler        Gwen    bird    \N      1997-12-09      \N
  Slim    Benny   snake   m       1996-04-29

  2、加载数据

  mysql> load data local infile '/root/data/pet.txt' into table pet;
  Query OK, 8 rows affected, 6 warnings (0.06 sec)
  Records: 8  Deleted: 0  Skipped: 0  Warnings: 6

  3、校验是否加载进去

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

3.7、数据检索部分

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

3.7.2、删除表中全部数据

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

3.7.3、更新表中特定记录的数据

• 更新表中名字为Bowser的生日

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

3.7.4、查询特定的行

• 查询名字为Bowser的记录

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

说明：字符串比较不区分大小写！如下所示：

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

3.7.4.1、查找生日在1998年以后的特定查询

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 |
+----------+-------+---------+------+------------+-------+

3.7.4.2、多条件查询（and | or）

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 |
  +-------+--------+---------+------+------------+-------+

3.7.5、检索特定的列

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

• 查询不重复的字段要使用关键词DISTINCT

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

• 可以使用组合条件查询特定的列

  mysql> SELECT name, species, birth FROM pet
  -> WHERE species = 'dog' OR species = 'cat';
  +--------+---------+------------+
  | name | species | birth |
  +--------+---------+------------+
  | Fluffy | cat | 1993-02-04 |
  | Claws | cat | 1994-03-17 |
  | Buffy | dog | 1989-05-13 |
  | Fang | dog | 1990-08-27 |
  | Bowser | dog | 1989-08-31 |
  +--------+---------+------------+

3.7.6、排序

• 根据某个字段进行排序（关键词：ORDER BY ）

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

• 升降序排列（desc：降序；asc：升序）

  mysql> SELECT name, birth FROM pet ORDER BY birth desc;//降序排列
  mysql> SELECT name, birth FROM pet ORDER BY birth asc ;//升序排列

• 多列排序

  根据species字段升序排列，根据birth字段降序排列

  注： ORDER BY species 中无asc，desc，默认为升序排列

  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 |
  +----------+---------+------------+

3.7.7、日期计算

查看宠物多少岁，就可以使用计算日期的函数TIMESTAMPDIFF()

#查询当前的日期
mysql> select curdate() from pet;
+------------+
| curdate()  |
+------------+
| 2018-08-09 |
+------------+
​
#获取当年的年
mysql> select YEAR('2018-02-05') AS YEARS from pet;
+-------+
| YEARS |
+-------+
|  2018 |
+-------+
​
#获取当年的月
mysql> select month('2018-02-05') AS YEARS from pet;      
+-------+
| YEARS |
+-------+
|     2 |
+-------+
​
#获取当年的日
mysql> select day('2018-02-05') AS YEARS from pet;      
+-------+
| YEARS |
+-------+
|     5 |
+-------+

mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
-> FROM pet;

3.7.8、null和not null值

对一些字段类型要进行检查，判断某些字段是否为NULL，或者 non-NULL

mysql> SELECT name, birth, death,
-> TIMESTAMPDIFF(YEAR,birth,death) AS age
-> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+

4、实例

以下是如何解决MySQL的一些常见问题的示例。

首先创建一个表，并且导入数据

CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer));
​
​
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

1、检索表中的全部数据

select * from shop;
​
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

2、求某一列的最大值或者 最小值

SELECT MAX(article) AS article FROM shop;
​
+---------+
| article |
+---------+
|       4 |
+---------+
​
//求某一列的最小值
 select min(price) as article from shop; 
 
 +---------+
| article |
+---------+
|    1.25 |
+---------+

3.1、过滤出某个字段值最大的整条记录数据-涉及到子查询

SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
​
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.2、也可以通过关联查询来进行检索

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
​
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

4、求出每一列的最大值，并且根据某一个字段进行分组--分组topn求法

SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;
​
+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

5、

SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
​
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+