python-数据库开发(MySQL)
1，初识数据库
1，数据库管理软件的由来
① 程序所有的组件就不可能运行在一台机器上
因为这台机器一旦挂掉则意味着整个软件的崩溃，并且程序的执行效率依赖于承载它的硬件，而一台机器的性能总归是有限的，
受限于目前的硬件水平，就一台机器的性能垂直进行扩展是有极限的。
于是我们只能通过水平扩展来增强我们系统的整体性能，这就需要我们将程序的各个组件分布于多台机器去执行。
②数据安全问题
根据1的描述，我们将程序的各个组件分布到各台机器，但需知各组件仍然是一个整体，言外之意，所有组件的数据还是要共享的。
但每台机器上的组件都只能操作本机的文件，这就导致了数据必然不一致。
于是我们想到了将数据与应用程序分离：把文件存放于一台机器，然后将多台机器通过网络去访问这台机器上的文件（用socket实现），
即共享这台机器上的文件,共享则意味着竞争，会发生数据不安全，需要加锁处理。。。。
③并发
根据2的描述，我们必须写一个socket服务端来管理这台机器（数据库服务器）上的文件，然后写一个socket客户端，完成如下功能：
1.远程连接（支持并发）
2.打开文件
3.读写（加锁）
4.关闭文件
总结：我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），于是有人将此类程序写成一个专门的处理软件，这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题，
总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写。
2，数据库概述
①什么是数据（Data）
描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机
在计算机中描述一个事物，就需要抽取这一事物的典型特征，组成一条记录，就相当于文件里的一行内容，如：
xiong,male,18,1997,山西,计算机系,2018,coolboy
单纯的一条记录并没有任何意义，如果我们按逗号作为分隔，依次定义各个字段的意思，相当于定义表的标题
name,sex,age,birth,born_addr,major,entrance_time,school #字段
xiong,male,18,1997,山西,计算机系,2018,coolboy#记录
这样我们就可以了解xiong，性别为男，年龄18岁，出生于1997年，出生地为山西，2017年考入coolboy计算机系
②什么是数据库（DataBase，简称DB）
数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的
过去人们将数据存放在文件柜里，现在数据量庞大，已经不再适用
数据库是长期存放在计算机内、有组织、可共享的数据即可。
数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种 用户共享
③什么是数据库管理系统（DataBase Management System 简称DBMS）
在了解了Data与DB的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键
这就用到了一个系统软件---数据库管理系统
如MySQL、Oracle、SQLite、Access、MS SQL Server
mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。
④数据库服务器、数据管理系统、数据库、表与记录的关系（重点理解！！！）
记录：事物一系列典型的特征：xiong，male，18
数据库服务器：运行数据库管理软件的计算机
数据库管理软件：管理-数据库，比如mysql
数据库：即文件夹，用来组织文件/表
表：即文件，用来存放多行内容/多条记录
3，下载安装
linux版本
#二进制rpm包安装 yum -y install mysql-server mysql
1.解压tar包 cd /software tar -xzvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz mv mysql-5.6.21-linux-glibc2.5-x86_64 mysql-5.6.21 2.添加用户与组 groupadd mysql useradd -r -g mysql mysql chown -R mysql:mysql mysql-5.6.21 3.安装数据库 su mysql cd mysql-5.6.21/scripts ./mysql_install_db --user=mysql --basedir=/software/mysql-5.6.21 --datadir=/software/mysql-5.6.21/data 4.配置文件 cd /software/mysql-5.6.21/support-files cp my-default.cnf /etc/my.cnf cp mysql.server /etc/init.d/mysql vim /etc/init.d/mysql #若mysql的安装目录是/usr/local/mysql,则可省略此步 修改文件中的两个变更值 basedir=/software/mysql-5.6.21 datadir=/software/mysql-5.6.21/data 5.配置环境变量 vim /etc/profile export MYSQL_HOME="/software/mysql-5.6.21" export PATH="$PATH:$MYSQL_HOME/bin" source /etc/profile 6.添加自启动服务 chkconfig --add mysql chkconfig mysql on 7.启动mysql service mysql start 8.登录mysql及改密码与配置远程访问 mysqladmin -u root password 'your_password' #修改root用户密码 mysql -u root -p #登录mysql,需要输入密码 mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION; #允许root用户远程访问 mysql>FLUSH PRIVILEGES; #刷新权限 源码安装mysql 源码安装mysql
1. 解压 tar zxvf mariadb-5.5.31-linux-x86_64.tar.gz mv mariadb-5.5.31-linux-x86_64 /usr/local/mysql //必需这样，很多脚本或可执行程序都会直接访问这个目录 2. 权限 groupadd mysql //增加 mysql 属组 useradd -g mysql mysql //增加 mysql 用户 并归于mysql 属组 chown mysql:mysql -Rf /usr/local/mysql // 设置 mysql 目录的用户及用户组归属。 chmod +x -Rf /usr/local/mysql //赐予可执行权限 3. 拷贝配置文件 cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf //复制默认mysql配置 文件到/etc目录 4. 初始化 /usr/local/mysql/scripts/mysql_install_db --user=mysql //初始化数据库 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql //复制mysql服务程序 到系统目录 chkconfig mysql on //添加mysql 至系统服务并设置为开机启动 service mysql start //启动mysql 5. 环境变量配置 vim /etc/profile //编辑profile,将mysql的可执行路径加入系统PATH export PATH=/usr/local/mysql/bin:$PATH source /etc/profile //使PATH生效。 6. 账号密码 mysqladmin -u root password 'yourpassword' //设定root账号及密码 mysql -u root -p //使用root用户登录mysql use mysql //切换至mysql数据库。 select user,host,password from user; //查看系统权限 drop user ''@'localhost'; //删除不安全的账户 drop user root@'::1'; drop user root@127.0.0.1; select user,host,password from user; //再次查看系统权限，确保不安全的账户均被删除。 flush privileges; //刷新权限 7. 一些必要的初始配置 1）修改字符集为UTF8 vi /etc/my.cnf 在[client]下面添加 default-character-set = utf8 在[mysqld]下面添加 character_set_server = utf8 2）增加错误日志 vi /etc/my.cnf 在[mysqld]下面添加： log-error = /usr/local/mysql/log/error.log general-log-file = /usr/local/mysql/log/mysql.log 3) 设置为不区分大小写，linux下默认会区分大小写。 vi /etc/my.cnf 在[mysqld]下面添加： lower_case_table_name=1 修改完重启：#service mysql restart 源码安装mariadb
windows版本
#1、下载：MySQL Community Server 5.7.16 http://dev.mysql.com/downloads/mysql/ #2、解压 如果想要让MySQL安装在指定目录，那么就将解压后的文件夹移动到指定目录，如：C:mysql-5.7.16-winx64 #3、添加环境变量 【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】--》【在第二个内容框中找到 变量名为Path 的一行，双击】 --> 【将MySQL的bin目录路径追加到变值值中，用 ； 分割】 #4、初始化 mysqld --initialize-insecure #5、启动MySQL服务 mysqld # 启动MySQL服务 #6、启动MySQL客户端并连接MySQL服务 mysql -u root -p # 连接MySQL服务器 安装
上一步解决了一些问题，但不够彻底，因为在执行【mysqd】启动MySQL服务器时，当前终端会被hang住，那么做一下设置即可解决此问题： 注意：--install前，必须用mysql启动命令的绝对路径 # 制作MySQL的Windows服务，在终端执行此命令： "c:mysql-5.7.16-winx64inmysqld" --install # 移除MySQL的Windows服务，在终端执行此命令： "c:mysql-5.7.16-winx64inmysqld" --remove 注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令： # 启动MySQL服务 net start mysql # 关闭MySQL服务 net stop mysql 将MySQL服务制作成windows服务 将MySQL服务制作成windows服务
4，MySQL的密码设置与破解
1，select user(); 查看当前的账号
2，进入用户，进入cmd，然后输入mysql -uroot -p123456(这里-u后面直接输入用户名，root是默认用户，默认用户是没有密码，直接回车可以跳过)
3，如何设置管理员密码，root默认是管理员。进入cmd，mysqladmin -uroot -p password "123456" （-u指定用户，-p指定密码，这里root没有密码，直接空格就可以了,password后面带个引号，里面加上自己想改的密码）
4，启动和关闭服务(前提：以管理员的方式启动cmd)
开启MySQL：net start MySQL
关闭MySQL：net stop MySQL
5，跳过权限表登录mysql（管理员，同时需要关闭mysql）
mysqld --skip-grant-tables
6，修改密码
update mysql.user set password=password("") where user="root" and host="localhost"
password后面括号的引号里面可以填入自己想修改的密码，不填则为空，user="root"表示给root用户修改密码，host=localhost代表的是一个本地账号
7，刷新授权表（权限）
flush privileges;
8，windows中杀死进程的方法
首先利用 tasklist | findstr mysql 命令找到相关的程序进程号
taskkill /F /PID 6956 这时的6956则为相应的程序号（每台电脑不一样）
9，mysql服务端默认ip为 127.0.0.1 ，默认端口为3306
musql -uroot -p123 -h 127.0.0.1 -P 3306
5，统一字符编码
强调：配置文件中的注释可以有中文，但是配置项中不能出现中文
1 #在mysql的解压目录下，新建my.ini,然后配置 2 #1. 在执行mysqld命令时，下列配置会生效，即mysql服务启动时生效 3 [mysqld] 4 ;skip-grant-tables 5 port=3306 6 character_set_server=utf8 7 default-storage-engine=innodb 8 innodb_file_per_table=1 9 10 11 #解压的目录 12 basedir=E:mysql-5.7.19-winx64 13 #data目录 14 datadir=E:my_data #在mysqld --initialize时，就会将初始数据存入此处指定的目录，在初始化之后，启动mysql时，就会<br>去这个目录里找数据 15 16 17 #2. 针对客户端命令的全局配置，当mysql客户端命令执行时，下列配置生效 18 [client] 19 port=3306 20 default-character-set=utf8 21 user=root 22 password=123 23 24 #3. 只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置 25 [mysql] 26 ;port=3306 27 ;default-character-set=utf8 28 user=egon 29 password=4573 30 31 32 #！！！如果没有[mysql],则用户在执行mysql命令时的配置以[client]为准
统一字符编码
1 s # 查看mysql字符编码 2 #1. 修改配置文件（5.5之前用如下版本） 3 [mysqld] 4 default-character-set=utf8 5 [client] 6 default-character-set=utf8 7 [mysql] 8 default-character-set=utf8 9 10 #mysql5.5以上：修改方式有所改动 11 [mysqld] # 服务端 12 character-set-server=utf8 13 collation-server=utf8_general_ci 14 [client] # 客户端 15 default-character-set=utf8 16 [mysql] 17 default-character-set=utf8 18 19 #2. 重启服务 20 #3. 查看修改结果： 21 s 22 show variables like '%char%'
6，初识sql语句
有了mysql这个数据库软件，就可以将程序员从对数据的管理中解脱出来，专注于对程序逻辑的编写
mysql服务端软件即mysqld帮我们管理好文件夹以及文件，前提是作为使用者的我们，需要下载mysql的客户端，或者其他模块来连接到mysqld，然后使用mysql软件规定的语法格式去提交自己命令，实现对文件夹或文件的管理。该语法即sql（Structured Query Language 即结构化查询语言）
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。 SQL语言分为3种类型： 1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER 2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT 3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
表的基本操作：
1 SQL语句： 2 3 操作文件夹（库） 4 增 5 create database db1 charset utf8; # 以utf-8的方式建立一个名为db1的数据库 6 查 7 show create database db1; # 查看刚刚建立的那个数据库 8 show databases; # 显示所有的数据库 9 改 10 alter database db1 charset gbk; # 改数据库的字符编码 11 删 12 drop database db1; 13 14 操作文件（表） 15 切换文件夹：use db1; 16 查看当前所在文件夹：select database(); 17 18 增 19 create table t1(id int,name char); # 创建一张表，里面字段id为int，名字为字符类型 20 查 21 show create table t1; # 根据表名查看表结构 22 show tables; # 查看所有的表 23 desc t1; 查看表的另一种方式 24 改 25 alter table t1 modify name char(6); # 改name char的宽度，modify为修改 26 alter table t1 change name NAME char(7); # 修改name的字段名，name>NAME 27 删 28 drop table t1; 29 30 操作文件内容（记录） 31 增 32 insert t1(id,name) values(1,'xiong1'),(2,'xiong2'),(3,'xiong3'); 33 查 34 select id,name from db1.t1; # 查看数据库db1中t1表中对应的id和name的值（有相对和绝对路径） 35 select * from db1.t1; # 查看查看数据库db1中t1表中所有的字段对应的值 36 改 37 update db1.t1 set name='SB'; # 全部修改 38 update db1.t1 set name='xiong' where id=2; # 指定修改 39 删 40 delete from t1; # 将表的所有记录都删除 41 delete from t1 where id=2; # 指定删除记录
2，库操作
1，系统数据库
information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库
2，创建数据库
1，语法(help create database)
CREATE DATABASE 数据库名 charset utf8;
2，数据库命名规则：（和python一样）
可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位
3，数据库相关操作
查看数据库
show databases;
show create database db1;
select database();
选择数据库
USE 数据库名
删除数据库
DROP DATABASE 数据库名;
修改数据库
alter database db1 charset utf8;
使用帮助命令：
help 后面加上自己需要查询的命令，需要注意点的是，中括号里面的代表的可有可无的，其他都是一定要有的
3，表操作
1，存储引擎介绍
①什么是存储引擎
mysql中建立的库===>文件夹
库中建立的表===>文件
现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等
数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方
法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和
操作此表的类型）
在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql
数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据
自己的需要编写自己的存储引擎
SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这 么强的要求 ;有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据的查询。
②mysql支持的存储引擎
show engines; # 查看所有支持的存储引擎 MariaDB [(none)]> show enginesG #查看所有支持的存储引擎 MariaDB [(none)]> show variables like 'storage_engine%'; #查看正在使用的存储引擎
1，InnoDB 存储引擎(mysql默认存储引擎)
支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其 特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。 InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。 InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。 对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。 InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。
2，其他存储引擎
2、MyISAM 存储引擎 不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。 3、NDB 存储引擎 年,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 的数据库应用类型。 4、Memory 存储引擎 正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。 5、Infobright 存储引擎 第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。 6、NTSE 存储引擎 网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。 7、BLACKHOLE 黑洞存储引擎，可以应用于主备复制中的分发主库。 MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。
不同类型的表，在硬盘上面会有不同的体现，对应的群体机制也不一样
③使用存储引擎
建表时指定
create table innodb_t1(id int,name char)engine=innodb;
2，表的增删改查
1，表介绍
表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段。比如id，name，qq，age称为字段，其余的，一行内容称为一条记录。
2，创建表
语法
1 create table 表名( 2 字段名1 类型[(宽度) 约束条件], 3 字段名2 类型[(宽度) 约束条件], 4 字段名3 类型[(宽度) 约束条件] 5 ); 6 7 #注意： 8 1. 在同一张表中，字段名是不能相同 9 2. 宽度和约束条件可选，中括号内的可有可无 10 3. 字段名和类型是必须的
示例
MariaDB [(none)]> create database db1 charset utf8; MariaDB [(none)]> use db1; MariaDB [db1]> create table t1( -> id int, -> name varchar(50), -> sex enum('male','female'), -> age int(3) -> ); MariaDB [db1]> show tables; #查看db1库下所有表名 MariaDB [db1]> desc t1; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ MariaDB [db1]> select id,name,sex,age from t1; # 查看表中对应字段的对应值 Empty set (0.00 sec) MariaDB [db1]> select * from t1; # 查看表中所有字段的对应值 Empty set (0.00 sec) MariaDB [db1]> select id,name from t1; Empty set (0.00 sec)
往表中插入数据
MariaDB [db1]> insert into t1 values -> (1,'xiong',18,'male'), -> (2,'laoxu',81,'female') -> ; MariaDB [db1]> select * from t1; +------+------+------+--------+ | id | name | age | sex | +------+------+------+--------+ | 1 | xiong | 18 | male | | 2 | laoxu | 81 | female | +------+------+------+--------+ MariaDB [db1]> insert into t1(id) values -> (3), -> (4); MariaDB [db1]> select * from t1; +------+------+------+--------+ | id | name | age | sex | +------+------+------+--------+ | 1 | xiong | 18 | male | | 2 | laoxu | 81 | female | | 3 | NULL | NULL | NULL | | 4 | NULL | NULL | NULL | +------+------+------+--------+
注意注意注意：表中的最后一个字段不要加逗号，如果加上了逗号，可以使用 c 进行中止运行
再比如，如果输入一条字符信息，但是缺少一个引号，需要将那个引号补全再加上 c 才能中止运行
错误修正一：最后面以逗号结束，直接加上 c 结束执行 mysql> create table t2(name char(6)); Query OK, 0 rows affected (0.04 sec) mysql> insert into t2 values('xiong'), -> c mysql> 错误修正二：括号中漏了一个逗号呼应，此时加上c不行，还要在c前面补充一个'号后，也就是 'c 即可结束执行 mysql> create table t2(name char(6)); Query OK, 0 rows affected (0.04 sec) mysql> insert into t2 values('xiong); '> c '> 'c
3，查看表结构
1 MariaDB [db1]> describe t1; #查看表结构，可简写为desc 表名 2 +-------+-----------------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +-------+-----------------------+------+-----+---------+-------+ 5 | id | int(11) | YES | | NULL | | 6 | name | varchar(50) | YES | | NULL | | 7 | sex | enum('male','female') | YES | | NULL | | 8 | age | int(3) | YES | | NULL | | 9 +-------+-----------------------+------+-----+---------+-------+ 10 11 12 MariaDB [db1]> show create table t1G; #查看表详细结构，可加G 13 # 当表中因格子出现混乱使用很方便
4，修改表结构
1 语法： 2 1. 修改表名 3 ALTER TABLE 表名 4 RENAME 新表名; 5 6 2. 增加字段 7 ALTER TABLE 表名 8 ADD 字段名 数据类型 [完整性约束条件…], 9 ADD 字段名 数据类型 [完整性约束条件…]; 10 ALTER TABLE 表名 11 ADD 字段名 数据类型 [完整性约束条件…] FIRST; # first表示放置位置 12 ALTER TABLE 表名 13 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 将新字段添加到什么字段后面 14 15 3. 删除字段 16 ALTER TABLE 表名 17 DROP 字段名; 18 19 4. 修改字段 20 ALTER TABLE 表名 21 MODIFY 字段名 数据类型 [完整性约束条件…]; 22 ALTER TABLE 表名 23 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…]; 24 ALTER TABLE 表名 25 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
示范
示例： 1. 修改存储引擎 mysql> alter table service -> engine=innodb; 2. 添加字段 mysql> alter table student10 -> add name varchar(20) not null, -> add age int(3) not null default 22; mysql> alter table student10 -> add stu_num varchar(10) not null after name; //添加name字段之后 mysql> alter table student10 -> add sex enum('male','female') default 'male' first; //添加到最前面 3. 删除字段 mysql> alter table student10 -> drop sex; mysql> alter table service -> drop mac; 4. 修改字段类型modify mysql> alter table student10 -> modify age int(3); mysql> alter table student10 -> modify id int(11) not null primary key auto_increment; //修改为主键 5. 增加约束（针对已有的主键增加auto_increment） mysql> alter table student10 modify id int(11) not null primary key auto_increment; ERROR 1068 (42000): Multiple primary key defined mysql> alter table student10 modify id int(11) not null auto_increment; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 6. 对已经存在的表增加复合主键 mysql> alter table service2 -> add primary key(host_ip,port); 7. 增加主键 mysql> alter table student1 -> modify name varchar(10) not null primary key; 8. 增加主键和自动增长 mysql> alter table student1 -> modify id int not null primary key auto_increment; 9. 删除主键 a. 删除自增约束 mysql> alter table student10 modify id int(11) not null; b. 删除主键 mysql> alter table student10 -> drop primary key;
5，复制表
1 ①复制表结构＋记录 （key不会复制: 主键、外键和索引） 2 mysql> create table new_form01 select * from exit_form; 3 4 ②只复制表结构<br>法一：建立一个条件为假的信息在后面，即可拷贝一个只有表结构的表 5 mysql> select * from exit_form where 1=2; //条件为假，查不到任何记录 6 Empty set (0.00 sec) 7 mysql> create table new_form01 select * from exit_form where 1=2; 8 Query OK, 0 rows affected (0.00 sec) 9 Records: 0 Duplicates: 0 Warnings: 0 10 <br>法二：使用like进行拷贝表结构（不支持数据库的拷贝） 11 mysql> create table t4 like exit_form;
6，删除表
DROP TABLE 表名;
3，数据类型
1，数值类型(整型，浮点型)
①整数类型
整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT（代表的整数范围不一样）
作用：存储年龄，等级，id，各种号码等
注意点：整型无需指定宽度，使用默认的就好了（因为整型不指定存储宽度，只指定显示宽度，而显示宽度又默认指定好了，所以无需指定宽度）其它数值类型指的是存储宽度。
对于整型类型，后面不需要添加宽度，整型类型非常特殊，后面的宽度并不是存储宽度，而是指的是显示宽度，对于所有的这些整型类型，他们在MySQL的存储宽度都已经固定好了，只要是声明了某种整型，字节大小就固定好了，能限制的只能是显示宽度（在查询的时候显示的宽度）。而且比如int无符号类型显示宽度不指定时默认宽度为10，但是实际上它的范围也就是到10。有符号时，加上符号就是11位，因此，对于整型来说，指定的宽度是显示宽度，而显示宽度又默认指定了，所以对于整型来说是没有必要指定宽度的。
语法：
1 ======================================== 2 tinyint[(m)] [unsigned] [zerofill] 3 4 小整数，数据类型用于保存一些范围的整数数值范围： 5 有符号： 6 -128 ～ 127 7 无符号： 8 0 ～ 255 9 10 PS： MySQL中无布尔值，使用tinyint(1)构造。 11 12 ======================================== 13 int[(m)][unsigned][zerofill] 14 15 整数，数据类型用于保存一些范围的整数数值范围： 16 有符号： 17 -2147483648 ～ 2147483647 18 无符号： 19 0 ～ 4294967295 20 21 ======================================== 22 bigint[(m)][unsigned][zerofill] 23 大整数，数据类型用于保存一些范围的整数数值范围： 24 有符号： 25 -9223372036854775808 ～ 9223372036854775807 26 无符号： 27 0 ～ 18446744073709551615
验证：
=========有符号和无符号tinyint========== #tinyint默认为有符号 MariaDB [db1]> create table t1(x tinyint); #默认为有符号，即数字前有正负号 MariaDB [db1]> desc t1; MariaDB [db1]> insert into t1 values -> (-129), -> (-128), -> (127), -> (128); MariaDB [db1]> select * from t1; +------+ | x | +------+ | -128 | #-129存成了-128 | -128 | #有符号，最小值为-128 | 127 | #有符号，最大值127 | 127 | #128存成了127 +------+ #设置无符号tinyint MariaDB [db1]> create table t2(x tinyint unsigned); MariaDB [db1]> insert into t2 values -> (-1), -> (0), -> (255), -> (256); MariaDB [db1]> select * from t2; +------+ | x | +------+ | 0 | -1存成了0 | 0 | #无符号，最小值为0 | 255 | #无符号，最大值为255 | 255 | #256存成了255 +------+ ============有符号和无符号int============= #int默认为有符号 MariaDB [db1]> create table t3(x int); #默认为有符号整数 MariaDB [db1]> insert into t3 values -> (-2147483649), -> (-2147483648), -> (2147483647), -> (2147483648); MariaDB [db1]> select * from t3; +-------------+ | x | +-------------+ | -2147483648 | #-2147483649存成了-2147483648 | -2147483648 | #有符号，最小值为-2147483648 | 2147483647 | #有符号，最大值为2147483647 | 2147483647 | #2147483648存成了2147483647 +-------------+ #设置无符号int MariaDB [db1]> create table t4(x int unsigned); MariaDB [db1]> insert into t4 values -> (-1), -> (0), -> (4294967295), -> (4294967296); MariaDB [db1]> select * from t4; +------------+ | x | +------------+ | 0 | #-1存成了0 | 0 | #无符号，最小值为0 | 4294967295 | #无符号，最大值为4294967295 | 4294967295 | #4294967296存成了4294967295 +------------+ ==============有符号和无符号bigint============= MariaDB [db1]> create table t6(x bigint); MariaDB [db1]> insert into t5 values -> (-9223372036854775809), -> (-9223372036854775808), -> (9223372036854775807), -> (9223372036854775808); MariaDB [db1]> select * from t5; +----------------------+ | x | +----------------------+ | -9223372036854775808 | | -9223372036854775808 | | 9223372036854775807 | | 9223372036854775807 | +----------------------+ MariaDB [db1]> create table t6(x bigint unsigned); MariaDB [db1]> insert into t6 values -> (-1), -> (0), -> (18446744073709551615), -> (18446744073709551616); MariaDB [db1]> select * from t6; +----------------------+ | x | +----------------------+ | 0 | | 0 | | 18446744073709551615 | | 18446744073709551615 | +----------------------+ ======用zerofill测试整数类型的显示宽度============= MariaDB [db1]> create table t7(x int(3) zerofill); MariaDB [db1]> insert into t7 values -> (1), -> (11), -> (111), -> (1111); MariaDB [db1]> select * from t7; +------+ | x | +------+ | 001 | | 011 | | 111 | | 1111 | #超过宽度限制仍然可以存 +------+
不同的整型只是表示显示的范围不同
②浮点型
定点数类型 DEC等同于DECIMAL
浮点类型：FLOAT DOUBLE
作用：存储薪资、身高、体重、体质参数等
语法：
1 ====================================== 2 #FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] # 这里M和D代表指定的宽度，M代表最多整数部分有多少，D代表小数部分最多有多少 3 4 定义： 5 单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30 6 7 有符号： 8 -3.402823466E+38 to -1.175494351E-38, 9 1.175494351E-38 to 3.402823466E+38 10 无符号： 11 1.175494351E-38 to 3.402823466E+38 12 13 14 精确度： 15 **** 随着小数的增多，精度变得不准确 **** 16 17 18 ====================================== 19 #DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 20 21 定义： 22 双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30 23 24 有符号： 25 -1.7976931348623157E+308 to -2.2250738585072014E-308 26 2.2250738585072014E-308 to 1.7976931348623157E+308 27 28 无符号： 29 2.2250738585072014E-308 to 1.7976931348623157E+308 30 31 精确度： 32 ****随着小数的增多，精度比float要高，但也会变得不准确 **** 33 34 ====================================== 35 decimal[(m[,d])] [unsigned] [zerofill] 36 37 定义： 38 准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。 39 40 精确度： 41 **** 随着小数的增多，精度始终准确 **** 42 对于精确数值计算时需要用此类型 43 decaimal能够存储精确值的原因在于其内部按照字符串存储。
验证：
mysql> create table t1(x float(256,31)); ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30. mysql> create table t1(x float(256,30)); ERROR 1439 (42000): Display width out of range for column 'x' (max = 255) mysql> create table t1(x float(255,30)); #建表成功 Query OK, 0 rows affected (0.02 sec) mysql> create table t2(x double(255,30)); #建表成功 Query OK, 0 rows affected (0.02 sec) mysql> create table t3(x decimal(66,31)); ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30. mysql> create table t3(x decimal(66,30)); ERROR 1426 (42000): Too-big precision 66 specified for 'x'. Maximum is 65. mysql> create table t3(x decimal(65,30)); #建表成功 Query OK, 0 rows affected (0.02 sec) mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t1 | | t2 | | t3 | +---------------+ rows in set (0.00 sec) mysql> insert into t1 values(1.1111111111111111111111111111111); #小数点后31个1 Query OK, 1 row affected (0.01 sec) mysql> insert into t2 values(1.1111111111111111111111111111111); Query OK, 1 row affected (0.00 sec) mysql> insert into t3 values(1.1111111111111111111111111111111); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t1; #随着小数的增多，精度开始不准确 +----------------------------------+ | x | +----------------------------------+ | 1.111111164093017600000000000000 | +----------------------------------+ row in set (0.00 sec) mysql> select * from t2; #精度比float要准确点，但随着小数的增多，同样变得不准确 +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111200000000000000 | +----------------------------------+ row in set (0.00 sec) mysql> select * from t3; #精度始终准确,d为30，于是只留了30位小数 +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111111111111111111 | +----------------------------------+ row in set (0.00 sec) 浮点类型验证
2，日期类型
DATE TIME DATETIME TIMESTAMP YEAR
作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等
YEAR YYYY（1901/2155） DATE YYYY-MM-DD（1000-01-01/9999-12-31） TIME HH:MM:SS（'-838:59:59'/'838:59:59'） DATETIME YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y） TIMESTAMP YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
验证
1 ============year=========== 2 MariaDB [db1]> create table t10(born_year year); #无论year指定何种宽度，最后都默认是year(4) 3 MariaDB [db1]> insert into t10 values 4 -> (1900), 5 -> (1901), 6 -> (2155), 7 -> (2156); 8 MariaDB [db1]> select * from t10; 9 +-----------+ 10 | born_year | 11 +-----------+ 12 | 0000 | 13 | 1901 | 14 | 2155 | 15 | 0000 | 16 +-----------+ 17 18 19 ============date,time,datetime=========== 20 MariaDB [db1]> create table t11(d date,t time,dt datetime); 21 MariaDB [db1]> desc t11; 22 +-------+----------+------+-----+---------+-------+ 23 | Field | Type | Null | Key | Default | Extra | 24 +-------+----------+------+-----+---------+-------+ 25 | d | date | YES | | NULL | | 26 | t | time | YES | | NULL | | 27 | dt | datetime | YES | | NULL | | 28 +-------+----------+------+-----+---------+-------+ 29 30 MariaDB [db1]> insert into t11 values(now(),now(),now()); 31 MariaDB [db1]> select * from t11; 32 +------------+----------+---------------------+ 33 | d | t | dt | 34 +------------+----------+---------------------+ 35 | 2017-07-25 | 16:26:54 | 2017-07-25 16:26:54 | 36 +------------+----------+---------------------+ 37 38 39 40 ============timestamp=========== 41 MariaDB [db1]> create table t12(time timestamp); 42 MariaDB [db1]> insert into t12 values(); 43 MariaDB [db1]> insert into t12 values(null); 44 MariaDB [db1]> select * from t12; 45 +---------------------+ 46 | time | 47 +---------------------+ 48 | 2017-07-25 16:29:17 | 49 | 2017-07-25 16:30:01 | 50 +---------------------+ 51 52 53 54 ============注意啦，注意啦，注意啦=========== 55 1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入 56 2. 插入年份时，尽量使用4位值 57 3. 插入两位年份时，<=69，以20开头，比如50, 结果2050 58 >=70，以19开头，比如71，结果1971 59 MariaDB [db1]> create table t12(y year); 60 MariaDB [db1]> insert into t12 values 61 -> (50), 62 -> (71); 63 MariaDB [db1]> select * from t12; 64 +------+ 65 | y | 66 +------+ 67 | 2050 | 68 | 1971 | 69 +------+ 70 71 ============综合练习=========== 72 MariaDB [db1]> create table student( 73 -> id int, 74 -> name varchar(20), 75 -> born_year year, 76 -> birth date, 77 -> class_time time, 78 -> reg_time datetime); 79 80 MariaDB [db1]> insert into student values 81 -> (1,'alex',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11"), 82 -> (2,'egon',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"), 83 -> (3,'wsb',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13"); 84 85 MariaDB [db1]> select * from student; 86 +------+------+-----------+------------+------------+---------------------+ 87 | id | name | born_year | birth | class_time | reg_time | 88 +------+------+-----------+------------+------------+---------------------+ 89 | 1 | alex | 1995 | 1995-11-11 | 11:11:11 | 2017-11-11 11:11:11 | 90 | 2 | egon | 1997 | 1997-12-12 | 12:12:12 | 2017-12-12 12:12:12 | 91 | 3 | wsb | 1998 | 1998-01-01 | 13:13:13 | 2017-01-01 13:13:13 | 92 +------+------+-----------+------------+------------+---------------------+
datetime与timestamp的区别
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。 下面就来总结一下两种日期类型的区别。 1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。 2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器， 操作系统以及客户端连接都有时区的设置。 3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。 4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP）， 如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。
3，字符类型
官网：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 = 'PAD_CHAR_TO_FULL_LENGTH'; # 设置MySQL的参数，使得取出完整的长度）。
就比如存的帅哥加上后面三个字符，一共五个字符，存的时候是五个字符，会给你补全空格，取的时候只会取出帅哥，查出的结果会自动删除尾部的空格，得到的就只有两个字符了。
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的前缀，(由于varchar的变长，造成实际上不知道多少字符才构成一个字段，因此需要该前缀来反映数据的真实性)该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
检索：
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
总结：
对于MySQL来说，无论是char还是varchar，在取的时候时候只会按照值进行比较，不会管默认有多少空格，因此取出的时候尾部空格就自动删除了。因此，所查字段尾部有空格，MySQL还是会忽略该空格，对查询结果无影响。但是对于其他地方的空格，MySQL不相同具备此功能。like查询会保留空格查询。
因为varchar需要存一个前缀，所以后面存的时候就会多出 1-2 bytes 来表示字符数
测试前了解两个函数
length：查看字节数
char_length:查看字符数
1. char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形
1 mysql> create table t1(x char(5),y varchar(5)); 2 Query OK, 0 rows affected (0.26 sec) 3 4 #char存5个字符，而varchar存4个字符 5 mysql> insert into t1 values('你瞅啥 ','你瞅啥 '); 6 Query OK, 1 row affected (0.05 sec) 7 8 mysql> SET sql_mode=''; 9 Query OK, 0 rows affected, 1 warning (0.00 sec) 10 11 #在检索时char很不要脸地将自己浪费的2个字符给删掉了，装的好像自己没浪费过空间一样，而varchar很老实，存了多少，就显示多少 12 mysql> select x,char_length(x),y,char_length(y) from t1; # 利用函数char_length可以得到该字符的长度 13 +-----------+----------------+------------+----------------+ 14 | x | char_length(x) | y | char_length(y) | 15 +-----------+----------------+------------+----------------+ 16 | 你瞅啥 | 3 | 你瞅啥 | 4 | 17 +-----------+----------------+------------+----------------+ 18 row in set (0.00 sec) 19 20 #略施小计，让char现出原形 21 mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; # 设置MySQL的参数，使得取出完整的长度 22 Query OK, 0 rows affected (0.00 sec) 23 24 #这下子char原形毕露了...... 25 mysql> select x,char_length(x),y,char_length(y) from t1; 26 +-------------+----------------+------------+----------------+ 27 | x | char_length(x) | y | char_length(y) | 28 +-------------+----------------+------------+----------------+ 29 | 你瞅啥 | 5 | 你瞅啥 | 4 | 30 +-------------+----------------+------------+----------------+ 31 row in set (0.00 sec) 32 33 34 #char类型：3个中文字符+2个空格=11Bytes 35 #varchar类型:3个中文字符+1个空格=10Bytes 36 mysql> select x,length(x),y,length(y) from t1; 37 +-------------+-----------+------------+-----------+ 38 | x | length(x) | y | length(y) | 39 +-------------+-----------+------------+-----------+ 40 | 你瞅啥 | 11 | 你瞅啥 | 10 | 41 +-------------+-----------+------------+-----------+ 42 row in set (0.00 sec)
2. 虽然 CHAR 和 VARCHAR 的存储方式不太相同,但是对于两个字符串的比较,都只比 较其值,忽略 CHAR 值存在的右填充,即使将 SQL _MODE 设置为 PAD_CHAR_TO_FULL_ LENGTH 也一样,但这不适用于like
1 Values in CHAR and VARCHAR columns are sorted and compared according to the character set collation<br> assigned to the column. 2 3 All MySQL collations are of type PAD SPACE. This means that all CHAR, VARCHAR, and TEXT values are <br>compared without regard to any trailing spaces. “Comparison” in this context does not include the <br>LIKE pattern-matching operator, for which trailing spaces are significant. For example: 4 5 mysql> CREATE TABLE names (myname CHAR(10)); 6 Query OK, 0 rows affected (0.03 sec) 7 8 mysql> INSERT INTO names VALUES ('Monty'); 9 Query OK, 1 row affected (0.00 sec) 10 11 mysql> SELECT myname = 'Monty', myname = 'Monty ' FROM names; 12 +------------------+--------------------+ 13 | myname = 'Monty' | myname = 'Monty ' | 14 +------------------+--------------------+ 15 | 1 | 1 | 16 +------------------+--------------------+ 17 row in set (0.00 sec) 18 19 mysql> SELECT myname LIKE 'Monty', myname LIKE 'Monty ' FROM names; 20 +---------------------+-----------------------+ 21 | myname LIKE 'Monty' | myname LIKE 'Monty ' | 22 +---------------------+-----------------------+ 23 | 1 | 0 | 24 +---------------------+-----------------------+ 25 row in set (0.00 sec) 26 3. 总结 27 28 #常用字符串系列：char与varchar 29 注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar<br>处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡 30 31 #其他字符串系列（效率：char>varchar>text） 32 TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT 33 BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB 34 BINARY系列 BINARY VARBINARY 35 36 text：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。 37 mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters. 38 longtext：A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters
4，枚举类型与集合类型
字段的值只能在给定范围中选择，如单选框，多选框
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）
1 mysql> create table t3( 2 -> name varchar(50), 3 -> sex enum('male','female'), 4 -> level enum('vip1','vip2','vip3','vip4','vip5'), 5 -> hobby set('play','music','read','study') 6 -> ); 7 Query OK, 0 rows affected (0.06 sec) 8 9 mysql> insert into t3 values 10 -> ('xiong','male','vip5','music,read,study'), 11 -> ('laoxu','female','vip1','play'); 12 Query OK, 2 rows affected (0.02 sec) 13 Records: 2 Duplicates: 0 Warnings: 0 14 15 mysql> select * from t3; 16 +-------+--------+-------+------------------+ 17 | name | sex | level | hobby | 18 +-------+--------+-------+------------------+ 19 | xiong | male | vip5 | music,read,study | 20 | laoxu | female | vip1 | play | 21 +-------+--------+-------+------------------+ 22 2 rows in set (0.01 sec)
4，完整性约束
1 PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录 2 FOREIGN KEY (FK) 标识该字段为该表的外键 3 NOT NULL 标识该字段不能为空 4 UNIQUE KEY (UK) 标识该字段的值是唯一的 5 AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键） 6 DEFAULT 为该字段设置默认值 7 8 UNSIGNED 无符号 9 ZEROFILL 使用0填充
说明：
1 1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值 2 2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 3 sex enum('male','female') not null default 'male' 4 age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20 5 3. 是否是key 6 主键 primary key 7 外键 foreign key 8 索引 (index,unique...)
②not null与default
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table t1(
id int,
name char(6),
sex enum('male','female') not null default 'male' # 这里则是设置了性别不为空，为空时则设置默认值为male
);
1 ==================not null==================== 2 mysql> create table t1(id int); # id字段默认可以插入空 3 mysql> desc t1; 4 +-------+---------+------+-----+---------+-------+ 5 | Field | Type | Null | Key | Default | Extra | 6 +-------+---------+------+-----+---------+-------+ 7 | id | int(11) | YES | | NULL | | 8 +-------+---------+------+-----+---------+-------+ 9 mysql> insert into t1 values(); #可以插入空 10 11 12 mysql> create table t2(id int not null); #设置字段id不为空 13 mysql> desc t2; 14 +-------+---------+------+-----+---------+-------+ 15 | Field | Type | Null | Key | Default | Extra | 16 +-------+---------+------+-----+---------+-------+ 17 | id | int(11) | NO | | NULL | | 18 +-------+---------+------+-----+---------+-------+ 19 mysql> insert into t2 values(); #不能插入空 20 ERROR 1364 (HY000): Field 'id' doesn't have a default value 21 22 23 24 ==================default==================== 25 #设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值 26 mysql> create table t3(id int default 1); 27 mysql> alter table t3 modify id int not null default 1; 28 29 30 31 ==================综合练习==================== 32 mysql> create table student( 33 -> name varchar(20) not null, 34 -> age int(3) unsigned not null default 18, 35 -> sex enum('male','female') default 'male', 36 -> hobby set('play','study','read','music') default 'play,music' 37 -> ); 38 mysql> desc student; 39 +-------+------------------------------------+------+-----+------------+-------+ 40 | Field | Type | Null | Key | Default | Extra | 41 +-------+------------------------------------+------+-----+------------+-------+ 42 | name | varchar(20) | NO | | NULL | | 43 | age | int(3) unsigned | NO | | 18 | | 44 | sex | enum('male','female') | YES | | male | | 45 | hobby | set('play','study','read','music') | YES | | play,music | | 46 +-------+------------------------------------+------+-----+------------+-------+ 47 mysql> insert into student(name) values('egon'); 48 mysql> select * from student; 49 +------+-----+------+------------+ 50 | name | age | sex | hobby | 51 +------+-----+------+------------+ 52 | egon | 18 | male | play,music | 53 +------+-----+------+------------+ 54 55 验证
③unique(唯一)
作用：为表中只能添加一种字符，且不能重复。例如可以在ID或者name下面添加unique，使得添加的字符唯一。
单列唯一
设置某一项内只能设置唯一一个字段，不能重复设置。
1 ============单列唯一，设置唯一约束 UNIQUE=============== 2 方法一：直接在添加的字段后面添加unique 3 create table department1( 4 id int, 5 name varchar(20) unique, 6 comment varchar(100) 7 ); 8 9 10 方法二：可以在后面统一添加unique 11 mysql> create table department2( 12 -> id int, 13 -> name char(10), 14 -> unique(id), 15 -> unique(name) 16 -> ); 17 Query OK, 0 rows affected (0.04 sec) 18 19 mysql> desc department2; 20 +-------+----------+------+-----+---------+-------+ 21 | Field | Type | Null | Key | Default | Extra | 22 +-------+----------+------+-----+---------+-------+ 23 | id | int(11) | YES | UNI | NULL | | 24 | name | char(10) | YES | UNI | NULL | | 25 +-------+----------+------+-----+---------+-------+ 26 2 rows in set (0.01 sec) 27 28 mysql> insert into department1 values(1,'IT','技术'); 29 Query OK, 1 row affected (0.00 sec) 30 mysql> insert into department1 values(1,'IT','技术'); 31 ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
not null+unique的化学反应
1 mysql> create table t1(id int not null unique); 2 Query OK, 0 rows affected (0.02 sec) 3 4 mysql> desc t1; 5 +-------+---------+------+-----+---------+-------+ 6 | Field | Type | Null | Key | Default | Extra | 7 +-------+---------+------+-----+---------+-------+ 8 | id | int(11) | NO | PRI | NULL | | 9 +-------+---------+------+-----+---------+-------+ 10 row in set (0.00 sec) 11 12 not null+unique的化学反应
联合唯一
设置只要几项加起来不是相同即可，但是同时也可设置单列唯一。
1 create table service( 2 id int primary key auto_increment, 3 name varchar(20), 4 host varchar(15) not null, 5 port int not null,<br>unique(id), # 单列唯一 6 unique(host,port) # 联合唯一 7 ); 8 9 mysql> insert into service values 10 -> (1,'nginx','192.168.0.10',80), 11 -> (2,'haproxy','192.168.0.20',80), 12 -> (3,'mysql','192.168.0.30',3306) 13 -> ; 14 Query OK, 3 rows affected (0.01 sec) 15 Records: 3 Duplicates: 0 Warnings: 0 16 17 mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80); 18 ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
④primary key(主键)
primary key字段的值不为空且唯一（not null unique），在默认存储引擎为innodb的表中，一张表内必须有一个主键
一个表中可以：
单列做主键
多列做主键（复合主键）
但一个表内只能有一个主键primary key
单列主键：
通常是id设置为主键
1 ============单列做主键=============== 2 #方法一：not null+unique 3 create table department1( 4 id int not null unique, #主键 5 name varchar(20) not null unique, 6 comment varchar(100) 7 ); 8 9 mysql> desc department1; 10 +---------+--------------+------+-----+---------+-------+ 11 | Field | Type | Null | Key | Default | Extra | 12 +---------+--------------+------+-----+---------+-------+ 13 | id | int(11) | NO | PRI | NULL | | 14 | name | varchar(20) | NO | UNI | NULL | | 15 | comment | varchar(100) | YES | | NULL | | 16 +---------+--------------+------+-----+---------+-------+ 17 rows in set (0.01 sec) 18 19 #方法二：在某一个字段后用primary key 20 create table department2( 21 id int primary key, #主键 22 name varchar(20), 23 comment varchar(100) 24 ); 25 26 mysql> desc department2; 27 +---------+--------------+------+-----+---------+-------+ 28 | Field | Type | Null | Key | Default | Extra | 29 +---------+--------------+------+-----+---------+-------+ 30 | id | int(11) | NO | PRI | NULL | | 31 | name | varchar(20) | YES | | NULL | | 32 | comment | varchar(100) | YES | | NULL | | 33 +---------+--------------+------+-----+---------+-------+ 34 rows in set (0.00 sec) 35 36 #方法三：在所有字段后单独定义primary key 37 create table department3( 38 id int, 39 name varchar(20), 40 comment varchar(100), 41 constraint pk_name primary key(id); #创建主键并为其命名pk_name 42 43 mysql> desc department3; 44 +---------+--------------+------+-----+---------+-------+ 45 | Field | Type | Null | Key | Default | Extra | 46 +---------+--------------+------+-----+---------+-------+ 47 | id | int(11) | NO | PRI | NULL | | 48 | name | varchar(20) | YES | | NULL | | 49 | comment | varchar(100) | YES | | NULL | | 50 +---------+--------------+------+-----+---------+-------+ 51 rows in set (0.01 sec)
多列主键：
1 ==================多列做主键================ 2 create table service( 3 ip varchar(15), 4 port char(5), 5 service_name varchar(10) not null, 6 primary key(ip,port) 7 ); 8 9 mysql> desc service; 10 +--------------+-------------+------+-----+---------+-------+ 11 | Field | Type | Null | Key | Default | Extra | 12 +--------------+-------------+------+-----+---------+-------+ 13 | ip | varchar(15) | NO | PRI | NULL | | 14 | port | char(5) | NO | PRI | NULL | | 15 | service_name | varchar(10) | NO | | NULL | | 16 +--------------+-------------+------+-----+---------+-------+ 17 rows in set (0.00 sec) 18 19 mysql> insert into service values 20 -> ('172.16.45.10','3306','mysqld'), 21 -> ('172.16.45.11','3306','mariadb') 22 -> ; 23 Query OK, 2 rows affected (0.00 sec) 24 Records: 2 Duplicates: 0 Warnings: 0 25 26 mysql> insert into service values ('172.16.45.10','3306','nginx'); 27 ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
⑤auto_increment(约束条件)
约束字段为自动增长，被约束的字段必须同时被key约束
1 #一：不指定id，则自动增长 2 create table student( 3 id int primary key auto_increment, 4 name varchar(20), 5 sex enum('male','female') default 'male' 6 ); 7 8 mysql> desc student; 9 +-------+-----------------------+------+-----+---------+----------------+ 10 | Field | Type | Null | Key | Default | Extra | 11 +-------+-----------------------+------+-----+---------+----------------+ 12 | id | int(11) | NO | PRI | NULL | auto_increment | 13 | name | varchar(20) | YES | | NULL | | 14 | sex | enum('male','female') | YES | | male | | 15 +-------+-----------------------+------+-----+---------+----------------+ 16 mysql> insert into student(name) values 17 -> ('xiong'), 18 -> ('laoxu') 19 -> ; 20 21 mysql> select * from student; 22 +----+------+------+ 23 | id | name | sex | 24 +----+------+------+ 25 | 1 | xiong| male | 26 | 2 | laoxu| male | 27 +----+------+------+ 28 29 30 #二：也可以指定id，直接传入id号。 31 mysql> insert into student values(4,'asb','female'); 32 Query OK, 1 row affected (0.00 sec) 33 34 mysql> insert into student values(7,'wsb','female'); 35 Query OK, 1 row affected (0.00 sec) 36 37 mysql> select * from student; 38 +----+------+--------+ 39 | id | name | sex | 40 +----+------+--------+ 41 | 1 | xiong| male | 42 | 2 | laoxu| male | 43 | 4 | asb | female | 44 | 7 | wsb | female | 45 +----+------+--------+ 46 47 48 #对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长 49 mysql> delete from student; 50 Query OK, 4 rows affected (0.00 sec) 51 52 mysql> select * from student; 53 Empty set (0.00 sec) 54 55 mysql> insert into student(name) values('ysb'); 56 mysql> select * from student; 57 +----+------+------+ 58 | id | name | sex | 59 +----+------+------+ 60 | 8 | ysb | male | 61 +----+------+------+ 62 63 #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它 64 mysql> truncate student; 65 Query OK, 0 rows affected (0.01 sec) 66 67 mysql> insert into student(name) values('xiong'); 68 Query OK, 1 row affected (0.01 sec) 69 70 mysql> select * from student; 71 +----+------+------+ 72 | id | name | sex | 73 +----+------+------+ 74 | 1 | xiong| male | 75 +----+------+------+ 76 row in set (0.00 sec)
约束条件拓展
修改默认步长和起始偏移量
1 #在创建完表后，修改自增字段的起始值 2 mysql> create table student( 3 -> id int primary key auto_increment, 4 -> name varchar(20), 5 -> sex enum('male','female') default 'male' 6 -> ); 7 8 mysql> alter table student auto_increment=3; 9 10 mysql> show create table student; 11 ....... 12 ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 13 14 mysql> insert into student(name) values('qiuma'); 15 Query OK, 1 row affected (0.01 sec) 16 17 mysql> select * from student; 18 +----+------+------+ 19 | id | name | sex | 20 +----+------+------+ 21 | 3 | qiuma| male | 22 +----+------+------+ 23 row in set (0.00 sec) 24 25 mysql> show create table student; 26 ....... 27 ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 28 29 30 #也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外 31 create table student( 32 id int primary key auto_increment, 33 name varchar(20), 34 sex enum('male','female') default 'male' 35 )auto_increment=3; 36 37 38 #设置步长 39 sqlserver：自增步长 40 基于表级别 41 create table t1（ 42 id int primary key auto_increment,）engine=innodb,auto_increment=2;<br> 步长=2 default charset=utf8 <br>mysql自增的步长： show session variables like 'auto_inc%';#基于会话级别
1 set session auth_increment_increment=2 #修改会话级别的步长 <br><br>#基于全局级别的 <br>set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效） <br><br>#！！！注意了注意了注意了！！！ <br>If the value of auto_increment_offset is greater than that of auto_increment_increment, the value
1 of auto_increment_offset is ignored. 2 翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 3 比如：设置auto_increment_offset=3，auto_increment_increment=2 4 5 6 mysql> set global auto_increment_increment=5; # 设置步长 7 Query OK, 0 rows affected (0.00 sec) 8 9 mysql> set global auto_increment_offset=3; # 设置起始偏移量 10 Query OK, 0 rows affected (0.00 sec)
mysql> set session auto_increment=5; # 设置session级别的起始偏移量，只会在该次链接才生效，推出后失效
mysql> show variables like 'auto_incre%'; #需要退出重新登录后才能设置完成 <br>+--------------------------+-------+<br>| Variable_name | Value | <br>+--------------------------+-------+ <br>| auto_increment_increment | 5 | <br>| auto_increment_offset | 3 | <br>+--------------------------+-------+ <br><br>create table student( <br>id int primary key auto_increment, <br>name varchar(20), <br>sex enum('male','female') <br>default 'male' <br>); <br>mysql> insert into student(name) values<br>('egon1'),<br>('egon2'),<br>('egon3'); <br>mysql> select * from student; <br>+----+-------+------+ <br>| id | name | sex | <br>+----+-------+------+ <br>| 3 | egon1 | male | <br>| 8 | egon2 | male | <br>| 13 | egon3 | male | <br>+----+-------+------+ <br>步长increment与起始偏移量offset:<br>auto_increment_increment,auto_increment_offset
auto_increment时增加新内容
清空表：
delete from t1; # 如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。也即不能清空自增的那个id，新增数据会继续沿用原来排序过来的那个id。
delete是用来删除固定范围的一些数据，比如delete from t20 where id = 3;
truncate table t1; # 数据量大，删除速度比上一条快，且直接从零开始，清空表，应该用truncate
⑥foreign key(建立表间关系)
一 ，快速理解foreign key
员工信息表有三个字段：工号 姓名 部门
公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费
解决方法：
我们完全可以定义一个部门表
然后让员工信息表关联该表，如何关联，即foreign key
示范：
1 # 1，建立表关系，先建被关联的表，并且保证被关联的字段唯一<br>#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一 2 create table department( 3 id int primary key, 4 name varchar(20) not null 5 )engine=innodb; 6 <br># 2，再建立关联的表 7 #dpt_id外键，关联父表（department主键id），同步更新，同步删除 8 create table employee( 9 id int primary key, 10 name varchar(20) not null, 11 dpt_id int, 12 constraint fk_name foreign key(dpt_id) references department(id) #外键关联 13 on delete cascade # 增加删除同步权限 14 on update cascade # 增加更新同步权限 15 )engine=innodb; 16 17 18 #先往父表department中插入记录 19 insert into department values 20 (1,'欧德博爱技术有限事业部'), 21 (2,'艾利克斯人力资源部'), 22 (3,'销售部'); 23 24 25 #再往子表employee中插入记录 26 insert into employee values 27 (1,'egon',1), 28 (2,'alex1',2), 29 (3,'alex2',2), 30 (4,'alex3',2), 31 (5,'李坦克',3), 32 (6,'刘飞机',3), 33 (7,'张火箭',3), 34 (8,'林子弹',3), 35 (9,'加特林',3) 36 ; 37 38 39 #删父表department，子表employee中对应的记录跟着删 40 mysql> delete from department where id=3; 41 mysql> select * from employee; 42 +----+-------+--------+ 43 | id | name | dpt_id | 44 +----+-------+--------+ 45 | 1 | egon | 1 | 46 | 2 | alex1 | 2 | 47 | 3 | alex2 | 2 | 48 | 4 | alex3 | 2 | 49 +----+-------+--------+ 50 51 52 #更新父表department，子表employee中对应的记录跟着改 53 mysql> update department set id=22222 where id=2; 54 mysql> select * from employee; 55 +----+-------+--------+ 56 | id | name | dpt_id | 57 +----+-------+--------+ 58 | 1 | egon | 1 | 59 | 3 | alex2 | 22222 | 60 | 4 | alex3 | 22222 | 61 | 5 | alex1 | 22222 | 62 +----+-------+--------+
二，如何找出两张表之间的关系
1 分析步骤： 2 #1、先站在左表的角度去找 3 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） 4 5 #2、再站在右表的角度去找 6 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） 7 8 #3、总结： 9 #多对一： 10 如果只有步骤1成立，则是左表多对一右表 11 如果只有步骤2成立，则是右表多对一左表 12 13 #多对多 14 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 15 16 #一对一: 17 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础<br>上，将左表的外键字段设置成unique即可
三，建立表之间的关系
实际上自己建立表关系的时候不要建立这种表之间的关系，易搞混乱，下面是一个实例
1 #一对多或称为多对一 2 三张表：出版社，作者信息，书 3 4 一对多（或多对一）：一个出版社可以出版多本书 5 6 关联方式：foreign key
建立多对一的关系（press为一，book为多）
1 ===================== 多对一 ===================== 2 create table press( 3 id int primary key auto_increment, 4 name varchar(20) 5 ); 6 7 create table book( 8 id int primary key auto_increment, 9 name varchar(20), 10 press_id int not null, 11 foreign key(press_id) references press(id) 12 on delete cascade 13 on update cascade 14 ); 15 16 17 insert into press(name) values 18 ('北京工业地雷出版社'), 19 ('人民音乐不好听出版社'), 20 ('知识产权没有用出版社') 21 ; 22 23 insert into book(name,press_id) values 24 ('九阳神功',1), 25 ('九阴真经',2), 26 ('九阴白骨爪',2), 27 ('独孤九剑',3), 28 ('降龙十巴掌',2), 29 ('葵花宝典',3) 30 ;
一夫多妻制
1 一夫多妻制 2 3 #妻子表的丈夫id外键到丈夫表的id
多对多
1 #多对多 2 三张表：出版社，作者信息，书 3 4 多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多 5 6 关联方式：foreign key+一张新的表
建立多对多的关系
1 =====================多对多===================== 2 create table author( 3 id int primary key auto_increment, 4 name varchar(20) 5 ); 6 7 8 #这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了 9 create table author2book( 10 id int not null unique auto_increment, 11 author_id int not null, 12 book_id int not null, 13 constraint fk_author foreign key(author_id) references author(id) # constraint fk_author 为foreign key取个名字 14 on delete cascade 15 on update cascade, 16 constraint fk_book foreign key(book_id) references book(id) 17 on delete cascade 18 on update cascade, 19 primary key(author_id,book_id) 20 ); 21 22 23 #插入四个作者，id依次排开 24 insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq'); 25 26 #每个作者与自己的代表作如下 27 egon: 28 九阳神功 29 九阴真经 30 九阴白骨爪 31 独孤九剑 32 降龙十巴掌 33 葵花宝典 34 alex: 35 九阳神功 36 葵花宝典 37 yuanhao: 38 独孤九剑 39 降龙十巴掌 40 葵花宝典 41 wpq: 42 九阳神功 43 44 45 insert into author2book(author_id,book_id) values 46 (1,1), 47 (1,2), 48 (1,3), 49 (1,4), 50 (1,5), 51 (1,6), 52 (2,1), 53 (2,6), 54 (3,4), 55 (3,5), 56 (3,6), 57 (4,1) 58 ; 59 单张表：用户表+相亲关系表，相当于：用户表+相亲关系表+用户表 60 多张表：用户表+用户与主机关系表+主机表 61 62 中间那一张存放关系的表，对外关联的字段可以联合唯一
一对一 ，里面的foreign key里面一定要加上unique，确保一对一且唯一
1 两张表：学生表和客户表 2 3 一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系 4 5 关联方式：foreign key+unique 一对一且不重复 6 #一定是student来foreign key表customer，这样就保证了： 7 #1 学生一定是一个客户， 8 #2 客户不一定是学生，但有可能成为一个学生 9 10 11 create table customer( 12 id int primary key auto_increment, 13 name varchar(20) not null, 14 qq varchar(10) not null, 15 phone char(16) not null 16 ); 17 18 create table student( 19 id int primary key auto_increment, 20 class_name varchar(20) not null, 21 customer_id int unique, #该字段一定要是唯一的 22 foreign key(customer_id) references customer(id) #外键的字段一定要保证unique 23 on delete cascade 24 on update cascade 25 ); 26 27 #增加客户 28 insert into customer(name,qq,phone) values 29 ('李飞机','31811231',13811341220), 30 ('王大炮','123123123',15213146809), 31 ('守榴弹','283818181',1867141331), 32 ('吴坦克','283818181',1851143312), 33 ('赢火箭','888818181',1861243314), 34 ('战地雷','112312312',18811431230) 35 ; 36 37 38 #增加学生 39 insert into student(class_name,customer_id) values 40 ('脱产3班',3), 41 ('周末19期',4), 42 ('周末19期',5) 43 ;
实例一：
1 例一：一个用户只有一个博客 2 3 用户表： 4 id name 5 egon 6 alex 7 wupeiqi 8 9 10 博客表 11 fk+unique 12 id url name_id 13 xxxx 1 14 yyyy 3 15 zzz 2 16 17 18 19 例二：一个管理员唯一对应一个用户 20 用户表： 21 id user password 22 egon xxxx 23 alex yyyy 24 25 管理员表： 26 fk+unique 27 id user_id password 28 1 xxxxx 29 2 yyyyy
实例二：
账号信息表，用户组，主机表，主机组
1 #用户表 2 create table user( 3 id int not null unique auto_increment, 4 username varchar(20) not null, 5 password varchar(50) not null, 6 primary key(username,password) 7 ); 8 9 insert into user(username,password) values 10 ('root','123'), 11 ('egon','456'), 12 ('alex','alex3714') 13 ; 14 15 16 #用户组表 17 create table usergroup( 18 id int primary key auto_increment, 19 groupname varchar(20) not null unique 20 ); 21 22 insert into usergroup(groupname) values 23 ('IT'), 24 ('Sale'), 25 ('Finance'), 26 ('boss') 27 ; 28 29 30 #主机表 31 create table host( 32 id int primary key auto_increment, 33 ip char(15) not null unique default '127.0.0.1' 34 ); 35 36 insert into host(ip) values 37 ('172.16.45.2'), 38 ('172.16.31.10'), 39 ('172.16.45.3'), 40 ('172.16.31.11'), 41 ('172.10.45.3'), 42 ('172.10.45.4'), 43 ('172.10.45.5'), 44 ('192.168.1.20'), 45 ('192.168.1.21'), 46 ('192.168.1.22'), 47 ('192.168.2.23'), 48 ('192.168.2.223'), 49 ('192.168.2.24'), 50 ('192.168.3.22'), 51 ('192.168.3.23'), 52 ('192.168.3.24') 53 ; 54 55 56 #业务线表 57 create table business( 58 id int primary key auto_increment, 59 business varchar(20) not null unique 60 ); 61 insert into business(business) values 62 ('轻松贷'), 63 ('随便花'), 64 ('大富翁'), 65 ('穷一生') 66 ; 67 68 69 #建关系：user与usergroup 70 71 create table user2usergroup( 72 id int not null unique auto_increment, 73 user_id int not null, 74 group_id int not null, 75 primary key(user_id,group_id), 76 foreign key(user_id) references user(id), 77 foreign key(group_id) references usergroup(id) 78 ); 79 80 insert into user2usergroup(user_id,group_id) values 81 (1,1), 82 (1,2), 83 (1,3), 84 (1,4), 85 (2,3), 86 (2,4), 87 (3,4) 88 ; 89 90 91 92 #建关系：host与business 93 94 create table host2business( 95 id int not null unique auto_increment, 96 host_id int not null, 97 business_id int not null, 98 primary key(host_id,business_id), 99 foreign key(host_id) references host(id), 100 foreign key(business_id) references business(id) 101 ); 102 103 insert into host2business(host_id,business_id) values 104 (1,1), 105 (1,2), 106 (1,3), 107 (2,2), 108 (2,3), 109 (3,4) 110 ; 111 112 #建关系：user与host 113 114 create table user2host( 115 id int not null unique auto_increment, 116 user_id int not null, 117 host_id int not null, 118 primary key(user_id,host_id), 119 foreign key(user_id) references user(id), 120 foreign key(host_id) references host(id) 121 ); 122 123 insert into user2host(user_id,host_id) values 124 (1,1), 125 (1,2), 126 (1,3), 127 (1,4), 128 (1,5), 129 (1,6), 130 (1,7), 131 (1,8), 132 (1,9), 133 (1,10), 134 (1,11), 135 (1,12), 136 (1,13), 137 (1,14), 138 (1,15), 139 (1,16), 140 (2,2), 141 (2,3), 142 (2,4), 143 (2,5), 144 (3,10), 145 (3,11), 146 (3,12) 147 ;
4，数据操作
1，数据的增删改查
1，介绍
MySQL数据操作： DML
在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括
1，使用INSERT实现数据的插入
2，UPDATE实现数据的更新
3，使用DELETE实现数据的删除
4，使用SELECT查询数据以及。
2，插入数据INSERT
1 1. 插入完整数据（顺序插入） 2 语法一： 3 INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n); 4 5 语法二： 6 INSERT INTO 表名 VALUES (值1,值2,值3…值n); 7 8 2. 指定字段插入数据 9 语法： 10 INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…); 11 12 3. 插入多条记录 13 语法： 14 INSERT INTO 表名 VALUES 15 (值1,值2,值3…值n), 16 (值1,值2,值3…值n), 17 (值1,值2,值3…值n); 18 19 4. 插入查询结果 20 语法： 21 INSERT INTO 表名(字段1,字段2,字段3…字段n) 22 SELECT (字段1,字段2,字段3…字段n) FROM 表2 23 WHERE …;
3，更新数据UPDATE
1 语法： 2 UPDATE 表名 SET 3 字段1=值1, 4 字段2=值2, 5 WHERE CONDITION; 6 7 示例： 8 UPDATE mysql.user SET password=password(‘123’) 9 where user=’root’ and host=’localhost’;
4，删除数据DELETE
1 语法： 2 DELETE FROM 表名 3 WHERE CONITION; 4 5 示例： 6 DELETE FROM mysql.user 7 WHERE password=’’; 8 9 练习： 10 更新MySQL root用户密码为mysql123 11 删除除从本地登录的root用户以外的所有用户
5，权限管理
1 #授权表 2 user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段 3 db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段 4 tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段 5 columns_priv #该表放行的权限，针对：某一个字段 6 7 #按图解释： 8 user：放行db1，db2及其包含的所有 9 db：放行db1，及其db1包含的所有 10 tables_priv:放行db1.table1，及其该表包含的所有 11 columns_prive:放行db1.table1.column1，只放行该字段
权限相关操作
1 #创建用户 2 create user 'egon'@'1.1.1.1' identified by '123'; 3 create user 'egon'@'192.168.1.%' identified by '123'; 4 create user 'egon'@'%' identified by '123'; 5 6 7 #授权：对文件夹，对文件，对文件某一字段的权限 8 查看帮助：help grant 9 常用权限有：select,update,alter,delete 10 all可以代表除了grant之外的所有权限 11 12 #针对所有库的授权:*.* 13 grant select on *.* to 'egon1'@'localhost' identified by '123'; #只在user表中可以查到egon1用户的select权限被设置为Y 14 15 #针对某一数据库：db1.* 16 grant select on db1.* to 'egon2'@'%' identified by '123'; #只在db表中可以查到egon2用户的select权限被设置为Y 17 18 #针对某一个表：db1.t1 19 grant select on db1.t1 to 'egon3'@'%' identified by '123'; #只在tables_priv表中可以查到egon3用户的select权限 20 21 #针对某一个字段： 22 mysql> select * from t3; 23 +------+-------+------+ 24 | id | name | age | 25 +------+-------+------+ 26 | 1 | egon1 | 18 | 27 | 2 | egon2 | 19 | 28 | 3 | egon3 | 29 | 29 +------+-------+------+ 30 31 grant select (id,name),update (age) on db1.t3 to 'egon4'@'localhost' identified by '123'; 32 #可以在tables_priv和columns_priv中看到相应的权限 33 mysql> select * from tables_priv where user='egon4'G 34 *************************** 1. row *************************** 35 Host: localhost 36 Db: db1 37 User: egon4 38 Table_name: t3 39 Grantor: root@localhost 40 Timestamp: 0000-00-00 00:00:00 41 Table_priv: 42 Column_priv: Select,Update 43 row in set (0.00 sec) 44 45 mysql> select * from columns_priv where user='egon4'G 46 *************************** 1. row *************************** 47 Host: localhost 48 Db: db1 49 User: egon4 50 Table_name: t3 51 Column_name: id 52 Timestamp: 0000-00-00 00:00:00 53 Column_priv: Select 54 *************************** 2. row *************************** 55 Host: localhost 56 Db: db1 57 User: egon4 58 Table_name: t3 59 Column_name: name 60 Timestamp: 0000-00-00 00:00:00 61 Column_priv: Select 62 *************************** 3. row *************************** 63 Host: localhost 64 Db: db1 65 User: egon4 66 Table_name: t3 67 Column_name: age 68 Timestamp: 0000-00-00 00:00:00 69 Column_priv: Update 70 rows in set (0.00 sec) 71 72 #删除权限 73 revoke select on db1.* to 'alex'@'%';
2，单表查询
1，简单查询
准备表和记录
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 ('egon','male',18,'20170301','鸣人大使',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), ('jinxin','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
简单查询
#简单查询 SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee; SELECT * FROM employee; SELECT name,salary FROM employee; #避免重复DISTINCT SELECT DISTINCT post FROM employee; #通过四则运算查询 SELECT name, salary*12 FROM employee; SELECT name, salary*12 AS Annual_salary FROM employee; # as后面可以加上salary*12的别名 SELECT name, salary*12 Annual_salary FROM employee; # 或者不加as默认后面的就是别名 #定义显示格式 CONCAT() 函数用于连接字符串 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary,concat('性别：',sex) as sex_info <br> FROM employee; CONCAT_WS() 第一个参数为分隔符，以及后面所有的参数都是用这个分隔符分割 SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee;
小练习
1查出所有员工的名字，薪资,格式为<名字:egon> <薪资:3000> 2查出所有的岗位（去掉重复） 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year select concat('<名字:',name,'> ','<薪资:',salary,'>') from employee; select distinct depart_id from employee; select name,salary*12 annual_salary from employee;
2，WHERE约束
where字句中可以使用：
- 比较运算符：><>= <= <> !=
- between 80 and 100 值在10到20之间
- in(80,90,100) 值是10或20或30
- 模糊匹配：like 'qiuma%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
1 #1:单条件查询 2 SELECT name FROM employee WHERE post='sale'; 3 4 #2:多条件查询 5 SELECT name,salary FROM employee WHERE post='teacher' AND salary>10000; 6 7 #3:关键字BETWEEN AND 8 SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000; 9 10 SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000; 11 12 #4:判断是否为空(NULL)，关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) 13 SELECT name,post_comment FROM employee WHERE post_comment IS NULL; 14 15 SELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL; 16 17 SELECT name,post_comment FROM employee WHERE post_comment=''; 注意''是空字符串，不是null 18 ps： 19 执行 20 update employee set post_comment='' where id=2; 21 再用上条查看，就会有结果了 22 23 #5:关键字IN集合查询 24 SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; 25 26 SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ; 27 28 SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ; 29 30 #6:关键字LIKE模糊查询 31 通配符’%’，表示任意字符 32 SELECT * FROM employee WHERE name LIKE 'eg%'; 33 34 通配符’_’，表示一个字符 35 SELECT * FROM employee WHERE name LIKE 'al__';
小实例：
1. 查看岗位是teacher的员工姓名、年龄 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资 4. 查看岗位描述不为NULL的员工信息 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 select name,age from employee where post = 'teacher'; select name,age from employee where post='teacher' and age > 30; select name,age,salary from employee where post='teacher' and salary between 9000 and 10000; select * from employee where post_comment is not null; select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000); select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000); select name,salary*12 from employee where post='teacher' and name like 'jin%'; where约束小实例
3，GROUP BY：分组查询
1，为何分组？
1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的，因此，如果有where，分组group by是一定放在where后面执行的。
2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等，且分组之后只能取分组的字段，以及每个组聚合结果。
3、为何要分组呢？
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据
4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
2，ONLY_FULL_GROUP_BY
1 #查看MySQL 5.7默认的sql_mode如下： 2 mysql> select @@global.sql_mode; 3 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,<br>NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 4 5 #！！！注意 6 ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，<br>在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。 7 8 9 #设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)： 10 mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,<br>ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 11 12 ！！！SQL_MODE设置！！！ 13 mysql> select @@global.sql_mode; 14 +-------------------+ 15 | @@global.sql_mode | 16 +-------------------+ 17 | | 18 +-------------------+ 19 row in set (0.00 sec) 20 21 mysql> select * from emp group by post; 22 +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ 23 | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | 24 +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ 25 | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | 26 | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | 27 | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | 28 | 1 | egon | male | 18 | 2017-03-01 | 鸣人 | NULL | 7300.33 | 401 | 1 | 29 +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ 30 rows in set (0.00 sec) 31 32 33 #由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的 34 35 mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; 36 Query OK, 0 rows affected (0.00 sec) 37 38 mysql> quit #设置成功后，一定要退出，然后重新登录方可生效 39 Bye 40 41 mysql> use db1; 42 Database changed 43 mysql> select * from emp group by post; #报错 44 ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY 45 mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数 46 +----------------------------+-----------+ 47 | post | count(id) | 48 +----------------------------+-----------+ 49 | operation | 5 | 50 | sale | 5 | 51 | teacher | 7 | 52 | 鸣人 | 1 | 53 +----------------------------+-----------+ 54 rows in set (0.00 sec)
3，GROUP BY
1 单独使用GROUP BY关键字分组 2 SELECT post FROM employee GROUP BY post; 3 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数 4 5 GROUP BY关键字和GROUP_CONCAT()函数一起使用 6 SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名 7 SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post; 8 9 GROUP BY与聚合函数一起使用 10 select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
强调：
如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
没有group by，则默认整体算作一组，此时整体都是一组，能用聚合函数
4，聚合函数
1 #强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组，聚合函数只能用在组里面，所以此时可以用聚合函数 2 3 示例： 4 SELECT COUNT(*) FROM employee; 5 SELECT post,COUNT(id) as emp_count FROM employee group by post; 6 SELECT post,MAX(salary) as emp_count FROM employee group by post; 7 SELECT MIN(salary) FROM employee; 8 SELECT AVG(salary) FROM employee; 9 SELECT SUM(salary) FROM employee; 10 SELECT SUM(salary) FROM employee WHERE depart_id=3;
5，实例：
1. 查询岗位名以及岗位包含的所有员工名字 2. 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 #题1：分组 mysql> select post,group_concat(name) from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | group_concat(name) | +-----------------------------------------+---------------------------------------------------------+ | operation | 张野,程咬金,程咬银,程咬铜,程咬铁 | | sale | 歪歪,丫丫,丁丁,星星,格格 | | teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | | 鸣人 | egon | +-----------------------------------------+---------------------------------------------------------+ #题目2： mysql> select post,count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 鸣人 | 1 | +-----------------------------------------+-----------+ #题目3： mysql> select sex,count(id) from employee group by sex; +--------+-----------+ | sex | count(id) | +--------+-----------+ | male | 10 | | female | 8 | +--------+-----------+ #题目4： mysql> select post,avg(salary) from employee group by post; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 鸣人 | 7300.330000 | +-----------------------------------------+---------------+ #题目5 mysql> select post,max(salary) from employee group by post; +-----------------------------------------+-------------+ | post | max(salary) | +-----------------------------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 鸣人 | 7300.33 | +-----------------------------------------+-------------+ #题目6 mysql> select post,min(salary) from employee group by post; +-----------------------------------------+-------------+ | post | min(salary) | +-----------------------------------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 鸣人 | 7300.33 | +-----------------------------------------+-------------+ #题目七 mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+ 小实例
4，过滤HAVING
HAVING与WHERE不一样的地方在于!!!!!!
执行优先级从高到低：where > group by > having
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
mysql> select @@sql_mode; +--------------------+ | @@sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ row in set (0.00 sec) mysql> select * from emp where salary > 100000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 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 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ row in set (0.00 sec) mysql> select * from emp having salary > 100000; ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause mysql> select post,group_concat(name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段 ERROR 1054 (42S22): Unknown column 'salary' in 'having clause' mysql> select post,group_concat(name) from emp group by post having avg(salary) > 10000; +-----------+-------------------------------------------------------+ | post | group_concat(name) | +-----------+-------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | +-----------+-------------------------------------------------------+ rows in set (0.00 sec) 验证 验证
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 3. 查询各岗位平均薪资大于10000的岗位名、平均工资 4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 #题1： mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2; +-----------------------------------------+--------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+--------------------+-----------+ | 鸣人 | egon | 1 | +-----------------------------------------+--------------------+-----------+ #题目2： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ #题目3： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+
5，查询排序:ORDER BY
1 按单列排序 2 SELECT * FROM employee ORDER BY salary; # 默认是按照升序排序 3 SELECT * FROM employee ORDER BY salary ASC; # asc升序，顺序从小到大 4 SELECT * FROM employee ORDER BY salary DESC; # desc降序，顺序从大到小 5 6 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序 7 SELECT * from employee ORDER BY age ASC,salary DESC; # 先按照年龄升序排，然后再按照薪资降序排
排列顺序
1 找到表:from
2 拿着where指定的约束条件，去文件/表中取出一条条记录
3 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4 将分组的结果进行having过滤
5 执行select
6 去重
7 将结果按条件排序：order by
8 限制结果的显示条数
排列顺序实例
1 select post,count(id) as emp_count from employee 2 where salary > 1000 3 group by post 4 having count(id) > 1 5 order by count(id) desc # 此处不能用emp_count代替count(id),因为是先运行emp_count,再运行count(id) 6 ;
其他实例
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 #题目1 mysql> select * from employee ORDER BY age asc,hire_date desc; #题目2 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ #题目3 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+
6，限制查询的记录数:LIMIT
1 示例： 2 SELECT * FROM employee ORDER BY salary DESC 3 LIMIT 3; #默认初始位置为0 4 5 SELECT * FROM employee ORDER BY salary DESC 6 LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条 7 8 SELECT * FROM employee ORDER BY salary DESC 9 LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
小实例：
1. 分页显示，每页5条 mysql> select * from employee limit 0,5; +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 鸣人 | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 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 | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 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 | jinxin | 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 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ rows in set (0.00 sec) mysql> select * from employee limit 10,5; +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 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 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ rows in set (0.00 sec) 小实例
7，使用正则表达式查询
REGEXP
1 SELECT * FROM employee WHERE name REGEXP '^ale'; 2 3 SELECT * FROM employee WHERE name REGEXP 'on$'; 4 5 SELECT * FROM employee WHERE name REGEXP 'm{2}'; 6 7 8 小结：对字符串匹配的方式 9 WHERE name = 'egon'; 10 WHERE name LIKE 'yua%'; 11 WHERE name REGEXP 'on$';
小练习：
1 查看所有员工中名字是jin开头，n或者g结果的员工信息 2 select * from employee where name regexp '^jin.*[gn]$';
8，总结(顺序，重点)
单表查询的语法顺序与执行顺序总结
①语法顺序
1 SELECT distinct(可加可不加，去重)字段1,字段2... FROM 库，表名 2 WHERE 条件 3 GROUP BY field(分组条件) 4 HAVING 筛选，过滤 5 ORDER BY field 6 LIMIT 限制条数
伪代码：
def select(); file = from('db1','db2') lines = where('id>3',f) group_res=group(lines) having_res = having(group_res) distinct_res = distinct(having_res) order_res = order(distinct_res) res = limit(order_res) print(res) return(res)
②执行顺序
1 重点中的重点：关键字的执行优先级 2 from 3 where 4 group by 5 having 6 select 7 distinct 8 order by 9 limit
3，多表查询
1，连表操作
为了方便管理，我们将数据分到多张表中储存，并在要用的时候将分散的数据进行提取，将有关系的表进行查询，也即连表操作
准备表
2，多表连接查询
1 #重点：外链接语法 2 3 SELECT 字段列表 4 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 5 ON 表1.字段 = 表2.字段;
①交叉连接：不适用任何匹配条件。生成笛卡尔积
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 | 运营 | +----+------------+--------+------+--------+------+--------------+ 交叉连接
②内连接：只连接匹配的行
1 #找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果 2 #department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来 3 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee <br>inner join department on employee.dep_id=department.id; 4 +----+-----------+------+--------+--------------+ 5 | id | name | age | sex | name | 6 +----+-----------+------+--------+--------------+ 7 | 1 | egon | 18 | male | 技术 | 8 | 2 | alex | 48 | female | 人力资源 | 9 | 3 | wupeiqi | 38 | male | 人力资源 | 10 | 4 | yuanhao | 28 | female | 销售 | 11 | 5 | liwenzhou | 18 | male | 技术 | 12 +----+-----------+------+--------+--------------+ 13 14 #上述sql等同于 15 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from <br>employee,department where employee.dep_id=department.id;
③外链接之左连接：优先显示左表全部记录
#以左表为准，即找出所有员工信息，当然包括没有部门的员工 #本质就是：在内连接的基础上增加左边有右边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee left <br>join department on employee.dep_id=department.id; +----+------------+--------------+ | id | name | depart_name | +----+------------+--------------+ | 1 | egon | 技术 | | 5 | liwenzhou | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 6 | jingliyang | NULL | +----+------------+--------------+
④外链接之右连接：优先显示右表全部记录
#以右表为准，即找出所有部门信息，包括没有员工的部门 #本质就是：在内连接的基础上增加右边有左边没有的结果 mysql> select employee.id,employee.name,department.name as depart_name from employee right <br>join department on employee.dep_id=department.id; +------+-----------+--------------+ | id | name | depart_name | +------+-----------+--------------+ | 1 | egon | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 5 | liwenzhou | 技术 | | NULL | NULL | 运营 | +------+-----------+--------------+
⑤全外连接：显示左右两个表全部记录
1 全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 2 #注意：mysql不支持全外连接 full JOIN 3 #强调：mysql可以使用此种方式间接实现全外连接 4 select * from employee left join department on employee.dep_id = department.id 5 union 6 select * from employee right join department on employee.dep_id = department.id 7 ; 8 #查看结果 9 +------+------------+--------+------+--------+------+--------------+ 10 | id | name | sex | age | dep_id | id | name | 11 +------+------------+--------+------+--------+------+--------------+ 12 | 1 | egon | male | 18 | 200 | 200 | 技术 | 13 | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | 14 | 2 | alex | female | 48 | 201 | 201 | 人力资源 | 15 | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | 16 | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | 17 | 6 | jingliyang | female | 18 | 204 | NULL | NULL | 18 | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | 19 +------+------------+--------+------+--------+------+--------------+ 20 21 #注意 union与union all的区别：union会去掉相同的纪录
3，符合条件连接查询
1 #示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门 2 select employee.name,department.name from employee inner join department 3 on employee.dep_id = department.id 4 where age > 25; 5 6 #示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示 7 select employee.id,employee.name,employee.age,department.name from employee,department 8 where employee.dep_id = department.id 9 and age > 25 10 order by age asc;
4，子查询
1 #1：子查询是将一个查询语句嵌套在另一个查询语句中。 2 #2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。 3 #3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 4 #4：还可以包含比较运算符：= 、 !=、> 、<等