mysql
一、数据库概念:
数据库管理:
数据库运维。
sql语句 数据库用来增删改查的语句
备份 数据库的数据进行备份 **
主从复制 读写分离 高可用 **
数据库的概念和相关语法规范 :
数据库:组织,存储,管理数据的仓库
数据库的管理系统"(DBMS)":实现对数据有效组织管理和存取的系统软件
软件 mysql oracle sql-server MariaDB 也是mysql mysqld
postgreSQL 大象数据库
二、mysql数据库:
关系型数据库和非关系型数据库
1、mysql数据库的存储引擎:
5.5之前:引擎是MYISAM,不支持事物和外键的存储引擎。适用于读的多,写得少
5.6之后:引擎是innodb,可以支持事务,外键,行级锁定的存储引擎。并且支持高并发性能的应用
mysql oracle postgreSQL
关系型数据存储的结构:是一张二维的表格,表里有行和列
表:行和列
行:记录,用来描述一个对象的信息
列:字段,用来描述对象的一个属性
非关系型数据库:
缓存型数据库:redis
索引型数据库:ES
文档型数据库:MongoDB
键值对形式存储的结构
key value
test1=3
关系型数据 | 非关系型数据库 | |
---|---|---|
优点 | 表的结构清晰,逻辑容易整理,记录的数据比较完整 | 高并发速写 对海量数据依旧可以保持高效率的存储和访问 架构可扩展 |
缺点 | 读写速度慢 并发量差 数据迁移比较麻烦 每个表都是关联的 | 键值对形式存储,数据逻辑比较复杂,数据是保存在缓存(内存)(redis),如果意外重启所有数据都会丢失 |
2、mysql中的名词
数据库 database
表 table
行 row
列 column
索引 index
视图 view
用户 user
权限 privilege
存储过程 procedure
存储函数 function
调度器 exent
3、mysql的语言规范:
在数据库系统中,sql语句是不区分大小写的,但是建议用大写
sql语句可以单行也可以多行但是需要 ; 结尾
4、数据库的命名规则:
命名规范 库名,表名,列的名字,都必须以字母开头,后面可以数字。后面也可以跟上特殊符号
不要使用mysql的保留字,table select show databases
数据库名,表名,用户名严格区分大小写。
5、mysql的字符类型
char:固定长度的字符串类型,用于存储固定长度的字符串
varchar:可变长度的字符类型,存储的是可变长度的字符串
char定义好了长度之后不论写的值是多少,都会占用固定长度的字节大小。保存在磁盘上的都是4字节。
varchar在保存字符串时,多少就保存多少,在保存的字符串结尾默认有一个隐藏的结束符,会多占一个字符
varchar比char要节约磁盘空间
从读写速度来说 :char高于varchar char是连续的磁盘空间,保存的内容是连续的
varchar在增删改查之后,会产生一个磁盘空间的碎片文件,影响读写性能
int 存储的数据类型为整数(大整数)**
float:浮点数,小数点 float(m,d)m表示总数为,d表示小数位
double :双精度浮点数 double(m,d)m表示总数为,d表示小数位
data:存储日期:YYYY-MM-DD
datatime:存储日期 YYYY-MM-DD
datatime:存储日期 YYYY-MM-DD HH:MM:SS
timestamp:和datatime格式一致YYYY-MM-DD HH:MM:SS 可以自动更新为当前时间戳
decimal(5,2):用于存储固定精度的小数,其中5,表示总位数,2表示小数位。
6、char varchar的区别(重点)
char:无论你是否定义了值,他都会占用固定长度的字节大小(你写了长度8,无论你写几个(不能大于8)他都是8个字符)
varchar:在保存时,varchar长度14,实际长度是5,那么占用情况就是5+1个隐藏符最后占用的字符是6个
varchar比char节省磁盘空间,但是varchar读写速度比char慢
varchar和char都是字符串类型:一定要用单引号引起来
三、数据库的管理:
增删改查–sql语句
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什么都没有,对象没有任何描述信息
空格:也是字符
select * from student where name is null; 查询哪个是空值
*鉴别是空格还是null*
**3、DML:**数据操作语言,对表中的数据进行管理(对表内容)
select查询
update更新
insert添加
delete删除(对表内容进行删除操作)
1、insert 插入数据
insert into 表名(字段1,字段2,字段3)values (1,2,3)
insert into student values(3,‘爱你’,22.33);
插入时间
insert into stu05 values(1,2,‘2024-07-16’,‘2024-07-16 09:43:00’); 插入时间
insert into stu07 values(1,2,‘2024-07-16’,now()); 实时时间
*对对象的密码进行加密:*
password (‘xxx’)
2、对表中内容进行更新修改:
update 表名 set 对象=修改内容 where id
*where id 是因为id是主键,唯一标识,不会重复查询没有歧义*
update 表名 set 列名=值 where 条件;
修改表名 alter table 旧表名 rename 新表名;
修改表的结构 alter table
3、如何在表中删除数据
4、DQL:查询数据记录(重点) select update insert delete
*1、指定查看行:*
limit 3:指定查看前三行
limit 2,4:指定从第3行开始往下查看4行
2、去重查看:
select distinct 对象 from 表名;
指定对象去重
多条件去重查询:
select distinct name,sroce from test;
3、修改表名:alter
alter table test rename demo;
4、修改表的结构:
插入新的对象:
alter table 表名 add 对象信息;
5、修改列对象名:
alter table test change birth birthday datetime;
6、删除整列:
alter table test drop birthday;
alter table woaini drop address;
7、修改列的数据类型:
alter table woaini modify column address char(10)
8、修改列的名称
alter table woaini change name username char (10);
alter table 表名 change 原来的名字 新名字 类型 ;
9、如何删除表的数据
delete from 表名 where 条件;
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、主键约束 primary key
用于标识表中的主键列的值,而且这个值是全表当中唯一的,而且值不能为null 一个表只能有一个主键
2、外键:用于简历表与表之间关系。确保外键中的值与另一个表的主键值匹配保证数据引用的完整性 多表联查 不要超过三张 超过三张会降低查询效率
3、非空约束 保证列中的值不含null值 not null,如果定义是 not null,那么必须要有一个值,空格也算。
4、唯一性约束:unique key确保列中的所有值都是唯一的,类似于主键,但是可以为空,而且一个表可以有多个唯一约束。
5、默认值约束:default,在插入表数据时,如果没有定义值,会提供一个默认值。
6、自增约束,在列生成的每一行都会自动生产一个标识符,通常和主键一起使用,每次插入新行时,自增列的值会自动增加 每行自动生成一个唯一标识
not null unique key : 不能为空不能重复
1、外键关联:
*创建主表:*
create table if not exists student (
crad_id int(5) primary key auto_increment,
stu_name char(10) not null,
stu_email varchar(128) not null unique key
);
*创建从表:*
create table class (
stu_id int(11) zerofill primary key auto_increment,
address varchar(128) default ‘地址不详’,
crad_id int(5) not null,
foreign key (crad_id) references student (crad_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;
删除索引
再在信息中找到外键名和索引名
先删除外键名和索引名
再删除外键
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外键只能关联主键或唯一键。
创建表
key 表示 是否主键或者外键
default 如果没有数据的默认展示结果
extra 提供列的附加信息 自增长等等
desc 查看表的结构
create table if not exists test01 (
表示在创建表时自动检测表是否已经存在,如果不存在就创建
id int (5) zerofill primary key auto_increment,
zerofill 5位 不足5位会自动填充0 auto_increment 自增长字段
每条记录自动递增1 自增长必须是主键,也不可以重复。如果添加失败下一条记录也会自动增加
key主键
default 不设置东西 默认的
extra 附加
六、数据库用户的管理:
创建用户
设置用户权限
取消权限
grant赋权语句最好在终端执行
不能免密登录:vim /etc/my.cnf修改将免密登录删除 skip-grant-tables
1、创建用户 create user
create user cgx’@‘192.168.183.250’ identified by ‘123456’;
cgx表示用户名
192.168.183.250 表示可以在哪些主机上登录
‘cgx’@'192.168.183.250’指定ip登录
‘cgx’@‘192.168.183.0/24’ 指定网段登录
‘cgx’@‘%’ 百分号表示所有,可以从任何设备登录
2、用户赋权
grant all privileges on *. *to ‘cgx’@‘192.168.183.250’ identified by ‘123456’;
grant 赋权的开头语句
若 on test.* 表示只能对指定的库test进行操作
on . 对所有库都有操作权限
赋权完刷新:
flush privileges;
*查看用户权限:*
show grants for ‘cgx’@‘192.168.183.250’;
赋完权之后只能在终端登录
mysql -h 20.0.0.31 -u test1 -p
3、删除用户的权限
revoke all privileges on * .* from ‘cgx’@‘192.168.183.250’;
设计权限更改要刷新 flush
*如何对权限进行控制:*
grant select on * .* to ‘cgx’@‘192.168.183.250’ identified by ‘123456’;
只能查数据库
设置之后检测,看看能不能增删改
如何给一个用户赋予多个权限:
grant select,insert,create on * .* to ‘cgx’@‘192.168.183.250’ identified by ‘123456’;
usage:允许该用户连接到数据库服务器
删除单个或多个权限
revoke drop,update on * .* from ‘cgx’@‘192.168.183.250’;
移除所有权限的时候,可以全部移除,不需要一个个删除,不需要和创建时一致
4、如何给用户重命名:
rename user ‘cgx’@‘192.168.183.250’ to ‘wyz’@‘192.168.183.240’;
5、删除用户:
drop user ‘cgx’@‘192.168.183.250’;
6、用户修改密码:
*修改当前登录用户的密码:(要在终端执行)*
set password=password(‘abc123’)
*修改其他用户的密码:*
set password for ‘cgx’@‘192.168.183.250’= passwoed(‘abc123’);
7、免密登录
vim /etc/my.cnf添加免密登录 skip-grant-tables
where语句 条件的筛选
select * from student where id = 7 and score =71; and且
select name from student where id = 5 or socre =77; or 或
模糊查询
like
select * from student where name like ‘杨%’;
select * from student where name like ‘%文’;
select * from student where name like ‘%白%’;
练习:
定义一个需求:
两张表:
主表:company
从表:depart
company:
- work_id 非空,主键,int(4),不满四位要补齐
- name 非空,char(5)
- sex 非空,char(2)
CREATE table company (
work_id int (4) zerofill primary key not null,
name char (5) not null,
sex char (2) not null
);
depart
- de_id 非空,主键,int(6),不满补齐6位
- work_id 要和主表的work_id 关联为外键
- address 可为空,但是有一个默认值
- phone 非空,不能重复
CREATE table depart (
de_id int (6) zerofill primary key not null,
work_id int (4) not NULL,
address char (5) default ‘牛’,
phone char (10) not null unique
);
片转存中…(img-uawP9ba9-1721203143541)]
select * from student where name like ‘%文’;
[外链图片转存中…(img-yZirckwh-1721203143541)]
select * from student where name like ‘%白%’;
[外链图片转存中…(img-kgFFADO4-1721203143541)]
练习:
定义一个需求:
两张表:
主表:company
从表:depart
company:
- work_id 非空,主键,int(4),不满四位要补齐
- name 非空,char(5)
- sex 非空,char(2)
CREATE table company (
work_id int (4) zerofill primary key not null,
name char (5) not null,
sex char (2) not null
);
depart
- de_id 非空,主键,int(6),不满补齐6位
- work_id 要和主表的work_id 关联为外键
- address 可为空,但是有一个默认值
- phone 非空,不能重复
CREATE table depart (
de_id int (6) zerofill primary key not null,
work_id int (4) not NULL,
address char (5) default ‘牛’,
phone char (10) not null unique
);