本文详细介绍了MySQL数据库的基础知识，包括数据库的创建、删除、使用以及表的操作。重点讲解了SQL语言，如DML（数据操作语言）中的插入、更新、删除，以及DQL（数据查询语言）的查询技巧，包括where条件子句、分页和排序。还探讨了数据库的字符集编码、事务处理以及InnoDB引擎的特点。此外，还涉及到了数据库的字段类型、外键和索引等概念，是数据库初学者和开发者的实用教程。
数据库
SQL结构化查询语言
Structured Query Language
RDBMS 指关系型数据库管理系统，全称 Relational Database Management System
1，认识DB数据库
前端：（页面：展示，数据！）
后端： （连接数据库JDBC，连接前端（控制跳转，给前端传数据））
数据库：存数据
程序员三大阶段
- 写代码，学好数据库
- 操作系统，数据结构与算法
- 离散，数电，…
关系型数据库：（SQL）
MySQL, Oracle, Sql Serve, DB2,SQLlite
表与表之间的联系，行列关系
非关系型数据库：（NoSQL）not only SQL
Redis,MongDB
对象存储，
DBMS(数据库管理软件)
- MySQL关系型 数据库管理系统
- 5.7比较稳定
- 尽可能使用压缩包安装，不要用exe
2，mysql操作
启动：
net start mysql
mysql -u root -p
关闭：
exit
net stop mysql
修改密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
刷新权限
flush privileges;
安装详见：https://mp.weixin.qq.com/s/E1PM4EHwU6Joot4OG0gDjw
安装SQLyog
可视化软件
下载链接
https://sqlyog.en.softonic.com/
MySQL基本命令行语句
mysql -u root -p123457 --链接数据库（--+注释）
-------------
语句使用:结尾
show databases: --查看所有的数据库
use school --切换数据库
show tables; --查看数据库所有的表
describe student; --查看表所欲数据
create database westos; -- 创建一个数据库
exit; --推出链接
--单行注释
/*
多行注释
多行注释
*/
数据库XXX语言 CRUD增删改查
DDL 定义
DML 操作
DQL 查询
DCL 控制
操作数据库（了解）
数据库>表>操作数据库中表的数据
不区分大小写
-
创建数据库
create database if not exists mingzi -
删除数据库
drop database [if exists] mingzi; -
使用数据库
--tab上边的·特殊关键字· show `user`; -
查看数据库
show databases --查看所有数据库
数据库的列类型
数值
- tinyint 1字节
- smallint 2字节
- mediumint 3字节
- int 标准的整数 4个字节
- bigint 8字节
- float 浮点数 4字节
- double 浮点数 8字节
- decimal 字符串形式的浮点数，金融计算时候，一般使用decimal
字符串
- char 字符串固定的大小2—255
- varchar 可变字符串0—65535 常用string
- 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到现在的毫秒数
- year年份表示
null
- 没有值，未知
- 不使用NULL进行运算，结果为NULL
数据库的字段类型（重要）
Unsigned
- 无符号的整数
- 不能为负数
zerodill
- 0填充
- 不足的位数用0填充
自增
- 自动在上一条记录上+1
- 通常使用设计唯一的主键-index ，必须是整数类型
- 自定义设计主键自增的起始值和步长
非空
- 假设设置为not null ,如果不赋值，就会报错
- Null，如果不填写值，默认是null
默认
- 设置默认的值
- sex,默认值为男，不指定该列的值，填充默认值
/*每一个表都必须存在以下字符段*/
id 主键
·version· 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update x
创建数据库表
--创建一个school数据库
--创建学生表，用sql创建
-- 学号int 密码varchar 姓名,性别varchar 出生日期，家庭住址，Email
--用英文符号() .``
--字符串用单引号
语句后加英文 ,最后一个不用加
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 '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
格式
CREAT TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE school --查看数据库的定义语句
SHOW CREATE TABLE student --查看数据表的定义语句
DESC student --显示表的结构
数据表类型
--关于数据库引擎
/*
INNODB默认使用
MYISAM早些年使用
*/
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | |
| 表空间大小 | 较小 | 较大，约为两倍 |
常规使用操作：
- MYISAM节约空间，速度快
- INNODB安全性高，事务处理，多表 多用户操作
物理空间存在位置
所有数据库文件在data目录下
本质还是文件存储
MYSQL引擎在物理文件上的区别
- innoDB在数据库表中只有一个*.frm文件，以及上级目录的ibdata1文件
- MYISAM对应文件
- *。frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据库表的字符集编码
CHARSET=utf8
不设置的话就是mysql默认的字符集编码（不支持中文）
MySQL的默认编码是Latin1，不支持中文，
打开my.ini以后，在[client]和[mysqld]下面均加上default-character-set=utf8
https://www.cnblogs.com/sailleaf/p/4704261.html
在my.ini配置默认编码
character-set-server=utf8
修改删除表
--修改表名
ALTER TABLE student RENAME AS student1
--增加表的字段
ALTER TABLE student1 ADD age INT(11)
--修改表的字段（重命名，修改约束）
ALTER TABLE student1 MODIFY age VARCHAR(11)--修改约束
ALTER TABLE student1 CHANGE age age1 INT(1)--段重命名
--除表的字段
ALTER TABLE student1 DROP age1
--删除表(如果存在再删除)
DROP TABLE IF EXISTS 表名
创建和删除尽量都是用判断IF EXISTS
3,MySQL数据管理
3.0，基础补充
- InnoDB，是MySQL的数据库引擎之一，现为MySQL的默认存储引擎，为[MySQL AB](https://baike.baidu.com/item/MySQL AB)发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务
3.1，外键（了解）
改变表，外部键啥都有
数据库级别的外键，不用
数据库纯粹论
- 单纯的表，只用来存（行）数据，（列）字段
- 如果想实现多张表的数据，使用外键（程序实现）
3.2DML语言
数据库意义：数据存储，数据管理
DML语言：数据操作语言
- Insert
- update
- delete
3.3 添加
--入语句
--insert INTO ([字段一,字段二,字段三])VALUES('值1')('值2'),('值3'),....
INSERT INTO `grade`(`gradename`) VALUES('大四')--设置的有自增
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES('3','大四')--一一对应
--插入多个字段
INSERT INTO `grade`(`gradename`)VALUES('大二'),('大一')
3.4 修改
--修改学员名字
UPDATE `student1` SET `name`='景之' WHERE id = 1;
--修改多个，逗号隔开
UPDATE `student1` SET `name`='景之',`email`='wang' WHERE id = 1;
-不指定条件改变所有
UPDATE `student1` SET `name`='景之';
条件：where子句 id等于，大于，…区间内修改
BETWEEN 2 AND 5(闭合区间)
AND
OR
多条件定位语句，条件无上限
也可以通过修改表的数据修改而增加一列
UPDATE `student` SET `birthday`=`CURRENT_TIME WHERE` `name`='哈哈哈' AND sex='女'
3.5 删除
--删除指 定数据
DELETE FROM `student1` WHERE id = 1;
TRUNCATE 命令
作用： 完全清空一个数据库表，表的结构和索引约束不会边！
--清空—表
TRUNCATE student1
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`) VALUES('1'),('23'),('34') DELETE FROM `test` --不会影响自增 `school` TRUNCATE `test` --自增会清零
4.DQL查询数据（重重点）
4.1DQL
data query language:查询语言
select
4.2 指定查询字段
--查询所有学生
SELECT * FROM student
--查询指定字段
SELECT `studentno` , `studentname` FROM student
--别名
SELECT `studentno` AS 学号 , `studentname` AS 学生姓名 FROM student AS s
--函数 CONCAT(a,b)
SELECT CONCAT('姓名:' , StudentName) AS 新名字 FROM student
列名字看不懂时，可以起别名
去重，distinct
作用：去除select查询出来的重复数据，重复数据只显示一条
--查询那些同学参加了考试，成绩
SELECT * FROM result
--查询那些同学参加了考试
SELECT `StudentNo` FROM result
--去重
SELECT DISTINCT `StudentNo` FROM result
数据库的列（表达式）
--查询系统版本号(函数)
SELECT VERSION()
--计算结果（表达式）
SELECT 100*7-33 AS 计算结果
--查询自增步长（变量）
SELECT @@auto_increment_increment
--学员考试成绩+1
SELECT `StudentNo`, `StudentResult`+1 AS '提分后' FROM result
数据库的表达式，文本值，列，变量，null，函数，计算表达式
4.3 where 条件子句
作用：检索数据中符合条件的值
搜索的条件由一个或多个表达式组成，结果为布尔值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a && b | 逻辑与 |
| or || | a or b a || b | 逻辑或 |
| Not ! | not a ! a | 逻辑非 |
尽量使用英文字母
--where
SELECT studentNo,`StudentResult` FROM result
--查询分数在95到100的
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100
--&&
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 && StudentResult<=100
--模糊查询（区间）
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 50 AND 100
--除了学号为1000的其他学生
SELECT studentNo,`StudentResult` FROM result
WHERE StudentNo!=1000;
--!= NOT
SELECT studentNo,`StudentResult` FROM result
WHERE NOT StudentNo = 1000;
模糊查询 ： 比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| 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…其中的某一个值中，结果为真 |
--in（具体的一个或者多个值）
--查询，1001，1000号同学
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE StudentNo IN (1000,1001);
--查询在北京的同学
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE `Address` IN ('北京海淀');
--null NOT NULL
--查询地址为空的学生 NULL
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE address='' OR address IS NULL
--查询有出生日期的学生
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE `borndate` IS NOT NULL
--查询没有出生日期的学生
SELECT `StudentNo` ,`StudentName` FROM `student`
WHERE `borndate` IS NULL
4.4 连表查询
--查询参加了考试的同学，（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result
/*思路
1.需求，分析查询的字段来自那些表（连接查询）
2.确定使用那种查询方式7种
确定交叉点
判断的条件，学生表中的studentNo = 成绩表studentNo
*/
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo = r.studentNo
--Right JOIN
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.studentNo = r.studentNo
--Left JOIN
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNo = r.studentNo
| 操作 | 描述 |
|---|---|
| inner join | 如果表中有一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
自链接及连表查询联系
自链接（了解）
自己的表和自己的表链接 核心：一张表拆成两张表
父类
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps设计 |
查询父类对应子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
4.5 分页和排序
升序ASC 降序DESC
order by 通多那个字段排序
分页
缓冲压力，体验 更好，现在是瀑布流
-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='Java程序设计-1'
--order by 名字 方式（ASC DESC）排序方式
ORDER BY studentresult DESC
--LIMIT起分页效果
LIMIT 0,2
子查询
--子查询（查询数据结构-1的所有考试结果）学号，科目编号，成绩，降序
--方法一，连接查询
SELECT studentno,r.subjectno,studentresult
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '数据结构-1'
ORDER BY studentresult DESC;
--方法二，比较简洁吧
SELECT studentno,r.subjectno,studentresult
FROM result r
WHERE subjectno = (
SELECT subjectno FROM `subject`
WHERE subjectname = '数据结构-1'
)
ORDER BY studentresult DESC;
5.MySQL函数
常用函数
数据函数
现在是8.0的文档了
https://dev.mysql.com/doc/refman/8.0/en/
SELECT ABS(-9);--绝对值
SELECT CEILING(9.4);--向上取整
SELECT FLOOR(7.7);--向下取整
SELECT RAND();--随机数，0-1之间
SELECT SIGN(7);--负数返回-1，正数返回1，0返回0
字符串函数
SELECT CHAR_LENGTH('王景玉大帅哥，子杰太牛逼，佳鹏很能卷，我要平复心情')；--字符串长度
SELECT CONCAT('我'，'最帅呀');--合并字符串
SELECT INSERT('我爱柳暗花明又一村',3,9,'少年行不终，景自到');--替换，从第几个，替换n个
SELECT LOWER ('WANGJINGYU');--小写
SELECT UPPER ('wangjingyu');--大写
SELECT RIGHT('wodes,hijieshipiaoliangme,ilide',5);--从右边往左边截取n个
SELECT LEFT('wodes,hijieshipiaoliangme,ilide',5);--从左边往右边截取n个
SELECT REPLACE('景之说效率能成功','效率','效率加努力');--替换，后换前
SELECT SUBSTR('景之说效率能成功',4,6);-- 从第几个开始截取长度为n个
SELECT REVERSE('景之说效率能成功');--反转字符串
--查询姓文的同学，改成景
SELECT REPLACE(studentname,'文','景') AS 新名字
FROM student WHERE studentname LIKE '文%';
日期和时间函数
--日期和时间函数
SELECT CURRENT_DATE();--当前日期
SELECT CURDATE();--获取当前日期
SELECT NOW();--获取当前日期和时间
SELECT LOCALTIME();--获取当前日期和时间
SELECT SYSDATE();--获取当前日期和时间
--年月日，时分秒
SELECT YEAR(NOW());--年份
SELECT MONTH(NOW());--月份
SELECT DAY(NOW());--日份
SELECT HOUR(NOW());--时
SELECT MINUTE(NOW());--分
SELECT SECOND(NOW());--秒
系统函数
--系统信息函数
SELECT VERSION();--版本
SELECT USER();--用户
聚合函数
| 函数 | 描述 |
|---|---|
| COUNT() | 统计出现满足条件的个数 |
| SUM（） | 求和（表达式，字段） |
| AVG（） | 平均值（表达式，字段） |
| MAX() | 最大值 |
| MIN（） | 最小值 |
--聚合函数
SELECT COUNT(studentname)FROM student;
SELECT COUNT(*)FROM student;
SELECT COUNT(1) FROM student;
--含义上，count(1)和count(*)都表示对全部数据查询
--count(字段) 会统计，该字段在表中出现的次数，忽略字段为null的
--count(1)和count(*)速度都差不多（对全表扫描），但是count(字段)比较快，只对该字段扫描
/*
对比
1.表没有主键时，count(1)比count(*)块
2.有主键时，count(主键)效率最高
3.若表格只有一个字段，则count(*)效率最高
*/
SELECT SUM(studentresult)AS 总和 FROM result;
SELECT AVG(studentresult)AS 平均分 FROM result;
SELECT MAX(studentresult)AS 最高分 FROM result;
SELECT MIN(studentresult)AS 最低分 FROM result;
分组过滤
--查询不同课程的最高分，最低分
--根据不同的课程分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,
MIN(studentresult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;
--having 也是一种筛选，是从前面筛选的字段再筛选
MD5加密
--实现数据加密
CREATE TABLE `testmd5`(
`id` INT (4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO testmd5 VALUES(1,'kuangshen','123456'),(2,'qinjiang','456789')
--d对pwd加密
UPDATE testmd5 SET pwd = MD5(pwd);
--单独对某个用户加密,不过是加了一个where条件而已，懂得都懂
UPDATE testmd5 SET pwd = MD5(pwd) WHERE NAME = 'wangjingyu';
--插入新的数据自动加密
INSERT INTO testmd5 VALUES(4,'jingzhi',MD5('1234'));
--查询登录用户信息（md5对比使用，看用户输入加密后的密码对比）说实话，这句话没看懂，
--感觉就是普通的查询加了个条件而已
SELECT * FROM testmd5 WHERE `name` = 'jingzhi' AND pwd=MD5('1234');
6.事务和索引
事务：同时成功，同时失败
-
将一组SQL语句放在同一批次内去执行
-
如果一个SQL语句出错，则该批次内所有SQL都将被取消执行
-
MySQL事务处理只支持InnoDB和BDB数据表类型
事务原则：ACID原则，原子性，一致性，隔离性，持久性（脏读，幻读）
（理解中。。）
参考来源：kuangshen
扫一扫
1680
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
