目录
2、DDL:数据定义语言,用于创建数据库的对象,创建库、表、索引
一、数据库的概念:
进入数据库的奇妙世界:mysql -u root -p123456
数据库是一个系统,是一个人机系统。由硬件、OS操作系统、数据库、DBMS和数据库的 用户共同组成。
用户时通过DBMS(各种数据库的软件)来对数据库进行操作。
数据:数字信息:属性,对一系列对象的具体属性的描述集合
数据库:数据库就是用来组织(各个数据之间是有关联的,是按照规则组织起来的),存储和管理(对数据的增删改查)数据的仓库
数据库是企业的重要信息资产,在使用数据库时,要注意(查和增无所谓,但是删和改要谨慎)
数据库管理系统(DBMS):实现对数据的有效组织,管理和存取的系统软件——mysql
1、数据库工作流程图:
2、数据库的分类:
关系型数据库:mysql
非关系型数据库:redis、es
关系型数据库:存储数据的结构是一个二维表格
表:行和列
行:记录,用来描述一个对象的信息
列:字段,用来描述对象的一个属性
mysql:中小型的数据并发请求
oracle:高并发大数据量
sql-server
mariaDB
postgresql
关系型数据库中,表中的数据是关联的,表和表之间的数据也是关联起来的
多表关联最多可以关联几张表?
多表关联最多三张表
非关系型数据库:保存数据不是一张二维表格,key——value,键值对来保存数据。
redis:缓存性的数据库
es:索引型数据库
MongoDB:文档型数据库
既然有关联,查询速度相对来说,关系型数据库查询速度更慢一点,非关系型数据库可以支持高并发读写。对海量数据依旧保持着高效率的存储和访问。
二、mysql数据库:
1、mysql数据库的存储引擎:
5.5之前:引擎是MYISAM,不支持事物和外键的存储引擎。适用于读的多,写得少
5.6之后:引擎是innodb,可以支持事务,外键,行级锁定的存储引擎。并且支持高并发性能的应用
事务:在数据库中,一个或者多个操作组成的序列
这些操作要么全部成功,失败一个就是全部不执行。原因是为了确保数据的一致性和完整性
事务的特点:
原子性。数据库的最小最小工作单位,要么全部不执行,只要有一个操作时间,整个执行的序列都会被回滚。完成的操作也会被撤
一致性:事务执行前后,数据库的完整性约束不能被破坏。只有在满足所有的约束条件的情况下,事务才能被提交。
隔离性:事务的执行是互相隔离的,一个事务的执行不能受到其他执行事务的干扰。并发事务之间互相隔离。防止数据不一致的情况发生
持久性:事务一旦提交,他所做的所有修改会被永久的保存在数据库中。即使系统崩溃提交的数据也不会丢失。确保数据库的状态始终处于一致性的状态。确保数据的完整和一致
2、mysql的名词:
数据库:database
表:table 行:row 列:column
索引:index
视图:view
存储过程:procedure
触发器:trigger
用户:user
权限:privilege
3、mysql语句的规范:
在数据库系统中,sql不区分大小写,但是建议大写。语句不区分,但是表名严格区分大小写
sql语句可以单行也可以多行书写,但是默认都以分号;结尾
关键词不能跨行或者简写。
子语句通常位于独立行,便于编辑,提高可读性
不支持双引号,只能用单引号
4、数据库的命名规则:
必须以字母为开头,后面可以包含数字,特殊字符:# _ $
不能使用mysql的保留字来命名表或者数据库:table select
数据库名 表名 用户名称严格区分大小写
5、数据库的字符类型:
int:占4个字节,用来存储整数
char:固定长度的字符串,用来存储定长的字符串
varchar:可变长度的字符类型,存储可变长度的字符串(不是无限制的随便写)
float(m,d):单精度浮点,存储浮点数。m表示总位数,d表示小数位
double:浮点类型(m,d):双精度浮点数,存储浮点数,m也是总位数,d表示小数位
decimal(5,2):用于存储固定精度的小数,其中5,表示总位数,2表示小数位。
text:用于存储大文本数据,文档,或者长字符串
image:二进制存储图像,图片,多媒体
data:存储日期:YYYY-MM-DD
datatime:存储日期 YYYY-MM-DD HH:MM:SS
timestamp:和datatime格式一致YYYY-MM-DD HH:MM:SS 可以自动更新为当前时间戳
6、char varchar的区别(重点)
char:无论你是否定义了值,他都会占用固定长度的字节大小(你写了长度8,无论你写几个(不能大于8)他都是8个字符)
varchar:在保存时,varchar长度14,实际长度是5,那么占用情况就是5+1个隐藏符最后占用的字符是6个
varchar比char节省磁盘空间,但是varchar读写速度比char慢
varchar和char都是字符串类型:一定要用单引号引起来
三、数据库管理:
1、sql语句:
DDL:create、drop、alter
DML:操纵语句,对数据进行管理
update、insert into、delete、truncate
DQL:查询语句 select
DCL:权限控制语句 grant、revoke
2、DDL:数据定义语言,用于创建数据库的对象,创建库、表、索引
CREATE:创建
DROP:删除
ALTER:修改
show global variables like 'port';
数据库当前使用的端口查看:3306
show databases;
查看当前有多少库
use 库名;
使用指定数据库
show tables;
查询指定库中有哪些表
如何查看表的结构:
describe 表名;
纵向查看表:
describe 表名\G; 缩写:desc 表名\G;
创建库:
create database 库名;
在库中创建表:
create table 表名 表内容属性...
create table test (id int(4) not null,name varchar(12) not null,sroce decimal(5,2),passwd char(48),primary key(id));
创建表:
id int(4) not null
第一列:名字是id,字符类型是int ,(4)字符长度是4,not null不能为空
name varchar(12) not null
第二列:名字是name,字符类型varchar, (12)字符长度是12,not null不能为空
sroce decimal(5,2)
第三列:名字是sroce,字符类型是decimal , (5,2)总长度是5,小数点后面最多2位,可以为空
passwd char(48)
第四列:名字是passwd,字符类型是char ,(48)字符长度最多为48,可以为空
primary key(id)
primary key 主键 (id)将id作为主键,唯一标识
主键用于唯一标识表中的每一条数据。不能重复, 不能为空。
create table test (id int(4) not null,name varchar(12) not null,sroce decimal(5,2),passwd char(48),primary key(id));
删除表:
drop tables 表名;
删除库:
drop database 库名;
类型 数据类型 值是否能为空 键值 默认值是否能为空 扩展信息
1、null和空格的区别:
两个概念
null什么都没有,对象没有任何描述信息
空格:也是字符
鉴别是空格还是null
3、DML:数据操作语言,对表中的数据进行管理(对表内容)
select查询
update更新
insert添加
delete删除(对表内容进行删除操作)
往表中插入数据:
方法一:指明插入的对象和插入的值
insert into表名(类型) values(对应的值)
insert into test (id,name,sroce,passwd) values(1,'刘备',97,'111');
方法二:直接按表格对象的顺序插入值
insert into test values(2,'关羽',99,'222');
对对象的密码进行加密:
password ('xxx')
对表中内容进行更新修改:
update 表名 set 对象=修改内容 where id
where id 是因为id是主键,唯一标识,不会重复查询没有歧义
如何在表中删除数据:
4、DQL:查询数据记录(重点)
指定查看行:
limit 3:指定查看前三行
limit 2,4:指定从第3行开始往下查看4行
去重查看:
select distinct 对象 from 表名;
指定对象去重
多条件去重查询:
select distinct name,sroce from test;
修改表名:alter
alter table test rename demo;
修改表的结构:
插入新的对象:
alter table 表名 add 对象信息;
修改列对象名:
alter table test change birth birthday datetime;
删除整列:
alter table test drop birthday;
修改列的数据类型:
5、DCL:数据控制语言
用于设置或者更改数据库用户或者用户的权限
GRANT:赋权
REVOKE:取消权限
6、TCL:事务控制语句,管理数据库当中的事务
commit:确认提交事务
ROLLBACK:回滚,事务提交之后无法回滚
savepoint:保存点,可以回滚
四、扩展语句:
create table if not exists ky32 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cradid int(18) unique key,
hobby varchar (50)
);
if not exists ky32:ky32这个表不存在才会创建
zerofill:自动填充位置1——0001
auto_incremant:表示该字段可以自增长,默认从1开始,每条记录会自动的自增1
primary key:当前表的主键,主键只能有一个,而且唯一,而且不能为空
unique key:唯一性的约束。跟主键不同,可以为空
1、表格复制:
通过like语法可以直接复制ky32的表结构。只是复制表的结构,不能复制表的结构
create table test1 like ky32;
将表的内容复制:必须两张表的结构一致
把ky32表的数据复制到test,两个表的数据结构要一致
insert into test select * from ky32;
完整复制一张表:
create table test2 (select * from ky32);
创建一张表,test2,数据内容和表的结构和ky32一样
2、如何删除表内的所有数据:清空表
delete、truncate、drop
delete清空表:delete删除时一行一行删除,如果表中有自增长列,清空所有记录之后,再出添加内容,会从原来的记录之后继续自增写入
delete from test2;
truncate清空:表的数据和结构会全部清空,但是表还在,工作中推荐使用
清空表的数据。而且会把表结构重新建立。速度比dalete块,推荐
truncate table test1;
drop删除整个表:直接把表删除了
drop table test1;
3、创建临时表:
临时表一般用于调试,而且临时表创建之后在表的目录中是不显示的,连接退出之后临时表会被销毁,而且这个临时表无法创建外键
create temporary table test1 (
id int(4) primary key,
name char(10),
sex char(2)
);
可以插入内容,断开连接之后消失
五、mysql的约束方式:(常见的六种)
1、主键约束,用于唯一标识表中的每一行,主键列的值必须是唯一而且不能为空,一个表只能有一个主键
2、外键约束:用于建立表与表之间的关系,一般是和另一张表的主键关联。保证数据引用的完整性。一个表可以有多个外键
3、非空约束:not null,如果定义是 not null,那么必须要有一个值,空格也算。
4、唯一性约束:unique key确保列中的所有值都是唯一的,类似于主键,但是可以为空,而且一个表可以有多个唯一约束。
5、默认值约束:default,在插入表数据时,如果没有定义值,会提供一个默认值。
6、自增约束:每行自动生成一个唯一标识
1、外键关联:
创建主表:
create table student (
card_id int(18) not null primary key,
stu_name varchar(12) not null,
stu_email varchar(255) unique
);
创建从表:
create table class (
stud_id int(11) auto_increment primary key,
address varchar(50) default '地址不详',
card_id int (18) not null,
foreign key (card_id) references student (card_id)
);
show create table class;
查看表的关联关系
2、主表和从表:
插入数据:先插入主表,再插入从表
删除数据:先删主表,再删除从表
3、删除外键过程:
先show create table test;查看表的关联信息
再在信息中找到外键名和索引名
先删除外键名和索引名
再删除外键
alter table class drop foreign key class_ibfk_1;
删除外键
alter table class drop index card_id;
删除索引
4、删除主键过程:
删除主键要先改变自增类型
删除主键:因为是一个自增约束的主键,要先改变它的数据类型,解除自增约束,之后才能删除主键——删除主键要先改变自增类型
先改自增约束:
alter table class modify stud_id int(12);
再删除主键:
alter table class drop primary key;
在对主键约束中,约束条件有先后顺序:
id int(4) zerofill PRIMARY KEY auto_increment,
MySQL外键只能关联主键或唯一键。
5、练习:
定义一个需求:
两张表:
主表:company
从表:depart
company:
- work_id 非空,主键,int(4),不满四位要补齐
- name 非空,char(5)
- sex 非空,char(2)
depart
- de_id 非空,主键,int(6),不满补齐6位
- work_id 要和主表的work_id 关联为外键
- address 可为空,但是有一个默认值
- phone 非空,不能重复
需求:
主表:school
- de_id int(4) 不满四位要补齐,自增长,主键
- name varchar(15) not null
- email varchar(45) 可空,默认值'bdqnkgc@126.com'
从表:class
- id 自增长主键 int(6)
- class_name 非空
- de_id 主键关联
- address 可空 默认 地址不详
- phone int(11) 非空,不能重复
创建完之后删除外键关联,删除从表的主键,重新定义主键为phone
先创建主表school:
create table school (
de_id int(4) zerofill primary key auto_increment,
name varchar(15) not null,
email varchar(45) default 'bdqnkgc@126.com'
);
再创建从表class:
create table class (
id int(6) primary key auto_increment,
class_name varchar(25) not null,
de_id int(4) zerofill,
address varchar(200) default '地址不详',
phone int(11) unique,
foreign key(de_id) references school(de_id)
);
检测:先插入主表再插入从表
看de_id是否对应
删除外键关联,删除从表的主键,重新定义主键为phone:
desc class;
查看从表的主键名,索引名:
alter table class drop FOREIGN key class_ibfk_1;
删除关联主键
alter table class drop index de_id;
删除关联索引
alter table class MODIFY id int(5);
更改主键之前,要先先更改自增约束
改主键对象的数据类型
alter table class drop primary key;
删除主键
alter table class add primary key(phone);
将phone的类型改为主键
desc class;
六、数据库用户的管理:
创建用户
修改用户的权限
删除用户
grant赋权语句最好在终端执行
不能免密登录:vim /etc/my.cnf修改将免密登录删除 skip-grant-tables
1、创建用户:create user
create user 'koeda'@'localhost' identified by '123456';
koeda表示用户名
localhost表示新建的用户koeda可以在哪些主机上登录。既可以使用IP地址,也可以使用网段,也可以使用主机名
'koeda'@'20.0.0.20' 指定ip登录
'koeda'@'20.0.0.0/24' 指定网段登录
'koeda'@'%' 百分号表示所有,可以从任何设备登录
2、用户赋权:(要在终端里面写)
赋权koeda对所有库进行所有操作权限:
grant all privileges on *.* to 'koeda'@'localhost' identified by '123456';
grant 赋权的开头语句
all privileges 赋予所有权限
on *.* 对所有库都有操作权限
若 on test.* 表示只能对指定的库test进行操作
to 'koeda'@'localhost' 赋权给哪个用户,要和创建用户赋权的格式一致
identified by '123456' 使用哪个密码进行登录。创建用户的时候可以省略
赋权完刷新:
flush privileges;
查看用户权限:
show grants for 'koeda'@'localhost';
赋完权之后只能在终端登录
mysql -h 20.0.0.31 -u test1 -p
3、练习:
创建一个用户test1
只允许该用户从20.0.0.32终端登录
只能对kgc库有权限,其他的库一律不行
先创建一个用户,只允许该用户从20.0.0.32登录:
create user 'test1'@'20.0.0.32' IDENTIFIED by '123456';
修改权限,只能对kgc库权限,其他库一律不行
grant all PRIVILEGES on kgc.* to 'koeda'@'20.0.0.32' IDENTIFIED by '123456';
4、删除用户的权限
删除权限revoke
revoke all privileges on kgc.* from 'test1'@'20.0.0.31';
权限匹配只能和创建的时候一致,
创建的时候是什么,删的时候就是什么
创建的时候是kgc.*,删的时候不能是*.*,只能值kgc.*
如何对权限进行控制:
grant select on kgc.* to 'test1'@'20.0.0.31' identified by '123456';
只能查数据库
设置之后检测,看看能不能增删改
如何给一个用户赋予多个权限:
grant select,insert,drop on kgc.* to 'test1'@'20.0.0.31' identified by '123456';
revoke drop,update on kgc.* from 'test1'@'20.0.0.31';
移除所有权限的时候,可以全部移除,不需要一个个删除,不需要和创建时一致
revoke all on kgc.* from 'test1'@'20.0.0.31';
5、如何给用户重命名:
rename user 'test1'@'20.0.0.31' to 'test2'@'20.0.0.31';
6、删除用户:
drop user 'test2'@'20.0.0.31';
7、用户修改密码:
修改当前登录用户的密码:(要在终端执行)
set password=password('abc123')
修改其他用户的密码:
set password for 'test1'@'20.0.0.31' = passwoed('abc123');
8、问题:忘记了root密码。如何恢复?
vim /etc/my.cnf添加免密登录 skip-grant-tables
登录过程回车即可
select User,authentication_string,Host from user;查看密码
修改root用户的密码
update user set authentication_string=password('123456') where Host='localhost' and User='root';
flush privileges;
改密码要加密改
select User,authentication_string,Host from user;查看密码
用户权限管理:
create user '用户名'@'20.0.0.21' identified by '123456';
grant all / select,insert,drop 赋权
revoke all / select,insert,drop 删除权限要和赋权格式一致
rename
drop user
set passwd=passwd('123456')
set password for '用户名'@'localhost'=password('abc123');
show grants for '用户名'@'localhost';
查看权限
flush privileges;
刷新权限