MySQL入门,原理及优化学习总结 (一)

mac2022-07-05 14

MySQL原理及优化学习总结

1、数据库介绍篇1.1什么是数据库1.2数据库介绍1.3数据库的分类1.4什么是关系型数据库 2、mysql在linux-安装篇2.1、vmware中安装linux注意事项2.1.1、记得关闭防火墙2.1.2、创建统一的管理目录2.1.3软件环境2.1.4安装环境 2.2、centos6.9安装mysql2.2.1、检查是否有自带的mysql2.2.2、卸载自带的mysql2.2.3、下载mysql安装包2.2.4、上传安装包到linux服务器2.2.5、安装2.2.6、查看初始化密码2.2.7、启动mysql并登录2.2.8、修改密码2.2.9、退出mysql客户端2.2.10、用新密码进行登录2.2.11、远程授权2.2.12、验证远程授权是否成功 3、mysql-基础操作篇3.1、登录mysql3.2、退出mysql3.3、输入查询3.4、创建和使用数据库3.5、创建表及使用3.6、表中导入数据3.7、数据检索部分3.7.1、检索全部数据3.7.2、删除表中全部数据3.7.3、更新表中特定记录的数据3.7.4、查询特定的行3.7.4.1、查找生日在1998年以后的特定查询3.7.4.2、多条件查询（and | or） 3.7.5、检索特定的列3.7.6、排序3.7.7、日期计算3.7.8、null和not null值 4、实例

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

查看表详情

准备数据

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代替）

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、检索全部数据

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年以后的特定查询

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

优先执行括号中的逻辑

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

最新回复(0)

MySQL入门,原理及优化 学习总结 (一)