MySQL语法及基础知识大全
前段时间应聘一家公司面试时对方让我写一个联表查询的语句，我居然没有写出来然后就没有然后了，后来反省了一段时间，本人是搞Java后台开发的如果数据库的基本语法都不熟练的话那基本GG，所以之后花了一周时间梳理了一下MySQL的基本知识记录下来与大家一同分享。
使用CMD操作数据库的流程如下：
登录MySQL：进入CMD之后，输入mysql -u root -p 之后按提示输入密码
show databases; 显示所有的数据库
use databasename; 选择使用哪个数据库
show talbes; 查看该数据库中有哪几张表
一、数据库操作
查看当前数据库：show databases;
创建数据库:create database database_name;
查看创建好的数据库的定义:show create database database_name;
删除数据库:drop database database_name;
查看所支持的存储引擎：show engines
查看事务：select @@tx_isolation;
修改当前事务：set tx_isolation='repeatable-read';
set session transaction isolation level repeatable read;
二、创建表
主键约束：primary key。主键可分为单主键和多字段联合主键
外键约束：foreign key
Constraint 外键名称 foreign key(表中外键名) references关联表名(关联属性);
非空约束：not null
唯一性约束：unique
默认约束：default （所默认的值）
属性值自动添加：auto-increment
查看表的结构：describe 表名 或 desc 表名
查看表的详细结构：show create table 表名
三、修改表
修改表名：alter table 旧表名 rename 新表名
修改字段的数据类型：alter table 表名 modify 字段名 新类型
修改字段名：alter table 表名 change 旧字段名 新字段名 新数据类型
添加字段：alter table 表名 add 新字段 数据类型(first)|(after已存在的字段名)最后俩个属性可以调整新插入的字段的位置 first表示将此字段插入到表头，after表示将此字段插入到某行之后。
删除字段：alter table 表名 drop 字段名
修改字段的排列位置：alter table 表名 modify 字段 数据类型first|after字段2
修改表的存储引擎：alter table 表名 engine=myisam(MySQL支持的数据库引擎有：myisam、InnoDB、memory、archive、BDB等)
修改表的编码方式：alter table 表名 default character set utf8;（该命令将表的默认编码方式改为utf8）
修改字段的编码方式：alter table 表名 change 字段名 字段名 数据类型character set utf8 ;（该语法类似于修改字段名的语法，只是在后面添加character set!所以也可以用于修改字段名）
删除表的外键约束:alter table 表名 drop foreign key 外键约束名
删除未被关联的表：drop table if exists 表名；
要删除被外键关联的主表先删除关联外键再删除主表即可！
四、MySQL数据类型
MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。
数值类型包括：整数类型tinyint、smallint、mediumint、int、bigint、浮点小数数据类型float、double、定点小数类型decimal。
日期/时间类型：包括year、time、date、datetime和timestamp。
字符串类型：包括char、varchar、binary、varbinay、blob、text、enum和set等。
整数类型：
|
类型名称 |
说明 |
存储需求 |
|
tinyint |
很小的整数 |
1个字节 |
|
smallint |
小的整数 |
2个字节 |
|
mediumint |
中等大小的整数 |
3个字节 |
|
Int（INTEGER） |
普通大小的整数 |
4个字节 |
|
bigint |
大整数 |
8个字节 |
浮点及定点数类型
|
类型名称 |
说明 |
存储需求 |
|
float |
单精度浮点数 |
4个字节 |
|
double |
双精度浮点数 |
8个字节 |
|
Decimal（M,D） , dec |
压缩的“严格”定点数 |
M+2个字节 |
日期与时间类型
|
类型名称 |
日期格式 |
日期范围 |
存储需求 |
|
year |
YYYY |
1901-2155 |
1个字节 |
|
time |
HH:MM:SS |
-838:59:59~838:59:59 |
3个字节 |
|
date |
YYYY-MM-DD |
1000-01-01~9999-12-3 |
3个字节 |
|
datetime |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 ~9999-12-31 23:59:59 |
8个字节 |
|
timestamp |
YYYY-MM-DD HH:MM:SS |
1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 utc |
4个字节 |
字符串类型
|
类型 |
说明 |
存储要求 |
|
Char(m) |
固定长度非二进制字符串 |
M字节 1<=m<=255 |
|
Varchar(m) |
变长非二进制字符串 |
L+1字节 在此L<=M和1<=M<=255 |
|
tinytext |
非常小的非二进制字符串 |
L+1字节 在此L<2^8 |
|
text |
小的非二进制字符串 |
L+2字节 在此L<2^16 |
|
mediumtext |
中等大小的非二进制字符串 |
L+3字节 在此L<2^24 |
|
longtext |
大的非二进制字符串 |
L+4字节 在此L<2^32 |
|
enum |
枚举类型 只能有一个枚举字符串值 |
1或2个字节，取决于枚举值得数目(最大值65535） |
|
set |
字符串对象 可以有零个或多个set成员 |
1、2、3、4或8个字节，取决于集合成员的数目（最多64个成员） |
二进制类型
二进制类型可以用来存放图片、音频信息等
|
类型 |
说明 |
存储要求 |
|
Bit(m) |
位字段类型 |
大约（M+7）/8个字节 |
|
Binary(m) |
固定长度二进制字符串 |
M个字节 |
|
Varbinary(m) |
可变长度二进制 |
M+1个字节 |
|
Tinyblob(m) |
非常小的blob |
L+1字节 在此L<2^8 |
|
Blob(m) |
小blob |
L+2字节 在此L<2^16 |
|
Mediumblob(m) |
中等大小的blob |
L+3字节 在此L<2^24 |
|
Longblob(m) |
非常大的blob |
L+4字节 在此L<2^32 |
五、索引
含义及特点
索引是一个单独的、存储在磁盘上的数据库结构，包含对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行（索引可对某行进行排序），MYSQL所有的列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
索引的优点：
1、通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
2、可以大大加快数据的查询速度，这也是创建索引最主要的原因
3、在实现数据的参照完整性方面，可以加速表和表之间的连接。
4、在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。
不利的方面：
1、创建和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
2、除了数据表要占数据空间之外，每一个索引还要占用一定的物理空间，增加了物理开销
3、当对表中数据进行增，删，改时，索引也要动态地维护，这样就降低了数据的维护速度。
索引的分类：普通索引（允许重复值和空值）、唯一索引（索引值唯一）、单列索引（索引中只包含一列）、组合索引（多个字段组合而成）、全文索引（支持值的全文查找）、空间索引（对空间数据类型的字段建立的索引）。
索引的设计原则:
1、索引并非越多越好
2、避免对经常更新的表进行过多的索引，并且索引的列应尽可能的少
3、数据量小最好不要使用索引
4、在条件表达式中经常会用到的不同值较多的列上建立索引。
5、当唯一性是某个数据本身的特征时，指定唯一索引。
6、在频繁进行排序或分组的列上建立索引。
创建索引
1、在建表时创建索引
（1）创建普通索引：最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度
Create table book
(
bookid int not null,
bookname char(50) not null,
year_publication year not null,
Index(year_publication)
); 使用show index from book \g 可以查看该表的所有索引
使用 show create table book \g; 也可以查看索引
使用explain select * from book where year_publication=1990 \g查看是否使用索引
（2）创建唯一索引：唯一索引与前面普通索引类似但唯一索引值必须唯一，可以有空值。
使用unique关键字来创建唯一索引
Create table t1
(
id int not null,
Name char(20) not null,
Unique Index indexname(id)
); 同样可以使用上述俩个语句来测试该索引
（3）创建单列索引：在数据库表中的一个字段创建的索引
Create table t2
(
id int not null,
name char(50) not null,
Index indexname(name(20))
创建了一个长度为20的索引
);
（4）创建组合索引：在多个字段上创建索引
Create table t3
(
id int not null,
name char(20) not null,
Age int not null,
Index indexname(id,name,age(100))
);
（5）建立全文索引：fulltext全文索引可用于全文搜索，但只有MYISAM存储引擎支持fulltext索引，并且只能在数据类型为：char、varchar、text的列上建立全文索引。
Create table t4
(
Id int not null,
name char(30) not null,
age int not null,
Info varchar(255),
Fulltext index indexname(info)
) engine=muisam;
（6）创建空间索引：该索引必须在MYISAM类型的表中创建 应建立在空间类型上（geometry,point,linestring,polygon），且空间类型的字段不能为空
Create table t5
(
g geometry not null,
Spatial index indexname(g)
)engine=MYISAM;
2、在已有的表中创建索引
在已有的表中创建索引，可以使用alter table或者create index语句
已第一次建立的book表为例：
Alter table book add index indexname(bookname(30));
Create index indexname on book(bookname);
这里已建立普通索引为例，建立其他类型的索引及查看索引信息语句已在上面讲的很清楚了就不在赘述。
3、删除索引
在MySQL中删除索引有俩种方式：alter table或者drop index语句。
（1）、alter table tablename drop index indexname;
（2）、drop index indexname on tablename;
六、视图
定义：视图是一张虚拟的表，是从数据库中的一张或多张表中导出来。它可以实现当你只需要某几张表中的几个属性信息时，便可以使用视图对该几列的信息进行提取。其中的数据与数据表中的数据一一对应，对通过视图看到的数据进行修改时，相应的基本表的数据也要发生改变，同时，若基本表数据发生改变则这种变化可以自动反应到视图中。
建立俩张表为例：
create table student
(
id int(8) not null primary key,
name char(10) not null,
gender char(1),
age int(3)
);
create table score
(
id int(8) not null primary key,
subjectid int(3) not null,
studentid int(8) not null,
score int(3) not null,
constraint fk_id foreign key(studentid) references student(id)
);
创建视图(创建一个学生成绩对应科目的视图）
create view stu_glass (subjectid,studentid,score) as select score.subjectid,student.id,score.score from score,student where score.studentid=student.id;
修改视图
alter view stu_glass as select studentid,score from score;
更新视图
update stu_glass set score = 100;
删除视图
drop view if exists stu_glass;
七、基本的增删改查
以学生成绩表为例插入数据
指定插入值：insert into student (id,name,gender,age) value (1,”jack”,”B”,23),(2,”rose”,”g”,20);
不指定插入值，则插入值应与建表时的属性顺序相同：
insert into student value (3,”tom”,”G”,19);
更新数据:update student set aeg=15,name=”wq” where id=1;
删除数据：delete from student where id=2;
八、MySQL函数
Mysql提供了丰富的函数，在进行数据库管理以及数据的查询和操作时将会经常用到这些函数。MySQL中的函数从功能方面主要分为数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数。若是对这方面有兴趣的可以自己去查阅相关资料这里就不再赘述。
九、查询语句（重点）
select语句的基本格式如下：
select
* 或 <字段列表>
from 表
where 表达式
group by（分组，按照指定的字段分组）
Having（）
order by（排序，asc升序、desc降序）
limit （分组查询）
还是以学生、成绩表为例。数据自己插入
create table student
(
id int(8) not null primary key,
name char(10) not null,
gender char(1),
age int(3)
);
create table score
(
id int(8) not null primary key,
subjectid int(3) not null,
studentid int(8) not null,
score int(3) not null,
constraint fk_id foreign key(studentid) references student(id)
);
简单的不多说，直接上干货！
（1）带in关键字的查询：
In操作符用来查询满足指定范围的记录，使用in操作符时，将所有检索条件用括号括起来，检索条件之间用逗号分隔，只要满足条件范围内的一个值即为匹配值。
Select * from score where score in (85,95) ;
查询成绩为85和95的记录
Select * from score where score not in (85,95) ;
查询成绩不为85和95的记录
（2）带between and的范围查询
Between and 用来查询某个范围内的值，该操作符需要俩个参数，即范围开始值和结束值。如果字段值满足指定的范围查询条件，则这些记录被返回。
Select * from score where score between 80 and 95;
查询成绩在80和95之间的记录
（3）带like的字符匹配查询
字符匹配查询like一般与“%”、“_”一起使用，代表该字符中存在某个字符。
百分号通配符：百分号通配符可以匹配任意长度的字符，甚至包括空字符
查询以j开头的学生的记录：
Select * from student where name like ‘j%’;
查询名字中带b字母的学生记录：
Select * from student where name like ‘%b%’;
下划线通配符_
下划线通配符_一次只能匹配任意一个字符。
查询以r开头并且后面只有三个字符的名字的学生信息
Select * from student where name like ‘r___’;
(4)查询结果不重复(distinct)
Select distinct 字段名 from表名
(5)对查询结果排序(order by)
默认的或ASC为升序排列，desc为降序排列
Select * from score order by studentid desc ,score ；
即先按学生ID降序排列，若学生ID中有相同的值按升序排序。
（6）使用limit限制查询结果的数量
语法为:
Limit [位置偏移量,]行数
位置偏移量表示从第一行开始显示，是一个可选参数，若没有则表示从第一行（0）开始，行数表示要显示几行。
如：select * from student limit 4,3;
表示返回从第五条记录开始以后的3条记录。
（7）分组查询group by
分组查询是对数据按照某个或多个字段进行分组
语法为：[group by 字段] [having <条件表达式> ]
“字段”为进行分组时所依据的列名称； having<条件表达式>指定满足表达式限定条件的结果将被显示。分组查询一般与集合函数、having一起使用。
集合函数：
Count（） 返回某列的行数
Sum（） 返回某列值的和
Avg() 返回某列的平均值
Max（） 返回某列的最大值
Min（） 返回某列的最小值
Count（）函数
Count（*）计算表中总的行数，不管某列有数值或者为空值。
Count（字段名）计算指定列下总的行数，计算时将忽略空值的行。
如：select subjectid , count(*) as total from score where score>60 and studentid in (select id from student where age>18 and gender=’B’) group by subjectid having count(*) > 10;
查询所有科目中年龄大于18岁且数量多于10的及格男生人数。
Sum（）函数
Sum（）是一个求和函数，返回指定列值的总和
Select subjectid , sum(score) as sum from score group by subjectid;
查询每门科目的成绩之和
Avg（）函数
Avg（）函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
Select subjectid ,avg(score) as avg from score group by subjectid;
查询每门科目的平均值
Max（）函数
Max（）返回指定列的最大值
Select subjectid , max(score) as maxscore from score group by subjectid;
查询每门科目的最高成绩
Min（）函数
Min（）返回指定列的最小值
Select subjectid , min(score) as minscore from score group by subjectid;
（8）连接查询
内连接查询（inner join）是使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。
Select student.id, subjectid ,name ,score from score inner join student on student.id=score.studentid;
外连接查询（分为左连接和右连接）：查询在俩张表中都存在的数据使用内连接查询，当数据在一张表中存在另一张表中不存在时就得使用外连接查询。
如： select score.name,score.subjectid,score.score from student left outer join score where student.id = score.studentid;
此为查询所有学生的各科目的成绩，有些学生的某门科目的成绩可能为null，因为其未参加考试。
（9）子查询
子查询是指一个查询语句嵌套在另一个查询语句内部的查询，子查询中常用的操作符有any(some)、all、in、exists.
带any some关键字的子查询：
关键字any和some是同义词，只要满足其中任一条件就可以返回查询结果。
Select studentid ,score from score where subjectid=1 and score > any (select score from score where subjectid=2);
查询科目一中成绩大于科目二中任一成绩的学生ID，及成绩。
带all关键字的子查询：
Select studentid ,score from score where subjectid=1 and score > all (select score from score where subjectid=2);
查询科目一中成绩大于所有科目二中学生成绩的信息。
带exists关键字的子查询:
Exists关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么exists的结果为TRUE，此时外层将进行查询；如果子查询没有返回任何一行，那么exists返回的结果是FALSE，此时外层语句将不再进行查询。注意内层和外层语句没有直接关系，只是内层如果返回了TRUE外层就执行，否则不执行。
如：select * from score where exists ( select name from student where age>20);
如果有学生年龄大于20就查询成绩表中所有的信息。
带in关键字的子查询：
用in关键字进行子查询时，内层查询语句只返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。
如：select * from student where id in (select studentid from score where subjectid=1 and score>90);
查询在科目一中考试成绩在90分以上的学生信息。
带比较运算符的子查询与in关键字的类似即把in换为”<” , “<=” , “=” , “>=” , “!=”等以实现不同的功能。
合并查询结果：
利用union关键字可以给出多条select语句，并将它们的结果组合成一个结果集。合并时俩个表对应的列数和数据类型必须相同。只使用union执行时会删除重复的记录，使用关键字all之后不删除重复行也不对结果进行自动排序
基本语法为：
Select column , ... From table1
Union [all]
Select column,...from table2
如：select * from student where gender=”B”
Union all
Select * from student where age>19;
查询性别为男 和 年龄大于19岁的结果集的并集。并不是说必须要同时满足这俩个条件而是先执行第一个查询 再执行第二个查询最后将俩个查询合并！
