数据库基础知识

数据库

一、概论

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注入,主要是字符串拼接引起的问题,十分危险!!
  • 18
    点赞
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值