数据库----知识点总结

目录

第一章 数据库初识

一, 思考

  1. 为什么要用数据库?
    • 将文件和程序存在一台机器上是很不合理的
    • 操作文件是一件很麻烦的事
  2. 所以就出现了一个新的概念 —— 数据库
    • 数据库是一个可以在一台机器上独立工作的,并且可以给我们提供高效,便捷的方式对数据进行增删改查的一种工具
  3. 数据库的优势
    • 程序稳定性: 这样任意一台服务所在的机器崩溃了都不会影响数据和另外的服务
    • 数据一致性: 所有的数据都存储在一起,所有的程序操作的数据都是统一的,就不会出现数据不一致的现象
    • 并发: 数据库可以良好的支持并发,所有的程序操作数据库都是通过网络,而数据库本身支持并发的网络操作,不需要我们自己写socket
    • 效率: 使用数据库对数据进行增删改查的效率要高出我们自己处理文件很多

二, 认识数据库

  1. 什么是数据
    • 描述事物的符号记录称为数据,描述事物的符号既可以是数字,也可以是文字,图片,图像,声音,语言等,数据由多种表现形式,它们都可以经过数字化后存入计算机
  2. 什么是数据库: DataBase简称DB
    • 数据库即存放数据的仓库,只不过这个仓库是在计算机存储设备上,而且数据是按一定的格式存放的
    • 数据库是长期存放在计算机内,有组织,可共享的数据集合
    • 数据库中的数据按一定的数据模型组织,描述和储存,具有较小的冗余度,较高的数据独立性和易扩展性,并可为各种用户共享
  3. 什么是数据库管理系统 Data Base Management System 简称DBMS
    • 科学地组织和存储数据,高效获取和维护数据
    • 分类:
      • 关系型数据库: 数据之间关系紧密 存取效率相对低
        • 三巨头: mysql oracle sqlserver
      • 非关系型数据库: 数据之间关联不紧密 存取效率相对高 key和value的关系
        • redis mongodb memcache(内存级别)
  4. 数据库服务器,数据库管理系统,数据库,表与记录的关系
    • 记录: 1 孙悟空 13838383838 38 (多个字段的信息组成一条记录,即文件中的一行内容)
    • 表: userinfo,studentinfo,courseinfo(即文件)
    • 数据库: db(即文件夹)
    • 数据库管理系统: 如mysql(是一个软件)
    • 数据库服务器: 一台计算机(对内存要求比较高)
    • 总结:
      • 数据库服务器: 运行数据库管理软件
      • 数据库管理软件: 管理数据库
      • 数据库: 即文件夹,用来组织文件/表
      • 表: 即文件,用来存放多行内容/多条记录

三, 初识mysql

  1. 下载和安装

  2. 初识SQL语句

    • SQL: 结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统
    • SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发.SQL语言分为3种类型:
      • DDL语句,数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER
      • DML语句,数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
      • DCL语句,数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE
    # 进入mysql客户端(CMD窗口)
$ mysql
mysql> select user();  # 查看当前用户
mysql> exit     # 也可以用\q quit退出
# 默认用户登陆之后并没有实际操作的权限
# 需要使用管理员root用户登陆
$ mysql -uroot -p
mysql> set password = password('密码'); # 给当前数据库设置密码

# 创建账号
mysql> create user 'username'@'192.168.13.%'   IDENTIFIED BY '123';# 指示网段
mysql> create user 'username'@'192.168.13.5'   # 指示某机器可以连接
mysql> create user 'usrename'@'%'              # 指示所有机器都可以连接  
mysql> show grants for 'username'@'192.168.13.%';查看某个用户的权限
# 远程登陆
$ mysql -u username -p123 -h 192.168.13.5

# 给账号授权
mysql> grant all on 数据库的名字(*表示所有).表的名字(*表示所有) to 'username'@'%'; 
mysql> grant select on 数据库的名字(*表示所有).表的名字(*表示所有) to 用户名;  给一个已经存在的账号授权
mysql> grant select on 数据库的名字(*表示所有).表的名字(*表示所有) to 'hxbs'@'192.168.13.%'   IDENTIFIED BY '123';  创建账号的同时授权
mysql> flush privileges;    # 刷新使授权立即生效

    1. 操作文件夹（库）
   增：mysql> create database 库名 charset utf8;
   查：mysql> show databases;
   改：mysql> alter database 库名 charset latin1;
   删除: mysql> drop database 库名;
2. 操作文件（表）
   先切换到文件夹下：mysql> use db1
   增：mysql> create table t1(id int,name char(长度单位字符));
   查：mysql> show tables;
   改：mysql> alter table t1 modify name char(3);
       修改字段类型和长度
       mysql>alter table t1 change name name1 char(2);
       修改字段名和长度
       mysql> alter table t1 rename t2;
       修改表名
   删：mysql> drop table t1;
   查看表的结构: mysql> desc 表名;
   查看表的创建语句和配置: mysql> show create table 表名
    

3. 操作文件中的内容（记录）
   增：mysql> insert into t1 values(1, '小马'),(2,'孙悟空'),(3,'猪八戒');
   查：mysql> select * from t1;
   改：mysql> update t1 set name='唐僧'; 修改所有name为唐僧
       mysql> update t1 set name='唐僧' where id=1;
       可以同时修改多个属性
   删：mysql> delete from t1 where id=3;

   清空表：mysql> delete from t1; # 如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始
       mysql> truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，

*mysql> auto_increment 表示：自增
*mysql> primary key 表示：约束（不能重复且不能为空）；加速查找
示例:
mysql> create table t2(id int not null auto_increment primary key,name char(10));

第二章 mysql表操作

一, mysql中的存储引擎

  1. mysql中的数据用各种不同的技术存储在文件(或者内存)中.这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力

  2. 这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)

  3. mysql支持的存储引擎:

    mysql 5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA.其中NDB和InnoDB提供事务安全表,其他存储引擎都是非事务安全表

  4. 各种存储引擎的特性

    并发性: 某些应用程序比其他应用程序具有很多的颗粒级锁定要求(如行级锁定).
事务支持: 并非所有的应用程序都需要事务,但对的确需要事务的应用程序来说,有着定义良好的需求,如ACID兼容等.
引用完整性: 通过DDL定义的外键,服务器需要强制保持关联数据库的引用完整性.
物理存储: 它包括各种各样的事项,从表和索引的总的页大小,到存储数据所需的格式,到物理磁盘.
索引支持: 不同的应用程序倾向于采用不同的索引策略,每种存储引擎通常有自己的编制索引方法,但某些索引方法(如B-tree索引)对几乎所有的存储引擎来说是共同的.
内存高速缓冲: 与其他应用程序相比,不同的应用程序对某些内存高速缓冲策略的响应更好,因此,尽管某些内存高速缓冲对所有存储引擎来说是共同的(如用于用户连接的高速缓冲,MySQL的高速查询高速缓冲等),其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义.
性能帮助: 包括针对并行操作的多I/O线程,线程并发性,数据库检查点,成批插入处理等.
其他目标特性: 可能包括对地理空间操作的支持,对特定数据处理操作的安全限制等.

    以上要求会在不同的需求中予以体现,通过单独一个系统实现是不可能的,以上特点有些本身就是相互矛盾的,鱼和熊掌的问题.对以上内容做些选择,形成的存储引擎就是一个插件引擎了,某些特定的需求可以使用.如下图,部分现有的存储引擎以及基本特点:

    InnoDB:
MySql 5.6 版本默认的存储引擎.InnoDB是一个事务安全(transactions)的存储引擎,保证数据安全和完整性;支持行级锁(row-level locking)和表级锁(table_level locking),提升了它的多用户并发数以及性能;树tree-加速查询(树形结构(数据 + 树) + 表结构),提升查询速度;为了保证数据的完整性,InnoDB还支持外键约束(foreign keys).
MyISAM:
MyISAM既不支持事务、也不支持外键、其优势是访问速度快,但是表级别的锁定限制了它在读写负载方面的性能,因此它经常应用于只读或者以读为主的数据场景.树tree-加速查询(树形结构 + 数据 + 表结构)
Memory:
在内存中存储所有数据,应用于对非关键数据快速查找的场景.Memory类型的表访问数据非常快,因为它的数据是存放在内存中的,并且默认使用HASH索引,但是一旦服务关闭,表中的数据就会丢失

  5. 常用存储引擎及适用场景

    • InnoDB:

      用于事务处理应用程序,支持外键和行级锁.如果应用对事物的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包括很多更新和删除操作,那么InnoDB存储引擎是比较合适的.InnoDB除了有效的降低由删除和更新导致的锁定,还可以确保事务的完整提交和回滚,对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择.(产生两个文件)

    • MyISAM:

      如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不高,那么可以选择这个存储引擎.(产生三个文件)

    • Memory:

      将所有的数据保存在内存中,在需要快速定位记录和其他类似数据的环境下,可以提供极快的访问.Memory的缺陷是对表的大小有限制,虽然数据库因为异常终止的话数据可以正常恢复,但是一旦数据库关闭,存储在内存中的数据都会丢失.(产生一个文件)

  6. 存储引擎在mysql中的使用

    查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";

查询当前数据库支持的存储引擎:
mysql> show engines \G;

  7. 指定存储引擎建表

    # 在建表时指定:
mysql> create table innodb_t(id int,name char(12));
mysql> create table myisam_t(id int,name char(12)) engine=myisam;
mysql> create table memory_t(id int,name char(12)) engine=memory;

# 在配置文件中指定:
# my.ini文件
[mysqld]
default-storage-engine=INNODB

  8. mysql的工作流程

    • 首先,最上层的服务并不是MySQL独有的,大多数基于网络的客户端/服务器的工具或者服务都有类似的架构.比如: 连接处理、授权认证、安全等.
    • 第二层的架构包括大多数的MySQL的核心服务.包括: 查询解析、分析、优化、缓存以及所有的内置函数(例如: 日期、时间、数学和加密函数).同时,所有的跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等.
    • 第三层包含了存储引擎.存储引擎负责MySQL中数据的存储和提取.服务器通过API和存储引擎进行通信.这些接口屏蔽了不同存储引擎之间的差异,使得这些差异对上层的查询过程透明化.存储引擎API包含十几个底层函数,用于执行“开始一个事务”等操作.但存储引擎一般不会去解析SQL(InnoDB会解析外键定义,因为其本身没有实现该功能),不同存储引擎之间也不会相互通信,而只是简单的响应上层的服务器请求.
    • 第四层包含了文件系统,所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上.

二, mysql的数据类型

  1. 表就相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段

  2. 例如:

    id name age sex phone job
    1 孙悟空 500 男 13838383838 和尚
    2 猪八戒 2000 男 18888888888 和尚
    3 沙悟净 2000 男 16666666666 和尚

  3. 创建表

    # 语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

# 注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

    mysql> create database python;
mysql> use python;
mysql> create table staff_info(id int,name char(12),age int(3),sex enum('男','女'),phone bigint(11),job varchar(11));
mysql> show tables;
mysql> desc staff_info;
mysql> insert into staff_info values(1,'猪八戒',18,'女',13838383838,'护士');
mysql> insert into staff_info values(2,'孙悟空',18,'男',16868686868,'院长');
mysql> select * from staff_info;

  4. 查看表结构

    mysql> desc 表名和describe 表名效果相同,可以查看当前的表结构
mysql> show create table 表名;  查看更全面的表定义信息

mysql> desc staff_info;
mysql> show create table staff_info;

  5. 数值类型

    MySQL支持所有标准SQL数值数据类型,这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION).关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词.MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT.下面的表显示了需要的每个整数类型的存储和范围.对于小数的表示,MYSQL分为两种方式：浮点数和定点数.浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种,在mysql中以字符串的形式存放,比浮点数更精确,适合用来表示货币等精度高的数据.

    类型 大小 范围（有符号） 范围（无符号）unsigned约束 用途
    INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
    TINYINT 1 字节 (-128，127) (0，255) 小整数值
    FLOAT 4 字节float(255,30) (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
    DOUBLE 8 字节double(255,30) (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
    DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2double(65,30) 依赖于M和D的值 依赖于M和D的值 小数值 
    # 整数示例  常用int tinyint
# 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table int_t(id1 int,id2 int(5));
mysql> insert into int_t values(1,1);
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
# 那么当我们插入了比宽度更大的值，会发生什么呢？
mysql> insert into int_t values(111111,111111);
+--------+--------+
| id1    | id2    |
+- ------+--------+
|      1 |      1 |
| 111111 | 111111 |
+--------+--------+
# id2仍然显示了正确的数值，没有受到宽度限制的影响
# 修改id1字段 给字段添加一个unsigned表示无符号
mysql> alter table int_t modify id1 int unsigned;
# id1不能为负数,且范围变为(0，4 294 967 295)

    # 小数示例 常用float double decimal
# float(一共多少位,小数点保留的位数)
# float默认五位小数
# decimal的精度最好,默认(10,0)
mysql> create table fd_t(f_t float(5,2),d_t double(5,2),dc_t decimal(5,2));
mysql> insert into fd_t values(1.23,1.23,1.23);
mysql> select * from fd_t;
+------+------+------+
| f_t  | d_t  | dc_t |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
mysql> insert into fd_t values(1.234,1.234,1.234);
+------+------+------+
| f_t  | d_t  | dc_t |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+ # 四舍五入

  6. 日期和时间类型

    表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR.每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值.TIMESTAMP类型有专有的自动更新特性.

    类型 大小 (字节) 范围 格式 用途
    DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 年月日时分秒
    DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 年月日
    TIME 3 '838:59:59'/'838:59:59' HH:MM:SS 时分秒
    YEAR 1 1901/2155 YYYY 年份值
    TIMESTAMP 4 2038年1月19日结束 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳 
    # datetime  date  time 示例
# now() 获取当前时间
mysql> create table time1(d date,t time,dt datetime);
mysql> desc time1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
mysql> insert into time1 values(now(),now(),now());
mysql> select * from time1;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2019-07-30 | 19:36:21 | 2019-07-30 19:36:21 |
+------------+----------+---------------------+
mysql> insert into time1 values(null,null,null);
mysql> select * from time1;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2019-07-30 | 19:36:21 | 2019-07-30 19:36:21 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+

    # timestamp示例
mysql> create table time2(time timestamp);
mysql> desc time2;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| time  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
# 插入数据null,会自动插入当前时间的时间
mysql> insert into time2 values(null);
mysql> select * from time2;
+---------------------+
| time                |
+---------------------+
| 2019-07-30 19:46:50 |
+---------------------+
# 添加一列 默认值是'0000-00-00 00:00:00'
mysql> alter table time2 add time2 timestamp;
mysql> desc time2;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| time  | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| time2 | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+
# 手动修改新的列默认值为当前时间
mysql> alter table time2 modify time2 timestamp default current_timestamp;
mysql> desc time2;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| time  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| time2 | timestamp | NO   |     | CURRENT_TIMESTAMP |                             |
+-------+-----------+------+-----+-------------------+-----------------------------+
mysql> insert into time2 values(null,null);
mysql> select * from time2;
+---------------------+---------------------+
| time                | time2               |
+---------------------+---------------------+
| 2019-07-30 19:46:50 | 0000-00-00 00:00:00 |
| 2019-07-30 20:12:08 | 2019-07-30 20:12:08 |
+---------------------+---------------------+
# timestamp时间的下限是19700101080001
# timestamp时间的上限是20380119111407

    # year示例
mysql> create table time3(y year);
mysql> insert into time3 values(2019);
mysql> select * from time3;
+------+
| y    |
+------+
| 2019 |
+------+

    # datetime示例
mysql> create table time4(dt datetime);
mysql> insert into time4 values('2018-9-26 12:20:10');
mysql> insert into time4 values('20180926122010');
mysql> insert into time4 values(20180926122010);
mysql> select * from time4;
+---------------------+
| dt                  |
+---------------------+
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
+---------------------+
# 使datetime具有timestamp的特性: 非空,默认当前时间,修改其它字段时自动更新
mysql> create table time5(dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
mysql> desc time5;
+-------+----------+------+-----+-------------------+-----------------------------+
| Field | Type     | Null | Key | Default           | Extra                       |
+-------+----------+------+-----+-------------------+-----------------------------+
| dt    | datetime | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+----------+------+-----+-------------------+-----------------------------+
mysql> insert into time5 values(null);
ERROR 1048 (23000): Column 'dt' cannot be null
mysql> insert into time5 values();
mysql> select * from time5;
+---------------------+
| dt                  |
+---------------------+
| 2019-07-30 20:24:15 |
+---------------------+

  7. 字符串类型

    字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

    类型 大小 用途
    CHAR 0-255字符(5.6版本) 定长字符串
    VARCHAR 0-65535字符 变长字符串

    CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同.它们的最大长度和是否尾部空格被保留等方面也不同.在存储或检索过程中不进行大小写转换.CHAR列的长度固定为创建表是声明的长度(自动补空格),范围(0-255);而VARCHAR的值是可变长字符串范围(0-65535).

    # char 定长存储,存储速度更快,占用更多的空间,默认为1
# varchar 变长存储,存储速度相对慢,占用的空间小,不能不设置
# char和varchar示例
mysql> create table char1(c char(4),v varchar(4));
mysql> insert into char1 values('tp ','tp ');
# 在检索的时候char数据类型会去掉空格
mysql> select * from char1;
+------+------+
| c    | v    |
+------+------+
| tp   | tp   |
+------+------+
# 来看看对查询结果计算的长度
mysql> select length(c),length(v) from char1;
+-----------+-----------+
| length(c) | length(v) |
+-----------+-----------+
|         2 |         3 |
+-----------+-----------+
# 给结果拼上一个加号会更清楚
mysql> select concat(c,'+'),concat(v,'+') from char1;
+---------------+---------------+
| concat(c,'+') | concat(v,'+') |
+---------------+---------------+
| tp+           | tp +          |
+---------------+---------------+
# 当存储的长度超出定义的长度，会截断
mysql> insert into char1 values('hahaha','hahaha');
mysql> select * from char1;
+------+------+
| c    | v    |
+------+------+
| tp   | tp   |
| haha | haha |
+------+------+

  8. ENUM和SET类型

    ENUM中文名称叫枚举类型,它的值范围需要在创建表时通过枚举方式显示.ENUM只允许从值集合中选取单个值,而不能一次取多个值.

    SET和ENUM非常相似,也是一个字符串对象,里面可以包含0-64个成员.根据成员的不同,存储上也有所不同.set类型可以允许值集合中任意选择1或多个元素进行组合.对超出范围的内容将不允许注入,而对重复的值将进行自动去重.

    类型 大小 用途
    ENUM 对1-255个成员的枚举需要1个字节存储;对于255-65535个成员，需要2个字节存储;最多允许65535个成员。 单选：选择性别
    SET 1-8个成员的集合，占1个字节9-16个成员的集合，占2个字节17-24个成员的集合，占3个字节25-32个成员的集合，占4个字节33-64个成员的集合，占8个字节 多选：兴趣爱好 
    # enum示例
mysql> create table enum(name char(20),sex enum('男','女'));
# 选择enum('男','女')中的一项作为sex的值,可以正常插入
mysql> insert into enum values('孙悟空','男');
# 不能同时插入'男','女'两个值,也不能插入不属于'男','女'的值
mysql> insert into enum values('猪八戒','男,女');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

# set示例
mysql> create table set1(name char(12),hobby set('抽烟','喝酒','烫头'));
# 可以任意选择set('抽烟','喝酒','烫头')中的项,并自带去重功能
mysql> insert into set1 values('于谦','抽烟,喝酒,烫头,烫头');
mysql> select * from set1;
+--------+----------------------+
| name   | hobby                |
+--------+----------------------+
| 于谦   | 抽烟,喝酒,烫头        |
+--------+----------------------+
# 不能选择不属于set('抽烟','喝酒','烫头')中的项
mysql> insert into set1 values('蔡旭鲲','唱跳,篮球');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

三, mysql表的完整性约束

  1. 为了防止不符合规范的数据进入数据库,在用户对数据进行插入,修改,删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确,有效,相容. 约束条件与数据类型的宽度一样,都是可选参数,主要分为以下几种:

    命令 约束
    UNSIGNED 无符号的:数字
    DEFAULT 默认值
    NOT NULL 非空约束,指定某列不能为空
    UNIQUE 唯一约束,指定某列或者几列组合不能重复
    PRIMARY KEY 主键,指定该列的值可以唯一地标识该列记录
    FOREIGN KEY 外键,指定该行记录从属于主表中的一条记录,主要用于参照完整性
    auto_increment 自增

  2. NOT NULL

    是否可空,null表示空,非字符串
    not null - 不可空
    null - 可空

    mysql> create table nn(id int not null);
mysql> desc nn;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
# 不能向id列插入空元素
mysql> insert into nn values(null);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into nn values(1);
Query OK, 1 row affected (0.01 sec)

  3. DEFAULT

    我们约束某一列不为空,如果这一列中经常有重复的内容,就需要我们频繁的插入,这样会给我们的操作带来新的负担,于是就出现了默认值的概念.默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值

    # not null+default
mysql> create table nd(id1 int not null,id2 int not null default 250);
mysql> desc nd;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   |     | NULL    |       |
| id2   | int(11) | NO   |     | 250     |       |
+-------+---------+------+-----+---------+-------+
# 只向id1字段添加值，会发现id2字段会使用默认值填充
mysql> insert into nd(id1) values(1);
mysql> select * from nd;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 | 250 |
+-----+-----+
# id1字段不能为空,所以不能单独向id2字段填充值
mysql> insert into nd(id2) values(111);
ERROR 1364 (HY000): Field 'id1' doesn't have a default value
# 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值
mysql> insert into nd values(520,521);
mysql> select * from nd;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 | 250 |
| 520 | 521 |
+-----+-----+

    设置严格模式：
    不支持对not null字段插入null值
    不支持对自增长字段插入”值
    不支持text字段有默认值

直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件添加(永久生效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

  4. UNIQUE

    唯一约束,指定某列或者几列组合不能重复

    # 方式一:
mysql> create table un(id int unique,name char(12));
# 方式二:
mysql> create table un2(id int,name char(12),unique(id));
mysql> insert into un values(1,'孙悟空');
mysql> insert into un values(1,'孙悟空');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

    # 唯一加非空  unique + not null
mysql> create table nnun(id int not null unique);
mysql> desc nnun;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
# 如果一张表中没有设置primary key主键,那么第一个设置非空+唯一的字段会被设置成主    键

# 唯一和空的关系,unique能不能重复插入多个null?
# 对于mysql来说,数据与数据之间相等就是重复,但null不能用=判断
# 所以,对于unique来说,可以插入多个空值

    # 联合唯一
create table service(tname
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) # 联合唯一,两者结合起来唯一
)

  5. PRIMARY KEY

    主键为了保证表中的每一条数据的该字段都是表格中的唯一值.换言之,它是用来独一无二地确认一个表格中的每一行数据.主键可以包含一个字段或多个字段.当主键包含多个栏位时,称为组合键 (Composite Key),也可以叫联合主键.主键可以在创建新表格时设定 (运用 CREATE TABLE 语句),或是以改变现有的表格架构方式设定 (运用 ALTER TABLE).主键必须唯一,主键值非空;可以是单一字段,也可以是多字段组合.

    # 单列做主键
# 方法一：not null+unique  第一个会被设为主键
mysql> create table t1(id int not null unique, # 主键
    -> name varchar(20) not null unique,
    -> comment varchar(100));
mysql> desc t1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
# 方法二：在某一个字段后用primary key
mysql> create table t2(id int primary key, # 主键
    -> name varchar(20),comment varchar(100));
mysql> desc t2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
# 方法三：在所有字段后单独定义primary key
mysql> create table t3(id int,
    -> name varchar(20),comment varchar(100),
    -> primary key(id));
mysql> desc t3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | 0       |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
# 方法四：给已经建成的表添加主键约束
mysql> create table t3(id int,
    -> name varchar(20),comment varchar(100),
    -> primary key(id));
mysql> desc t4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
mysql> alter table t4 modify id int primary key;
mysql> desc t4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

    # 联合主键  非空,且联合唯一
mysql> create table t5(
    -> ip varchar(15),
    -> port char(5),
    -> s_name varchar(10) not null,
    -> primary key(ip,port));
mysql> desc t5;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ip     | varchar(15) | NO   | PRI |         |       |
| port   | char(5)     | NO   | PRI |         |       |
| s_name | varchar(10) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
mysql> insert into t5 values('192.168.13.10','8848','QQ'),
    -> ('192.168.13.10','3306','MYSQL');
mysql> select * from t5;
+---------------+------+--------+
| ip            | port | s_name |
+---------------+------+--------+
| 192.168.13.10 | 3306 | MYSQL  |
| 192.168.13.10 | 8848 | QQ     |
+---------------+------+--------+
mysql> insert into t5 values('192.168.13.10','8848','微信');
ERROR 1062 (23000): Duplicate entry '192.168.13.10-8848' for key 'PRIMARY'

  6. AUTO_INCREMENT

    约束字段为自动增长,被约束的字段必须同时被key约束

    1. 只能操作数字
    2. 自带非空属性
    3. 只能对unique字段进行设置
    4. 不受删除影响,内部会记录
    # 不指定id,则自动增长
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20) not null);
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('孙悟空'),('猪八戒'),('沙悟净');
mysql> select * from student;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
|  2 | 猪八戒    |
|  3 | 沙悟净    |
+----+-----------+
# 也可以指定id,不可以为空
# 对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
# 应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它

    # 在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('男','女') default '男'
    -> );
mysql> alter table student auto_increment=3;
mysql> insert into student(name) values('马倩');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  3 | 马倩 |  男  |
+----+------+------+
# 也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('男','女') default '男'
) auto_increment=3;

  7. FOREIGN KEY

    # 外键约束  对应外表中的至少是unique,推荐使用主键作为关联字段
# 表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一
# 必须先创建被关联的表
mysql> create table department(
    -> id int primary key,
    -> name varchar(20) not null
    -> );
# dpt_id外键,关联父表(department主键id),同步更新,同步删除
mysql> create table employee(
    -> id int primary key,
    -> name varchar(20) not null,
    -> dpt_id int,
    -> foreign key(dpt_id)
    -> references department(id)
    -> on delete cascade   # 级联删除
    -> on update cascade); # 级联更新
# 先往父表department中插入记录
mysql> insert into department values
    -> (1,'人事部'),(2,'研发部'),(3,'产品部');
# 再往子表employee中插入记录
mysql> insert into employee values
    -> (1,'孙悟空',1),
    -> (2,'猪八戒',3),
    -> (3,'沙悟净',3),
    -> (4,'唐僧',2);
# 删父表department,子表employee中对应的记录跟着删
# 更新父表department,子表employee中对应的记录跟着改


    on delete    on update
   . cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录 
   . set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null  
   . No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  
   . Restrict方式
同no action, 都是立即检查外键约束

四, 表结构

  1. 修改表结构

    语法：
1. 修改表名
      ALTER TABLE 表名 
                      RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                      ADD 字段名  数据类型 [完整性约束条件…],
                      ADD 字段名  数据类型 [完整性约束条件…];
                            
3. 删除字段
      ALTER TABLE 表名 
                      DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                      MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                      CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

5.修改字段排列顺序/在增加的时候指定字段位置
    ALTER TABLE 表名
                     ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                     ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
    ALTER TABLE 表名
                     CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;
    ALTER TABLE 表名
                     MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

    # alter操作非空和唯一
mysql> create table t1(id int unique,name char(10) not null);
# 去除name字段的非null约束
mysql> alter table t1 modify name char(10) null;
# 添加非null约束
mysql> alter table t1 modify name char(10) not null;
# 去除id字段的unique约束
mysql> alter table t1 drop index id;
# 添加unique约束
mysql> alter table t1 modify id int unique;

    # alter操作主键
mysql> create table t2(id int, name varchar(12));
# 设定id为主键
mysql> alter table t2 add primary key(id);
# 注:在增加主键之前,必须先把反复的id删除掉
# 删除主键
mysql> alter table t2 drop primary key;
# 注意,删除时,因主键产生的not null约束不会删除

    # 为表添加外键
mysql> create table press(
    -> id int primary key,
    -> name char(10) default null
    -> );
mysql> create table book(
    -> id int primary key,
    -> book_name char(12) default null,
    -> press_id int not null
    -> );
# 为book表添加外键
mysql> alter table book add constraint fk_id foreign key(press_id) references press(id);
# 删除外键
mysql> alter table book drop foreign key fk_id;

  2. 删除表

    drop table 表名;

  3. 多表结构的创建和分析

    分析步骤：
# 1,先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id)

# 2,再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id)

# 3,总结：
# 多对一：
如果只有步骤1成立,则是左表多对一右表
如果只有步骤2成立,则是右表多对一左表

# 多对多
如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

# 一对一:
如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然.这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可

    # 一对多或多对一 示例
mysql> create table press(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
mysql> create table book(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> press_id int not null,
    -> foreign key(press_id) references press(id)
    -> on delete cascade
    -> on update cascade
    -> );
mysql> insert into press(name) values
    -> ('北京工业地雷出版社'),
    -> ('人民音乐不好听出版社'),
    -> ('知识产权没有用出版社');
mysql> insert into book(name,press_id) values
    -> ('九阳神功',1),
    -> ('九阴真经',2),
    -> ('九阴白骨爪',2),
    -> ('独孤九剑',3),
    -> ('降龙十巴掌',2),
    -> ('葵花宝典',3);
mysql> select * from book;
+----+-----------------+----------+
| id | name            | press_id |
+----+-----------------+----------+
|  1 | 九阳神功         |        1 |
|  2 | 九阴真经         |        2 |
|  3 | 九阴白骨爪       |        2 |
|  4 | 独孤九剑         |        3 |
|  5 | 降龙十巴掌       |        2 |
|  6 | 葵花宝典         |        3 |
+----+-----------------+----------+

    # 多对多
# 三张表：作者,书,对应关系
# 多对多：一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多
# 关联方式：foreign key+一张新的表
# 作者表
mysql>  create table author(
    ->  id int primary key auto_increment,
    ->  name varchar(20)
    -> );
# 这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
mysql> create table author_book(
    -> id int primary key auto_increment,
    -> author_id int not null,
    -> book_id int not null,
    -> constraint fk_author foreign key(author_id) references author(id)
    -> on delete cascade on update cascade,
    -> constraint fk_book foreign key(book_id) references book(id)
    -> on delete cascade on update cascade,
    -> unique(author_id,book_id)
    -> );

mysql> insert into author(name) values
    -> ('孙悟空'),('猪八戒'),('唐僧'),('沙和尚');
mysql> insert into author_book(author_id,book_id) values
    -> (1,4),(1,2),(1,3),(2,1),(2,6),(3,5),(4,2),(4,6);
    
mysql> select * from book;
+----+-----------------+----------+
| id | name            | press_id |
+----+-----------------+----------+
|  1 | 九阳神功        |        1 |
|  2 | 九阴真经        |        2 |
|  3 | 九阴白骨爪      |        2 |
|  4 | 独孤九剑        |        3 |
|  5 | 降龙十巴掌      |        2 |
|  6 | 葵花宝典        |        3 |
+----+-----------------+----------+
mysql> select * from author;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
|  2 | 猪八戒    |
|  3 | 唐僧      |
|  4 | 沙和尚    |
+----+-----------+
mysql> select * from author_book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  2 |         1 |       2 |
|  3 |         1 |       3 |
|  1 |         1 |       4 |
|  4 |         2 |       1 |
|  5 |         2 |       6 |
|  6 |         3 |       5 |
|  7 |         4 |       2 |
|  8 |         4 |       6 |
+----+-----------+---------+

    # 一对一
# 两张表：用户表和博客表
# 一对一：一个用户只有一个博客
# 关联方式：foreign key+unique
# 用户表
mysql> create table user(
    -> id int primary key auto_increment,
    -> name varchar(12));
mysql> insert into user(name) values
    -> ('孙悟空'),('猪八戒'),('沙悟净');
# 博客表
mysql> create table blog(
    -> id int primary key auto_increment,
    -> url varchar(40) not null unique,
    -> name_id int unique,
    -> foreign key(name_id) references user(id)
    -> on delete cascade on update cascade);
mysql> insert into blog(url,name_id) values
    -> ('www.baidu.com',1),('www.sohu.com',3),('www.taobao.com',2);
mysql> select * from user;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
|  2 | 猪八戒    |
|  3 | 沙悟净    |
+----+-----------+
mysql> select * from blog;
+----+----------------+---------+
| id | url            | name_id |
+----+----------------+---------+
|  1 | www.baidu.com  |       1 |
|  2 | www.sohu.com   |       3 |
|  3 | www.taobao.com |       2 |
+----+----------------+---------+

第三章 mysql记录操作

一, 插入数据insert into

# 插入完整数据(顺序插入)
语法一：
    insert into 表名(字段1,字段2,字段3…字段n) values(值1,值2,值3…值n);
语法二：
    insert into 表名 values (值1,值2,值3…值n);

# 指定字段插入数据
语法：
    insert into 表名(字段1,字段2,字段3…) values (值1,值2,值3…);

# 插入多条记录
语法：
    insert into 表名 values
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);
# 插入查询结果
语法：
    insert into 表名(字段1,字段2,字段3…字段n) 
                   select (字段1,字段2,字段3…字段n) from 表2
                   where …;

二, 更新数据update

语法：
    update 表名 set
        字段1=值1,
        字段2=值2,
        where 条件;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

三, 删除数据delete

语法：
    delete from 表名 
        where 条件;

示例：
    delete from mysql.user 
        where password=’’;

一,单表查询

  1. 单表查询语法

    SELECT DISTINCT 字段1,字段2... FROM 表名
                              WHERE 条件
                              GROUP BY field
                              HAVING 筛选
                              ORDER BY field
                              LIMIT 限制条数

  2. 关键字执行的优先级

    关键字 作用
    from 找到表
    where 拿着where指定的约束条件,去文件/表中取出一条条记录
    group by 将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
    having 将分组的结果进行having过滤
    select distinct 执行select(去重)
    order by 将结果按条件排序：order by
    limit 限制结果的显示条数

  3. 简单查询

    # 示例表的信息
mysql> create table employee(
    -> id int primary key auto_increment,
    -> emp_name varchar(20) not null,
    -> sex enum('男','女') not null default '男', # 大部分是男的
    -> age int(3) unsigned not null default 18,
    -> 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 |
| emp_name     | varchar(20)       | NO   |     | NULL    |                |
| sex          | enum('男','女')   | NO   |     | 男      |                |
| age          | int(3) unsigned   | NO   |     | 18      |                |
| 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    |                |
+--------------+-------------------+------+-----+---------+----------------+
mysql> insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
    -> ('孙悟空','男',18,'20170301','develop',7300,401,1), # 以下是开发部门
    -> ('猪八戒','男',78,'20150302','develop',1000000,401,1),
    -> ('唐僧','男',81,'20130305','develop',8300,401,1),
    -> ('小白龙','男',73,'20140701','develop',3500,401,1),
    -> ('沙悟净','男',28,'20121101','develop',2100,401,1),
    -> ('太上老君','女',18,'20110211','develop',9000,401,1),
    -> ('元始天尊','男',18,'19000301','develop',30000,401,1),
    -> ('通天教主','男',48,'20101111','develop',10000,401,1),
    ->
    -> ('歪歪','女',48,'20150311','sale',3000,402,2),# 以下是销售部门
    -> ('丫丫','女',38,'20101101','sale',2000,402,2),
    -> ('丁丁','女',18,'20110312','sale',1000,402,2),
    -> ('星星','女',18,'20160513','sale',3000,402,2),
    -> ('格格','女',28,'20170127','sale',4000,402,2),
    ->
    -> ('李世民','男',28,'20160311','operation',10000,403,3), # 以下是运营部门
    -> ('程咬金','男',18,'19970312','operation',20000,403,3),
    -> ('程咬银','女',18,'20130311','operation',19000,403,3),
    -> ('程咬铜','男',18,'20150411','operation',18000,403,3),
    -> ('程咬铁','女',18,'20140512','operation',17000,403,3)
    -> ;

    # 简单查询
mysql> select * from employee;
mysql> select emp_name,salary from employee;

# 避免重复DISTINCT
mysql> select distinct post from employee;

# 通过四则运算查询
mysql> select emp_name,salary*12 from employee;

# 定义显示格式  concat() 函数用于连接字符串
mysql> select concat('姓名:',emp_name,'  年薪:',salary*12) as Annual_salary
	# as 新字段名
    -> from employee;
mysql> select concat('姓名:',emp_name,'  年薪:',salary*12) Annual_salary
    -> from employee;  # 不用as也可以
+---------------------------------------+
| Annual_salary                         |
+---------------------------------------+
| 姓名:孙悟空  年薪:87600.00             |
| ...                                   |
+---------------------------------------+
# CONCAT_WS() 第一个参数为连接符
mysql> select concat_ws(':',emp_name,salary*12) as Annual_salary
    -> from employee;
+------------------------+
| Annual_salary          |
+------------------------+
| 孙悟空:87600.00        |
| ...                    |
+------------------------+

# 结合case语句
mysql> select
    -> (  case
    ->    when emp_name='通天教主' then
    ->        emp_name
    ->    when emp_name='孙悟空' then
    ->        concat(emp_name,'齐天大圣')
    ->    else
    ->        concat(emp_name,'打工仔')
    ->    end ) as person
    -> from employee;

  4. where约束

    select 字段名 from 表名 where 条件

    类型 用法
    比较运算符 > < >= <= <> !=
    between 80 and 100 值在80到100之间(包含80,100)
    in(80,90,100) 值是80或90或100
    like 'e%' 通配符可以是%或_,%表示任意多字符, _表示一个字符
    regexp 正则匹配
    is / is not is null / is not null
    逻辑运算符 and or not 
    # 1:单条件查询
mysql> select emp_name from employee
    -> where post='sale';
# 2:多条件查询
mysql> select emp_name,salary from employee
    -> where post='develop' and salary>10000;
# 3:关键字between on
mysql> select emp_name,salary from employee
    -> where salary between 10000 and 20000;
mysql> select emp_name,salary from employee
    -> where salary not between 10000 and 20000;
# 4:关键字IS NULL(判断某个字段是否为NULL不能用等号,需要用IS)
mysql> select emp_name,post_comment from employee
    -> where post_comment is null;
mysql> select emp_name,post_comment from employee
    -> where post_comment is not null;
mysql> select emp_name,post_comment from employee
    -> where post_comment=''; # 注意''是空字符串,不是null
# 5:关键字IN集合查询
mysql> select emp_name,salary from employee
    -> where salary=3000 or salary=3500 or salary=4000 or salary=9000;
mysql> select emp_name,salary from employee
    -> where salary in(3000,3500,4000,9000);
mysql> select emp_name,salary from employee
    -> where salary not in (3000,3500,4000,9000);
# 6:关键字like模糊查询
# 通配符’%’
mysql> select * from employee
    -> where emp_name like('程%');
# 通配符’_’
mysql> select * from employee
    -> where emp_name like('程咬_');

  5. group by分组

    根据某个重复率比较高的字段进行的,这个字段有多少种可能就分成多少组,能够做到去重,一旦分组了就不能对具体某一条数据进行操作了,永远都是考虑这个组的操作.对unique字段进行分组毫无意义.
单独使用group by关键字分组
	select post from employee group by post;
	注意: 我们按照post字段分组,那么select查询的字段只能是post,想要获取组内的其他相关信息,需要借助函数

group_concat(): 只用于做最终的显示,不能作为中间结果操作其他数据

group by与聚合函数一起使用:
	select post,conut(id) as count from employee group by post; 按照岗位分组,并查看每个组有多少人

  6. 聚合函数

    绝大多数情况是和分组一起使用的,如果没有和分组一起使用,那么一整张表就是一组

    聚合函数 作用
    count() 计数:每个组对应几条数据
    max() 求最大值:这个组中某字段的最大值
    min() 求最大值:这个组中某字段的最小值
    avg() 求平均值
    sum() 求和 
    # 示例
# 查询男员工个数与男员工的平均薪资，女员工个数与女员工的平均薪资
select sex,count(id),avg(salary) from employee group by sex;
# 查询岗位名以及各岗位的最高薪资
select post,max(salary) from employee group by post;

  7. having过滤

    • 主要用于对组进行筛选

    • 和where不同在于:

      # 执行优先级从高到低：where > group by > having 
# 1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数
# 2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数

  8. order by查询排序

    按单列排序:
select * from employee order by salary; 默认升序
select * from employee order by salary asc;  升序
select * from employee order by salary desc; 降序

按多列排序: 先按照age排序,如果年纪相同,则按照薪资排序
select * from employee order by age asc,salary desc;

  9. limit限制查询的记录数

     1.显示分页
 2.取前n名,总是跟order by一起用
 示例:
 # 默认初始位置为0
 select * from employee order by salary desc limit 3;
 # 从第m开始,即先查询出第m+1条,然后包含这一条在内往后查n条
 select * from employee order by salary desc limit m,n;
 select * from employee order by salary desc limit n offset m; # 同上,从m+1开始取条

二, 多表查询

一, 连表查询

  1. 示例用表

    # 建表
mysql> create table post(
    -> id int,
    -> name varchar(20)
    -> );
mysql> create table employee(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('男','女') not null default '男',
    -> age int,
    -> post_id int
    -> );
# 插入数据
mysql> insert into post values
    -> (200,'技术'),
    -> (201,'人力资源'),
    -> (202,'销售'),
    -> (203,'运营');
mysql> insert into employee(name,sex,age,post_id) values
    -> ('孙悟空','男',18,200),
    -> ('猪八戒','女',48,201),
    -> ('沙悟净','男',38,201),
    -> ('唐僧','女',28,202),
    -> ('小白龙','男',18,200),
    -> ('哪吒','女',18,204)
    -> ;
# 查看数据
mysql> select * from post;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
mysql> select * from employee;
+----+-----------+-----+------+---------+
| id | name      | sex | age  | post_id |
+----+-----------+-----+------+---------+
|  1 | 孙悟空    | 男  |   18 |     200 |
|  2 | 猪八戒    | 女  |   48 |     201 |
|  3 | 沙悟净    | 男  |   38 |     201 |
|  4 | 唐僧      | 女  |   28 |     202 |
|  5 | 小白龙    | 男  |   18 |     200 |
|  6 | 哪吒      | 女  |   18 |     204 |
+----+-----------+-----+------+---------+

  2. 语法

    select 字段 from 表1 inner/left/right join 表2
		on 表1.字段 = 表2.字段;

  3. 交叉连接: 不适用任何匹配条件,生成笛卡尔积,再根据条件进行筛选,但是不能对查询到表再进行操作

    mysql> select * from employee,post where post_id=post.id;
+----+-----------+-----+------+---------+------+--------------+
| id | name      | sex | age  | post_id | id   | name         |
+----+-----------+-----+------+---------+------+--------------+
|  1 | 孙悟空    | 男  |   18 |     200 |  200 | 技术          |
|  2 | 猪八戒    | 女  |   48 |     201 |  201 | 人力资源      |
|  3 | 沙悟净    | 男  |   38 |     201 |  201 | 人力资源      |
|  4 | 唐僧      | 女  |   28 |     202 |  202 | 销售          |
|  5 | 小白龙    | 男  |   18 |     200 |  200 | 技术          |
+----+-----------+-----+------+---------+------+--------------+

  4. 内连接: 只会显示两张表中互相匹配的项,其他不匹配的不显示

    # select * from 表1 inner join 表2 on 条件;
# 找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果
# post没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来
mysql> select e.id,e.name,age,sex,p.name from employee as e inner join post as p on post_id=p.id;
+----+-----------+------+-----+--------------+
| id | name      | age  | sex | name         |
+----+-----------+------+-----+--------------+
|  1 | 孙悟空    |   18 | 男  | 技术          |
|  2 | 猪八戒    |   48 | 女  | 人力资源      |
|  3 | 沙悟净    |   38 | 男  | 人力资源      |
|  4 | 唐僧      |   28 | 女  | 销售          |
|  5 | 小白龙    |   18 | 男  | 技术          |
+----+-----------+------+-----+--------------+

  5. 外连接之左连接: 不管左表中是不是匹配上都会显示所有内容

    # select * from 表1 left join 表2 on 条件;
# 以左表为准,即找出所有员工信息,当然包括没有部门的员工
# 本质就是: 在内连接的基础上增加左边有右边没有的结果
mysql> select e.id,e.name,post.name as post_name from employee as e left join post on post_id=post.id;
+----+-----------+--------------+
| id | name      | post_name    |
+----+-----------+--------------+
|  1 | 孙悟空    | 技术          |
|  5 | 小白龙    | 技术          |
|  2 | 猪八戒    | 人力资源      |
|  3 | 沙悟净    | 人力资源      |
|  4 | 唐僧      | 销售         |
|  6 | 哪吒      | NULL         |
+----+-----------+--------------+

  6. 外链接之右连接: 不管右表中是不是匹配上都会显示所有内容

    # select * from 表1 right join 表2 on 条件
# 以右表为准,即找出所有部门信息,包括没有员工的部门
# 本质就是: 在内连接的基础上增加右边有左边没有的结果
mysql> select e.id,e.name,post.name as post_name from employee as e right join post on post_id=post.id;
+------+-----------+--------------+
| id   | name      | post_name    |
+------+-----------+--------------+
|    1 | 孙悟空    | 技术          |
|    2 | 猪八戒    | 人力资源      |
|    3 | 沙悟净    | 人力资源      |
|    4 | 唐僧      | 销售         |
|    5 | 小白龙    | 技术         |
| NULL | NULL      | 运营         |
+------+-----------+--------------+

  7. 全外连接: 显示左右两个表全部记录

    # 全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
# 注意:mysql不支持全外连接 full join
# 强调:mysql可以使用此种方式间接实现全外连接
# select * from 表1 left join 表2 on 条件
# union
# select * from 表1 right join 表2 on 条件;
mysql> select e.id,e.name,post.name as post_name from employee as e left join post on post_id=post.id
    -> union
    -> select e.id,e.name,post.name as post_name from employee as e right join post on post_id=post.id;
+------+-----------+--------------+
| id   | name      | post_name    |
+------+-----------+--------------+
|    1 | 孙悟空    | 技术         |
|    5 | 小白龙    | 技术         |
|    2 | 猪八戒    | 人力资源     |
|    3 | 沙悟净    | 人力资源     |
|    4 | 唐僧      | 销售         |
|    6 | 哪吒      | NULL         |
| NULL | NULL      | 运营         |
+------+-----------+--------------+
# 注意 union与union all的区别: union会去掉相同的纪录

  8. 符合条件连接查询

    # 示例1：以内连接的方式查询employee和psot表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
mysql> select e.name,post.name as post_name from employee as e inner join post on post_id=post.id where age>25;
+-----------+--------------+
| name      | post_name    |
+-----------+--------------+
| 猪八戒    | 人力资源      |
| 沙悟净    | 人力资源      |
| 唐僧      | 销售         |
+-----------+--------------+

# 示例2：以内连接的方式查询employee和post表,并且以age字段的升序方式显示
mysql> select e.name,age,post.name as post_name,post_id from employee as e inner join post on post_id=post.id order by age;
+-----------+------+--------------+---------+
| name      | age  | post_name    | post_id |
+-----------+------+--------------+---------+
| 孙悟空    |   18 | 技术         |     200 |
| 小白龙    |   18 | 技术         |     200 |
| 唐僧      |   28 | 销售         |     202 |
| 沙悟净    |   38 | 人力资源      |     201 |
| 猪八戒    |   48 | 人力资源      |     201 |
+-----------+------+--------------+---------+

二, 子查询

# 1:子查询是将一个查询语句嵌套在另一个查询语句中.
# 2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
# 3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
# 4:还可以包含比较运算符:= 、 !=、> 、<等

  1. 带in关键字的子查询

    # 查询平均年龄在25岁以上的部门名
mysql> select name from post where id in (select post_id from employee group by post_id having avg(age)>25);
+--------------+
| name         |
+--------------+
| 人力资源      |
| 销售         |
+--------------+
# 查看技术部员工姓名
mysql> select name from employee where post_id=(select id from post where name='技术');
+-----------+
| name      |
+-----------+
| 孙悟空    |
| 小白龙    |
+-----------+
# 查看不足1人的部门名(子查询得到的是有人的部门id)
mysql> select name from post where id not in (select post_id from employee);
+--------+
| name   |
+--------+
| 运营   |
+--------+

  2. 带比较运算符的子查询

    # 查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from employee where age>(select avg(age) from employee);
+-----------+------+
| name      | age  |
+-----------+------+
| 猪八戒    |   48 |
| 沙悟净    |   38 |
+-----------+------+
# 查询大于部门内平均年龄的员工名、年龄
mysql> select name,post_id from employee inner join (select post_id as post_id2,avg(age) as avg_age from employee group by post_id) as emp on post_id=post_id2 where age>avg_age;
+-----------+---------+
| name      | post_id |
+-----------+---------+
| 猪八戒    |     201 |
+-----------+---------+

  3. 带exists关键字的子查询

    exists关键字表示存在,在使用exists关键字时,内层查询语句不返回查询的记录.而是返回一个真假值,True或False.当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询

    # post表中存在id=200，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from psot where id=200);

# post表中不存在id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from post where id=205);

  4. 练习: 查询每个部门最新入职的那位员工

    # 准备表和数据
mysql> create table emp(
    -> id int primary key auto_increment,
    -> emp_name varchar(20) not null,
    -> sex enum('男','女') not null default '男', # 大部分是男的
    -> age int(3) unsigned not null default 18,
    -> hire_date date not null,
    -> post varchar(50),
    -> post_comment varchar(100),
    -> salary double(15,2),
    -> office int, # 一个部门一个屋子
    -> depart_id int
    -> );
mysql> insert into emp(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
    -> ('孙悟空','男',18,'20170301','develop',7300,401,1), # 以下是开发部门
    -> ('猪八戒','男',78,'20150302','develop',1000000,401,1),
    -> ('唐僧','男',81,'20130305','develop',8300,401,1),
    -> ('小白龙','男',73,'20140701','develop',3500,401,1),
    -> ('沙悟净','男',28,'20121101','develop',2100,401,1),
    -> ('太上老君','女',18,'20110211','develop',9000,401,1),
    -> ('元始天尊','男',18,'19000301','develop',30000,401,1),
    -> ('通天教主','男',48,'20101111','develop',10000,401,1),
    -> ('歪歪','女',48,'20150311','sale',3000,402,2),# 以下是销售部门
    -> ('丫丫','女',38,'20101101','sale',2000,402,2),
    -> ('丁丁','女',18,'20110312','sale',1000,402,2),
    -> ('星星','女',18,'20160513','sale',3000,402,2),
    -> ('格格','女',28,'20170127','sale',4000,402,2),
    -> ('李世民','男',28,'20160311','operation',10000,403,3), # 以下是运营部门
    -> ('程咬金','男',18,'19970312','operation',20000,403,3),
    -> ('程咬银','女',18,'20130311','operation',19000,403,3),
    -> ('程咬铜','男',18,'20150411','operation',18000,403,3),
    -> ('程咬铁','女',18,'20140512','operation',17000,403,3);

    # 连表查询
mysql> select emp.post,emp_name,max_date from emp inner join (select post,max(hire_date) as max_date from emp group by post) as emp2
    -> on emp.post=emp2.post where hire_date=max_date;
+-----------+-----------+------------+
| post      | emp_name  | max_date   |
+-----------+-----------+------------+
| develop   | 孙悟空    | 2017-03-01 |
| sale      | 格格      | 2017-01-27 |
| operation | 李世民    | 2016-03-11 |
+-----------+-----------+------------+

    # 子查询  有缺陷
mysql> select emp_name,hire_date,post from (select * from emp order by hire_date desc) as emp2 group by post;
+-----------+------------+-----------+
| emp_name  | hire_date  | post      |
+-----------+------------+-----------+
| 孙悟空    | 2017-03-01 | develop   |
| 李世民    | 2016-03-11 | operation |
| 格格      | 2017-01-27 | sale      |
+-----------+------------+-----------+
mysql> select emp_name,hire_date,post from emp where id in(
    -> select (select id from emp as t2 where t2.post=t1.post order by hire_date desc limit 1) from emp as t1 group by post
    -> );
+-----------+------------+-----------+
| emp_name  | hire_date  | post      |
+-----------+------------+-----------+
| 孙悟空    | 2017-03-01 | develop   |
| 格格      | 2017-01-27 | sale      |
| 李世民    | 2016-03-11 | operation |
+-----------+------------+-----------+
# 每个部门可能有>1个为同一时间入职的新员工
# 说明,子查询可以在select后(要求查询的结果必须是一个单行单列的值)和from后使用
posted @ 2019-12-06 15:34  豆子V  阅读(240)  评论(0)  编辑  收藏  举报