数据库基础-进阶

07-17 1428阅读

数据库管理:

*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 服务。

数据库基础-进阶

VPS购买请点击我

文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。

目录[+]