MySQL学习笔记（1-1）-阿里云开发者社区

列的CRUD操作

列的类型

列的类型 分为如下:数字类型
整数:tinyint、smallint、mediumint、int、bigint
浮点数:float、double、real、decimal
日期和时间:date、time、datetime、timestamp、year
字符串类型
字符串:char、varchar
文本:tinytext、text、mediumtext、longtext
二进制(可用来存储图片、音乐等):tinyblob、blob、mediumblob、longblob
对比Java中的类型:列的类型
java                mysql
byte                 TinyIntshort                SMALLINT
intintfloatfloatdoubledoubleString              char(长度)/varchar(长度)char 和varchar:区别主要体现在存储字符时候,如果是char存储类型,不足的字符会以空格存储,varchar则不会
booleanbooleanDatedate: YYYY-MM-DD 年/月/日
time: hh:mm:ss 时:分:秒
datetime:timestamp:最大取值是2038年.
text: 大文本类型
blob: 二进制大对象类型

列的约束

主键约束: primary key，不能重复,不能为空通常用在编号ID auto_increment(自增)
唯一约束: unique，不能重复,但是可以为空
非空约束: not null，不能为空
默认约束：default,设置字段的默认值
自增约束：auto_increment,约束字段的值为自动增长
外键约束：foreign key,约束字段为表的外键

设置外键约束

--建表时createtable table_name(    列名 列的类型 列的约束，
    列名 列的类型 列的约束
    ......
    constraint 外键约束名 foreign key(属性名) references 表名(外表的属性))

添加列

altertable 表名 add 列名 列的类型 列的约束;--在表的所有字段第一个位置添加字段altertable 表名 add 列名 列的类型 列的约束 first;--在表的指定字段添加字段altertable 表名 add 列名 列的类型 列的约束 after 列名;

修改列

--修改列的类型altertable 表名 modify 列名 列的类型;--修改某个字段的顺序altertable 表名 modify 列名 列的类型 first|after 列名;--修改列名altertable 表名 change 旧列名 新列名 列的类型 列的约束;

删除列:

altertable 表名 drop 列名;

索引

由于数据存储在数据库表中，所以索引是建立在数据库表对象上的，由表中的一个字段或者多个字段生成的键组成。这些键存储在数据结构中，通过MySQL快速有效地查找键值相关联的字段。根据索引的存储类型，可以将索引分为B型数索引（BTREE）和哈希索引（HASH）。

注意：InnoDB和MyISAM存储引擎支持BTREE类型索引，MEMORY存储引擎支持HSAH类型索引。

MySQL支持六种索引：普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

创建索引

普通索引

1.创建表时创建普通索引

createtable table_name(    列名 列的类型 [列的约束],    列名 列的类型 [列的约束],    index|key [索引名](属性名 [长度][asc|desc1]))

2.在已经存在的表上创建普通索引

create index 索引名 on 表名(属性名 [长度][asc|desc]);

3.通过SQL语句ALTER TABLE 创建普通索引

altertable table_name add index|key 索引名(属性名 [长度][asc|desc]);

唯一索引

1.创建表时创建唯一索引

createtable table_name(    列名 列的类型 列的约束，
    列名 列的类型 列的约束，
    unique index|key [索引名](属性名 [长度][asc|desc]))

2.在已经存在的表中创建唯一索引

create unique index 索引名 on 表名(属性名 [长度][asc|desc]);

3.通过SQL语句ALTER TABLE创建唯一索引

altertable table_name add  unique index|key 索引名(属性名 [长度][asc|desc]);

全文索引

1.创建表时创建全文索引

createtable table_name(    列名 列的类型 [列的约束],    列名 列的类型 [列的约束]，
    fulltext index|key [索引名](属性名 [长度][asc|desc]))

2.在已经存在的表上创建全文索引

create fulltext index 索引名 on 表名(属性名 [长度][asc|desc]);

3.通过SQL语句ALTER TABLE 创建全文索引

altertable table_name add fulltext index|key 索引名(属性名 [长度][asc|desc]);

多列索引

1.创建表时创建全文索引

createtable table_name(    列名 列的类型 [列的约束],    列名 列的类型 [列的约束],    .....
    index|key [索引名](属性名 [长度][asc|desc],                       属性名 [长度][asc|desc]))

2.在已经存在的表上创建全文索引

createtable table_name on 表名(属性名 [长度][asc|desc],                                属性名 [长度][asc|desc]);

3.通过SQL语句ALTER TABLE 创建全文索引

altertable table_name add index|key 索引名(属性名 [长度][asc|desc], 属性名 [长度][asc|desc]);

删除索引

drop index index_name on table_name;

视图

未来提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一些列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值形式存在。行和列数据来自定义视图的查询所引用的基本表，并且再具体引用视图时动态生成。

创建视图

create view view_name as 查询语句;

查看视图

show table status语句查看视图详细信息

show table status [form db_name][like'pattern'];或者
show table status form db_name \G

查看视图定义信息

show create view view_name;或者
show create view view_name \G

DESCRIBE|DESC查看视图设计信息

describe |desc view_name;

删除视图

删除一个或者多个视图

drop view view_name1 [,view_name2]....

修改视图

alter view view_name as 查询语句;

触发器的操作

触发器（TRIGGER）是MySQL的数据库对象之一，该对象与编程语言中的很函数类似，都需要声明、执行等，触发器是由时间出发激活的从而实现执行的。

创建触发器

create trigger trigger_name 
    before | after trigger_event
on table_name for each row trigger_stmt;

trigger_event参数表示触发器事件，即触发器执行条件，包含delete、insert和update语句
table_name参数表示触发事件操作表的名字
for each row参数表示任何一条记录上的操作都满足触发器事件都会触发该触发器
trigger_stmt参数表示激活触发器后被执行的语句

创建包含多条执行语句的触发器

create trigger trigger_name
    before | after trigger_event
on table_name for each row 
begin                trigger_stmt1;                trigger_stmt2;            end
            $$
            delimiter;

查看触发器

show triggers \G

执行SQL语句SELECT，查看系统表triggers中的所有记录

select*from triggers \G

插叙指定触发器的详细信息

select*from triggers where trigger_name='触发器的名称' \G

删除触发器

drop trigger trigger_name;

数据的操作

插入数据记录

插入单条数据

insertinto table_name(field1,field2...)values(value1,value2...);

插入多条数据

insertinto table_name(field1,field2...)values(value1,value2...),(value1,value2...),(value1,value2...);

插入查询结果

insertinto table_name1(field1,field2...)select(field1,field2...)from table_name2
where ...;

更新数据

更新特定数据

update table_name
set field1=value2,        field1=value2
where condition;

condition参数指定更新满足条件的特定数据

更新所有数据

update table_name
set field1=value2,        field1=value2;

删除数据记录

删除特定数据记录

deletefrom table_name where condition;

删除所有数据

deletefrom table_name;

数据查询

简单查询

select*from table_name;

别名查询

select field1 as 列别名，field2 as 列别名
fromtable table_name as 表别名;或者
select field1  列别名，field2  列别名
fromtable table_name  表别名;

去重查询

selectdistinct*from table_name;

运算查询

四则运算：

+、-、*、/或者div、%或者mod

关系运算：

>、<、=、!=或者<>、>=、<=

逻辑运算符：

and或者&&、or或者||，or、not或者！

其他运算符

between...and...
notbetween...and...
isnullisnotnullin(value1,value2,value3...)notin(value1,value2,value3...)

模糊查询——like、not like

%匹配0到多个字符
_匹配单个字符

排序查询——order by

单个字段排序

select*from table_name orderby field desc;

多个字段排序

select*from table_name orderby field1 desc                               field2 asc;

首先按照第一个字段进行排序，如果遇到值相同的字段则会按照第二个字段进行排序

限制数据记录查询数量——limit a,b

select*from table_name where condition 
limit offset_start,row_count;

offset_start参数不指定数据，则默认为0，显示row_count条数据
offset_start参数指定数据，表示offset_start+1开始，显示row_count条数据

聚合函数查询

count():查询表中数据记录的条数
avg():计算字段值的平均值
sum():求计算字段的总和
max():求计算字段的最大值
min():求计算字段的最小值

注意：如果所操作的表没有任何数据记录，则count()函数返回数据为0，其他则返回null。

分组查询——group by

实现多个字段分组查询

select*from group_concat(field),function(field)from table_name
where condition
groupby field1,field2...;

分组数据查询——实现HAVING子句限定分组查询

select function(field)from table_name
where condition
groupby field1,field2...
having condition;

SQL编写顺序

select ... from ... where ... groupby ... having ... orderby;

先from去查询表
根据where的条件过滤
group by分组
having是对分组后的数据过滤
select查询
最后order by排序

多表数据记录查询

交叉查询

select*from table_name1 cross join table_name2;或者
select*from table_name1,table_name2;

内连接查询

显示内连接

select*from table_name1 inner join table_name2 on table_name1.field=table_name2.fieldwhere condition;

隐式内连接

select*from table_name1,table_name2 where condition;

外连接查询

左外连接

select*from table_name1 left outer join table_name2 on table_name.field1=table.name2.field2where 条件;

右外连接

select*from table_name1 right outer join table_name2 on table_name.field1=table.name2.field2where 条件;

MySQL学习笔记（1-1）