MySQL基础笔记（基于Navicat操作）

1、初识MySQL

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统(RDBMS)之一。

学习视频网址：MySQL学习视频

1.1 为什么学数据库

数据库从大一就开始学，从开始的SQL Server到Oracle再到MySQL，但我学到的感觉都是皮毛，过不了多久就全忘了，这次重新再来学习，所以做笔记真的很重要。

到了后面做项目的时候，发现基本上就是在做数据库CRUD增删改查，我估计着今后毕业到公司估计也是几乎都在在做CRUD了，所以好数据库至关重要啊。

数据库是所有软件体系中最核心的存在

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

1.2 数据库分类

关系型数据库：(SQL)

• MySQL，SQL Server，Oracle，DB2，SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储。

非关系型数据库：(NoSQL) No Only

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

DBMS(数据库管理系统)

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

2、使用Navicat操作MySQL

2.1 连接localhost

我也不知道为什么，我电脑安装的软件总是出现错误，今天一打开Navicat连接数据库的时候爆出下面错误：2003

这里说我无法连接到MySQL，那可能是MySQl服务没有打开，于是打开服务找到MySQL将其打开就好

今天刚用了电脑管家清理内存，看来是那时候关掉的，果然要慎用这些软件

然后点亮localhost即可

也可以使用命令行操作

我也是第一次使用命令行操作MySQl，结果发现我竟然没有搭建环境变量？？？

1、找到我们的mysql安装路径下的bin目录，复制地址

2、打开系统环境变量，编辑Path

3、插入路径即可

4、打开cmd，输入mysql -V 看看是否成功

命令行连接

mysql -uroot -p123 --连接数据库（123是我的密码）
show databases;   --查看所有数据库,有个;不要漏了
use handsome  --切换数据库  use 数据库名
show tables;  --查看数据库中所有的表
describe handsome;  --显示数据库中所有表信息
create database westos; --创建一个数据库
exit; --退出连接

2.2 创建一个数据库

字符集：utf8

排序规则：utf8_general_ci

2.3 新建一个表

建表还是鼠标操作舒服，使用sql语句是真的长和麻烦，不过还是要掌握命令行

选中表右键点击对象信息，打开DDL可以查看SQL语句

之后就是往表里填写信息了，相信这个大家都会

2.4使用sql语句操作数据库(了解)

1、创建数据库

CREATE DATABASE SCHOOL
CREATE DATABASE IF NOT EXISTS SCHOOL --IF NOT EXISTS 加了一个判断表示不存在

2、删除数据库

DROP DATABASE SCHOOL
DROP DATABASE IF EXISTS SCHOOL  --IF  EXISTS  表示如果存在

3、使用数据库

--如果表名或字段是一个特殊符号，就需要带''，例如user
USE 'SCHOOL' --切换数据库

4、查看数据库

SHOW DATABASES 

Navicat可以通过Shift+Ctrl+R实现选中执行

2.5数据库列的类型

数值

类型	大小	字节
tinyint	十分小的数据	1
smalint	较小的数据	2
mediumint	中等大小	3
int	标准的整数	4
bigint	较大的数据	8
float	浮点数	4
double	浮点数	8（精度问题）
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	yyyy	年份

java.util.Date

null

• 没有值，未知
• 注意，不要使用null进行运算，结果为null

2.6 数据库的字段属性（重点）

Unsigned：

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

zerofill：

• 0填充
• 不足的位数，用0来填充，int（3），5—> 005

自增:

• 自动在上一条记录的基础上+1（默认）
• 通常用来设计唯一的主键，~index，必须是整数类型
• 可以自定义设置主键自增的起始值和步长

非空 null or no null

• 不填入数值就会报错
  

默认

• 设置默认的之值！
• sex，默认为男，如果不指定该列的值，则会有默认的值！
  

拓展

/*
*未来做项目中，每一个表必须存在以下五个字段！！！

id 主键
version  乐观锁
is_delete  伪删除（逻辑删除）
gmt_create 创建时间
gmt_update 修改时间
*/

2.7 sql创建数据库表（重点）

sql语句

-- 注意点，使用英文（），表名和字段不是''(单引号，而是``
-- AUTO INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有语句后面加, （英文的），最后一个不用加
-- PRIMARY KEY 主键，一班一个表就一个唯一的主键
CREATE TABLE IF NOT EXISTS `students` (
	`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

我发现我的表建成功了，但是底下面报了个[Err] 1055的错误

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

于是就上百度查了一下，输入以下命令可以解决

select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

我也不知道为什么，以后再说！！

用sql建表真的是很麻烦，而且一不小心就会会报错，但是还是要懂的操作

格式

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

常用命令

有时候我们想用sql创建表但是不知道如何写，可以用以下：

SHOW CREATE DATABASE handsome  -- 查看创建数据库语句
SHOW CREATE TABLE students  -- 查看表的定义语句
DESC students -- 显示表的结构

还有一种方法：

2.8 数据表的类型（了解）

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/

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

常规使用操作：

• MYISAM 节省空间，速度较快
• INNODB 安全高，事务处理，多表多用户操作

在物理空间存在的位置

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

本质还是文件的存储

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

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

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

CHARSET=UTF8

不设置的话，会是mysql默认的字符集编码~ （不支持中文！）

MySQL的默认编码是Latin1 ，不支持中文‘

在my.ini 中配置默认的编码(不推荐)

character-set-server=utf8

2.9 修改删除表

修改

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE students RENAME AS student1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE students ADD age INT(11)

-- 修改表的字段 
-- ALTER TABLE 表名 MODIFY 字段名 列属性（）
ALTER TABLE students MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 MODIFY 旧字段名 新字段名 列属性（）
ALTER TABLE students CHANGE age age1 int(1) -- 字段重命名 + 修改约束

-- 删除表的字段： ALTER TABLE 表名 drop 字段名
ALTER TABLE students drop age1

删除表

-- 删除表（IF EXISTS 如果表存在再删除）
DROP TABLES IF EXISTS 表名

注意点：

• `` 字段名，使用这个包裹 ，不是’'
• 注释 – /* */
• sql关键字大小写不敏感，建议大家写小写
• 所有的符号用英文！！！

3、MySQL数据管理

3.1 外键（了解即可）

很少会用到，懒得敲了

方式一：在建表的时候创建外键

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），在删除被引用的表（主表）

方式二：建表后再创建外键

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

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键（程序去实现）

3.2 DML语言 （全部记住）

数据库操作语言：

• insert 添加
• update 修改
• delete 删除

3.3 添加

-- 由于主键id自增我们可以省略（如果不写表的字段啊，他就会一一对应，就要添加所有的字段名）
INSERT INTO `student` VALUES ('张三',20)  --  报错，没有写id的数值

-- 一般插入语句，我们一定要数据和字段相对应，顺序一致
INSERT INTO `student`(name,age) VALUES ('张三',20)

-- 插入多个字段
INSERT INTO `student`(name,age) VALUES ('张四',20),('张五',20)

-- 赋值int不用加‘’，担心会出错可以都加，字段也是，加``
INSERT INTO `student`(`name`,`age`) VALUES ('张四','20'),('张五','20')

语法：INSERT INTO 表名 (字段1，字段2) VALUES ('值1',值2)

3.4 修改

-- 修改学员名字，
UPDATE `student` SET `name`='张三' where id =1
-- 不指定的情况下会修改所有的字段，谨慎！！！
UPDATE student SET `name`='水'
-- 修改多个字段,好隔开
UPDATE `student` SET `name`='里斯' , age =22 where id=2
-- 通过过条件定位数据
UPDATE `student` set `name`='李四' where id=3 and age=20

语法：UPDATE 表名 set 列名1=value ，列名2=value where 条件

条件：where 子句 运算符 id 等于某个值，大于某个值，再某个区间内修改

value可以是一个具体的值，也可以是一个变量

ALTER TABLE student ADD birthday date -- 插入一个生日字段
UPDATE student set birthday =CURRENT_TIME where id =1 -- CURRENT_TIME当前时间

操作符会返回 布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<> 或 ！=	不等于	5<>6	true
>	大于	5>6	false
<	小于	5<6	true
between …and…	再某个范围	[ ]区间
and	我和你 &&	5>1 and 1>2	false
or	我或你 ||	5>1 or 1>2	true

注意：

• 数据库的列，尽量带上``
• 条件如果没有指定会修改所有列
• 多个设置的属性之间，使用英文逗号隔开

3.5 删除

-- 清空一个表
DELETE FROM student
-- 删除指定数据
DELETE FROM student where id =15

-- 也是清空一个表
TRUNCATE TABLE students

delete 和 TRUNCATE 的区别

• 相同点：都能删除数据，但不会删除表结构
• 不同：
  • TRUNCATE 重新设置自增列 ，计数器会归零
  • TRUNCATE 不会影响事务

了解即可：DELETE删除的问题，重启数据库的现象：

• INNODB 自增列会从1开始 （存在内春中，断电即失）
• MYISAM 继续上一个自增开始（存在文件中，不会丢失）

4、DQL查询数据（重点）

4.1 、DQL

（Data Query LANGUAGE：数据查询语句）

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

4.2、 指定查询字段

-- 查询全部的学生 
select * from student

-- 查询指定字段
select `studentNo` ,`StudentName· from student

-- 给字段或表起个别名 as
select `studentNo` as 学号 ,`StudentName` as 学生姓名 from student as s

--函数 CONCAT（a,b)
select CONCAT('姓名：', StudentName) As 新名字 from student 

语法： select 字段 from 表名

去重 distinct

作用：去除select查询出来的结构中重复的数据，重复的数据只显示一条

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

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量

语法： select 表达式 from 表

4.3、where条件子句

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

逻辑运算符

运算符	语法	描述
and &&	A and B A && B	与
or ||	A or B A || B	或
Not ！	not A ! A	非

-- 查询考试成绩在95~100之间
select studenNo , StudentResult from result 
where StudentResult > 95 and StudentResult <= 100

select studenNo , StudentResult from result 
where StudentResult > 95 && StudentResult <= 100

-- 模糊查询（区间）
select studenNo , StudentResult from result 
where StudentResult between 95 and 100

-- 查询除了学号1000意外的同学成绩
select studenNo , StudentResult from result 
where studenNo!=1000

select studenNo , StudentResult from result 
where not studenNo=1000

模糊查询：比较运算符

运算符	语法	描述
is null	A is null	如果操作符为Null，结果为真
is not ull	A is not null	如果操作符不为Null，结果为真
between	A between B and C	若A在B和C之间，则结果为真
Like	A like B	SQL匹配，如果A匹配B，则结果为真
In	A in （A1，A2，A3）	假设a 在a1，a2，a3中某一值中，结果为真

-- 查询姓刘的同学
select studenNo , StudentName from student 
where StudentName like '刘%'

-- 查询姓刘的同学，后面一个字
select studenNo , StudentName from student 
where StudentName like '刘_'

-- 查询姓刘的同学，后面两个字
select studenNo , StudentName from student 
where StudentName like '刘__'

-- 查询名字中带‘嘉’的同学
select studenNo,StudentName from student 
where StudentName like '%嘉%'

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

--查询地址为空的学生
select studenNo,StudentName from student 
where address='' or address is null

4.4、 联表查询

join 比较

操作	描述
Inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即时右表中没有匹配
right join	会从右表中返回所有的值，即时左表中没有匹配

思路

• 分析需求，分析查询的字段来自那些表（连接查询）
• 确定使用哪种连接？
• 确认交叉点（这两种表中那些数据是相同的）
• 判断条件： 学生表中的 studentNo = 成绩表 studentNo

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
select s.studentNo,syudentName,SubjectNo,StudentResult
from student as s
inner join result  as r 
on s.studentNo=r.studentNo

-- 左连接
select s.studentNo,syudentName,SubjectNo,StudentResult
from student as s
left join result  as r 
on s.studentNo=r.studentNo

-- 右连接
select s.studentNo,syudentName,SubjectNo,StudentResult
from student as s
right join result  as r 
on s.studentNo=r.studentNo

-- 查询缺考的人
select s.studentNo,syudentName,SubjectNo,StudentResult
from student as s
left join result  as r 
on s.studentNo=r.studentNo
where StudentResult is null

-- 三表查询（学号，学生姓名，科目，分数）
select s.studentNo,syudentName,SubjectNo,StudentResult
from student as s
right join result  as r 
on s.studentNo=r.studentNo
inner join subject sub
on r.SubjectNo =sub.SubjectNo

--  我要查询哪些数据 select ...
-- 从那几个表查   from 表 ...join 连接的表  on  交叉条件
-- 如果是多表查询，先查询两张在慢慢增加

自连接

自己的表和自己的表连接。

核心：一张表拆违两张一样的表即可

有这样一张表，pid与categoryid是父子关系：

我们将其拆成两张表：

查询父子信息：

select a.categoryname as 父栏目 , b.categoryname as 子栏目
from  category as a , category as b
where a.categoryid = b.pid

排序与分页

排序 (ORDER BY)： 升序 ASC ， 降序 DESC

-- 对所有学生按学号升序排序
select * from student 
order by studentNo ASC

语法：order by 字段 ASC/DESC

分页（limit)

select * from student 
order by studentNo ASC
limit 0,5

-- 第一页 limit 0,5  
-- 第一页 limit 5,5  
-- 第一页 limit 10,5  
-- 第 N 页 limit （n-1)*5,5  

语法：limit 查询起始下标 ， pageSize

公式：(n-1) * pageSize , pageSize

n : 当前页

pageSize：页面大小

(n-1) * pageSize： 起始值

数据总数 / 页面大小 =总页数

4.6、 子查询

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

-- 1、查询数据库结构-1 的所有考试结果（学号，科目编号，成绩） ，降序排列
-- 方式一： 使用连接查询
select StudentNo , SubjectName , StudentResult 
from result r
inner join sybhect sub
on  r.SubjectNo= sub.SubjectNo
where SubjectName = '数据库结构-1'
order by StudentResult DESC

-- 方式二：使用子查询
select StudentNo , SubjectName , StudentResult 
from result 
where SubjectNo = 
(Select subjectNo from subject where SubjectName='数据库结构-1')
order by StudentResult DESC

-- 三表查询
select StudentNo , SubjectName from student where StudentNo in(
	select StudentNo from resuly where StudentResult>80 and SubjectNo=(
    	select StudentNo from subject where StudentName='高等数学-2'))

4.7、函数

常用函数

聚合函数

-- 查询有多少条记录 count()
select count(*) from student;  -- 不会忽略null值，本质：计算行数
select count('BornDate') from student; -- 会忽略所有的null值
select count(1) from student;  -- 不会忽略所有的null值

select SUM('StudentResult') as 总和 from result
select AVG('StudentResult') as 平均分 from result
select MAX('StudentResult') as 最高分 from result
select MIN('StudentResult') as 最低分 from result

分组和排序

-- 查询不用课程的平均分，最高分，最低分，平均分大于80
select SubjectName, AVG(StudentResult) as 平均分,MAX(StudentResult),MIN(StudentResult)
from result r
inner join `subject` sub
on r.SubjectNo=sub.SubjectNo
GROUP BY r.SubjectNo  -- 通过什么来分组
HAVING 平均分 > 80 -- 用于分组后的过滤，写在（group by )后面

4.8、数据库级别的MD5加密（扩展）

什么是MD5？

主要增强算法复杂度和不可逆性

实例

创建一张表

插入数据

加密操作：

update testmd5 set pwd=MD5(pwd) where id =1
update testmd5 set pwd=MD5(pwd)  -- 加密全部的密码

-- 插入的时候加密
insert into testmd5 values(4,'xiaoming',MD5('123456'))

-- 如何校验 ： 将用户传递进来的密码，进行md5加密，然后对比加密后得到值
select * from testmd5 where name = 'xiaoming' and pwd = MD5('123456')

4.9 、小结

select完整的语法：

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
      [left | right | inner | join table_references2] -- 联表查询
    [WHERE where_condition]  -- 指定结果需满足的条件
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]] -- 按字段进行分组
    [HAVING where_condition] -- 过滤分组的记录必须满足的次要条件
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]  --指定一个或多个条件排序
    [LIMIT {[offset,] row_count | row_count OFFSET offset}] -- 分页，指定查询那几条记录
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED] 
      | LOCK IN SHARE MODE]
    [into_option]

注意先后的顺序

5、事务

什么是事务?

要么都成功，要么都失败

举例：转账

事务原则：ACID

• 原子性（Atomicity）
  • 要么都成功，要么都失败
• 一致性（Consistency）
  • 事务前后的数据完整性要保证一致
• 隔离性（Durability）
  • 多个用户并发访问数据库是，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰
• 持久性（lsolation）
  • 事务一旦提交则不可逆，被持久化到数据库中

执行事务

6、索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

6.1、索引的分类

• 主键索引（PRIMAKY KEY）
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（UNIQUE KEY）
  • 避免重复的列出现，唯一索引可以重复，多个列都标识为唯一索引
• 常规索引（KEY/INDEX)
  • 默认的，index，key关键字来设置
• 全文索引（FullText）
  • 在特定的数据库下才有，MyISAM
  • 快速定位数据

6.2、 索引的使用

-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增加一个全文索引 (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName` (`studentName`);

-- 给一个表的字段添加索引(id_表名_字段)
create index id_app_user_name on app_user('name');

-- EXPLAIN 分析sql执行的状况
EXPLAIN select * from student; -- 非全文索引

测试索引

在有100万条数据的表中执行查询

6.3、索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据的表不需要加索引
• 索引一般加在常用来查询的字段上（id，name）

7、权限管理和备份

7.1、用户管理

用户信息都存在mysql数据库中的user表中：

7.2、使用sql命令

7.3、MySQL备份

然后选择要保存的位置即可，想要导入数据就点击上面的运行SQL文件

可以使用cmd命令操作：