数据库基础知识

最新推荐文章于 2024-07-25 17:05:51 发布
最新推荐文章于 2024-07-25 17:05:51 发布
阅读量913 收藏 17
点赞数 18
文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_61367575/article/details/138345030
版权

数据库

一、概论

1、什么是数据库

数据库：DB（DataBase）

概念：数据仓库，软件，安装在操作系统之上

作用：存储数据，管理数据

2、数据库分类

关系型数据库：SQL（Structured Query Language）

MySQL、Oracle、Sql Server、DB2、SQLlite

通过表和表之间，行和列之间的关系进行数据的存储

通过外键关联来建立表与表之间的关系

非关系型数据库：NoSQL（Not Only SQL）

Redis、MongoDB

指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

3、相关概念

DBMS（数据库管理系统）

数据库的管理软件，科学有效的管理、维护和获取我们的数据

MySQL就是数据库管理系统

在这里插入图片描述

4、数据库存储引擎

INNODB

默认使用，安全性高，支持事务的处理，多表多用户操作

MYISAM

早些年使用，节约空间，速度较快

区别MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为2被

数据库文件存在的物理空间位置：

  • MySQL数据表以文件方式存放在磁盘中
    • 包括表文件 , 数据文件 , 以及数据库的选项文件
    • 位置 : Mysql安装目录\data\（目录名对应数据库名 , 该目录下文件名对应数据表）

MySQL在文件引擎上区别：

INNODB数据库文件类型就包括**.frm**、.ibd以及在上一级目录的ibdata1文件

MYISAM存储引擎，数据库文件类型就包括

.frm：表结构定义文件

.MYD：数据文件

.MYI：索引文件

二、安装

三、基本知识

所有的数据库语句都要以分号（；）结尾

show databases;	--查看当前所有的数据库
use 数据库名;	--打开指定的数据库
show tables;	--查看所有的表
describe/desc 表名;	--显示表的信息
create database 数据库名;	--创建一个数据库
exit	--退出连接


--		--单行注释
#		--单行注释
/*...*/		--多行注释

1、数据库的列类型

整型

数据类型字节数无符号取值范围有符号取值范围
TINYINT10~255-128~127
SMALLINT20~65535-32768~32767
MEDIUMINT30~16777215-8388608~9388607
INT40~4294967295-2147483648~2147483647
INTEGER40~4294967295-2147483648~2147483647
BIGINT80~18446744073709551615-9223372036854775808~9223372036854775807
mysql> use school;   #选择数据库school
 
mysql> create table class5(class_id int , class_name varchar(128), class_teacher varchar(64) );         #创建表class5
整数类型的附带属性
类型名称后面的小括号

指定显示宽度（并不是该类型占用字节数）。如果不显示指定宽度则默认为tinyint(3)、 smallint(5)、 mediumint(8)、 int(11) 和 bigint(20)。一般配合 zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。

mysql> use school;   #选择数据库school
 
mysql> create table class6(class_id integer(5) zerofill, class_name varchar(128), class_teacher varchar(64) );         #创建表class6

(2).unsigned

如果需要在字段里面保存非负数或者需要较大的上限值时，可以用此选项，它的取值范围是正常值的下限取 0，上限取原值的 2 倍，例如，tinyint 有符号范围是-128～+127，而无符号范围是 0～255。如果一个列指定为 zerofill，则 MySQL 自动为该列添加 UNSIGNED 属性。

mysql> use school;   #选择数据库school
 
mysql> create table class6(id integer unsigned , name varchar(128), teacher varchar(64) );         #创建表class6
 
mysql> create table class7(id integer zerofill , name varchar(128), teacher varchar(64) );         #创建表class7, id类型为 int unsigned

(3).auto_increment

在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。 一个表中最多只能有一个 AUTO_INCREMENT列 。对于任何想要使用 AUTO_INCREMENT 的列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键。 例如，可按下列任何一种方式定义 AUTO_INCREMENT 列：

mysql> use school;   #选择数据库school
 
mysql> create table class8(id integer auto_increment PRIMARY KEY , name varchar(128), teacher varchar(64) );         #创建表class8， id 具有自增长属性
 
mysql> create table class9(id integer auto_increment UNIQUE , name varchar(128), teacher varchar(64) );         #创建表class9， id 具有自增长属性

浮点型

数据表中用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数（FLOAT型）和双精度浮点数（DOUBLE型）。定点数类型就是DECIMAL型。下面从这三种类型的字节数、取值范围等方面进行对比，如下表所示。

  • float

    • 有符号Float ：单精度浮点型，8位精度(4字节) ，float(m,d)：m表示的是最大长度，d表示的显示的小数位数。例如上面的sql里：float(4,2) 表示：这个浮点数最大长度为5，也就是五位，然后小数部分为2位，至于存储范围，取决于你是否定义了无符号。

    • 无符号的话，最小是0.0 最大能存储到9999.9，如果有符号的话，范围是：-99.99至99.99。

    • 默认大小为24位数字，精度大约7位数字（经测试为6位），当设置M大小大于24时，自动转换为DOUBLE类型;同时设置M和D时不进行自动转换。

    • float的取值范围在-3.4E+38 和 3.4E+38

  • Double ：double(m,d) 双精度浮点型 16位精度(8字节) m总个数，最大为，d小数位

    • 设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.

定点数：

  • Decimal ：浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
  • decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；


mysql> use school;   #选择数据库school
 
mysql> create table class10 (f1 float, do1 double, de1 decimal);  #创建表class10

MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。对于decimal,M是最大位数（精度），范围是1到65。可不指定，默认值是10。D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。
FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0,即(10, 0)。
忠告: 当我们需要存储小数，并且有精度要求，比如存储金额时，通常会考虑使用DECIMAL字段类型!!!

字符型

CHAR 类型和 VARCHAR 类型

CHAR类型和VARCHAR类型都在创建表时指定了最大长度

 其中，“字符串类型”参数指定了数据类型为CHAR类型还是VARCHAR类型；M参数指定了该字符串的最大长度为M。例如，CHAR(4)就是数据类型为CHAR类型，其最大长度为4。
  CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。例如，CHAR(100)就是指定CHAR类型的长度为100。CHAR 存储值时，它们会用空格右填充到指定的长度。
 VARCHAR类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100，但是不是每条记录都要占用100个字节，而是在这个最大值范围内使用多少就分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1或2，这样即可有效节约系统的空间。
值CHAR(4)存储字节数VARCHAR(4)存储字节数
‘’‘’4‘’1
‘ab’‘ab’4‘ab’3
‘abcd’‘abcd’4‘abcd’5
‘abcdefgh’-#插入失败-#插入失败
实战类型

mysql> use test;   #选择数据库test
 
mysql> create table char_example(e_char char(5), v_char varchar(5)); #创建数据库表
 
mysql> insert into char_example values('12345','12345');  #正常插入数据
 
mysql> insert into char_example values('1 2  ','1 2  ');   #char类型会屏蔽后面隐藏的空格，varchar 不会
 
mysql>select concat('(',e_char, ')'), concat('(',v_char, ')')  from char_example ; #让char 后面屏蔽的空格原型毕露

#1、 char一定会使用指定的空间，varchar是根据数据来定空间
#2、 char的数据查询效率比varchar高：varchar是需要通过后面的记录数来计算
#3、  如果确定数据一定是占指定长度，那么使用char类型；
#4、  如果不确定数据到底有多少，那么使用varchar类型；
#5、  如果数据长度超过255个字符而在65535之内，直接使用varchar
#6、  如果字符串尾部要保留空格，必须选择varchar
TEXT类型

TEXT类型是一种特殊的字符串类型，包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，其长度和存储空间的对比下表所示:

类型允许的长度
TINYTEXT0~255字节
TEXT0~65535字节
MEDIUMTEXT0~16772150字节
LONGTEXT0~4294967295字节

各种TEXT类型的区别在于允许的长度和存储空间不同。因此，在这几种TEXT类型中，根据需求选取既能满足需要又节省空间的类型即可。

注意：

1）以上各类型无须指定长度！
2）允许的长度是指实际存储的字节数，而不是实际的字符个数，比如假设一个中文字符占两个字节， 那么TEXT 类型可存储 65535/2 = 32767 个中文字符，而varchar(100)可存储100个中文字 符，实际占200个字节，但varchar(65535) 并不能存储65535个中文字符，因为已超出表达范围。

mysql> use test;   #选择数据库test
 
mysql> create table text_example(e_text tinytext, v_char varchar(255)); #创建数据库表，e_text 可存储255个字节，v_char可存储255个字符  
 
 
mysql> insert into char_example values(90个中文字符,90个中文字符);  #插入失败，utfmb4 用3个字节表示一个中文汉字,会超出tinytext 保存范围
 
 
mysql> insert into char_example values(80个中文字符,100个中文字符);  #插入成功

建议：
1、 char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。超过255字节的只能用varchar或者text；
2、 varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
3、 text不设置长度， 当不知道属性的最大长度时，适合用text， 能用varchar的地方不用text；
4、 如果都可以选择，按照查询速度： char最快， varchar次之，text最慢。

实战练习

创建一个用户信息表，用来存储用户的如下信息：姓名，手机号码，家庭地址，个人简介，性别，年龄，身份证号。数据库表名可定义为userinfo, 同时增加一个列id 用来作为每一条记录的唯一标识，并设为主键，自加！

mysql> use test;   #选择数据库test
 
mysql> create table userinfo (
 id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,
 name   varchar(64) DEFAULT NULL COMMENT ‘姓名’,
 mobile  char(11)  DEFAULT NULL COMMENT ‘手机号码’,
 address varchar(128) DEFAULT NULL COMMENT ‘居住地址’,
 description text  DEFAULT NULL COMMENT ‘个人简介-不知道具体的范围，不常更新用text’,
 sex     char(1)  DEFAULT NULL COMMENT ‘性别 - 男或女’,
 age     tinyint unsigned DEFAULT 0 COMMENT ‘年龄’,
 idno    char(18) DEFAULT NULL COMENT ‘身份证号码’
);
ENUM类型

ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围以列表的形式指定，其基本形式如下：

属性名 ENUM(‘值1’, ‘值2’, …, ‘值n’)

    其中，“属性名”参数指字段的名称，“值n”参数表示列表中的第n个值。ENUM类型的值只能取列表中的一个元素。其取值列表中最多能有65535个值。如果数据值列表在255个以内，那么一个字节就够，如果超过255但是小于65535，那么系统采用两个字节保存。列表中的每个值独有一个顺序排列的编号，MySQL中存入的是这个编号，而不是列表中的值。默认编号从1开始！

mysql> use test;   #选择数据库test
 
mysql> create table enum_example (e_enum enum('男','女','选择保密') ); #创建表
 
mysql> insert into enum_example values('男');  #插入记录,必须是enum 选项中的值
 
mysql> insert into enum_example values(1);  #插入记录可以用数值表示
 
mysql>select e_enum + 0  from enum_example; #查询enum 选项对应的整数值

如果ENUM类型加上了NOT NULL属性，其默认值为取值列表的第一个元素。如果不加NOT NULL属性，ENUM类型将允许插入NULL，而且NULL为默认值。
Enum(‘男’,’女’,‘选择保密’) # ‘男’=>1 ‘女’=>2 ‘选择保密’=> 3

SET类型

在创建表时，SET类型的取值范围就以列表的形式指定了，其基本形式如下：

属性名 SET(‘值1’, ‘值2’, …, ‘值n’)

其中，属性名参数指字段的名称，“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是由64个元素构成的组合。

mysql> use test;   #选择数据库test
 
mysql> create table set_example (interest set('足球','追剧','篮球','撩妹') ); #创建表
 
mysql> insert into set_example values( '足球,撩妹' ); #插入记录,必须是enum 选项中的值
 
mysql> insert into enum_example values(9);  #插入相应位效果等同，9 =>1001 选择1,4
 
mysql>select interest+0 from set_example;  #以整数的方式查询

日期和事件类型

日期与时间类型是为了方便在数据库中存储日期和时间而设计的，数据库有多种表示日期和时间的数据类型。其中，YEAR类型表示年，DATE类型表示日期，TIME类型表示时间，DATETIME和TIMESTAMP表示日期和时间。

类型字节数取值范围零值
YEAR11901~21550000
DATE41000-01~9999-12-310000:00:00
TIME3-838:59:59838:59:59
DATETIME81000-01 00:00:00~9999-12-21 23:59:590000-00-00 00:00:00
TIMESTAMP4:19700101080001~203801191140700000000000000

每种日期与时间类型都有一个有效范围。如果插入的值超过了这个范围，系统就会报错，并将零值插入到数据库中。不同的日期与时间类型均有不同的零值.

插入日期时间时，日期年月日和时间时分秒可以使用 “: - _ / ”中的任意字符来分隔,如果单独插入时间，则会报错！！！

mysql> use test;   #选择数据库test

mysql> create table date_example (e_date date, e_datetime datetime, e_timestamp
timestamp, e_time time, e_year year);  #创建表dt_example

mysql> insert into date_example values('2020-5-9', '2020-5-9 15:01:00', '2020-05-09 15:01:00', '15:56:01', 2011);

mysql> insert into date_example values('2020_5/9', '2020:5-9 15/01-00', '2020:05/09 15-01_00',  '15:56:01', '2011');   #效果同上面插入语句

注意：

允许的长度是指实际存储的字节数，而不是实际的字符个数，比如假设一个中文字符占两个字节， 那么TEXT 类型可存储 65535/2 = 32767 个中文字符，而varchar(100)可存储100个中文字 符，实际占200个字节，但varchar(65535) 并不能存储65535个中文字符，因为已超出表达范围。

mysql> use test;   #选择数据库test
 
mysql> insert into date_example values(CURDATE(), NOW(), NOW(), time(NOW()), YEAR(NOW()) );

2、数据库字段属性

UnSigned

无符号的
声明了该列不能为负数

ZEROFILL

0填充的
不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

通常理解为自增，自动在上一条记录的基础上默认+1
通常用来设计唯一的主键，必须是整数类型
可定义起始值和步长
当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表

SET @@auto_increment_increment=5 ;

影响所有使用自增的表(全局)

NULL 和 NOT NULL

默认为NULL , 即没有插入该列的数值
如果设置为NOT NULL , 则该列必须有值

DEFAULT

默认的
用于设置默认值
例如,性别字段,默认为"男" , 否则为 “女” ; 若无指定该列的值 , 则默认值为"男"的值

拓展：每一个表，都必须存在以下五个字段：

名称描述
id主键
version乐观锁
is_delete伪删除
gmt_create创建时间
gmt_update修改时间

四、操作命令

1、数据库

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名;

删除数据库

DROP DATABASE [if EXISTS] 数据库名;

使用数据库

--如果表名或者字段名是特殊字符，则需要带``
use 数据库名;

查看数据库

SHOW DATABASES;

2、数据表

创建数据库表

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

注意点：

表名和字段尽量使用``括起来

AUTO_INCREMENT 代表自增

所有的语句后面加逗号，最后一个不加

字符串使用单引号括起来

主键的声明一般放在最后，便于查看

不设置字符集编码的话，会使用MySQL默认的字符集编码Latin1，不支持中文，可以在my.ini里修改

格式：

CREATE TABLE IF NOT EXISTS `student`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]

常用命令：

SHOW CREATE DATABASE 数据库名;-- 查看创建数据库的语句
SHOW CREATE TABLE 表名;-- 查看表的定义语句
DESC 表名;-- 显示表的具体结构

修改表

修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

修改字段 :
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除字段 : ALTER TABLE 表名 DROP 字段名

-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;
 
-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);
 
-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段重命名
 
-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;

删除表

语法：DROP TABLE [IF EXISTS] 表名

IF EXISTS为可选 , 判断是否存在该数据表

如删除不存在的数据表会抛出错误

– 删除表(如果存在再删除)

DROP TABLE IF EXISTS teachers;

所有的创建和删除尽量加上判断，以免报错

五、MySQL数据管理

1、外键

外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。

外键作用：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

目标：学生表（student）的gradeid字段 要去引用年级表（grade）的 gradeid字段

创建外键

方式一：在创建表的时候增加约束

/*
	1. 定义外键key
	2. 给外键添加约束（执行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2)	NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
 
-- 创建年级表
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

方法二：创建表成功后，添加外键约束

/*
	1. 定义外键key
	2. 给外键添加约束（执行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2)	NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
 
-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
 
-- 创建年级表
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

以上的操作都是物理外键，数据库级别的外键，不建议使用！避免数据库过多造成困扰！

最佳实践

数据库就是用来单纯的表，只用来存数据，只有行（数据）和列（属性）

我们想使用多张表的数据，使用外键，用程序去实现

2、DML语言

数据库的意义：数据存储，数据管理

Data Manipulation Luaguge：数据操作语言

添加 insert

-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');
 
-- 插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');
 
-- 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1);
语法：
INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];
注意：

字段和字段之间使用英文逗号隔开
字段是可以省略的，但是值必须完整且一一对应
可以同时插入多条数据，VALUES后面的值需要使用逗号隔开

修改 update

-- 修改学员名字,指定条件
UPDATE `student` SET `name`='zsr204' WHERE id=1;
 
-- 不指定条件的情况,会改动所有表
UPDATE `student` SET `name`='zsr204';
 
-- 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;
 
-- 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';
语法：
UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

关于WHERE条件语句：

操作符含义
=等于
<>或!=不等于
>大于
<小于
<=小于等于
>=大于等于
BETWEEN…AND…闭合区间
AND和
OR或

删除 delete

-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`;
 
-- 删除指定数据
DELETE FROM `student` WHERE id=1;

语法：

DELETE FROM 表名 [WHERE 条件]
关于DELETE删除的问题，重启数据库现象：
 
INNODB 自增列会从1开始（存在内存当中，断电即失）
MYISAM 继续从上一个子增量开始（存在内存当中，不会丢失）
TRUNCATE

作用：完全清空一个数据库表，表的结构和索引约束不会变！

DELETE和TRUNCATE 的区别：

DELETE可以条件删除（where子句），而TRUNCATE只能删除整个表

TRUNCATE 重新设置自增列，计数器会归零，而DELETE不会影响自增

DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

在这里插入图片描述


CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
 
INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');
 
-- 不会影响自增
DELETE FROM `test`;
 
-- 会影响自增
TRUNCATE TABLE `test`;

3、DQL查询数据Data

| QueryLanguage 数据查询语言

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
   [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
  • 查询数据库数据 , 如SELECT语句
  • 简单的单表查询或多表的复杂查询和嵌套查询
  • 是数据库语言中最核心,最重要的语句
  • 使用频率最高的语句

前提配置

-- 创建学校数据库
CREATE DATABASE IF NOT EXISTS `school`;
 
-- 用school数据库
USE `school`;
 
-- 创建年级表grade表
CREATE TABLE `grade`(
	`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
	`GradeName` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY	(`GradeID`)
)ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
 
-- 给grade表插入数据
INSERT INTO `grade`(`GradeID`,`GradeName`) 
VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');
 
-- 创建成绩result表
CREATE TABLE `result`(
	`StudentNo` INT(4) NOT NULL COMMENT '学号',
	`SubjectNo` INT(4) NOT NULL COMMENT '考试编号',
	`ExamDate` DATETIME NOT NULL COMMENT '考试日期',
	`StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
	KEY `SubjectNo` (`SubjectNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
 
-- 给result表插入数据
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) 
VALUES (1000,1,'2019-10-21 16:00:00',97),(1001,1,'2019-10-21 16:00:00',96),
(1000,2,'2019-10-21 16:00:00',87),(1001,3,'2019-10-21 16:00:00',98);
 
-- 创建学生表student
CREATE TABLE `student`(	
	`StudentNo` INT(4) NOT NULL COMMENT '学号',
	`LoginPwd` VARCHAR(20) DEFAULT NULL,
	`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
	`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,取值0或1',
	`GradeID` INT(11) DEFAULT NULL COMMENT '年级编号',
	`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空,即可选输入',
	`Adress` VARCHAR(255) NOT NULL COMMENT '地址,允许为空,即可选输入',
	`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
	`Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,允许为空,即可选输入',
	`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
	PRIMARY KEY (`StudentNo`),
	UNIQUE KEY `IdentityCard` (`IdentityCard`),
	KEY `Email` (`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
 
-- 给学生表插入数据
INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeID`,`Phone`,`Adress`,`BornDate`,`Email`,`IdentityCard`) 
VALUES (1000,'1241','dsaf',1,2,'24357','unknow','2000-09-16 00:00:00','1231@qq.com','809809'),
(1001,'1321','dfdj',0,2,'89900','unknow','2000-10-16 00:00:00','5971@qq.com','908697');
 
-- 创建科目表
CREATE TABLE `subject`(
	`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
	`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
	`ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
	`GradeID` INT(4) DEFAULT NULL COMMENT '年级编号',
	PRIMARY KEY (`SubjectNo`)
)ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
 
-- 给科目表subject插入数据
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) 
VALUES(1,'高数','96',2),(2,'大物','112',2),(3,'程序设计',64,3);
 
SELECT 字段 FROM 表;

基础查询

语法：

SELECT 查询列表 FROM 表名;

  • 查询列表可以是：表中的（一个或多个）字段，常量，变量，表达式，函数
  • 查询结果是一个虚拟的表格
-- 查询全部学生
SELECT * FROM student;
 
-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;
 
-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;
 
-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;
 
-- 查询系统版本(函数)
SELECT VERSION();
 
-- 用来计算(计算表达式)
SELECT 100*53-90 AS 计算结果;
 
-- 查询自增步长(变量)
SELECT @@auto_increment_increment;
 
-- 查询有哪写同学参加了考试,重复数据要去重
SELECT DISTINCT `StudentNo` FROM result;

条件查询

where 条件字句：检索数据中 符合条件的值

语法：
select 查询列表 from 表名 where 筛选条件;
-- 查询考试成绩在95~100之间的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`BETWEEN 95 AND 100;
 
-- 查询除了1000号以外的学生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;
 
-- 查询名字含d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d%';
 
-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';
 
-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);

分组查询

语法：
select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

区别：

-使用关键字筛选的表位置
分组前筛选where原始表group by的前面
分组后筛选having分组后的表group by的后面
-- 查询不同科目的平均分、最高分、最低分且平均分大于90
-- 核心：根据不同的课程进行分组
SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;

连接查询

在这里插入图片描述

-- 查询学员所属的年级（学号，学生姓名，年级名称）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID;
 
-- 查询科目所属的年级
SELECT `SubjectName`,`GradeName`
FROM `subject` s
INNER JOIN `grade` g
ON s.GradeID=g.GradeID;
 
-- 查询列参加程序设计考试的同学信息（学号，姓名，科目名，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN result r
on s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
on r.SubjectNo=sub.SubjectNo
where SubjectName='课程设计';

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

-- 创建一个表
CREATE TABLE `course` (
`courseid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程id',
`pid` INT(10) NOT NULL COMMENT '父课程id',
`courseName` VARCHAR(50) NOT NULL COMMENT '课程名',
PRIMARY KEY (`courseid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
 
-- 插入数据
INSERT INTO `course` (`courseid`, `pid`, `courseName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

将该表进行拆分：

pid(父课程)courseid（课程id）courseName（课程名）
12信息技术
13软件开发
15美术设计
pid(父课程)courseid（课程id）courseName（课程名）
12信息技术
13软件开发
15美术设计

操作：查询父类和对应的子类关系

父子
信息技术2办公信息4
软件开发数据库、web开发6
美术设计ps技术7
SELECT a.`courseid` AS '父课程',b.`courseid` AS '子课程'
FROM course AS a,course AS b
WHERE a.`courseid`=b.`pid`;

排序和分页

排序

语法：
select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc

order by的位置一般放在查询语句的最后（除limit语句之外）

asc升序，默认
desc降序
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID
ORDER BY `StudentNo` DESC;

分页

语法：
select 查询列表
from 表
limit offset,pagesize;
  • offset代表的是起始的条目索引，默认从0开始
  • size代表的是显示的条目数
  • offset=(n-1)*pagesize
-- 第一页 limit 0 5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页  limit (n-1)*pagesize,pagesize
-- pagesize:当前页面大小
-- (n-1)*pagesize:起始值
-- n:当前页面
-- 数据总数/页面大小=总页面数
-- limit n 表示从0到n的页面

子查询

本质：在 where子句中嵌套一个子查询语句

-- 查询‘课程设计’的所有考试结果（学号，科目编号，成绩）降序排列
 
-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.StudentNo=s.SubjectNo
WHERE SubjectName='课程设计'
ORDER BY StudentResult DESC;
-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
	SELECT SubjectNo FROM `subject`
	WHERE SubjectName='课程设计'
)

4、MYSQKL函数

常用函数

-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1
 
-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('rsz'); -- 反转字符串
 
-- 时间日期函数
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT now(); -- 获取当前时间
SELECT LOCALTIME(); -- 本地时间
SELECT SYSDATE(); -- 系统时间
 
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
 
-- 系统信息
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();

聚合函数

函数描述
max最大值
min最小值
sum和
avg平均值
count个数
SELECT COUNT(StudentName) FROM student; 
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
 
SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;

六、数据库级别的MD5加密

MD5信息摘要算法（MD5 Message-Digest Algorithm）

  • MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性
  • MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密前的值

CREATE TABLE `testMD5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET =utf8;
 
-- 明文密码
INSERT INTO `testMD5` VALUES(1,'zsr','200024'),
(2,'gcc','000421'),(3,'bareth','123456');
 
-- 加密
UPDATE `testMD5` SET `pwd`=MD5(pwd) WHE RE id=1;
UPDATE `testMD5` SET `pwd`=MD5(pwd); -- 加密全部的密码
 
-- 插入的时候加密
INSERT INTO `testMD5` VALUES(4,'barry',MD5('654321'));
 
-- 如何校验:将用户传递进来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM `testMD5` WHERE `name`='barry' AND `pwd`=MD5('654321');

七、事务

要么都成功，要么都失败

SQL执行：A转账给B
SQL执行：B收到A的钱
将一组SQL放在一个批次中去执行

例如银行转账：只有A转账成功且B成功到账，该事件才算结束，如果一方不成功，则该事务不成功

1、原则

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）

事务前后数据的完整性必须保持一致。

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

2、事务并发导致的问题

名称描述
脏读一个事务读取了另外一个事务未提交的数据
不可重复读在一个事务内的某一行数据，多次读取结果不同
虚读（幻读）是指在一个事务内读取到了别的事物插入的数据，导致前后读取不一致

3、隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的 事务隔离级别
读未提交：一个事务读取到其他事务未提交的数据；这种隔离级别下，查询不会加锁，一致性最差，会产生脏读、不可重复读、幻读的问题

读已提交：一个事务只能读取到其他事务已经提交的数据；该隔离级别避免了脏读问题的产生，但是不可重复读和幻读的问题仍然存在；

读提交事务隔离级别是大多数流行数据库的默认事务隔离级别，比如 Oracle，但是不是 MySQL 的默认隔离界别

可重复读：事务在执行过程中可以读取到其他事务已提交的新插入的数据，但是不能读取其他事务对数据的修改，也就是说多次读取同一记录的结果相同；该个里级别避免了脏读、不可重复度的问题，但是仍然无法避免幻读的问题

可重复读是MySQL默认的隔离级别

串行化：事务串行化执行，事务只能一个接着一个地执行,、，并且在执行过程中完全看不到其他事务对数据所做的更新；缺点是并发能力差，最严格的事务隔离，完全符合ACID原则，但是对性能影响比较大

事务隔离级别脏读不可重复读幻读
读未提交是是是
读已提交否是是
可重复读否否是
串行化否否否

执行事务的过程

1️⃣ 关闭自动提交

SET autocommit=0;
2️⃣ 事务开启

START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
3️⃣ 成功则提交，失败则回滚

-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
4️⃣ 事务结束

SET autocommit=1; -- 开启自动提交
​5️⃣ 其他操作

SAVEPOINT 保存点名; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名; -- 撤销保存点

八、索引

索引是帮助MYSQL高效获取的数据结构

优点：

  • 提高查询速度
  • 确保数据的唯一性
  • 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
  • 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
  • 全文检索字段进行搜索优化

1、索引分类

-- 创建学生表student
CREATE TABLE `student`(	
	`StudentNo` INT(4) NOT NULL COMMENT '学号',
	`LoginPwd` VARCHAR(20) DEFAULT NULL,
	`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
	`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,取值0或1',
	`GradeID` INT(11) DEFAULT NULL COMMENT '年级编号',
	`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空,即可选输入',
	`Adress` VARCHAR(255) NOT NULL COMMENT '地址,允许为空,即可选输入',
	`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
	`Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,允许为空,即可选输入',
	`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
	PRIMARY KEY (`StudentNo`),
	UNIQUE KEY `IdentityCard` (`IdentityCard`),
	KEY `Email` (`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

主键索引（PRIMARY KEY）

唯一的标识，主键不可重复，只有一个列作为主键

最常见的索引类型，不允许为空值
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

-- 创建表的时候指定主键索引
CREATE TABLE tableName(
  ......
  PRIMARY INDEX (columeName)
)
 
-- 修改表结构添加主键索引
ALTER TABLE tableName ADD PRIMARY INDEX (columnName)

普通索引（KEY / INDEX）

默认的，快速定位特定数据

index 和 key 关键字都可以设置常规索引
应加在查询找条件的字段
不宜添加太多常规索引,影响数据的插入,删除和修改操作

-- 直接创建普通索引
CREATE INDEX indexName ON tableName (columnName)
 
-- 创建表的时候指定普通索引
CREATE TABLE tableName(
  ......
  INDEX [indexName] (columeName)
)
 
-- 修改表结构添加普通索引
ALTER TABLE tableName ADD INDEX indexName(columnName)

唯一索引（UNIQUE KEY）

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

与主键索引的区别：主键索引只能有一个、唯一索引可以有多个

-- 直接创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName)
 
-- 创建表的时候指定唯一索引
CREATE TABLE tableName(  
	......
	UNIQUE INDEX [indexName] (columeName)  
);  
 
-- 修改表结构添加唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)

全文索引（FULLText）

快速定位特定数据（百度搜索就是全文索引）

在特定的数据库引擎下才有：MyISAM
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集

-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
 
-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引

2、索引的使用

索引的创建

-- 在创建表的时候给字段增加索引
CREATE TABLE 表名 (
    字段名1 数据类型 [完整性约束条件…],
    字段名2 数据类型 [完整性约束条件…],
    [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (字段名[(长度)] [ASC |DESC])
);
-- 创建完毕后，增加索引

-- 方法一：CREATE在已存在的表上创建索引
       CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
       ON 表名 (字段名[(长度)] [ASC |DESC]) ;
 
-- 方法二：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] 
       INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;

索引的删除

-- 删除索引
	DROP INDEX 索引名 ON 表名;
-- 删除主键索引
	ALTER TABLE 表名 DROP PRIMARY KEY;

显示索引信息

SHOW INDEX FROM 表名;
5. explain分析sql执行的情况
-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
 
-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引

测试索引

建表app_user：

CREATE TABLE `app_user` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT '' COMMENT '用户昵称',
    `email` varchar(50) NOT NULL COMMENT '用户邮箱',
    `phone` varchar(20) DEFAULT '' COMMENT '手机号',
    `gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
    `password` varchar(100) NOT NULL COMMENT '密码',
    `age` tinyint(4) DEFAULT '0' COMMENT '年龄',
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据：100w

-- 1418错解决方案(创建函数前执行此语句)
set global log_bin_trust_function_creators=true;
 
-- 插入100万条数据
DELIMITER $$	-- 写函数之前要写的标志
CREATE FUNCTION mock_data()	-- 创建mock_data()函数
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i < num DO
  		INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   		VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  		SET i = i + 1;
	END WHILE;
	RETURN i;
END;
 
-- 执行函数
SELECT mock_data();
测试查询速度
-- 查询用户名为'用户9999'性能分析
EXPLAIN SELECT * FROM app_user where name='用户99999'

在这里插入图片描述
增加索引后测试

-- 给name列创建常规索引
CREATE INDEX id_app_user_name ON app_user(`name`)
-- 再测试
EXPLAIN SELECT * FROM app_user where name='用户99999'

对比两次结果，速度有了很大的提升

4、索引原则

索引不是越多越好，小数据量的表不需要加索引
不要对经常变动的数据增加索引
索引一般加在经常要查询的列上

九、用户管理

方式一：可视化管理
方式二：SQL命令操作

用户信息存储在 mysql数据库的 user表中，对用户的管理本质上就是对这张表进行增删改查

-- 创建用户
CREATE USER zsr IDENTIFIED BY '123456'
 
-- 删除用户
DROP USER zsr
 
-- 修改当前用户密码
SET PASSWORD = PASSWORD('200024')
 
-- 修改指定用户密码
SET PASSWORD FOR zsr = PASSWORD('200024')
 
-- 重命名
RENAME USER zsr to zsr2
 
-- 用户授权(授予全部权限,除了给其他用户授权)
GRANT ALL	PRIVILEGES on *.* TO zsr2
 
-- 查询权限
SHOW GRANTS FOR zsr
-- 查看root用户权限
SHOW GRANTS FOR root@localhost
 
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM zsr

十、备份

保证重要的数据不丢失、数据转义

方式一：直接拷贝物理文件，MySQL数据表以文件方式存放在磁盘中
包括表文件 , 数据文件 , 以及数据库的选项文件
位置 : Mysql安装目录\data\（目录名对应数据库名 , 该目录下文件名对应数据表）

方式二：可视化管理
Navicat打开要备份的数据库，然后点击新建备份，点击对象选择，这里可以自定义选择备份的表，选择完毕后，点击备份即可开始备份，等待备份完成，关闭，然后便可看到备份的文件

方式三：可视化管理
选中要导出的表，右键转储SQL文件，然就就可以得到.sql文件

方式四：命令mysqldump导出

# mysqldump -h主机 -u用户名 -p密码 数据库 [表1 表2 表3] >物理磁盘位置/文件名
 
# 导出school数据库的cource grade student表到D:/school.sql
mysqldump -hlocalhost -uroot -p200024 school course grade student >D:/school.sql

然后便可看到导出的sql文件，然后可以命令行登录mysql，切换到指定的数据库，用source命令导入

十一、三大范式

如果一个关系模式R的所有属性都是不可分的数据项，则R属于第一范式
如果关系模式R属于第一范式，且每一个非主属性完全函数依赖于码，则R属于第二范式
若关系模式R属于第二范式，且R中所有的非主属性都直接依赖于码，则R属于第三范式
规范性问题：

数据库的范式是为了规范数据库的设计，但是实际中相比规范性，往往更需要看中性能、成本、用户体验等问题；

因此有时会故意给某些表增加一个冗余的字段，使多表查询变为单表查询。有时还会增加一些计算列，从大数据量变为小数据量（数据量大时，count(*)很耗时，可以直接添加一列，每增加一行+1，查该列即可）；阿里也曾提出关联查询的表最多不超过三张表。

这些就是为了性能、成本而舍弃一定规范性的例子

十二、SQL注入

SQL注入问题
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

sql注入案例：主函数中传入用户名，查找指定名字用户信息

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class SQLInjection {
    public static void main(String[] args) throws SQLException {
        searchName("' or '1=1");
    }
 
    //查找指定名字用户信息
    public static void searchName(String username) throws SQLException {
        //获得数据库对象connection
        Connection connection = JDBCUtils.getConnection();
        //获取sql执行对象statement
        Statement statement = connection.createStatement();
        //执行sql
        String sql = "select * from app_user where name='" + username + "'";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println("id:" + resultSet.getObject("id") + "phone:" + resultSet.getObject("phone"));
        }
        //释放连接
        JDBCUtils.release(connection, statement, resultSet);
    }
}

结果：查询到了数据库中所有的数据
这里传入一个不是用户名，而是一个不合法字符串，却获取到了全部的数据，为什么呢？

拼接整条sql语句是select * from app_user where name=' ' or '1==1'，其中1==1永远是真的，所以该sql语句相当于查询表中所有的数据；这就是sql注入，主要是字符串拼接引起的问题，十分危险！！
海市蜃楼807
关注
  • 18
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
【MySQL】数据库基础知识
m0_51064412的博客
05-20 5万+
文章目录一. 什么是数据库二. 为什么要有数据库三. 数据库分类四. 数据库的基本使用1. MySQL安装2. 检查MySQL后端服务器是否启动3. 连接MySQL服务器 一. 什么是数据库 数据库（database）是一整套的数据存储容灾解决方案。是用来组织、存储和管理数据的仓库。 当今世界是一个充满着数据的互联网世界，充斥着大量的数据。数据的来源有很多，比如出行记录、消费记录、浏览的网页、发送的消息等等。除了文本类型的数据，图像、音乐、声音都是数据。 为了方便管理互联网世界中的数据，就有了数据库管理系统
Mysql数据库基础知识整理
Kenneth0617的博客
07-14 7901
关系型数据库；以表格形式存储数据；一个数据库中包含多张表；在mysql中，事务是一种机制、一个操作序列，是访问和更新数据库的程序执行单元。事务中包含一个或多个数据库操作命令，会把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行.MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
数据库基础知识总结
weixin_48974143的博客
06-21 1081
天池龙珠计划训练营地址：https://tianchi.aliyun.com/specials/promotion/aicamps
数据库基础知识大全
u012087859的博客
09-01 9144
在大数据、云计算等概念被广泛提及的今天，数据库作为入门知识，它的重要性想必大家都知道了，今天小编为大家整理了一些数据库的基础知识，快快收藏吧~ 目录： 一、基本概念 二、数据库分类 三、数据库模型 四、数据库语言四大类 五、五个基本的关系代数操作 六、完整性 七、常用关系型数据库 八、国产化数据库 一、基本概念 首先，我们先来辨析一下几个关于数据库的基本概念，什么是 DB ？什么是 DBMS ？他们之间的联系和区别是什么？ 1. Data：数据，是数据库中存储的基本...
数据库基础知识.ppt
11-16
数据库基础知识 数据库基础知识是计算机科学中的一门重要课程，本篇资源摘要信息将对数据库的基本概念、数据库管理系统、数据库系统、数据模型等进行详细的解释。 一、数据库的概念 数据库是存储数据的仓库，是...
数据库基础知识.doc
12-01
数据库基础知识 数据库基础知识是指了解数据库的基本概念、原理和操作方法，它是数据库管理和开发的基础。以下是数据库基础知识的相关知识点： 一、数据库基本概念 * 数据库（Database）：是一个长期存储在计算机...
数据库基础知识培训.ppt
11-21
数据库基础知识培训 本文档主要介绍了数据库基础知识培训的相关内容，涵盖了数据库的基本概念、常用的数据库管理系统、数据库体系结构等知识点。 一、数据库基础概念 数据库是长期存储在计算机中的数据的集合，...
oracle数据库基础知识.ppt
11-21
"Oracle数据库基础知识" Oracle数据库基础知识是指Oracle数据库的基本概念、原理和操作技术。Oracle数据库是一种关系型数据库管理系统，提供了强大的数据存储、管理和检索功能。本文档将对Oracle数据库基础知识进行...
基于JSP的高校信息资源共享平台
heye0910032的博客
07-22 670
高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。通过使用JSP技术和MySQL数据库，本系统实现了一个稳定、安全、易用的高校信息资源管理平台。虽然在开发过程中遇到了一些技术挑战，但通过不断学习和实践，最终完成了一个能够满足用户需求的系统。未来，我们将继续优化系统功能，提升用户体验，以期达到更高的应用价值和社会效益。高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。
04-用户画像+sqoop使用
weixin_46567476的博客
07-21 561
sqoop的作用是实现数据的导入和导出，主要是对数据库和数据仓库之间的操作。qoop脚本就是将sqoop指令写入shell文件 后缀是 .sh。只要是支持jdbc连接的数据库都可以使用sqoop操作。
redis常用指令
m0_62289159的博客
07-22 777
redis常用指令
Django cursor()增删改查和shell环境执行脚本
人生苦短，何妨一试
07-21 469
在Django中，cursor()方法是DatabaseWrapper对象（由django.db.connectio提供）的一个方法，用于创建一个游标对象。这个游标对象可以用来执行SQL命令，从而实现对数据库的增删改查操作。使用cursor.execute()方法执行SQL查询语句，可以获取数据库中的数据。查询your_table表中的所有记录。执行查询后，你可以使用cursor.fetchall()或cursor.fetchone()等方法来获取查询结果。
sqlalchemy使用json_unquote函数的mysql like查询
ithongchou的博客
07-23 1148
为您自己的数据库连接信息，并根据实际表结构修改模型类。函数查询MySQL JSON字段可以通过使用。下面是一个示例，假设有一个名为。请确保替换示例代码中的数据库连接字符串(在SQLAlchemy中使用。的表，其中包含一个名为。
mysql的存储过程：
最新发布
m0_71178834的博客
07-25 266
完成特点功能的sql语句的集合。把定义好的sql集合在一个特定的sql的函数当中每次执行调用函数即可，还可以实现传参的调用。
MongoDB教程（十五）：MongoDB原子操作
菜鸟小码的博客
07-21 840
在多线程或分布式环境中，保证数据一致性是一项重大挑战。原子操作是解决这一问题的关键技术之一，它确保一系列操作要么全部成功，要么全部失败，从而维持数据的完整性和一致性。MongoDB 提供了一系列内置的原子操作，使开发者能够轻松地在数据库层面实现事务性和数据完整性。本文将深入探讨 MongoDB 中的原子操作，包括更新、插入和删除操作的原子性，并通过具体案例代码展示如何在实际应用中运用这些原子操作。
ubuntu20.04服务器搭建mongodb7
glovenone的专栏
07-25 472
ubuntu安装mongodb
数据库基础知识与关系模型解析
"数据模型——概念模型实例-第1章 数据库基础知识，涵盖了数据库系统、数据库系统的体系结构、数据模型、关系数据库、规范化设计理念和方法等核心内容，适合初学者了解数据库基础知识。" 在数据库技术中，数据模型...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值