MYSQL数据库的概念和sql语句

数据库概念和sql语句

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

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

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

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

Mysql

数据是一个系统，是一个人机系统，硬件。os数据库，DBMS和数据库的用户共同组成

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

数据库的分类：

Mysql：关系型数据库

       非关系型数据库 例：redis，es

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

表：行列

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

列：字段，用来描述对象的一个信息

关系型数据库：mysql（中小类型的数据并发请求 免费） oracle（大数据量还是用它）

sql-server  mariaDB  postgresql

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

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

3张表

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

redis 缓存性的数据库

es 索引型数据库

MongDB 文档型数据库

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

Mysql数据库的存储引擎：

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

5.6之后默认的存储引擎：Innodb，可以支持事务，外键，行级锁定的存储引擎

支持高并发性能的应用

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

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

事务的特点：原子性。它是数据库最小工作单元，要么全部执行成功，要么全部不执行，只要有一个操作时间，整个执行的序列都会被回滚，完成的操作也会被撤销

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

隔离性：事务的执行是相互隔离。一个事务的执行不能收到其他执行事务的干扰，并发事务之间互相隔离。防止数据不一致的情况发生。持久性：事务一旦提交，他所做的所有修改会被永久的保存在书库中。即使系统崩溃，提交的数据也不丢失。

确保数据库的始终处于一致性的状态，确保数据的完整和一致

Mysql的名词：

数据库：database

表：table

行：row

列：column

索引：index

视图：view

存储过程：procedure

触发器：trigger

用户：user

权限：privilege

Mysql的语句的规范：

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

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

关键词不能跨行或者简写

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

数据库的命名规则：

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

不要使用mysql的保留字：table  select

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

数据库的字符类型：

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

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

varchar：可变长度的字符串类型，存储可变长度的字符串。（无限制的可以随便写，也要根据类型的长度）

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

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

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

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

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

date：存储日期：yyyy-mm--dd

datetime：存储日志：YYYY-MM-DD HH:MM:SS

timestamp:和datetime格式一致。可以自动的更新为当前的时间戳

重点：

char varchar

‘ ‘ char4个字节  ‘ ‘ 一个字节

‘ab’ ‘ab’4个字节 ‘ ab‘ 3个字节

‘abcd’ ‘abcd’4个字节 ‘abcd’5个字节

char无论你是否定义了值，都会占用固定长度的字节大小

varchar：在保存时，varchar 14 实际长度是5 就占用5个，加一个隐藏符，6个

varchar比char节省磁盘空间，但是varchar读写速度比char慢，都是字符串类型：一定要用单引号

sql语句的分类

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

CREATE 创建

DROP  删除

 ALTER  修改

查看数据库当前使用的端口查看

show global variables like 'port';

查看当前数据库有多少库

show databases;

使用库

use 库名

例：use mysql; #使用mysql库

     show tables; #查看mysql当中有哪些表

查看表结构

describe 表名；

简写：desc 表名；

纵向查看

desc  表名\G;

创建库

create database 库名；

创建表

create table test (id int(4) not null,name varchar(10) not null,sroce decimal(5,2),passwd char(48),primary key(id));

删除表

drop tables test（创建的表名）；

Null和空格的区别：
null什么都没有，对象没有任何的描述信息

空格：也是字符

DML：数据操作语句，对表中的数据进行管理

insert 添加

两种方式：

insert into test (id,name,sroce,passwd) values(1,'张三',90,123)；

insert into test values(2,'李四',95,456);

密码加密添加

insert into test values(5,'张三2',100,password('789'));

select：查看表格式

select * from test;

update 更新

密码加密

update test set passwd=password('000') where id = 4;

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

delete from test where id = 6;

DQL：查询数据记录

select

查看表的第2行

select * from test where id =2;

显示前4行

select * from test limit 4;

从第2行开始向下走3行

select * from test limit 2,3;

去重查询，压缩重复的行为1行

sroce去重

select distinct sroce from test;

id去重

select distinct id,name,sroce from test;

name去重

select distinct name from test;

修改表名

alter table 原表名 rename 新表名

扩展表结构

alter table test add birth date;

更新表信息

update tesst set birth=date('2002-7-13') where id =4;

修改字段(列)名

alter table test change birth birthday datetime;

删除字段

alter table test drop birthday

修改字段类型

alter table test modify column passwd varchar(52);

DCL：数据控制语句，设置或者更改数据库用户或者用户的权限

Grant：赋予权限

Revoke：取消权限

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(10) unique key,

hobby varchar(50)

);

if not exists ky32：ky32这个表不存在，才会创建

zerofill：自动填充位置

primary key：当前表的主键，主键只能有一个，而且唯一，而且不能为空

auto_increment：表示该字段可以自增长，默认从1开始，每条记录会自动递增1

unique：唯一性约束，跟主键不同，可以为空的

create table test like ky32；

通过like这个语法直接复制ky32表的结构（但是没有数据）

insert into test select * from ky32;

把ky32 表里的数据，复制到test，两个表数据结构要一致。

create table test1 （select * from ky32）;

创建一张表，test1，数据从ky32来，表结构也是ky32；直接全部复制

删除表内所有数据：

delete  truncate  drop

delete from ky32;

delete删除是一行一行删除，如果表中有自增长列，清空所有记录之后，再次添加内容，会从原来的记录之后继续自增写入。

truncate table ky32;

清空表中的数据，而且会把表结构重新建立。速度上比delete快，推荐

drop table test1;

直接删除表，不推荐。

创建临时表：临时表一般用于调试，而且临时表创建之后在表的目录当中是不显示的，连接退出之后，临时表会被销毁。而且临时表无法创建外键。

MySQL的约束方式;

6种常见的约束：

1，主键约束，用于唯一表示表中的每一行，主键列的值必须唯一而且不能为空，一个表只能有一个主键

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

3，非空约束，not null 必须要有一个值。

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

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

6，自增约束：auto_increment每行自动生成一个唯一标识。通常和主键在一起使用。

foreign key (card_id) references student (card_id) 外键连接，要保存一致

show create table class; 在命令行显示外键连接

alter table class drop foreign key class_ibfk_1;

删除外键连接。

desc class 查看class表的结构

mul：表示这是一个索引。

alter table class drop index card_id;

删除class表中card_id的索引

alter table class drop primary key;

删除主键

CREATE TABLE company (

work_id int(4) PRIMARY KEY ZEROFILL,

name char(5) not null,

sex char(2) not null

);

desc company;

CREATE TABLE depart (

de_id int(6) ZEROFILL PRIMARY KEY,

work_id int(4) not null ZEROFILL,

FOREIGN KEY (work_id) REFERENCES company (work_id),

addrss VARCHAR(255) DEFAULT '地址不详',

select * from test1;

主表：

create table student (

card_id int(18) PRIMARY key,

stu_name varchar(12) not NULL,

stu_email varchar(255) unique

);

desc student;

创建从表：

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;

主表和从表：

插入数据：先插入主表，再插入从表

删除数据：先删除主表，在删除从表

alter table class drop FOREIGN key class_ibfk_1;

desc class;

mul：表示这是一个索引。

alter table class drop index card_id;

alter table class drop primary key;

[Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

这是一个自增约束的主键，要先改变他的数据类，解除自增约束，之后主键才可以删除

用修改的方式添加主键

alter table depart add foreign key (work_id) references company (work_id);

alter table class modify stud_id int(12);

alter table class drop primary key;

定义一个需求：

两张表：

主表 company

从表 depart

company:

1、work_id 非空，主键 int(4) 不满四位要补齐

2、name 非空 char(5)

3、sex 非空 char(2)

depart

1、de_id 非空 主键 int(6).不满六位要补齐

2、work_id 要和主表的work_id关联为外键

4、address  为空，但是有一个默认值

5、phone 不能为空而且不能想同。

create table company (

work_id int(4) zerofill primary key,

name varchar(5) not null,

sex char(2) not null

);

create table depart (

de_id int(6) zerofill primary key,

work_id int(4)zerofill,

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

phone int(11) not null unique

);

alter table depart add FOREIGN key (work_id) REFERENCES company (work_id);

alter table depart drop PRIMARY key;

ALTER table depart add PRIMARY key (de_id);

dcl：权限控制语句 grant revoke

数据库用户管理

创建用户

修改用户的权限

删除用户

grant 要在终端执行

创建用户

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

create user 这是创建用户的开头

'ky32'@'localhost'

ky32 表示的是用户名

localhost：新建的用户可以在哪些主机上登录。即可以使用IP地址，网段，主机名都可以

'ky32'@'192.168.120.80'：ky32在这个ip上可以登录

'ky32'@'192.168.120.0/24'：ky32在这个网段可以登录

'ky32'@'%'：ky32在所有主机都可以登录

identified by '123456'：新建用户的密码

加密密码

给用户赋权

给指定用户赋予查询权限

grant select on *.* to 'ky32'@'localhost' identified by '123456';

给一个用户赋予多个权限（用逗号隔开）：

grant create,update,select,insert,drop on kgc.* to 'test1'@'192.168.120.80' IDENTIFIED by '123456';

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

grant 赋权的开头语句

all privileges 赋予所有权限

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

on kgc.* 只能对指定的库（kgc）进行操作

to 'ky32'@'localhost' 赋权给哪个用户

identified by 使用哪个密码进行登录。创建用户的时候可以省略，密码为空（不推荐）

查看指定用户权限

show grants for 'ky32'@'localhost';

刷新用户权限

flush privileges；

删除用户的权限：revoke

删除指定权限，删除select查询权限

revoke select on kgc.* from 'test1'@'192.168.120.80';

删除多个权限（用逗号隔开）

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

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

查看赋予权限

show grants for 'test1'@'192.168.120.80';

用户重命名（前后必须一致）：

rename user 'test1'@'192.168.120.80' to 'dym'@'192.168.120.80';

删除用户：

drop user 'ky32'@'localhost';

修改用户密码：

修改当前登录用户的密码

set password = password('abc123');

给其他用户修改密码

set password for 'dym'@'192.168.120.80' = password('abc123');

忘记了root密码如何恢复：

vim /etc/my.cnf

添加免密登录命令

skip-grant-tables

刷新mysql服务

systemctl restart mysql

重新进入mysql不用输入密码直接进入

mysql -u root -p

use mysql；

show user；

select user,authentication_string,host from user;

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