MySQL 笔记

MySQL 简介

MySQL 的介绍

---

数据可的基本概念

什么是SQL？

• SQL 是 Structured Query Language （结构化查询语言）缩写
• SQL 是转为数据库而建立的操作命令集，是一种功能器权的数据语言。
• 在使用 SQL 时，只需要发出“做什么”的命令，“怎么做“是不用使用者考虑的。

数据（Data）的定义：

• 对客观事物的符号表示，如图形符号、数字、字幕等，数据是数据库中存储的基本对象。
• 在常生活中，人们直接用语言来描述事物；
• 在计算机中，为了存储和处理这些事物，就要将事物的特种抽象出来组成一个记录来描述。

数据库（DB）定义:

• 数据库（database,， 简称DB）：是按照数据结构来组织存储和管理数据的仓库
• 长期存储在计算机内的、有组织的、可共享的数据集合。
• 数据库中的数据按一定数据模型组织、描述和存储。
• 具有较小的冗余度、较高的数据独立性和一扩展性、并为各个用户共享。

什么是DBMS ？

• 数据管理系统（Database Management System，简称 DBMS）：是指数据中对输出管理的软件系统
• 位于应用程序与存储数据的之间的一层数据管理软件。
• 是基础软件，是一个大型复杂的软件系统。
  DBMS 的用途
• 科学组织和存数数据、高效地获取和维护数据。‘

数据库系统DBS

• 一般有数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员和用户构成。
• 数据库系统的发展经历了3个阶段：
  网状数据库、层次数据库和关系数据库。他们分别采用网状模型、层次模型和关闭模型作为数的组织方式。
  • 网状模型（图形结果）
  • 层次模型（属性结构）
  • 关系模型（简单二位表结构）

信息和数据：

• 回想什么时数据？
• 信息：是指数据经过加工处理后获取的有用的知识。信息是以某种数据形式表
• 信息的3中数据
  • （1）现实世界
    • 显示世界就是存在于人脑之外的客观世界。
  • （2）信息世界
    • 信息世界就是显示世界在人们脑中的反应，又称观念世界。
  • （3）数据世界
    • 数据世界是信息世界中的信息数据化后对应的产物，就是信息世界中的信息经过抽象和组织，按照特定的数据结构，将数据存储在计算机中。

---

数据库的主要功能

数据库的定义功能：

• 它把用 DDL（数据库定义语言） 编写的各级元模式编译成各级目标模式，这些目标模式是对数据库结果信息的描述，而不是数据本身，它们被保护在数据库字典中，供以后数据操纵或数据控制时使用。

数据库的操作功能：

• 单独数据操作语言 DML 及其编译程序，实现对数据库的基本操作。基本操作有两类检索和更新。

数据库的保护功能：

• 数据可的数据恢复、数据库的并发控制、数据库的完整性控制和数据安全性控制。

数据库的维护功能：

• 数据的数据导入、转换、存储、数据库性能监控等。

数据库管理系统的优点：

• 相互关联的数据的集合
• 较少的数据冗余
• 程序与数据相互独立
• 保证数据安全、可靠、正确
• 数据可以并发使用并能同时保证一致性

---

数据库术语

数据库

数据库（database） 保存有组织的数据的容器（通常是一个文件或一组文件）

表

表（table） 某种特定类型数据的结构化清单。
存储在表中的数据是一种类型的数据或一个清单。绝不应该将顾客的清单与订单存储在同一个数据库中。这样做将使以后的检索和访问很困难。应该创建两个表，每个清单一个表。
数据库中的每个表都有一个名字，用来标识自己。此名字是唯一的，这表示数据库中没有其他表具有相同的名字。
表名 表名的唯一性取决于多个因素，如数据库名和表明等的结合。这表示，虽然在相同数据库中不能两次使用相同的表名，但在不同的数据库中却可以使用相同的表明。
模式（schema） 关于数据库和表的布局及特性的信息。

列和数据类型

表由列组成。列中存储着表中某部分的信息。
列（column） 表中的一个字段。所有表都是有一个或多个列组成的。
数据库中每个列都有相应的数据类型。数据类型定义列可以存储的数据种类。例如，如果列中存储的为数字（或许是订单中的物品数），则相应的数据类型应该为数值类型。如果列中存储的是日期、文本、注释、金额等。则应该用恰当的数据类型规定出来。

数据类型（datatype） 所容许的数据的类型。每个列表都有相应的数据类型，它限制（或容许）该类中存储的数据。
数据类型限制可存储在类中的数据种类（例如，放置在数值字段中录入字符值）。数据类型还帮助正确地排序数据，并在优化磁盘使用方面起重要的作用。因此，子啊创建表时必须对数据类型给予特别的关注。

行

表中的数据是按行存储的，所保存的每个记录存储在自己的行内。如果将表想象为网格，网格中垂直的列为表列，水平的行为表行。
行row 表中的一个记录

主键

表中每一行都应该可以唯一标识自己的一列（或一组列）。一个顾客表可以使用一个顾客编号列，而订单可以使用订单ID。
主键（primary key） 一列（或一组列），其值能够唯一区分表中的每个行。
唯一标识表中每行的这个列（或这组列）成为主键。主键用来表示一个特定的行。没有主键，更新或删除表中特定行很苦难，因为没有安全的方法保证只涉及相关的行。
表中的任何列都可以作为主键，只要它满足以下条件：

1. 任意两行都不具相同的主键值；
2. 每个行都必须具有一个主键（主键列不允许NULL值）。
   主键值规则：这里列出的规则是MySQL本身强制实施的。

主键的最好习惯： 除MySQL强制实施的规则外，应该坚持的几个普遍认可的最好习惯为：

1. 不更新主键列中的值；
2. 不重用主键列的值；
3. 不在主键列中使用可能会更改的值。

主键约束

数据完整性

• 数据的完整性指存储在数据库中的数据应该保持准确性和可靠性。如出现学号相同的数据就是不准确的。
• 实体完整性是对关系中的唯一性。
• 定义表中所有行能唯一的标识
• 表中主属性（字段）不能为 null 且不能有相同值
• 一般用主键、唯一索引、unique 关键字来实现

实体完整性 —— 主键约束

• 主关键字（primary key）是表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录
• 创建表时指定主键

实体-联系-模型

实体： 客观事物在信息世界中成为实体（Entity），它是现实世界中任何可区分、识别的事物。

属性： 描述实体或者联系的性值或特征的数据项。

联系： 反映事物内部或事物之间的关系集合； 常见的实体联系有 3 种：一对一联系、一对多联系和多对多联系。

关系的基本概念

• 关系：一个关系就是一张二位表，通常将一个没有重复行、重复列的二维表看成一个关系，每个关系都有一个关系名。
• 元组：二维表的每一行在关系中成为元组。在 MySQL 中，一个元组对应表中一个记录。
• 属性：二维表的每一列子啊关系中成为属性，每个属性都有一个属性名，属性值则是各个元组属性的取值。

关系模型（Relational Model）

• 域：属性的取值范围成为域。域作为属性值的集合，其类型域范围具体由属性的性值及其所表示的意义去顶。同一属性只能在相同域中取值。
• 关键字：关系中能唯一区分、去顶不同元组的属性或属性组合，成为该关系的一个关键字。
• 关系模式
  • 对关系的描述成为关系模式、其格式为;
    • 关系名（属性1, 属性2, …, 属性n
• 关系的基本特点
  • 在关系模型中，关系具有一下基本特点：
    ① 关系必须规范化，属性不可再分割。
    ② 在同一关系中不允许出现相同的属性名。
    ③ 在同一关系中元组的顺序可以任意。
    ④ 在同一关系中属性的顺序可以任意。

关系运算

• 关系运算主要由选择、投影和连接 3 种。
• 选择：从关系模型中找出满足给定条件的元组成新的关系。
  -(从行的角度进行运算）
• 投影：从关系模型中指定若干属性组成新的关系
  -（从列的角度进行运算）
• 连接：从两个关系的笛卡尔积中选取属性间满足一定条件元组，组成新的关系
  假设集合 A={a, b}， B={0, 1, 2}，则两个集合的笛卡尔积温 {(a,0), {a, 1}, (a, 2), (b, 0), {b, 1}, (b, 2)}。
  类似的例子有，如果 A 表示某学校学生的集合，B 表示该学校所有课程的集合，则 A 与 B的笛卡尔积表示所有可能的选课情况。

关系的完整的约束

• 实体完整性
  • 主键值（住关系键值的值）不能取空值。
    如：学生信息表（学号，姓名，性别，年龄，地址）中学号不能为空。
• 参照完整性（引用完整性）
  • 简单说就是主角按域外键关系
    如学生表域选课表之间用学号建立关系。学生表是主键，选课表是从表。若向从表中输入一条记录，系统会检查 记录的学生序号是否在主键中存在。若存在，则运行此操作，否则将拒绝。
• 域完整性（用户定义完整性）
  限制某县属性中出现的值，把属性限制在一个有限的集合中。
  如若属性类型为整数，那么输入值则不能为小数等其它任何非整数。

关系模型的规范化

• 关系模式要满足的条件成为规范化形式，简称范式。
• 关系模型规范化的目的是为了消除存储异常，减少数据冗余，保证数据的完整性和存储效率，一般规范为 3NF 即可。
  • 第一范式（1NF）
    如果关系 R 的所有属性均为简单属性，即每个属性都是不可再分的，则成为 R 满足第一范式。
    简单来说：第一范式就是无重复的列。
  • 第二范式（2NF）
    如果关系 R 满足第一范式，且每一个非主键字段完全依赖于主键，则称为 R 满足第二范式。
  • 第三范式（3NF）
    如果关系 R 满足第二范式，且非主键字段之间不存在依赖关系，则称 R满足第三范式。

数据库 E-R 图
E-R 图也称实体-联系图（Entity Relationship Diagram），提供了表示实体类型、属性和联系的方法，用来描述显示世界的概念模型。

• 通常矩形表示实体性，矩形框内写明实体名
• 用椭圆表示实体属性，椭圆内写名实体属性
• 用菱形表示实体型间的关系，在菱形内写名联系名
• 用先端连起来

---

---

当今主流的数据库：

• Oracle
• MySQL
• Microsoft SQL Management
• PostgreSQL
• MongoDB
• DS2
• Microsoft Access
• Redid
• SQLite

什么是MySQL?

• MySQL是一个开源的关系型数据库管理系统

MySQL简介

• MySQL由瑞典MySQL AB公司开发，目前属于Oracle旗下产品，SQL语言属于访问数据库的最常用的标准化语言，MySQL分为社区版和商业版.
  MySQL数据库是一个 C/S(client/server) 架构的互联网软件，专门用来存储和管理数据的。

操作数据库

MySQL 的安装

MySQL官网：https://www.mysql.com
MySQL目录结果：

• bin：用于存储一些可执行文件，如mysql.ext
• include：用于存储包含一些头文件，如mysql.h
• lib：用于存储一些库文件
• share：用于存储错误信息，字符集文件
• data：用于存储一些日志文件以及数据库
• my.ini：数据库配置文件

---

---

MySQL 登录和退出命令

命令	说明	示例
net start mysql	启动数据库	net start mysql80
net stop mysql	关闭数据库	net stop mysql80
mysql -h	连接数据库的地址，本地连接可忽略	mysql -h127.0.0.1 -uroot -ppassword
mysql -u	后面跟数据用户名，比如uroot	mysql -uroot -ppassword
mysql -p	后面跟数据用密码，也可以忽略回车后输入	mysql -uroot -p mysql -uroot -ppasword
mysql -V	查询本地mysql版本号	mysql -V
Exit	退出数据库（前提是已经登录mysql数据）	exit
Quit	退出数据库（前提是已经登录mysql数据）	quit
\q	退出数据库（前提是已经登录mysql数据）	\q

---

MySQL 数据类型分类

MySQL 支持多种数据类型，大致分为三类：

• 数值
• 日期/时间
• 字符串（字符）类型

数值类型

类型	大小	范围(有符号)	范围(无符号)	用途
TINYINT	1 字节	-128 ~ 127	0 ~ 255	小整数
SMALLINT	2 字节	-32768 ~ 32767	0 ~ 65535	大整数
MEDIUMINT	3 字节	-212 ~ 212-1	0 ~ 218	大整数
INT / INTEGER	4 字节	-216 ~ 216-1	0 ~ 232	大整数
BIGINT	8 字节	-216 ~ 216-1	0 ~ 264	极大整数
FLOAT	4 字节	-3.4E+38~3.4E+38		单精度浮点数
DOUBLE	8 字节	2.3E-308 ~ 1.7E+308		双精度浮点数
DECIMAL[(M,D)]	P：数字精度 D：小数精度			小数值

日期和时间类型

类型	大小	范围	格式	用途
YEAR	1 字节	1901 / 2155	YYYY	年份值
TIME	3 字节	-838：59：59 ~ 838：59：59	HH:MM:SS	时间值
DATE	3 字节	1000-01-01 / 9999-12-31	YYYY-MM-DD	日期值
DATETIME	8 字节	1000-01-01 00:00:00 / 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAM	4 字节	1970-01-01 00:00:00 / 2037 年某时 19700101080001~20380119111470	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

字符串

类型	存储需求	范围(有符号)
CHAR(M)	M个字节，0<=M<=255	定长字符串
VARCHAR(M)	L+1个字节，其中L<=M且0<=M<=65535	可边长字符串
TINYTEXT	0 ~ 255(28-1)	不超过 255 个字符串
TINYBLOB	0 ~ 255(28-1)	不超过 255 个字符串的二进制字符串
BLOB	0 ~ 65535(216-1)	二进制形式的长文本数据
TEXT	0 ~ 65535(216-1)	长文本数据
MEDIUBLOB	224-1	二进制形式的中等长度文本数据
MEDIUTEXT	224-1	中等长度文本数据
LONGBLOB	232-1	二进制形式的极大文本数据
LONGTEXT	232-1	极大文本数据
ENUM(‘value1’, ‘value2’, …)	取决于枚举的个数（最多65535个值）例：性别 enum(‘男’， ‘女’)
SET(‘value1’, ‘value2’, …)	取决于set成员的数目（最多64个成员）

---

MySQL 数据库操作命令

命令	说明	示例
show databases;	返回可用数据库的一个列表，包含在这个列表中的可能是MySQL内部使用的数据库	show databases;
show variables like ‘%datadir%’;	查询数据存放地址
create database 库名;	新建数据库	create database gong;
drop database 库名;	删除数据库	drap database gong;
use database 库名;	进入/使用数据库	use database gong;
alter database 库名 character set 字符集 collate 比较方式	修改数据库字符集比较方式

---

MySQL 表操作命令

操作表的前置条件是在使用数据库的情况下

命令	说明	示例
show tables;	查询当前数据库下的所有表名	show tables;
show columns from <表名>	获取表头信息	show columns table stu;
show create table 表名;	查询表创建时的定义语句	show create table stu;
rename table <旧表名> to <新表名>;	修改表名	rename table stu to student;
desc <表名>;	查询表中的列名（字段）	desc stu;
create table 表名(字段 数据类型 [约束条件], …);	创建表	create table stu(name varchar(50), age(int));
drop table <表名1>,<表名2>;	删除数据库中的表	drop table stu;
alter table <表名> add 字段名 数据类型;	向表中添加列（字段）	alter table stu add phone varchar(11);
alter table <表名> drop 字段名;	删除表中的列（字段）	alter table stu drop phone;
alter table <表名> modify 字段名 新的数据类型;	修改表中的列（字段）	alter table stu modify phone char(11);
alter table <表名> change 旧字段名 新字段名 数据类型;	修改表中的列名（字段名） 改字段名的同时也可以改字段数据类型（数据类型不可忽略）	alter table stu change phone my_phone varchar(11);
字段名 字段类型 [primary key]	创建表时为表的字段添加字段	id int primary key (主键)
[auto_increment]	限制主键自动增长	id int primary key auto_increamen
[constraint uni_idnumber unique(i字段名)]	给表中的字段添加唯一约束	constraint uni_idnumber unique(phone)(唯一约束)
create table 表名(字段名 字段属性 defable 默认值);	创建表时给字段添加默认值	create table stu(age int defable 18);
alter table 表名 modify 字段名 原字段类型 not null	给已有表的字段添加非空约束	alter table stu modify name varchar(50) not null;非空约束
constraint fk_name foreign key(要关联字段) references 关联的外键表名(关联的外键字段)	设置表中段字段与外部表字段关联	constraint fk_id foreign key(id) references stu(id);外键约束
inster into <表名> values(<字段1>,<字段2>…);	向表中插入数据	insert into stu values(“0001”, “xiaoming”, 18, “X”)
inster into <表名>(字段1,字段2, …) values(<字段1>,<字段2>…);	向表中插入数据	insert into stu(id,name) values(“0001”, “xiaoming”)
select * from <表名>;	查询表下的所有记录	select * from stu;
delete from <表名> where 过滤条件	删除表中的记录	delete from stu where name=‘xiaoming0001’
delete from <表名>	清空表中的所有记录	delete from stu
update <表名> set 字段=值;		update
update <表名> set 字段=值; where 条件;
truncate <表名>	清空表中的所有记录	truncate stu

---

查询数据库

查询命令

命令	说明	示例
一下关键字说明 stu: 表名 age: 列名 name：列名
select * from <表名>	查询当前数据库下的所有表名	查询数据;
select <列名> from <表名>	查询指定表中指定列中的所有数据	查询数据;
select <列名1><列名2> from <表名>	检索多列
select distinct <列名> from <表名>	查询指定列中的数据并去除掉重复值
select * from <表名> limit <数值n>	限制结果，列出前 n 条数据	select * from stu limit 5; select * from stu 5, 2;
select <列名> from <表名> order by <列名>	将查询结果按顺序输出
select <列名> from <表名> order by <列名> desc	将查询结果按降序输出	select name from std order by name desc;
select * from <表名> where <列名>=<值>	列出指定列中与特定值相等的记录
select * from <表名> where <列名> is null;	查询指定列中值为 null 的记录	select * from stu where name is null;
select * from <表名> where <列名> between <值1> and <值2>;	查询指定列中值符合值1和值2的所有记录	select * from stu where age between 18 and 25;
select * from <表名> where <列名1>=<值1> and <列名2>=<值2>;	查询符合逻辑1且符合逻辑2的所有记录	select * from stu where age<18 and age>25;
select * from <表名> where 字段名 in (元素1, 元素2, …);	选择查询	查询数据
select * from <表名> where 字段名 not in (元素1, 元素2, …);
select * from <表名> where 字段名 like 字符串（包含通配符）	模糊匹配 如果匹配的字符串不加通配符就是精准匹配
（通配符）
%	可以匹配任意0个、单个或多个字符 ;
_	可以匹配任意的单个字符;
select * from 表名 while 字段名 is null	判空查询
select * from 表名 while 字段名 not is null	判断非空
select * from as <alias> while <alias.key>	使用表别名

---

比较运算符

运算符	含义
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
<>	不等于
!=	不等于
!>	不大于
!<	不小于
between	在指定的两个值之间

逻辑运算符

运算符	含义
and	与
or	或
not	非

聚合函数

命令	说明	示例
sum([distinct] <列明>)	计算列值总和	select sum(字段名) from 表名 where 条件;
avg([distinct] <列明>)	计算列值平均值
max([distinct] <列明>)	求列值最大值
min([distinct] <列明>)	求列值最小值
count( * )	统计表中元组个数
count( [distinct] <列明> )	统计本列列值个数
Concat()	拼接将值连接到一起构成单个值	select Concat('name: ', name, '; age: ', age) from from name;

子查询

示例：
select * from <table1> where <table1.key> IN (select <key> from <table> where <表达式>)；

在 WHERE 子句中使用子查询能够编写出功能很强并灵活的 SQL 语句。对于嵌套的子查询的数目并没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

注意：在 WHERE 子句使用子查询，应保证 SELECT 语句具有 WHERE 子句相同数目的列，通常，子查询将返回单个列并且单个列匹配，但是需要也可以使用多个列。

逐渐增加子查询来建立查询：用子查询测试和调试查询很有技巧性，特别是在这些语句的发杂性不断增加的情况下更是如此。用子查询建立（和测试）子查询最可靠的方法时逐渐进行，这与 MySQL 处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌套子查询。这时，再次测试它。对于重要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但是节省了（找出查询为什么不正常）的大量时间，并且极大提高了查询一开始就正常工作的可能想。

联结表

SQL 最强大的功能之一就是在数据检索查询的执行中联结（join）表。联结是利用 SQL 的 SELECT 能执行的最重要操作，很好的理解联结及其语法是学习 SQL 的一个极为重要的组成部分。

个人理解：连接表的含义就是将多个表的数据显示在同一个表中，通过一些关联性的字段（主键、外键、字段）使用不同的表达式或判断进行过滤数据的手段。

示例：
select <table1.key>, <table2.key>, ... from <table1>, <table2> where <table1.key2>=<table2.key1>

select <table1.key>, <table2.key>, ... from <table1> INNER JOIN <table2> ON <table1.key2>=<table2.key1>

MySQL 可以联结多个表，MySQL 在运行时关联指定的每个表可以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降的越厉害。

组合

需要组合查询的情况：

1. 在单个查询中从不同的表返回类似结构数据；
2. 对单个表执行多个查询，按单个查询返回数据。

组合查询和多个 where 条件多数情况下，组合相同表的两个查询完成的工作与具有多个 where 子句条件的单个查询完成的工作相同。换句话说，任何具有多个 whree 子句的 select 语句都可以作为一个组合查询给出，在一下段落中可以看到这一点。这两种技术在不同的查询中性能也不同，因此，应该试一下这两种技术，以确定特定的查询哪一种性能更好。

UNION 的使用很简单。所需要做的只是给出每条 select 语句，在各条语句之间放上 UNION。

UNION 关键字进行组合两个 select 语句的结果
select * from products where vend_id in (1001, 1002) union select * from products where prod_price<=5;

ALL 关键字可以取消过滤重复行
select * from products where vend_id in (1001, 1002) union all select * from products where prod_price<=5;

UNION 的规则
UNION 是非常容易使用的。但是在进行行并是有几条规则需要注意：

1. UNION 必须有两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分割；
2. UNION 中每个查询必须包含先沟通的列、表达式或聚合函数（不过各个列不需要以相同的次序列出）。
3. 列数据类型必须兼容：类型不必完全相同，必须是 DBMS 可以隐含转换的类型（例如，不同数值类型或不同日期类型）。

---

正则表达式

正则表达式特殊字符

符号	含义	说明
.	匹配任意个字符	select * from stu where name regexp ‘张.’
\|	或表达式	select * from stu where age regexp ‘18|15’; 匹配出年龄等于18或15的行
[]	匹配几个字符之一	select * from stu where name regexp '[abc]'; 匹配名称含有a、b‘、c的行
[-]	匹配范围	select * from stu where age regexp '[1-9]'; 匹配年龄在 11-19 的行
\\	匹配特殊字符 匹配转义字符	select * from stu where regexp ‘\\.’ 匹配名称中包含 . 的行
\\\	匹配 \
*	0个或多个匹配
+	1个或多个匹配（等于{1,}）
?	0个或1个匹配
{n}	指定数目的匹配
{n, }	不少于指定数目的撇配
{n, m}	匹配数目的范围（m不超过255）
^	文本开始
$	文本结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾
````

注意：
MySQL 中的正则表达式匹配不区分大小写。为区分大小写，可以使用 BINARY 关键字

正则表达式语法

命令	说明	示例
select * from where REGEXP '18'	检索 key 字段中 包含 文本 18 的所有行	select * from stu where age REGEXP ‘18’
select * from where REGEXP BINARY 'abc'	检索 key 字段中 包含 字符串 abc，区分大小写的所有行	select * from stu where age REGEXP BINARY ‘abc’

插入数据

插入完整行

insert into <tbale name> values(<value1>, <value2>, ...)
values 内部的参数需要将所有列的值填入，如果不想添加值使用 NULL 进行占位，此语法简单，但并不安全，应该尽量避免使用。上面的 SQL 语句高度依赖与表中列的定义次序。因此，在编写依赖于特定次序的 SQL 语句不是很安全。

可以使用更安全的方法如下：
insert into <table name>(<key1, key3, key2>) values(value1, value3, value2)

在表后面提供了列名， values 必须以其指定的列名填入列值。其优点是，即使表结构改变， 此 inserts 语句仍然能正常工作，不需要填入的值可以不填写 key。

插入多行数据

插入多行数据与单行数据类型，需要在每个记录后面添加新的 values 使用都好进行分割

insert into <table name>(<key1, key3, key2>) values(value1, value3, value2) , values(value1, value3, value2)

提高 INSERT 性能此技术可以提高数据库处理的能力，因为 MySQL 用单条 INSERT 语句处理多个插入比使用多个 INSERT 语句块。

插入检索出的数据

insert into table2(key1, key2, key3) select key1, key2, key3 from table2;
从 table2 中检索出来的数据插入 table1 中。

更新和删除数据

更新数据

为了更新（修改）表中数据，可以使用 UPDATE 语句。采用两种方法使用 UPDATE

• 更新表中特定行；更新表中所有行。

update <table> set <key1>=<value1>, <key2>=<value2> where <查询条件>;

删除数据

为了从一个表中删除(去掉）数据，使用 DELETE 语句。可以两种方法使用。

• 从表中删除特定行；
• 从表中删除所有行。

注意： 不要省略 WHERE 子句在使用 DELETE 时一定要细心。因为稍不注意，就会错误地删除表中所有行。

delete from <table> where <表达式>;

更快的删除 如果想从表中删除所有行，不要使用 DELETE。 可以使用 TRUNCATE TABLE 语句，它完成相同的工作，但速度更快（TRUNCATE 实际上时删除原来的表中并重新创建一个表，而不是逐行删除表中的数据）。

更新和删除的指导原则

上面使用了 UPDATE 和 DELETE 语句全都具有 WHERE 子句，这样做的理由很充分。如果省略掉 WHERE 子句，则 UPDATE 或 DELETE 将应用到表中的所有行。

以下是许多 SQL 程序员使用 UPDATE 和 DELETE 时所遵循的习惯：

• 除非确定更新和删除每一行，否则绝对不要使用带 WHERE 子句的 UPDATE 和 DELETE 语句；
• 保证每个表都有主键；
• 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前， 应该先用 SELECT 进行测试，保证它过滤的时正确的记录，以防编写 WHERE 子句不正确。
• 使用强制实施引用完整性的数据库，这样 MySQL 将不允许删除具有与其他表相关联的数据的行。

创建和操纵表

创建表

MySQL 不仅用于表数据的操纵，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。

一般有两种创建表的方法：

• 使用具有交互式创建和管理白哦的工具
• 表也可以直接用 MySQL 语句操操纵。
  为了创建表可以使用 SQL 的 CREATE TABLE 语句。
  有关表的操作语法请看上面的 MySQL 表操作命令:

处理现有的表 在创建表时，指定的表名必须不存在，否则将出错。如果要放置意外覆盖已有的表，SQL 要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。
如果想在一个表不存在创建它，应该在表名后给出 IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在的时创建它。

创建视图

为什么使用视图

• 重用 SQL 语句；
• 简化复杂的 SQL 操作，在编写后，可以方便的重用它而不必知道它的基本查询细节；
• 使用表的组成部分而不是整个表；
• 保护数据，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
• 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据。

个人理解什么是视图：视图就是给一个 select 语句起了一个别名，可以对这个 视图 再次进行 select 语句操作。

在视图创建之后，可以用与表基本相同的方式利用它们。可以对视图执行 select 操作，过滤和排序操作数据，将视图联结到其它视图或表，甚至能添加和更新数据（添加和更新数据存在某些限制）。

创建视图语法
create view as <view name> <select 查询语句>

查询视图语法
show tables status where conment='view';

查询视图的详细信息
show create view <view name>;

视图的规则和限制

• 与表名一样，视图名必须唯一命名（也不能与表名同名）；
• 对于可以创建的视图数目没有限制；
• 为了创建视图，必须有足够的访问权限。这些限制通常由数据库管理人员授予；
• 视图可以嵌套，即可以利用从其它视图中检索数据的查询来构造一个视图；
• ORDER BY 可以用在视图中，但如果从该视图检索数据 SELECT 中也含有 ORDER BY，那么该视图中的 ORDER BY 将被覆盖；
• 视图不能索引，也不能有关联的触发器或默认值；
• 视图可以和表一起使用。

性能问题：因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降的很厉害。因此，在部署大量视图的应用前，应该进行测试。

使用存储过程

---

实战

练习基本语句

# 创建数据库 school
create database school character set utf8 collate utf8_general_ci;

# 查看数据库
show databases;

# 使用数据库
use school;

# 显示数据库定义语句
show create database shool;

# 创建班级表
create table classes(c_id int, c_name varchar(100));

# 创建学生表
create table students(s_id int, name varchar(100), sex enum('男', '女'), age int, addr varchar(200), cid int);

# 向 students 表中插入数据
insert into students values(1, '张三', '男', 30, "未知1", 10001);
insert into students values(1, '李四', '您能', 39, "未知2", 10001);

# 创建科目表
#		字段： c_id（科目编号）, c_name（科目名称）
create table course(c_id int, c_name varchar(100));

# 创建分数表
#		字段： sid （学号）, cname（科目名）, score（分数）, time（第几期考试）
create table score(sid int, cname varchar(100), score int, time varchar(100));

# 修改表结构，添加一列出生日期 time
alter table students add birthday date;

# 修改分数表结构，删除一列 cname
alter table score drop cname;
desc score;

# 修改学生表名为stu
rename table students to stu;

# 删除所有表及数据库
drop table stu;
drop database school;

主键

注意：

1. 主键不能有重复值；
2. 不能有null；
3. 在一张表中有且只能有一个主键；
   

# 创建一张表
create table teacher(tno int primary key, name varchar(100), phone varchar(11));
# 创建表时添加主键
create table teacher1(tno int primary key, name varchar(100), id varchar(18), phone varchar(11));
# 创建表时添加 自动增长 主键
create table teacher2(tno int primary key auto_increment, name varchar(100), id varchar(18), phone varchar(11));
# 创建表时添加主键方式2
create table teacher3(tno int auto_increment, name varchar(100), id varchar(18), phone varchar(11), primary key(tno));
# 创建主键的时候可以给主键起一个名称
create table teacher4(tno int auto_increment, name varchar(100), id varchar(18), phone varchar(11), constraint PRI_INO primary key(tno));

desc teacher4;

insert into teacher values(1, "gavin", "12346578901");
select * from teacher;
insert into teacher values(2, "xiaoming", "11346578901");
insert into teacher values(null, "xiaoming", "11346578901");

insert into teacher2(name, phone) values('xiaoming', '12345678901');
insert into teacher2(name, phone) values('xiaoming2', '12345678901');
insert into teacher2(tno, name, phone) values(9, 'xiaoming3', '12345678901');
insert into teacher2(name, phone) values('xiaoming4', '12345678901');

create table user(id int, username varchar(50), pw varchar(50), primary key(id, username));

insert into user value(1, 'xiaoming0000', '1324651');
insert into user value(1, 'xiaoming0002', '1324652');
insert into user value(2, 'xiaoming0002', '1324652');

# 查询表的定义语句
show create table teacher4;

# 添加主键的三种方式
# 方式1 使用 modify 语句添加主键
create table teacher5(tno int, name varchar(100), phone varchar(11));
alter table teacher5 modify tno int primary key;
show create table teacher5;

create table teacher6(tno int, name varchar(100), phone varchar(11));
insert into teacher6 values(1, 'xiaoming', 123456790);
insert into teacher6 values(1, 'xiaoming', 123456791);
# 如果表中字段的记录有重复，那么就不能给该字段添加主键。
# alter table teacher6 modify tno int primary key;

# 方式2 使用 add 方式添加主键
create table teacher7(tno int, name varchar(100), phone varchar(11));
alter table teacher7 add primary key(tno);

# 方式3 使用 add 添加主键并给标题名
create table teacher8(tno int, name varchar(100), phone varchar(11));
alter table teacher8 add constraint pri_tno primary key(tno);
show create table teacher8;

# 删除主键
alter table teacher8 drop primary key;

---

唯一约束

• 唯一约束是指定 table 的列或组合不能重复，保证数据的唯一性；
• 唯一约束不允许出现重复的值，但是可以为多个null；
• 同一个表可以有多个唯一约束，多个列表组合的约束；
• 如果不给唯一约束名称，就默认和列名相同；
• MySQL 会给唯一约束的列默认创建一个唯一索引；
  

# 方式 1
# 创建 people 表时给字段 idnumber 添加唯一约束
create table people(
	id int primary key, 
	name varchar(20), 
	idnumber varchar(18), 
	constraint uni_idnumber unique(idnumber)
);
desc people;
show create table people;

insert into people values(1, 'xiaoming0001', '123456');
# 添加失败
# insert into people values(2, 'xiaoming0002', '123456');
# 给添加唯一约束的字段添加 null ， 添加成功
insert into people values(3, 'xiaoming0003', null);
insert into people values(4, 'xiaoming0004', null);

# 方式 2
# 添加唯一约束 
alter table people add constraint  uni_name unique(name);

# 删除唯一约束
alter table  people drop key uni_name;
desc people;

# 唯一约束可以作用到多个字段上面
create table user1(id int, username varchar(50), password varchar(50));
alter table user1 add constraint uni_key unique(username, password);
show create table user1;
insert into user1 values(1, 'xiaoming0001', '123456');
insert into user1 values(2, 'xiaoming0002', '123456');
insert into user1 values(3, 'xiaoming0002', '123456');	# 添加失败

---

域完整性

• 域完整性是对数据表中字段属性的约束；
• 它是由确定表结构是所定义的字段的属性决定的
• 限制数据类型，缺省值，规则，约束，是否可以为空；
• 域完整性可以确保不会输入无效的值。
  练习主键、唯一约束

# 练习 创建学生表
create table tempStudent(
	sno int primary key auto_increment,				# 设置为主键并添加自动增长
	sname varchar(50),
	sdept varchar(50) not null,						# 设置非空约束
	sage int not null default 18,					# 设置非空约束
	constraint uni_sname unique(sname)				# 设置唯一约束
);
	
show create table tempstudent;

# 测试默认值 sage 
insert into tempstudent(sno, sname, sdept) values(1, 'xiaoming0001', '蓝天幼儿园');
# 测试覆盖默认值 sage
insert into tempstudent values(2, 'xiaoming0002', '双语幼儿园', 19);

# 使用 alter 添加 默认约束
alter table tempstudent modify sdept varchar(50) default '幼儿园';
desc tempstudent;
insert into tempstudent(sno, sname) values(3, 'xiaoming0003');

# 删除缺省的默认值
alter table tempstudent modify sage int;

---

not null 非空约束

• 限制某个字段不能取空值
• 非约束可以有多个重复值
• 一张表中可以有多个非空约束

# 创建一个表，字段中包含非空约束
create table teacher9(
	id int primary key,
	name varchar(50),
	age int,
	salary decimal(8, 2),
	birthday date not null
);

# 测试给 birthday 字段添加 null ，结果创建按失败：Column 'birthday' cannot be null
insert into teacher9 values(1, 'xiaoming0001', 18, '302.89', null);
# 以字符串的形式插入 date 格式的日期
insert into teacher9 values(1, 'xiaoming0001', 18, '302.89', '1999-10-01');
# 以数值的形式插入 date 格式的日期
insert into teacher9 values(2, 'xiaoming0002', 18, '302.99', 19991001);


# 使用 alter 添加非空约束
alter table teacher9 modify name varchar(50) not null;
desc teacher9;

---

参照完整性约束

• 指表与表之间的数据参照引用
• 使用外键约束实现
  

create table class(
	c_no int primary key,
	c_name varchar(50),
	room varchar(50)
);

create table student(
	s_no int,
	s_name varchar(50),
	s_age int,
	c_no int,
	constraint fk_c_no_class foreign key(c_no) references class(c_no)
);

# 向表 class 中插入数据
insert into class values(1, "xiaoxin0001", '0001');
# 向表 student 中插入数据，将会插入失败，因为 c_no 外联的主键中没有该值
insert into student values(1, 'xiaoming0001', 18, 2);
# 向表 student 中插入数据，插入成功
insert into student values(1, 'xiaoming0001', 18, 1);

向表中插入数据

create table class(
	c_no int,
	c_name varchar(5),
	room varchar(5)
);

# 插入数据的 c_name 字段长度大于 5 ，插入失败
insert into class values(1, 'xiaoming0001', 'room1');
# 修改表 class 字段 c_name 数据类型为 varchar 长度为 50
alter table class modify c_name varchar(50);
# 插入成功
insert into class values(1, 'xiaoming0001', 'room1');

# 插入字段 c_no 为不同数据类型，插入失败 
insert into class values('12', 'xiaoming0001', 'room1');

# 插入数据值指定具体的字段
insert into class(c_no, c_name) values(3, 'xiaoming0003');

# 插入数据的修改指定字段的顺序
insert into class( c_name, c_no) values('xiaoming0004', 4);

---

删除数据

• 不能使用delete删除记录中的一个或多个字段
• delete 最少删除一条记录
  

create table class(
	c_no int,
	c_name varchar(50),
	room varchar(5)
);

# 插入数据的修改指定字段的顺序
insert into class( c_name, c_no) values('xiaoming0004', 4);

desc class;
select * from class;

# 删除 calss 表中字段 c_no 值为 4 的记录
delete from class where c_no=4;

# 清空表记录
truncate table class;

delete from 与 truncate table 的对比：

• delete from 在删除数据的时候可以加上过滤条件（where），truncate table 删除的是整张表的数据，不能加条件
• truncate table 删除数据的速度非常快，delete from 删除数据的速度比较慢
• truncate table 删除的表数据没法恢复，delete from 删除的数据还有办法恢复

---

修改数据

create table class(
	c_no int,
	c_name varchar(50),
	room varchar(5)
);

# 插入数据的修改指定字段的顺序
insert into class( c_name, c_no) values('xiaoming0001', 1);
insert into class( c_name, c_no) values('xiaoming0002', 2);
insert into class( c_name, c_no) values('xiaoming0003', 3);

# 修改所有字段为 c_no 的值
update class set c_no=2;
select * from class;

# 指该指定记录的字段值
update class set c_name='xiaoming0006', c_no=6 where c_name='xiaoming0001';

查询数据

select 语句的语法

select select_list from table_list
	[where search_conditions]
	[group by group_by_list]
	[having search_conditions]
	[order by order_list [ASC|DESC]]

实战

# 查询表中的所有数据
select * from class;

# 查询表 class 中列字段为 name 下的所有数据
select name from class;

# 查询表中的指定字段
select c_name from class;

# 查询 stu 表中 name 列中等于 xiaoming0000 的所有记录
select * from stu where name="xiaoming0000";

# 将查找结果中重复的部分去掉
select distinct c_no from class;

# 将查询结果的字段名取别名
select c_no as '编号', c_name as '姓名' from class;