一、MySQL安装
MySQL的下载
http://dev.mysql.com/downloads/mysql/
MySQL版本选择
MySQL功能自定义选择安装
1.功能自定义选择
2.路径自定义选择
3.设置root用户密码
4.安装完成，点击MySQL Workbench 6.3 CE进入MySQL客户端
二、SQL基础
SQL语句分类
1.DDL(Data Definition Languages)语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create/drop/alter
2.DML(Data Manipulation Language)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括 insert/delete/update/select等
3.DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant/revoke等
DDL语句(涉及表的定义、结构的修改)
一、create语句
- Query Ok代表语句执行成功
- 1 row affected代表数据库一行收到影响
- 0.01 sec代表操作执行的时间
create table student(
SID int not null auto_increment,
sNo int ,
sName varchar(50) not null,
primary key(SID)
);
1.查看系统中都存在哪些数据库（show databases;）
2.在查看系统中已有的数据库后，可以用（use dbname）选择对应的数据库
3.在选择对应的数据库后，查询该数据库下面的所有的表（show tables）
二、删除数据库
删除数据库的语法：drop databse dbname;
三、创建表
语法：create table tablename(column_name_1 column_type_1 constraints,column_name_2 column_type2 constrationts)
- mysql的表名是以目录形式存储在磁盘上，表名的字符可以是任何目录名允许的字符。
- column_name是列名
- column_type是列的数据类型
- constrationts是列的约束条件
- 查看表定义：desc tablename
- 查看创建表的SQL语句：show create table tablename
四、删除表
删除表的语法：drop table tablename;
五、修改表
| aleter 语法 | 说明 |
|---|---|
| alter table tablename modify columnname newColumnType | 修改表字段的类型(modify 不能更改字段名称) |
| alter table tablename add newColumnname newColumnType | 增加表字段 |
| alter table tablename drop oldCloumnname | 删除表字段 |
| alter table tablename change oldColumname newColumnname newColumntype | 修改字段的名称及类型 |
| alter table tablename rename (to) newtablename | 修改表名称 |
- 修改字段的排列顺序
在alter的语法后面都有[firstafter columnname]可选项
alter table user add address varchar(20) first ;
alter table user add age int after name ;
DML(对数据库表记录进行操作，增(insert)删(delete)改(update)查(select))
1.insert语句
语法：
插入一条：insert into tablename(columnname1,columnname2...)values(val1,val2...);
插入多条：insert into tablename(columnname1,columnname2...)values(val1,val2...),(val1,val2...);
2.update语句
语法：update tablename set columnname=value [where condition]
如果使用MySQL Workbench，update语句不加where条件的会执行错误，需要如下图设置取消设置：
3.delete语句
语法：**delete from ** tablename where condition
4.select语句
语法：select * from tablename [where condition]
| select 查询相关的 | 说明 |
|---|---|
| select distinct name from user; | 查询不重复记录 |
| select * from user where id=1; | 条件查询 |
| select * from user order by id desc | 排序（desc倒序、asc正序） |
| select * from user order by id desc limit 0,2 | 根据id进行倒序，0代表从第一个开始，2代表查询出来的个数 select ...[limit offset_start,row_count] |
| select count(1) from user | 聚合函数count(),其他的还有sum()/max()/min() |
| group by | 表示要进行分类聚合select name,count(1) from user group by name |
| having | 表示对分类后的结果再进行条件过滤 select name,count(1) from user group by name having count(1)>2 |
5.表连接
1.内连接（仅选出两张表中互相匹配的数据）
select cno,cname,sname from student inner join course on cno=sno;
select cno,cname,sname from student,course where cno=sno;
2.外连接
外连接又区分：
左连接（left join）:包含左边表的所有记录，右边没有的为Null
右连接（right join）:包含右边表的所有记录，左边没有的为null
6.子查询
| 子查询关键字 | 说明 |
|---|---|
| in | 存在 |
| not in | 不存在 |
| = | 等于 |
| != | 不等于 |
| exists | 存在 |
| not exists | 不存在 |
7.记录联合
语法：
1.select * from t1 union all select * from t2;
2.select * from t1 union select * from t2;
union all与union的区别：
union all是把结果集直接合并在一起，而union是将union all后的结果进行一次distinct，去除重复后的结果
DCL语句（DCL语句主要是dba用来管理系统中的对象权限）
grant与revoke
三、MySQL支持的数据类型
数值类型
| 常用的数值类型 | 字节 | 说明 |
|---|---|---|
| int | 4 | 有个额外的额属性Auto_Increment |
| bigint | 8 | |
| float | 4 | |
| double | 8 | |
| decimal(M,D) | M+2 | M代表精度，D代表标度 |
MySQL支持类型后面的小括号指定显示宽度，例如：int(5)表示当数值宽度小于5的时候在数字前面填满宽度，如果不显示指定宽度则默认为int(11)。如果插入的数据大于这个数值宽度，对实际的插入值是没有影响的，是按照int类型的实际大小进行的。
create table valuetype(
age int,
age1 int
)
insert into valuetype(age,age1)values(1,2);//这时候数据库就显示1
alter table valuetype modify age int zerofill;//这时候数据库就显示'0000000001'
| 位类型 | 说明 |
|---|---|
| bit(M) | 位类型最小1，最大64 |
create table test(pwd bit(64))
数据插入bit类型字段时，首先转换为二进制，如果位数允许，将插入成功，如果位数小于实际的位置，则插入失败。
日期时间类型
| 日期类型 | 说明 |
|---|---|
| date | 表示年月日 |
| datetime | 表示年月日时分秒 |
| time | 表示时分秒 |
| timestamp | 时间戳（1970-2038） |
| year | 年份(1901-2155) |
mysql里面获取当前时间为now().mssql获取当前时间为getdate()
timestamp,支持的范围非常小，从1970-2038年，timestamp受时区的影响
create table timestamptest(
tp timestamp)
系统会自动给tp赋予默认值current_timestamp(系统日期)，但是mysql只给第一个timestamp设置默认值，如果有第二个timestamp类型，则默认值设置为0
字符串类型
| 常用字符串类型 | 说明 |
|---|---|
| char(M) | M为0-255之间的整数 |
| varchar(M) | M为0-65535之间的整数，值长度+1个字节 |
| text | 允许0-65535字节，值长度+2字节 |
char与varchar类型的区别：
char列最后的空格已经删除，而varchar保留空格
四、MySQL中运算符
算术运算符
| 运算符 | 说明 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| /,DIV | 除法，返回商 |
| %,MOD | 除法， |
比较运算符，满足返回1，否则返回0
| 运算符 | 说明 |
|---|---|
| = | 等于 |
| <>,!= | 不等于 |
| <=> | null安全的等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between | 存在于指定范围 |
| in | 存在于指定集合 |
| is null | 为null |
| is not null | 不为null |
| like | 通配符匹配 |
| regexp,rlike | 正则表达式 |
逻辑运算符（布尔运算符）
| 运算符 | 说明 |
|---|---|
| NOT,! | 逻辑非 |
| AND,&& | 逻辑与 |
| OR | 逻辑或 |
| XOR | 逻辑异或 |
位运算符
| 运算符 | 说明 |
|---|---|
| & | 位与 |
| 位或 | |
| ^ | 位异或 |
| ~ | 位取反 |
| >> | 位右移 |
| << | 位左移 |
运算符优先级
大多情况下使用()进行操作
五、常用函数
字符串函数
| 函数 | 说明 |
|---|---|
| concat(s1,s2,...,sn) | 连接s1,s2,...sn为一个字符串 |
| insert(str,x,y,instr) | 将字符串str从第x位置，y个字符串长的子串替换为字符串instr |
| lower(str) | 将字符串str中所有字符变为小写 |
| upper(str) | 将字符串str中所有字符变为大写 |
| left(str,x) | 返回字符串str最左边的x个字符 |
| right(str,x) | 返回字符串str最右边的x个字符 |
| lpad(str,n,pad) | 用字符串pad对str最左边进行填充，直到长度为n个字符长度 |
| rpad(str,n,pad) | 用字符串pad对str最右边进行填充，直到长度为n个字符长度 |
| ltrim(str) | 去掉字符串str左侧的空格 |
| rtrim(str) | 去掉字符串str行尾的空格 |
| repeat(str,x) | 返回str重复x次的结果 |
| replace(str,a,b) | 用字符串b替换字符串str中所有出现的字符串a |
| strcmp(s1,s2) | 比较字符串s1,s2 |
| trim(str) | 去掉字符串行尾和行头的空格 |
| substring(str,x,y) | 返回字符串str x位置起y个字符串长度的字符串 |
数值函数
| 函数 | 说明 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| ceil(x) | 返回大于x的最小整数值。MSSQL则为ceiling。ceil(1.2)返回2 |
| floor(x) | 返回小于x的最大整数值。floor(1.2)则返回1 |
| mod(x,y) | 返回x/y的模，取余 |
| rand() | 返回0~1内的随机数 |
| round(x,y) | 返回参数x的四舍五入的有y为小数的值，select round(1.211,2)，返回1.21；select round(1.25,1) 返回1.3 |
| truncate(x,y) | 返回数字x截断为y位小数的结果.select round(1.25,1)返回1.2 |
日期和时间函数
| 函数 | 说明 |
|---|---|
| curdate() | 返回当前日期。select curdate().2016-08-13 |
| curtime() | 返回当前时间 |
| now() | 返回当前的日期和时间 |
| week(date) | 返回date为一年中的第几周 |
| year(date) | 返回日期date的年份 |
| date_format(date,fmt) | 返回按字符串fmt格式化日期的date值.fmt的格式化有%M（月），%D(日)，%Y(年) |
| datediff(date1,date2) | 返回起始时间和结束时间之间的天数 |
| data_add(date,interval expr type) | 返回与所给日期相差interval时间段的日期 |
流程函数
| 函数 | 说明 |
|---|---|
| if(value,t,f) | 如果value为真，返回t，否则返回f |
| ifnull(value1,value2) | 如果value1不为空，返回value1,否则返回value2.select ifnull(2,'woc')，返回2 |
| case when value1 then result1 ..else default end | 如果value1是真，则返回result1,返回返回default |
| case expr when value1 then result1.. else default end | 如果表达式expr等于value1,则返回result，否则返回default |
其他函数
| 函数 | 说明 |
|---|---|
| database() | 返回当前数据库名称 |
| password(str) | 返回字符串str的加密版本 |
| md5(str) | 返回字符串str的md5值 |
六、选择合适的数据类型
char与varchar
在Innodb存储引擎中，建议使用varchar类型。对于Innodb数据表，内部的行存储格式没有区分固定长度和可变长度列，因此固定长度列的性能不一定比不可变长度的性能好。
Text与blob
一般在保存少量字符串的时候，我们会选择char或者varchar，而在保存较大文本的时候，通常会选择使用text或者blob。两者的区别：text只能保存字符数据，比如日志。blob能保存二进制数据，比如照片。
浮点数与定点数
在MySQL中，decimal或者(numberic)用来表示定点数
日期类型的选择
date/time/datetime/timestamp
七、索引的设计和使用
索引概述
索引是数据库中用来提高性能的最常用工具。在MySQL中，MyISAM与Innodb存储引擎的表默认创建的都是Btree索引。
1.索引的创建
create table indexTest(
id int not null auto_increment,
memberid int not null,
createtime datetime not null default current_timestamp,
primary key (id)
)
alter table indextest add orderserial varchar(50) not null;
create unique index IX_orderserial on indexTest(orderserial);
insert into indextest (memberid,createtime,orderserial)values(112123,'2016-08-14','sz121213')
说明：上面创建一个表，其中定义orderserial为唯一索引。
语法：create [uniquefulltextspatial] index index_name on tablename(columname)
2.设计索引的原则
- 最合适的索引列是出现在where子句中列，或连接子句中指定的列，而不是出现在select关键字后面的选择列表的列
- 使用唯一索引，需要考虑列中某个值得分布，如果索引列种的基数越大，则索引的效果越好。举个例子：订单号就可以设置唯一索引，因为订单号的不一样。而对于rowstatus就无须了，因为rowstatus要么是有效要么是无效。这样的筛选出的范围还是很多，没有意义
- 不要过度索引。因为所有也要占用额外的磁盘空间，如果一个索引很少使用，那么会不必要的减缓表的修改速度
显示MySQL的执行计划：explain 后面加mysql语句
八、视图
视图(View)
定义：视图是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的，视图并不是在数据库中实际存在。
优势：
- 简单，用户完全不需要关心后面对应的表的结构/关联条件和筛选条件。对用户来说已经是过滤好的符合条件的结果集
- 安全，使用视图的用户只能访问他们被允许查询的结果集
- 数据独立，一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响。
语法：
create or replace view index_view as
select * from indextest
- 创建create [or replace] **view **viewName as select ...
- 查询 select * from 视图名称
- 展示视图 show tables;
- 删除视图 drop view viewname
九、存储过程和函数
存储过程（store procedure）和函数
存储过程和函数是事先经过编译并存在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的
语法：
create database finance;//创建finance数据库
use finance;
create table orders(
orderId bigint not null auto_increment,
memberId int not null default 0,
serialNumber varchar(50) not null default '',
amount decimal(18,2) not null default 0,
createTime datetime not null default current_timestamp,
primary key (orderid)
)//创建orders订单表
insert into orders (memberId,serialNumber,amount) values(6561121,'sz12234222',5),(233444,'ys1652233',10)//插入测试数据
delimiter &
create procedure orders_serial(in serial varchar(50))
reads sql data
begin
select * from orders
where serialNumber=serial;
end &
注释：delimiter $$命令就是将语句的结束符从分号；修改成其他符号，这里指的是$$为结尾。这样在number后面的分号就不会认为结束。
-
调用存储过程
call orders_serial('sz12234222')
-
存储过程的好处
逻辑封装在数据库端，调用者不需要了解中间的处理逻辑，一旦调用逻辑发生变化，只需要修改存储过程即可，而对调用者的程序完全没有影响。
-
删除存储过程
drop procedure if exists orders_serial
//if exists可选
-
查看存储过程状态
show procedure status like 'orders_serial'
-
查询存储过程的定义
show create procedure orders_serial
存储过程变量的使用
存储过程可以使用变量，并且在MySQL5.1版本后，不区分大小写
-
变量的定义
declare currentTime date;
变量的作用域只能在begin...end块中，可以嵌套在块中
-
变量的赋值
set currentTime=now();//直接赋值
select x into currentTime from x;//也可以通过sql语句进行赋值
-
定义条件和处理
declare handler_type handler for contidtion_value
handler_type:
- continue
- exit
- undo
contidtion_value:
- sqlstate
- sqlwarning
- not found
- sqlexception
declare continue handler for sqlstate '2' set @x=1
光标的使用
在存储过程和函数中，可以使用光标对结果集进行循环处理，光标的使用包含光标的声明: open、fetch、close
定义
declare cur_id cursor for select * from orders;
open cur_id;
fetch cur_id;
close cur_id;
事件调度器
事件调度器是MySQL5.1后面新增的功能，可以将数据库按照自定义时间周期触发某种操作。数据库默认操作是关闭的。需要打开
create event x
on schedule
every 5 second
do
insert into orders (memberId,serialNumber,amount) values(6561121,'222',5)
set global event_scheduler =1//打开调度器
alter event x disable;//禁用事件调度器
drop event x;//删除事件调度器
十、触发器
触发器
触发器是在5.02版本后支持的，触发器是与表有关的数据库对象，在满足条件时触发，并执行触发器中定义的语句集合。可以协助应用在数据库端确保数据的完整性
drop trigger orderlog
delimiter $
create trigger orderlog
after insert on orders for each row
begin
insert into orderslog (content) values(new.serialNumber);
end $
insert into orders (memberId,serialNumber,amount) values(6561121,'sz12234222',5)
解释：上面描述的是创建一个触发器，当往订单表中插入数据之后，在订单日志表插入一条记录。使用old和new来引用触发器发生变化的记录内容，目前只支出行级触发，不支持语句级触发
触发器执行的顺序
before insertefore updateafter update
十一、事务控制和锁定语句
MySQL存储引擎的事务说明
| 存储引擎 | 说明 |
|---|---|
| InnoDB | 支持行级锁定 |
| MyISAM | 支持表级锁定 |
| Memory | 支持表级锁定 |
| BDB | 支持页级锁定 |
-
Lock Table与Unlock Table
use finance;
lock table orders read;
unlock table;
如果某个进程(session1)lock定了表，那么其他的进程(session2)可以查询，但是不能进行更新操作，直到第一个进程释放了锁
-
事务控制
| 事务关键字 | 说明 |
|---|---|
| start transaction | 开启一个新事务 |
| commit | 提交事务 |
| rollback | 回滚事务 |
十二、总结
很高兴您能阅读到这里，可能在三十分钟很难吸收这么多的知识，这篇文章也是我之前学习MySQL笔记整合的，但请容许我以这个词为标题。这篇文章也是理论偏多，对于其中比较比较难理解知识点写些Demo，权当个人理解，如有不足的地方，请您指出。如果对您有所帮助，请点个赞！