共计 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 是列的约束条件
- 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