从employee讲解mysql

最新推荐文章于 2024-08-03 21:25:25 发布

lovekun_03

最新推荐文章于 2024-08-03 21:25:25 发布

阅读量711

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/lovekun_03/article/details/62236846

版权

官方提供的数据库样本employee

本文会以官方employee样本数据库作为DEMO,对数据库的基本操作以及优化等内容进行分析，总结.
官方提供的employee样本数据库共包含6张表，默认引擎是InnoDB.
下面简单说明下具体的安装导入的过程:
解压后,进入目录,有employees.sql文件.该sql脚本默认会使用InnoDB引擎,如需更改,在脚本中修改注释部分即可

   set storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;

需要注意的是，官方给的导入语句是:

    # 导入
    mysql -t < employees.sql            
    # 校验
    time mysql -t < test_employees_sha.sql

但我们需要根据自己数据库的用户密码进行修改

    #导入
    mysql -t -u root -p < employees.sql   
    #校验      
    time mysql -t -u root -p < test_employees_sha.sql

本文采用的官方employee样本数据库版本，已上传七牛云:官方employee样本数据库

关于mysql忘记用户密码解决方案

如果忘记用户密码，以下两个链接给出了解决方案:
http://www.cnblogs.com/linuxnotes/archive/2013/03/09/2951101.html
https://segmentfault.com/n/1330000004000000
如果仍然无法解决，直接卸载重装:
Mac下卸载重装的过程:
首先停止mysql服务，依次执行以下命令：

    sudo rm /usr/local/mysql
    sudo rm -rf /usr/local/mysql*
    sudo rm -rf /Library/StartupItems/MySQLCOM
    sudo rm -rf /Library/PreferencePanes/My*
    sudo rm -rf /Library/Receipts/mysql*
    sudo rm -rf /Library/Receipts/MySQL*
    sudo rm -rf /var/db/receipts/com.mysql.*

编辑/etc/hostconfig，删除其中的MYSQLCOM=-YES-这行。
mac下安装mysql过程比较简单，安装完成后，如果直接在用户目录下执行mysql命令，会报错：command not found。需要把mysql命令映射到/usr/bin中：
执行命令：

    ln -s /usr/local/mysql/bin/mysql /usr/bin

数据类型

整形：存在5中确切的整数类型。

数据类型	字节	有符号范围	无符号范围
TINIINT	1字节	-128~127（ $[-2^7, 2^7-1]$ ）	0~255 （ $[0, 2^8-1]$ ）
SMALLINT	2字节	-32768~32767 （ $[-2^15, 2^15-1]$ ）	0~65535 （ $[0, 2^16-1]$ ）
MEDIUMINT	3字节	-128~127 （ $[-2^23, 2^23-1]$ ）	0~255 （ $[0, 2^24-1]$ ）
INT	4字节	-2147483648~2147483647（ $[-2^31, 2^31-1]$ ）	0~4294967295（ $[0, 2^32-1]$ ）
BIGINT	8字节	$[-2^63, 2^63-1]$	0~255

* 浮点型：

FLOAT和DOUBLE，
(M是数字总位数,D是小数点后面的位数,如果M和D被省略,根据硬件允许的限制来保存值)
FLOAT[(M,D)]: 单精度浮点数精确到大约7位小数位
DOUBLE[(M,D)]

日期时间型：

类型	存储需求
YEAR	1字节
TIME	3字节
DATE	3字节
DATETIME	8字节
TIMESTAMP	4字节

* 字符型：

定长类型(在存储过程中,不论所存储内容是否小于M个字节,都会按时M个字节来存储)
CHAR(M) M个字节,0<=M<=255
变长类型(根据存储内容的长度来决定存储多少字节)
VARCHAR(M) L+1个字节,其中L<=M且0<=M<=65535

基本SQL语句总结

最新安装的mysql默认有四个数据库，分别为information_schema, mysql, performance_schema, test。

常用命令

显示当前服务器版本：

SELECT VERSION();

显示当前日期时间：

SELECT NOW();

显示当前列表：

SELECT USER();

显示当前用户：

SELECT CURRENT_USER();

显示当前已经打开的数据库：

SELECT DATABASE();

数据库操作

选择数据库,使用USE语句.

USE <数据库名>;

创建数据库(增)

CREATE DATABASE [IF NOT EXISTS] <数据库名> [DEFAULT] CHARACTER SET = <字符集>;

删除数据库(删)

DROP DATABASE [IF EXISTS] <数据库名>

修改数据库(改)

ALTER DATABASE <数据库名> [DEFAULT] CHARACTER SET = <字符集>;

查看数据库列表(查)

SHOW DATABASES [LIKE 'pattern' | WHERE expr];

查看数据库创建过程(查)

SHOW CREATE DATABASE <数据库名>;

employees数据库中创建过程如下:

mysql> show create database employees;
+-----------+----------------------------------------------------------------------+
| Database  | Create Database                                                      |
+-----------+----------------------------------------------------------------------+
| employees | CREATE DATABASE `employees` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

这里“/!40100 DEFAULT CHARACTER SET latin1 /”,是何意？
mysql中的注释方法有三种:

# 注释内容
– 注释内容
/* 注释内容 * /

这一段语句表示在某些情况下会被mysql进行解析，某些情况就是指MySQL>=4.1.0 (4.01.00)时的情况。
! 就是表示强制执行注释里的内容。这样写法不会影响数据结构，同时可以保证在导入和导出的过程中，可以正常的解析1。

表操作

创建新表(增)

CREATE TABLE [IF NOT EXISTS] <表名>(<字段名> <字段类型>,...);

删除表(删)

DROP TABLE <表名>;

重命名表名

ALTER TABLE <表名> RENAME <新表名>

查看数据库下的所有数据表(查)

SHOW TABLES [FROM <数据库名>] [LIKE 'pattern' | WHERE expr];

查看数据表结构(查)

SHOW COLUMNS FROM <表名>;

如下是employees数据库中employees表的创建过程:

mysql> show create table employees;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                   |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

数据表的更改语句ALTER TABLE涉及的内容比较多，所以单独拿出来进行讨论。通过ALTER TABLE语句可以对创建删除索引，更改存储引擎，增加和删除列，改变数据列的数据类型，更改字符集等内容。
更改数据列或者数据列的数据类型，使用CHANGE或Modify进行更改。
区别在于，CHANGE能够在更改数据列的数据类型的同时对数据列的名称进行更改。

-- 添加单列
ALTER TABLE <表名> ADD [COLUMN] <字段名> <字段定义> [FIRST | AFTER <字段名>];
-- 修改单列
ALTER TABLE <表名> [modify | change] [COLUMN] <字段名> <字段定义> [FIRST | AFTER <字段名>];
-- 添加多列
ALTER TABLE <表名> ADD [COLUMN] (<字段名> <字段定义>, ...);
-- 删除列
ALTER TABLE <表名> DROP [COLUMN] <字段名>, DROP [COLUMN] <字段名>,...;
-- 删除主键约束
ALTER TABLE <表名> DROP PRIMARY KEY;
-- 更改数据列类型
ALTER TABLE <表名> MODIFY <原字段名> <原字段类型> <新字段类型>;
-- 更改数据列名称和类型
ALTER TABLE <表名> CHANGE <原字段名> <新字段名> <原字段类型> <新字段类型>;
-- 更改数据表的字符集
ALTER TABLE <表名> MODIFY <字段名> <字段类型> CHARACTER SET <字符集>;
-- 添加普通索引
ALTER TABLE <表名> ADD INDEX <索引名>(columnList,...);
-- 添加UNIQUE索引
ALTER TABLE <表名> ADD UNIQUE (columnList,...);
-- 添加主键索引
ALTER TABLE <表名> ADD PRIMARY KEY (columnList,...);
-- 更改表的存储引擎
ALTER TABLE <表名> ENGINE=InnoDB;  
-- 更改数据表名称。  
RENAME TABLE <表名> <原表名> TO <新表名>; 
ALTER TABLE <表名> RANAME TO <新表名>；

数据操作

新增数据(增),可以省略列名称，如果省略列名称，需要为所有字段赋值。

INSERT [INTO] <表名>(<字段名>,...) values(<字段值>,...);

删除数据(删)

DELETE FROM <表名> [WHERE expr];

更新数据(改)

UPDATE <表名> SET <字段名> = value [WHERE expr];

数据的检索

查询操作也是比较负责，故单独进行讨论:

单表的条件检索

SELECT语句的基本语法如下:

SELECT <字段>,... 
FROM <表名>,... 
WHERE rowConstant
GROUP BY groupColumns
ORDER BY sortingColumns
HAVING groupConstants
LIMIT count;

用联结方式进行多表检索

通过内连接或者外连接的方法，对多表进行检索。
所谓内连接，就是在将两张表的所有数据一一匹配，在匹配出来的表中进行查询
外连接又分为左联结和右联结，左联结就是依据左表的字段在右表中依次寻找对应的数据进行匹配得到一张新表，右联结是左联结对应相反的过程。

使用子查询进行多表检索

子查询就是一种sql语句的嵌套使用，通过使用关键字: IN, NOT IN, ALL, ANY, SOME, EXISTS, NOT EXISTS进行查询。

视图

employees表中有多个字段，但是如果，大部分情况下，我只想知道first_name, last_name, gender的内容时，就可以使用视图，将这三个字段建立一个视图，便可以直接对该视图进行查询。

CREATE VIEW <视图名> AS SELECT first_name, last_name, gender FROM employees;

这里，我们创建了一个视图名为viewName的视图，通过select语句，可以对该视图进行查询:

SELECT * FROM <视图名>;

视图本质是一张虚拟表，通过对实体表的映射，提供一种更加便捷的查询方式。
如何查看自己当前数据库中存在多少视图呢？
视图的相关数据会存储在information_schema中的VIEWS表里，对该表进行查询，可以得到所有当前存在的视图信息。

数据表约束

约束保证数据的完整性和一致性，分为表级约束和列级约束

自动编号：AUTO_INCREMENT，默认情况下，起始值为1，每次的增量为1，必须和主键组合使用，修饰的字段必须为数值类型。
非空约束：空值与非空，NULL 和 NOT NULL。
主键约束：PRIMARY KEY。每张数据表只能存在一个主键。主键保证记录的唯一性，主键自动为NOT NULL。
唯一约束：UNIQUE KEY。唯一约束可以保证记录的唯一性，唯一约束的字段可以为空值（NULL），每张数据表可以存在多个唯一约束。
默认约束：DEFAULT。当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。
外键约束：FOREIGN KEY。保证数据一致性，完整性。实现一对一或一对多关系。
外键约束要求：
- 父表和子表必须使用相同的存储引擎，而且存储引擎只能为InnoDB，而且进制使用临时表。
- 外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同。
- 外键列和参照列必须创建索引，如果外键列不存在索引的话，mysql将自动创建索引。
  外键约束的参照操作：
  在外键约束创建后，在更新表的时候，子表是否也进行相应操作：
- CASCADE：从父表删除或更新，且自动删除或更新子表中匹配的行。
- SET NULL：从父表删除或更新行，并设置子表中的外键列维NULL，如果使用该选项，必须保证子表没有指定NOT NULL
- RESTRICT：拒绝对父表的删除或更新操作。

查询优化

数据库的长连接和短连接

通常的短连接操作步骤是：
连接—–>数据传输——->关闭连接
而长连接通常是：
连接—–>数据传输——–>保持连接——–>数据传输——–>保持连接——–>…………….——–>关闭连接
这样就需要长连接在没有数据通信时，定时发送数据包，以维持连接状态，短连接在没有数据传输时直接关闭就可以。

长连接主要用于少数客户端与服务器频繁通信，因为这时候如果用短连接频繁通信会发生Socket出错，并且频繁创建socket连接也是对资源的浪费。
但是对于服务端来说，长连接也会耗费一定的资源，需要专门的现场来负责维护连接状态。

数据库连接池

对象池化的思想是：将用过的对象保存下来，等下一次需要这种对象的时候，再拿出来重复使用，从而在一定程度上减少频繁穿件对象所造成的开销。采用对象池化的本意是通过减少对象的生成次数，减少花在对象初始化上的开销，从而提高整体的性能。
由于打开数据库连接比较耗时，所以连接池机制预先打开N个数据库连接，把它们缓存起来，当需要使用数据库的时候就直接使用这些已经打开的连接，从而节省时间。

MySql视图

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
从employee讲解mysql

官方提供的数据库样本employee本文会以官方employee样本数据库作为DEMO,对数据库的基本操作以及优化等内容进行分析，总结. 官方提供的employee样本数据库共包含6张表，默认引擎是InnoDB. 下面简单说明下具体的安装导入的过程: 解压后,进入目录,有employees.sql文件.该sql脚本默认会使用InnoDB引擎,如需更改,在脚本中修改注释部分即可 set st
复制链接

扫一扫