MySQL 学习笔记

一、数据库简单介绍
1、 按照数据库的发展时间顺序，主要出现了以下类型数据库系统：

Ø 网状型数据库

Ø 层次型数据库

Ø 关系型数据库

Ø 面向对象数据库

上面4中数据库系统中，关系型数据库使用最为广泛。面向对象数据库则是由面向对象语言催生的新型数据库，目前的一些数据库系统，如：SQL Server 2005、Oracle10g等都开始增加面向对象的特性。

 

二、常用基本SQL语句/语法
Ø SQL语句基础理论

SQL是操作和检索关系型数据库的标准语言，标准SQL语句可用于操作然后关系型数据库。

标准的SQL语句通常划分为以下类型：

查询语句：主要由于select关键字完成，查询语句是SQL语句中最复杂，功能最丰富的语句。

DML（Data Munipulation Language，数据操作语言）语句，这组DML语句修改后数据将保持较好的一致性；操作表的语句，如插入、修改、删除等；

DDL（Data Definition Language，数据定义语言）语句，操作数据对象的语言，有create、alter、drop。

DCL（Data Control Language，数据控制语言）语句，主要有grant、revoke语句。

事务控制语句：主要有commit、rollback和savepoint三个关键字完成

DDL语句

DDL语句是操作数据库对象的语句，包括创建create、删除drop、修改alter数据库对象。

常见数据库对象 对象名称
对应关键字
描述

表
table
表是数据库存储的逻辑单元，以行和列的形式存在；列是字段，行就是一条数据记录

数据字典
   就是系统表，存储数据库相关信息的表，系统表里的数据通常有数据库系统维护。系统表结构和数据，开发人员不应该手动修改，只能查询其中的数据

视图
view
一个或多个数据表里的数据的逻辑显示，视图就是一张虚拟的表，并不真正存储数据

约束
constraint
执行数据检验规则，用于保证数据完整性的规则

索引
index
用于提高查询性能，相当于书的目录

函数
function
用于完成一个特定的计算，具有返回值和参数

存储过程
procedure
完成某项完整的业务处理，没有返回值，但可通过传出参数将多个值传个调用环境

触发器
trigger
相当于一个事件的监听器，当数据库发生特定的事件后，触发器被触发，完成响应处理



上面的对象都可以通过用create、alter、drop完成相关的创建、修改、删除操作。

常用数据类型 列类型
说明

tinyint/smallint/mediumint int(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、    显示所有数据库
show databases;

2、    删除数据库
drop database dbName;

3、    创建数据库
create database [if not exists] dbName;
中括号部分可选的，判断该数据不存在就创建

4、    切换、使用指定数据库
use dbName;

5、    显示当前使用数据库所有的表对象
show tables；

6、    显示表结构describe（desc）
desc tableName;

7、    创建一张表
create table user (
        --int 整型
        uId int,
        --小数
        uPrice decimal,
        --普通长度文本，default设置默认值
        uName varchar(255) default ‘zhangsan’,
        --超长文本
        uRemark text,
        --图片
        uPhoto blob,
        --日期
        uBirthday datetime
);

8、    子查询建表方法
部分列名匹配模式：
create table userInfo (
name varchar(20),
sex char
)
as
select name, sex from user;
上面的列名和子查询的列名以及类型要对应

全部列名模式：
create table userInfo
as
select * from user;
直接将整个表的类型和数据备份到新表userInfo中

9、    添加表字段
添加单列
alter table user add tel varchar(11) default ‘02012345678’;

添加多列
alter table user
add (
photo blob,
birthday date
);
上面就同时增加了多列字段

10、    修改表字段
修改tel列
alter table user modify tel varchar(15) default ‘02087654321’;
修改tel列的位置，在第一列显示
alter table user modify tel varchar(15) default '02087654321' first;
修改tel列的位置，在指定列之后显示
alter table user modify tel varchar(15) default '02087654321' after age;
注意：alter modify不支持一次修改多个列，但是Oracle支持多列修改
但是MySQL可以通过多个modify的方式完成：
alter table user
modify tel varchar(15) default '02087654321' first,
modify name varchar(20) after tel;

11、    删除指定字段
alter table user drop photo;

12、    重命名表数据
表重命名
alter table user rename to users;

字段重命名
alter table users change name u_name varchar(10);
alter table users change sex u_sex varchar(10) after u_name;
如果需要改变列名建议使用change，如果需要改变数据类型和显示位置可以使用modify

13、 删除表

drop table users;
drop删除表会删除表结构，表对象将不存在数据中；数据也不会存在；表内的对象也不存在，如：索引、视图、约束；

truncate删除表
truncate都被当成DDL出来，truncate的作用就是删除该表里的全部数据，保留表结构。相当于DDL中的delete语句，
但是truncate比delete语句的速度要快得多。但是truncate不能带条件删除指定数据，只会删除所有的数据。如果删除的表有外键，
删除的速度类似于delete。但新版本的MySQL中truncate的速度比delete速度快。

Ø 约束

MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；

约束主要完成对数据的检验，保证数据库数据的完整性；如果有相互依赖数据，保证该数据不被删除。

常用五类约束：
not null：非空约束，指定某列不为空
unique： 唯一约束，指定某列和几列组合的数据不能重复
primary key：主键约束，指定某列的数据不能重复、唯一
foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据
check：检查，指定一个表达式，用于检验指定数据
MySQL不支持check约束，但可以使用check约束，而没有任何效果；

根据约束数据列限制，约束可分为：
单列约束：每个约束只约束一列
多列约束：每个约束约束多列数据

MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；
1、    not null约束
非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
Null类型特征：
所有的类型的值都可以是null，包括int、float等数据类型
空字符串“”是不等于null，0也不等于null
create table temp(
        id int not null,
        name varchar(255) not null default ‘abc’,
        sex char null
)
上面的table加上了非空约束，也可以用alter来修改或增加非空约束
增加非空约束
alter table temp
modify sex varchar(2) not null;

取消非空约束
alter table temp modify sex varchar(2) null;

取消非空约束，增加默认值
alter table temp modify sex varchar(2) default ‘abc’ null;

2、    unique
唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null
同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。
唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。
MySQL会给唯一约束的列上默认创建一个唯一索引；
create table temp (
        id int not null,
        name varchar(25),
        password varchar(16),
        --使用表级约束语法，
        constraint uk_name_pwd unique(name, password)
);
表示用户名和密码组合不能重复
添加唯一约束
alter table temp add unique(name, password);
alter table temp modify name varchar(25) unique;
删除约束
alter table temp drop index name;

3、    primary key
主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；如果的多列组合的主键约束，
那么这些列都不允许为空值，并且组合的值不允许重复。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。MySQL的主键名总是PRIMARY，
当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
列模式：
create table temp(
    /*主键约束*/
    id int primary key,
    name varchar(25)
);

create table temp2(
    id int not null,
    name varchar(25),
    pwd varchar(15),
    constraint pk_temp_id primary key(id)
);

组合模式：
create table temp2(
    id int not null,
    name varchar(25),
    pwd varchar(15),
    constraint pk_temp_id primary key(name, pwd)
);

alter删除主键约束
alter table temp drop primary key;

alter添加主键
alter table temp add primary key(name, pwd);

alter修改列为主键
alter table temp modify id int primary key;

设置主键自增
create table temp(
        id int auto_increment primary key,
        name varchar(20),
        pwd varchar(16)
);
auto_increment自增模式，设置自增后在插入数据的时候就不需要给该列插入值了。

4、    foreign key 约束
外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
也就是说从表的外键值必须在主表中能找到或者为空。
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，
然后才可以删除主表的数据。还有一种就是级联删除子表数据。
注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录，
那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。
创建外键约束：
主表
create table classes(
        id int auto_increment primary key,
        name varchar(20)
);
从表
create table student(
        id int auto_increment,
        name varchar(22),
        constraint pk_id primary key(id),
        classes_id int references classes(id)
);

通常先建主表，然后再建从表，这样从表的参照引用的表才存在。
表级别创建外键约束：
create table student(
        id int auto_increment primary key,
        name varchar(25),
        classes_id int,
        foreign key(classes_id) references classes(id)
);
上面的创建外键的方法没有指定约束名称，系统会默认给外键约束分配外键约束名称，命名为student_ibfk_n，
其中student是表名，n是当前约束从1开始的整数。

指定约束名称：
create table student(
        id int auto_increment primary key,
        name varchar(25),
        classes_id int,
        /*指定约束名称*/
        constraint fk_classes_id foreign key(classes_id) references classes(id)
);

多列外键组合，必须用表级别约束语法：
create table classes(
        id int,
        name varchar(20),
        number int,
        primary key(name, number)
);
create table student(
        id int auto_increment primary key,
        name varchar(20),
        classes_name varchar(20),
        classes_number int,
        /*表级别联合外键*/
        foreign key(classes_name, classes_number) references classes(name, number)
);

删除外键约束：
alter table student drop foreign key student_ibfk_1;
alter table student drop foreign key fk_student_id;

增加外键约束
alter table student add foreign key(classes_name, classes_number) references classes(name, number);

自引用、自关联（递归表、树状表）
create table tree(
        id int auto_increment primary key,
        name varchar(50),
        parent_id int,
        foreign key(parent_id) references tree(id)
);

级联删除：删除主表的数据时，关联的从表数据也删除，则需要在建立外键约束的后面增加on delete cascade
或on delete set null，前者是级联删除，后者是将从表的关联列的值设置为null。
create table student(
        id int auto_increment primary key,
        name varchar(20),
        classes_name varchar(20),
        classes_number int,
        /*表级别联合外键*/
        foreign key(classes_name, classes_number) references classes(name, number) on delete cascade
);

5、    check约束
MySQL可以使用check约束，但check约束对数据验证没有任何作用。
create table temp(
        id int auto_increment,
        name varchar(20),
        age int,
        primary key(id),
/*check约束*/
check(age > 20)
);
上面check约束要求age必须大于0，但没有任何作用。但是创建table的时候没有任何错误或警告。



Ø 索引

索引是存放在模式（schema）中的一个数据库对象，索引的作用就是提高对表的检索查询速度，
索引是通过快速访问的方法来进行快速定位数据，从而减少了对磁盘的读写操作。
索引是数据库的一个对象，它不能独立存在，必须对某个表对象进行依赖。
提示：索引保存在information_schema数据库里的STATISTICS表中。

创建索引方式：
自动：当表上定义主键约束、唯一、外键约束时，该表会被系统自动添加上索引。
手动：手动在相关表或列上增加索引，提高查询速度。

删除索引方式：
自动：当表对象被删除时，该表上的索引自动被删除
手动：手动删除指定表对象的相关列上的索引
索引类似于书籍的目录，可以快速定位到相关的数据，一个表可以有多个索引。

创建索引：
create index idx_temp_name on temp(name);

组合索引：
create index idx_temp_name$pwd on temp(name, pwd);

删除索引：
drop index idx_temp_name on temp;

 

Ø 视图

视图就是一个表或多个表的查询结果，它是一张虚拟的表，因为它并不能存储数据。
视图的作用、优点：
限制对数据的访问
让复杂查询变得简单
提供数据的独立性
可以完成对相同数据的不同显示
   
创建、修改视图
create or replace view view_temp
as
    select name, age from temp;
通常我们并不对视图的数据做修改操作，因为视图是一张虚拟的表，它并不存储实际数据。如果想让视图不被修改，可以用with check option来完成限制。
create or replace view view_temp
as
    select * from temp
with check option;

修改视图：
alter view view_temp
as
    select id, name from temp;

删除视图：
drop view view_temp;

显示创建语法：
show create view v_temp;

 

Ø DML语句

DML主要针对数据库表对象的数据而言的，一般DML完成：
插入新数据
修改已添加的数据
删除不需要的数据
1、    insert into 插入语句
insert into temp values(null, ‘jack’, 25);
主键自增可以不插入，所以用null代替

指定列
insert into temp(name, age) values(‘jack’, 22);
在表面后面带括号，括号中写列名，values中写指定列名的值即可。当省略列名就表示插入全部数据，
注意插入值的顺序和列的顺序需要保持一致。
Set方式插入，也可以指定列
insert into temp set id = 7, name = 'jason';

MySQL中外键的table的外键引用列可以插入数据可以为null，不参照主表的数据。

使用子查询插入数据
insert into temp(name) select name from classes;

多行插入
insert into temp values(null, ‘jack’, 22), (null, ‘jackson’ 23);

2、    update 修改语句
update主要完成对数据的修改操作，可以修改一条或多条数据。修改多条或指定条件的数据，需要用where条件来完成。
修改所有数据
update temp set name = ‘jack2’;
所有的数据的name会被修改，如果修改多列用“,”分开
update temp set name = ‘jack’, age = 22;
修改指定条件的记录需要用where
update temp set name = ‘jack’ where age > 22;

3、    delete 删除语句
删除table中的数据，可以删除所有，带条件可以删除指定的记录。
删除所有数据
delete from temp;
删除指定条件数据
delete from temp where age > 20;

 

Ø select 查询、function 函数

select查询语句用得最广泛、功能也最丰富。可以完成单条记录、多条记录、单表、多表、子查询等。
1、    查询某张表所有数据
select * from temp;
*代表所有列，temp代表表名，不带条件就查询所有数据

2、    查询指定列和条件的数据
select name, age from temp where age = 22;
查询name和age这两列，age 等于22的数据。

3、    对查询的数据进行运算操作
select age + 2, age / 2, age – 2, age * 2 from temp where age – 2 > 22;

4、    concat函数，字符串连接
select concat(name, ‘-eco’) from temp;
concat和null进行连接，会导致连接后的数据成为null

5、    as 对列重命名
select name as ‘名称’ from temp;
as也可以省略不写，效果一样
如果重命名的列名出现特殊字符，如“‘”单引号，那就需要用双引号引在外面
select name as “名’称” from temp;

6、    也可以给table去别名
select t.name Name from temp as t;

7、    查询常量
类似于SQL Server
select 5 + 2;
select concat('a', 'bbb');

8、    distinct 去掉重复数据
select distinct id from temp;
多列将是组合的重复数据
select distinct id, age from temp;

9、    where 条件查询
大于>、大于等于>=、小于<、小于等于<=、等于=、不等于<>
都可以出现在where语句中
select * from t where a > 2 or a >= 3 or a < 5 or a <= 6 or a = 7 or a <> 0;

10、    and 并且
select * from temp where age > 20 and name = ‘jack’;
查询名称等于jack并且年龄大于20的

11、    or 或者
满足一个即可
select * from tmep where name = ‘jack’ or name = ‘jackson’;

12、    between v and v2
大于等于v且小于等于v2
select * form temp where age between 20 and 25;

13、    in 查询
可以多个条件 类似于or
select * from temp where id in (1, 2, 3);
查询id在括号中出现的数据

14、    like 模糊查询
查询name以j开头的
select * from temp where name like ‘j%’;

查询name包含k的
select * from temp where name like ‘%k%’;

escape转义
select * from temp where name like ‘\_%’ escape ‘\’;
指定\为转义字符，上面的就可以查询name中包含“_”的数据

15、    is null、is not null
查询为null的数据
select * from temp where name is null;
查询不为null的数据
select * from temp where name is not null;

16、    not
select * from temp where not (age > 20);
取小于等于20的数据
select * from temp where id not in(1, 2);

17、    order by
排序，有desc、asc升序、降序
select * from temp order by id;
默认desc排序
select * from temp order by id asc;
多列组合
select * from temp order by id, age;