目录
默认端口3306
sql语句的分类
DDL Data Definition Language (DDL 数据定义语言) 如:建库,建表
DQL Data Query Language(DQL 数据查询语言),如:对表中的查询操作
DML Data Manipulation Language(DML 数据操纵语言),如:对表中的记录操作增删改
DCL Data Control Language(DCL 数据控制语言),如:对用户权限的设置
注释方式:
单行注释:--内容
多行注释:/*内容*/
mysql特有注释:#内容
DDL操作数据库:
创建数据库:
创建数据库:create database 数据库名称;
创建不存在的数据库:create database if not exists 数据库名称;
创建数据库并指定字符集:create database 数据库名称 character set 字符集;
字符集:gbk,utf8等
查看数据库:
查看数据库:show databases;
查看数据库的定义信息:show create database 数据库名称;
修改数据库:
修改数据库的默认字符集:alter database 数据库名称 default character set 字符集;
删除数据库:
删除数据库:drop database 数据库名称;
使用数据库:
查看正在使用的数据库:select database();
使用/切换数据库:use 数据库名称;
DDL操作表结构:
前提是使用某一个数据库
创建表:
快速创建一个表结构相同的表:create table 新表名 like 旧表名;
标准创建:
create table 表名(
列名1 字段类型 1,
列名2 字段类型 2,
列名3 字段类型 --注意最后一个字段没有逗号
) engine = InnoDB default charset = utf8;
数据库的字段类型:
整型:tinyint(1字节),smallint(2字节),mediumint(3字节),int(integer)(4字节)
浮点型:float(单精度 4字节 8位有效数字),double(双精度 8字节 16位有效数字)
字符串型:char(m) (m范围0~255整数,固定长度,无论使用几个字符都要占满),varchar(m)(m范围0~65535整数,可变长度,使用几个字符就占用几个)
日期类型:time(时间),data(日期),datatime(时间日期)
大二进制(对大小写敏感,二进制数据,不需要指定字符集,可以存储图片,可以看作是varbinary在长度不足时的扩展):
tinyblob(0~255字节)
blob(0~65535字节 64kB)
mediumblob(0~167772150字节 160MB)
longblob(0~4294967295字节 4GB)
大文本(对大小写不敏感,非二进制数据(字符数据),需要指定字符只能存储纯文本,可以看作是VARCHAR在长度不足时的扩展):
tinytext(0~255字节)
text(0~65535字节 64kB)
mediumtext(0~167772150字节 160MB)
lomngtext(0~4294967295字节 4GB)
blob和text的相同点:
当保存或检索BLOB和TEXT列的值时不删除尾部空格。
对于BLOB和TEXT列的索引,必须指定索引前缀的长度。
BLOB和TEXT列不能有默认值。
查看表:
查看数据库中的所有表:show tables;
查看表结构:desc 表名;
查看建表语句:show create table 表名;
删除表:
直接删除表:drop table 表名;
若存在此表,则删除此表:drop table if exists 表名;
修改表结构:
添加表列名:alter table 表名 add 列名 列类型;
修改列类型:alter table 表名 modify 列名 新的列类型;
修改列名和列类型:alter table 表名 change 旧列名 新列名 新列类型;
删除列:alter table 表名 drop 列名;
修改表名:rename table 表名 to 新表名;
修改字符集:alter table 表名 character set 字符集;
DML操作表数据:
插入全部数据:
所有字段名都写出来:
insert into 表名(字段名1,字段名2,字段名3,等) values(值1,值2,值3,等);
不写字段名:
insert into 表名 values(值1,值2,值3,等);
插入部分数据:
insert into 表名(字段名1,字段名2) values (值1,值2);
没有添加数据的字段会使用NULL
更新表数据:
update 表名 set 字段名1 = 值1,字段名2 = 值2 [where 字段名 = 值];
删除表数据:
delete from 表名 [where 条件表达式];
truncate table 表名;属于DDL的操作,不需要提交事务,不会触发触发器。
truncate 和delete区别:
truncate相当于删除表结构后,重新创建了一张一模一样表。
删除速度:drop > truncate >delete
drop 是直接将表删除。
drop truncate 是DDL 操作 不可以回滚,不会触发触发器。
delete 是 DML 操作 可以回滚,可以触发触发器。
DQL表数据查询:
查询数据:
select * from 表名 where 条件表达式 group by 字段名 having 条件表达式 order by 字段名 asc(desc) limit 0,10;
asc:升序
desc:降序
limit:分页 从第0行开始,向下数10个。
还可以使用列别名和表别名,使用as关键字,可以省略。
清除重复数据:
select distinct 字段名 from 表名;
查询结果参加运算:参与运算的必须是数值类型
select 列名1+列名2 from 表名;
select 列名+1 from 表名;
where条件表达式:
运算符:
比较运算符:
< ,> ,= , >= , <>, = 在MySQL中可以使用!=代表不等于
between and 在一个范围之内,如:between 100 and 200相当于条件在 100 到 200 之间,包头又包尾
in (集合) 集合表示多个值,使用逗号分隔
like '张%' 模糊查询 %匹配0或者多个字符
like '张_' _匹配一个字符
is null 查询某一列为 NULL 的值,注:不能写=NULL
逻辑运算符:
and:与 连接比较运算符
or:或 连接比较运算符
not:非 is not null , not in (集合)
聚合函数:
count(字段名):统计个数
max(字段名):最大值
min(字段名):最小值
sum(字段名):求和
avg(字段名):求平均
分页查询:
limit 起始值从0开始,长度
limit 0,10:头10条数据
分组查询:
group by 分组列 having 过滤条件
约束:
主键:primary key
唯一:unique
非空:not null
默认:default
外键:foreign key
自增长:
auto_increment
级联操作(配合外键使用):
外键必须建立索引
foreign key(列名) references 表名(主键) on update cascade on delete cascade
on update cascade:级联更新
on delete cascade:级联删除
假如是sc成绩表的sid(学生id)参考了stu表的id(学生id),当stu表中的id更改,sc表中的sid也会随之更改(stu表中id为3的学生更改为id为6,该学生在sc表中的对应id也会级联更新);当stu表中的id行数据删除,sc表中对应的数据也会被删除(将stu表中id为2的学生删除,该学生在sc表中的成绩也会级联删除)
注意:删除表的时候必须先删除外键表(sc),再删除主键表(stu)
表与表之间的关系:
一对一:唯一外键约束
一对多:通过主外键约束,在多的一方添加外键
多对多:通过创建中间表,中间表和两个表的关系是多对一,中间表中 创建 外键
三大范式:
第一范式:原子性,每列不可再分。
比如adress的数据是中国北京市,美国纽约市。这个可以拆分Country和City。
第二范式:不产生局部依赖,表中的每一列都完全依赖于主键。
订单表中包括订单编号,订单日期,产品编号,产品价格。这个就不满足第二范式。因为产品价格依赖于产品编号,不依赖订单编号。故可以分成两张表,分别是订单表和产品表。
第三范式:不产生传递依赖,表中的每一列都直接依赖主键。
订单表中包括订单编号,订单日期,订单顾客编号,顾客姓名。这个顾客姓名不直接依赖于订单编号,但是依赖订单顾客编号,所以产生了传递依赖。故应该将顾客姓名这一列删除。专门在客户信息表中维护。
多表查询:
隐式内连接:使用where进行表字段匹配
显示内连接:inner join 表名 on 连接条件(inner可以省略)
连接那几张表,连接的条件是什么,查询的条件是什么,需要查询的字段
左外连接:left outer join 表名 on 连接条件(outer可以省略)
用左边的表去匹配右边的表,如果符合条件的则显示,否则,显示NULL
可以理解为在内连接的基础上保证左表的数据全部显示。
右外连接:right outer join 表名 on 连接条件(outer可以省略)和左外连接相反
子查询:
一个查询的结果作为另一个查询的条件,需要使用括号,有查询的嵌套,内部的查询成为子查询。
select * from emp where dept_id = (select id from dept where name = '市场部' );
子查询的结果是:
1.单行单列:比较运算符 < > != = 等。聚合函数的返回值是单行单列的。
2.多行单列:需要用到范围 in
3.多行多列:作为表 需要取别名 可以访问表中字段
子查询结果如果是单列的,则在where后面作为条件;
子查询的结果如果是多列的,则在from后面作为表进行二次查询。
事务:
事务执行是一个整体,所有的sql语句必须执行成功。如果其中有一条sql异常,则之前执行过的sql语句都要回滚,整个业务执行失败。
事务提交方式:
手动提交事务:
开启事务start transaction;
输入若干sql语句;
提交事务commit;
回滚事务rollback;
如果事务中 SQL 语句没有问题,commit 提交事务,会对数据库数据的数据进行改变。 如果事务中 SQL语句有问题,rollback 回滚事务,会回退到开启事务时的状态。
自动提交事务:
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务,每条语句都会自动开启一个事务,语句执行完毕,自动提交事务,MySQL 默认开始自动提交事务。
查看 MySQL 是否开启自动提交事务:select @@autocommit;
@@表示全局变量,1 表示开启,0 表示关闭
取消自动提交事务:set @@sutocommit = 0;
取消完自动事务提交,每次执行完增删改需要执行commit;进行事务的提交。
事务原理:
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中,其他任何情况都会清空事务日志(rollback,断开连接)
事务的步骤:
1) 客户端连接数据库服务器,创建连接时创建此用户临时日志文件
2) 开启事务以后,所有的操作都会先写入到临时日志文件中
3) 所有的查询操作从表中查询,但会经过日志文件加工后才返回
4) 如果事务提交则将日志文件中的数据写到表中,否则清空日志文件。
回滚点:
在某些成功的操作完成之后,后续的操作有可能成功有可能失败,但是不管成功还是失败,前面操作都已经成功,可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置,而不是返回所有操作,这个点称之为回滚点。
设置回滚点:savepoint 回滚点名称
回到回滚点:rollback to 回滚点名称
利用保存点可以实现只提交事务中部分处理的功能。
事务的四大特征(ACID):
原子性(atomicity):每个事务都是一个整体,不可再拆分,事务中所有的 SQL 语句要么都执行成功,要么都失败。
一致性(consistency):事务在执行前数据库的状态与执行后数据库的状态保持一致。如:转账前2个人的总金额是 2000,转账后 2 个人总金额也是 2000
隔离性(isolation):事务与事务之间不应该相互影响,执行时保持隔离的状态。
持久性(durability):一旦事务执行成功,对数据库的修改是持久的。就算关机,也是保存下来的。
事务的隔离级别:
查询隔离级别:select @@tx_isolation;
设置隔离级别:set global transaction isolation level 级别字符串;(需要退出mysql,重新登录才能看到隔离级别的变化)
串行化:使用这个隔离级别,一个事务没有执行完,其他事务就执行不了,可以挡住幻读。
DCL数据控制语言:
DDL:create/alter/drop/show
DML:insert/update/delete
DQL:select
DCL:grant(授予)/revoke(撤销)
注:mysqld 是 MySQL 的主程序,服务器端。mysql 是 MySQL 的命令行工具,客户端。
创建用户:
create user '用户名'@'主机名' identified by '密码';
用户名:将创建的用户名
主机名:指定该用户在哪个主机上可以登陆,如果是本地用户可用 localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%
密码:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器
注:创建的用户名都在 mysql 数据库中的 user 表中可以查看到,密码经过了加密。
给用户授权:
grant 权限1,权限2,等 on 数据库.表名 to '用户名'@'主机名';
grant 权限 on 数据库.表名 to 授权关键字
权限:create,alter,update,select,delete,insert等,要是授予全部权限就用ALL
数据库.表名:具体的每张表,如果是全部数据库的全部表,可以使用 *.*
注:用户名和主机名要与上面创建的相同,要加单引号。
撤销权限:
revoke 权限1,权限2,等 on 数据库.表名 from '用户名'@'主机名';
查看权限:
show grants for '用户名'@'主机名';
注:usage 是指连接(登陆)权限,建立一个用户,就会自动授予其 usage 权限(默认授予)。
删除用户 :
drop user '用户名'@'主机名';
修改管理员密码:
mysqladmin -uroot -p password 新密码
注意:需要在未登陆 MySQL 的情况下操作,新密码不需要加上引号。需要校验老密码。
修改普通用户的密码:
set password for '用户名'@'主机名' = password('新密码');
注意:需要在登陆 MySQL 的情况下操作,新密码要加单引号。不需要校验老密码。