MySql基础知识
1、Mysql概念
数据库：database :db
存储数据的文件系统
按指定的数据结构把数据持久化存储到硬盘：按制定方式对数据进行增删改查：create delete update read:crud
数据库管理员：database Administrator :dba
对数据库进行维护管
数据库管理系统：database manager system :dbms
对数据库进行管理和维护的软件
关系型数据库： relational database manager system :rdbms
以二维表(行和列)的模式来存储数据
2、数据库模式
- 网状模型 ：数据与数据之间有比较复杂的关系
每一个数据用一个节点表示，每个节点与其他节点都有联系，这样数据库中的所有数据节点就构成了一个复杂的网络。
缺点：查询和删除比较麻烦：使用场景比较单一
- 层次模型：数据之间有严格上下级关系
以树形结构来组织数据
缺点：不能适用于所有场景
- 关系型数据库：以二维表的格式描述数据
行：一条记录：对应的是java的对象
列：一个字段：对应的是java的属性
3、常见的dbms软件
- rdbms
Oracle:甲骨文公司：(java)
Mysql:甲骨文公司：
sql server:Microsoft微软：
Access:Microsoft微软：
IB2: IBM
sybase:赛贝斯
- nosql:非关系型数据库
redis:键值对模型
mangodb:文档模型(bson)
hbase:列簇模型
Neo4J:图形模型
- dbms的作用
数据定义功能可定义数据库中的数据对象。
数据操纵功能可对数据库表进行基本操作，如插入、删除、修改、查询。
数据的完整性检查功能保证用户输入的数据应满足相应的约束条件。
数据库的安全保护功能保证只有赋予权限的用户才能访问数据库中的数据。
数据库的并发控制功能使多个应用程序可在同一时刻并发地访问数据库的数据。
数据库系统的故障恢复功能使数据库运行出现故障时进行数据库恢复，以保证数据库可靠运行。
数据定义：定义表结构（列名+列类型）
数据操作：crud
数据约束：对数据的完整性进行检查
权限控制：对数据库用户的数据访问和使用权限进行控制
并发控制：数据库创建多个连接/数据事务控制
数据恢复：数据库故障时数据库数据的恢复
4、安装mysql
4.1、查看是否已经安装了mysql
4.2、安装mysql
-
选择标准安装
-
记录安装位置
- 详细安装
- 开发者
- 多功能
- 连接数
- 端口
-
编码集：utf-8
-
服务名称
- 密码
- 等待安装
- 配置环境变量
-
查看服务
-
测试
启动命令行：windows+r
输入：mysql -uroot -p
任何输入：密码(root)
4.3、卸载mysql
- 关闭服务
- 卸载软件
-
删除安装文件夹
-
删除数据文件夹
-
开启注册表
-
删除注册项
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\EventLog\Application\MySQL
- 重启电脑
4.4、启动客户端
- 通过dos命令行启动
- mysql自带客户端
- sqlyog
4.5、sqlyog的使用
- 修改字体大小
5、rdbms的数据结构
数据库：database:一个数据库软件可以有多个database
一般：一个项目对应一个数据库
表：table:一个二维表
表：由表结构+表记录
表结构：列名+列类型
表记录：行数据
数据库中的表 在java中对应的是类
记录：行数据：每行记录的数据个数 取决于表结构
表记录：在java中对应的是对象
字段：列
6、sql
sql：Structured Query Language:结构化查询语言
RDBMS数据库的通用语言：
客户/程序通过给数据库软件发送sql语句 来对数据库解析crud
sql按是否标准分类：
sql标准：所有的rdbms都识别的语句
sql方言：只适用于当前rdbms
sql按作用分类：
ddl:data defined language: 数据定义：对表和表结构的增删改查
关键字：create drop show
dql:data query language: 数据查询：对表记录的查询
关键字：select
dml:data manipulation language:数据操作：对表记录的增删改
关键字：delete update insert
dcl:data control language :权限控制：对数据库用户的权限控制
关键字：grant revoke
tcl:transaction control language:事务控制：
关键字：commit rollback
7、语法
1: 语句结束--分号
2: 关键字不区分大小写--建议大写
3：mysql中双引号和单引号都表示字符串
4：mysql中注释：和java块注释一样
建议：通过换行和缩进 对sql语句格式化
8、数据类型
- 整数类型
1：int ：4B
2：integer：4B
3：tinyint：1B
4: bigint: 8B
字节、字、位、比特，这四者之间的关系是：
1位=1比特
1字=2字节
1字节=8位
1字=16位
- 字符串
1:char: 固定长度 char(n) n表示字符个数
数据不够n个字符用空格字符填充
2:varchar:可变长度 varchar(n) n表示字符最大个数
3: text: 64kb
4: longtext: 4GB
- 字节型
1：binary:255B
2：blob:64kB
- 浮点类型
1：float:小数点后有效位数：6-8
float(n,m) m是小数位数 n-m为整数位数
2：double:小数点后有效位数：12
double(n,m) m是小数位数 n-m为整数位数
3: decimal:用法和float和double相同 专用于表示钱相关的数字
- 布尔类型
1 boolean:
2 bool
- 日期类型
date:yyyy-MM-dd
time:HH:mm:ss
datetime:yyyy-MM-dd HH:mm:ss
timestamp:时间戳（毫秒值）
9、DDL:数据定义：对表结构的增删改查
--与database相关的数据定义：ddl
show databases;/*显示所有的数据库*/
create database db_java37 charset='utf8'; /*创建数据库并指定编码集*/
use db_java37;/*使用数据库*/
show variables like "%char%";/*显示数据库相关的编码集合*/
drop database db_java37;/*删除数据库*/
show tables;/*显示数据库下有哪些表*/
--与table相关的数据定义：ddl
create table tab_1(
id int,
age int
);/*创建表*/
drop table tab_1;
create table tab_1(
tid int comment "编号",
tname varchar(4) comment "名字",
tsex char(1) comment "性别",
tage tinyint comment "年龄",
tshow text comment "自我介绍",
tdy boolean comment "是否党员",
tmoney decimal comment "钱：decimal",
theight float(4,1) comment "身高：单位厘米 可以取到毫米",
tbirthday date comment "生日：没有时分秒",
tin datetime comment "注册时间：精确到秒",
tphoto longblob comment "照片"
);
/*
注意事项：
1 列格式： 列名 列类型 约束
2 列结束：,
3 最后一列不加,
*/
SHOW TABLES;/*显示当前数据库的所有表*/
desc tab_1;/*查询表结构*/
DESC tab_37;/*查询表结构*/
show create table tab_1;/*显示创建表的结构语句*/
/*对表结构的增删改统一前缀：alter table 表名*/
alter table tab_1 rename to tab_37;/*修改表名*/
alter table tab_37 add taddress varchar(100) comment "地址";/*添加列*/
ALTER TABLE tab_37 drop taddress;/*删除列*/
ALTER TABLE tab_37 change tin tcomein datetime;/*修改列名*/
ALTER TABLE tab_37 change tcomein tcomein time;/*修改列类型*/
ALTER TABLE tab_37 modify tcomein datetime;/*修改列类型*/
10、dml:数据操作：对表记录的增删改
- 准备工作
--dml:对表记录的增删改
CREATE TABLE IF NOT EXISTS tab_1(
tname VARCHAR(4) COMMENT "名字",
tsex CHAR(1) COMMENT "性别",
tage TINYINT COMMENT "年龄",
tdy BOOLEAN COMMENT "是否党员",
tmoney DECIMAL COMMENT "钱：decimal",
theight FLOAT(4,1) COMMENT "身高：单位厘米 可以取到毫米",
tbirthday DATE COMMENT "生日：没有时分秒",
tin DATETIME COMMENT "注册时间：精确到秒",
tphoto LONGBLOB COMMENT "照片"
);
- 插入数据
ALTER TABLE tab_1 MODIFY tmoney DECIMAL(6,2);
INSERT INTO tab_1 VALUES("韩梅梅","女",18,TRUE,19.1,165.1,
"1990-11-12","1990-11-12 13:14:15",NULL);/*插入记录*/
INSERT INTO tab_1 VALUES("韩雪","女",38,TRUE,19.1,166.1,
"1970-11-12","1991-11-12 13:14:15",
LOAD_FILE("C:/Users/Administrator/Desktop/imgs/1.jpg"));/*插入记录*/
--mysql支持一次插入多条记录
INSERT INTO tab_1 VALUES("韩红","女",32,TRUE,19.1,166.1,
"1970-11-12","1991-11-12 13:14:15",
LOAD_FILE("C:/Users/Administrator/Desktop/imgs/1.jpg")),
("韩庚","男",28,TRUE,19.1,166.1,
"1970-11-12","1991-11-12 13:14:15",
LOAD_FILE("C:/Users/Administrator/Desktop/imgs/1.jpg"));
--插入指定列：未指定的列值为NULL
INSERT INTO tab_1(tname,tsex,tbirthday,tphoto)
VALUES("韩红","女","1970-11-12",
LOAD_FILE("C:/Users/Administrator/Desktop/imgs/1.jpg"));
- 查询记录
SELECT * FROM tab_1;/*查询所有记录的所有列*/
- 删除记录
DELETE FROM tab_1;/*删除所有记录*/
TRUNCATE tab_1;/*删除所有记录*/
/*
delete和truncate的区别
相同：都可以用于删除整个表记录
不同之处：1 DELETE后面可以加where条件实现删除指定记录
TRUNCATE只能删除所有记录
2 DELETE是dml语句：可以加事务 实现回滚
TRUNCATE是ddl语句：不能加事务 不能回滚
3 DELETE是逐行删除
TRUNCATE直接删除表的数据文件
*/
DELETE FROM tab_1 WHERE tsex="女";/*删除满足条件的记录*/
DELETE FROM tab_1 WHERE tage>11;
DELETE FROM tab_1 WHERE tage<=11;
DELETE FROM tab_1 WHERE tage>11 AND tsex='女';/*两个条件都必须满足*/
DELETE FROM tab_1 WHERE tage>11 OR tsex='女';/*成立一个条件即可*/
- 修改记录
UPDATE tab_1 SET tsex='妖'; /*修改所有行的tsex列的值*/
UPDATE tab_1 SET tsex='女' WHERE tage>10;/*修改所有满足条件行的tsex列的值*/
UPDATE tab_1 SET tsex='妖',tname="无名氏",tage=tage+1;/*修改多列*/
SELECT * FROM tab_1;
CREATE DATABASE db_java37_2 CHARSET='utf8';
11、函数
11.1、数学函数
/*求绝对值：abs(n)*/
SELECT ABS(-1),ABS(11.1);
/*近似值：*/
SELECT FLOOR(11.4),FLOOR(11.5),FLOOR(11.6),FLOOR(12.5),FLOOR(12.6),FLOOR(12.4);
SELECT CEIL(11.4),CEIL(11.5),CEIL(11.6),CEIL(12.5),CEIL(12.6),CEIL(12.4);
SELECT ROUND(11.4),ROUND(11.5),ROUND(11.6),ROUND(12.5),ROUND(12.6),ROUND(12.4);
SELECT ROUND(11.4789,2),ROUND(11.4789,1),ROUND(11.4789,3);/*四舍五入保留指定位小数*/
SELECT TRUNCATE(11.4789,2),TRUNCATE(11.4789,1),TRUNCATE(11.4789,3);/*直接截取保留指定位小数*/
/*常量*/
SELECT PI();
/*幂运算*/
SELECT SQRT(12),POW(3,2);
/*求余数:mod(n,m)*/
SELECT 1+1,1-1,1*1,1/2,MOD(4,5);
/*随机：rand() [0,1)*/
SELECT RAND();
11.2、字符串函数
/*LENGTH获取字节个数，CHAR_LENGTH获取字符个数*/
SELECT LENGTH("abcdefghijkh呵呵"),CHAR_LENGTH("abcdefghijk呵呵");
/*字符串拼接*/
SELECT CONCAT("1","2"),"1"+"2";
SELECT CONCAT("1","2","a",1);
/*字符串转换:去除空格*/
SELECT CONCAT("--",TRIM(" 123 "),"---");
SELECT CONCAT("--",LTRIM(" 123 "),"---");
SELECT CONCAT("--",RTRIM(" 123 "),"---");
/*字符串转换:大小写转换*/
SELECT LCASE("123abcANBC"),UCASE("123abcANBC");
/*字符串转换:反转*/
SELECT REVERSE("123abcANBC");
/*截取字串*/
SELECT SUBSTR("abcdefghijk",3);/*下标从1开始：一个参数为起始索引*/
SELECT SUBSTR("abcdefghijk",3,6);/*3为起始索引 6为字串长度*/
SELECT SUBSTRING("abcdefghijk",3,6);
SELECT SUBSTRING("abcdefghijk",3);
11.3、日期 函数
/*获取当前时间*/
SELECT CURRENT_DATE(),CURDATE(); /*年月日*/
SELECT CURRENT_TIME(),CURTIME();/*时分秒*/
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();/*年月日时分秒*/
/*时间运算*/
SELECT NOW()-1,NOW();/*秒数-1*/
SELECT ADDDATE(NOW(),1),NOW();/*天数+1*/
SELECT DATEDIFF(NOW(),"2021-09-11");/*日期比较 返回的是差的天数*/
/*获取时间参数*/
SELECT YEAR(NOW()),MONTH(NOW()),DAYOFMONTH(NOW()),DAYOFWEEK(NOW()),
HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
SELECT EXTRACT(YEAR FROM NOW()),EXTRACT(MONTH FROM NOW()),
EXTRACT(DAY FROM NOW()),EXTRACT(HOUR FROM NOW()),
EXTRACT(MINUTE FROM NOW()),EXTRACT(SECOND FROM NOW()));
11.4、加密
/*加密函数*/
SELECT MD5("anabc");
SELECT PASSWORD("anabc");
/*字符串随机*/
SELECT UUID();/*随机字符串32的16进制*/
SELECT SUBSTR(UUID(),1,4);/*随机一个4个字符的字符串*/
11.5、其他函数
SELECT IF(1>2,"额呵呵","不呵呵");
SELECT *,CASE tsex WHEN '男' THEN '帅哥' WHEN '女' THEN '美女' ELSE '妖精' END
FROM tab_1;
12、作业
--作业2：1 创建数据库db_1 编码集为gbk
DROP DATABASE db_1;
CREATE DATABASE IF NOT EXISTS db_1 CHARSET="gbk";
--2 查看与db_1相关的编码集
USE db_1;
SHOW VARIABLES LIKE "%char%";
--3 修改db_1编码集为utf8
ALTER DATABASE db_1 CHARSET="utf8";
--4 使用数据库db_1
USE db_1;
--5 创建表student: 名字 性别 年龄 是否党员 照片 分数 饭卡钱数 生日
CREATE TABLE student(
sname VARCHAR(10),
sex CHAR(1),
sage INT,
sdy BOOLEAN,
sphoto BLOB,
score FLOAT(4,1),
smoney DECIMAL(9,1),
sbirthday DATE
);
--6 修改列名和列类型
ALTER TABLE student CHANGE smoney sfk FLOAT(4,1);
--7 删除饭卡钱数
ALTER TABLE student DROP sfk;
ALTER TABLE student CHANGE sphoto sphoto LONGBLOB;
--8 添加10条记录
DESC student;
INSERT INTO student(
sname,
sex,
sage,
sdy,
sphoto,
score,
sbirthday) VALUES(
SUBSTR(UUID(),1,4),
IF(RAND()>0.5,"男","女"),
TRUNCATE(RAND()*8+18,0),
RAND()>0.5,
LOAD_FILE(CONCAT("C:/Users/Administrator/Desktop/imgs/",(TRUNCATE(RAND()*7+1,0)),".jpg")),
TRUNCATE(RAND()*100,1),
CONCAT((TRUNCATE(RAND()*8+1996,0)),"-",(TRUNCATE(RAND()*12+1,0)),"-",(TRUNCATE(RAND()*31+1,0)))
);
SELECT * FROM student;
DELETE FROM student WHERE sphoto IS NULL;
TRUNCATE student;
SELECT TRUNCATE(3.12345,1),RAND()>0.5;
--9 修改女生的分数+1
UPDATE student SET score=score+1 WHERE sex="女";
--10 修改所有学生名字后面加"同学"两个字
UPDATE student SET sname=CONCAT(sname,"学生");
UPDATE student SET sname=SUBSTR(UUID(),1,4);
--11 删除分数少于30的学生记录
DELETE FROM student WHERE score < 30;
--12 查询所有记录
