关于MySQL的笔记（更新中.......）

1 MySQL

1.1 连接mysql服务器

1、下载并安装mysql或者maraiadb ，过程省略。

2、连接mysql服务器：mysql -u root -p 密码

3、修改密码：mysqladmin -u root -p flush-privileges password “new_pwd”

4、连接mysql服务器并指定IP和端口:
mysql -uroot -proot -h127.0.0.1 -P3306
-h：后面给出的127.0.0.1是服务器主机名或ip地址，可以省略的，默认连接本机；
-P：(大写的P)后面的3306是连接端口，可以省略，默认连接3306端口。

5、退出客户端命令：quit 或 exit 或 \q

6、常见问题：

原因：环境变量没有配置或配置路径不对。
解决方法：复制mysql安装目录下的bin目录的路径，将bin目录的路径添加到path环境变量
中!! 可以在cmd中通过 echo %path% 检查path环境变量的值。

7、扩展内容：
1）在cmd中连接mysql服务器之后，可以使用 #、/**/、–（空格）等符号添加注释，例如:

2）在cmd中连接mysql服务器之后，在书写SQL语句时，可以通过 \c 取消当前语句的执行。

3）命令要以分号（;）或斜线+g（\g）结尾。你用的是\G，会是另一种格式：它导致结果不以表格形式展示，而是使每条记录都分成多行来展示。

1.2 数据库概述

1.2.1 什么是数据库

1、MySQL 是一个开源、多线程、关系型数据库管理系统，它稳定可靠、性能高、功能强大

2、数据库：“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、
有组织的、可共享的、统一管理的大量数据的集合。

数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏。
数据库完整性（DatabaseIntegrity）是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。

3、好处：1.可以持久化数据到本地；2.结构化查询

4、数据库的分类：
1.早期: 层次式数据库、网络型数据库
2.现在：关系型数据库、非关系型数据库

5、关系型数据库：底层以二维表的形式保存数据的库就是关系型数据库。

6、什么是数据库服务器
数据库服务器就是一个软件（比如mysql软件）将数据库软件安装在电脑上，当前电脑就是
一个数据库服务器。就可以对外提供存取数据的服务。

在一个数据库服务器中可以创建多个数据库（dataBases），每一个数据库都是一个单独的仓
库。

7、什么是表
一个数据库中可以创建多张表，每张表用于存储一类信息（数据库）。
表名具有唯一性。

表记录：一张表中可以包含多行表记录，每一行表记录用于存储某一个具体的数据。

8、数据库几个固定角色

1.2.2 什么是SQL语言

1、SQL是一门用于操作关系型数据库的通用的语言.

DB：数据库–保存一些列有组织的数据
DBMS：数据库管理系统–数据库是通过DBMS创建和操作的容器
SQL：结构化查询语言–专门与数据通信的的语言

2、SQL语句对大小写不敏感。推荐关键字使用大写，自定义的名称（库名，表名，列名、别名等）使用小写。

并且在自定义名称时，针对多个单词不要使用驼峰命名，而是使用下划线连接。(例如：tab_name，而不是 tabName )

3、SQL语言的分类：
数据查询语言（DQL）：是由SELECT子句，FROM子句，WHERE子句组成的查询块
数据操纵语言（DML）: SELECT(查询) INSERT(插入) UPDATE(更新) DELETE(删除）
数据定义语言（DDL）：CREATE(创建数据库或表或索引）ALTER(修改表或者数据库）DROP(删除表或索引）
数据控制语言（DCL）：GRANT(赋予用户权限） REVOKE(收回权限） DENY(禁止权限)
事务控制语言（TCL）：SAVEPOINT (设置保存点）ROLLBACK (回滚) COMMIT(提交)

1.3 数据库及表操作

1.3.1 创建、删除、查看数据库

1、查看mysql服务器中所有数据库：
SHOW DATABASES; – 查看当前数据库服务器中的所有库

CREATE DATABASE mydb1; – 创建mydb1库
DATABASE 和SCHEMA结果一样：CREATE SCHEMA database_name

2、进入某一数据库（进入数据库后，才能操作库中的表和表记录）
use test; – 进入test数据库

3、show tables; – 查看test库中的所有表

4、删除mydb1库：drop database mydb1; – 删除不存在的库，这种写法会报错！

drop database if exists mydb1; – 如果存在mydb1，则删除；

5、重新创建mydb1库，指定编码为utf8
语法：CREATE DATABASE 库名 CHARSET 编码;
需要注意的是，mysql中不支持横杠（-），所以utf-8要写成utf8；
create database mydb1 charset utf8; – 建库时，最好指定编码，如果不指定，这个库的编码有可能是latin1

create database if not exists mydb1 charset utf8; 如果不存在则创建mydb1;

6、查看建库时的语句（了解, 并验证数据库库使用的编码）

语法：SHOW CREATE DATABASE 库名;

7、其他
COLLATE latin1_bin;告诉MySQL 数据的存储方式是二进制拉丁字符。

1.3.2 创建、删除、查看表

1、进入mydb1库，删除stu学生表(如果存在)
use mydb1;
drop table if exists stu; – 如果存在则删除

2、创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]），建表的语法：

use mydb1;
create table stu(
id int,
name varchar(50), -- 50表示name这一列，最多能存50个字符
gender varchar(10),
birthday date,
score double
);

3、查看stu学生表结构 – 语法：desc 表名

1.3.3 新增、修改、删除表记录

1、往学生表(stu)中插入记录(数据)
语法：INSERT INTO 表名(列名1,列名2,列名3…) VALUES(值1,值2,值3…);

insert into stu(id,name,gender,birthday,score)
value(1,'小黄','male','2000-1-1',85);
-- 如果插入的有中文数据，在cmd中，先 set names gbk; 再插入，可
以防止中文乱码（或者中文数据插入失败）
insert into stu value(2,'小红','female','2001-2-3',90);
insert into stu value(3,'小明','male','2002-3-4',78);

提示：
方式一支持插入多行，以及子查询，方式二不支持插入多行，也不支持子查询；
方式二：insert into 表名 set 列名=值,列名=值,…；

1）当为所有列插入值时，可以省写列名，但值的个数和顺序必须和声明时列的个数和顺序保持一致！
2）SQL语句中的值为字符串或日期时，值的两边要加上单引号（有的版本的数据库双引号也可以，但推荐使用单引号）。
3）(针对cmd窗口)在插入数据之前，先设置编码：set names gbk;
或者用以下命令连接mysql服务器：mysql --default-character-set=gbk -uroot -proot
等价于：
mysql -uroot -proot
set names gbk;

2、查询所有信息
select * from stu; – *号是通配符，表示查询所有列

3、修改stu表中所有学生的成绩，加10分特长分
修改语法: UPDATE 表名 SET 列=值,列=值,列=值…[WHERE子句];
update stu set score=83 where id=1;

修改多表的记录：
update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值，…where 筛选条件

4、删除stu表中所有的记录
DELETE FROM 表名 [where子句]
delete from stu where id>1;

多表删除：
delete 表1的别名, 表2的别名 from 表1的别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件

方式二：truncate table 表名；–效率要高一丢丢

truncate和delete的区别
1.假如要删除的表中有自增长列，用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的数据从1开始
2.truncate删除没有返回值，delete删除有返回值，可以返回受影响的行数
3.delete可以加where条件，truncate不能加
4.truncate删除不能回滚，delete删除可以回滚。

5、修改表名，语法：ALTER TABLE 表名 ADD|DROP|CHANGE COLUMN 列名 [列的类型 约束]
ALTER TABLE emp RENAME TO newemp；

1.3.4 列的记录操作

1、MySQL 和MariaDB 是不关注列的排位的。若想将新列加在最前面，要用FIRST，而不是 AFTER。FIRST 后不需要带列名。
ALTER TABLE birds_new ADD COLUMN wing_id CHAR(2) AFTER family_id;

2、修改列的大小，就需要用到CHANGE COLUMN 子句。 或者在添加新列的时候指定
CHANGE COLUMN common_name common_name VARCHAR(255);

CHANGE COLUMN 子句中，第一次指示要改哪一列。第二次给该列指定一个新名字。不过即使没打算改列名，你还是要写上一个名字，否则会报错，并拒绝执行。接下来指定数据类型。即使你只打算改名而没打算改类型，你还是要写上一个类型。

在使用CHANGE COLUMN 时，就算只想修改该列的某一方面，服务器也需要你完整地声明整个新列。

3、标识列：又称自增长列，可以不用手动的插入值，系统提供默认的序列值

特点：

1. 标识列必须和主键搭配吗？不一定，但要求是一个key
2. 一个表至多有一个标识列
3. 标识列的类型只能是数值型
4. 标识列可以通过set auto_increment=3;设置步长
5. 可以通过手动插入值，设置起始值。

修改表时设置标识列：
alter table emp modify column id int primary key auto_increment;

4、修改列名
ALTER TABLE emp CHANGE COLUMN name newname VARCHAR(50);

5、修改列的类型和约束
ALTER TABLE emp MODIFY COLUMN name char(4);

6、添加新列
ALTER TABLE emp ADD COLUMN sal DOUBLE [FIRST|AFTER 字段名]；FIRST后面不跟字段名

7、删除列
ALTER TABLE emp DROP COLUMN sal ；

8、关于表的修改
change 用来修改字段名字以及类型
modify 用来修改字段类型和约束
alter column … set 用来修改字段数据
如：修改test_tbl 字段i的缺省值为1000， alter table tset_tbl alter i set default 1000;

1.3.5 复制表记录

1、 复制表的结构、数据和设定。
INSERT INTO birds_new SELECT * FROM rookery.birds;

2、仅复制表的结构
CREATE TABLE copy LIKE author;

3、复制表的结构+数据
CREATE TABLE copy2 SELECT * FROM author;

4、只复制部分数据
CREATE TABLE copy3 SELECT id, au_name FROM author WHERE nation=’中国‘；

5、只复制某些字段
CREATE TABLE copy4 SELECT id, au_name FROM author WHERE 1；

1.4 查询表记录

1.4.1 基础查询

1、SELECT 语句用于从表中选取数据。结果被存储在一个结果表中（称为结果集）。
语法：SELECT 列名称 | * FROM 表名
提示：1） *（星号）为通配符，表示查询所有列。2）但使用 *（星号）有时会把不必要的列也查出来了，并且效率不如直接指定列名

select * from emp; -- *表示查询emp表中的所有列
select name,sal,bonus from emp; -- 查询指定的列

2、在select之后、列名之前，使用DISTINCT剔除重复的记录

select distinct dept,job from emp;

1.4.2 WHERE子句查询

1、WHERE子句查询语法：SELECT 列名称 | * FROM 表名称 WHERE 列 运算符值

WHERE子句后面跟的是条件，条件可以有多个，多个条件之间用连接词（or | and）进行连接。但是where后面不能使用多行函数。

2、下面的运算符可在 WHERE 子句中使用

逻辑运算符：&& || ！ and or not

模糊查询：
like
between and: 包含临界值，两个临界值不要调换顺序
in
not

is null ：仅仅可以判断null值，可读性较高，如果是返回1，否则，返回0 //is not null

<=> 安全等于: 既可以判断null值，又可以判断普通数值，可读性较低。

= 或<>不能用于判断null值
is null 或 is not null 可以判断null值

3、查询emp表中【薪资大于3000】的所有员工，显示员工姓名、薪资
select name, sal from emp where sal>3000;

4、查询emp表中【总薪资(薪资+奖金)大于3500】的所有员工，显示员工姓名、总薪资
ifnull(列名, 值)函数: 判断指定的列是否包含null值，如果有null值，用第二个值替换null值
select name, sal+ifnull(bonus,0) from emp where sal+ifnull(bonus,0) > 3500;

5、使用as可以为表头指定别名（格式：列名 as 别名），另外as可以省略
select name 姓名, sal+ifnull(bonus,0) 总薪资 from emp where sal+ifnull(bonus,0) > 3500;

起别名：

1. 便于理解；
   2）如果要查询的字段有重名的情况，使用别名可以区分开来方式：as 或者 使用空格；
   3）如果为表起别名，则查询的字段就不能使用原来的表名去限定。

6、查询emp表中【薪资在3000和4500之间】的员工，显示员工姓名和薪资
select name,sal from emp where sal>=3000 and sal<=4500;
and：必须同时满足and两边的条件

提示: between…and… 在…和…之间
select name,sal from emp where sal between 3000 and 4500;

或者使用 in，只要sal是in后面括号中的任何一个值，就算符合条件！
select name,sal from emp where sal in(1400,1600,1800);

7、查询薪资不为1400、1600、1800的员工，显示员工姓名和薪资
select name,sal from emp where not(sal=1400 or sal=1600 or sal=1800);

not表示对条件进行取反
select name,sal from emp where sal not in(1400,1600,1800);

8、查询没有部门的员工（即部门列为null值）
select * from emp where dept is null; – 查询dept列为null的
如何查询有部门的员工（即部门列不为null值）
select * from emp where not(dept is null); – 查询dept列不为null的
或者
select * from emp where dept is not null;

1.4.3模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
可以和通配符（%、）配合使用，其中"%“表示0或多个任意的字符,”"表示一个任意的字符。
语法：SELECT 列 | * FROM 表名 WHERE 列名 LIKE 值
示例：
– 25.查询emp表中姓名中以"刘"字开头的员工，显示员工姓名。
select name from emp where name like ‘刘%’;-- %表示匹配0个、1个或多个任意字符
– 26.查询emp表中姓名中包含"涛"字的员工，显示员工姓名。
select name from emp where name like ‘%涛%’; – %涛%, 表示匹配某一列包含涛的记录，'涛’可能在开头、结尾或中间某一个位置
– 27.查询emp表中姓名以"刘"开头，并且姓名为两个字的员工，显示员工姓名。
– 以’刘’开头，姓名为两个字的员工
select name from emp where name like ‘刘_’; – _(下划线)表示任意一个字符
– 以’刘’开头，姓名为三个字的员工
select name from emp where name like ‘刘__’;

1.4.4多行函数查询

多行函数也叫做聚合(聚集)函数，根据某一列或所有列进行统计。
常见的多行函数有：
COUNT( 列名 | * ) 统计结果集中指定列的记录的行数。
MAX( 列名 ) 统计结果集中某一列值中的最大值
MIN( 列名 ) 统计结果集中某一列值中的最小值
SUM( 列名 ) 统计结果集中某一列所有值的和
AVG( 列名 ) 统计结果集中某一列值的平均值
提示：
1）多行函数不能用在where子句中
2）多行函数和是否分组有关，分组与否会直接影响多行函数的执行结果。
3）多行函数在统计时会对null值进行过滤，直接将null值丢弃，不参与统计。
特点：
1.sum,avg 一般用于处理数值类型
2.max，min、count可以处理任何类型
3.都忽略null值
4.都可以搭配distinct使用，实现去重的统计。select max(distinct 字段) from 表
count计算效率
count(salary)/count()/count(1)
myisam存储引擎下，count（）效率高
innodb存储引擎下，count（*）和count（1）的效率差不多，比count（字段）要高一些

– 28.统计emp表中薪资大于3000的员工个数
– 查询薪资大于3000的员工有哪些？
select * from emp where sal>3000;
– count:用于统计行数
select count(*) from emp where sal>3000; – 7
select count(id) from emp where sal>3000; – 7
select count(bonus) from emp where sal>3000; – 6
– 由于bonus中有null值，在统计时，null值直接别丢弃，不参与统计
– 29.求emp表中的最高薪资
– emp表中的最高薪资（max函数）select max(sal) from emp;
– emp表中的最低薪资（min函数）select min(sal) from emp;
– 求emp表中薪资最高的员工select name,max(sal) from emp;
– 上面的结果，max(sal)是求薪资中的最大值，而name只是name列中的第一个姓名，因此name和max(sal)很可能是不对应的。因此结果是错的！
– 30.统计emp表中所有员工的薪资总和(不包含奖金)
select sum(sal) from emp; – 求薪资这一列所有值的和
select sum(bonus) from emp; – 求奖金这一列所有值的和
– 31.统计emp表员工的平均薪资(不包含奖金)
select avg(sal) from emp; – 薪资平均值（总薪资/12）
select avg(bonus) from emp; – 奖金平均值（总奖金/11）,有null值

多行函数需要注意的问题:
1）多行函数和是否分组有关，如果查询结果中的数据没有经过分组，默认整个查询结果是一个组，多行函数就会默认统计当前这一个组的数据。产生的结果只有一个。
2）如果查询结果中的数据经过分组（分的组不止一个），多行函数会根据分的组进行统计，有多少个组，就会统计出多少个结果。
例如：根据性别对所有员工进行分组，再统计每组的人数，显示性别和对应人数
– 根据性别分组，最终分为两个组，统计两个组的人数（10、2）
select gender,count(*) from emp group by gender;

1.4.5分组查询

分组前筛选 原始表 group by 子句前面 where
分组后的筛选 分组后的结果集 group by子句后面 having
GROUP BY 语句根据一个或多个列对结果集进行分组。也可添加排序，排序放在整个分组查询的最后。
在分组的列上我们可以使用 COUNT，SUM，AVG，MAX，MIN等函数。
语法：SELECT 列 | * FROM 表名 [WHERE子句] GROUP BY 列;
– 32.对emp表，按照部门对员工进行分组，查看分组后效果。
– 按照部门分组（3个组） select * from emp group by dept;
– 对上面3个组的人数进行统计 select dept,count(*) from emp group by dept;

1.4.6排序查询

使用 ORDER BY 子句将结果集中记录根据指定的列排序后再返回。
order by 的位置一般放在查询语句的最后（除limit之外）
语法：SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC]
ASC(默认)升序，即从低到高；DESC 降序，即从高到低。
– 35.对所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资。
– 按照薪资进行排序，默认是升序（asc），即从低到高
select name,sal from emp order by sal;
select name,sal from emp order by sal asc;
– 按照奖金进行降序排序
select name,bonus from emp order by bonus desc;

1.4.7分页查询

在mysql中，通过limit进行分页查询，查询公式为：
limit (页码-1)*每页显示记录数, 每页显示记录数 放在查询语句的最后。
– 37.查询emp表中的所有记录，分页显示：每页显示3条记录，返回所有页的数据。
– 分页查询：每页显示3条，返回第1页数据：
select * from emp limit 0, 3;
– 分页查询：每页显示3条，返回第2页数据：
select * from emp limit 3, 3;
– 分页查询：每页显示3条，返回第3页数据：
select * from emp limit 6, 3;
– 分页查询：每页显示3条，返回第4页数据：
select * from emp limit 9, 3;
– 38.求emp表中薪资最高的前3名员工的信息，显示姓名和薪资
– 按照薪资对员工信息进行排序，降序排序
select name,sal from emp order by sal desc;
– 在上面查询的基础上，分页查询，每页显示3条，查询第一页
select name,sal from emp order by sal desc limit 0, 3;

1.4.8其他函数

函数名 解释说明
curdate() 获取当前日期，格式是：年月日
curtime() 获取当前时间 ，格式是：时分秒
sysdate()/now() 获取当前日期+时间，格式是：年月日 时分秒
year(date) 返回date中的年份
month(date) 返回date中的月份
day(date) 返回date中的天数
hour(date) 返回date中的小时
minute(date) 返回date中的分钟
second(date) 返回date中的秒
version（） 当前数据库的版本
database（） 当前打开的数据库
user（） 当前用户
password（‘字符’）返回该密码的字符

CONCAT(s1,s2…) 将s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x,s1,s2…) 同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x，x是分隔符
– 41.查询emp表中员工的姓名和薪资（薪资格式为: xxx(元) 、 xxx/元）
select name,concat(sal,’(元)’) from emp;
select name,concat(sal,’/元’) from emp;
select name,concat_ws(’/’,sal,‘元’) from emp;

str_to_date(‘9-13-1999’,’%m-%d-&y’) 将日期格式的字符转换成指定格式的日期 1999-09-13
date_format(‘2018/6/6’,’%Y年%m月%d日’): 将日期转换成字符 2018年06月06日
% Y --四位的年份
% y --两位的年份
%m --月份（01,02，…11,12）
%c --月份（1,2，…11,12）
%d --日（01,02，…）
%H --小时（24小时制）
%h --小时（12小时制）
%i --分钟（00,01，…59）
%s --秒（00,01，…59）

– 39.查询emp表中所有【在1993和1995年之间出生】的员工，显示姓名、出生日期。
select name,birthday from emp where birthday between 1993 and 1995;
– birthday(日期) 和 1993、1995不能比较，因为类型不同
– 解决方案1：将1993、1995转换成日期后，再和birthday进行比较
select name,birthday from emp where birthday between ‘1993-1-1’ and ‘1995-12-31’;
– 解决方案2：将birthday中的年份取出，和1993、1995进行比较
select name,birthday from emp where year(birthday) between 1993 and 1995;

– 40.查询emp表中本月过生日的所有员工
– 求本月过生日的员工
select * from emp where month(now())=month(birthday);
– 求下个月过生日的员工
select * from emp where month(now())+1=month(birthday); – 问题：如果当前是12月份
select * from emp where ( month(now())+1 )%12=month(birthday); – 问题：如果当前是11月份
– 最终版本：求下个月过生日的员工
select * from emp where ( month(now())+1 )%12=month(birthday)%12;

表的索引

查看表中所有的索引，包括主键、外键、唯一
SHOW INDEX FROM stu;
数据库中 字符串的索引 开始于1

索引分类
1、普通索引 2、唯一索引 3、全文索引 4、组合索引

普通索引：仅加速查询，最基本的索引，没有任何限制

唯一索引：加速查询 + 列值唯一（可以有null）

全文索引：仅适用于MyISAM引擎的数据表，作用于char、varchar、text数据类型的列

组合索引：将几个列作为一条索引进行检索，使用最左匹配原则

–索引用于快速找出在某个列中有一特定值的行，不使用，MySQL必须从第一条记录开始读完整个表

– 优点
1、所有的MySQL列类型（字段类型）都可以被索引，也就是可以给任意字段设置索引
2、大大加速数据的查询速度
– 缺点
1、创建索引和维护索引要耗费时间
2、对表的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度

create table healerjean (
id int AUTO_INCREMENT PRIMARY KEY COMMENT ‘主键’,
name VARCHAR(32) NOT NULL COMMENT ‘姓名’,
email VARCHAR(64) NOT NULL COMMENT ‘邮箱’,
message text DEFAULT NULL COMMENT ‘个人信息’
#INDEX index_name (name) COMMENT ‘索引name’
) COMMENT = ‘索引测试表’;
（1）ALTER TABLE
创建普通索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
创建唯一索引
ALTER TABLE table_name ADD UNIQUE (column_list);
创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。
（2）CREATE INDEX
CREATE INDEX 可对表增加普通索引或UNIQUE索引。
创建普通索引
CREATE INDEX index_name ON table_name (column_list);
创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_list);
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。
需要注意的是不能用CREATE INDEX语句创建PRIMARY KEY索引。

–删除索引
drop index index_name on table_name;
ALTER TABLE table_name DROP INDEX index_name;
删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY;
其中，前两条语句是等价的，删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

表的约束

1、六大约束
1）、NOT NULL：非空，用于保证该字段的值不能为空，比如姓名、学号、密码
如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。
create table user(
password varchar(50) not null,
…
);

2）、DEFAULT:默认，用于保证该字段有默认值，比如性别

3）、PRIMARY KEY：主键，用于保证该字段的值具有唯一性，不能为空
主键的作用: 作为一个唯一标识，唯一的表示一条表记录（作用类似于人的身份证号，可以唯一的表示一个人一样。）
create table stu(
id int primary key,
…
);
如果主键是数值类型，为了方便插入主键（并且保证插入数据时，主键不会因为重复而报错），可以设置一个主键自增策略。
create table stu(
id int primary key auto_increment,
…
);
主键自增策略是指：设置了自增策略的主键，可以在插入记录时，不给id赋值，只需要设置
一个null值，数据库会自动为id分配一个值（AUTO_INCREMENT变量，默认从1开始，后面依
次+1），这样既可以保证id是唯一的，也省去了设置id的麻烦。

4）、UNIQUE：唯一，用于保证该字段的值具有唯一性，既不能重复，可以为空，比如座位号
create table user(
username varchar(50) unique not null,
…
);

5）、CHECK：检查约束[mysql中不支持]: gender CHAR(1) CHECK(gender=‘男’ OR gender=‘女’)

6）、FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表中添加外键约束，用于引用主表中某列的值 参考的外键应该是主表的主键，否则报错
create table emp(
id int,
name varchar(50),
dept_id int,
foreign key(dept_id) references dept(id)
);
如果将dept_id列设置为外键，等同于通知数据库，部门表和员工表之间存在对应关系，
dept_id列中的数据要参考部门的主键，数据库一旦知道部门和员工表之间存在关系，就会帮我
们维护这层关系。

2、添加约束的时机：
1.创建表时 2.修改表时

3、 约束添加分类：
列级约束(都支持，但外键约束没有效果)；
表级约束(除非空、默认外都支持)。

4、主键和唯一的对比：
1.主键：保证唯一性，不允许为空；
2.唯一：保证唯一性，允许为空。
3.都允许组合，但不推荐。

5、外键：
1）要求在从表中设置外键关系；
2）主表的关联列必须是一个key（一般是主键或唯一）
3）从表的外键列类型和主表的关联列的类型要求一致或兼容，名称无要求。
4）插入数据时，先插入主表，再插入从表。删除数据时，先删除从表，再删除主表。

6、修改表时添加约束
1.添加列级约束：alter table 表名 modify column 列名 类型 新约束；
2.添加表级约束：alter table 表名 add [constraint 约束名] 约束类型（列名） [外键的引用]；

视图

1、视图（mysql从5.0.1开始的的功能）
一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。
应用场景：多个地方用到同样的查询结果 或 该查询结果使用的sql语句较复杂
示例： create view v1 as select name,job from emp e inner join dept p on e.‘deptid’=p.‘id’;
select *from v1 where name like ‘张%’;

好处：重用sql语句；简化复杂的sql操作，不必知道它的查询细节；保护数据，提高安全性。

视图的修改：
方式一：create or replace view 视图名 as 查询语句;
方式二：alter view 视图名 as 查询语句;

视图的删除：drop view 视图名 ， 视图名，…

查看视图：desc v1; 或 show create view v1;

视图的更新：1.插入：insert into v1 values(‘zhangfei’,‘zf@qq.com’);
2.修改：update v1 set name=‘张飞’where 筛选条件
3.删除：delete from v1 where name = ‘张飞’；

以下类型视图是不能更新的：
1.包含以下关键字的sql语句：分组函数，distinct、group by、having 、union或者union all；
2.select中包含子查询；
3.join；
4.from一个不能更新的视图；
5.where 子句的子查询引用了from子句中的表。

存储引擎和JVM调优

存储引擎：在mysql中的数据用各种不同的技术存储在文件（或内存）中。

1. 通过show engines;来查看mysql支持的存储引擎；
2. 在mysql中用的最多的存储引擎有：innodb, myisam, memoryd等。其中innodb支持事务，而myisam, memoryd等不支持事务。

创作不易，转载请注明出处

备注

BLOB是二进制大对象的意思。我们可以将一张图像文件，如JPEG 文件，放进BLOB 列。但这种做法通常是不好的，因为它会使得表变大，导致备份困难。更好的做法是将图像文件放在文件系统中，然后将其文件路径或URL 地址存进数据库，以指示如何找到该文件。

ADD COLUMN endangered BIT DEFAULT b’1’ AFTER bill_id,
BIT 数据类型，只占一位，状态有两种：1 代表有设值，0 代表没设值。用DEFAULT 指定了该列的默认值。还有，为了给位类型设值，我们需要将值用引号包围，并在前面加上字母b。这种数据类型有个问题。它确实有保存值，但就是显示不出值。如果有设值，则查询时不会显示任何东西，导致ASCII 形式的结果集会在该位置有一个向左的缩进。
对于BIT 类型的列来说，endangered 的意思等同于endangered = 1。如果想选取没设置endangered 的行，则可使用NOT 运算符：
SELECT bird_id, scientific_name FROM birds_new WHERE endangered \G SELECT * FROM birds_new WHERE NOT endangered \G
SHOW COLUMNS FROM birds_new LIKE ‘endangered’ \G
用SHOW COLUMN 语句加上LIKE 子句，结果仅显示endangered 列的设定：