浅谈MySQL
MySQL
1.初识MySQL
JavaEE：企业家java开发 Web
前端（页面：展示，数据！）
后台（连接点：连接数据库JDBC，链接前端（控制，控制视图跳转和给前端传递数据））
数据库（存数据，Txt，Excel，Word）
1.1为什么学习数据库
1.岗位需求
2.现在的世界，大数据时代~，得数据库者得天下
3.被迫需求：存数据
4.数据库是所有软件体系中最核心的存在
1.2什么是数据库
数据库（DB，DataBase）
概念：数据仓库，软件，安装在操作系统（Window，linux，mac，…）之上
作用：存储数据，管理数据
1.3数据库分类
关系型数据库： （SQL）
MySQL，Oracle，Sql Server，DB2，SQLite
通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表，…
非关系型数据库： （NoSQL）Not Only
Redis, MongDB
非关系型数据库，对象存储，通过对象的自身属性来决定。
DBMS（数据库管理系统）
只是一个数据库的管理软件，科学有效的管理我们的数据，维护和获取数据；
MySQL，数据管理系统！
1.4MySQL简介
MySQL是一个关系型数据库管理系统
前世：瑞典MySQL AB 公司
今生：属于 Oracle 旗下产品
MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
开源的数据库软件
1.5SQLyog
1.新建一个数据库school；
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GheC5Sqk-1605413388227)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\image-20201024092052331.png)]
每一个SQLyog的执行操作，本质都是对应了一个sql，可以在历史记录中找到
2.创建一个新表student
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oSN8SY5U-1605413388229)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\image-20201024092417269.png)]
创建内容
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hy9oNuHc-1605413388231)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\image-20201024092819703.png)]
1.6连接数据库
命令行连接
mysql -u root -pxuanjie
“mysql -u root -p”是执行语句“xuanjie”是密码
alter user user() identified by "密码"; --修改密码
flush privileges; --刷新权限
所有语句都要“；”结尾
show databases;
use school; --切换数据库
Database changed
show tables; --查看当前库中所有的表
describe student; --显示表中所有的数据
create database cd; --创建数据库“cd”
exit； --退出连接
数据库 xxx 语言
DDL 定义
DML 操作
DQL 查询
DCL 控制
2.操作数据库
操作数据库->操作数据库中的表->操作数据库中表的数据
MySQL不区分大小写
2.1操作数据库
1.创建数据库
CREATE DATABASE (IF NOT EXISTS) school;
2.删除数据库
DROP DATABASE (IF EXISTS) schoo;
3.使用数据库
USE school;
4.查看所有的数据库
SHOW DATABASES;
2.2数据库的列类型
数值
tinyint 十分小的数据 1个字节
smallint 较小的数据 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
时间日期
java.util.Date
date YYYY-MM-DD 日期格式
time HH：mm：ss 时间格式
datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
timestamp 时间戳，1970.1.1到现在的毫秒数
NULL
没有值，未知
不要使用NULL进行运算，结果位NULL
2.3数据库的字段属性
Unsigned：
无符号的整数；
该列不能位负数；
zerofill：
不足的位数使用0来填充
5–>005
自动增量：
自动在上一条记录的基础上+1（默认，可更改）；
通常用来设计唯一的主键 index，必须是整数类型；
可以自定义设计主键自增的起始值和步长；
非空：
设置非空以后，必须添加值否则报错；
默认：
不主动添加值，会自动生成值；
2.4创建数据库的表
NOT NULL：非空
AUTO_INCREMENT：自动增量
COMMENT’ '：注释
DEFAULT’ '：默认值
DATETIME：时间日期格式
PRIMARY KEY( )：主键
ENGINE=INNODB：引擎
CHARSET=utf8：字符集
CREATE TABLE jiaoxue(
id INT(10) NOT NULL AUTO_INCREMENT COMMENT'学号',
`name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT'姓名',
pwd VARCHAR(20) NOT NULL DEFAULT'1' COMMENT'密码',
sex VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
birthday DATETIME DEFAULT NULL COMMENT'出生日期',
address VARCHAR(200) DEFAULT NULL COMMENT'家庭住址',
email VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8
2.5数据表的类型
| 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
show create database school; --查看创建数据库的语句
show create table student; --查看创建student的语句
desc student; --相当于“describe student”查询表
2.6修改删除表
修改表
--修改表名：alter table 旧表名 rename as 新表名;
alter table jiaoxue rename as guanli;
--增加表的字段：alter table 表名 add 属性名 字符类型;
alter table student add sex varchar(3);
--修改表的属性：
--alter table 表名 modify 属性名 字符类型;修改为新的属性
alter table student modify sex int(10);
--alter table 表名 change 旧表名 新名字 字符类型;
alter table student change sex gender varchar(10);
--删除表的属性：alter table 表名 drop 字段名;
alter table student drop gender;
删除表
--drop table 表名;
drop table student;
3.MySQL数据管理
3.1外键
3.2DML语言
数据库意义：数据存储，数据管理
DML语言：数据操作语言
insert；
update；
delete；
3.3添加
--insert into 表名（'属性','属性','属性'） values('对应值','对应值','对应值'),('对应值','对应值','对应值');
--当添加所有属性值时，表名后面的属性可以省略
insert guanli values(0153,'xuanjie',123456,'男','2001-11-28','shouxian','296088132@qq.com');
3.4修改
--update 表 set 属性 = '***' where 限制条件;
update student set name = 'xuanjie' where id = 2019;
修改的值还可以是变量；
限制条件可以不存在，也可以很复杂，当其不存在时则全部修改；
3.5删除
--delete from 表名 where 限制条件;
delete from student where id = 2021;
--完全清空一个数据库表，表的结构和索引约束都不会变！
truncate student;
delete不添加限制条件时和truncate都会清空表。
delete from student --不会影响自增
truncate from student --会清空自增
4.DQL查询数据
4.1DQL
（Data Query Language：数据查询语言）
- 所有的查询操作都用它，select
- 简单的查询，复杂的查询它都能做
- 数据库中最核心的语言，最重要的语句
- 使用频率最高的语句
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_count0FFSET offset}];
-- 指定查询的记录从哪条到哪条
4.2指定查询字段
--查询全部的学生 select 字段 from 表;
select * from student;
--查询指定字段
select studentno,studentname from student;
--给查询结果赋予新名字 as ...as...
select StudentNo as 学号,StudentName as 学生姓名 from student;
--函数 concat（a，b）连接a和b
select concat('姓名：',studentname) as 新名字 from student;
去重 distinct
作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条
select * from result; --查询所有结果
select studentno from result; --查询studentno结果
select distinct studentno from result; --查询studentno不重复的结果
数据库的列（表达式）
select version(); --查询系统版本
select 100*3-1 as 计算结果; --可以用来计算
select @@auto_increment_increment; --查询自增步长
--studentresult查询的结果+1，并将其命名为加分
select studentno,studentresult+1 as 加分 from result;
数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量…
4.3where条件子句
作用：检索数据中符合条件的值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 与，两个都为真，结果为真 |
| or || | a or b a||b | 或，其中一个为真，结果为真 |
| not ！ | not a ！a | 非，真为假，假为真 |
select studentno,studentresult from result
-> where studentresult>=95 and studentresult<=100;
以此类推…
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果操作符为NULL，结果为真 |
| is not null | 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…其中的某一个值中，结果为真 |
--like %（代表有任意个字符） _（代表一个字符）
> select studentno,studentname from student
-> where studentname like "%大%";
+-----------+-------------+
| studentno | studentname |
+-----------+-------------+
| 1002 | 张大炮 |
+-----------+-------------+
> select studentno,studentname from student
-> where studentname like "大_";
Empty set (0.00 sec)
--in(里面的内容必须是精确的，不能含有%和_) 表示在一个范围内
> select studentno,studentname from student
-> where studentno in (1000,1001);
+-----------+-------------+
| studentno | studentname |
+-----------+-------------+
| 1000 | 张伟 |
| 1001 | 赵强 |
+-----------+-------------+
--NULL
> select studentno,studentname from student
-> where sex is null;
+-----------+-------------+
| studentno | studentname |
+-----------+-------------+
| 1001 | 赵强 |
+-----------+-------------+
> select studentno,studentname from student
-> where sex is not null;
+-----------+-------------+
| studentno | studentname |
+-----------+-------------+
| 1000 | 张伟 |
| 1002 | 张大炮 |
+-----------+-------------+
4.4联表查询
join查询
--inner join(获取两表的交集部分，两表都包含的才会显示)
> select s.studentno,studentname,subjectno,studentresult
-> from student as s
-> inner join result as r
-> on s.studentno = r.studentno;
+-----------+-------------+-----------+---------------+
| studentno | studentname | subjectno | studentresult |
+-----------+-------------+-----------+---------------+
| 1000 | 张伟 | 1 | 85 |
| 1000 | 张伟 | 2 | 70 |
| 1000 | 张伟 | 3 | 68 |
| 1000 | 张伟 | 4 | 98 |
| 1000 | 张伟 | 5 | 58 |
+-----------+-------------+-----------+---------------+
--right join(以右表为基础，获取信息。即右表信息全部写上，左表只写满足on条件的信息)
> select s.studentno,studentname,subjectno,studentresult
-> from student as s
-> right join result as r
-> on s.studentno = r.studentno;
+-----------+-------------+-----------+---------------+
| studentno | studentname | subjectno | studentresult |
+-----------+-------------+-----------+---------------+
| 1000 | 张伟 | 1 | 85 |
| 1000 | 张伟 | 2 | 70 |
| 1000 | 张伟 | 3 | 68 |
| 1000 | 张伟 | 4 | 98 |
| 1000 | 张伟 | 5 | 58 |
+-----------+-------------+-----------+---------------+
--left join(以左表为基础，获取信息。即左表信息全部写上，右表只写满足on条件的信息)
> select s.studentno,studentname,subjectno,studentresult
-> from student as s
-> left join result as r
-> on s.studentno = r.studentno;
+-----------+-------------+-----------+---------------+
| studentno | studentname | subjectno | studentresult |
+-----------+-------------+-----------+---------------+
| 1000 | 张伟 | 1 | 85 |
| 1000 | 张伟 | 2 | 70 |
| 1000 | 张伟 | 3 | 68 |
| 1000 | 张伟 | 4 | 98 |
| 1000 | 张伟 | 5 | 58 |
| 1001 | 赵强 | NULL | NULL |
| 1002 | 张大炮 | NULL | NULL |
+-----------+-------------+-----------+---------------+
自连接：
自连接事实上需要在建表的时候将其字段设置的相互呼应，这样才能自连接。
自连接：一张表拆成两张表使用
+------------+-----+--------------+
| categoryid | pid | categoryName |
+------------+-----+--------------+
| 2 | 1 | 信息技术 |
| 3 | 1 | 软件开发 |
| 4 | 3 | 数据库 |
| 5 | 1 | 美术设计 |
| 6 | 3 | web开发 |
| 7 | 5 | PS技术 |
| 8 | 2 | 办公信息 |
+------------+-----+--------------+
可以将上表划分父类和子类：
父类：
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类：
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | 美术设计 |
这里你就可以看出来，父类表中的categoryid和子类表中的pid相对应。
通过这样的一个对应，就可以实现自连接。
> select a.categoryname,b.categoryname
-> from category as a,category as b
-> where a.categoryid = b.pid;
+--------------+--------------+
| categoryname | categoryname |
+--------------+--------------+
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | PS技术 |
| 信息技术 | 办公信息 |
+--------------+--------------+
4.5分页和排序
排序
-- 排序： 升序 ASC，降序DESC
-- ORDER BY 通过哪个字段排序，怎么排
> select * from result
-> order by studentresult desc;
+-----------+-----------+---------------------+---------------+
| studentno | subjectno | examdate | studentresult |
+-----------+-----------+---------------------+---------------+
| 1000 | 4 | 2013-11-13 16:00:00 | 98 |
| 1000 | 1 | 2013-11-11 16:00:00 | 85 |
| 1000 | 2 | 2013-11-12 16:00:00 | 70 |
| 1000 | 3 | 2013-11-11 09:00:00 | 68 |
| 1000 | 5 | 2013-11-14 16:00:00 | 58 |
+-----------+-----------+---------------------+---------------+
-- desc降序 asc升序
分页
-- 分页：limit n，m
-- n是该表起始位置，m是显示多少个值
> select * from result
-> order by studentresult desc
-> limit 0,2;
4.6子查询
查询高等数学考试成绩大于80同学的学号和姓名
-- 方法1：
SELECT DISTINCT s.studentno,studentname
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.`studentno`
INNER JOIN SUBJECT AS sub
ON r.subjectno = sub.subjectno
WHERE subjectname LIKE "高等%" AND studentresult >= 80
-- 方法2：（子查询）
SELECT studentno,studentname
FROM student
WHERE studentno IN(
SELECT studentno
FROM result
WHERE studentresult>80 AND subjectno IN (
SELECT subjectno
FROM SUBJECT
WHERE subjectname LIKE "高等%"
)
)
4.7分组与过滤
GROUP BY：
根据一个或多个列对结果集进行分组。会把值相同放到一个组中，最终查询出的结果只会显示组中一条记录。
一般配套聚合函数，group by用于规定函数的作用规范。
如当前例子的GROUP BY r.subjectno，就是subjectno相同的分数在一起算一次avg（）
GROUP_CONCAT()会把隐藏的内容展示出来。这是个函数，应该这么用：
SELECT GROUP_CONCAT(username),GROUP_CONCAT(age),sex FROM testusers GROUP BY sex
HAVING：
having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。
就是再多一层筛选，只不过是靠后的。
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result AS r
INNER JOIN SUBJECT AS sub
ON r.subjectno = sub.`subjectno`
GROUP BY r.subjectno
HAVING 平均分>80
+-------------+---------+--------+--------+
| subjectname | 平均分 | 最高分 | 最低分 |
+-------------+---------+--------+--------+
| 高等数学-1 | 85.0000 | 85 | 85 |
| 高等数学-2 | 70.0000 | 70 | 70 |
| 高等数学-3 | 68.0000 | 68 | 68 |
| 高等数学-4 | 98.0000 | 98 | 98 |
| C语言-1 | 58.0000 | 58 | 58 |
+-------------+---------+--------+--------+
5.MySQL函数
5.1常用函数
select abs(-9); --绝对值
select ceiling(9.4); --向上取整
select floor(9.4); --向下取整
select rand(); --返回一个 0~1 之间的随机数
select sign(10); --判断正负数，正数返回1，负数返回-1
select char_length('我爱'); --返回字符串长度
+---------------------+
| char_length('我爱') |
+---------------------+
| 2 |
+---------------------+
select concat('你是','阿瑟大声地','阿萨德'); --拼接字符串
+--------------------------------------+
| concat('你是','阿瑟大声地','阿萨德') |
+--------------------------------------+
| 你是阿瑟大声地阿萨德 |
+--------------------------------------+
select insert ('我爱变成打撒打撒',2,5,'啦啦'); --从第2个字符开始，将后面的5个字符替换成“啦啦”
+----------------------------------------+
| insert ('我爱变成打撒打撒',2,5,'啦啦') |
+----------------------------------------+
| 我啦啦打撒 |
+----------------------------------------+
select lower('WWW'); --改为小写
+--------------+
| lower('WWW') |
+--------------+
| www |
+--------------+
select upper('wWw'); --改为大写
+--------------+
| upper('wWw') |
+--------------+
| WWW |
+--------------+
select instr('xuanjie','a'); --返回第一次出现“a”的位置
+----------------------+
| instr('xuanjie','a') |
+----------------------+
| 3 |
+----------------------+
select replace('xuanjielala','lala','niubi'); --将“lala”替换成“niubi”
+---------------------------------------+
| replace('xuanjielala','lala','niubi') |
+---------------------------------------+
| xuanjieniubi |
+---------------------------------------+
select substr('许安杰将来会走向哪里？',4,2); --返回第4个字符后面的2个字符（包括第4个字符）
+--------------------------------------+
| substr('许安杰将来会走向哪里？',4,2) |
+--------------------------------------+
| 将来 |
+--------------------------------------+
select reverse('我是谁'); --反转
+-------------------+
| reverse('我是谁') |
+-------------------+
| 谁是我 |
+-------------------+
select current_date(); --当前时间
select curdate();
+------------+
| 2020-10-31 |
+------------+
select now(); --当前时间
select localtime();
select sysdate();
+---------------------+
| 2020-10-31 10:37:24 |
+---------------------+
select year(now());
+-------------+
| 2020 |
+-------------+
select month(now());
+--------------+
| 10 |
+--------------+
select day(now());
+------------+
| 31 |
+------------+
select hour(now());
+-------------+
| 10 |
+-------------+
select minute(now());
+---------------+
| 39 |
+---------------+
select second(now());
+---------------+
| 14 |
+---------------+
select system_user();
select user();
+----------------+
| root@localhost |
+----------------+
select version();
+-----------+
| version() |
+-----------+
| 8.0.19 |
+-----------+
5.2聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| count（） | 计数 |
| sum（） | 求和 |
| avg（） | 平均值 |
| max（） | 最大值 |
| min（） | 最小值 |
| … | … |
例子：
--count(studentname)会忽略null值，count（*）不会忽略null值
select count(studentname) from student;
+--------------------+
| count(studentname) |
+--------------------+
| 3 |
+--------------------+
select count(*) from student;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
select sum(studentresult) as 总和 from result;
+------+
| 总和 |
+------+
| 379 |
+------+
select avg(studentresult) as 平均分 from result;
+---------+
| 平均分 |
+---------+
| 75.8000 |
+---------+
select max(studentresult) as 最高分 from result;
+--------+
| 最高分 |
+--------+
| 98 |
+--------+
select min(studentresult) as 最低分 from result;
+--------+
| 最低分 |
+--------+
| 58 |
+--------+
3584
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
