一、库操作

二、表操作

1.存储引擎介绍

show engines; 查看数据库支持的引擎

MySQL 使用 InnoDB

指定表类型/存储引擎

create table t1(id int)engine = innodb;icreate table t2(id int)engine = memory;create table t3(id int)engine = blackhole;create table t4(id int)engine = myisam;

• innodb 对应 t1 ,frm:表结构 ibd:innodb缩写 是t1 的数据文件
• memory 对应 t2, 数据是放在内存里的，不对存到硬盘里，所以只有一个文件 t2.frm
• blackhole 对应 t3 ,黑洞，也是只有 t3.frm ，即表结构文件， 数据都进去就没了
• myisam 对应 t4, t4.frm:表结构文件 、 t4.MYD:数据文件 、 t4.MYI :索引文件

2. 增删查改

show create table t1; 查看表结构信息

-- 查看MySQL 的用户表 信息show create table mysql.user\G;-- \G ,可以让显示的内容按行显示 ，有利于阅读

mysql> create database mygd_test01 charset utf8;mysql> select host,user from mysql.user;+-----------+---------------+| host      | user          |+-----------+---------------+| %         | publiccms     || localhost | mysql.session || localhost | mysql.sys     || localhost | root          |+-----------+---------------+-- 复制表, 把 查询结果 复制到新创建的表里mysql> create table t1 select host,user from mysql.user; -- 查询 表 的字段列表信息 mysql> desc t1;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| host  | char(60) | NO   |     |         |       || user  | char(32) | NO   |     |         |       |+-------+----------+------+-----+---------+-------+mysql> select * from t1;  --看一下，表已经复制过来了+-----------+---------------+| host      | user          |+-----------+---------------+| %         | publiccms     || localhost | mysql.session || localhost | mysql.sys     || localhost | root          |+-----------+---------------+-- 可以加一个不满足的条件 ，这样可以到复制表结构的目的mysql> create table t2 select host,user from mysql.user where 1>2;mysql> desc t2;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| host  | char(60) | NO   |     |         |       || user  | char(32) | NO   |     |         |       |+-------+----------+------+-----+---------+-------+-- 只想拷贝表结构,不拷贝数据 的另一种方式mysql> create table t3 like mysql.user;

3.数据类型

(1)数值类型

整数类型

mysql> create table t1(x tinyint);insert into t1 valuse(-1);mysql> select * from t1;+------+| x    |+------+|   -1 |+------+-- 插入-129，超出范围了mysql> insert into t1 values(-129);ERROR 1264 (22003): Out of range value for column 'x' at row 1-- 创建一个无符号的mysql> create table t2(x tinyint unsigned);-- 插入超出无符号范围的值，有时候超范围的数是可以插入的，插入256，会变为255，可能还版本的原因mysql> insert into t2 values(-1);ERROR 1264 (22003): Out of range value for column 'x' at row 1mysql> insert into t2 values(256);ERROR 1264 (22003): Out of range value for column 'x' at row 1-- 创建表，指定字段长度mysql> create table t3(x tinyint(1) unsigned);--看一下创建的表机构，没问题mysql> desc t3;+-------+---------------------+------+-----+---------+-------+| Field | Type                | Null | Key | Default | Extra |+-------+---------------------+------+-----+---------+-------+| x     | tinyint(1) unsigned | YES  |     | NULL    |       |+-------+---------------------+------+-----+---------+-------+--然后我们插入一个超出范围的很大的数

--创建表，如上图，int字节大小为 4个字节，宽度是没有必要加的，如上图，int 的数据长度是固定好的mysql> create table t3(id int(1));-- 另外，我们在设计字段的时候写的 int(5) 这个不是存储宽度，而是显示宽度-- 创建表，无符号， zerofill 表示用 0 填充mysql> create table t4(uid int(5) unsigned zerofill);mysql> insert into t4 values(6);mysql> select * from t4;+-------+| uid   |+-------+| 00006 |+-------+-- int 无符号，默认显示宽度为 10，如上图,int无符号 范围是(0,4294 967 295) ,正好10位，-- int 有符号,默认宽度就是 11，因为要加一个 负号mysql> create table t5(id int unsigned);mysql> desc t5;+-------+------------------+------+-----+---------+-------+| Field | Type             | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| id    | int(10) unsigned | YES  |     | NULL    |       |+-------+------------------+------+-----+---------+-------+

​

浮点型

定点数类型 DEC等同于DECIMAL

浮点类型：FLOAT DOUBLE

======================================#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]定义：        单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30有符号：           -3.402823466E+38 to -1.175494351E-38,           1.175494351E-38 to 3.402823466E+38无符号：           1.175494351E-38 to 3.402823466E+38精确度：            **** 随着小数的增多，精度变得不准确 ****======================================#DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]定义：           双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30有符号：           -1.7976931348623157E+308 to -2.2250738585072014E-308           2.2250738585072014E-308 to 1.7976931348623157E+308无符号：           2.2250738585072014E-308 to 1.7976931348623157E+308精确度：           ****随着小数的增多，精度比float要高，但也会变得不准确 ****======================================decimal[(m[,d])] [unsigned] [zerofill]定义：          准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。精确度：           **** 随着小数的增多，精度始终准确 ****           对于精确数值计算时需要用此类型           decaimal能够存储精确值的原因在于其内部按照字符串存储。

-- FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]-- 单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30--精确度：   **** 随着小数的增多，精度变得不准确 ****-- 255 整数位数，30小数位数mysql> create table t8(x float(255,30));mysql> create table t9(x double(255,30));mysql> create table t10(x decimal(255,30));ERROR 1426 (42000): Too-big precision 255 specified for 'x'. Maximum is 65.mysql> create table t10(x decimal(65,30));-- 插入数据mysql> insert into t8 values(1.111111111111111111111111111111);mysql> insert into t9 values(1.111111111111111111111111111111);mysql> insert into t10 values(1.111111111111111111111111111111);--查询结果mysql> select * from t8;+----------------------------------+| x                                |+----------------------------------+| 1.111111164093017600000000000000 |+----------------------------------+mysql> select * from t9;+----------------------------------+| x                                |+----------------------------------+| 1.111111111111111200000000000000 |+----------------------------------+mysql> select * from t10;+----------------------------------+| x                                |+----------------------------------+| 1.111111111111111111111111111111 |+----------------------------------+

​

(2)日期类型

-- 创建表mysql> create table student(id int,name char(6),born_year year,birth_date date,class_time time,reg_time datetime);-- 插入数据mysql> insert into student values(1,'gudon',now(),now(),now(),now());mysql> select * from student;+------+-------+-----------+------------+------------+---------------------+| id   | name  | born_year | birth_date | class_time | reg_time            |+------+-------+-----------+------------+------------+---------------------+|    1 | gudon |      2018 | 2018-09-25 | 10:43:38   | 2018-09-25 10:43:38 |+------+-------+-----------+------------+------------+---------------------+-- 按时间格式插入数据mysql> insert into student values(3,'Astro',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13");mysql> select * from student;+------+-------+-----------+------------+------------+---------------------+| id   | name  | born_year | birth_date | class_time | reg_time            |+------+-------+-----------+------------+------------+---------------------+|    1 | gudon |      2018 | 2018-09-25 | 10:43:38   | 2018-09-25 10:43:38 ||    3 | Astro   |      1998 | 1998-01-01 | 13:13:13   | 2017-01-01 13:13:13 |+------+-------+-----------+------------+------------+---------------------+

​

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语句中没有指定该列的更新值，则默认更新为当前时间。

还是使用datatime 较多，时间范围比较大。

时间范围：

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 年某时）

​

(3).字符类型

char :定长

varchar: 变长

-- #宽度指的是 字符的个数mysql> create table t13(name char(5));mysql> create table t14(name varchar(5));mysql> insert into t13 values('孟浩   '); --后面加了3个空格mysql> select char_length(name) from t13; --存了了5个，查询结果是2个+-------------------+| char_length(name) |+-------------------+|                 2 |+-------------------+-- 即，存的时候，按5个存的，取得时候，把空格给去掉了-- 设置mysql模式，填充字符到完整的长度mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';-- 再查询一下试试mysql> select char_length(name) from t13;+-------------------+| char_length(name) |+-------------------+|                 5 |+-------------------+-- 数据库里存的是 孟浩 + 3个空格，但是我们通过如下的方式，不带空格也可以查询到结果，即MySQL在查询的时候，将默认的空格给去掉了。mysql> select * from t13 where name = '孟浩';+---------+| name    |+---------+| 孟浩    |+---------+--但是只针对末尾的空格，字符前面有空格是没法查的，另外，如果条件查询是like,不会去掉末尾的空格去查询mysql> select * from t13 where name = '  孟浩';Empty set (0.00 sec)

#char类型：定长，简单粗暴，浪费空间，存取速度快    字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）    存储：        存储char类型的值时，会往右填充空格来满足长度        例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储    检索：        在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）#varchar类型：变长，精准，节省空间，存取速度慢    字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）    存储：        varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来        强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）        如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）        如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）    检索：        尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

​

name  char(5)jack |rose |sky  |name varchar(5)1byte+jack|1byte+rose|1byte+sky|

varchar, 字符串前面会有一个byte 来存目标数据 的长度，一个byte 是255，如果目标数据的长度超过 255，记录长度 就用两个byte, 两个byte 还 65535，MySQL要求规定这是最大的地址长度

​

• 更多的使用char类型，因为存取速度快，而且存储空间现在不是太大问题了
• 如果是数据查询频率不高，可以使用varchar 类型
• 在同一张表里，最好不要 char 和 varchar 混合使用

(4) 枚举类型和集合类型

​

enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female

set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

mysql> create table consumer(id int,name char(16),sex enum('male','female','other'),level enum('A','B','C'),hobbies set('game','music','read','run'));mysql> insert into consumer values(1,'astro','male','A','music');mysql> insert into consumer values(1,'astro','male','W','music'); --传入不存在的值mysql> insert into consumer values(1,'Nurato','other','A','read,run');--传入多个值

​

​

4.完整性约束

mysql> create table t16(id int, name char(6), sex enum('male','famale') not null default 'male');mysql> desc t16;+-------+-----------------------+------+-----+---------+-------+| Field | Type                  | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| id    | int(11)               | YES  |     | NULL    |       || name  | char(6)               | YES  |     | NULL    |       || sex   | enum('male','famale') | NO   |     | male    |       |+-------+-----------------------+------+-----+---------+-------+mysql> insert into t16 (id,name) values(1,'zs');mysql> select * from t16;+------+--------+------+| id   | name   | sex  |+------+--------+------+|    1 | zs     | male |+------+--------+------+

unique

单列唯一：

--方式一：mysql> create table department(id int,name char(10) unique);--插入两条 name 一样记录，会报错mysql> insert into department values(1,'zs'),(2,'zs');ERROR 1062 (23000): Duplicate entry 'zs        ' for key 'name'-- 方式二create table department(id int, name char(10),unique(id),unique(name))

​

联合唯一：

-- ip port 联合唯一mysql> create table services(id int,ip char(15),port int,unique(id),unique(ip,port));mysql> desc services;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(11)  | YES  | UNI | NULL    |       || ip    | char(15) | YES  | MUL | NULL    |       || port  | int(11)  | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+mysql> insert into services values(1,'192.168.10.1',80),(2,'192.168.10.1',80);ERROR 1062 (23000): Duplicate entry '192.168.10.1   -80' for key 'ip'mysql> insert into services values(1,'192.168.10.1',80),(2,'192.168.10.1',82);mysql> select * from services;+------+-----------------+------+| id   | ip              | port |+------+-----------------+------+|    1 | 192.168.10.1    |   80 ||    2 | 192.168.10.1    |   82 |+------+-----------------+------+

​

primary key (not null unique)

对于 innodb 存储引擎来说，一张表里，必须要有一个主键

单列主键：

mysql> create table t17(id int primary key,name char(16));mysql> desc t17;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(11)  | NO   | PRI | NULL    |       | --主键 PRI| name  | char(16) | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+mysql> insert into t17 values(1,'gd'),(2,'astro');mysql> insert into t17 values(2,'zs');ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'  --报错mysql> select *  from t17;+----+------------------+| id | name             |+----+------------------+|  0 | jack             ||  1 | gd               ||  2 | astro            |+----+------------------+-- 如果不指定主键，MySQL会默认找一个-- not null unique 就是主键mysql> create table t18(id int not null unique,name char(16));mysql> desc t18;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(11)  | NO   | PRI | NULL    |       || name  | char(16) | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+

复合主键:

mysql> create table t19(ip char(15),port int,primary key(ip,port));mysql> desc t19;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ip    | char(15) | NO   | PRI | NULL    |       || port  | int(11)  | NO   | PRI | NULL    |       |+-------+----------+------+-----+---------+-------+-- 插入相同的数据，报错mysql> insert into t19 values('1.1.1.1',80),('1.1.1.1',80);ERROR 1062 (23000): Duplicate entry '1.1.1.1        -80' for key 'PRIMARY'-- 插入不同的数据mysql> insert into t19 values('1.1.1.1',80),('1.1.1.1',81);mysql> select * from t19;+-----------------+------+| ip              | port |+-----------------+------+| 1.1.1.1         |   80 || 1.1.1.1         |   81 |+-----------------+------+

​

auto_increment

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

mysql> create table t20(id int auto_increment);ERROR 1075 (42000): Incorrect table definition; there can be only one autoined as a keymysql> create table t20(id int primary key auto_increment,name char(16));mysql> desc t20;+-------+----------+------+-----+---------+----------------+| Field | Type     | Null | Key | Default | Extra          |+-------+----------+------+-----+---------+----------------+| id    | int(11)  | NO   | PRI | NULL    | auto_increment || name  | char(16) | YES  |     | NULL    |                |+-------+----------+------+-----+---------+----------------+mysql> insert into t20 (name) values('astro'),('nurato');mysql> select * from t20;+----+------------------+| id | name             |+----+------------------+|  1 | astro            ||  2 | nurato           |+----+------------------+-- id不用自己传，但是也可以传的，如果现在新增数据 id 为7，然后再插入2条数据(不传入id)，那这两条数据的id会从7开始增加，为 8 和 9-- 自动增长的步长也可以修改mysql> show variables like 'auto_inc%'  -- %代表任意内容+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     |  --表示步长| auto_increment_offset    | 1     |  -- 表示起始位置，从1开始+--------------------------+-------+-- 设置步长mysql> set session auto_increment_increment = 5;  --当前会话级别mysql> set global auto_increment_increment = 5; --全局级别，需要关闭当前会话重新开启后生效-- 设置偏移量 [起始偏移量的设置要小于等于步长,否则失效 ]set global auto_increment_offset = 5；

-- 这种删除方式，会把数据删除，但是删除后重新插入数据，id会沿着之前的id值往后累加delete from t20;-- 这种方式，全部清空数据，会把自动增长的也清除掉，下次再新增数据，会从初始值开始累加truncate t20;

​

​

foreign key 用来建立表之间的关系

-- 先建被关联的表，被关联的字段必须是唯一的create table dep(    id int primary key,    name char(16),    comment char(50)    );-- 再建关联表create table emp(    id int primary key,    name char(10),    sex enum('male','female'),    dep_id int,    foreign key(dep_id) references dep(id)    on delete cascade  --这句，可以在删除 dep(被关联表)的时候，关联表里相关的内容也一并被删除    on update cascade -- 使得更新 dep 的时候，emp也会跟更新);-- 插入数据-- 先在被关联表中插入数据insert into dep values(1,'IT','技术部门'),(2,'销售','销售部门'),(3,'财务','花钱最多部门');-- 再向关联表插入数据insert into emp values(1,'astro','male',1),(2,'Nurato','male',1),(3,'jack','female',2),(4,'zs','male',3),(5,'sky','female',2);-- 如果要删除技术部门以及技术部门下的所有人员，要先删除 emp下的人员，再删除 dep下的部门-- 但是，我们在创建 emp 表的时候 ，foreign key(dep_id) references dep(id) on delete cascade -- on delete cascade 使得在删除 dep 的时候，可以把 emp下相关的人员也全部删除掉-- on update cascade 使得，也可以在 dep 更新 dep_id 的时候,自动把 emp 下的相关内容(id)也更新

一般从逻辑意义上设计表之间的关联，不在数据库中真正去使用 外键 做关联，因为外键使得表之间的耦合度变高，在有需要做扩展的时候，可能会有麻烦

​

表之间的关系：

分析步骤：#1、先站在左表的角度去找是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）#2、再站在右表的角度去找是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）#3、总结：#多对一：如果只有步骤1成立，则是左表多对一右表如果只有步骤2成立，则是右表多对一左表#多对多如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系#一对一:如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

===================== 多对一 =====================-- 一对多（或多对一）：一个出版社可以出版多本书-- 关联方式：foreign keycreate table press(id int primary key auto_increment,name varchar(20));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 cascadeon update cascade);insert into press(name) values('北京工业地雷出版社'),('人民音乐不好听出版社'),('知识产权没有用出版社');insert into book(name,press_id) values('九阳神功',1),('九阴真经',2),('九阴白骨爪',2),('独孤九剑',3),('降龙十巴掌',2),('葵花宝典',3);=====================多对多=====================--多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多　--关联方式：foreign key+一张新的表-- 三张表：出版社，作者信息，书create table author(id int primary key auto_increment,name varchar(20));#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了create table author2book(id int not null unique auto_increment,author_id int not null,book_id int not null,constraint fk_author foreign key(author_id) references author(id)on delete cascadeon update cascade,constraint fk_book foreign key(book_id) references book(id)on delete cascadeon update cascade,primary key(author_id,book_id));#插入四个作者，id依次排开insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');#每个作者与自己的代表作如下egon:     九阳神功    九阴真经    九阴白骨爪    独孤九剑    降龙十巴掌    葵花宝典alex:     九阳神功    葵花宝典    yuanhao:    独孤九剑    降龙十巴掌    葵花宝典wpq:    九阳神功insert into author2book(author_id,book_id) values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(2,1),(2,6),(3,4),(3,5),(3,6),(4,1);------------------------------------------------------------------------------- =======================  一对一  =========================两张表：学生表和客户表一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系关联方式：foreign key+unique#一定是student来foreign key表customer，这样就保证了：#1 学生一定是一个客户，#2 客户不一定是学生，但有可能成为一个学生create table customer(id int primary key auto_increment,name varchar(20) not null,qq varchar(10) not null,phone char(16) not null);create table student(id int primary key auto_increment,class_name varchar(20) not null,customer_id int unique, #该字段一定要是唯一的foreign key(customer_id) references customer(id) #外键的字段一定要保证uniqueon delete cascadeon update cascade);#增加客户insert into customer(name,qq,phone) values('李飞机','31811231',13811341220),('王大炮','123123123',15213146809),('守榴弹','283818181',1867141331),('吴坦克','283818181',1851143312),('赢火箭','888818181',1861243314),('战地雷','112312312',18811431230);#增加学生insert into student(class_name,customer_id) values('脱产3班',3),('周末19期',4),('周末19期',5);

​

三、数据操作

​

1.数据的增删改

​

DML

INSERT

1. 插入完整数据（顺序插入）    语法一：    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);    语法二：    INSERT INTO 表名 VALUES (值1,值2,值3…值n);2. 指定字段插入数据    语法：    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);3. 插入多条记录    语法：    INSERT INTO 表名 VALUES        (值1,值2,值3…值n),        (值1,值2,值3…值n),        (值1,值2,值3…值n);4. 插入查询结果    语法：    INSERT INTO 表名(字段1,字段2,字段3…字段n)                     SELECT (字段1,字段2,字段3…字段n) FROM 表2                    WHERE …;

​

UPDATE

语法：    UPDATE 表名 SET        字段1=值1,        字段2=值2,        WHERE CONDITION;示例：    UPDATE mysql.user SET password=password(‘123’)         where user=’root’ and host=’localhost’;

​

DELETE

语法：    DELETE FROM 表名         WHERE CONITION;示例：    DELETE FROM mysql.user         WHERE password=’’;

​

2.单表查询

​

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准备表和记录---------------------------------------------------------------

-- 定义显示格式mysql> select concat('姓名:',name, '年薪:',salary*12) as TNT from employee;+-------------------------------+| TNT                           |+-------------------------------+| 姓名:egon年薪:87603.96        || 姓名:alex年薪:12000003.72     || 姓名:wupeiqi年薪:99600.00     || 姓名:yuanhao年薪:42000.00     || 姓名:liwenzhou年薪:25200.00   || 姓名:jingliyang年薪:108000.00 || 姓名:jinxin年薪:360000.00     || 姓名:成龙年薪:120000.00       || 姓名:歪歪年薪:36001.56        || 姓名:丫丫年薪:24004.20        || 姓名:丁丁年薪:12004.44        || 姓名:星星年薪:36003.48        || 姓名:格格年薪:48003.96        || 姓名:张野年薪:120001.56       || 姓名:程咬金年薪:240000.00     || 姓名:程咬银年薪:228000.00     || 姓名:程咬铜年薪:216000.00     || 姓名:程咬铁年薪:204000.00     |+-------------------------------+-- CONCAT_WS() 第一个参数为分隔符mysql> select concat_ws('---',name,salary) as TNT from employee;+----------------------+| TNT                  |+----------------------+| egon---7300.33       || alex---1000000.31    || wupeiqi---8300.00    || yuanhao---3500.00    || liwenzhou---2100.00  || jingliyang---9000.00 || jinxin---30000.00    || 成龙---10000.00      || 歪歪---3000.13       || 丫丫---2000.35       || 丁丁---1000.37       || 星星---3000.29       || 格格---4000.33       || 张野---10000.13      || 程咬金---20000.00    || 程咬银---19000.00    || 程咬铜---18000.00    || 程咬铁---17000.00    |+----------------------+-- 下面两句是一样的结果mysql> select * from employee where salary >=20000 and salary<=30000;mysql> select * from employee where salary between 20000 and 30000;-- 模糊查询mysql> select name from employee where name like 'j%';  -- j + 后面任意多个字符mysql> select name from employee where name like 'jin___'; -- 一个下划线代表一个字符--group by-- 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义-- 多条记录之间的某个字段值相同，该字段通常用来作为分组的依据mysql> select post,count(name) from employee group by post;-- 没有 group by ，默认整体算一组mysql> select max(salary) from employee;--  group_concatmysql> select post,group_concat(name) from employee group by post;+-----------+-------------------------------------------------------+| post      | group_concat(name)                                    |+-----------+-------------------------------------------------------+| operation | 张野,程咬金,程咬银,程咬铜,程咬铁                      || sale      | 歪歪,丫丫,丁丁,星星,格格                              || teacher   | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 || 总统      | egon                                                  |+-----------+-------------------------------------------------------+

​

having

执行顺序：

执行优先级从高到低：where > group by > having > order by > limit n

• Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
• Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

mysql> select post,group_concat(name),count(id) from employee group by post;+-----------+-------------------------------------------------------+-----------+| post      | group_concat(name)                                    | count(id) |+-----------+-------------------------------------------------------+-----------+| operation | 张野,程咬金,程咬银,程咬铜,程咬铁                      |         5 || sale      | 歪歪,丫丫,丁丁,星星,格格                              |         5 || teacher   | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 |         7 || 总统      | egon                                                  |         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 |+------+--------------------+-----------+

​

order by

asc 升序(默认)

desc 降序

-- 先按age 升序排， 再按id降序排mysql> select * from employee order by age asc,id desc;

-- 关于执行顺序select distinct 字段1,字段2,字段3 from 库.表     where 条件    group by 分组条件    having 过滤    order by 排序字段    limit n;        1.from 库.表2.where3.group by4.having5.distinct（其他去和函数等）6.order by7.limit n

​

limit

-- 查询 salary 最高的前三个mysql> select id,name,salary from employee order by salary desc limit 3;+----+--------+------------+| id | name   | salary     |+----+--------+------------+|  2 | alex   | 1000000.31 ||  7 | jinxin |   30000.00 || 15 | 程咬金 |   20000.00 |+----+--------+------------+SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; --从第0开始，即先查询出第一条，然后包含这一条在内往后查5条SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; --从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

​

​

使用正则表达式查询

-- 用正则表达式的规则查询mysql> select name,age from employee where name regexp '^程';  --以程 开头的+--------+-----+| name   | age |+--------+-----+| 程咬金 |  18 || 程咬银 |  18 || 程咬铜 |  18 || 程咬铁 |  18 |+--------+-----+-- 查看所有员工中名字是jin开头，n或者g结果的员工信息select name,age from employee where name regexp '^jin.*[gn]$';+------------+-----+| name       | age |+------------+-----+| jingliyang |  18 || jinxin     |  18 |+------------+-----+

3.多表查询

(1) 连接查询

#建表create table department(id int,name varchar(20) );create table employee(id int primary key auto_increment,name varchar(20),sex enum('male','female') not null default 'male',age int,dep_id int);#插入数据insert into department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');insert into employee(name,sex,age,dep_id) values('egon','male',18,200),('alex','female',48,201),('wupeiqi','male',38,201),('yuanhao','female',28,202),('liwenzhou','male',18,200),('jingliyang','female',18,204);-- 查询表mysql> select * from department;+------+--------------+| id | name |+------+--------------+| 200 | 技术 || 201 | 人力资源 || 202 | 销售 || 203 | 运营 |+------+--------------+mysql> select * from employee;+----+------------+--------+------+--------+| id | name | sex | age | dep_id |+----+------------+--------+------+--------+| 1 | egon | male | 18 | 200 || 2 | alex | female | 48 | 201 || 3 | wupeiqi | male | 38 | 201 || 4 | yuanhao | female | 28 | 202 || 5 | liwenzhou | male | 18 | 200 || 6 | jingliyang | female | 18 | 204 |+----+------------+--------+------+--------+表department与employee

1 交叉连接：不适用任何匹配条件。生成笛卡尔积

mysql> select * from employee,department;+----+------------+--------+------+--------+------+--------------+| id | name       | sex    | age  | dep_id | id   | name         |+----+------------+--------+------+--------+------+--------------+|  1 | egon       | male   |   18 |    200 |  200 | 技术         ||  1 | egon       | male   |   18 |    200 |  201 | 人力资源     ||  1 | egon       | male   |   18 |    200 |  202 | 销售         ||  1 | egon       | male   |   18 |    200 |  203 | 运营         ||  2 | alex       | female |   48 |    201 |  200 | 技术         ||  2 | alex       | female |   48 |    201 |  201 | 人力资源     ||  2 | alex       | female |   48 |    201 |  202 | 销售         ||  2 | alex       | female |   48 |    201 |  203 | 运营         ||  3 | wupeiqi    | male   |   38 |    201 |  200 | 技术         ||  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     ||  3 | wupeiqi    | male   |   38 |    201 |  202 | 销售         ||  3 | wupeiqi    | male   |   38 |    201 |  203 | 运营         ||  4 | yuanhao    | female |   28 |    202 |  200 | 技术         ||  4 | yuanhao    | female |   28 |    202 |  201 | 人力资源     ||  4 | yuanhao    | female |   28 |    202 |  202 | 销售         ||  4 | yuanhao    | female |   28 |    202 |  203 | 运营         ||  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         ||  5 | liwenzhou  | male   |   18 |    200 |  201 | 人力资源     ||  5 | liwenzhou  | male   |   18 |    200 |  202 | 销售         ||  5 | liwenzhou  | male   |   18 |    200 |  203 | 运营         ||  6 | jingliyang | female |   18 |    204 |  200 | 技术         ||  6 | jingliyang | female |   18 |    204 |  201 | 人力资源     ||  6 | jingliyang | female |   18 |    204 |  202 | 销售         ||  6 | jingliyang | female |   18 |    204 |  203 | 运营         |+----+------------+--------+------+--------+------+--------------+

​

2 内连接：只连接匹配的行

#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+-----------+------+--------+--------------+| id | name      | age  | sex    | name         |+----+-----------+------+--------+--------------+|  1 | egon      |   18 | male   | 技术         ||  2 | alex      |   48 | female | 人力资源     ||  3 | wupeiqi   |   38 | male   | 人力资源     ||  4 | yuanhao   |   28 | female | 销售         ||  5 | liwenzhou |   18 | male   | 技术         |+----+-----------+------+--------+--------------+#上述sql等同于mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

​

3 外链接之左连接：优先显示左表全部记录

#以左表为准，即找出所有员工信息，当然包括没有部门的员工#本质就是：在内连接的基础上增加左边有右边没有的结果mysql> select * from employee left join department on employee.dep_id=department.id;+----+------------+--------+------+--------+------+----------+| id | name       | sex    | age  | dep_id | id   | name     |+----+------------+--------+------+--------+------+----------+|  1 | egon       | male   |   18 |    200 |  200 | 技术     ||  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术     ||  2 | alex       | female |   48 |    201 |  201 | 人力资源 ||  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源 ||  4 | yuanhao    | female |   28 |    202 |  202 | 销售     ||  6 | jingliyang | female |   18 |    204 | NULL | NULL     |+----+------------+--------+------+--------+------+----------+

​

4 外链接之右连接：优先显示右表全部记录

#以右表为准，即找出所有部门信息，包括没有员工的部门#本质就是：在内连接的基础上增加右边有左边没有的结果mysql> select * from employee right join department on employee.dep_id=department.id;+------+-----------+--------+------+--------+------+----------+| id   | name      | sex    | age  | dep_id | id   | name     |+------+-----------+--------+------+--------+------+----------+|    1 | egon      | male   |   18 |    200 |  200 | 技术     ||    2 | alex      | female |   48 |    201 |  201 | 人力资源 ||    3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源 ||    4 | yuanhao   | female |   28 |    202 |  202 | 销售     ||    5 | liwenzhou | male   |   18 |    200 |  200 | 技术     || NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营     |+------+-----------+--------+------+--------+------+----------+

​

5 全外连接：显示左右两个表全部记录

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果#注意：mysql不支持全外连接 full JOIN#强调：mysql可以使用此种方式间接实现全外连接select * from employee left join department on employee.dep_id = department.idunionselect * from employee right join department on employee.dep_id = department.id;#查看结果+------+------------+--------+------+--------+------+--------------+| id   | name       | sex    | age  | dep_id | id   | name         |+------+------------+--------+------+--------+------+--------------+|    1 | egon       | male   |   18 |    200 |  200 | 技术         ||    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         ||    2 | alex       | female |   48 |    201 |  201 | 人力资源     ||    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     ||    4 | yuanhao    | female |   28 |    202 |  202 | 销售         ||    6 | jingliyang | female |   18 |    204 | NULL | NULL         || NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |+------+------------+--------+------+--------+------+--------------+#注意 union与union all的区别：union会去掉相同的纪录

​

(2)符合条件的连接查询

查询平均年龄大于 30 的部门名称

select avg(employee.age),department.name from employee inner join department on employee.dep_id = department.idgroup by department.idhaving avg(employee.age) > 30;+-------------------+----------+| avg(employee.age) | name     |+-------------------+----------+|           43.0000 | 人力资源 |+-------------------+----------+

​

(3) 子查询

带IN关键字的子查询

#查询平均年龄在25岁以上的部门名select id,name from department    where id in         (select dep_id from employee group by dep_id having avg(age) > 25);          #查看技术部员工姓名select id,name from employee    where dep_id in         (select id from department where name='技术'); #查看不足1人的部门名 select id,name from department where id not in  (    select distinct dep_id from employee );

​

带比较运算符的子查询

#查询大于所有人平均年龄的员工名与年龄mysql> select name,age from employee where age > (select avg(age) from employee);#查询大于部门内平均年龄的员工名、年龄select emp.name,emp.age from employee emp inner join(select department.id departmentID,avg(employee.age) avg_age  from employee inner join department on department.id=employee.dep_id group by department.id) Bon emp.dep_id = B.departmentIDwhere emp.age > B.avg_age;

带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。

而是返回一个真假值。True或False

当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

select * from employee where exists(select * from department where name = "木叶医疗班");-- 返回False ，所以查询不到结果

​

​

4.mysql 权限管理

权限管理#1、创建账号# 本地账号create user 'egon1'@'localhost' identified by '123'; # mysql -uegon1 -p123# 远程帐号create user 'egon2'@'192.168.31.10' identified by '123'; # mysql -uegon2 -p123 -h 服务端ipcreate user 'egon3'@'192.168.31.%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ipcreate user 'egon3'@'%' identified by '123'; # mysql -uegon3 -p123 -h 服务端ip    #2、授权user:*.*  -- 全部权限db:db1.*   -- 数据库级别tables_priv:db1.t1  -- 表级别columns_priv:id,name  -- 字段级别grant all on *.* to 'egon1'@'localhost';  -- 授权全部权限grant select on *.* to 'egon1'@'localhost';  -- 授权 查询权限revoke select on *.* from 'egon1'@'localhost';  -- 删除删除权限grant select on db1.* to 'egon1'@'localhost'; revoke select on db1.* from 'egon1'@'localhost';grant select on db1.t2 to 'egon1'@'localhost';revoke select on db1.t2 from 'egon1'@'localhost';grant select(id,name),update(age) on db1.t2 to 'egon1'@'localhost'; -- 授权查询 id,name ，更新 age 的权限-- 可以查询字段操作权限 select * from mysql.columns_priv;