MySQL 学习笔记 一
本文内容纲要：
一、数据库简单介绍
1、按照数据库的发展时间顺序，主要出现了以下类型数据库系统：
Ø网状型数据库
Ø层次型数据库
Ø关系型数据库
Ø面向对象数据库
上面4中数据库系统中，关系型数据库使用最为广泛。面向对象数据库则是由面向对象语言催生的新型数据库，目前的一些数据库系统，如：SQLServer2005、Oracle10g等都开始增加面向对象的特性。
二、常用基本SQL语句/语法
ØSQL****语句基础理论
SQL是操作和检索关系型数据库的标准语言，标准SQL语句可用于操作然后关系型数据库。
标准的SQL语句通常划分为以下类型：
**查询语句：**主要由于select关键字完成，查询语句是SQL语句中最复杂，功能最丰富的语句。
DML（DataMunipulationLanguage，数据操作语言）语句，这组DML语句修改后数据将保持较好的一致性；操作表的语句，如插入、修改、删除等；
DDL（DataDefinitionLanguage，数据定义语言）语句，操作数据对象的语言，有create、alter、drop。
DCL（DataControlLanguage，数据控制语言）语句，主要有grant、revoke语句。
**事务控制语句：**主要有commit、rollback和savepoint三个关键字完成
DDL**语句
DDL语句是操作数据库对象的语句，包括创建create、删除drop、修改alter数据库对象。
常见数据库对象
对象名称 |
对应关键字 |
描述 |
表 |
table |
表是数据库存储的逻辑单元，以行和列的形式存在；列是字段，行就是一条数据记录 |
数据字典 |
就是系统表，存储数据库相关信息的表，系统表里的数据通常有数据库系统维护。系统表结构和数据，开发人员不应该手动修改，只能查询其中的数据 |
|
视图 |
view |
一个或多个数据表里的数据的逻辑显示，视图就是一张虚拟的表，并不真正存储数据 |
约束 |
constraint |
执行数据检验规则，用于保证数据完整性的规则 |
索引 |
index |
用于提高查询性能，相当于书的目录 |
函数 |
function |
用于完成一个特定的计算，具有返回值和参数 |
存储过程 |
procedure |
完成某项完整的业务处理，没有返回值，但可通过传出参数将多个值传个调用环境 |
触发器 |
trigger |
相当于一个事件的监听器，当数据库发生特定的事件后，触发器被触发，完成响应处理 |
上面的对象都可以通过用create、alter、drop完成相关的创建、修改、删除操作。
常用数据类型
列类型 |
说明 |
tinyint/smallint/mediumintint(integer)/bigint |
1字节、2字节、3字节、4字节、8字节整数，又可分有符号和无符号两种。这些整数类型的区别仅仅表现范围不同 |
float/double |
单精度、双精度浮点类型 |
decimal(dec) |
精确小数类型，相当于float和double不会产生精度丢失问题 |
date |
日期类型，不能保存时间。当Java里的Date对象保存到该类型中，时间部分丢失 |
time |
时间类型，不能保存日期。当Java的Date对象的保存在该类型中，日期部分丢失 |
datetime |
日期、时间类型 |
timestamp |
时间戳类型 |
year |
年类型，仅保存年份 |
char |
定长字符串类型 |
varchar |
可变长度字符串类型 |
binary |
定长二进制字符串类型，它以二进制形式保存字符串 |
varbinary |
可变长度的二进制字符串类型，二进制形式保存字符串 |
tingblob/blob mediumblob/longblob |
1字节、2字节、3字节、4字节的二进制大对象，可存存储超图片、音乐等二进制数据，分别可存储：255/64K/16M/4G的大小 |
tingtext/text mediumtext/longtext |
1字节、2字节、3字节、4字节的文本对象，可存储超长长度的字符串，分别可存储：255/64K/16M/4G的大小的文本 |
enum(‘val1’,‘val2’,…) |
枚举类型，该列的值只能是enum括号中出现的值的之一 |
set(‘value1’,‘value2’,…) |
集合类型，该列的值可以是set中的一个或多个值 |
Ø常用查询
MySQL结束符是“；”结束。
1、显示所有数据库
showdatabases;
2、删除数据库
dropdatabasedbName;
3、创建数据库
createdatabase[ifnotexists]dbName;
中括号部分可选的，判断该数据不存在就创建
4、切换、使用指定数据库
usedbName;
5、显示当前使用数据库所有的表对象
showtables；
6、显示表结构describe（desc）
desctableName;
7、创建一张表
createtableuser(
--int整型
uIdint,
--小数
uPricedecimal,
--普通长度文本，default设置默认值
uNamevarchar(255)default‘zhangsan’,
--超长文本
uRemarktext,
--图片
uPhotoblob,
--日期
uBirthdaydatetime
);
8、子查询建表方法
部分列名匹配模式：
createtableuserInfo(
namevarchar(20),
sexchar
)
as
selectname,sexfromuser;
上面的列名和子查询的列名以及类型要对应
全部列名模式：
createtableuserInfo
as
select*fromuser;
直接将整个表的类型和数据备份到新表userInfo中
9、添加表字段
添加单列
altertableuseraddtelvarchar(11)default‘02012345678’;
添加多列
altertableuser
add(
photoblob,
birthdaydate
);
上面就同时增加了多列字段
10、修改表字段
修改tel列
altertableusermodifytelvarchar(15)default‘02087654321’;
修改tel列的位置，在第一列显示
altertableusermodifytelvarchar(15)default'02087654321'first;
修改tel列的位置，在指定列之后显示
altertableusermodifytelvarchar(15)default'02087654321'afterage;
注意：altermodify不支持一次修改多个列，但是Oracle支持多列修改
但是MySQL可以通过多个modify的方式完成：
altertableuser
modifytelvarchar(15)default'02087654321'first,
modifynamevarchar(20)aftertel;
11、删除指定字段
altertableuserdropphoto;
12、重命名表数据
表重命名
altertableuserrenametousers;
字段重命名
altertableuserschangenameu_namevarchar(10);
altertableuserschangesexu_sexvarchar(10)afteru_name;
如果需要改变列名建议使用change，如果需要改变数据类型和显示位置可以使用modify
13、删除表
droptableusers;
drop删除表会删除表结构，表对象将不存在数据中；数据也不会存在；表内的对象也不存在，如：索引、视图、约束；
truncate删除表
truncate都被当成DDL出来，truncate的作用就是删除该表里的全部数据，保留表结构。相当于DDL中的delete语句，
但是truncate比delete语句的速度要快得多。但是truncate不能带条件删除指定数据，只会删除所有的数据。如果删除的表有外键，
删除的速度类似于delete。但新版本的MySQL中truncate的速度比delete速度快。
Ø约束
MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；
约束主要完成对数据的检验，保证数据库数据的完整性；如果有相互依赖数据，保证该数据不被删除。
常用五类约束：
notnull：非空约束，指定某列不为空
unique：唯一约束，指定某列和几列组合的数据不能重复
primarykey：主键约束，指定某列的数据不能重复、唯一
foreignkey：外键，指定该列记录属于主表中的一条记录，参照另一条数据
check：检查，指定一个表达式，用于检验指定数据
MySQL不支持check约束，但可以使用check约束，而没有任何效果；
根据约束数据列限制，约束可分为：
单列约束：每个约束只约束一列
多列约束：每个约束约束多列数据
MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；
1、notnull约束
非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
Null类型特征：
所有的类型的值都可以是null，包括int、float等数据类型
空字符串“”是不等于null，0也不等于null
createtabletemp(
idintnotnull,
namevarchar(255)notnulldefault‘abc’,
sexcharnull
)
上面的table加上了非空约束，也可以用alter来修改或增加非空约束
增加非空约束
altertabletemp
modifysexvarchar(2)notnull;
取消非空约束
altertabletempmodifysexvarchar(2)null;
取消非空约束，增加默认值
altertabletempmodifysexvarchar(2)default‘abc’null;
2、unique
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null
同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。
唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。
MySQL会给唯一约束的列上默认创建一个唯一索引；
createtabletemp(
idintnotnull,
namevarchar(25),
passwordvarchar(16),
--使用表级约束语法，
constraintuk_name_pwdunique(name,password)
);
表示用户名和密码组合不能重复
添加唯一约束
altertabletempaddunique(name,password);
altertabletempmodifynamevarchar(25)unique;
删除约束
altertabletempdropindexname;
3、primarykey
主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；如果的多列组合的主键约束，
那么这些列都不允许为空值，并且组合的值不允许重复。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。MySQL的主键名总是PRIMARY，
当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
列模式：
createtabletemp(
/*主键约束*/
idintprimarykey,
namevarchar(25)
);
createtabletemp2(
idintnotnull,
namevarchar(25),
pwdvarchar(15),
constraintpk_temp_idprimarykey(id)
);
组合模式：
createtabletemp2(
idintnotnull,
namevarchar(25),
pwdvarchar(15),
constraintpk_temp_idprimarykey(name,pwd)
);
alter删除主键约束
altertabletempdropprimarykey;
alter添加主键
altertabletempaddprimarykey(name,pwd);
alter修改列为主键
altertabletempmodifyidintprimarykey;
设置主键自增
createtabletemp(
idintauto_incrementprimarykey,
namevarchar(20),
pwdvarchar(16)
);
auto_increment自增模式，设置自增后在插入数据的时候就不需要给该列插入值了。
4、foreignkey约束
外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
也就是说从表的外键值必须在主表中能找到或者为空。
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，
然后才可以删除主表的数据。还有一种就是级联删除子表数据。
注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录，
那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。
创建外键约束：
主表
createtableclasses(
idintauto_incrementprimarykey,
namevarchar(20)
);
从表
createtablestudent(
idintauto_increment,
namevarchar(22),
constraintpk_idprimarykey(id),
classes_idintreferencesclasses(id)
);
通常先建主表，然后再建从表，这样从表的参照引用的表才存在。
表级别创建外键约束：
createtablestudent(
idintauto_incrementprimarykey,
namevarchar(25),
classes_idint,
foreignkey(classes_id)referencesclasses(id)
);
上面的创建外键的方法没有指定约束名称，系统会默认给外键约束分配外键约束名称，命名为student_ibfk_n，
其中student是表名，n是当前约束从1开始的整数。
指定约束名称：
createtablestudent(
idintauto_incrementprimarykey,
namevarchar(25),
classes_idint,
/*指定约束名称*/
constraintfk_classes_idforeignkey(classes_id)referencesclasses(id)
);
多列外键组合，必须用表级别约束语法：
createtableclasses(
idint,
namevarchar(20),
numberint,
primarykey(name,number)
);
createtablestudent(
idintauto_incrementprimarykey,
namevarchar(20),
classes_namevarchar(20),
classes_numberint,
/*表级别联合外键*/
foreignkey(classes_name,classes_number)referencesclasses(name,number)
);
删除外键约束：
altertablestudentdropforeignkeystudent_ibfk_1;
altertablestudentdropforeignkeyfk_student_id;
增加外键约束
altertablestudentaddforeignkey(classes_name,classes_number)referencesclasses(name,number);
自引用、自关联（递归表、树状表）
createtabletree(
idintauto_incrementprimarykey,
namevarchar(50),
parent_idint,
foreignkey(parent_id)referencestree(id)
);
级联删除：删除主表的数据时，关联的从表数据也删除，则需要在建立外键约束的后面增加ondeletecascade
或ondeletesetnull，前者是级联删除，后者是将从表的关联列的值设置为null。
createtablestudent(
idintauto_incrementprimarykey,
namevarchar(20),
classes_namevarchar(20),
classes_numberint,
/*表级别联合外键*/
foreignkey(classes_name,classes_number)referencesclasses(name,number)ondeletecascade
);
5、check约束
MySQL可以使用check约束，但check约束对数据验证没有任何作用。
createtabletemp(
idintauto_increment,
namevarchar(20),
ageint,
primarykey(id),
/*check约束*/
check(age>20)
);
上面check约束要求age必须大于0，但没有任何作用。但是创建table的时候没有任何错误或警告。
Ø索引
索引是存放在模式（schema）中的一个数据库对象，索引的作用就是提高对表的检索查询速度，
索引是通过快速访问的方法来进行快速定位数据，从而减少了对磁盘的读写操作。
索引是数据库的一个对象，它不能独立存在，必须对某个表对象进行依赖。
提示：索引保存在information_schema数据库里的STATISTICS表中。
创建索引方式：
自动：当表上定义主键约束、唯一、外键约束时，该表会被系统自动添加上索引。
手动：手动在相关表或列上增加索引，提高查询速度。
删除索引方式：
自动：当表对象被删除时，该表上的索引自动被删除
手动：手动删除指定表对象的相关列上的索引
索引类似于书籍的目录，可以快速定位到相关的数据，一个表可以有多个索引。
创建索引：
createindexidx_temp_nameontemp(name);
组合索引：
createindexidx_temp_name$pwdontemp(name,pwd);
删除索引：
dropindexidx_temp_nameontemp;
Ø视图
视图就是一个表或多个表的查询结果，它是一张虚拟的表，因为它并不能存储数据。
视图的作用、优点：
限制对数据的访问
让复杂查询变得简单
提供数据的独立性
可以完成对相同数据的不同显示
创建、修改视图
createorreplaceviewview_temp
as
selectname,agefromtemp;
通常我们并不对视图的数据做修改操作，因为视图是一张虚拟的表，它并不存储实际数据。如果想让视图不被修改，可以用withcheckoption来完成限制。
createorreplaceviewview_temp
as
select*fromtemp
withcheckoption;
修改视图：
alterviewview_temp
as
selectid,namefromtemp;
删除视图：
dropviewview_temp;
显示创建语法：
showcreateviewv_temp;
ØDML语句
DML主要针对数据库表对象的数据而言的，一般DML完成：
插入新数据
修改已添加的数据
删除不需要的数据
1、insertinto插入语句
insertintotempvalues(null,‘jack’,25);
主键自增可以不插入，所以用null代替
指定列
insertintotemp(name,age)values(‘jack’,22);
在表面后面带括号，括号中写列名，values中写指定列名的值即可。当省略列名就表示插入全部数据，
注意插入值的顺序和列的顺序需要保持一致。
Set方式插入，也可以指定列
insertintotempsetid=7,name='jason';
MySQL中外键的table的外键引用列可以插入数据可以为null，不参照主表的数据。
使用子查询插入数据
insertintotemp(name)selectnamefromclasses;
多行插入
insertintotempvalues(null,‘jack’,22),(null,‘jackson’23);
2、update修改语句
update主要完成对数据的修改操作，可以修改一条或多条数据。修改多条或指定条件的数据，需要用where条件来完成。
修改所有数据
updatetempsetname=‘jack2’;
所有的数据的name会被修改，如果修改多列用“,”分开
updatetempsetname=‘jack’,age=22;
修改指定条件的记录需要用where
updatetempsetname=‘jack’whereage>22;
3、delete删除语句
删除table中的数据，可以删除所有，带条件可以删除指定的记录。
删除所有数据
deletefromtemp;
删除指定条件数据
deletefromtempwhereage>20;
Øselect查询、function函数
select查询语句用得最广泛、功能也最丰富。可以完成单条记录、多条记录、单表、多表、子查询等。
1、查询某张表所有数据
select*fromtemp;
*代表所有列，temp代表表名，不带条件就查询所有数据
2、查询指定列和条件的数据
selectname,agefromtempwhereage=22;
查询name和age这两列，age等于22的数据。
3、对查询的数据进行运算操作
selectage+2,age/2,age–2,age*2fromtempwhereage–2>22;
4、concat函数，字符串连接
selectconcat(name,‘-eco’)fromtemp;
concat和null进行连接，会导致连接后的数据成为null
5、as对列重命名
selectnameas‘名称’fromtemp;
as也可以省略不写，效果一样
如果重命名的列名出现特殊字符，如“‘”单引号，那就需要用双引号引在外面
selectnameas“名’称”fromtemp;
6、也可以给table去别名
selectt.nameNamefromtempast;
7、查询常量
类似于SQLServer
select5+2;
selectconcat('a','bbb');
8、distinct去掉重复数据
selectdistinctidfromtemp;
多列将是组合的重复数据
selectdistinctid,agefromtemp;
9、where条件查询
大于>、大于等于>=、小于<、小于等于<=、等于=、不等于<>
都可以出现在where语句中
select*fromtwherea>2ora>=3ora<5ora<=6ora=7ora<>0;
10、and并且
select*fromtempwhereage>20andname=‘jack’;
查询名称等于jack并且年龄大于20的
11、or或者
满足一个即可
select*fromtmepwherename=‘jack’orname=‘jackson’;
12、betweenvandv2
大于等于v且小于等于v2
select*formtempwhereagebetween20and25;
13、in查询
可以多个条件类似于or
select*fromtempwhereidin(1,2,3);
查询id在括号中出现的数据
14、like模糊查询
查询name以j开头的
select*fromtempwherenamelike‘j%’;
查询name包含k的
select*fromtempwherenamelike‘%k%’;
escape转义
select*fromtempwherenamelike‘\_%’escape‘\’;
指定\为转义字符，上面的就可以查询name中包含“_”的数据
15、isnull、isnotnull
查询为null的数据
select*fromtempwherenameisnull;
查询不为null的数据
select*fromtempwherenameisnotnull;
16、not
select*fromtempwherenot(age>20);
取小于等于20的数据
select*fromtempwhereidnotin(1,2);
17、orderby
排序，有desc、asc升序、降序
select*fromtemporderbyid;
默认desc排序
select*fromtemporderbyidasc;
多列组合
select*fromtemporderbyid,age;
本文内容总结：
原文链接：https://www.cnblogs.com/hoojo/archive/2011/06/20/2085390.html