MySql数据库

前言

随便打开一个Web网站，比如淘宝：

为什么学习MySQL数据库？

  MySQL广占IT市场，比如淘宝、网易、百度、新浪、facebook等大部分互联网公司，都在使用mysql软件，而且在网络游戏领域，大部分的后台数据库都在使用mysql，如劲舞团、魔兽世界，还有更厉害的，中国电网、中国移动中很多项目也在使用我们的mysql。

  如果我们把mysql学好学精了，还愁没工作吗？我们甚至可以去任何IT行业公司，这不像小众 的产品，你哪怕学透了，也不一定有多少人要你。

第1章 数据库概述

1.1 什么是数据库

         数据库是持久化数据的一种介质，可以理解成用来存储和管理数据的仓库！

持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。持久化的大多数时候是将内存中的数据存储在数据库中，当然也可以存储在磁盘文件、XML数据文件中。

1.2为什么要用数据库

保存数据 的容器：数组、集合、文件

保存到文件：

• 可将数据持久化到硬盘
• 可存储大量数据
• 方便检索
• 保证数据的一致性、完整性
• 安全，可共享
• 通过组合分析，可以产生新数据

1.3常见数据库产品

u  Oracle：甲骨文

u  DB2：IBM；

u  SQL Server：微软；

u  MySQL：甲骨文；

1.4数据库相关概念

• DB

数据库（ database ）：存储数据的“仓库”。它保存了一系列有组织的数据。

• DBMS

数据库管理系统（ Database Management System ）。数据库是通过 DBMS 创

建和操作的容器

• SQL

结构化查询语言（ Structure Query Language ）：专门用来与数据库通信的语

言。

三者的关系：

1.5数据库存储数据的特点

n  将数据放到表中，表再放到库中

n  一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。

n  表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。

n  表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”

n  表中的数据是按行存储的，每一行类似于java中的“对象”

2  表——类

2  列，字段——属性

2  行——对象

第2章 MySQL数据库的介绍

2.1 MySQL产品的介绍

MySQL是一种开放源代码的关系型数据库管理系统，开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年,SUN又被Oracle收购.目前 MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库（Facebook, Twitter, YouTube）。阿里提出“去IOE”，更多网站也开始选择MySQL。[I1] 

2.2 MySQL产品的优点

• 成本低：开放源代码，一般可以免费试用
• 性能高：执行很快
• 简单：很容易安装和使用

2.2 MySQL服务端的安装和卸载

DBMS分为两类：

–  基于共享文件系统的DBMS （Access ）

–  基于客户机——服务器的DBMS   C/S

（MySQL、Oracle、SqlServer）

注：查看数据库的安装和卸载文档

1、卸载

1）软件的卸载

方式一：通过控制面板

方式二：通过电脑管家等软件卸载

方式三：通过安装包中提供的卸载功能卸载

2）清理残余文件

如果再次安装不成功，可以卸载后对残余文件进行清理后再安装

a）清除安装残余文件

b）清除数据残余文件

请在卸载前做好数据备份

c）清理注册表

如果前两步做了，再次安装还是失败，那么可以清理注册表

1：HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL服务 目录删除

2：HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL服务 目录删除

3：HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL服务 目录删除

4：HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MySQL服务 目录删除

5：HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL服务目录删除

6：HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL服务删除

注册表中的ControlSet001,ControlSet002,不一定是001和002,可能是ControlSet005、006之类

2、安装

（1）准备安装

（2）欢迎安装

（3）准许协议

（4）选择安装模式

Typical：表示一般常用的组件都会被安装，默认情况下安装到”C:\Program Files\MySQL\MySQL Server 5.5\”下。

Complete：表示会安装所有的组件。此套件会占用比较大的磁盘空间。

Custom：表示用户可以选择要安装的组件，可以更改默认按照的路径。这种按照类型最灵活，适用于高级用户。

（5）选择安装组件及安装路径

这里可以选择安装哪些部分，主要是这里可以设置两个路径：

MySQL Server的应用软件的安装路径，默认在“C:\Program Files\MySQL\MySQL Server 5.5\”

Server data files的数据存储的目录路径，默认在“C:\ProgramData\MySQL\MySQL Server 5.5\”

（6）开始安装

安装进度

系统会显示MySQL Enterprise版（企业版）的一些功能介绍界面，可以单击“Next”继续。

（7）安装完成

单击“Finish”按钮完成安装过程。如果想马上配置数据库连接，选择“Launch the MySQL Instance Configuration Wizard”复选框。如果现在没有配置，以后想要配置或重新配置都可以在“MySQL Server”的安装目录的bin目录下（例如：D:\ProgramFiles\MySQL5.5\MySQL Server 5.5\bin）找到“MySQLInstanceConfig.exe”打开“MySQL Instance Configuration Wizard”向导。

3、MySQL的配置

（1）准备开始

（2）选择配置类型

选择配置方式，“Detailed Configuration（手动精确配置）”、“Standard Configuration（标准配置）”，我们选择“Detailed Configuration”，方便熟悉配置过程。

（3）选择MySQL的应用模式

Develop Machine（开发机），使用最小数量的内存

Server Machine（服务器），使用中等大小的内存

Dedicated MySQL Server Machine（专用服务器），使用当前可用的最大内存。

（4）选择数据库用途选择界面

选择mysql数据库的大致用途：

“Multifunctional Database（通用多功能型，好）”：此选项对事务性存储引擎（InnoDB）和非事务性（MyISAM）存储引擎的存取速度都很快。

“Transactional Database Only（服务器类型，专注于事务处理，一般）”：此选项主要优化了事务性存储引擎（InnoDB），但是非事务性（MyISAM）存储引擎也能用。

“Non-Transactional Database Only（非事务处理型，较简单）主要做一些监控、记数用，对MyISAM数据类型的支持仅限于non-transactional，注意事务性存储引擎（InnoDB）不能用。

（5）配置InnoDB数据文件目录

InnoDB的数据文件会在数据库第一次启动的时候创建，默认会创建在MySQL的安装目录下。用户可以根据实际的空间状况进行路径的选择。

（6）并发连接设置

选择您的网站的一般mysql 访问量，同时连接的数目，“Decision Support(DSS)/OLAP（决策支持系统，20个左右）”、“Online Transaction Processing(OLTP)（在线事务系统，500个左右）”、“Manual Setting（手动设置，自己输一个数）”

（7）网络选项设置

是否启用TCP/IP连接，设定端口，如果不启用，就只能在自己的机器*问mysql 数据库了，我这里启用，把前面的勾打上，Port Number：3306，还有一个关于防火墙的设置“Add firewall exception ……”需要选中，将MYSQL服务的监听端口加为windows防火墙例外，避免防火墙阻断。

在这个页面上，您还可以选择“启用标准模式”（Enable Strict Mode），这样MySQL就不会允许细小的语法错误。尽量使用标准模式，因为它可以降低有害数据进入数据库的可能性。

（8）选择字符集

注意：如果要用原来数据库的数据，最好能确定原来数据库用的是什么编码，如果这里设置的编码和原来数据库数据的编码不一致，在使用的时候可能会出现乱码。

这个比较重要，就是对mysql默认数据库语言编码进行设置，第一个是西文编码，第二个是多字节的通用utf8编码，第三个，手工选择字符集。

提示：

如果安装时选择了字符集和“utf8”，通过命令行客户端来操作数据库时，有时候会出现乱码，

这是因为“命令行客户端”默认是GBK字符集，因此客户端与服务器端就出现了不一致的情况，会出现乱码。

可以在客户端执行：

mysql> set names gbk; 

可以通过以下命令查看：

mysql> show variables like ‘character_set_%‘;

对于客户端和服务器的交互操作，MySQL提供了3个不同的参数：character_set_client、character_set_connection、character_set_results，分别代表客户端、连接和返回结果的字符集。通常情况下，这3个字符集应该是相同的，才能确保用户写入的数据可以正确的读出和写入。“set names xxx;”命令可以同时修改这3个参数的值，但是需要每次连接都重新设置。

（9）安全选择

选择是否将mysql 安装为windows服务，还可以指定Service Name（服务标识名称，例如我这里取名为“MySQL5.5”），是否将mysql的bin目录加入到Windows PATH环境变量中（加入后，就可以直接使用bin下的命令）”，我这里全部打上了勾。

（10）设置密码

这一步询问是否要修改默认root 用户（超级管理）的密码（默认为空），“New root password”如果要修改，就在此填入新密码，“Confirm（再输一遍）”内再填一次，防止输错。（如果是重装，并且之前已经设置了密码，在这里更改密码可能会出错，请留空，并将“Modify Security Settings”前面的勾去掉，安装配置完成后另行修改密码）

“Enable root access from remotemachines（是否允许root 用户在其它的机器或使用IP地址登陆，如果要安全，就不要勾上，如果要方便，就勾上它）”。如果没有勾选，默认只支持localhost和127.0.0.1连接。

最后“Create An Anonymous Account（新建一个匿名用户，匿名用户可以连接数据库，不能操作数据，包括查询，如果要有操作数据的权限需要单独分配）”，一般就不用勾了

（11）准备执行界面

（12）完成

2.3 MySQL的安装目录

u  bin目录中都是可执行文件；

u  my.ini文件是MySQL的配置文件；

u  MySQL的数据存储目录为data

u  data目录通常在C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data位置。

u  在data下的每个目录都代表一个数据库。

2.4 MySQL环境变量的配置

将C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin;添加到path中

第3章 MySQL的使用

1、启动和停止服务

MySQL软件的服务器端必须先启动，客户端才可以连接和使用使用数据库。

启动服务的方式：

方式一：图形化方式

“我的电脑/计算机”-->右键-->“管理”-->“服务”-->启动和关闭MySQL

“开始菜单”-->“控制面板”-->“管理工具”-->“服务”-->启动和关闭MySQL

“任务管理器”-->“服务”-->启动和关闭MySQL

方式二：命令行

以管理员身份运行

net  start  MySQL服务名

net  stop  MySQL服务名

2、客户端登录

方式一：MySQL自带客户端

“开始菜单”-->MySQL-->MySQL Server 5.5 --> MySQL 5.5 Command Line Client

仅限于root用户

方式二：命令行

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

例如：mysql -h localhost -P 3306 -u root -proot        

注意：

（1）-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格

                  mysql -hlocalhost -P3306 -uroot -proot

（2）密码建议在一行输入

                  mysql -h localhost -P 3306 -u root -p

                  Enter password:****

（3）如果是连本机：-hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略

                  简写成：mysql -u root -p

                              Enter password:****

连接成功后，有关于MySQL Server服务版本的信息，还有第几次连接的id标识。

也可以在命令行通过以下方式获取MySQL Server服务版本的信息

或登录后，通过以下方式查看当前版本信息：

方式三：可视化工具

例如：Navicat Preminum，SQLyogEnt等工具

还有其他工具：mysqlfront,phpMyAdmin

（1）Navicat Preminum

（2）SQLyog

3、MySQL的基本命令

启动和关闭mysql服务

方式一：

l  启动：net start mysql；

l  关闭：net stop mysql；

方式二：

在启动mysql服务后，打开windows任务管理器，会有一个名为mysqld.exe的进程运行，所以mysqld.exe才是MySQL服务器程序。

客户端登录退出mysql

在启动MySQL服务器后，我们需要使用管理员用户登录MySQL服务器，然后来对服务器进行操作。登录MySQL需要使用MySQL的客户端程序：mysql.exe

l  登录：mysql -u root -p root -h localhost；

• -u：后面的root是用户名，这里使用的是超级管理员root；
• -p：后面的root是密码，这是在安装MySQL时就已经指定的密码；
• -h：后面给出的localhost是服务器主机名，它是可以省略的，例如：mysql -u root -p root；

l  退出：quit或exit；

在登录成功后，打开windows任务管理器，会有一个名为mysql.exe的进程运行，所以mysql.exe是客户端程序。

第4章 SQL语句

4.1 SQL概述

1. 什么是SQL

SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。SQL标准有：

l  1986年，ANSI X3.135-1986，ISO/IEC 9075:1986，SQL-86

l  1989年，ANSI X3.135-1989，ISO/IEC 9075:1989，SQL-89

l  1992年，ANSI X3.135-1992，ISO/IEC 9075:1992，SQL-92（SQL2）

l  1999年，ISO/IEC 9075:1999，SQL:1999（SQL3）

l  2003年，ISO/IEC 9075:2003，SQL:2003

l  2008年，ISO/IEC 9075:2008，SQL:2008

l  2011年，ISO/IEC 9075:2011，SQL:2011

l  2016年，ISO/IEC 9075:2016，SQL:2016

这些标准就与JDK的版本一样，在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。

虽然SQL可以用在所有关系型数据库中，但很多数据库还都有标准之后的一些语法，我们可以称之为“方言”。例如MySQL中的LIMIT语句就是MySQL独有的方言，其它数据库都不支持！当然，Oracle或SQL Server都有自己的方言。

2. SQL语法要求

l  SQL语句可以单行或多行书写，以分号结尾；

l  可以用空格和缩进来来增强语句的可读性；

l  关键字不区别大小写，建议使用大写；

4.2 分类

l  DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；

l  DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

l  DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

l  DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

4.3 DQL

　　DQL就是数据查询语言，数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。

语法：

SELECT

selection_list /*要查询的列名称*/

FROM

table_list /*要查询的表名称*/

WHERE

condition /*行条件*/

GROUP BY

grouping_columns /*对结果分组*/

HAVING

condition /*分组后的行条件*/

ORDER BY

sorting_columns /*对结果分组*/

LIMIT

offset_start, row_count /*结果限定*/

创建名：

l  学生表：stu

l  雇员表：emp

l  部分表：dept

1.基础查询

1.1　查询学生表中的所有列

1.2　查询指定列

2.条件查询

介绍

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

l  =、!=、<>、<、<=、>、>=；

l  AND、OR、NOT

l  BETWEEN…AND、IN(set)、IS NULL

案例

案例1：查询工资>10000的员工信息

案例2：查询 job_id不是‘IT_PROG‘的员工姓名、job_id、salary

案例3：查询工资>10000并且小于20000的员工姓名、工资、年薪

案例4：查询部门编号不在90到120之间的并且 工资>15000的员工信息

3.模糊查询

• like

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

#案例1：查询员工信息表中姓名第一个字符为e的员工信息

#案例2：查询员工信息表中姓名第二个字符为e，第五个字符为a的员工信息

#案例3：查询姓名中前面字符为ernst的员工信息

#案例4：查询姓名中第二个字符为_的员工信息

#方式一：直接通过\进行转义

#方式二：通过escape关键字指定转义字符

• between and

特点：

①用于筛选某个字段或表达式是否在指定的区间范围

②等价于 使用逻辑表达式的效果，只是语法上更加简单

③两个区间值不能调换顺序

• 两个区间值包含

>=左区间 and <=右区间

#案例1：查询年薪在100000到200000的员工年薪和姓名

#案例2：查询年薪不在100740到187200的员工年薪和姓名

• in

#案例1：查询工种编号是IT_PROT或是ST_CLERK或是AD_VP的员工信息

• is null

#案例1：查询哪个员工没有奖金

#案例2：查询哪个员工有奖金

4.字段控制查询

• 去除重复记录

去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

• 查看雇员的月薪与佣金之和(IFNULL函数)

　　因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

• 给列名添加别名

方式一：

select 列名 as 别名 from 表名;

方式二：

select 列名 别名 from 表名;

5.排序 order by

1）按单个字段进行升序 ★

案例1：按工资从低到高排序

案例2：按工资从低到高排序

2）.按多个字段排序

案例1：部门编号>50的按工资从高到低排序，如果一样，再按frist_name升序

3）.按表达式排序

案例：将姓名中包含e字符的年薪和姓名显示出来，并且按年薪进行降序

4）.按别名排序

案例：将姓名中包含e字符的年薪和姓名显示出来，并且按年薪进行降序

5）.按函数排序

案例：显示所有有奖金的员工姓名、奖金、姓名的长度，按姓名的长度从低到高排序

6.常见函数

MySQL数据库提供了很多函数包括：

l  数学函数；

l  字符串函数；

l  日期和时间函数；

l  条件判断函数；流程控制函数；

l  系统信息函数；

l  加密函数；

l  格式化函数；

1）、数学函数

2）、字符串函数

3）、日期时间函数

l  （1）DATE_ADD(datetime,INTERVAL  expr  type)

l  （2）DATE_FORMAT(datetime ,fmt)和STR_TO_DATE(str, fmt)

4）、流程函数

7.分组（聚合）函数

聚合函数是用来做纵向运算的函数：

l  COUNT()：统计指定列不为NULL的记录行数；

l  MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

l  MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

l  SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

l  AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

• COUNT

当需要纵向统计时可以使用COUNT()。

l  查询emp表中记录数：

l  查询emp表中有佣金的人数：

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

l  查询emp表中月薪大于2500的人数：

l  统计月薪与佣金之和大于2500元的人数：

l  查询有佣金的人数，以及有领导的人数：

• SUM和AVG

当需要纵向求和时使用sum()函数。

l  查询所有雇员月薪和：

l  查询所有雇员月薪和，以及所有雇员佣金和：

l  查询所有雇员月薪+佣金和：

l  统计所有员工平均工资：

• MAX和MIN

l  查询最高工资和最低工资：

8.分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。

案例：

1）简单的分组

案例1：查询每个工种的员工平均工资

2）可以实现分组前的筛选

案例1：查询邮箱中包含a字符的 每个部门的最高工资

案例2：查询有奖金的每个领导手下员工的平均工资

3）可以实现分组后的筛选

案例1：查询哪个部门的员工个数>5

案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

4）可以实现排序

案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

5）按多个字段分组

案例：查询每个工种每个部门的最低工资,并按最低工资降序

9.连接查询

概念：

笛卡尔集会在下面条件下产生:

–       省略连接条件

–       连接条件无效

–       所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件

分析：

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

案例分析：

你能想像到emp和dept表连接查询的结果么？emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。

Sql语句：SELECT * FROM emp,dept;

查询结果：

也就你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了。

Sql语句：SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno ;

查询结果：

上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。

Sql语句：

SELECT emp.ename,emp.sal,emp.comm,dept.dname

FROM emp,dept

WHERE emp.deptno=dept.deptno;

查询结果：

给表指定别名的写法：

SELECT e.ename,e.sal,e.comm,d.dname

FROM emp AS e,dept AS d

WHERE e.deptno=d.deptno;

9.1　内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

注意：inner可以省略、on是连接条件

内连接的特点：查询结果必须满足条件。

9.2　外连接（左连接、右连接）

外连接的特点：查询出的结果存在不满足条件的可能。

• 左连接：

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

注意：OUTER可以省略

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

• 右连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

9.3　自然连接（不常用）

大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：

l  两张连接的表中名称和类型完成一致的列作为条件，例如emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！

当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

SELECT * FROM emp NATURAL JOIN dept;

SELECT * FROM emp NATURAL LEFT JOIN dept;

SELECT * FROM emp NATURAL RIGHT JOIN dept;

内连接案例

外连接案例

10.子查询

概念：

    出现在其他语句的内部的select语句，称为子查询或内查询

         里面嵌套其他select语句的查询语句，称为主查询或外查询

  子查询不一定必须出现在select语句内部，只是出现在select语句内部的时候较多！

示例：

分类：

         单行子查询

         特点：子查询的结果集只有一行一列

         多行子查询

         特点：子查询的结果集有多行一列

说明：

         1、子查询语句需要放在小括号内，提高代码的阅读性

         2、子查询先于主查询执行，一般来讲，主查询会用到子查询的结果

         3、如果子查询放在条件中，一般来讲，子查询需要放在条件的右侧

         示例：where job_id>(子查询)

         不能写成：where (子查询)<job_id

         4、单行子查询对应的使用单行操作符：> < >= <= = <>

            多行子查询对应的使用多行操作符:in 、any 、all 、not in

LIMIT用来限定查询结果的起始行，以及总行数。

案例：

11.分页查询

         语法：

select 查询列表

from 表

【where 条件】

limit 【起始条目索引,】查询的条目数;

         案例：

l   

12.SQL的执行顺序

手写的顺序：

真正执行的顺序：

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：

4.4 DDL

1.基本操作

l  查看所有数据库名称：SHOW DATABASES；　

l  切换数据库：USE mydb1，切换到mydb1数据库；

2.操作数据库

l  创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1；

创建数据库，例如：CREATE DATABASE mydb1，创建一个名为mydb1的数据库。如果这个数据已经存在，那么会报错。例如CREATE DATABASE IF NOT EXISTS mydb1，在名为mydb1的数据库不存在时创建该库，这样可以避免报错。

l  删除数据库：DROP DATABASE [IF EXISTS] mydb1；

删除数据库，例如：DROP DATABASE mydb1，删除名为mydb1的数据库。如果这个数据库不存在，那么会报错。DROP DATABASE IF EXISTS mydb1，就算mydb1不存在，也不会的报错。

3.数据类型

MySQL与Java一样，也有数据类型。MySQL中数据类型主要应用在列上。

常用类型：

l  int：整型

l  double/float：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；

l  decimal：浮点型，在表示钱方面使用该类型，因为不会出现精度缺失问题；

l  char：固定长度字符串类型；char(4) 范围是0-255

l  varchar：可变长度字符串类型；

l  text：字符串类型；表示存储较长文本

l  blob：字节类型；//jpg mp3 avi

l  date：日期类型，格式为：yyyy-MM-dd；

l  time：时间类型，格式为：hh:mm:ss

l  timestamp/datetime：时间戳类型；日期+时间 yyyyMMdd hhmmss

 详见 《MySQL之数据类型.doc》

4.操作表

l  创建表：

CREATE TABLE 表名(

  字段名 字段类型 【字段约束】,

  字段名 字段类型 【字段约束】,

  ......

);

例如：

再例如：

l  查看当前数据库中所有表名称：SHOW TABLES；　

l  查看指定表的创建语句：SHOW CREATE TABLE  emps;

l  查看表结构：DESC emps;

l  删除表：DROP TABLE emps

l  修改表结构:

a)       修改之添加列：给stus表添加classname列：

ALTER TABLE stu ADD (classname varchar(100));

b)       修改之修改列类型：修改stu表的gender列类型为CHAR(2)：

ALTER TABLE stus MODIFY gender CHAR(2);

c)        修改之修改列名：修改stu表的gender列名为sex：

ALTER TABLE stus change gender sex CHAR(2);

d)       修改之删除列：删除stsu表的classname列：

ALTER TABLE stus DROP classname;

e)       修改之修改表名称：修改stu表名称为student：

ALTER TABLE stus RENAME TO student;

4.5 DML

1.插入数据

语法：

INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

语法：

INSERT INTO 表名 VALUES(值1,值2,…)

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：

　　注意：所有字符串数据必须使用单引号

2.修改数据

单表修改语法：

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

    多表修改语法：

         UPDATE 表1 【inner】 john 表2 on 表 SET 列名1= 新值1,列名2 =新值2

         【where 筛选条件】

3.删除数据

单表删除语法：

①DELETE FROM 表名 [WHERE 条件]

②语法：

TRUNCATE TABLE 表名

l  虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！

l  TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的.

【面试题】二者的区别：

多表删除语法：

DELETE FROM 表1 别名1 INNER JOIN 表2 别名2 on 连接条件 【AND 筛选条件】

第5章完整性约束

　　完整性约束是为了表的数据的正确性！如果数据不正确，那么一开始就不能添加到表中。

5.1主键

当某一列添加了主键约束后，那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。例如学生的学号可以用来做唯一标识，而学生的姓名是不能做唯一标识的，因为学习有可能同名。

主键列的值不能为NULL，也不能重复！

　　指定主键约束使用PRIMARY KEY关键字

l  创建表：定义列时指定主键：

CREATE TABLE stu(

                  sid        CHAR(6) PRIMARY KEY,

                  sname     VARCHAR(20),

                  age           INT,

                  gender    VARCHAR(10)

);

l  创建表：定义列之后独立指定主键：

CREATE TABLE stu(

                  sid        CHAR(6),

                  sname     VARCHAR(20),

                  age           INT,

                  gender    VARCHAR(10),

                  PRIMARY KEY(sid)

);

l  修改表时指定主键：

ALTER TABLE stu ADD PRIMARY KEY(sid);

l  删除主键（只是删除主键约束，而不会删除主键列）：

ALTER TABLE stu DROP PRIMARY KEY;

5.2主键自增长

MySQL提供了主键自动增长的功能！这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键值+1，也就不会出现重复主键的可能了。

l  创建表时设置主键自增长（主键必须是整型才可以自增长）：

CREATE TABLE stu(

                  sid INT PRIMARY KEY AUTO_INCREMENT,

                  sname     VARCHAR(20),

                  age           INT,

                  gender    VARCHAR(10)

);

l  修改表时设置主键自增长：

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

l  修改表时删除主键自增长：

ALTER TABLE stu CHANGE sid sid INT;

5.3非空

指定非空约束的列不能没有值，也就是说在插入记录时，对添加了非空约束的列一定要给值；在修改记录时，不能把非空列的值设置为NULL。

l  指定非空约束：

CREATE TABLE stu(

                  sid INT PRIMARY KEY AUTO_INCREMENT,

                  sname VARCHAR(10) NOT NULL,

                  age           INT,

                  gender    VARCHAR(10)

);

当为sname字段指定为非空后，在向stu表中插入记录时，必须给sname字段指定值，否则会报错：

INSERT INTO stu(sid) VALUES(1);

　　插入的记录中sname没有指定值，所以会报错！

5.4唯一

还可以为字段指定唯一约束！当为字段指定唯一约束后，那么字段的值必须是唯一的。这一点与主键相似！例如给stu表的sname字段指定唯一约束：

CREATE TABLE tab_ab(

         sid INT PRIMARY KEY AUTO_INCREMENT,

         sname VARCHAR(10) UNIQUE

);

　　INSERT INTO sname(sid, sname) VALUES(1001, ‘zs‘);

INSERT INTO sname(sid, sname) VALUES(1002, ‘zs‘);

　　当两次插入相同的名字时，MySQL会报错！

5.5外键

主外键是构成表与表关联的唯一途径！

外键是另一张表的主键！例如员工表与部门表之间就存在关联关系，其中员工表中的部门编号字段就是外键，是相对部门表的外键。

l  创建dept表，指定deptno为主键列：

CREATE TABLE dept(

         deptno             INT PRIMARY KEY,

         dname             varchar(14),

         loc            varchar(13)

);

l  创建t_section表，指定sid为主键列，u_id为相对t_user表的uid列的外键：

CREATE TABLE emp(

     empno             INT,

     ename             VARCHAR(50),

     job           VARCHAR(50),

     mgr          INT,

     hiredate  DATE,

     sal             DECIMAL(7,2),

     comm              decimal(7,2),

     deptno             INT,

     CONSTRAINT fk_e_d FOREIGN KEY(deptno) REFERENCES dept(deptno)

)

5.6表与表之间的关系

l  一对一：例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：

• 在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
• 给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

l  一对多（多对一）：最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！

l  多对多：例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

第6章 事务

         事务属于TCL控制语言（Transaction Control Language ）。

6.1事务概述

什么是事务？为什么要用事务？

         一个事务是由一条或者多条sql语句构成，这一条或者多条sql语句要么全部执行成功，要么全部执行失败！

         默认情况下，每条单独的sql语句就是一个单独的事务！

举例：

银行转账！张三转10000块到李四的账户，这其实需要两条SQL语句：

l  给张三的账户减去10000元；

l  给李四的账户加上10000元。

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么李四的账户没有加上10000元，而张三却减去了10000元。这肯定是不行的！

你现在可能已经知道什么是事务了吧！事务中的多个操作，要么完全成功，要么完全失败！不可能存在成功一半的情况！也就是说给张三的账户减去10000元如果成功了，那么给李四的账户加上10000元的操作也必须是成功的；否则给张三减去10000元，以及给李四加上10000元都是失败的！

6.2事务的四大特性（ACID）

l  原子性（Atomicity）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

l  一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。

l  隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

l  持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

6.3 MySql中的事务

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。

l  开启事务：start transaction；

l  结束事务：commit或rollback。

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

演示事务

创建事务要用到的表

CREATE TABLE account(

         id INT PRIMARY KEY AUTO_INCREMENT,

         NAME VARCHAR(30),

         balance NUMERIC(10.2)

);

INSERT INTO account(NAME,balance) VALUES(‘fanbingbing‘, 100000);

INSERT INTO account(NAME,balance) VALUES(‘lichen‘, 100000);

SELECT * FROM account;

• 演示事务执行失败

案例一：回滚 事务执行失败！

START TRANSACTION;

UPDATE account SET balance=balance-10000 WHERE id=1;

UPDATE account SET balance=balance+10000 WHERE id=2;

ROLLBACK;

案例二：退出mysql客户端 事务执行失败！

START TRANSACTION;

UPDATE account SET balance=balance-10000 WHERE id=1;

UPDATE account SET balance=balance+10000 WHERE id=2;

• 演示事务执行成功

START TRANSACTION;

UPDATE account SET balance=balance-10000 WHERE id=1;

UPDATE account SET balance=balance+10000 WHERE id=2;

COMMIT;

mysql基础部分-史上最全