【MySQL】 基础知识 详细版(上)

两天学完MySQL

今天肝了一天学了MySQL,本来打算一天学完的,但是放假了还是会有各种乱七八糟的事情。只能分成两天来学习。关于MySQL 的安装还是比较简单的,主要是我安装了就懒得再截图记录了,大家不要用msi文件安装,直接cmd安装比较方便,初学者还能熟悉一下cmd操作。博客里面大部分都是使用了SQLyog,这可以对数据库进行可视化操作。

1 连接数据库

打开cmd 不熟悉的小伙伴 快捷键 :win+r 输入cmd shift +ctrl+enter是以管理员的身份启动

mysql -uroot -p --按回车可以不显示密码

mysql -uroot -p密码  --直接输入密码

flush privileges;--刷新权限
--------------------------------------------
--所有语句都是;结尾
show databases; --展示全部数据库

use 数据库名 --使用数据库

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

describe 表名; --显示数据库中表的信息

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

exit; --退出链接

--单行注释
/*
*/


2 操作数据库

2.1 操作数据库(了解)

创建数据库

create database if not exists 数据库名

删除数据库

drop database if exists 数据库名

如果你的表名或者字段名是个特殊字符,就需要带`` 之后的字段和表都会带上以免不必要的麻烦

使用数据库

use 数据库名 --如果你的表名或者字段名是个特殊字符,就需要带``

查看数据库

 show databases --查看所有数据库

学习思路:

  1. 对照sqlyog可视化历史记录,我们再sqlyog上进行操作的时候,历史记录上都会有对应的代码。基此我们可以对照学习

  2. 固定的语法或关键词必须要记住

2.2 数据库的列类型


数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标准的整数 4个字节 常用的
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节
  • decimal 字符串的类型浮点数 大数类型 金融计算的时候,一般使用 decimal

字符串

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

时间日期

  • date YYYY-MM-DD 日期格式
  • time HH:mm:ss 时间格式
  • datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
  • timestamp 时间戳 1970.1.1 到现在的毫秒数
  • year 年份表示

null

  • 没有值,未知
  • 不要使用null进行运算,结果一定为null

2.3 数据库的字段属性(重点)

Unsigned:

  • 无符号的整数
  • 表示该列不能为负数

zeorfill:

  • 0填充的
  • 不足的位数,使用0来填充 int(3), 5—005;

自增:

  • 自动在上一条的基础上+1
  • 通常用来设置唯一的主键,必须是整数类型
  • 可以自定义设置主键自增的起始值和步长

非空(NULL或 NOT NULL):

  • 如果不填写值就会报错
  • NULL,如果不填写,默认就是null;

默认:

  • 设置默认的值
  • sex 默认值为男,如果不指定该列的值,那么指定为男

拓展

每个表都必须存在一下五个字段!未来做项目用的,表示一个记录存在意义。这是阿里巴巴规范手册上的,现阶段了解即可

id 主键

`version`  乐观锁

is_delete   伪删除

gmt_create 创建时间

gmt_update 修改时间

2.4 使用sql创建一张表格

-- 目标创建school数据库里面的student表格
-- 注意点,使用英文(),表的名称和字段 尽量使用`` 括起来
-- AUTO_INCREMENT 自增
-- 字符串的使用 单括号加起来
-- PRIMARY KEY 主键,一般一个表只有唯一的主键

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 '123435' 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
SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --显示表的结构
-- 这就是逆向操作

2.5 数据表的类型

关于数据库引擎

MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间的大小较小较大

常规使用操作

  • MYISAM 节约空间 ,速度较快
  • INNODB 安全性高,支持事务的处理

在物理空间存在的位置

所有数据库文件都存在data目录下

本质还是文件的存储

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

  • INNODB 在数据库表中只有一个 *frm文件,以及上级文件下innodb文件
  • MYISAM 对应的文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件
    • .MYI 索引文件

设置数据表的字符集编码

CHARSET=utf8

不设置的话,会是mysql默认的字符集编码(不支持中文)!

MYSQL的默认编码时Latin1,不支持中文

可以在my.ini中配置默认的编码

character-set-server=utf8

但是还是在建表的时候加上CHARSET=utf8,否则这个表在别人电脑上可能就是乱码。

2.6 修改删除 表

修改表
--修改表名    旧表名             新表名
ALTER TABLE teacher RENAME AS teacher1

-- 增加表的字段
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段名称(重命名,修改约束)
ALTER TABLE teacher1 MODIFY age VARCHAR (11)--修改约束
ALTER TABLE teacher1 CHANGE age age1 INT(1) --字段重命名

-- 删除表的字段
ALTER TABLE  teacher1 DROP age1

删除表
--删除表
DROP TABLE IF EXISTS teacher1

注意点:

  • `` 所有的字段名使用这个包裹,为了安全
  • 注释 –
  • sql 关键字大小写不敏感,建议大家写小写
  • 所有的符号全部用英文

3.MySQL数据管理

3.1 外键(了解即可)

外键也称之为外键约束: foreign key

外键: 外面的键, 一张表的一个字段(非主键)指向另外一个表的主键, 那么该字段就称之为外键.

外键所在的表称之为子表(附表); 外键所指向的主键所在的表称之为父表(主表)

添加外键

方式一 在创建表的时候,添加约束(麻烦好复杂)

  1. student表的gradeid字段要去引用年级表的gradeid
  2. 定义外键key
  3. 给这个外键添加约束 references引用
CREATE TABLE `grade`(
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
   `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
   PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
  


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 '123435' 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

删除有外键关系的表的时候,先要删除使用外键的(从表student),再去删除被引用的表(主表)

方式二:创建表成功后,添加外键约束

--创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade` (`gradeid`);
--ALTER TABLE `表`ADD CONSTRAINT `约束名` FOREIGN KEY(`作为外键的列`) REFERENCES `哪个表` (`哪个字段`);

以上操作都是物理外键,数据库级别的外键,不建议使用。

最佳实践
  • 数据库就是单纯的表,只用来存数据,只有行和列
  • 我们想使用多张表的数据,想使用外键,用程序来实现。

3.2 DML语言(全部背下来)

我们之前学了如何修改删除 表,这是对表或者字段操作的,现在学习一下DML语言。忘了修改删除表操作的回头去看一下。

数据库的意义:数据的存储,数据管理

DML语言:

insert

update

delete

3.3添加

--插入语句(添加)
--insert into 表名 (字段名1) values('值1'),('值2'),('值3'),('值4')
--由于主键自增我们可以省略,如果不写表的字段,他就会一一匹配
-- 一般写插入语句,字段名可以省略,我们一定要数据和字段一一对应
-- 可以一次插入多个值,就意味着对应的几列有好几行的数据都添加了

INSERT INTO `grade` (`gradename`) VALUES('大一'),('大二'),('大三'),('大四')

注意事项:

  1. 字段值可以省略,但是我们必须一一对应不能少
  2. values后面的值需要使用**,**隔开即可 values(‘值1’),(‘值2’),(‘值3’),(‘值4’)

3.4 修改

Update 修改谁(条件) set 原来的值=新值

-- 修改学员的名字
UPDATE `student` SET `name`='学员1' where id=1;

--不指定条件的情况下,会改动所有表格
update `student` set `name`='糟糕';

--修改多个属性,逗号隔开
update `student` set `name`='詹姆斯' ,`email`='13453141@outlook.com' where id=1;

--语法
--update 表名 set colnum_name =value, [colnum_name =value] where [条件]

条件:where 子句 运算符 id 等于某个值,大于某个值,在某个区间的时候修改…

操作符会返回布尔值

操作符:
  • =, <, >, >=, <= 这些操作符与我们平时使用时的作用相同
  • <> 和!= 都是不等于的意思
  • AND相当于&&
  • OR相当于||/
  • between … and… 在某一个范围之内,闭区间。
--通过多个条件定位数据
update `student` set `name`='杜兰特' where `name`='詹姆斯' and sex='男'

注意:

  • colnum_name 是数据库的列,尽量带上``
  • 筛选的条件,如果没有指定,则会修改所有的列
  • value,是一个具体的值,也可以是一个变量
update `student` set `birthday`= CURRENT_TIME where`name`='杜兰特' AND sex='男'

3.5删除

delete 命令

语法:delete from 表名 [where 条件]

-- 删除数据(避免这样写),会将表中的所有数据删除
delete from `student`

-- 删除指定数据
delete from `student` where id=1;

TRUNCATE 命令

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

-- 清空 student 表
TRUNCATE `student`

delete 与 TRUNCATE 的区别

相同点:

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

不同点:

  • TRUNCATE 重新设置 自增列,计数器会归零
  • TRUNCATE 不会影响事务
--测试 delete与 TRUNCATE的区别
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`) VALUE('1'),('2'),('3')
DELETE FROM `test` -- 不会影响自增

TRUNCATE TABLE `test` -- 会影响到自增,自增归零

  1. 我们先创建了一个test表格

在这里插入图片描述

  1. 创建好是这样的,我们在添加三条记录

在这里插入图片描述

  1. 我们使用delete进行全部删除后,再添加三条记录

在这里插入图片描述

可以看到自增的id没有归零,而TRUNCATE会将id的自增归零

了解即可:delete删除问题,断电重启数据库

  • innodb 自增会从1开始(存在内存当中的,断电即失)
  • MYISAM 继续从上一个自增量开始 (存放在文件当中的,不会丢失)

4.DQL查询数据

(Data Query) LANGUAGE :数据查询语言

  • 所有的查询操作都用它 select
  • 简单的查询,复杂的查询它都能做
  • 数据库中最核心的语言,最重要的语言,使用频率最高的

4.1简单查询

我们有两张表 student表中含有 学号 姓名 性别 年级 住址 手机号 身份证号 和出生日期

result 中含有 学号 考试科目 考试时间 和成绩

-- 语法 select 字段 from 表
--查询全部的学生
select * from student

--查询指定字段,中间用逗号隔开
select `StudentNo`,`StudentName` from student

-- 别名,有时候列的名字不是那么清楚,我们可以起别名 字段和表都可以起别名
select `StudentNo` as 学号 ,`StudentName` 姓名 from student as 学生

-- 函数 Concat(a,b) 将a和b连起来
select concat('姓名',StudentName) as 新名字 from student

去重 distinct

作用:我们想查询哪些学生参加了考试,但是一个学生会参加多门考试,这时候我们就需要去重,使重复的数据只显示一条

--查询一下有哪些同学参加了考试
select *from result --查询全部成绩
select `studentNo` from result --查询有哪些同学参加了考试
select distinct `studentNo` from result--发现重复数据,去重

数据库的列(表达式)

select version() --查询系统版本
select 100*3-1 as 计算结果 --用来计算(表达式)
select @@auto_increment_increment --查询自增的步长(变量)

-- 学员考试成绩 +1分查看
select `StudentNo` ,`StudentResult`+1 as'提分后' from result

select 表达式 from 表

数据库中的表达式 :文本值,列,NULL,函数,计算表达式,系统变量…

4.2where条件子句

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

搜索的条件就是

逻辑运算符

运算符语法描述
and &&a and b a&& b逻辑与
or ||a or b a|| b逻辑或
not !not a !a逻辑非
select `studentNo`,`StudentResult` from result

-- 查询考试成绩再95-100之间的同学
select `StundentNo`,`StudentResult` from result
where StudentResult >=95 and StudentResult <=100

--模糊查询
select `StudentNo`,`StudentResult` from result
where StudentResult between 95 and 100

-- 除了学号为100的学生之外的同学成绩
select `StudentNo`,`StudentResult` from result
where studentNo!=100

模糊查询

运算符
IS NULLa is null如果操作符为NULL,结果为真
IS NOT NULLa is not null如果操作符不为NULL,结果为真
BETWEENa between b nad c若a在b和 c之间,结果为真
Likea like bSQL匹配,若a匹配b结果为真
Ina in (a1,a2,a3,a4…)假设a在a1,或者a2…其中的某一个值中,结果为真
-- ======================like============================

-- 查询姓刘的同学
--like结合 %代表0到任意字符  _代表一个字符
select `StudentNo`,`StudentName` from student
where StudentName like '刘%'


--查询姓刘的同学,后面只有以一个字
select `StudentNo`,`StudentName` from student
where StudentName like '刘_'

--查询名字中间带有嘉字的
select `StudentNo`,`StudentName` from student
where StudentName like '%嘉%'

-- ======================in=============================
--查询 1001,1002,1003号学员
select `StudentNo`,`StudentName` from student
where StudentNo in (1001,1002,1003);

--查询在北京,上海的学生
select `StudentNo`,`StudentName` from student
where `Address`  in('北京','上海');

--=====================null not null=====================
查询地址为空的同学
select `StudentNo`,`StudentName` from student
where `Address` is NULL or `Address` is '';

4.3联表查询

在这里插入图片描述

之前的查询都是查一张表里面的,现实中的查询肯定比这复杂多,所以我们学习一下多表查询。举个例子:我们有两张表,一张是关于学生成绩的表格,里面包含学生的成绩,学号,姓名。一张是学生填的申请交换生的表格,里面有 学号 姓名 想要去的学校。 那么我们怎么查询表一中报名了的同学呢?

首先我们了解一下SQL 连接(join)子句

JOIN 类型:

  1. INNER JOIN :如果两个表中都满足条件,则返回对应的字段相当于取交集。
  2. LEFT JOIN :假设有一张空表,left join就是先将左表全部放在这个空表上,再去跟右表匹配,符合条件的则会添加左表对应的字段。如果匹配不成功,左表 也会全部保留,对应右表的字段则为NULL。
  3. RIGHT JOIN:保留全部右表,即使匹配不成功。
--语法
 from 左表a left join 右表b  --这时候就是以左表a为基准
 from 左表a right join 右表b --这时候就是以右表b为基准

思路:

  1. 分析需求看,分析查询的字段来自哪些表
  2. 确定使用哪种连接查询
  3. 确定交叉点,这个两个表中哪个数据是相同的
    • 判断的条件:学生表中id=报名表中id
4.3.1INNER JOIN :

这个是表一:

在这里插入图片描述

这个是表二

在这里插入图片描述

SELECT j.id,sex,`name`,GPA,university 
FROM jiaohuan j
INNER JOIN student s
ON s.id = j.id

在这里插入图片描述

4.3.2 LEFT JOIN

在这里插入图片描述

4.3.3 RIGHT JOIN

在这里插入图片描述

4.3.4 自连接(了解)

自己的表和自己的表连接,核心:一张表分成两张一样的表即可

现在我们有一张叫category的表格 其中 pid代表这个category的上级种类,为1就代表这是最高级别的。categoryid代表的它的子种类的pid。

在这里插入图片描述

为了看的清楚我将这一张表拆成两张表

父类

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

子类

pidcategoryidcategoryname
34数据库
36web开发
57ps技术
28办公信息

操作:查询父类对应的子类关系,查询出来的结果应该长这样:

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

那么我们怎么使用sql查询呢?

-- 查询父子信息

SELECT a.categoryname AS '父栏目' ,b.categoryname AS '子栏目' 
FROM category AS a ,category AS b
WHERE a.categoryid = b.pid

我们只有一张表,但是我们给这一张表起两个名字,相当于有了两张一模一样的表。

在这里插入图片描述

4.4分页和排序

4.4.1 排序
-- 分页和排序  
-- order by 通过哪个字段排序
-- 排序:升序 asc
-- 降序 desc
-- 语法 order by `字段` asc/desc
 
4.4.2 分页

为什么要分页?

缓解数据库压力,给人的体验更好

语法:limit 查询起始下标 , pagesize

--假设一页有五条数据
-- 第一页 limit 0,5  (1-1)*5
-- 第二页 limit 5,5  (2-1)*5
-- 第二页 limit 10,5 (3-1)*5
-- 第N页 的查询起始下标  (n-1)*pagesize

--一般我们在分页的时候 要显示这么几个值
-- 【pagesize :页面大小】
-- 【(n-1)*pagesize:起始值】
-- 【n:当前页】
-- 【数据总数/页面大小=总页面】

现在查询语句慢慢复杂起来了,我们看一下select的标准语法

在这里插入图片描述

4.5 子查询

我们之前的where里的查询都是一个确定的值,那么怎么让where里面判断一个不确定的值(计算出来的值)。这就要用到子查询

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

我们有三张表 】

  1. student 里面有 StudentNo StudentName
  2. result 里面有 StudentNo SubjectNo 和 studentReuslt
  3. subject 里面有 SubjectNo 和SubjectName
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNO`,`StudentName` 
FROM student s
INNER JOIN result r
ON r.StudentNo =s.StudentNo
WHERE `StudentResult` >=80

-- 在这基础上增加一个学科  高等数学
-- 查询 高等数学 的编号
SELECT DISTINCT s.`StudentNO`,`StudentName` 
FROM student s
INNER JOIN result r
ON r.StudentNo =s.StudentNo
WHERE `StudentResult` >=80 
AND `SubjectNo` =(
  SELECT `SubjectNo` FROM `subject`
  WHERE `SubjectName`='高等数学'
)

-- 全部用子查询
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN(
 SELECT StudentNo FROM result WHERE StudentResult>=80 AND `SubjectNO`=(
     SELECT SubjectNo FROM `subject` WHERE `SubjectName` ='高等数学' 
 )
)

/* 
  我们现在学生表里面查询学生的学号与名字,由于student表里面每个学生的学号只有一次所有不用distinct。
  那么只用依靠StudentNo筛选,先从result中筛选分数大于80分的学号,再从subject里面筛选SubjectName='高等数学'的学科编号
*/
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值