MySQL(一)

MySQL

一.初识数据库

1. 数据库分类

关系型数据库:

• MySQL,Oracle,Sql Server,SQLite
• 通过表和表之间,行与列之间的关系进行数据的存储。

非关系型数据库:

• Redis,MongDB
• 非关系型数据库,对象存储,通过对象的自身属性来决定

数据库语言:

• DDL:数据库定义语言
• DML:数据库操作语言
• DQL:数据库查询语言
• DCL:数据库控制语言

2.连接数据库

命令行连接:

mysql -u root -p*****    --*****是密码

updata mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost';  --修改用户密码

flush privileges;  --刷新权限

show databases;  --查看所有的数据库

use ***;  --切换数据库 ***为数据库名

show tablse;  --查看数据库中所有的表

describe ****;  --显看表的结构  或者desc ****;

create database ****;  --创建一个数据库

exit  --退出连接

--单行注释

/* 多行注释 */

二.操作数据库

1.操作数据库

1. 创建数据库:

create database [if not exists] ***;

2. 删除数据库:

drop database [if exists] ***;

3. 使用数据库:

use `***`;
-- 如果你的表名或者字段名是一个特殊字符,就需要带 ``

4. 查看数据库

show databases; --查看所有的数据库

2. 数据库的数据类型

数值

• tinyint 十分小的数据 1字节

• smallint 较小的数据 2字节

• mediumint 中等大小数据 3字节

• int 标准整数 4字节

• bigint 较大的数字 8字节

• floot 单精度浮点数 4字节

• double 双精度浮点数 8字节(精度问题)

• decimal 字符串形式的浮点数 (一般用于金融计算)

字符串

• char 字符串 固定255
• varchar 可变字符串 0~255
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1

时间日期

• data 日期格式:yyyy-mm-dd
• time 时间格式:hh:mm:ss
• datatime 日期时间组合 :yyyy-mm-dd hh:mm:ss
• timestamp 时间戳 yyyymmddhhmmss
• year 年份

null 没有值 未知
不建议使用NULL来计算,结果为NULL

3. 数据库的字段属性

• Unsiqned 无符号 声明后不能为负
• zerofill 0填充的,不足的位数使用0来填充
• auto_increment 自增 自动在上一条的基础上+1 个表只能有一个自增字段 一般用于主键 且为整数类型
• Not Null 非空 不允许字段为空
• Null 空 如果不填写值,默认为null
• 默认 设置默认的值,不填写自动为默认值

一些做项目常用的列,表示一个记录存在的意义:

• ID 主键
• version 用于乐观锁
• is_delete 伪删除
• gmt_create 创建时间
• gmt_update 修改时间

4. 创建数据库表

CREATE TABLE if not EXISTS `student` (
`id` INT(4) NOT NULL auto_increment COMMENT '学号',
`name` VARCHAR(10)  DEFAULT('匿名') COMMENT '姓名',
`sex` VARCHAR(2) NOT NULL DEFAULT('男') COMMENT '性别',
`pwd` VARCHAR(20) NOT NULL DEFAULT('123456') COMMENT '密码',
`birthday` datetime DEFAULT(NULL) COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT(NULL) COMMENT '家庭住址',
`email` VARCHAR(20) DEFAULT(NULL) COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- auto_increment 自增 COMMENT 注释 ENGINE 引擎  DEFAULT CHARSET 字符集

格式:

CREATE TABLE [if not EXISTS] `表名` (
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集] [注释]

常用命令

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

5.数据表的类型

数据库引擎:

INNODB  默认使用
MYISAM 早些年使用的

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大,约为MYISAM的两倍

常规使用操作:
MYISAM :节约空间,速度较快
INNODB:安全性高,事务的处理,多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下
本质还是文件的存储!

MySQL引擎在物理文件上的区别

• INNODB 在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
• MYISAM 对应文件
  *.frm:表结构的定义文件
  *.MYD :数据文件(data)
  *.MYI : 索引文件(index)

在MySQL8.0移除了 .frm文件,都统一归并为.ibd文件
Oracle 将.frm文件的信息及更多信息移动到叫做序列化字典信息（Serialized Dictionary Information，SDI），SDI被写在.ibd文件内部，它是数据字典包含的信息的一个冗余副本。

设置数据库表的字符集编码

navicat 中: CHARACTER SET = utf8
其他中:CHARSET=utf8

(低版本)不设置的话,会是MySQL默认的字符集编码latin1(不支持中文)
在MySQL8.0中将默认字符集编码修改为:Utf8mb4,不再是latin1

6.修改删除数据库表

修改表

ALTER TABLE 表名 [RENAME AS 修改后的表名,] /*修改表名*/
[ADD 字段名 类型,]   /*添加字段*/
[MODIFY 字段名 类型,]   /*修改约束*/
[CHANGE 旧字段名 新字段名 类型,]  /*重命名字段,修改约束*/
[DROP 字段名];  /*删除字段*/

删除表

DROP TABLE [IF EXISTS] tea;
-- [IF EXISTS] 如果存在

三.MySQL数据管理

1.外键(了解)

方式一,在创建爱你表的时候,增加约束,非常麻烦

CREATE TABLE `grade`(
`gradeid` INT(4) NOT NULL auto_increment COMMENT '年级id',
`gradename` VARCHAR(10) NOT NULL COMMENT '年级姓名',
PRIMARY KEY (`gradeid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL COMMENT '性别',
  `birthday` datetime NOT NULL COMMENT '出生日期',
  `gradeid` INT(4) NOT NULL COMMENT '学生的年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(20) DEFAULT 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(4) NOT NULL auto_increment COMMENT '年级id',
`gradename` VARCHAR(10) NOT NULL COMMENT '年级姓名',
PRIMARY KEY (`gradeid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL COMMENT '性别',
  `birthday` datetime NOT NULL COMMENT '出生日期',
  `gradeid` INT(4) NOT NULL COMMENT '学生的年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(20) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `student` /*表名*/
ADD CONSTRAINT `FK_gradeid`   /*约束名*/
FOREIGN KEY(`gradeid`)   /*作为外键的列*/
REFERENCES `grade`(`gradeid`);   /*要连接的表名(要连接的字段名)*/

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

最佳实践:

• 数据库就是单纯的表,只用来存数据,只有数据和字段
• 我们想使用多张表的数据,想使用外键(用程序去实现)

2.DML语言

数据库的意义:数据存储,数据管理
DML:数据操作语言

• insert
• update
• delete

3.添加

INSERT INTO 表名 ([字段一],[字段二],[字段三]) VALUES ('值一','值二','值三');
INSERT INTO `grade` (`gradename`) VALUES ('大一');
INSERT INTO `grade` (`gradename`) VALUES ('大二'),('大三'),('大四');
INSERT INTO `student` (`name`,`pwd`,`sex`,`gradeid`) VALUES ('张三','aaaaaa','男','1');

注意事项:
1.字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
3.可以同时插入多条数据，VALUES后面的值需要使用逗号隔开

4.修改

UPDATE 表名 SET 字段名 = 内容 [,字段名2=内容2] [WHERE 条件];
字段名两侧最好加上 如 ` 如 `name`
不指定条件的情况下,会修改所有的表
内容,是一个具体的值,也可以是一个变量。如：

UPDATE `student` SET `birthday` = CURRENT_TIME WHERE id ='1';

where条件内的操作符:

操作符	含义	例子	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>	大于	5>5	false
<	小于	5<5	false
>=	大于等于	5>=5	true
<=	小于等于	5<=5	true
between and	在某个范围内	between 2 and 5	[2,5]
and	两个条件同时成立	5>3 and 4<6	true
or	两个条件有一个成立	5>3 or 4>6	true

5.删除

delete from 表名 [where 条件]

--删除数据(全部删除,不推荐)
DELETE FROM `grade`;
--删除指定数据
DELETE FROM `grade` WHERE gradeid='4';

TRUNCATE 表名
完全清空一个数据表,表的结构和索引不变

TRUNCATE `grade`;

delete 和 truncate 的区别

相同点:

• 都能删除数据,不会修改表结构

不同点:

• truncate 会重新设置自增列,计数器归零
• truncate 不会影响事务

四.DQL查询数据

1.DQL(Data Query Language 数据查询语言)

所有的查询操作都是用它,数据库中最核心的语言,使用评率最高的语言

2.指定查询字段

-- select常用语法
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_conntOFFSET offset}];  -- 指定查询的记录从哪条至哪条

-- SELECT 完整语法
SELECT
	[ALL | DISTINCT | DISTINCTROW]
	[HIGN_PRIORITY]
	[STRAIGHT_JOIN]
	[SQL_SMALL_RESULT] [SQL_GIB_RESULT] [SQL_BUFFER_RESULT]
	[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
	[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY{col_name | expr | position}
	[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY{col_name | expr | position}
	[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
	[CHARACTER SET charset_name]
	export_options 
	| INTO DUMPFILE 'file_name' 
	| INTO var_name[, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

查询全部的学生:

SELECT * FROM student;  --

查询指定字段,并给’列’和’表’起一个别名

SELECT `studentno` AS '学号',`studentname` AS '姓名' FROM `student` AS s;

函数: Concat(a,b) 拼接字符串

SELECT CONCAT(studentno,' ',studentname) AS '学生信息' FROM `student`;

去重:去掉select查询出来结果的重复项,重复数据只显示一条

SELECT DISTINCT `studentno` AS '学生学号' FROM `result`;

数据库列中的表达式

SELECT VERSION();  -- 查询系统版本

SELECT 100*3-1;  -- 用来计算

SELECT @@auto_increment_increment;  -- 查询自增的步长

SELECT `studentno`,`studentresult`+1 AS '改变后成绩' from result;
-- 数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量....

3. where条件子句

用来检索数据中符合条件的值

逻辑运算符

运算符	语法	描述
and 或 &&	a and b 或 a && b	逻辑与
or 或 ||	a or b 或 a||b	逻辑或
not 或 !	not a 或 !a	逻辑非

-- 查询
SELECT `studentno` AS '学号',`studentresult` AS '学生成绩' FROM result WHERE `studentresult`>=90;
或者
SELECT `studentno` AS '学号',`studentresult` AS '学生成绩' FROM result WHERE `studentresult` BETWEEN 90 AND 100;

SELECT `studentno` AS '学号',`studentresult` AS '学生成绩' FROM result WHERE `studentno`!='1000';

模糊查询:比较运算符

运算符	语法	描述
IS NULL	a is null	如果a为null则为真
IS NOT NULL	a is not null	如果a不为null则为真
BETWEEN	a between b and c	若a在b和c之间,则为真
LIKE	a like b	SQL匹配,a匹配b,则为真
IN	a in(b,c,d)	如果a在b,c,d中的某一个,则为真

-- 空有两种状态,一种是null一种是'' 不可`address`=null
SELECT `studentno`,`studentname` FROM `student` WHERE `address` ='' OR `address` IS NULL;

-- between 后的区间必须连续
SELECT `studentno`,`studentname` FROM	`student` WHERE `gradeid` BETWEEN 1 AND 2;

-- like中 %表示多个任意字符 _表示一个任意字符
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张%';

-- in中包含的是一个具体的值,不可使用% 和 _
SELECT `studentno`,`studentname` FROM `student` WHERE `gradeid` IN ('1','3');

4. 联表查询

join语句

-- 第一种情况
SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S LEFT JOIN `result` AS R ON S.studentno = R.studentno;

-- 第二种情况
SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S LEFT JOIN `result` AS R ON S.studentno = R.studentno WHERE R.studentno IS NULL;

-- 第三种情况
SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S INNER JOIN `result` AS R ON S.studentno = R.studentno;

--第四种情况
SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S RIGHT JOIN `result` AS R ON S.studentno = R.studentno;

-- 第五种情况
SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S RIGHT JOIN `result` AS R ON S.studentno = R.studentno WHERE S.studentno IS NULL;

-- 第六种情况
SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S LEFT JOIN `result` AS R ON S.studentno = R.studentno UNION
SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S RIGHT JOIN `result` AS R ON S.studentno = R.studentno;

-- 第七种情况
SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S LEFT JOIN `result` AS R ON S.studentno = R.studentno WHERE R.studentno IS NULL UNION 
SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S RIGHT JOIN `result` AS R ON S.studentno = R.studentno WHERE S.studentno IS NULL;

自连接

自己的表和自己的表连接,核心:将一张表拆为两张一样的表使用

实例:

-- 数据表创建
CREATE TABLE `category`(
 `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
 `pid` INT(10) NOT NULL COMMENT '父id',
 `categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`) 
 ) ENGINE=INNODB  AUTO_INCREMENT=9 DEFAULT CHARSET=utf8; 

INSERT INTO `category` (`categoryid`, `pid`, `categoryname`) 
VALUES ('2','1','信息技术'),
('3','1','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');

父类:

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类:

pid	categoryid	categoryname
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

目的操作:查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 实现命令:
SELECT A.`categoryname` AS '父类',B.`categoryname` AS '子类' FROM `category` AS A,`category` AS B WHERE A.`categoryid` = B.`pid`;

5.分页和排序

排序:…order by 要排序的字段 asc/desc

升序asc(默认) 降序desc

SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S 
LEFT JOIN `result` AS R ON S.studentno = R.studentno ORDER BY `studentresult` DESC;

分页: …LIMIT 起始值,页面大小(一页数据的行数)

limit 0,5 显示第1-5条
limit 1,5 显示第2-6条,以此类推
分页:缓解数据库压力,现在都用瀑布流

SELECT S.`studentno`,`studentname`,`subjectno`,`studentresult` FROM `student` AS S 
LEFT JOIN `result` AS R ON S.studentno = R.studentno ORDER BY `studentresult` DESC LIMIT 1,5;

网页中使用的第几页的计算方式:
第一页 limit 0 , 5
第二页 limit 5 , 5
第n页 limit (n-1)*pageSize , pageSize
pageSize:页面大小 n 当前页 (n-1)*pageSize 起始值 (数据总数-1)/页面大小+1=总页数

6. 子查询和嵌套查询

格式:
select … from … where (select …);
在where中再嵌套一个查询语句

SELECT `studentname`,`subjectno`,`studentresult` FROM `result` AS r 
INNER JOIN `student` as s ON r.studentno=s.studentno WHERE r.subjectno in 
(SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-1') ORDER BY `studentresult` DESC;

7. 分组

所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

SELECT `subjectname`,COUNT(*) AS 数量 FROM `result` AS r INNER JOIN `subject` as s ON r.subjectno=s.subjectno 
WHERE `studentresult`>=80 GROUP BY `subjectname` HAVING COUNT(*) >=2;

五.函数

1.常用函数

数学运算:

函数	描述
ABS()	绝对值函数
CEILING()	向上取整
FLOOR()	向下取整
RAND()	返回一个(0,1)之间的随机数
SIGN()	判断一个数的符号 0-0 正数返回1 负数返回-1

字符串函数:

函数	描述
CHAR_LENGTH(str)	返回字符串长度
CONCAT(str1,str2)	拼接字符串
INSERT(str,pos,len,newstr)	将str字符串从第pos个位置len个字符替换为newstr
LOWER(str)	将字符串转换为小写
UPPER(str)	将字符串转换为大写
INSTR(str,substr)	查找substr第一次出现在str中的位置
REPLACE(str,from_str,to_str)	将str中的from_str替换为to_str
SUBSTR(str,x,y)	将str中从第x个开始,截取y个输出
REVERSE(str)	翻转字符串

时间和日期函数:

函数	描述
CURRENT_DATE / CURDATE()	获取当前日期
NOW()	获取当前日期时间
LOCALTIME()	获取本地时间
SYSDATE()	系统时间
YEAR(NOW()) / MONTH(NOW()) / DAY(NOW())	获取年/月/日
HOUR(NOW()) / MINUTE(NOW()) / SECOND(NOW())	获取时/分/秒

系统:

函数	描述
SYSTEM_USER() / USER()	当前系统用户
VERSION()	当前版本

2.聚合函数

函数	描述
COUNT()	计数
SUM()	求和
AVG()	平均数
MAX()	最大值
MIN()	最小值

统计的区别:
count(1),count(*),count(列)的区别

count():

SELECT COUNT(*) FROM student; 

sum():

SELECT `studentname`,sum(studentresult) as '总分' FROM `student` S INNER JOIN `result` R on S.studentno=R.studentno GROUP BY 1;

AVG()

SELECT `subjectname`,AVG(studentresult) AS '平均分' FROM `subject` S INNER JOIN `result` R ON S.subjectno=R.subjectno GROUP BY 1;

MAX()

SELECT `subjectname`,MAX(studentresult) AS '平均分' FROM `subject` S INNER JOIN `result` R ON S.subjectno=R.subjectno GROUP BY 1;

MIN()

SELECT `subjectname`,MIN(studentresult) AS '平均分' FROM `subject` S INNER JOIN `result` R ON S.subjectno=R.subjectno GROUP BY 1;

3.数据库级别的MD5加密

MD5信息摘要算法（MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数

主要增强算法复杂度和不可逆性
MD5不可逆,但具体值的MD5是一样的
MD5破解的原理:背后有一个数据字典库,去解密库匹配或者暴力破解,简单的可以破解

-- 表结构
CREATE TABLE `testmd5` (
	`id` INT(4) NOT NULL auto_increment,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8;

-- 插入数据
INSERT INTO `testmd5` (`name`,`pwd`) VALUES ('张三','123456'),('李四','123456'),('王五','123456');

-- 加密
UPDATE `testmd5` SET `pwd`=MD5(`pwd`);

-- 插入时加密
INSERT INTO `testmd5` (`name`,`pwd`) VALUES ('赵六',MD5('123456'));

-- 校验呢?
-- 将用户传递进来的密码,进行MD5加密,然后对比加密后的值
SELECT * FROM `testmd5` WHERE `name` ='张三' AND `pwd`=MD5('123456');