数据库基础-进阶
数据库管理:
*sql语句 数据库用来增删改查的语句 ***
备份 数据库的数据进行备份 *
主从复制,读写分离,高可用 原理
数据库的概念和相关的语法和规范:
数据库:组织,存储,管理数据的仓库。
数据库的管理系统(DBMS):实现对数据有效组织,管理和存取的系统软件
mysql oracle(大数据系统使用) slq-server MariaDB postgreSQL (大象数据库)
关系型数据库和非关系型数据库
关系型数据库:mysql oracle postgreSQL
关系型数据存储的结构:是一张二维的表格,表格里面有行和列
列:是对象,是字段
行:对象的信息,字段的属性。
行+列:组成一张表。
非关系型数据库:
缓存型数据库:Redis
索引型数据库:ES
文档型数据库:MongoDB
键值对形式存储的结构
key value
关系型数据库和非关系型数据库的优缺点:
**关系型的优点:**表的结构清晰,逻辑容易整理,记录的数据比较完整。
**缺点:**读写速度慢,并发量差,数据迁移比较麻烦。
每个表都是有关联的
非关系数据库:
**优点:**高并发读写,对海量数据依旧可以保持1高效率的存储和访问。架构可扩展。
**缺点:**键值对形式存储,数据逻辑比较复杂,数据是保存在缓存当中(Redis),如果意外重启所有数据都会丢失。
mysql的数据类型:
char :固定长度的字符串类型 用于存储固定长度的字符串
varchar:可变长度的字符类型,存储的是可变长度的字符串。
char:定义好了长度之后,不论写的值是多少,都会固定长度的字节大小,保存在磁盘上的都是8字节。
varchar:在保存字符串时,多少就保存多少,在保存的字符串结尾默认有一个隐藏的结束符,会多占一个字节。
varchar比char要节约磁盘空间
**读写速度:**char的读写速度性能要高于varchar,char是连续的磁盘空间,保存的内容是连续的。
varchar在增删改查之后,会产生一个磁盘空间的碎片文件,影响读写性能.
int 存储的数据类型为整数
float: 单精度浮点数,小数点 float(m,d): m表示总位数,d表示小数位数。
double: 双精度浮点数 double(m,d),m表示总位数,d表示小数位数
date:用于存储日期,YYYY-MM-DD
datetime:用来存储日志和时间,格式YYYY-MMM-DD HH:MM:SS
timestamp:和datetime类似,但是他可以自动记录当前时间。
smallint:存储小整数
bigint:存储大整数
decimal(5,2):存储浮点,存储精度的浮点数,5表示总位数,2,表示小数位
数据库的管理:
增删改查
SQL中的名词
数据库:database
表:table
行:row
列:column
索引:index
视图:view
用户:user
权限:privilege
存储过程:procedure
存储函数:function
调度器:exent
sql语言规范:
在数据库系统中,sql语句不区分大小写,建议使用大写,;为结束语
sql语句可以分单行和多行但是一定要以分号为结尾
**命令规范:**库名,表名,列名,必须字母开头,后面可以跟上数字,后面也可以跟上特殊符号。
不要使用mysql的保留字,例如:table select show databases 等等不能使用。
数据库名,表名,用户名严格区分大小写。
sql语言分类
1、DDL 数据库定义语言: 创建数据库的对象语言,库,表和索引等等。
create drop
2、DML:数据库操作语言,对表里面数据进行管理
select update insert delete
3、DQL:数据库查询语言,数据库的查询语句
select
4、DCL:数据控制语言,控制和管理数据用户的角色和权限
grant revoke
5、tcl 事务控制语句,用来管理的数据库的事务 脚本开发,存储过程等等
commit rollback savepoint.
DDL:
create database xy102; 创建库 create table test01( id int(4) not null, #定义表的列的属性,数据类型不能为空 id int(4) not null, name char(10) not null, score decimal(5,2) ); Key | Default | Extra key:表示是否是主键或者外键 default:如果没有数据的默认展示结果 extra:提供列的附加信息,自增长等等。
DML和DQL 管理语句和查询语句
insert 插入语句 insert into 表名(字段1,字段2,字段3) values(字段1,字段2,字段3); insert into 表名 values (2,'wdd',6); insert 插入数据时,插入时间类型 date datetime timestamp CREATE TABLE stu01 ( id INT (5) PRIMARY KEY, NAME CHAR (10) NOT NULL UNIQUE KEY, date_time date NOT NULL, date_times datetime ); INSERT INTO stu01 VALUES (1,2,'2024-08-26','2024-08-26 14:00:00'); CREATE TABLE stu066 ( id INT (5) PRIMARY KEY, name CHAR (10), date_time date NOT NULL, date_times TIMESTAMP NOT NULL ); INSERT INTO stu066 VALUES (1,2,'2024-08-26',NOW()); #插入当时系统时间 SELECT * FROM stu066; desc student; 查看表的结构。 null值与空值之间的区别: null就是什么都没有就是为空。 空值也是值,只是值是空的 修改和更新数据 update update 表名 set 需要修改的列名=修改之后的值 where 唯一标识; 举例:UPDATE student SET NAME='杨凯文' WHERE id=5; 删除 delete 删除表的数据 DELETE FROM 表名 where 需要删的列 where 条件; DQL语句 查询语句 select score,NAME from student; 查看student的score列和name列 查看指定行 select * from student limit 0,3; 查看student表中的第一行到第三行 去重查询 SELECT DISTINCT id FROM student; SELECT DISTINCT NAME FROM student; where语句 根据条件进行筛选 and 且 OR 或 SELECT * FROM student WHERE id=5 AND score =100; SELECT * FROM student WHERE id=5 OR score =100; 模糊查询 LIKE select * FROM student; SELECT * FROM student where name like 'F%' #以F为开头 SELECT * FROM student where name LIKE '%B' #以B为结尾 SELECT * FROM student where name LIKE '%B%' #name中有B的都进行匹配(包含内容) ALTER 可以修改表名和修改表的结构 修改表名 ALTER TABLE student RENAME stu01; #将student表名修改为stu01 修改表结构 给表添加一列为address 默认为地址不详 ALTER TABLE student add address VARCHAR(50) DEFAULT '地址不详'; 修改字段的数据类型; ALTER table student MODIFY COLUMN address CHAR(10); 删除列 ALTER TABLE student DROP address; 修改列名 alter table student CHANGE address addresss VARCHAR(50);
约束条件和用户管理
约束条件: 主键 主键约束 primary key 用于标识表中的主键列的值,而且这个值是全表当中唯一的,而且这个值不能为null 一个表只能有一个主键 创建主键的两种方式 create TABLE stu03 ( id INT(5) PRIMARY KEY, name CHAR(10), score DECIMAL(5,2), address VARCHAR(128) );
**外键(UNIQUE KEY):**用来建立表与表之间的关系,确保外键中的值于另一个表的主键值匹配。保证数据引用的完整性。
主键配置PRIMARY KEY 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 ); 外键与主键关联REFERENCES 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) ); 主键与外键关联所有的字符的类型都要保持一致,主键所在表的数据类型与外键关联的数据类型都需要一致。 INSERT INTO student VALUES (411421,'FBB','2727088244'); INSERT INTO class VALUES (01,'南京市',411421); 先增加主键的内容,再增加外键的内容,外键增加内容的时候与主键关联的值要一直。(相对于一个比对的功能,比对是否正确添加) 修改主键,需要先关闭自增 ALTER TABLE class MODIFY stu_id int(11); 修改从表的主键关联 ALTER TABLE class DROP PRIMARY KEY; 主键和外键 外键就是和主表进行关联的列,不需要设置为从表的主键,但是不能为空,必须和主表的数据类型保持一致。 外键的值和主键的值要相同。 先插入主表的数据,在插入从表的数据 删除表的1外键不是直接删除外键的列名,而是删除外键的索引,show create table 表名;查看表的详细信息。 删除主键不需要加入主键的列名。如果有extra的额外属性,比如自增长等等要先移除属性(ALTER TABLE class MODIFY stu_id int(11);),然后删除属性,然后才能删除主键 删除主键方式: alter table 表名 drop PRIMARY KEY; NOT NULL约束:确保列中的值不为NULL。 UNIQUE约束:确保列中的所有值都是唯一的。 PRIMARY KEY约束:是NOT NULL和UNIQUE的组合,确保表中每行都有一个唯一标识。 FOREIGN KEY约束:定义了两个表之间的关系,确保一个表中的值必须在另一个表的列中存在。 CHECK约束(MySQL 8.0.16及以后版本支持):允许你指定列中值的范围或条件。
**多表联查,不要超过三张。**超过三种降低查询效率
非空约束:保证列中的值不含null值 not null
唯一性约束:确保列中的所有值都是唯一的,类似主键,但是一个表可以有多个唯一约束。
自增约束,在列生成的每一行都会自动生成一个唯一标识符。,通常和主键一起使用,每次插入新行时,自增列的值会自动增加。
CREATE TABLE if NOT EXISTS test01 ( #表示在创建表时自动检测表是否已经存在,如不存在就创建 id INT(5) ZEROFILL PRIMARY KEY auto_increment, #ZEROFILL,5位,不足五位自动填充0, #auto_increment自增长字段,每条记录自动递增1,必须为主键,不可以重复,如果添加失败 #下一条记录也会自动增加 name CHAR (10), score DECIMAL (5,2), address VARCHAR (128) );
复制表 create TABLE stu_01 LIKE student; 复制student的表的结构为stu_01 desc stu_01; INSERT INTO stu_01 SELECT * FROM student; 复制表的内容 SELECT * FROM stu_01; CREATE TABLE stu_02 (SELECT * FROM student); 复制表的数据同时复制表的内容并导入 SELECT * FROM stu_02;
临时表创建完成之后,在库里面是看不到的,但是依然可以增删改查,但是重新连接数据之后,临时表就会消失
创建表的时候加入TEMPORARY CREATE TEMPORARY TABLE class01 ( stu_id INT(11) ZEROFILL PRIMARY KEY auto_increment, address VARCHAR(128) DEFAULT '地址不详', crad_id INT(5) not NULL ); INSERT INTO class01 VALUES (1,'NAN',411421); SELECT * FROM class01;
清空表的数据
清空表的数据 面试:如何删除表内的数据,但是保留表和表结构? drop 删除表; delete from 表名; 一行一行的清空表数据,速度比较慢,如果有自增长字段,delete清空之后,会继续按照原来的序号,继续递增 ALTER TABLE 表名 MODIFY 自增长字段和数据类型; TRUNCATE TABLE 表名; 清空表,保留表的结构,但是清空之后原有的记录全部抹去,自增长也将从头开始,速度比较快
数据库的用户管理
root都是相同的,Host:可以登录的主机 localhost指的时本地登录 %:任意主机(ip地址) 权限1上:localhost > %的权限 root@localhost 安装mysql之后就有了,不需要额外设置,其他的都需要人工创建,其他用户不设置为localhost 都是设置为主机名 SELECT * from user; 查看用户信息 一、创建用户 create user 'FBB'@'192.168.11.144' 用户FBB只能登陆192.168.11.144这台主机 CREATE USER 'FBB'@'192.168.11.144' IDENTIFIED BY '123456'; 设置用户权限 GRANT ALL PRIVILEGES ON *.* TO 'FBB'@'192.168.11.144'; FLUSH PRIVILEGES; 刷新权限 grant select on xy102.* to FBB@192.168.11.144; #用户FBB只能登录192.168.11.144,只能进行对xy102库下所有的表进行查询 grant select,insert,.... on xy102.* to FBB@192.168.11.144; 给多个权限用,隔开 移除权限 revoke all privileges on *.* from FBB@192.168.11.144; FLUSH PRIVILEGES; 刷新权限 revoke insert,update,alter,delete on xy102.* from FBB@192.168.11.144; FLUSH PRIVILEGES; 刷新权限 show grants for 'FBB'@'192.168.11.144'; 查看用户权限 GRANT USAGE ON *.* TO `FBB`@`192.168.11.144` #允许用户登录的权限 GRANT SELECT ON `xy102`.* TO `FBB`@`192.168.11.144` 只允许用户查询xy102库下的所有表 删除用户 DROP USER 'FBB'@'192.168.11.144';
information_schema : 这个库包含了mysql服务器中,所有其他数据的库、表、列、索引等详细的元数据的信息,可以用来查询数据库的结构和元数据信息 performance_schema : 包含mysq1的服务性能和资源利用情况。查询语句的执行时间和锁定等信息
数据库忘记登录密码怎么办?
(1)修改 /etc/my.cnf 配置文件,不使用密码直接登录到 mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加,使登录mysql不使用授权表
systemctl restart mysqld
mysql #直接登录
(2)使用 update 修改 root 密码,刷新数据库
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD(‘abc123’) where user=‘root’;
FLUSH PRIVILEGES;
quit
mysql -u root -pabc123
注意:最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除,并重启 mysql 服务。
y.cnf
[mysqld]
skip-grant-tables #添加,使登录mysql不使用授权表
systemctl restart mysqld
mysql #直接登录
(2)使用 update 修改 root 密码,刷新数据库
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD(‘abc123’) where user=‘root’;
FLUSH PRIVILEGES;
quit
mysql -u root -pabc123
注意:最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除,并重启 mysql 服务。