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

目录

一、数据库的概念：

1、数据库工作流程图：

2、数据库的分类：

二、mysql数据库：

1、mysql数据库的存储引擎：

2、mysql的名词：

3、mysql语句的规范：

4、数据库的命名规则：

5、数据库的字符类型：

6、char varchar的区别（重点）

三、数据库管理：

1、sql语句：

2、DDL：数据定义语言，用于创建数据库的对象，创建库、表、索引

1、null和空格的区别：

3、DML：数据操作语言，对表中的数据进行管理（对表内容）

4、DQL：查询数据记录（重点）

5、DCL：数据控制语言

6、TCL：事务控制语句，管理数据库当中的事务

四、扩展语句：

1、表格复制：

2、如何删除表内的所有数据：清空表

3、创建临时表：

五、mysql的约束方式：（常见的六种）

1、外键关联：

2、主表和从表：

3、删除外键过程： 

4、删除主键过程：

5、练习：

六、数据库用户的管理：

1、创建用户：create user

2、用户赋权：（要在终端里面写）

3、练习：

4、删除用户的权限

5、如何给用户重命名：

6、删除用户：

7、用户修改密码：

8、问题：忘记了root密码。如何恢复？

---

一、数据库的概念：

进入数据库的奇妙世界：mysql -u root -p123456

数据库是一个系统，是一个人机系统。由硬件、OS操作系统、数据库、DBMS和数据库的 用户共同组成。

用户时通过DBMS（各种数据库的软件）来对数据库进行操作。

数据：数字信息：属性，对一系列对象的具体属性的描述集合

数据库：数据库就是用来组织（各个数据之间是有关联的，是按照规则组织起来的），存储和管理（对数据的增删改查）数据的仓库

数据库是企业的重要信息资产，在使用数据库时，要注意（查和增无所谓，但是删和改要谨慎）

数据库管理系统（DBMS）：实现对数据的有效组织，管理和存取的系统软件——mysql

1、数据库工作流程图：

2、数据库的分类：

关系型数据库：mysql

非关系型数据库：redis、es

关系型数据库：存储数据的结构是一个二维表格

表：行和列

行：记录，用来描述一个对象的信息

列：字段，用来描述对象的一个属性

mysql：中小型的数据并发请求

oracle：高并发大数据量

sql-server

mariaDB

postgresql

关系型数据库中，表中的数据是关联的，表和表之间的数据也是关联起来的

多表关联最多可以关联几张表？

多表关联最多三张表

非关系型数据库：保存数据不是一张二维表格，key——value，键值对来保存数据。

redis：缓存性的数据库

es：索引型数据库

MongoDB：文档型数据库

既然有关联，查询速度相对来说，关系型数据库查询速度更慢一点，非关系型数据库可以支持高并发读写。对海量数据依旧保持着高效率的存储和访问。

二、mysql数据库：

1、mysql数据库的存储引擎：

5.5之前：引擎是MYISAM，不支持事物和外键的存储引擎。适用于读的多，写得少

5.6之后：引擎是innodb，可以支持事务，外键，行级锁定的存储引擎。并且支持高并发性能的应用

事务：在数据库中，一个或者多个操作组成的序列

这些操作要么全部成功，失败一个就是全部不执行。原因是为了确保数据的一致性和完整性

事务的特点：

原子性。数据库的最小最小工作单位，要么全部不执行，只要有一个操作时间，整个执行的序列都会被回滚。完成的操作也会被撤

一致性：事务执行前后，数据库的完整性约束不能被破坏。只有在满足所有的约束条件的情况下，事务才能被提交。

隔离性：事务的执行是互相隔离的，一个事务的执行不能受到其他执行事务的干扰。并发事务之间互相隔离。防止数据不一致的情况发生

持久性：事务一旦提交，他所做的所有修改会被永久的保存在数据库中。即使系统崩溃提交的数据也不会丢失。确保数据库的状态始终处于一致性的状态。确保数据的完整和一致

2、mysql的名词：

数据库：database

表：table  行：row  列：column

索引：index

视图：view

存储过程：procedure

触发器：trigger

用户：user

权限：privilege

3、mysql语句的规范：

在数据库系统中，sql不区分大小写，但是建议大写。语句不区分，但是表名严格区分大小写

sql语句可以单行也可以多行书写，但是默认都以分号；结尾

关键词不能跨行或者简写。

子语句通常位于独立行，便于编辑，提高可读性

不支持双引号，只能用单引号

4、数据库的命名规则：

必须以字母为开头，后面可以包含数字，特殊字符：# _ $

不能使用mysql的保留字来命名表或者数据库：table select

数据库名 表名 用户名称严格区分大小写

5、数据库的字符类型：

int：占4个字节，用来存储整数

char：固定长度的字符串，用来存储定长的字符串

varchar：可变长度的字符类型，存储可变长度的字符串（不是无限制的随便写）

float（m,d）：单精度浮点，存储浮点数。m表示总位数，d表示小数位

double：浮点类型（m,d）：双精度浮点数，存储浮点数，m也是总位数，d表示小数位

decimal（5,2）：用于存储固定精度的小数，其中5，表示总位数，2表示小数位。

text：用于存储大文本数据，文档，或者长字符串

image：二进制存储图像，图片，多媒体

data：存储日期：YYYY-MM-DD

datatime：存储日期 YYYY-MM-DD HH:MM:SS

timestamp：和datatime格式一致YYYY-MM-DD HH:MM:SS  可以自动更新为当前时间戳

6、char varchar的区别（重点）

char：无论你是否定义了值，他都会占用固定长度的字节大小（你写了长度8，无论你写几个（不能大于8）他都是8个字符）

varchar：在保存时，varchar长度14，实际长度是5，那么占用情况就是5+1个隐藏符最后占用的字符是6个

varchar比char节省磁盘空间，但是varchar读写速度比char慢

varchar和char都是字符串类型：一定要用单引号引起来

三、数据库管理：

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什么都没有，对象没有任何描述信息

空格：也是字符

鉴别是空格还是null

3、DML：数据操作语言，对表中的数据进行管理（对表内容）

select查询

update更新

insert添加

delete删除（对表内容进行删除操作）

往表中插入数据：

方法一：指明插入的对象和插入的值

insert into表名（类型） values（对应的值）

insert into test (id,name,sroce,passwd) values(1,'刘备',97,'111');

方法二：直接按表格对象的顺序插入值

insert into test values(2,'关羽',99,'222');

对对象的密码进行加密：

password ('xxx')

对表中内容进行更新修改：

update 表名 set 对象=修改内容 where id

where id 是因为id是主键，唯一标识，不会重复查询没有歧义

如何在表中删除数据：

4、DQL：查询数据记录（重点）

指定查看行：

limit 3：指定查看前三行

limit 2,4：指定从第3行开始往下查看4行

去重查看：

select distinct 对象 from 表名;

指定对象去重

多条件去重查询：

select distinct name,sroce from test;

修改表名：alter

alter table test rename demo;

修改表的结构：

插入新的对象：

alter table 表名 add 对象信息；

修改列对象名：

alter table test change birth birthday datetime;

删除整列：

alter table test drop birthday；

修改列的数据类型：

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、主键约束，用于唯一标识表中的每一行，主键列的值必须是唯一而且不能为空，一个表只能有一个主键

2、外键约束：用于建立表与表之间的关系，一般是和另一张表的主键关联。保证数据引用的完整性。一个表可以有多个外键

3、非空约束：not null，如果定义是 not null，那么必须要有一个值，空格也算。

4、唯一性约束：unique key确保列中的所有值都是唯一的，类似于主键，但是可以为空，而且一个表可以有多个唯一约束。

5、默认值约束：default，在插入表数据时，如果没有定义值，会提供一个默认值。

6、自增约束：每行自动生成一个唯一标识

1、外键关联：

创建主表：

create table student (

card_id int(18) not null primary key,

stu_name varchar(12) not null,

stu_email varchar(255) unique

);

创建从表：

create table class (

stud_id int(11) auto_increment primary key,

address varchar(50) default '地址不详',

card_id int (18) not null,

foreign key (card_id) references student (card_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;

删除索引

4、删除主键过程：

删除主键要先改变自增类型

删除主键：因为是一个自增约束的主键，要先改变它的数据类型，解除自增约束，之后才能删除主键——删除主键要先改变自增类型

先改自增约束：

alter table class modify stud_id int(12);

再删除主键：

alter table class drop primary key;

在对主键约束中，约束条件有先后顺序：
id int(4) zerofill PRIMARY KEY auto_increment,

MySQL外键只能关联主键或唯一键。

5、练习：

定义一个需求：

两张表：

主表：company

从表：depart

company：

1. work_id 非空，主键，int(4)，不满四位要补齐
2. name 非空，char(5)
3. sex 非空，char(2)

depart

1. de_id 非空，主键，int(6),不满补齐6位
2. work_id 要和主表的work_id 关联为外键
3. address 可为空，但是有一个默认值
4. phone 非空，不能重复

需求：

主表：school

1. de_id int(4) 不满四位要补齐，自增长，主键
2. name varchar(15) not null
3. email varchar(45) 可空，默认值'bdqnkgc@126.com'

从表：class

1. id 自增长主键 int(6)
2. class_name 非空
3. de_id 主键关联
4. address 可空 默认 地址不详
5. phone int(11) 非空，不能重复

创建完之后删除外键关联，删除从表的主键，重新定义主键为phone

先创建主表school：

create table school (

de_id int(4) zerofill primary key auto_increment,

name varchar(15) not null,

email varchar(45) default 'bdqnkgc@126.com'

);

再创建从表class:

create table class (

id int(6) primary key auto_increment,

class_name varchar(25) not null,

de_id int(4) zerofill,

address varchar(200) default '地址不详',

phone int(11) unique,

foreign key(de_id) references school(de_id)

);

检测：先插入主表再插入从表

看de_id是否对应

删除外键关联，删除从表的主键，重新定义主键为phone：

desc class;

查看从表的主键名，索引名：

alter table class drop FOREIGN key class_ibfk_1;

删除关联主键

alter table class drop index de_id;

删除关联索引

alter table class MODIFY id int(5);

更改主键之前，要先先更改自增约束

改主键对象的数据类型

alter table class drop primary key;

删除主键

alter table class add primary key(phone);

将phone的类型改为主键

desc class;

六、数据库用户的管理：

创建用户

修改用户的权限

删除用户

grant赋权语句最好在终端执行

不能免密登录：vim /etc/my.cnf修改将免密登录删除    skip-grant-tables

1、创建用户：create user

create user 'koeda'@'localhost' identified by '123456';

koeda表示用户名

localhost表示新建的用户koeda可以在哪些主机上登录。既可以使用IP地址，也可以使用网段，也可以使用主机名

'koeda'@'20.0.0.20' 指定ip登录

'koeda'@'20.0.0.0/24' 指定网段登录

'koeda'@'%' 百分号表示所有，可以从任何设备登录

2、用户赋权：（要在终端里面写）

赋权koeda对所有库进行所有操作权限：

grant all privileges on *.* to 'koeda'@'localhost' identified by '123456';

grant 赋权的开头语句

all privileges 赋予所有权限

on *.* 对所有库都有操作权限

若 on test.* 表示只能对指定的库test进行操作

to 'koeda'@'localhost' 赋权给哪个用户，要和创建用户赋权的格式一致

identified by '123456' 使用哪个密码进行登录。创建用户的时候可以省略

赋权完刷新：

flush privileges;

查看用户权限：

show grants for 'koeda'@'localhost';

赋完权之后只能在终端登录

mysql -h 20.0.0.31 -u test1 -p

3、练习：

创建一个用户test1

只允许该用户从20.0.0.32终端登录

只能对kgc库有权限，其他的库一律不行

先创建一个用户，只允许该用户从20.0.0.32登录：

create user 'test1'@'20.0.0.32' IDENTIFIED by '123456';

修改权限，只能对kgc库权限，其他库一律不行

grant all PRIVILEGES on kgc.* to 'koeda'@'20.0.0.32' IDENTIFIED by '123456';

4、删除用户的权限

删除权限revoke

revoke all privileges on kgc.* from 'test1'@'20.0.0.31';

权限匹配只能和创建的时候一致，

创建的时候是什么，删的时候就是什么

创建的时候是kgc.*，删的时候不能是*.*，只能值kgc.*

如何对权限进行控制：

grant select on kgc.* to 'test1'@'20.0.0.31' identified by '123456';

只能查数据库

设置之后检测，看看能不能增删改

如何给一个用户赋予多个权限：

grant select,insert,drop on kgc.* to 'test1'@'20.0.0.31' identified by '123456';

revoke drop,update on kgc.* from 'test1'@'20.0.0.31';

移除所有权限的时候，可以全部移除，不需要一个个删除，不需要和创建时一致

revoke all on kgc.* from 'test1'@'20.0.0.31';

5、如何给用户重命名：

rename user 'test1'@'20.0.0.31' to 'test2'@'20.0.0.31';

6、删除用户：

drop user 'test2'@'20.0.0.31';

7、用户修改密码：

修改当前登录用户的密码：（要在终端执行）

set password=password('abc123')

修改其他用户的密码：

set password for 'test1'@'20.0.0.31' = passwoed('abc123');

8、问题：忘记了root密码。如何恢复？

vim /etc/my.cnf添加免密登录    skip-grant-tables

登录过程回车即可

select User,authentication_string,Host from user;查看密码

修改root用户的密码

update user set authentication_string=password('123456') where Host='localhost' and User='root';

flush privileges;

改密码要加密改

select User,authentication_string,Host from user;查看密码

用户权限管理：

create user '用户名'@'20.0.0.21' identified by '123456';

grant all / select,insert,drop 赋权

revoke all / select,insert,drop 删除权限要和赋权格式一致

rename

drop user

set passwd=passwd('123456')

set password for '用户名'@'localhost'=password('abc123');

show grants for '用户名'@'localhost';

查看权限

flush privileges;

刷新权限