数据库的基本概念
主流数据库
数据库两大类
关系型数据库 SQL
存储结构:二维表格
存储的数据:结构化数据
使用场景:存储业务数据、账户信息等
对象: 库 —> 表 —> 二维表格形式的结构化数据
行(记录):用来描述一个对象的信息
列(字段):用来描述对象的一个属性
典型代表: MySQL(5.7/8.0) MariaDB PostgreSQL SQL server Oracle Db2
国产数据库代表 华为 高斯 阿里 Oceanbase 阿里云数据库 RDB 腾讯 TDBA 人大金仓 达梦 great SQL
非关系型数据库 NoSQL
存储结构:不同类型的noSQL采用不同的存储结构,比如 键值对 、 文档 、 索引 、 时间序列 等
缓存型数据库 Redis Memcached
文档型 MongoDB
索引型 Elasticsearch
时序型 Prometheus InfluxDB
列式型 HBase
MySQL数据库编译安装
官网下载mysql安装包
MySQL数据库操作管理
常用的数据库类型
Navicat连接使用图形化操作
同时也可以使用命令界面操作
查看当前的 MySQL 版本信息及连接用户名
SELECT VERSION();
SELECT USER();
查看当前服务器中的数据库
SHOW DATABASES; #大小写不区分,分号“;”表示结束
MySQL会附带4个系统数据库:
information_schema:主要存储系统中的一些数据库对象信息,如数据库名,数据库的表,表栏的数据类型与访问权限等
performance_schema:主要存储数据库服务器性能参数信息
mysql:主要存储系统的用户权限信息
sys:MySQL5.7之后引入的一个新的 sys 数据库,sys 库里面的表、视图、函数以及存储过程可以让用户快速了解MySQL的一些信息,它的数据来源于performance_schema
查看数据库中包含的表
USE 数据库名;
SHOW TABLES;
查看表的结构(字段)
USE 数据库名;
DESCRIBE [数据库名.]表名;
可缩写成:DESC 表名;
SQL语句操作
SQL语句用于维护管理数据库,包括数据查询、数据更新、访问控制、对象管理等功能。
SQL语言分类:
DDL:数据定义语言,用于创建数据库对象,如库、表、索引等
DML:数据操纵语言,用于对表中的数据进行管理
DQL:数据查询语言,用于从数据表中查找符合条件的数据记录
DCL:数据控制语言,用于设置或者更改数据库用户或角色权限
MySQL的六大约束属性
主键约束 primary key 字段的值不能重复,且不能为NULL,一个表只能有一个主键
唯一键约束 unique key 字段的值不能重复,但可以为NULL,一个表可以有多个唯一键
非空约束 not null 字段的值不能为NULL
默认值约束 default 'XXX' 字段的值如果没有设置值则使用默认值自动填充
自增约束 auto_increment 字段的值如果没有设置值会默认从开始每行自动递增 1 ,要求设置自增的字段必须为主键字段
外键约束 foreign key 用来保证外键表和主键表相关联数据的完整性和一致性
零填充 int(N) zerofill 使用0进行填充使字段的数组满足 N 位数
DDL创建及删除数据库和表
创建新的数据库
CREATE DATABASE 数据库名;
创建新的表
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值(NULL),一个表只能有一个主键。
例:
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE xy101 (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id));
删除指定的数据表
DROP TABLE [数据库名.]表名; #如不用USE进入库中,则需加上数据库名
删除指定的数据库
DROP DATABASE 数据库名;
生产环境中删除慎用
DML管理表中的数据记录
向数据表中插入新的数据记录
INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);
例:
INSERT INTO xy101(id,name,score,passwd) values(1,'zhangsan',70.5,PASSWORD('123456'));
#PASSWORD('123456'):查询数据记录时,密码字串以加密形式显示;若不使用PASSWORD(),查询时以明文显示。
INSERT INTO xy101 VALUES(2,'lisi',90.5,654321);
SELECT * FROM xy101; #查询表的数据记录
查询表数据记录
SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];
例:
SELECT * FROM xy101;
SELECT id,name,score FROM xy101 WHERE id=2;
select name from xy101\G #以列表方式竖向显示
select * from xy101 limit 2; #只显示头2行
select * from xy101 limit 2,3; #显示第2行后的前3行
修改、更新数据表中的数据记录
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
例:
UPDATE xy101 SET passwd=PASSWORD('') WHERE name='zhangsan';
UPDATE xy101 SET name='wangxiaoer',passwd='' WHERE id=3;
在数据表中删除指定的数据记录
DELETE FROM 表名 [WHERE 条件表达式];
DQL修改表名和表结构
修改表名
ALTER TABLE 旧表名 RENAME 新表名;
扩展表结构(增加字段)
ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default '地址不详':表示此字段设置默认值 地址不详;可与 NOT NULL 配合使用
修改字段(列)名,添加唯一键
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
例:
ALTER TABLE xy101 CHANGE name user_name varchar(10) unique key;
#CHANGE可修改字段名、数据类型、约束等所有项。
删除字段
ALTER TABLE 表名 DROP 字段名;
例:
ALTER TABLE xy101 DROP passwd;
数据表高级操作
克隆表,将数据表的数据记录生成到新的表中
方法一:
create table test01 like xy101; #通过 LIKE 方法,复制 xy101 表结构生成 test01 表
insert into test01 select * from xy101;
#此方法能保证 新表的表结构、表数据 跟旧表都是一致的
方法二:
CREATE TABLE test02 (SELECT * from xy101);
#此方法创建的新表的表数据和旧表是一样的,但可能会出现新表的表结构和旧表的不一致
show create table test02\G #获取数据表的表结构、索引等信息
SELECT * from test02;
清空表,删除表内的所有数据
delete from 表名; #一条一条的删除记录,清空表效率较慢;AUTO_INCREMENT自增字段仍保持原有的记录
truncate table 表名; #直接重建表,清空表效率较快;AUTO_INCREMENT自增字段的记录也会重置
临时表
create temporary table 表名 (....); #临时表跟一般的表的相同点:都可以进行增删改查表数据
区别:show tables; 查不到临时表;临时表只能再当前会话中有效,退出当前会话,临时表就会失效
创建外键约束,保证数据的完整性和一致性。
外键的定义:如果同一个属性字段X在表一中是主键,而在表二中不是主键,则字段X称为表二的外键。
主键表和外键表的理解:
(1)以公共关键字作主键的表为主键表(父表、主表)
(2)以公共关键字作外键的表为外键表(从表、外表)
创建主表calss 从表student
主键表: alter table 主表名 add primary key (公共字段);
外键表: alter table 从表名 add foreign key(公共字段) references 主表名(公共字段);
保证数据的完整性和一致性(插入数据时,需要先保证主键表的公共字段有相关数据,才能在外键表插入相关联的数据;删除数据时,需要先保证外键表已经没有相关数据,才能在主键表删除相关联的数据)
删除外键
alter table 表名 drop foreign key 外键别名;
alter table 表名 drop key 键名;
DCL:用于管理用户和权限
新建用户
CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
create user '用户名'@'来源地址' identified by '密码';
来源地址可以是 localhost、IP、网段、%(代表任意地址)、主机名
----------------------------------------------------------------------------------------------------------
'用户名':指定将创建的用户名
'来源地址':指定新创建的用户可在哪些主机上登录,可使用IP地址、网段、主机名的形式,本地用户可用localhost,允许任意主机登录可用通配符%
'密码':若使用明文密码,直接输入'密码',插入到数据库时由Mysql自动加密;
若使用加密密码,需要先使用SELECT PASSWORD('密码'); 获取密文,再在语句中添加 PASSWORD '密文';
若省略“IDENTIFIED BY”部分,则用户的密码将为空(不建议使用)
----------------------------------------------------------------------------------------------------------
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';
SELECT PASSWORD('abc123');
CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
查看用户信息
#创建后的用户保存在 mysql 数据库的 user 表里
USE mysql;
SELECT User,authentication_string,Host from user;
查看当前登录用户
select user();
重命名用户
rename user '用户名'@'来源地址' to '新用户名'@'来源地址';
删除用户
drop user '用户名'@'来源地址';
修改当前登录用户密码
set password for '用户名'@'来源地址' = password('密码'); #只能root用户使用
alter user '用户名'@'来源地址' identified by '密码';
如何找回 root 密码?
1)修改MySQL配置文件,在 [mysqld] 配置项下面添加配置 skip-grant-tables
2)重启MySQL服务,使用 mysql 命令即可直接登录数据库
3)执行命令 update mysql.user set authentication_string=password('密码') where user='root'; 来修改root用户密码
4)还原MySQL配置文件,再重启MySQL服务,即可使用命令 mysql -u root -p密码 [-h 服务器地址 -P 端口] 登录数据库
数据库用户授权
授予权限
grant 权限1,权限2,.... on 库名.表名 to '用户名'@'来源地址' [identified by '密码']; #在5.7或之前支持 grant 创建新用户和授予权限,8.0开始只能用于授予权限
all [privileges] *.*(代表 任意库.任意表)
flush privileges;
查看权限
show grant for '用户名'@'来源地址';
撤销权限
revoke 权限列表 on 库名.表名 from '用户名'@'来源地址';
all