数据库概念和sql语句+库表管理操作+数据库用户管理

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：

1. work_id 非空，主键，int(4)，不满四位要补齐
2. name 非空，char(5)
3. 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

1. de_id 非空，主键，int(6),不满补齐6位
2. work_id 要和主表的work_id 关联为外键
3. address 可为空，但是有一个默认值
4. 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：

1. work_id 非空，主键，int(4)，不满四位要补齐
2. name 非空，char(5)
3. 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

1. de_id 非空，主键，int(6),不满补齐6位
2. work_id 要和主表的work_id 关联为外键
3. address 可为空，但是有一个默认值
4. 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
);