MySQL 快速入门学习教程

共计 9546 个字符，预计需要花费 24 分钟才能阅读完成。

MySQL 快速入门学习教程

• 一、MySQL 安装

• MySQL 的下载

  http://dev.mysql.com/downloads/mysql/

• MySQL 版本选择

  

• MySQL 功能自定义选择安装

• 功能自定义选择
• 路径自定义选择
• 设置 root 用户密码

• 安装完成，点击 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 是列的约束条件
    
• 1. 查看表定义：desc tablename

• 2. 查看创建表的 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 的语法后面都有 [first\after 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. 外连接
  外连接又区分：
  1. 左连接（left join）: 包含左边表的所有记录，右边没有的为 Null
  2. 右连接（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 字节

1.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 [unique\fulltext\spatial] index index_name on tablename(columname)

• 2. 设计索引的原则
• 1. 最合适的索引列是出现在 where 子句中列，或连接子句中指定的列，而 不是 出现在 select 关键字后面的选择列表的列
• 2. 使用唯一索引，需要考虑列中某个值得分布，如果索引列种的基数越大，则索引的效果越好。举个例子：订单号就可以设置唯一索引，因为订单号的不一样。而对于 rowstatus 就无须了，因为 rowstatus 要么是有效要么是无效。这样的筛选出的范围还是很多，没有意义
• 3. 不要过度索引。因为所有也要占用额外的磁盘空间，如果一个索引很少使用，那么会不必要的减缓表的修改速度

显示 MySQL 的执行计划：explain 后面加 mysql 语句

• 八、视图

• 视图(View)

  定义：视图是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的，视图并不是在数据库中实际存在。
  优势：
  1. 简单，用户完全不需要关心后面对应的表的结构 / 关联条件和筛选条件。对用户来说已经是过滤好的符合条件的结果集
  2. 安全，使用视图的用户只能访问他们被允许查询的结果集
  3. 数据独立，一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响。
  语法：

create or replace view index_view as
select * from indextest

1. 创建create [or replace] view viewName as select …
2. 查询 select * from 视图名称
3. 展示视图 show tables;
4. 删除视图 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 后面的分号就不会认为结束。

• 1. 调用存储过程

call orders_serial('sz12234222')

• 2. 存储过程的好处

• 逻辑封装在数据库端，调用者不需要了解中间的处理逻辑，一旦调用逻辑发生变化，只需要修改存储过程即可，而对调用者的程序完全没有影响。

• 3. 删除存储过程

drop procedure if exists orders_serial
//if exists 可选

• 4. 查看存储过程差状态

show  procedure status like 'orders_serial'

• 5. 查询存储过程的定义

show  create  procedure orders_serial

• 二、存储过程变量的使用

  存储过程可以使用变量，并且在 MySQL5.1 版本后，不区分大小写

• 1. 变量的定义

  变量的作用域只能在 begin…end 块中，可以嵌套在块中

    declare currentTime date;

• 2. 变量的赋值

set currentTime=now();// 直接赋值

select XX into currentTime from XX;// 也可以通过 sql 语句进行赋值

• 3. 定义条件和处理

  declare handler_type handler for contidtion_value;
  
  handler_type:
  1.continue;
  2.exit;
  3.undo;
  
  condition_value:
  1.sqlstate
  2.sqlwarning
  3.not found
  4.sqlexception
  
  eg:  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 insert\before update\after update

• 十一、事务控制和锁定语句

• MySQL 存储引擎的事务说明

存储引擎	说明
InnoDB	支持行级锁定
MyISAM	支持表级锁定
Memory	支持表级锁定
BDB	支持页级锁定

• 1.Lock Table 与 Unlock Table

  语法：

use finance;
lock table orders read;

unlock table;

如果某个进程 (session1)lock 定了表，那么其他的进程(session2) 可以查询，但是不能进行更新操作，直到第一个进程释放了锁

• 2. 事务控制

  事务关键字	说明
  start transaction	开启一个新事务
  commit	提交事务
  rollback	回滚事务

• 十二、总结

• 很高兴您能阅读到这里，可能在三十分钟很难吸收这么多的知识，这篇文章也是我之前学习 MySQL 笔记整合的，但请容许我以这个词为标题。这篇文章也是理论偏多，对于其中比较比较难理解知识点写些 Demo，权当个人理解，如有不足的地方，请您指出。

 本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/136939.htm