文章目录
1.数据库的介绍
1.1什么是数据库？
-
什么是数据库？
存储数据的地方就是数据库，这个地方指的是内存或者磁盘。我们之前接触过一种数据库：文本数据库，将数据文件都放在一个文件夹中，这个文件夹就是文本数据库。这个数据库io阻塞，效率低，功能少等等。
假如现在没有现成数据库，我们开发的项目都需要与数据打交道，我们开发一个项目就需要写一套与增删改查的大组件（与数据打交道），在开发一个项目，还需要写一套与增删改查的大组件（与数据打交道），我们一直要重复造轮子，那么我们可不可以专门写一套软件，这套软件支持大部分编程语言，帮助我们管理维护数据库的数据的软件，我们要写这个软件需要满足什么条件？
- 减少io阻塞，提升效率，满足增删改查，备份的常用的功能。
- 这个管理数据库的软件一定要是C/S架构，开发两套程序。
- 这个软件由于连接我服务端的客户端数量不固定，我们要支持高并发，并且由于数据共享可能带来的数据不安全问题，我们要加锁保证数据安全。
- 这个软件要设置认证机制，连接我的客户端要进行登录认证。
1.2 什么是DBMS？
DataBase Management System 数据库管理系统（软件）,现在市面上存在很多优秀成熟这个软件，这样就避免我们直接用编程语言与数据库打交道，我们直接用编程语言与数据库管理系统这个软件打交道，所以我们要学习这个软件的使用方法以及功能，也就是我们之前见过的sql语句。
所以我们学好数据库并不是学数据库，而是学数据库管理软件的各种操作，方法，（例如MySQL的sql语句）。
工作中：有人称这个数据管理系统为数据库，有人称这个存放数据的机器叫做数据库，有人称里面的一个存放数据的库叫做数据库，（存放数据的库，其实就是将数据分开管理，例如：你有两个项目来使用这个数据管理系统，那么我们两个项目的数据肯定是不能掺和在一块的对不对，那么就需要分开管理，分开管理的是可以给每个项目单独创建一个库，每个库存放自己项目对应的数据，有人称这个库为数据库），但是不管怎么称呼，你结合他当时说话的场景，就能够理解他指的是什么了，反正大意也都差不多，这个知道就好了。那么我们来看看有哪些NB的数据库管理系统啊，看下节的分类（还是先安装一下mysql搞一下看看在看分类吧！直接看第三大节）。
2.数据库的分类
2.1 关系型数据库
2.1.1 什么是关系型数据库
表(文件)与表(文件)之间是有关系的，主要是利用关系进行增删改查的操作。
这个表看着好乱啊，但是你细看一下，这几个表之间建立了某种关系，共享着双方的数据。这就是关系。关系型数据库里面存数据的时候就类似这个样子的。有个大概了解了吗~~~
操作关系型数据库的命令，我们称之为SQL，看解释
结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
1986年10月，美国国家标准协会对SQL进行规范后，以此作为关系式数据库管理系统的标准语言（ANSI X3. 135-1986），1987年得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL不能完全相互通用
其中最后一句挺重要的：不同数据库系统之间的SQL不能完全相互通用
2.1.2 常见的关系型数据库
-
oracle数据库
Oracle前身叫SDL、由Larry Ellison和两个编程人员在1977创办，他们开发了主机的拳头产品，在市场上大量销售。Oracle公司是最早开发关系型数据库的厂商之一，其产品支持最广泛的操作系统平台。目前Oracle关系数据库产品的市场占有率数一数二 。
Oracle公司是目前全球最大的数据库软件公司，也是近年业务增长极为迅速的软件提供与服务商
主要应用范围：传统大企业、大公司、政府、金融、证券等。
版本升级：oracle8i，oracle9i，oracle10g，oracle11g，oracle12c -
MySQL数据库
MySQL被广泛的应用在Internet上的大中小型网站中。由于体积小、速度快、总体拥有成本低，开放源代码，有免费的社区版，社区版的功能也很强大，可以满足一般中小型企业的使用。
-
MariaDB数据库
MAriaDB数据库管理系统是MySQL数据库的一个分支，主要由开元社区维护，采用GPL授权许可。开发这个MariaDB的原因之一是：甲骨文公司收购了MySQL后，有MySQL闭源的潜在风险，因此MySQL开元社区采用分支的方式来避开这个风险。
MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM的存储引擎，它使用了Percona的XtraDB（InnoDB的变体）这个版本还包括了PrimeBase XT （PBXT）和Federated X存储引擎。
-
SQL Server数据库
Microsoft SQL Server是微软公司开发的大型关系数据库系统。SQL Server的功能比较全面，效率高，可以作为中型企业或单位的数据库平台。SQL Server可以与Winodws操作系统紧密集成，不论是应用程序开发速度还是系统事务处理运行速度，都得到较大的提升，对于在Windows平台上开发的各种企业级信息管理系统来说，不论是C/S（客户机/服务器）架构还是B/S（浏览器/服务器）架构。SQL Server都是一个很好的选择。SQL Server的缺点是只能在Windows系统下运行
-
Access数据库
Access是入门级小型桌面数据库，性能安全性都很一般，可供个人管理或小型企业只用
Access不是数据库语言，只是一个数据库程序，目前最新版本为Office 2007，其特点主要如下：
（1）完善地管理各种数据库对象，具有强大的数据组织，用户管理、安全检查等功能
（2）强大的数据处理功能，在一个工作组级别的网络环境中，使用Access开发的多用户管理系统具有传统的XSASE(DBASE、FoxBASE的统称)数据库系统所无法实现的客户服务器（Ckient/Server）结构和响应的数据库安全机制，Access具备了许多先进的大型数据管理管理系统所具备的特征。
（3）可以方便地生成各种数据对象，利用存储的数据建立窗体和报表
（4）作为Office套件的一部分，可以与Office集成，实现无缝连接
（5）能够利用Web检索和发布数据，实现与Internet的连接，Access主要适用于中小企业应用系统，或作为客户机/服务器系统中的客户端数据库。其他不常用关系型数据库
DB2，PostgreSQL，Informix，Sybase等。这些关系型数据库逐步的淡化了普通运维的实现，特别是互联网公司几乎见不到.
2.2 非关系型数据库
2.2.1 什么是非关系型数据库
非关系型数据库也被成为NoSQL数据库，NOSQL的本意是“Not Olnly SQL”
指的是非关系型数据库，而不是“No SQL”的意思，因此，NoSQL的产生并不是要彻底地否定关系型数据库，而是作为传统关系型数据库的一个有效补充。NOSQL数据库在特定的场景下可以发挥出难以想象的高效率和高性能。
随着互联网Web2.0网站的兴起，传统的关系型数据库在应付web2,0网站，特别是对于规模日益扩大的海量数据，超大规模和高并发的微博、微信、SNS类型的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。
例如：传统的关系型数据库IO瓶颈、性能瓶颈都难以有效突破，于是出现了大批针对特定场景，以高性能和使用便利为目的功能特异化的数据库产品。NOSQL（非关系型）类的数据就是在这样的情景下诞生并得到了非常迅速的发展
高性能、高并发、对数据一致性要求不高
开源的NoSQL体系，如Facebook的Cassandra，Apache的HBase，也得到了广泛认同，Redis，mongb也逐渐越来越受到各类大中小型公司的欢迎和追捧
NOSQL非关系型数据库小结：
1、NOSQL不是否定关系数据库，而是作为关系数据库的一个重要补充
2、NOSQL为了高性能、高并发而生，忽略影响高性能，高并发的功能
3、NOSQL典型产品memcached （纯内存），redis（持久化缓存），mongodb（文档的数据库）
非关系型数据库又分为以下4种：
（1）键值（Key-Value）存储数据库
键值数据库就类似传统语言中使用哈希表，可以通过key来添加、查询或删除数据，因为使用key主键访问，所以会获得很高的性能及扩展性
键值（Key-Value）数据库主要是使用一个哈希表，这个表中有一个特定的键和一个指针指向特定的数据。Key/value模型对于IT系统来说的优势在于简单、易部署、高并发
k1—>数据
k2—>数据
典型产品：Memcached、Redis、MemcacheDB、BerkeleyDB
（2）列存储（Column-oriedted）数据库 ======>了解即可，一般公司用不到
这部分数据库通常用来分布式存储的海量数据，键仍然存在，但是他们的特点是指向了多个列。
典型产品：Cassandra,HBase
（3）面向文档（Document-Oriented）数据库
面向文档数据库会将以文档的形式存储。每个文档都是自包含的数据单元，是一系列数据项的集合。每个数据项都有一个名称与对应的值，值既可以是简单的数据类型，如字符串、数字和日期等；也可以是复杂的类型，如有序列表和关系对象。数据存储的最小单位是文档，同一个表中存储的文档属性可以是不同的，数据可以使用XML、JSON或者JSONB等多种形式存储
典型产品：MorgoDB、CouchDB
（4）图形（Graph）数据库
2.2.2 常见的非关系型数据库
-
memcached（key-value）
Memcached是一个开源的、高性能的、具有分布式内存对象的缓存系统。通过它可以减轻数据库负载，加速动态的web应用，最初版本由LiveJoumal 的Brad Fitzpatrick在2003年开发完成。目前全球有非常多的用户都在使用它来架构主机的大负载网站或提升主机的高访问网站的响应速度。注意：Memcache 是这个项目的名称，而Memcached是服务端的主程序文件名。
缓存一般用来保存一些进程被存取的对象或数据，通过缓存来存取对象或数据要比在磁盘上存取块很多，前者是内存，后者是磁盘、Memcached是一种纯内存缓存系统，把经常存取的对象或数据缓存在memcached的内存中，这些被缓存的数据被程序通过API的方式被读取，memcached里面的数据就像一张巨大的hash表，数据以key-value对的方式存在。Memcached通过缓存经常被存取的对象或数据，从而减轻频繁读取数据库的压力，提高网站的响应速度，构建出快速更快的可扩展的Web应用。
官网：http://memcached.org/
由于memcached为纯内存缓存软件，一旦重启所有数据都会丢失，因此，新浪网基于Memcached开发了一个开源项目Memcachedb。通过为Memcached增加Berkeley DB的特久化存储机制和异步主复制机制，使Memcached具备了事务恢复能力、持久化数据能力和分布式复制能力，memcached非常适合需要超高性能读写速度、持久化保存的应用场景，但是最近几年逐渐被其他的持久化产品替代如RedisMemcached小结：
1、key-value行数据库
2、纯内存数据库
3、持久化memcachedb（sina） -
Redis（key-value）
和Memcached类似，redis也是一个key-value型存储系统。但redis支持的存储value类型相对更多，包括string（字符串）、list（链表）、set（集合）和zset（有序集合）等。这些数据类型都支持push/pop、add/remove及取交集、并集和差集及更丰富的操作，而且这些操作都是原子性的。为了保证效率，redis的数据都是缓存在内存中。区别是redis会周期性的把更新的数据写入磁盘或者把修改操作写入追加的记录文件，并且在基础上实现了master-slave（主从）同步。
redis是一个高性能的key-value数据库。redis的出现、很大程度补偿了memcached这类key/value存储的不足，在部分场合可以对关系数据库起到很好的补充作用。它提供了Python、Ruby、Erlang、PHP客户端，使用方便。
官方：http://www.redis.io/documentation
redis特点：
1）支持内存缓存，这个功能相当于memcached
2）支持持久化存储，这个功能相当于memcachedb，ttserver
3）数据库类型更丰富。比其他key-value库功能更强
4）支持主从集群、分布式
5）支持队列等特殊功能
应用：缓存从存取memcached更改存取redis -
MongoDB（Document-oriented）
MongoDB是一个介于关系型数据库和非关系型数据库之间的产品，是非关系型数据库当中功能最丰富，最像关系数据库的。他支持的数据库结构非常松散，类似json的bjson格式，因此可以存储比较复杂的数据类型。Mongodb最大的特点是他支持的查询语言非常强大，其语法有点类似于面向对象的查询语言，几乎可以实现类似关系数据库单表查询的绝大部分功能，而且还支持对数据建立索引。
特点：
高性能、易部署、易使用、存储数据非常方便
主要功能特性：1.面向集合存储，易存储对象类型的数据
2.“面向集合”（Collenction-Orented）意思是数据库被分组存储在数据集中，被称为一个集合（Collenction）每个 集合在数据库中都有一个唯一的标识名，并且可以包含无限数目的文档，集合的概念类似关系型数据库（RDBMS）里的表（table）不同的是它不需要定义任何模式（schema）
3.模式自由
模式自由（schema-free）意为着存储在mongodb数据库中的文件，我们不需要知道它的任何结构定义。
4.支持动态查询
5.支持完全索引，包含内部对象
6.支持查询
7.支持复制和故障恢复
8.使用高效的二进制数据存储，包括大型对象
9.自动处理碎片、以支持云计算层次的扩展性 -
Cassandra（Column-oriented）
Apache Cassndra是一套开源分布式Key-Value存储系统。它最初由Facebook开发，用于存储特别大的数据。Facebook目前在使用此系统。
主要特点：1.分布式
2.基于column的结构化
3.高伸展性
4.Cassandra的主要特点就是它不是一个数据库，而是由一堆数据库节点共同构成一个分布式网络服务，对Cassandra的一个写操作，会被复制到其他节点上去，对Cassandra的读操作。也会被路由到某个节点上面去读取。
Cassandir是一个混合型的非关系的数据库，类似于Google的BigTable。其主要功能比Dynomie（分布式的key-value存储系统）更丰富，Cassandra最初由Facebook开发，后转变成了开源项目。其他不常用非关系型数据库
HBase、MemcacheDB、BerkeleyDB、Tokyo Cabinet\Tokyo Tyrant（ttserver）
ttserver 持久化输出，缺点存储2千万条 性能下降（由日本人发明）
3.MySQL介绍
3.1 MySQL介绍
MySQL版本
双授权版本：社区版（完全免费，功能也够nb了）和商业版（更好，功能更多更强大一些，但是收费，VIP，有售后服务，也会参考和吸收社区版的一些nb的功能，安全性和稳定性都是最好的，大几十万），一般NB的开源软件都是双授权的
每个版本又分四个版本依次进行发布：
Alpha版：一般只在开发公司内部使用，不对外公开，测试、自我检查的版本
Beta版：一般是开发完也测试完的版本，一般不会出现比较大的性能bug（一般我们不用，阿里、去哪儿会使用这个版本，有些新功能，内部有高手能调，也能评估新功能的性能）
RC版：根据Beta版测试之后收集到一些bug等在进行完善的一个版本
GA版：软件正式发布的版本，没有特别的需求一定要使用GA版，有些公司追求新功能会使用Beta版，这是个例。
MySQL的产品线：（mysql是C++写的，oracle 9i版本之前是C语言写的，之后主要是java）
最早期，mysql是按照3.x–4.x–5.x等来开发的，但是为了提高MySQL的竞争优势，以及提高性能、降低开发维护成本等原因，同时，更方便企业用户更精准的选择合适的版本产品用于自己的企业生产环境中，MySQL在发展到5.1系列版本之后，重新规划为三条产品线。
第一条：5.0.xx 到 5.1.xx产品线系列介绍
第二条：5.4.xx 到 5.7.xx产品线系列介绍(主流：5.5和5.6)
第三条：6.0.xx 到 7.1.xx产品线系列介绍
MySQL数据库软件命名介绍
以mysql-5.6.42.tar.gz的版本号来解释：
1.第一个数字5是主版本号，描述了文件格式。所有版本5发行都有相同的文件格式。
2.第二个数字6是发行级别。主版本号和发行级别组合到一起便构成了发行序列号。
3.第三个数据42是在此发行系列的版本号，随每个新发布版递增。通常你需要已经选择发行的最新版本，每次更新后，版本字符串的最后一个数字会递增。如果增加了一些新功能或者微小的不兼容性，版本字符串的第二个数字会递增。如果文件格式改变，第一个数字会递增。
一般有的版本也会加上上面我们说的4个版本的后缀，beta、alpha、rc版、ga版等等，我们举得这个例子是不带后缀的，就相当于GA版
MySQL版本选择建议
1.稳定版：选择开源的社区版的稳定版GA版本
2.产品线：可以选择5.1、5.5、5.6，互联网公司主流5.5和5.6，其次是5.1.
3.选择MySQL数据库GA版发布后6个月以上的GA版本。
4.要选择前后几个月没有大的BUG修复的版本，而不是大量修复BUG的集中版本
5.最好向后较长时间没有更新发布的版本。
6.开发、测试、运维、DBA进行自己本地测试的时候，最好要和线上的版本一致，最差也要兼容，所以作为开发，你要清楚公司用的哪个版本的数据库
7.作为内部开发测试数据库环境，跑大概3-6个月的时间。
8.优先企业非核心业务采用新版本的数据库GA版本的软件。
10.想DBA高手请教，或者在技术分为好的群里和大家一起交流，使用真正高手用过的好用的GA版本产品
经过上述工序后，若没有重要的功能BUG或者性能瓶颈，则可以开始考虑作为任何业务数据服务的后端数据库软件。
4.MySQL客户端服务端启动方式
4.1方式一：通过cmd命令窗口
-
启动MySQL的服务器
在终端cmd输入： mysqld -
启动MySQL的客户端
本地测试： mysql -u 用户名 -p 密码 如果在工作中，相关人员会给你数据库的IP地址和端口号，给你配一个账号 密码 mysql -h IP地址 -P 端口号 -u 账号 -p 密码 -
关闭服务端
- 直接关闭开启服务端的cmd的窗口
-
关闭客户端
在客户端的命令窗口输入： exit： quit: 断开连接
4.2 方式二：添加系统服务
我们要以管理员的身份打开终端cmd；
mysqld --install 这样就把mysql服务端添加到操作系统的系统服务中了。
-
启动服务端
-
通过命令启动和关闭服务端
-
以管理员的身份打开终端通过命令关闭和开启MySQL服务端
net start mysql net stop mysql-
注意
如果你已经将MySQL添加到了系统服务中，在从终端输入
mysqld失效了。
-
-
如果将MySQL服务端从系统服务中去除？
在终端输入
mysqld remove
-
5.MySQL服务端目录介绍
看一下data目录下：
其中mysql这个库我需要提一下：
mysql：这个是mysql整个服务的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除，如果对mysql不是很了解，也不要轻易修改这个数据库里面的表信息。
总结：其实这些库就是我们电脑上对应的文件夹，在mysql中显示为对应的库，来方便我们管理数据，而文件或者文件夹这种与硬盘打交道的事情就交给mysql了，我们只需要对mysql库中的数据进行操作就可以了，你可以看到，我们刚才简单使用的时候创建的一个crm库，也就是在data目录下的生成了一个crm文件夹。
6.root用户密码
6.1已知原密码，修改新密码
6.1.1.方式一
方法1： 用SET PASSWORD命令*
首先登录MySQL，使用mysql自带的那个客户端连接上mysql。
格式：mysql> set password for 用户名@localhost = password('新密码');
例子：mysql> set password for root@localhost = password('123');
6.1.2方式二
方法2：用mysqladmin （因为我们将bin已经添加到环境变量了，这个mysqladmin也在bin目录下，所以可以直接使用这个mysqladmin功能，使用它来修改密码）
关于mysqladmin的介绍：是一个执行管理操作的客户端程序。它可以用来检查服务器的配置和当前状态、创建和删除数据库、修改用户密码等等的功能，虽然mysqladmin的很多功能通过使用MySQL自带的mysql客户端可以搞定，但是有时候使用mysqladmin操作会比较简单。
格式：mysqladmin -u用户名 -p旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 123
只用mysqladmin的时候，会出现一个warning警告信息：Warning: Using a password on the command line interface can be insecure.，这个没关系，是提示你，你直接在cmd下使用明文设置密码的时候，是不安全的，因为别人可以通过翻看你输入指令的历史记录来查看到你设置的密码，所以提示你一下，不信你按上下键，可以看到自己之前输入的命令，或者输入下面这个指令也可以看到：查看历史命令：doskey/history
所以我们最好连接进入到mysql里面之后，在进行密码的修改和设置。
6.2忘记密码如何设置新密码
前提：除root账户外所有帐户都适用。
先用root账户登录mysql：
查看mysql数据库的user表：
最后修改该用户所在的行记录：
update user set password=password(‘123’) where user=‘root’ and host=‘localhost’;
然后可以强制刷新：
mysql> flush privileges; 刷新权限，让其生效，否则不生效，修改不成功。
7.MySQL数据库 库的操作
7.1创建库的语法
create database 库名 charset 编码;
库名：
1. 数字自母下划线组成
2. 唯一性
3. 库名长度限制128位
4. 不能单独使用数字，不能使用关键字。
5. 区分大小写
7.2库的常用操作
# 创建一个库：
create database 库名 charset utf8;
# 删除一个库：
drop database 库名;
# 使用/进入 一个库：
use 库名；
# 查看所有的库：
show databases;
# 查看指定的库：
show create database 库名;
# 查看当前的库：
select database();
# 修改一个库的信息：
alter database 库名 charset gbk;
8.MySQL数据库·表的操作
8.1存储引擎
存储引擎是标识MySQL数据库的不同的表的类型，不同的存储引擎底层与磁盘的处理机制不同，性能不同，功能不同，占用空间不同等等，但是展示给用户的数据相同，sql相同。
存储引擎即表类型，mysql根据不同的表类型会有不同的处理机制，存储引擎的介绍：
首先确定一点，存储引擎的概念是MySQL里面才有的，不是所有的关系型数据库都有存储引擎这个概念，后面我们还会说，但是现在要确定这一点。
在讲清楚什么是存储引擎之前，我们先来个比喻，我们都知道录制一个视频文件，可以转换成不同的格式，例如mp4，avi，wmv等，而存在我们电脑的磁盘上也会存在于不同类型的文件系统中如windows里常见的ntfs、fat32，存在于linux里常见的ext3，ext4，xfs，但是，给我们或者用户看懂实际视频内容都是一样的。直观区别是，占用系统的空间大小与清晰程度可能不一样。
那么数据库表里的数据存储在数据库里及磁盘上和上述的视频格式及存储磁盘文件的系统格式特征类似，也有很多种存储方式。
但是对于用户和应用程序来说同样一张表的数据，无论用什么引擎来存储，用户能够看到的数据是一样的。不同储引擎存取，引擎功能，占用空间大小，读取性能等可能有区别。说白了，存储引擎就是在如何存储数据、提取数据、更新数据等技术方法的实现上，底层的实现方式不同，那么就会呈现出不同存储引擎有着一些自己独有的特点和功能，对应着不同的存取机制。
因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(即：对表的存储、操作等的实现方法不同)，表是什么，表本质上就是磁盘上的文件。
其实MySQL支持多种存储引擎，每种引擎有着一些自己独特的功能，用户在使用的时候，可以根据自己的业务场景来使用不同的存储引擎，其中MySQL最常用的存储引擎为：MyISAM和InnoDB。
在详细介绍这些存储引擎之前，我们先来看看MySQL的整个工作流程，看一下存储引擎在哪里，MySQL都做了哪些事情。
8.2 MySQL服务端的处理流程
看下图：这是我在MySQL官方手册上拿下来的(手册你如果能够玩好，将来你就能做一个NB的DBA~~~)
英文版的看着很难受啊超哥，好好好，给你来个中文的，看下图：
来，看每个部分的解释，看下图：
搞明白整个过程之后，我们来细说一下这些存储引擎。
8.3 MySQL存储引擎分类
**重点引擎: ** MyISAM引擎、InnoDB引擎、Memory 存储引擎、BLACKHOLE引擎
8.3.1 *MyISAM引擎
MyISAM引擎特点： ！# 重点 #
1.不支持事务 ！
事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败。
2.表级锁定 ！
数据更新时锁定整个表：其锁定机制是表级锁定，也就是对表中的一个数据进行操作都会将这个表锁定，其他人不能操作这个表，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。
3.读写互相阻塞
不仅会在写入的时候阻塞读取，MyISAM还会再读取的时候阻塞写入，但读本身并不会阻塞另外的读。
4.只会缓存索引
MyISAM可以通过key_buffer_size的值来提高缓存索引，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。
5.读取速度较快
占用资源相对较少
6.不支持外键约束，但支持全文索引 ！
7.MyISAM引擎是MySQL5.5版本之前的默认引擎，是对最初的ISAM引擎优化的产物。
8.3.2 InnoDB引擎
InnoDB引擎 ！# 重点 #
介绍：InnoDB引擎是MySQL数据库的另一个重要的存储引擎，正称为目前MySQL AB所发行新版的标准，被包含在所有二进制安装包里。和其他的存储引擎相比，InnoDB引擎的优点是支持兼容ACID的事务(类似于PostGreSQL)，以及参数完整性(即对外键的支持)。Oracle公司与2005年10月收购了Innobase。Innobase采用双认证授权。它使用GNU发行，也允许其他想将InnoDB结合到商业软件的团体获得授权。
InnoDB引擎特点：
1.支持事务：支持4个事务隔离界别，支持多版本读。 ！
2.行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。 ！
3.读写阻塞与事务隔离级别相关(有多个级别，这就不介绍啦~)。
4.具体非常高效的缓存特性：能缓存索引，也能缓存数据。
5.整个表和主键与Cluster方式存储，组成一颗平衡树。(了解)
6.所有SecondaryIndex都会保存主键信息。(了解)
7.支持分区，表空间，类似oracle数据库。
8.支持外键约束，不支持全文索引(5.5之前)，以后的都支持了。 ！
9.和MyISAM引擎比较，InnoDB对硬件资源要求还是比较高的。
小结：三个重要功能：Supports transactions，row-level locking，and foreign keys
8.3.3 其他的引擎
Memory，BLACKHOLE
#NDB 存储引擎
2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。
#Memory 存储引擎 ！# 重点 #
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。
#Infobright 存储引擎
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。
#NTSE 存储引擎
网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。
#BLACKHOLE ！# 重点 #
黑洞存储引擎，可以应用于主备复制中的分发主库。
MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。
9.事务
事务的解释：从逻辑的角度来说：事务是一组sql语句，要不全部执行成功，要不全部执行失败（失败了有回滚机制，回滚到只之前的状态）.
事务的四个特性：原子性，一致性，隔离性，持久性。
事务介绍：
简单地说，事务就是指逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句，执行时要么全成功要么全失败。
例如：你给我转账5块钱，流程如下
a.从你银行卡取出5块钱，剩余计算money-5
b.把上面5块钱打入我的账户上，我收到5块，剩余计算money+5.
上述转账的过程，对应的sql语句为：
update 你_account set money=money-5 where name='你'；
update 我_account set money=money+5 where name='我'；
上述的两条SQL操作，在事务中的操作就是要么都执行，要么都不执行，不然钱就对不上了。
这就是事务的原子性(Atomicity)。
事务的四大特性：
1.原子性(Atomicity)
事务是一个不可分割的单位，事务中的所有SQL等操作要么都发生，要么都不发生。
2.一致性(Consistency)
事务发生前和发生后，数据的完整性必须保持一致。
3.隔离性(Isolation)
当并发访问数据库时，一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据是相互隔离的。也就是其他人的操作在这个事务的执行过程中是看不到这个事务的执行结果的，也就是他们拿到的是这个事务执行之前的内容，等这个事务执行完才能拿到新的数据。
4.持久性(Durability)
一个事务一旦被提交，它对数据库中的数据改变就是永久性的。如果出了错误，事务也不允撤销，只能通过'补偿性事务'。
事务的开启：
数据库默认事务是自动提交的，也就是发一条sql他就执行一条。如果想多条sql放在一个事务中执行，则需要使用事务进行处理。当我们开启一个事务，并且没有提交，mysql会自动回滚事务。或者我们使用rollback命令手动回滚事务。
数据库开启事务的命令，我们后面会讲到~~~
10.数据库·表的操作
10.1 表的介绍
数据库中的表就是一个特殊的文件，一条数据就是一行记录，每一列都有一个列名在数据库中称为字段。
10.2 创建表
-
创建表的语法
#语法： create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); #注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选、非必须，宽度指的就是字段长度约束，例如：char(10)里面的10 3. 字段名和类型是必须的测试
create table t1(id int,name char(15)); -
给表中插入数据
mysql> insert into t1 values -> (1, '小胖'), -> (2, '大个'); Query OK, 2 rows affected, 2 warnings (0.01 sec) Records: 2 Duplicates: 0 Warnings: 2 -
查看表中的数据
mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1 | 小青 | +------+------+ -
查看表结构
desc 表名;
10.3 MySQL基础数据类型
10.3.1 数值类型
整数类型：tinyint smallint int bigint …
作用：id, 年龄，学号，电话，身份证号.
-
测试
-
tinyint 范围测试
数据库可以设置严格模式和非严格模式，默认的一般是非严格模式。 mysql> create table t2(id int, age tinyint); Query OK, 0 rows affected (0.01 sec) mysql> insert into t2 values -> (1, -129), -> (2, -1000), -> (2, 40), -> (3, 128), -> (4, 2000); Query OK, 5 rows affected, 4 warnings (0.01 sec) Records: 5 Duplicates: 0 Warnings: 4 mysql> select * from t2; +------+------+ | id | age | +------+------+ | 1 | -128 | | 2 | -128 | | 2 | 40 | | 3 | 127 | | 4 | 127 | +------+------+ 5 rows in set (0.00 sec) -
给某个字段设置无符号
mysql> create table t3(x tinyint unsigned); Query OK, 0 rows affected (0.01 sec) mysql> insert into t3 values -> (-1), -> (-200), -> (20); Query OK, 3 rows affected, 2 warnings (0.01 sec) Records: 3 Duplicates: 0 Warnings: 2 mysql> select * from t3; +------+ | x | +------+ | 0 | | 0 | | 20 | +------+ 3 rows in set (0.00 sec)
-
-
整型类型的宽度限制的问题
整型的宽度设置不是限定的数据的位数，只是限定的显示位数，超出了也不报错。
mysql> create table t4(x int(4),name char(4)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t4 values -> (1,'t'), -> (22, '中国'), -> (223, '中国好'), -> (2233, '中国好呀'), -> (223345, '中国好呀nb'); Query OK, 5 rows affected, 1 warning (0.00 sec) Records: 5 Duplicates: 0 Warnings: 1 mysql> select * from t4; +--------+----------+ | x | name | +--------+----------+ | 1 | t | | 22 | 中国 | | 223 | 中国好 | | 2233 | 中国好呀 | | 223345 | 中国好呀 | +--------+----------+ 5 rows in set (0.00 sec) mysql> create table t5(x int(4) zerofill); Query OK, 0 rows affected (0.01 sec) mysql> insert into t5 values -> (1), -> (22), -> (333333); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t5; +--------+ | x | +--------+ | 0001 | | 0022 | | 333333 | +--------+ 3 rows in set (0.00 sec)
10.3.2 浮点类型
浮点类型：float double decimal
作用：身高，体重，价格，温度，平均分数等等。
-
语法
1.FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 定义： 单精度浮点数（非准确小数值），m是整数部分+小数部分的总个数，d是小数点后个数。m最大值为255，d最大值为30，例如：float(255,30) 有符号： -3.402823466E+38 to -1.175494351E-38, 1.175494351E-38 to 3.402823466E+38 无符号： 1.175494351E-38 to 3.402823466E+38 精确度： **** 随着小数的增多，精度变得不准确 **** 2.DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 定义： 双精度浮点数（非准确小数值），m是整数部分+小数部分的总个数，d是小数点后个数。m最大值也为255，d最大值也为30 有符号： -1.7976931348623157E+308 to -2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308 无符号： 2.2250738585072014E-308 to 1.7976931348623157E+308 精确度： ****随着小数的增多，精度比float要高，但也会变得不准确 **** 3.decimal[(m[,d])] [unsigned] [zerofill] 定义： 准确的小数值，m是整数部分+小数部分的总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。比float和double的整数个数少，但是小数位数都是30位 精确度： **** 随着小数的增多，精度始终准确 **** 对于精确数值计算时需要用此类型 decimal能够存储精确值的原因在于其内部按照字符串存储。 精度从高到低：decimal、double、float decimal精度高，但是整数位数少 float和double精度低，但是整数位数多 float已经满足绝大多数的场景了，但是什么导弹、航线等要求精度非常高，所以还是需要按照业务场景自行选择，如果又要精度高又要整数位数多，那么你可以直接用字符串来存。-
测试
mysql> create table t6(x float(255,30),y double(255,30),z decimal(65,30)); Query OK, 0 rows affected (0.06 sec) mysql> insert into t6 values -> (1.1111111111111111111111111111111,1.1111111111111111111111111111111,1.1111111111111111111111111111111); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t6; +----------------------------------+----------------------------------+----------------------------------+ | x | y | z | +----------------------------------+----------------------------------+----------------------------------+ | 1.111111164093017600000000000000 | 1.111111111111111200000000000000 | 1.111111111111111111111111111111 | +----------------------------------+----------------------------------+----------------------------------+ 1 row in set (0.00 sec)
-
10.3.3 日期类型
日期类型：year date time datatime timestamp
作用：注册日期，登录日期，生日，出生日期，纪念日等等。
-
语法
YEAR YYYY（范围：1901/2155）2018 DATE YYYY-MM-DD（范围：1000-01-01/9999-12-31）例：2018-01-01 TIME HH:MM:SS（范围：'-838:59:59'/'838:59:59'）例：12:09:32 DATETIME YYYY-MM-DD HH:MM:SS（范围：1000-01-01 00:00:00/9999-12-31 23:59:59 Y）例： 2018-01-01 12:09:32 TIMESTAMP YYYYMMDD HHMMSS（范围：1970-01-01 00:00:00/2037 年某时） -
测试
mysql> create table t7(y year,d date, t time, dt datetime); Query OK, 0 rows affected (0.01 sec) mysql> insert into t7 values -> ('2018','2018-12-12','14:20:45','2018-12-12 14:20:45'); Query OK, 1 row affected (0.01 sec) mysql> select * from t7; +------+------------+----------+---------------------+ | y | d | t | dt | +------+------------+----------+---------------------+ | 2018 | 2018-12-12 | 14:20:45 | 2018-12-12 14:20:45 | +------+------------+----------+---------------------+ 1 row in set (0.00 sec) mysql> insert into t7 values -> (now(),now(),now(),now()); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t7; +------+------------+----------+---------------------+ | y | d | t | dt | +------+------------+----------+---------------------+ | 2018 | 2018-12-12 | 14:20:45 | 2018-12-12 14:20:45 | | 2020 | 2020-12-08 | 15:31:22 | 2020-12-08 15:31:22 | +------+------------+----------+---------------------+ 2 rows in set (0.00 sec) timestamp: mysql> create table t8(tm timestamp); Query OK, 0 rows affected (0.01 sec) mysql> insert into t8 values -> (); Query OK, 1 row affected (0.00 sec) mysql> select * from t8; +---------------------+ | tm | +---------------------+ | 2020-12-08 15:32:21 | +---------------------+ 1 row in set (0.00 sec) ============注意啦，注意啦，注意啦=========== 1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入 2. 插入年份时，尽量使用4位值 3. 插入两位年份时，<=69，以20开头，比如50, 结果2050 >=70，以19开头，比如71，结果1971 mysql> insert into t7 values -> (45,now(),now(),now()); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t7; +------+------------+----------+---------------------+ | y | d | t | dt | +------+------------+----------+---------------------+ | 2018 | 2018-12-12 | 14:20:45 | 2018-12-12 14:20:45 | | 2020 | 2020-12-08 | 15:31:22 | 2020-12-08 15:31:22 | | 2045 | 2020-12-08 | 15:33:55 | 2020-12-08 15:33:55 | +------+------------+----------+---------------------+ 3 rows in set (0.00 sec) mysql> insert into t7 values -> (78,now(),now(),now()); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t7; +------+------------+----------+---------------------+ | y | d | t | dt | +------+------------+----------+---------------------+ | 2018 | 2018-12-12 | 14:20:45 | 2018-12-12 14:20:45 | | 2020 | 2020-12-08 | 15:31:22 | 2020-12-08 15:31:22 | | 2045 | 2020-12-08 | 15:33:55 | 2020-12-08 15:33:55 | | 1978 | 2020-12-08 | 15:34:24 | 2020-12-08 15:34:24 | +------+------------+----------+---------------------+ 4 rows in set (0.00 sec)
10.3.4 字符串类型
字符串类型：char ,varchar,blob,text等等
作用：姓名，性别，少量信息数据等等。
-
语法
官网：https://dev.mysql.com/doc/refman/5.7/en/char.html #注意：char和varchar括号内的参数指的都是字符的长度 #char类型：定长，简单粗暴，浪费空间，存取速度快 字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节） 存储： 存储char类型的值时，会往右填充空格来满足长度 例如：指定长度为10，存>10个字符则报错(严格模式下)，存<10个字符则用空格填充直到凑够10个字符存储 检索： 在检索或者说查询时，查出的结果会自动删除尾部的空格，如果你想看到它补全空格之后的内容，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH';） #varchar类型：变长，精准，节省空间，存取速度慢 字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html） 存储： varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来 强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用） 如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255） 如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535） 检索： 尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容 -
测试
mysql> create table t9(n1 char(4), n2 varchar(4)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t9 values -> ('sb', 'sb'), -> ('sbb', 'sbb'), -> ('ssbb', 'ssbb'), -> ('ssbb1', 'ssbb2'); Query OK, 4 rows affected, 2 warnings (0.01 sec) Records: 4 Duplicates: 0 Warnings: 2 mysql> select * from t9; +------+------+ | n1 | n2 | +------+------+ | sb | sb | | sbb | sbb | | ssbb | ssbb | | ssbb | ssbb | +------+------+ 4 rows in set (0.00 sec)char和varchar性能对比：
以char(5)和varchar(5)来比较，加入我要存三个人名：sb，ssb1，ssbb2
char：
优点：简单粗暴，不管你是多长的数据，我就按照规定的长度来存，5个5个的存，三个人名就会类似这种存储：sb ssb1 ssbb2，中间是空格补全，取数据的时候5个5个的取，简单粗暴速度快
缺点：貌似浪费空间，并且我们将来存储的数据的长度可能会参差不齐varchar：
varchar类型不定长存储数据，更为精简和节省空间
例如存上面三个人名的时候类似于是这样的：sbssb1ssbb2，连着的，如果这样存，请问这三个人名你还怎么取出来，你知道取多长能取出第一个吗？(超哥，我能看出来啊，那我只想说：滚犊子！)
不知道从哪开始从哪结束，遇到这样的问题，你会想到怎么解决呢？还记的吗？想想？socket？tcp？struct？把数据长度作为消息头。所以，varchar在存数据的时候，会在每个数据前面加上一个头，这个头是1-2个bytes的数据，这个数据指的是后面跟着的这个数据的长度，1bytes能表示28=256，两个bytes表示216=65536，能表示0-65535的数字，所以varchar在存储的时候是这样的：1bytes+sb+1bytes+ssb1+1bytes+ssbb2，所以存的时候会比较麻烦，导致效率比char慢，取的时候也慢，先拿长度，再取数据。
优点：节省了一些硬盘空间，一个acsii码的字符用一个bytes长度就能表示，但是也并不一定比char省，看一下官网给出的一个表格对比数据，当你存的数据正好是你规定的字段长度的时候，varchar反而占用的空间比char要多。
| Value | CHAR(4) | Storage Required | VARCHAR(4) | Storage Required |
|---|---|---|---|---|
'' | ' ' | 4 字符 | '' | 1 字符 |
'ab' | 'ab ' | 4字符 | 'ab' | 3 字符 |
'abcd' | 'abcd' | 4 字符 | 'abcd' | 5字符 |
'abcdefgh' | 'abcd' | 4 字符 | 'abcd' | 5 字符 |
缺点：存取速度都慢
总结：
需要根据业务需求来选择用哪种类型来存
其实在多数的用户量少的工作场景中char和varchar效率差别不是很大，最起码给用户的感知不是很大，并且其实软件级别的慢远比不上硬件级别的慢，所以你们公司的运维发现项目慢的时候会加内存、换nb的硬盘，项目的效率提升的会很多，但是我们作为专业人士，我们应该提出来这样的技术点来提高效率。
但是对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。
所以啊，两个选哪个都可以，如果是大型并发项目，追求高性能的时候，需要结合你们服务器的硬件环境来进行测试，看一下char和varchar哪个更好，这也能算一个优化的点吧~~~~
10.3.5 枚举和集合
枚举：提供了一种选择：多选一。 性别，
集合：提供了一种选择：多选多。爱好，
字段的值只能在给定范围中选择，如单选框，多选框，如果你在应用程序或者前端不做选项限制，在MySQL的字段里面也能做限制
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）
-
测试
mysql> create table t10(id int, sex enum('female','male','laddboy'),hobby set('球类','舞蹈','太极','极限运动')); Query OK, 0 rows affected (0.01 sec) mysql> insert into t10 values -> (1,'位置','球类'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t10; +------+------+-------+ | id | sex | hobby | +------+------+-------+ | 1 | | 球类 | +------+------+-------+ 1 row in set (0.00 sec) mysql> insert into t10 values -> (1,'male','球类,太极'); Query OK, 1 row affected (0.01 sec) mysql> select * from t10; +------+------+-----------+ | id | sex | hobby | +------+------+-----------+ | 1 | | 球类 | | 1 | male | 球类,太极 | +------+------+-----------+ 2 rows in set (0.00 sec)
10.4 表的约束条件
10.4.1 表的约束条件介绍
介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
说明
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)
10.4.2 not null default
not null : 限定该字段不能为空
default：设置该字段的默认值
-
测试
# 默认情况下 字段可以为null，什么都不插入默认也为null. mysql> create table t11(id int,name char(4)); Query OK, 0 rows affected (0.01 sec) mysql> desc t11; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(4) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> insert into t11 values -> (); Query OK, 1 row affected (0.01 sec) mysql> insert into t11 values -> (1,null); Query OK, 1 row affected (0.01 sec) mysql> select * from t11; +------+------+ | id | name | +------+------+ | NULL | NULL | | 1 | NULL | +------+------+ 2 rows in set (0.00 sec) mysql> create table t12(id int not null); Query OK, 0 rows affected (0.01 sec) mysql> insert into t12 values -> (); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t12; +----+ | id | +----+ | 0 | +----+ 1 row in set (0.00 sec) mysql> create table t13(name char(4) not null); Query OK, 0 rows affected (0.01 sec) mysql> insert into t13 values -> (); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t13; +------+ | name | +------+ | | +------+ 1 row in set (0.00 sec) mysql> SET sql_mode = 'strict_trans_tables'; # 设置了严格模式之后，就不能插入空 Query OK, 0 rows affected (0.00 sec) mysql> insert into t13 values -> (); ERROR 1364 (HY000): Field 'name' doesn't have a default value # default mysql> create table t14(name char(4) default '张三'); Query OK, 0 rows affected (0.01 sec) mysql> insert into t14 values -> (); Query OK, 1 row affected (0.01 sec) mysql> select * from t14; +------+ | name | +------+ | 张三 | +------+ 1 row in set (0.00 sec) # not null 与default 配合使用不合理，按照需求去设置字段 mysql> create table t15(id int, name char(4) not null default '张三'); Query OK, 0 rows affected (0.01 sec) mysql> insert into t15 values -> (1,); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2 mysql> insert into t15 values -> (1,null); ERROR 1048 (23000): Column 'name' cannot be null mysql> insert into t15 values -> (2,''); Query OK, 1 row affected (0.01 sec) mysql> select * from t15; +------+------+ | id | name | +------+------+ | 2 | | +------+------+ 1 row in set (0.00 sec)
10.4.3 unique
unique: 唯一的，限制的该字段的数据唯一性。
独一无二，唯一属性：id，身份证号等
是一种key，唯一键，是在数据类型之外的附加属性，其实还有加速查询的作用，后面再讲这个。
创建unique
-
测试
-
单独字段的唯一设置
mysql> create table t16(id int unique,name char(4)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t16 values -> (1,'大个'), -> (1,'大个'); ERROR 1062 (23000): Duplicate entry '1' for key 'id' mysql> insert into t16 values -> (1,'大个'), -> (2,'大个'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t16; +------+------+ | id | name | +------+------+ | 1 | 大个 | | 2 | 大个 | +------+------+ 2 rows in set (0.00 sec) -
多个字段设置联合唯一
mysql> create table t17(id int, account char(10),password char(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t17 values -> (1,123123,111111), -> (2,123123,111111); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t17; +------+---------+----------+ | id | account | password | +------+---------+----------+ | 1 | 123123 | 111111 | | 2 | 123123 | 111111 | +------+---------+----------+ 2 rows in set (0.00 sec) mysql> create table t18(id int, account char(10),password char(10),unique(account,password)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t18 values -> (1,123123,111111), -> (2,123123,112222), -> (3,343112,112222); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into t18 values -> (1,123123,111111); ERROR 1062 (23000): Duplicate entry '123123-111111' for key 'account' mysql> select * from t18; +------+---------+----------+ | id | account | password | +------+---------+----------+ | 1 | 123123 | 111111 | | 2 | 123123 | 112222 | | 3 | 343112 | 112222 | +------+---------+----------+ 3 rows in set (0.00 sec)
-
11.自定义my.ini配置文件
my.ini可以解决的问题：
- 可以让我们创建的库默认使用utf8的数据集。
- 让我们每次登陆MySQL客户端。
找到MySQL的安装目录，将配置好的my.ini文件放在其目录下：
[mysqld]
character_set_server=utf8
default-storage-engine=INNODB
[client]
default-character-set=utf8
user=root
password=111
[mysql]
default-character-set=utf8
重启mysql的服务端：
以管理员的身份打开终端：
net stop mysql
net start mysql
12.MySQL的表的约束
# not null default 配合使用
mysql> create table t19(id int, name char(10) not null default '张三');
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t19(id) values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t19;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
+------+--------+
1 row in set (0.00 sec)
12.1 primary key
主键，从约束的角度来说主键 == not null unique
主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
一个表中可以：
单列做主键
多列做主键（复合主键或者叫做联合主键）
-
每个MySQL的表必须有且只有一个主键，如果你没有设置主键，MySQL会自动从第一个字段开始找not null unique的字段，如果都没有找到，那么MySQL会给你这张表虚拟一个字段当做主键。
一张表中必须有，并且只能由一个主键字段：innodb引擎下存储表数据的时候，会通过你的主键字段的数据来组织管理所有的数据，将数据做成一种树形结构的数据结构，帮你较少IO次数，提高获取定位数据、获取数据的速度，优化查询。
解释：如果我们在一张表中没有设置primary key，那么mysql在创建表的时候，会按照顺序从上到下遍历你设置的字段，直到找到一个not null unique的字段，自动识别成主键pri，通过desc可以看到，这样是不是不好啊，所以我们在创建表的时候，要给他一个主键，让他优化的时候用，如果没有pri也没有not null unique字段，那么innodb引擎下的mysql被逼无奈，你没有设置主键字段，主键又有不为空且唯一的约束，又不能擅自给你的字段加上这些约束，那么没办法，它只能给你添加一个隐藏字段来帮你组织数据，如果是这样，你想想，主键是不是帮我们做优化查询用的啊，这个优化是我们可以通过主键来查询数据：例如：如果我们将id设置为主键，当我们查一个id为30的数据的时候，也就是select * from tb11 where id=30；这个查询语句的速度非常快，不需要遍历前面三十条数据，就好像我们使用的字典似的，找一个字，不需要一页一页的翻书，可以首先看目录，然后看在哪一节，然后看在哪一页，一步步的范围，然后很快就找到了，这就像我们说的mysql的索引(主键、唯一键)的工作方式，一步一步的缩小范围来查找，几步就搞定了，所以通过主键你能够快速的查询到你所需要的数据，所以，如果你的主键是mysql帮你加的隐藏的字段，你查询数据的时候，就不能将这个隐藏字段作为条件来查询数据了，就不能享受到优化后的查询速度了，对么+ -
一般我们给一个表的id字段设置成主键。
-
设置主键的两种方式
# 方式一；
mysql> create table t1(id int primary key,name char(10));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
# 方式二：
mysql> create table t2(id int, name char(10),constraint pk_name primary key(id));
Query OK, 0 rows affected (0.01 sec)
mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
-
测试没有主键mysql会将not null unique的字段设置成主键
mysql> create table t3(id int not null unique, name char(10)); Query OK, 0 rows affected (0.01 sec) mysql> desc t3; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) -
联合主键
mysql> create table t4(id int, ip char(15), port int,primary key(ip, port)); Query OK, 0 rows affected (0.01 sec) mysql> desc t4; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | ip | char(15) | NO | PRI | | | | port | int(11) | NO | PRI | 0 | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
12.2 auto_increment
之前我们插入数据的时候，id也需要自己来写，是不是很麻烦啊，我们是不是想，只要有一条记录就直接插入进去啊，不需要考虑说，你现在存储到第多少条数据了，对不对，所以出现了一个叫做auto_increment的属性
约束字段为自动增长，被约束的字段必须同时被key约束，也就是说只能给约束成key的字段加自增属性，默认起始位置为1，步长也为1.
-
测试
mysql> create table t5(id int primary key auto_increment,name char(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t5(name) values -> ('大个'), -> ('啊呀'), -> ('小青'), -> ('蘑菇头'); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t5; +----+-----------+ | id | name | +----+-----------+ | 1 | 大个 | | 2 | 啊呀 | | 3 | 小青 | | 4 | 蘑菇头 | +----+-----------+ 4 rows in set (0.00 sec) mysql> insert into t5 values -> (10,'人生'); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; +----+-----------+ | id | name | +----+-----------+ | 1 | 大个 | | 2 | 啊呀 | | 3 | 小青 | | 4 | 蘑菇头 | | 10 | 人生 | +----+-----------+ 5 rows in set (0.00 sec) mysql> insert into t5(name) values -> ('松平'); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +----+-----------+ | id | name | +----+-----------+ | 1 | 大个 | | 2 | 啊呀 | | 3 | 小青 | | 4 | 蘑菇头 | | 10 | 人生 | | 11 | 松平 | +----+-----------+ 6 rows in set (0.00 sec) mysql> insert into t5 values -> (7,'子硕'); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +----+-----------+ | id | name | +----+-----------+ | 1 | 大个 | | 2 | 啊呀 | | 3 | 小青 | | 4 | 蘑菇头 | | 7 | 子硕 | | 10 | 人生 | | 11 | 松平 | +----+-----------+ 7 rows in set (0.00 sec) # 利用delete删除所有数据在插入数据并不是从1开始，而是从上一次最大的数开始。 mysql> delete from t5 ; Query OK, 7 rows affected (0.00 sec) mysql> insert into t5(name) values -> ('太白'); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +----+--------+ | id | name | +----+--------+ | 12 | 太白 | +----+--------+ 1 row in set (0.00 sec) # 利用truncate清空表内容，然后再插入数据从1开始的。 mysql> truncate t5; Query OK, 0 rows affected (0.01 sec) mysql> insert into t5(name) values -> ('太白'); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +----+--------+ | id | name | +----+--------+ | 1 | 太白 | +----+--------+ 1 row in set (0.00 sec)
12.3 foreign key
foreign key 外键，给一个字段设置了外键foreign key 该字段受到其他的表的某个字段的约束。
外键是确定两张表之间的关系的约束条件，两张表有几种关系？多对一，多对多，一对一。
12.3.1 多对一
快速理解foreign key（外键其实就是标明表和表之间的关系，表和表之间如果有关系的话就三种：一对一，多对一，多对多，我们挨个看看~）
员工信息表有三个字段：工号 姓名 部门
公司有3个部门，但是有11个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费
那这就体现出来了三个缺点：
1.表的组织结构不清晰：员工的信息、部门的信息等等都掺在一张表里面。
2.浪费空间，每一条信息都包含员工和部门，多个员工从属一个部门，也需要每个员工的信息里都包含着部门的信息，浪费硬盘空间。
3.扩展性极差：如果想修改一个部门的信息，比如修改部门名称，那么这个包含员工和部门信息的表中的所有的包含这个部门信息的数据都需要进行修改，那么修改起来就非常麻烦，这是非常致命的缺点。
解决方法：（画一个excel表格来表示一下效果~~）
-
如何判断两张表的关系
分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左对右 多对一 #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右对左 多对一 #多对一： 如果是单项的多对一那么这两张表就是多对一 #多对多 如果是双向的多对一那么这两张表就是多对多 #一对一: 左表的一条记录唯一对应右表的一条记录，反之亦然。则两张表是一对一。 -
foreign key约束应该加在哪张表的哪个字段？
-
先创建被关联表，在创建关联表。
# 错误演示 mysql> create table employee(id int primary key auto_increment, name char(10),sex char(6),dep_id int,foreign key(dep_id)references department(id)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key(dep_id)references department(id))' at line 1 # 正确演示 mysql> create table department(id int primary key auto_increment,d_name char(5),d_info varchar(30)); Query OK, 0 rows affected (0.01 sec) mysql> create table employee(id int primary key auto_increment,name char(5),sex char(6),dep_id int,foreign key(dep_id) references department(id)); Query OK, 0 rows affected (0.01 sec) -
先给被关联表插入数据，然后再给关联表插入数据。
# 错误演示 mysql> insert into employee(name,sex,dep_id) values -> ('大个','male',1); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`nb_db`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`)) # 正确演示 mysql> insert into department(d_name,d_info) values ('保洁部','维护公司环境'), ('安保部','维护公司秩序'), ('开发部','维护公司项目'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into employee(name,sex,dep_id) values ('大个','male',2), ('子硕','male',1), ('小青','female',3), ('旭哥','male',3); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 -
上面这种方式有问题，改动被关联表的数据，关联表不会随之改动，而会报错。
mysql> update department set id=100 where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`nb_db`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`)) -
删除被关联表的数据，关联表不会随之删除，而会报错。
mysql> delete from department where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`nb_db`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`)) mysql> -
3 4 步骤都报错了，这是由于我们建表时没有加上两个功能：on delete cascade on update cascade，级联删除、级联更新。
-
删除原表，重新建表。
mysql> create table department(id int primary key auto_increment,d_name char(5),d_info varchar(30)); Query OK, 0 rows affected (0.01 sec) mysql> create table employee(id int primary key auto_increment,name char(5),sex char(6),dep_id int,foreign key(dep_id) references department(id) on delete cascade on update cascade); Query OK, 0 rows affected (0.01 sec) -
重新插入数据。
mysql> insert into department(d_name,d_info) values -> ('保洁部','维护公司环境'), -> ('安保部','维护公司秩序'), -> ('开发部','维护公司项目'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into employee(name,sex,dep_id) values -> ('大个','male',2), -> ('子硕','male',1), -> ('小青','female',3), -> ('旭哥','male',3); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 -
测试 被关联表更新和删除。
mysql> update department set id = 100 where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from department; +-----+-----------+--------------------+ | id | d_name | d_info | +-----+-----------+--------------------+ | 2 | 安保部 | 维护公司秩序 | | 3 | 开发部 | 维护公司项目 | | 100 | 保洁部 | 维护公司环境 | +-----+-----------+--------------------+ 3 rows in set (0.00 sec) mysql> select * from employee; +----+--------+--------+--------+ | id | name | sex | dep_id | +----+--------+--------+--------+ | 1 | 大个 | male | 2 | | 2 | 子硕 | male | 100 | | 3 | 小青 | female | 3 | | 4 | 旭哥 | male | 3 | +----+--------+--------+--------+ 4 rows in set (0.00 sec) mysql> delete from department where id=100; Query OK, 1 row affected (0.01 sec) mysql> select * from department; +----+-----------+--------------------+ | id | d_name | d_info | +----+-----------+--------------------+ | 2 | 安保部 | 维护公司秩序 | | 3 | 开发部 | 维护公司项目 | +----+-----------+--------------------+ 2 rows in set (0.00 sec) mysql> select * from employee; +----+--------+--------+--------+ | id | name | sex | dep_id | +----+--------+--------+--------+ | 1 | 大个 | male | 2 | | 3 | 小青 | female | 3 | | 4 | 旭哥 | male | 3 | +----+--------+--------+--------+ 3 rows in set (0.00 sec) -
小结
1、先要建立被关联的表才能建立关联表
2、在插入数据记录的时候，要先想被关联表中插入数据，才能往关联表里面插入数据
3、更新或者删除数据的时候，都需要考虑关联表和被关联表的关系
解决方案：
a.删除表的时候，先删除关联表，再删除被关联表
b.重建表的时候，在加外键关联的时候加上这两句：on delete cascade 和 on update cascade
一对多的内容大致就说完了，我们看一下多对多的关系
12.3.2 多对多
书籍与出版社的关系就是多对多的关系。
-
先建被关联表，在建关联表。
mysql> create table book(id int primary key auto_increment,name char(10),price float); Query OK, 0 rows affected (0.01 sec) mysql> create table publish(id int primary key auto_increment,b_name char(10)); Query OK, 0 rows affected (0.01 sec) mysql> create table b_to_p(id int primary key auto_increment,book_id int,publish_id int -> ,foreign key(book_id) references book(id) on delete cascade on update cascade, -> foreign key(publish_id) references publish(id) on delete cascade on update cascade); Query OK, 0 rows affected (0.02 sec) -
插入数据
mysql> insert into book(name,price) values -> ('太白教你学python',999.99), -> ('金瓶梅',998.99), -> ('我用什么才能留住你',1158.99); Query OK, 3 rows affected, 11 warning (0.011 sec) Records: 3 Duplicates: 0 Warnings: 11 mysql> insert into publish(b_name) values -> ('IT出版社'), -> ('nb出版社'), -> ('起价出版社'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into b_to_p(book_id,publish_id) values -> (11,11), -> (11,12), -> (3,12), -> (12,3), -> (3,11); Query OK, 5 rows affected (0.011 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from b_to_p; +----+---------+------------+ | id | book_id | publish_id | +----+---------+------------+ | 11 | 11 | 11 | | 12 | 11 | 12 | | 3 | 3 | 12 | | 4 | 12 | 3 | | 5 | 3 | 11 | +----+---------+------------+ 5 rows in set (0.00 sec) -
删除出版社的某条记录
mysql> delete from publish where id=11; Query OK, 11 row affected (0.00 sec) mysql> select * from publish; +----+-----------------+ | id | b_name | +----+-----------------+ | 12 | nb出版社 | | 3 | 起价出版社 | +----+-----------------+ 12 rows in set (0.00 sec) mysql> select * from b_to_p; +----+---------+------------+ | id | book_id | publish_id | +----+---------+------------+ | 12 | 11 | 12 | | 3 | 3 | 12 | | 4 | 12 | 3 | +----+---------+------------+ 3 rows in set (0.00 sec) mysql> select * from book; +----+-----------------------------+--------+ | id | name | price | +----+-----------------------------+--------+ | 11 | 太白教你学pytho | 999.99 | | 12 | 金瓶梅 | 998.99 | | 3 | 我用什么才能留住你 | 1158.99 | +----+-----------------------------+--------+ 3 rows in set (0.00 sec)
13.一对一
两张表要是一对一的关系：关联表的一条数据对应被关联表的唯一一条数据，不能重复对应。
-
先建立被关联表，在建立关联表。
mysql> create table application(id int primary key auto_increment,name char(10), sex enum('male','female'),phone int); Query OK, 0 rows affected (0.01 sec) mysql> create table student(id int primary key auto_increment,name char(10), app_id int unique,foreign key(app_id) references application(id) on delete cascade on update cascade); Query OK, 0 rows affected (0.01 sec) -
先给被关联表插入数据，在给关联表插入数据。
mysql> insert into application(name,sex,phone) values('1','male',123456),('2','female',451341); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into student(name,app_id) values('1',1),('2',2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select*from application; +----+------+--------+--------+ | id | name | sex | phone | +----+------+--------+--------+ | 1 | 1 | male | 123456 | | 2 | 2 | female | 451341 | +----+------+--------+--------+ 2 rows in set (0.00 sec) mysql> truncate student; Query OK, 0 rows affected (0.02 sec) mysql> insert into student(name,app_id) values('1',1),('2',2); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select*from student; +----+------+--------+ | id | name | app_id | +----+------+--------+ | 1 | 1 | 1 | | 2 | 2 | 2 | +----+------+--------+ 2 rows in set (0.00 sec) mysql>
14.表的总结
-
增
#语法： create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); -
查
show tables; 查询所有的表 desc 表名; 查看指定的表结构 show create table 表名; 查看指定表的详细信息 -
删
drop table 表名; 删除指定表 -
改
语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…], #注意这里可以通过逗号来分割，一下添加多个约束条件 ADD 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; #添加这个字段的时候，把它放到第一个字段位置去。 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; #after是放到后的这个字段的后面去了，我们通过一个first和一个after就可以将新添加的字段放到表的任意字段位置了。 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; #change比modify还多了个改名字的功能，这一句是只改了一个字段名 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];#这一句除了改了字段名，还改了数据类型、完整性约束等等的内容 给一个字段添加外键属性的语句：alter table 表2名 add foreign key(表2的一个字段) references 表1名(表1的一个字段); 注意一点：在mysql里面表名是不区分大小写的，如果你将一个名为t1的(小写的t1)改名为一个T1（大写的T1），是完全没用的，因为在数据库里面表名都是小写的。-
部分代码示例
mysql> alter table t1 rename t100; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-----------------+ | Tables_in_nb_db | +-----------------+ | application | | b_to_p | | book | | department | | employee | | publish | | student | | t100 | | t2 | | t3 | | t4 | | t5 | +-----------------+ 12 rows in set (0.00 sec) mysql> desc t100; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> alter table t100 add sex enum('male','female'); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t100; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(10) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> alter table t100 add hobby char(10) first; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t100; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | hobby | char(10) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | char(10) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> alter table t2 add info_id int after id; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t2; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | info | char(5) | YES | | NULL | | | id | int(11) | YES | | NULL | | | info_id | int(11) | YES | | NULL | | | name | char(5) | YES | | NULL | | +---------+---------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> # 剩下的自己测试
-
15.MySQL数据库·行数据的操作
15.1 增
-
增加单行数据
-
给所有字段都增加数据
# 语法 INSERT INTO 表名 VALUES (值1,值2,值3…值n); #不指定字段的话，就按照默认的几个字段来插入数据 mysql> insert into t5 values(3,'dage','男'); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 太白 | NULL | | 3 | dage | 男 | +----+--------+------+ -
给指定的字段增加数据
# 语法 INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n); #指定字段来插入数据，插入的值要和你前面的字段相匹配 mysql> insert into t5(name,age) values('雄哥','男'); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 太白 | NULL | | 3 | dage | 男 | | 4 | 雄哥 | 男 | +----+--------+------+ 3 rows in set (0.00 sec)
-
-
增加多行数据
-
给所有字段增加多行数据
# 语法 INSERT INTO 表名 VALUES (值1,值2,值3…值n), (值1,值2,值3…值n), (值1,值2,值3…值n); mysql> insert into t5 values -> (5,'小黄','男'), -> (6,'子硕','男'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t5; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 太白 | NULL | | 3 | dage | 男 | | 4 | 雄哥 | 男 | | 5 | 小黄 | 男 | | 6 | 子硕 | 男 | +----+--------+------+ -
给指定的字段增加多行数据
# 语法 INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…), (值1,值2,值3…), ......; mysql> insert into t5(name,age) values ('新超','男'), ('强哥','男'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t5; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 太白 | NULL | | 3 | dage | 男 | | 4 | 雄哥 | 男 | | 5 | 小黄 | 男 | | 6 | 子硕 | 男 | | 7 | 新超 | 男 | | 8 | 强哥 | 男 | +----+--------+------+ 7 rows in set (0.00 sec)
-
15.2 删
-
测试
语法 delete from 表名 where 条件; # 删除一条记录 mysql> delete from t5 where id=1; Query OK, 1 row affected (0.00 sec) mysql> select * from t5; +----+--------+------+ | id | name | age | +----+--------+------+ | 3 | dage | 男 | | 4 | 雄哥 | 男 | | 5 | 小黄 | 男 | | 6 | 子硕 | 男 | | 7 | 新超 | 男 | | 8 | 强哥 | 男 | +----+--------+------+ 6 rows in set (0.00 sec) # 删除多条记录 mysql> delete from t5 where id>6; Query OK, 2 rows affected (0.01 sec) mysql> select * from t5; +----+--------+------+ | id | name | age | +----+--------+------+ | 3 | dage | 男 | | 4 | 雄哥 | 男 | | 5 | 小黄 | 男 | | 6 | 子硕 | 男 | +----+--------+------+ 4 rows in set (0.00 sec) # 删除所有记录 mysql> select * from t5; +----+--------+------+ | id | name | age | +----+--------+------+ | 3 | dage | 男 | | 4 | 雄哥 | 男 | | 5 | 小黄 | 男 | | 6 | 子硕 | 男 | +----+--------+------+ 4 rows in set (0.00 sec) mysql> delete from t5; Query OK, 4 rows affected (0.01 sec) mysql> select * from t5; Empty set (0.00 sec) # 知道delete 和truncate 的区别。
15.3 改
-
测试
#语法 update 表名 set 字段1=结果1,字段2=结果2.... where 条件 # 修改一条记录 mysql> select * from t5; +----+--------+------+ | id | name | age | +----+--------+------+ | 9 | 新超 | 男 | | 10 | 强哥 | 男 | | 11 | 新超 | 男 | | 12 | 强哥 | 男 | | 13 | 新超 | 男 | | 14 | 强哥 | 男 | +----+--------+------+ 6 rows in set (0.00 sec) mysql> update t5 set name='宋平',age='男女' where id=9; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t5; +----+--------+--------+ | id | name | age | +----+--------+--------+ | 9 | 宋平 | 男女 | | 10 | 强哥 | 男 | | 11 | 新超 | 男 | | 12 | 强哥 | 男 | | 13 | 新超 | 男 | | 14 | 强哥 | 男 | +----+--------+--------+ 6 rows in set (0.00 sec) # 修改多条数据 mysql> select * from t5; +----+--------+--------+ | id | name | age | +----+--------+--------+ | 9 | 宋平 | 男女 | | 10 | 强哥 | 男 | | 11 | 新超 | 男 | | 12 | 强哥 | 男 | | 13 | 新超 | 男 | | 14 | 强哥 | 男 | +----+--------+--------+ 6 rows in set (0.00 sec) mysql> update t5 set name='小广播',age='男' where id>9; Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from t5; +----+-----------+--------+ | id | name | age | +----+-----------+--------+ | 9 | 宋平 | 男女 | | 10 | 小广播 | 男 | | 11 | 小广播 | 男 | | 12 | 小广播 | 男 | | 13 | 小广播 | 男 | | 14 | 小广播 | 男 | +----+-----------+--------+ 6 rows in set (0.00 sec)
15.4 查
-
简答语法
#查询数据的本质：mysql会到你本地的硬盘上找到对应的文件，然后打开文件，按照你的查询条件来找出你需要的数据。下面是完整的一个单表查询的语法 select * from，这个select * 指的是要查询所有字段的数据。 SELECT distinct 字段1,字段2... FROM 库名.表名 #from后面是说从库的某个表中去找数据，mysql会去找到这个库对应的文件夹下去找到你表名对应的那个数据文件，找不到就直接报错了，找到了就继续后面的操作 WHERE 条件 #从表中找符合条件的数据记录，where后面跟的是你的查询条件 GROUP BY field（字段） #分组 HAVING 筛选 #过滤，过滤之后执行select后面的字段筛选，就是说我要确定一下需要哪个字段的数据，你查询的字段数据进行去重，然后在进行下面的操作 ORDER BY field（字段） #将结果按照后面的字段进行排序 LIMIT 限制条数 #将最后的结果加一个限制条数，就是说我要过滤或者说限制查询出来的数据记录的条数 关于上面这些内容，我们在下面一个一个的来详细解释 -
优先级顺序
1.找到表:from 2.拿着where指定的约束条件，去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.将分组的结果进行having过滤 5.执行select 6.去重 7.将结果按条件排序：order by 8.限制结果的显示条数
15.5 单表查询
-
创建一张表，插入一些数据
#我们来创建一个员工表，然后对员工表进行一个简单的查询，来看一下效果，下面是员工表的字段 company.employee 员工id id int 姓名 emp_name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 岗位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int #创建表 create table employee( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ #插入记录 #三个部门：教学，销售，运营 insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('太白','male',18,'20170301','CEO',7300.33,401,1), #以下是教学部，全都是老师 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('元宝','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3) ; #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
15.5.1 简单四则运算查询
四则运算：+-*/ concat字符串拼接 concat_ws() 字符串拼接
# 一般我们选择指定字段查询 一般不用*查询所有字段
mysql> select id,name,salary from employee;
+----+------------+------------+
| id | name | salary |
+----+------------+------------+
| 1 | 太白 | 7300.33 |
| 2 | alex | 1000000.31 |
| 3 | wupeiqi | 8300.00 |
| 4 | yuanhao | 3500.00 |
| 5 | liwenzhou | 2100.00 |
| 6 | jingliyang | 9000.00 |
| 7 | 元宝 | 30000.00 |
| 8 | 成龙 | 10000.00 |
| 9 | 歪歪 | 3000.13 |
| 10 | 丫丫 | 2000.35 |
| 11 | 丁丁 | 1000.37 |
| 12 | 星星 | 3000.29 |
| 13 | 格格 | 4000.33 |
| 14 | 张野 | 10000.13 |
| 15 | 程咬金 | 20000.00 |
| 16 | 程咬银 | 19000.00 |
| 17 | 程咬铜 | 18000.00 |
| 18 | 程咬铁 | 17000.00 |
+----+------------+------------+
18 rows in set (0.00 sec)
mysql> select * from employee;
+----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 1 | 太白 | male | 18 | 2017-03-01 | CEO | NULL | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | 元宝 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
# distinct 去重
mysql> select post from employee;
+-----------+
| post |
+-----------+
| CEO |
| teacher |
| teacher |
| teacher |
| teacher |
| teacher |
| teacher |
| teacher |
| sale |
| sale |
| sale |
| sale |
| sale |
| operation |
| operation |
| operation |
| operation |
| operation |
+-----------+
18 rows in set (0.00 sec)
mysql> select distinct post from employee;
+-----------+
| post |
+-----------+
| CEO |
| teacher |
| sale |
| operation |
+-----------+
4 rows in set (0.00 sec)
# 通过四则运算进行查询
# 查询所有员工的姓名，年薪。
mysql> select name,salary*12 from employee;
+------------+-------------+
| name | salary*12 |
+------------+-------------+
| 太白 | 87603.96 |
| alex | 12000003.72 |
| wupeiqi | 99600.00 |
| yuanhao | 42000.00 |
| liwenzhou | 25200.00 |
| jingliyang | 108000.00 |
| 元宝 | 360000.00 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 丁丁 | 12004.44 |
| 星星 | 36003.48 |
| 格格 | 48003.96 |
| 张野 | 120001.56 |
| 程咬金 | 240000.00 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+-------------+
18 rows in set (0.00 sec)
# 给字段其别名
mysql> select name,salary*12 as annual_salary from employee;
+------------+---------------+
| name | annual_salary |
+------------+---------------+
| 太白 | 87603.96 |
| alex | 12000003.72 |
| wupeiqi | 99600.00 |
| yuanhao | 42000.00 |
| liwenzhou | 25200.00 |
| jingliyang | 108000.00 |
| 元宝 | 360000.00 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 丁丁 | 12004.44 |
| 星星 | 36003.48 |
| 格格 | 48003.96 |
| 张野 | 120001.56 |
| 程咬金 | 240000.00 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+---------------+
18 rows in set (0.00 sec)
# concat函数字符串的拼接
# 查询所有员工的姓名 年薪 以这样的形式显示每条记录 '姓名：xx 年薪：xx'
mysql> select concat('姓名：',name,'年薪：',salary*12) from employee;
+------------------------------------------------+
| concat('姓名：',name,'年薪：',salary*12) |
+------------------------------------------------+
| 姓名：太白年薪：87603.96 |
| 姓名：alex年薪：12000003.72 |
| 姓名：wupeiqi年薪：99600.00 |
| 姓名：yuanhao年薪：42000.00 |
| 姓名：liwenzhou年薪：25200.00 |
| 姓名：jingliyang年薪：108000.00 |
| 姓名：元宝年薪：360000.00 |
| 姓名：成龙年薪：120000.00 |
| 姓名：歪歪年薪：36001.56 |
| 姓名：丫丫年薪：24004.20 |
| 姓名：丁丁年薪：12004.44 |
| 姓名：星星年薪：36003.48 |
| 姓名：格格年薪：48003.96 |
| 姓名：张野年薪：120001.56 |
| 姓名：程咬金年薪：240000.00 |
| 姓名：程咬银年薪：228000.00 |
| 姓名：程咬铜年薪：216000.00 |
| 姓名：程咬铁年薪：204000.00 |
+------------------------------------------------+
18 rows in set (0.00 sec)
mysql> select concat('姓名：',name,'年薪：',salary*12) as annual_salary from employee;
+---------------------------------------+
| annual_salary |
+---------------------------------------+
| 姓名：太白年薪：87603.96 |
| 姓名：alex年薪：12000003.72 |
| 姓名：wupeiqi年薪：99600.00 |
| 姓名：yuanhao年薪：42000.00 |
| 姓名：liwenzhou年薪：25200.00 |
| 姓名：jingliyang年薪：108000.00 |
| 姓名：元宝年薪：360000.00 |
| 姓名：成龙年薪：120000.00 |
| 姓名：歪歪年薪：36001.56 |
| 姓名：丫丫年薪：24004.20 |
| 姓名：丁丁年薪：12004.44 |
| 姓名：星星年薪：36003.48 |
| 姓名：格格年薪：48003.96 |
| 姓名：张野年薪：120001.56 |
| 姓名：程咬金年薪：240000.00 |
| 姓名：程咬银年薪：228000.00 |
| 姓名：程咬铜年薪：216000.00 |
| 姓名：程咬铁年薪：204000.00 |
+---------------------------------------+
18 rows in set (0.00 sec)
# concat_ws(连接符,字段1,字段2,....)
mysql> select concat_ws('---->',name,salary*12) as annual_salary from employee;
+--------------------------+
| annual_salary |
+--------------------------+
| 太白---->87603.96 |
| alex---->12000003.72 |
| wupeiqi---->99600.00 |
| yuanhao---->42000.00 |
| liwenzhou---->25200.00 |
| jingliyang---->108000.00 |
| 元宝---->360000.00 |
| 成龙---->120000.00 |
| 歪歪---->36001.56 |
| 丫丫---->24004.20 |
| 丁丁---->12004.44 |
| 星星---->36003.48 |
| 格格---->48003.96 |
| 张野---->120001.56 |
| 程咬金---->240000.00 |
| 程咬银---->228000.00 |
| 程咬铜---->216000.00 |
| 程咬铁---->204000.00 |
+--------------------------+
18 rows in set (0.00 sec)
15.5.2 where条件
-
可以加的条件
where语句中可以使用：
之前我们用where 后面跟的语句是不是id=1这种类型的啊，用=号连接的，除了=号外，还能使用其他的，看下面：
-
比较运算符：> < >= <= <> !=
-
between 80 and 100 值在80到100之间
-
in(80,90,100) 值是80或90或100
-
like ‘egon%’
pattern可以是%或_， %表示任意多字符 _表示一个字符 -
逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
-
-
测试
mysql> select id,name,salary from employee where salary > 15000; +----+-----------+------------+ | id | name | salary | +----+-----------+------------+ | 2 | alex | 1000000.31 | | 7 | 元宝 | 30000.00 | | 15 | 程咬金 | 20000.00 | | 16 | 程咬银 | 19000.00 | | 17 | 程咬铜 | 18000.00 | | 18 | 程咬铁 | 17000.00 | +----+-----------+------------+ 6 rows in set (0.00 sec) mysql> select id,name,salary from employee where salary > 15000; +----+-----------+------------+ | id | name | salary | +----+-----------+------------+ | 2 | alex | 1000000.31 | | 7 | 元宝 | 30000.00 | | 15 | 程咬金 | 20000.00 | | 16 | 程咬银 | 19000.00 | | 17 | 程咬铜 | 18000.00 | | 18 | 程咬铁 | 17000.00 | +----+-----------+------------+ 6 rows in set (0.00 sec) mysql> select id,name,age,salary from employee where age between 18 and 30; +----+------------+-----+----------+ | id | name | age | salary | +----+------------+-----+----------+ | 1 | 太白 | 18 | 7300.33 | | 5 | liwenzhou | 28 | 2100.00 | | 6 | jingliyang | 18 | 9000.00 | | 7 | 元宝 | 18 | 30000.00 | | 11 | 丁丁 | 18 | 1000.37 | | 12 | 星星 | 18 | 3000.29 | | 13 | 格格 | 28 | 4000.33 | | 14 | 张野 | 28 | 10000.13 | | 15 | 程咬金 | 18 | 20000.00 | | 16 | 程咬银 | 18 | 19000.00 | | 17 | 程咬铜 | 18 | 18000.00 | | 18 | 程咬铁 | 18 | 17000.00 | +----+------------+-----+----------+ 12 rows in set (0.00 sec) mysql> select id,name,age,salary from employee where age>=18 and age<=30; +----+------------+-----+----------+ | id | name | age | salary | +----+------------+-----+----------+ | 1 | 太白 | 18 | 7300.33 | | 5 | liwenzhou | 28 | 2100.00 | | 6 | jingliyang | 18 | 9000.00 | | 7 | 元宝 | 18 | 30000.00 | | 11 | 丁丁 | 18 | 1000.37 | | 12 | 星星 | 18 | 3000.29 | | 13 | 格格 | 28 | 4000.33 | | 14 | 张野 | 28 | 10000.13 | | 15 | 程咬金 | 18 | 20000.00 | | 16 | 程咬银 | 18 | 19000.00 | | 17 | 程咬铜 | 18 | 18000.00 | | 18 | 程咬铁 | 18 | 17000.00 | +----+------------+-----+----------+ 12 rows in set (0.00 sec) mysql> select id,name,age,salary from employee where salary > 15000 or age < 25; +----+------------+-----+------------+ | id | name | age | salary | +----+------------+-----+------------+ | 1 | 太白 | 18 | 7300.33 | | 2 | alex | 78 | 1000000.31 | | 6 | jingliyang | 18 | 9000.00 | | 7 | 元宝 | 18 | 30000.00 | | 11 | 丁丁 | 18 | 1000.37 | | 12 | 星星 | 18 | 3000.29 | | 15 | 程咬金 | 18 | 20000.00 | | 16 | 程咬银 | 18 | 19000.00 | | 17 | 程咬铜 | 18 | 18000.00 | | 18 | 程咬铁 | 18 | 17000.00 | +----+------------+-----+------------+ 10 rows in set (0.00 sec) mysql> select id,name,age from employee where age in (20,24,28); +----+-----------+-----+ | id | name | age | +----+-----------+-----+ | 5 | liwenzhou | 28 | | 13 | 格格 | 28 | | 14 | 张野 | 28 | +----+-----------+-----+ 3 rows in set (0.00 sec) mysql> select * from employee where name like '程%'; # 匹配任意多个字符 +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 4 rows in set (0.00 sec) mysql> select * from employee where name like 'jing%g'; +----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | +----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+ 1 row in set (0.00 sec) mysql> select * from employee where name like '__'; +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 1 | 太白 | male | 18 | 2017-03-01 | CEO | NULL | 7300.33 | 401 | 1 | | 7 | 元宝 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | +----+--------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 9 rows in set (0.00 sec) # is 只能与 null 配合使用 mysql> select * from employee where name is 'alex'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''alex'' at line 1 mysql> select * from employee where post_comment is null; +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 1 | 太白 | male | 18 | 2017-03-01 | CEO | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | 元宝 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) # null '' 不是一个类型。 mysql> select * from employee where post_comment='' and id = 1; Empty set (0.00 sec)where条件咱们就说完了，这个where条件到底怎么运作的，我们来说一下：我们以select id,name,age from employee where id>7;这个语句来说一下
首先先找到employee表，找到这个表之后，mysql会拿着where后面的约束条件去表里面找符合条件的数据，然后遍历你表中所有的数据，查看一下id是否大于7，逐条的对比，然后只要发现id比7大的，它就会把这一整条记录给select，但是select说我只拿id、name、age这个三个字段里面的数据，然后就打印了这三个字段的数据，然后where继续往下过滤，看看id是不是还有大于7的，然后发现一个符合条件的就给select一个，然后重复这样的事情，直到把数据全部过滤一遍才会结束。这就是where条件的一个工作方式。
16.单表查询
16.1 group by
16.1.1 什么是分组，为什幺要分组？
分组就是讲MySQL表中（默认）所有的行数据按照相同的字段汇成一类（一组）。按照部门分组，按照男女性别分组，按照办公室分组，按照部门编号进行分组等等。
你们认为那些字段可以进行分组？分组的字段一定要是重复度较高的字段，这样分组才有意义。
为什么我们要分组？分完组之后，我们可以进行以组为单位，统计数据：
查看每个部门的最高工资
查看男女人数
查看每个部门的员工人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据，只是个小窍门，但是不能表示所有的情况，看上面第三个分组，没有'每'字，这个就需要我们通过语句来自行判断分组依据了
我们能用id进行分组吗，能，但是id是不是重复度很低啊，基本没有重复啊，对不对，这样的字段适合做分组的依据吗？不适合，对不对，依据性别分组行不行，当然行，因为性别我们知道，是不是就两种啊，也可能有三种是吧，这个重复度很高，对不对，分组来查的时候才有更好的意义
16.1.2 分组的注意事项
-
分组一定发生在where条件之后，先要进行where条件参训将满足查询条件的行记录过滤出来之后，在进行分组。
-
如果使用了分组，那么select可选取的字段只能是分组的字段，或者是通过聚合函数（group_concat 内置函数）获取的其他的字段。
-
在查询语句里面select 字段 from 表，这几项是必须要有的，其他的什么where、group by等等都是可有可无的。
-
分组的简单测试
# 查看employee表中所有的岗位 select post from employee group by post; # 测试分组之后select可选择的字段只能是分组依赖的字段 如果是选组其他的字段，（在only_full_group_by的模式下，会报错）而不再这种模式下，选出来的结果也是有问题的。 mysql> select post,name from employee group by post; +-----------+--------+ | post | name | +-----------+--------+ | CEO | 太白 | | operation | 张野 | | sale | 歪歪 | | teacher | alex | +-----------+--------+
16.1.3 分组配合聚合函数
group_concat, count, sum, max, min, avg
16.1.3.1 单独测试聚合函数
单独使用聚合函数，默认整张表是一组。select也不可以选取其他的字段。
mysql> select max(salary) from employee;
+-------------+
| max(salary) |
+-------------+
| 1000000.31 |
+-------------+
1 row in set (0.00 sec)
mysql> select name,max(salary) from employee; # 错误的！！！
+--------+-------------+
| name | max(salary) |
+--------+-------------+
| 太白 | 1000000.31 |
+--------+-------------+
1 row in set (0.00 sec)
mysql> select min(age) from employee;
+----------+
| min(age) |
+----------+
| 18 |
+----------+
1 row in set (0.00 sec)
mysql> select count(id) from employee; # 计数，员工的数量 一般计数都用于id字段
+-----------+
| count(id) |
+-----------+
| 18 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(post) from employee;
+-------------+
| count(post) |
+-------------+
| 18 |
+-------------+
1 row in set (0.00 sec)
mysql> select avg(age) from employee; # 所有员工的平均年龄
+----------+
| avg(age) |
+----------+
| 34.0000 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(salary) from employee; # 每个月所有员工的工资总额
+-------------+
| sum(salary) |
+-------------+
| 1167202.24 |
+-------------+
1 row in set (0.00 sec)
16.1.3.2 分组配合聚合函数测试
#1. 获取每个部门的所有员工的名字
mysql> select post,group_concat(name) from employee group by post;
+-----------+---------------------------------------------------------+
| post | group_concat(name) |
+-----------+---------------------------------------------------------+
| CEO | 太白 |
| operation | 程咬铜,程咬银,程咬金,张野,程咬铁 |
| sale | 丫丫,格格,星星,丁丁,歪歪 |
| teacher | jingliyang,成龙,liwenzhou,yuanhao,wupeiqi,alex,元宝 |
+-----------+---------------------------------------------------------+
4 rows in set (0.00 sec)
# 2. 计算每个部门的平均工资。
mysql> select post,avg(salary) from employee group by post;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| CEO | 7300.330000 |
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
+-----------+---------------+
4 rows in set (0.00 sec)
# 查看所有员工的男女生人数。
mysql> select sex,count(id) from employee group by sex;
+--------+-----------+
| sex | count(id) |
+--------+-----------+
| male | 10 |
| female | 8 |
+--------+-----------+
2 rows in set (0.00 sec)
16.1.4 分组配合where条件
先执行where，在执行group by
1. 查看每个部门男生的平均工资。
mysql> select post,avg(salary) from employee where sex='male' group by post;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| CEO | 7300.330000 |
| operation | 16000.043333 |
| teacher | 175650.051667 |
+-----------+---------------+
3 rows in set (0.00 sec)
2. 查询年龄在18~30之间的每个部门的员工的人数。
mysql> select post,count(id) from employee where age between 18 and 30 group by post;
+-----------+-----------+
| post | count(id) |
+-----------+-----------+
| CEO | 1 |
| operation | 5 |
| sale | 3 |
| teacher | 3 |
+-----------+-----------+
4 rows in set (0.00 sec)
补充：设置MySQL的严格模式
sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY
最后一步，重启MySQL服务端。
16.2 having
having也是过滤的意思，他的职责与where 过滤职责相同，但是having的过滤是发生在分组之后。
优先级顺序：where > group by > having
16.2.1 where 与 having的区别
- where过滤发生在分组之前，having过滤发生在分组之后。
- where可以直接使用任何字段进行条件过滤，但是where不能使用聚合函数；having只能使用分组字段或者通过聚合函数使用其他的字段。
- where可以按照需求任意设置，但是having必须基于分组之后，如果sql语句没有分组，则不能使用having。
16.2.2 sql语句测试
# where 可以使用任意字段，但是不能使用聚合函数。
mysql> select * from employee where age > 30;
+----+---------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+---------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
+----+---------+--------+-----+------------+---------+--------------+------------+--------+-----------+
6 rows in set (0.00 sec)
mysql> select * from employee where post = 'teacher';
+----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | 元宝 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
+----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+
7 rows in set (0.00 sec)
mysql> select * from employee where salary > avg(salary);
ERROR 1111 (HY000): Invalid use of group function
# having必须要在分组之后使用
mysql> select name,salary from employee having salary > avg(salary);
ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause
mysql>
# having 只能选取分组的字段，或者是通过聚合函数获取其他的字段
# 获取每个部门的年龄大于30的员工的人数。
mysql> select post,count(id) from employee group by post having age > 30;
ERROR 1054 (42S22): Unknown column 'age' in 'having clause'
# 获取每个部门的平均工资大于5000的部门。
mysql> select post from employee group by post having avg(salary) > 5000;
+-----------+
| post |
+-----------+
| CEO |
| operation |
| teacher |
+-----------+
3 rows in set (0.00 sec)
# 统计各部门年龄在30岁及以上的员工的平均薪资，并且保留平均工资大于10000的部门
各部门 group by
30岁及以上 直接使用字典名过滤只能用where
平均薪资 avg(salary)
保留平均工资大于10000 用having avg(salary) > 10000
select post from employee where age > 30 group by post having avg(salary) > 10000;
分析一下过程：
select post from empoyee where age > 30 group by post having avg(salary) > 10000;
首先执行from找到目标的表，在执行where一条一条按照条件取数据，先拿出第一条满足条件的数据，在交给group by 给其进行一个分组，然后交个having进行条件过滤，第一条数据的平均工资1000000/1，满足条件，再交由select选取部门名称post;在执行下一条，是wusir 年龄满足，交由group by 与第一条的alex合并成一组，再交由having过滤，（1000000+8300）/2 > 10000,将这交给select，select在选取这两条的部门名称…
16.3 去重distinct
-
需要查询出某个字段不重复的记录。
-
只能返回他的目标字段，而无法返回其他字段，distinct 想卸载其他字段后面需要配合聚合函数来写
# 查看员工表部门的总个数。 mysql> select distinct post from employee; +-----------+ | post | +-----------+ | CEO | | teacher | | sale | | operation | +-----------+ 4 rows in set (0.00 sec) mysql> select count(distinct post) from employee; +----------------------+ | count(distinct post) | +----------------------+ | 4 | +----------------------+ 1 row in set (0.00 sec) mysql> select name,count(distinct post) from employee; ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
16.4 排序 order by
排序就是基于整张表或者是通过上面查询出来的最终的表进行进一步排序，可以从大到小，也可以从小到大。
-
测试
# 按照年龄从小到大对员工表进行排序。 mysql> select * from employee order by age; +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 1 | 太白 | male | 18 | 2017-03-01 | CEO | NULL | 7300.33 | 401 | 1 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 7 | 元宝 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | # 按照工资从大到小进行排序。 mysql> select * from employee order by salary desc; +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 7 | 元宝 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 1 | 太白 | male | 18 | 2017-03-01 | CEO | NULL | 7300.33 | 401 | 1 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 18 rows in set (0.00 sec) # 按照年龄从小到大，相同年龄的工资从大到小。 # 通过age salary 两个字段进行排序。 select * from employee order by age asc,salary desc; # 可以对筛选或者分组之后的虚拟表进行排序： # 将员工表所有的男性按照工资的从大小排序。 select * from employee where sex='male' order by salary desc; # 查询每个部门按照首字母进行排序。 select post from employee group by post order by post; +-----------+ | post | +-----------+ | CEO | | operation | | sale | | teacher | +-----------+ 4 rows in set (0.00 sec) mysql> select post from employee group by post order by post desc; +-----------+ | post | +-----------+ | teacher | | sale | | operation | | CEO | +-----------+ 4 rows in set (0.00 sec)
16.5 limit 限制查询条数
limit可以进行优化查询
-
测试
# 取出工资最高的前三位。 mysql> select * from employee order by salary desc limit 3; +----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 7 | 元宝 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | +----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+ 3 rows in set (0.00 sec) # 按照索引去规律查询 mysql> select * from employee limit 0,5; +----+-----------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 1 | 太白 | male | 18 | 2017-03-01 | CEO | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-----------+------+-----+------------+---------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec) mysql> select * from employee limit 5,5; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | 元宝 | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec)
16.6 正则表达式查询
之前我们用like做模糊匹配，只有%和_ ， 局限性比较强，所以我们说一个正则，之前我们是不是学过正则匹配，你之前学的正则表达式都可以用，正则是通用的
select * from employee where name regexp '^ale';
mysql> select * from employee where name regexp 'ou$';
+----+-----------+------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+------+-----+------------+---------+--------------+---------+--------+-----------+
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-----------+------+-----+------------+---------+--------------+---------+--------+-----------+
1 row in set (0.00 sec)
16.7 关键字的查询顺序
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
17. 多表查询
17.1 介绍
首先说一下，我们写项目一般都会建一个数据库，那数据库里面是不是存了好多张表啊，不可能把所有的数据都放到一张表里面，肯定要分表来存数据，这样节省空间，数据的组织结构更清晰，解耦和程度更高，但是这些表本质上是不是还是一个整体啊，是一个项目所有的数据，那既然分表存了，就要涉及到多个表连接查询了，比如说员工信息一张表，部门信息一张表，那如果我想让你帮我查一下技术部门有哪些员工的姓名，你怎么办，单独找员工表能实现吗，不能，单独找部门表也无法实现，因为部门表里面没有员工的信息，对不对，所以就涉及到部门表和员工表来关联到一起进行查询了，好，那我们来建立这么两张表：
# 这两张表虽然是多对一，但是没有建立外键约束，只能形成逻辑上的多对一。
# 每一张表都有一条记录，与另一张表对应不上。
mysql> create table department(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> create table employee(
-> id int primary key auto_increment,
-> name varchar(20),
-> sex enum('male','female') not null default 'male',
-> age int,
-> dep_id int
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into department values
-> (200,'技术'),
-> (201,'人力资源'),
-> (202,'销售'),
-> (203,'运营');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into employee(name,sex,age,dep_id) values
-> ('egon','male',18,200),
-> ('alex','female',48,201),
-> ('wupeiqi','male',38,201),
-> ('yuanhao','female',28,202),
-> ('liwenzhou','male',18,200),
-> ('jingliyang','female',18,204)
-> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)
17.2 多表连接查询
17.2.1 交叉连接
不适用任何匹配条件，生成笛卡尔积。
mysql> select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
24 rows in set (0.01 sec)
mysql> select * from department,employee;
+------+--------------+----+------------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+------------+--------+------+--------+
| 200 | 技术 | 1 | egon | male | 18 | 200 |
| 201 | 人力资源 | 1 | egon | male | 18 | 200 |
| 202 | 销售 | 1 | egon | male | 18 | 200 |
| 203 | 运营 | 1 | egon | male | 18 | 200 |
| 200 | 技术 | 2 | alex | female | 48 | 201 |
| 201 | 人力资源 | 2 | alex | female | 48 | 201 |
| 202 | 销售 | 2 | alex | female | 48 | 201 |
| 203 | 运营 | 2 | alex | female | 48 | 201 |
| 200 | 技术 | 3 | wupeiqi | male | 38 | 201 |
| 201 | 人力资源 | 3 | wupeiqi | male | 38 | 201 |
| 202 | 销售 | 3 | wupeiqi | male | 38 | 201 |
| 203 | 运营 | 3 | wupeiqi | male | 38 | 201 |
| 200 | 技术 | 4 | yuanhao | female | 28 | 202 |
| 201 | 人力资源 | 4 | yuanhao | female | 28 | 202 |
| 202 | 销售 | 4 | yuanhao | female | 28 | 202 |
| 203 | 运营 | 4 | yuanhao | female | 28 | 202 |
| 200 | 技术 | 5 | liwenzhou | male | 18 | 200 |
| 201 | 人力资源 | 5 | liwenzhou | male | 18 | 200 |
| 202 | 销售 | 5 | liwenzhou | male | 18 | 200 |
| 203 | 运营 | 5 | liwenzhou | male | 18 | 200 |
| 200 | 技术 | 6 | jingliyang | female | 18 | 204 |
| 201 | 人力资源 | 6 | jingliyang | female | 18 | 204 |
| 202 | 销售 | 6 | jingliyang | female | 18 | 204 |
| 203 | 运营 | 6 | jingliyang | female | 18 | 204 |
+------+--------------+----+------------+--------+------+--------+
24 rows in set (0.00 sec)
mysql> select count(id) from empoloyee,department;
ERROR 1146 (42S02): Table 'nb_db.empoloyee' doesn't exist
mysql> select count(id) from employee,department;
ERROR 1052 (23000): Column 'id' in field list is ambiguous
mysql> select count(employee.id) from employee,department;
+--------------------+
| count(employee.id) |
+--------------------+
| 24 |
+--------------------+
1 row in set (0.00 sec)
# 最终形成了24条记录，这就是笛卡尔积。
咱们为了更好的管理数据，为了节省空间，为了数据组织结构更清晰，将数据拆分到了不同表里面，但是本质上是不是还是一份数据，一份重复内容很多的很大的数据，所以我们即便是分表了，但是咱们是不是还需要找到一个方案把两个本来分开的表能够合并到一起来进行查询，那你是不是就可以根据部门找员工，根据员工找部门了，对不对，但是我们合并两个表的时候，如何合并，根据什么来合并，通过笛卡儿积这种合并有没有浪费，我们其实想做的是不是说我们的员工表中dep_id这个字段中的数据和部门表里面的id能够对应上就可以了，因为我们知道我们设计表的时候，是通过这两个字段来给两个表建立关系的，对不对，看下图：
mysql> select * from employee,department where department.id = employee.dep_id;
+----+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.01 sec)
# 这张表就是内连接
17.2.2 内连接
内连接： 只连接匹配的行
表1 inner join 表2 on 表1.字段 = 表2.字段
mysql> select * from employee,department where department.id = employee.dep_id;
+----+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.01 sec)
# 这个代码有一个问题，员工表对不上部门表的那条数据，还有部门表对不上员工表的那条数据丢失了。
# 需求：
再看一个需求，我要查出技术部的员工的名字。
select name from employee,department where department.id = employee.dep_id and name = '技术';
ERROR 1052 (23000): Column 'name' in field list is ambiguous
# 由于我们交叉连接形成的表有两个name字段，所以我们在选取或者比较name字段时产生了歧义。
select employee.name from employee,department where department.id = employee.dep_id and department.name = '技术';
分析一下这条语句：
select employee.name from employee,department where department.id = employee.dep_id and department.name = '技术';
employee,department where department.id = employee.dep_id 这条语句是一个内连接的连表操作
department.name = '技术' 这个才是真正的条件语句
所以，上面的内连接的sql语句逻辑不清晰，代码的可读性差，连表操作就应该是有连表的语句，条件查询才需要where…MySQL对内连接的连表操作提供了专门的语句。
select employee.name
from
employee inner join department
on department.id = employee.dep_id
where department.name = '技术';
mysql> select * from employee inner join department on employee.dep_id = department.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-----------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
+----+-----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
mysql> select employee.name from employee inner join department on employee.dep_id = department.id where department.name = '技术';
+-----------+
| name |
+-----------+
| egon |
| liwenzhou |
+-----------+
2 rows in set (0.00 sec)
select employee.id,employee.name as e_name,department.name as d_name from employee inner join department on employee.dep_id = department.id;
# 获取销售部所有员工。
select employee.id,employee.name as e_name,department.name as d_name from employee inner join department on employee.dep_id = department.id where department.name = '销售';
17.2.3 外连接
语法：
表1 left|right join 表2 on 表1.字段 = 表2.字段
-
外连接之左连接
#以左表为准，即找出所有员工信息，当然包括没有部门的员工 #本质就是：在内连接的基础上增加左边有右边没有的结果 mysql> select * from employee left join department on employee.dep_id = department.id ; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | +----+------------+--------+------+--------+------+--------------+ -
右连接
#以右表为准，即找出所有部门信息，包括没有员工的部门 #本质就是：在内连接的基础上增加右边有左边没有的结果 select * from employee right join department on employee.dep_id = department.id ; +------+-----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+-----------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec) -
全外连接：显示左表右表都有的记录。
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 #注意：mysql不支持全外连接 full JOIN #强调：mysql可以使用此种方式间接实现全外连接 select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id ; -
课上测试题
#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门 select employee.id,employee.name as ename,department.name as dname from employee inner join department on employee.dep_id = department.id where employee.age > 25; -
小结
- 连表查询最常用的就是inner join，无论内连接还是外连接都是基于交叉连接的基础实现的。
- 如果需要通过连表查询进行查询的操作：首先第一步，先连表，形成了虚拟表之后，还可以在连表…
- 表多连接的组织结构
- 交叉连接
- 内连接
- 外连接
- 左连接
- 右连接
- 全外连接
17.3 子查询
17.3.1 什么叫子查询？
对一张表进行sql语句的查询得到一张虚拟表，这张虚拟表用括号括起来，作为另一条sql语句的查询条件继续查询。
举例说明：
查询技术部所有的员工的姓名。
-
用连表操作解决
先连表，在对虚拟表进行过滤筛选查询。
mysql> select employee.name from employee inner join department on employee.dep_id = department.id where department.name='技术'; +-----------+ | name | +-----------+ | egon | | liwenzhou | +-----------+ -
用子查询解决
先分析需求：
先要通过department表查询技术部的id，然后将id作为查询条件在查询employee表中dep_id = id 的员工的姓名。
select id from department where name='技术'; #那我们把上面的查询结果用括号括起来，它就表示一条id=200的数据，然后我们通过员工表来查询dep_id=这条数据作为条件来查询员工的name select name from employee where dep_id = (select id from department where name='技术'); 上面这些就是子查询的一个思路，解决一个问题，再解决另外一个问题，你子查询里面可不可以是多个表的查询结果，当然可以，然后再通过这个结果作为依据来进行过滤，然后我们学一下子查询里面其他的内容，往下学。
17.3.2 子查询查询方法
-
子查询的解释：子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以为外层查询语句提供查询条件。
-
子查询的查询方法有：比较运算符，= 、 !=、> 、<等：还有：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
-
带in的关键字查询
需求：查询员工平均年龄在25岁以上的部门名，进一步翻译：每个部门的员工的平均年龄在25岁以上的部门名。
# 连表查询 select department.name from employee inner join department on employee.dep_id = department.id group by department.name having avg(age) > 25; # 子查询 需求分析：要想知道部门名称，就要知道目标部门的id，要想知道id，就需要通过employee这张表筛选中满足条件的dep_id,满足什么条件？ 每个部门的员工的平均年龄在25岁以上。 mysql> select * from employee; +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ # 第一步： select dep_id from employee group by dep_id having avg(age) > 25; # 第二步： select name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); -
比较运算符的子查询
查询大于所有人平均年龄的员工名与年龄
# 子查询 # 思路： 先要查询出所有员工的平均年龄，然后在进行sql语句的比较查询。 # 第一步： select avg(age) from employee; # 第二步：select name,age from employee where age > (select avg(age) from employee);查询大于部门内平均年龄的员工名、年龄
# 子查询配合连表查询 # 1. 查询每个部门的平均年龄 形成一张虚拟表 select dep_id, avg(age) from employee group by dep_id; # 2 查询employee表中的员工名，年龄 select name,age,dep_id from employee; # 3. 连表查询 select t1.name,t1.age from (select name,age,dep_id from employee) as t1 inner join (select dep_id, avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age; 优化 起别名用的as 可以省略不写 select t1.name,t1.age from (select name,age,dep_id from employee) t1 inner join (select dep_id, avg(age) avg_age from employee group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age; -
带exists关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。还可以写not exists，和exists的效果就是反的mysql> select * from employee where exists (select id from department where name = '开发'); Empty set (0.00 sec) mysql> select * from employee where not exists (select id from department where name = '开发'); +----+------------+--------+------+--------+ | id | name | sex | age | dep_id | +----+------------+--------+------+--------+ | 1 | egon | male | 18 | 200 | | 2 | alex | female | 48 | 201 | | 3 | wupeiqi | male | 38 | 201 | | 4 | yuanhao | female | 28 | 202 | | 5 | liwenzhou | male | 18 | 200 | | 6 | jingliyang | female | 18 | 204 | +----+------------+--------+------+--------+ 6 rows in set (0.00 sec) -
总结
- 子查询是对一张表进行查询得到的结果是一个虚拟表，可以作为另一个sql语句的查询条件再次查询。
- 以后遇到的连表的问题一般都是子查询配合连表查询共同查询。
扫一扫
8万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
