MySQL数据库学习基础

测试时不仅要在界面上操作，还要检查数据库中的数据是否正确，从而探索出更深层次问题

比如输入注册信息后显示注册正确，但无法登录。这时需要检查数据库中是否保存了正确的注册信息。

数据库：长期存储在计算机内、有组织的数据集合

他人笔记借鉴：MySQL - 智云知识

安装MySQL(社区版）:

MySQL :: Download MySQL Installer

安装视频：03. 基础-概述-MySQL安装及启动_哔哩哔哩_bilibili

启动：1.终端运行   (要在任意环境的可运行需path环境变量）

mysql [-h 127.0.0.1] [-p 3306] -u root -p   

 

2.客户端运行：

图形化MySQL管理软件：Navicat /SQLyog

下载链接：Navicat | 产品

SQL语言注释：

Navicat中ctrl+/     快速注释选中的SQL

               crtl+shift+/   取消注释

常用的数据类型：

元素单词说明：

数据库（database)

表 （table)

字段【列】（field)

记录【行】（record）

通用语法及分类：增删改查

• DDL: 数据定义语言，用来定义数据库对象（数据库、表、字段），[create表、库]
• DML: 数据操作语言，用来对数据库表中的数据进行增删改，[insert,update,delete]
• DQL: 数据查询语言，用来查询数据库中表的记录，[select]
• DCL: 数据控制语言，用来创建数据库用户、控制数据库的控制权限，[授予grant,撤回revoke]

创建表：

-- 语法：CREATE TABLE 表名（字段名 数据类型，字段名 数据类型）

CREATE TABLE a(NAME VARCHAR(10),height DECIMAL(5,2))

 insert插入记录：

-- INSERT INTO 表名 VALUE()
INSERT INTO b VALUE('zhangfei',123.22)

插入部分字段：

-- 在b表中只插入部分东西
-- INSERT INTO 表名 （字段名，字段名）VALUE（值，值）
INSERT INTO b (name) VALUE('lisi')

一个insert语句插入多条记录：

INSERT INTO b  VALUE('lisi',234.34),('wangwu',235.67)

select查询：

-- SELECT 想要查看的字段名（全部为*） from 表名
SELECT * FROM b;

update修改：

-- UPDATE 表名 SET 字段=值，字段=值 WHERE 条件
UPDATE b SET height=555.28 WHERE name='zhangfei';

UPDATE b SET name='wangjin' WHERE height>300;

delete删除：

-- DELETE FROM 表名 WHERE 条件
DELETE FROM b WHERE name='wangwu';
SELECT * FROM b;

 truncate删除：

-- 全部删除，无法删除部分
TRUNCATE TABLE a;

 删除表：

-- 删除整个表
DROP table a ;

DROP table if exists a;防止已经删除带来的报错

 字段的约束：

主键：被赋予主键的字段不允许值有重复

CREATE TABLE a (
id int UNSIGNED PRIMARY key,
name VARCHAR(10),
age INT);
INSERT into a value(1,'zhangfei',35);
SELECT * FROM a;

 自增长：被约束的字段自动顺序增长

DROP TABLE a;
CREATE TABLE a (
id int UNSIGNED PRIMARY key auto_increment,
name VARCHAR(10),
age INT);
INSERT into a (name,age) value('lisi',30);
-- 插入的时候指定了带自增长的值那就以定义的值往后变化
INSERT into a value(5,'wangwu',30);
INSERT into a (name,age) value('liping',30);
SELECT * FROM a;

主键自增长字段可以用0或者null作为占位符，不发生报错

INSERT into a value(0,'kangxi',50);
INSERT into a value(0,'biji',10);
SELECT * FROM a;

 非空约束：被约束字段不允许记录里有空的

CREATE TABLE e (
id int UNSIGNED ,
name VARCHAR(10) not null,
age INT);

INSERT into e value(5,'wangwu',30);
INSERT into e (int,age) value(2,30);           --此处报错，因为非空约束字段不能为空

SELECT * FROM e;

unique唯一约束 ：

和主键有差不多的意义，字段内记录不能有重复的

default默认约束：

该约束使得不指定约束字段值时该字段值会是建表时的默认值

CREATE TABLE e (
id int UNSIGNED ,
name VARCHAR(10) ,
age INT DEFAULT 30);

查询功能进阶学习：

字段、表的别名：

SELECT name as 姓名, age as 性别 FROM students;

SELECT *  FROM students as stu;

distinct消除重复记录：

SELECT DISTINCT sex FROM students -- 对于select查询后每行重复记录进行筛选

 条件查询：

SELECT * FROM students WHERE studentNo='001'

select紧跟后面的 决定了返回什么字段

select的where语句决定返回什么样的记录

 where后语句允许

比较运算符

逻辑运算符：

and or not

模糊查询 ：

• like
• %代表多个任意字符
• _代表任意单个字符

-- 寻找姓名以孙开头的学生
SELECT * FROM students WHERE name LIKE '孙%'

SELECT * FROM students WHERE name LIKE '孙_'

范围查询：

SELECT * from students WHERE hometown IN ('北京','上海')

SELECT * from students WHERE hometown='北京' or hometown='上海'  --等效，但上一代码更简便

 SELECT * from students where age BETWEEN 25 and 30

SELECT * from students where age not BETWEEN 25 and 30

 空判断：空没有东西，不能用等号判断

SELECT * from students where card IS NULL

SELECT * from students where card IS not NULL

where子句在update与delete中依然有效

排序：asc/desc

-- 将年龄从小到大排序,系统一般以此为默认值
SELECT * from students ORDER BY age ASC
-- 从大到小排序
SELECT * from students ORDER BY age DESC

-- 排序有优先级，先排第一个字段后，再在相同的地方排序下一字段
SELECT * from students ORDER BY age DESC, studentNO

--与where子句连用时，查询所有男同学，按年龄降序排序，年龄相同时按学号升序排

--SELECT * from students WHERE 条件 ORDER BY 排序字段 趋势变化

SELECT * from students WHERE sex='男' ORDER BY age DESC, studentNO

聚合函数 ：

聚合函数不能用在where后面的条件中使用

count：

-- count计算select查询到多少条记录
select COUNT(class) FROM students

max最大值，min最小值：

select MAX(age) FROM students WHERE sex='女'

--查找一个表中年龄最大的人名字SELECT name FROM students WHERE age = (SELECT MAX(age) FROM students);

函数类型	用例
计算select查询到多少条记录【count】	select COUNT(class) FROM students
计算指定字段最大值max、最小值min	select MAX(age) FROM students WHERE sex='女'
指定字段求和sum	select sum(age) FROM students WHERE sex='女'
指定字段求平均avg  求平均时会忽略Null	select avg(age) FROM students WHERE sex='女'

 数据分组：一定配合聚合函数使用

group by：分组

SELECT sex, COUNT(*) FROM students GROUP BY sex

SELECT class, COUNT(*),AVG(age),MAX(age),MIN(age) FROM students GROUP BY class

--练习，统计各班学生总数、平均年龄、最大年龄、最小年龄，但不统计3班，统计结果按照班号从大到小排序

SELECT class, COUNT(*),AVG(age),MAX(age),MIN(age) FROM students WHERE class!='3班' group BY class ORDER BY class DESC

 having：分组聚合后的数据筛选

分组的筛选语句，与where不同，where：先筛选后组才会分好

总是在group by后面

having后面可以使用聚合函数

SELECT class, COUNT(*) FROM students group BY class HAVING class='1班'

where无法做到，例子求班级人数大于3的班级名字

 SELECT class FROM students GROUP BY class HAVING COUNT(*)>3

 数据分页显示：

-- LIMIT 开始行号（从0开始标记） 显示行数
SELECT * FROM students LIMIT 3,3

查询年龄最小同学的名字

SELECT NAME FROM students ORDER BY age LIMIT 1

已知每页记录m行数据，查询第n页的数据

SELECT * FROM students  LIMIT （n-1）*m, m

 连接查询：

内连接		select * from 表1 inner join 表2 on 表1.字段=表2.字段
左连接		select * from 表1 left join 表2 on 表1.字段=表2.字段
右连接		select * from 表1 right join 表2 on 表1.字段=表2.字段

 内连接案例：

SELECT * FROM students INNER join scores ON students.studentNO =scores.studentNo

--利用表的别名

SELECT * FROM students st INNER join scores sc ON st.studentNO =sc.studentNo

--多表内连接

SELECT * FROM students INNER join scores ON students.studentNO =scores.studentNo INNER JOIN courses ON scores.courseNo=courses.courseNo

--多表查询同名字段，即查询结果需显示多表同名字段，需要特殊说明显示那个表的

SELECT students.studentNO FROM students INNER join scores ON students.studentNO =scores.studentNo

写查询SQL三步法：

1.搭框架，如果有多表，连接多表

2.加入where条件语句/分组having

3.确定返回的字段名

自关联：同一张表的连接查询，找到一张表的可关联字段

SELECT * FROM areas a1 INNER JOIN areas a2 on a1.id=a2.pid WHERE a1.name='广东省'

子查询： 嵌入到主查询，可以独立运行

SELECT * FROM students WHERE age>(SELECT AVG(age) FROM students)

--多表

SELECT * FROM scores WHERE studentNO IN (SELECT studentNO FROM students WHERE age=30)

 MYSQL内置函数；

字符串函数

连接字符串concat	concat(参数1，参数2，参数3）
字符串长度length	length(str)
左侧截取字符left	left(字符串，截取数量） 例子：查询所有学生的姓 select left（name，1） from students
右侧截取字符right	right(字符串，截取字符数量）
指定位置截取字符substring	substring（字符串，开始截取位置，截取字符数量）
去除字符串左侧的空格ltrim	ltrim(字符串）
去除字符串左侧的空格rtrim	rtrim(字符串）
去除字符串两侧的空格trim	trim(字符串）
四舍五入round	round(小数，保留小数位数）
随机数rand	产生0-1的浮点数 --随机抽出一个学生 select * from students order by rand() limit 1

时间函数

current_date()	返回当前日期
current_time()	返回当前时间
now()	返回当先时期和时间

 存储过程：

--创建存储过程

CREATE PROCEDURE stu()
BEGIN
  SELECT * FROM students;
END
-- 调用存储过程stu
call stu();
-- 删除存储过程，删除时不用写后面（）
DROP PROCEDURE stu;
DROP PROCEDURE if EXISTS stu;

 视图：对select语句封装

可以认为一个只读的表，不能进行更新和删除

-- 创建视图，查询男生信息，可以像表一样利用视图,视图类似于对select的封装
CREATE VIEW stu_nan AS SELECT * FROM students WHERE sex='男'
-- 删除视图
DROP VIEW stu_nan;

事务：保证数据库数据的一致性

如果没有开始事务，修改都是实时生效的，事务算是一个缓存文档

 

BEGIN;
DELETE FROM students WHERE studentNO='001';
-- 回滚事务，撤回
ROLLBACK;

--提交事务，一旦提交，前面操作立即生效

commit;

如果一个事务没有rollback也没有提交，默认进行rollback，保持原表

索引：带索引后查询效率会高

创建索引：

--create index 索引名称 on 表名（字段名称（长度））  如果指定字段为字符串，需要指定长度，建议长度与定义字符串长度一致

create index name_students on students（name（10））

索引调用：

where子句会自动调用带索引的，不需要额外语句

查看索引：主键字段自带索引

show index from students

删除索引：

drop index age_index on students

 cmd操作mysql:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p      本机可以忽略-h

show databases	查看数据库
use 数据库名	进入某一数据库
show tables	展示数据库下所有表
如果命令行展示的字体不符采用set names gbk
desc 表名	查看一个表的字段结构

 创建和删除数据库：

create database mytest default charset utf8;

drop database mytest；