MySQL数据库基础01 韩顺平自学笔记

心向阳光的天域

已于 2022-12-13 17:08:25 修改

阅读量1.7k

点赞数 9

分类专栏：数据库文章标签：数据库 mysql java

于 2022-11-03 13:46:09 首次发布

本文链接：https://blog.csdn.net/sinat_38316216/article/details/127614753

版权

数据库专栏收录该内容

5 篇文章 2 订阅

订阅专栏

MySQL数据库基础

数据库简单原理图
- 连接Mysql
- - 启动和关闭mysql服务
  - 连接Mysql指令
数据库三层结构
数据在数据库中的存储方式
SQL语句的分类
简单通过Java操作Mysql数据库
数据库操作
- 创建数据库
- 查看、删除数据库
- 备份和恢复数据库
- 课后练习
表操作
- 创建表
Mysql常用数据类型
数值型的基本使用
- 数值型(整型)的基本使用
- 数值型(bit)的基本使用
- 数值型(小数)的基本使用
字符串的基本使用
- 字符串的使用细节
日期类的基本使用
课后练习
修改表
- 修改表的基本介绍
- 课后作业
数据库CRUD语句
插入字段
- 插入语句的细节
修改字段
- 修改语句的基本操作
- 修改字段的注意细节
删除字段
- 删除语句细节
查询语句
- 注意事项
- - 课堂练习
- 注意事项2
- where子句经常使用的运算符
- - 课后练习
  - - 课后练习1
    - 课后练习2
- order by 子句
- 合计/统计函数
- 字符串函数
- 数学函数
- 时间日期相关函数
- 加密函数
- 流程控制函数

数据库简单原理图

连接Mysql

启动和关闭mysql服务

管理员
启动服务

连接Mysql指令

数据库三层结构

所谓安装Mysql数据库，就是在主机安装一个数据库管理系统(DBMS),这个管理程序可以管理多个数据库。DBMS(database manage system)
一个数据库中可以创建多个表，用以保存数据(信息)。
数据库管理系统(DBMS)、数据库和表的关系如图所示:示意图。

数据库三层结构

数据在数据库中的存储方式

SQL语句的分类

DDL 数据定义语句 create 表、库
DML 数据操作语句(增加、修改、删除)
DQL 数据查询语句select
DCL 数据控制语句(管理数据库：比如用户权限grant , revoke)

简单通过Java操作Mysql数据库

简单通过Java操作Mysql数据库
添加jar包：mysql-connector-java-5.1.37-bin.jar
代码示例:

package com.mysql;

import org.junit.Test;

import java.sql.*;

/**
 * @author wty
 * @date 2022/10/31 18:17
 */
public class JavaMysql {
    @Test
    public void JavaMysqlTest() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db02", "root", "wty");
        //String sql = "create table goods ( id int, name varchar(32), price double, introduce text)" ;
        //String sql = "insert into goods values(1, '华为手机', 2000, '这是不错的一款手机')" ;
        String sql = "drop table goods" ;

        //得到statement对象，把sql 语法发送给mysql执行
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);

        //关闭连接
        statement.close();
        connection.close();
        System.out.println("成功~");

    }
}

数据库操作

创建数据库

# 创建数据库
# 1.创建一个名称为db_01的数据库(图形化+指令)
# 使用指令创建和删除数据库
DROP DATABASE db_01;

# 这里什么也不写，字符集默认是utf-8 数据库排序规则是utf-8 bin 区分大小写
CREATE DATABASE db_01;

# 2.创建一个utf-8字符集的db_02数据库
CREATE DATABASE db_02 CHARACTER SET utf8;
# 3.创建一个使用utf-8字符集，并带校对规则的db_03数据库
CREATE DATABASE db_03 CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE db_04 CHARACTER SET utf8 COLLATE utf8_bin;
#utf8_general_ci 不区分大小写，可以查询出来2个
SELECT * FROM db_03.t2 WHERE NAME = 'tom';
#utf8_bin 区分大小写只能查询出来1个
SELECT * FROM db_04.t1 WHERE NAME = 'tom';

查看、删除数据库

#演示删除和查询数据库
#查看当前数据库服务器中的所有数据库
SHOW DATABASES;

#查看前面创建的数据库的定义信息
# 在创建数据库、表的时候，为了规避关键字可以用``解决
SHOW CREATE DATABASE db_01;
# 反引号的引用
CREATE DATABASE `CREATE`;

#删除前面创建的数据库(慎用)
DROP DATABASE `CREATE`;

备份和恢复数据库

备份数据库

mysqldump -u root -p -B db_02 db_03 > e:\\bak.sql

备份数据库

恢复数据库

# 备份 要在Dos下执行mysqldump指令其实是mysql安装目录下
DROP DATABASE db_02;
DROP DATABASE db_03;

# 恢复数据库
source e:\\bak.sql;

## 恢复方法2
把bak.sql里的sql语句执行一下

备份恢复数据库的表

mysqldump -u root -p  db_03 t2 > e:\\db_02.sql;

在这里插入图片描述

课后练习

表操作

创建表

# 指令创建表
CREATE TABLE db_02.user_bf 
(id INT,
 `name` VARCHAR(255),
 `password` VARCHAR(255),
 `birthday` DATE) CHARACTER SET utf8
 COLLATE utf8_bin ENGINE INNODB;

Mysql常用数据类型

数值型的基本使用

数值型(整型)的基本使用

说明:使用规范:在能够满足需求的情况下，尽量选择占用空间小的类型。
定义无符号的整数

# 以tinyint来演示范围 
# 有符号-128~127
# 无符号0~255
# 表的字符集和校验规则，存储引擎是默认的
# 没有指定unsinged，则tinyint就是有符号的

CREATE TABLE db_02.t3(
id TINYINT
);

INSERT INTO db_02.t3(id) VALUE(
-129
);
#错误代码： 1264
#Out of range value for column 'id' at row 1

SELECT * FROM db_02.t3;

INSERT INTO db_02.t3(id) VALUES(
-128
);

INSERT INTO db_02.t3(id) VALUES(
127
);

# 创建无符号的整型字段
CREATE TABLE db_02.t4(
id TINYINT UNSIGNED
);

INSERT INTO db_02.t4 VALUES(
256);
#错误代码： 1264
#Out of range value for column 'id' at row 1

INSERT INTO db_02.t4 VALUES(
255);


SELECT * FROM db_02.`t4`;

数值型(bit)的基本使用

# 演示bit类型的使用 num bit(m) m的大小是1-64
CREATE TABLE t05 (num BIT(8));
# 添加数据 范围: 按照你给定的位数如果m=8 则是0~255
# 显示是按照bit

INSERT INTO t05 VALUES(1);

SELECT * FROM t05;

INSERT INTO t05 VALUES(3);
INSERT INTO t05 VALUES(255);

#查询时候，可以按照数来查询
SELECT * FROM t05 a
WHERE a.`num` = 1;

数值型(小数)的基本使用

数值型小数

#演示decimal类型、float、double使用
#创建表
CREATE TABLE db_02.t06 
(num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20),
num4 DECIMAL);#默认是10位，小数后0位

#添加数据
INSERT INTO db_02.`t06`
VALUES(88.12345678912345,
88.12345678912345,
88.12345678912345,
88.12345678912345);

#decimal可以存放很大的数字
CREATE TABLE t07
(num1 DECIMAL(65));

INSERT INTO t07 VALUES(230597714867813463622352353243344425235);


SELECT * FROM t07;

字符串的基本使用

# 演示字符串类型使用char varchar
# 注释快捷键 shift + ctrl + c
# 取消注释快捷键 shift + ctrl + r

-- char(size)
-- 固定长度字符串 最大255字符
-- varchar(size) 0~65535
-- 可变长度字符串 最大65532字节 utf8编码 最大21844字符 1~3字节用于记录大小
CREATE TABLE t08(`name` CHAR(256));


#错误代码： 1074
#Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

CREATE TABLE t08(`name` CHAR(255));


CREATE TABLE t09(`name` VARCHAR(21846));

-- 错误代码： 1074
-- Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
# 这里算的是错的，应该是(65535-3)/3 = 21844
CREATE TABLE t09(`name` VARCHAR(21844));

# 如果表的编码是GBK，应该是(65535-3)/2 = 32766
CREATE TABLE t11(`name` VARCHAR(32767)) CHARACTER SET gbk;
-- 错误代码： 1118
-- Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
CREATE TABLE t10(`name` VARCHAR(32766)) CHARACTER SET gbk;

字符串的使用细节

使用细节

细节4

#演示字符串类型使用的细节

# char(4)表示4个字符数(最大255)，不管中文还是字母只能放4个，按字符计算
CREATE TABLE t11(`name` CHAR(4));
INSERT INTO t11 VALUES('abcde');

-- 错误代码： 1406
-- Data too long for column 'name' at row 1

-- 超长了，缩减一个即可
INSERT INTO t11 VALUES('abcd');

INSERT INTO t11 VALUES('今天星期四');

-- 错误代码： 1406
-- Data too long for column 'name' at row 1
INSERT INTO t11 VALUES('今天周四');

SELECT * FROM t11;

# varchar(4)这个4表示字符数,按照编码存放数据
CREATE TABLE t12(`name` VARCHAR(4));

INSERT INTO t12 VALUES('今天星期四');
-- 错误代码： 1406
-- Data too long for column 'name' at row 1
INSERT INTO t12 VALUES('今天周四');
INSERT INTO t12 VALUES('ab北京');


#如果vachar不够用，可以考虑使用mediumtext或者longtext,如果想简单点可以直接用text
CREATE TABLE t13(content TEXT,content2 MEDIUMTEXT, content3 LONGTEXT);

INSERT INTO t13 VALUES
('今天星期五','今天星期五medium','今天星期五long');

日期类的基本使用

#date datetime timestamp

# 创建一张表 date,datetime,timestamp
CREATE TABLE t14(
	`birthday` DATE,-- 年月日
	`jobtime` DATETIME,-- 年月日时分秒
	`login_time` TIMESTAMP 
	NOT NULL DEFAULT CURRENT_TIMESTAMP 
	ON UPDATE CURRENT_TIMESTAMP  -- 登录时间
);

INSERT INTO t14 (birthday, jobtime) 
VALUES
  (
    '2022-11-11',
    '2022-11-11 10:09:12'
  ) ;

SELECT * FROM t14 ;

-- 如果我们更新t14表的某条记录，则
-- login_time会以当前时间更新
UPDATE db_02.t14 a
SET a.`birthday` = '2022-11-01'
WHERE a.`birthday` = '2022-11-11';

课后练习

CREATE TABLE db_02.emp (
  `id` INT,
  `name` VARCHAR (100),
  `sex` CHAR(2),
  `birthday` DATE,
  `entry_date` DATETIME,
  `job` VARCHAR (100),
  `salary` DOUBLE,
  `resume` TEXT
) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;


INSERT INTO db_02.emp (
  `id` ,
  `name`,
  `sex`,
  `birthday`,
  `entry_date`,
  `job`,
  `salary`,
  `resume`
) 
VALUES
  (
    100,
    '小妖怪',
    '男',
    '2000-11-11',
    '2010-11-10 10:35:02',
    '巡山',
    '300.92',
    '大王叫我来巡山'
  ) ;

SELECT * FROM db_02.`emp`;

修改表

修改表的基本介绍

课后作业

# alter语句
DESC db_02.`employee` ;
-- 增加一列
ALTER TABLE db_02.`emp` 
  ADD image VARCHAR (255) ;
-- 修改某列的类型
ALTER TABLE db_02.`emp` 
  MODIFY job VARCHAR (60) ;
-- 修改表名
RENAME TABLE emp TO employee ;

-- 修改表的字符集
ALTER TABLE db_02.`employee` 
  CHARACTER SET utf8 ;
-- 修改某列的字段名称
ALTER TABLE db_02.`employee` 
  CHANGE `name` `user_name` VARCHAR (20) ;

-- 删除列
ALTER TABLE db_02.`employee` 
  DROP `sex` ;

数据库CRUD语句

插入字段

插入表

CREATE TABLE db_02.goods (
  `id` INT,
  `goods_name` VARCHAR (10),
  `price` DOUBLE
) ;

INSERT INTO db_02.goods
(`id`,`goods_name`,`price`)
VALUES
(1001,'AD钙',28.89);

INSERT INTO db_02.goods
(`id`,`goods_name`,`price`)
VALUES
(1002,'奥利奥',8.91);

DESC db_02.goods;

SELECT * FROM db_02.goods;

插入语句的细节

#说明 insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同。
-- 比如 把 'abc' 添加到 int 类型会错误
INSERT INTO db_02.goods
(`id`,`goods_name`,`price`)
VALUES
('abc','奥利奥',8.91);
#错误代码： 1366
#Incorrect integer value: 'abc' for column 'id' at row 1

-- 这样写'13'是可以的，mysql底层会把'13'转成13
INSERT INTO db_02.goods
(`id`,`goods_name`,`price`)
VALUES
('13','果冻',8.91);

SELECT * FROM db_02.`goods`;
DESC db_02.`goods`;


-- 2. 数据的长度应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
INSERT INTO db_02.goods
(`id`,`goods_name`,`price`)
VALUES
('13','vivo手机我就觉得非常好用你呢你觉得如何',3000);

#错误代码： 1406
#Data too long for column 'goods_name' at row 1


-- 3. 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。
# 这里id和goods_name交换了
INSERT INTO db_02.goods
(`id`,`goods_name`,`price`)
VALUES
('喜之郎果冻',13,8.91);

#错误代码： 1366
#Incorrect integer value: '喜之郎果冻' for column 'id' at row 1


-- 4. 字符和日期型数据应包含在单引号中。


-- 5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null)

-- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录
INSERT INTO db_02.goods
(`id`,`goods_name`,`price`)
VALUES
('1003','红茶',3.0),
('1004','可乐',4.0);

-- 7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
INSERT INTO db_02.goods
VALUES
('1005','脉动',4.5),
('1006','百岁山',4.0);
-- 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错
-- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给 null -- 如果我们希望指定某个列的默认值，可以在创建表时指定
CREATE TABLE db_02.goods2 (
  `id` INT,
  `goods_name` VARCHAR (10),
  `price` DOUBLE DEFAULT 0.00
) ;

INSERT INTO db_02.goods2
(`id`,`goods_name`)
VALUES
(1001,'哇哈哈');

SELECT * FROM db_02.goods2;

修改字段

修改语句的基本操作

修改表

SELECT * FROM db_02.`employee`;
# 薪水修改为5000
UPDATE db_02.`employee` a
SET a.`salary`  = '5000';

# 小妖怪薪水修改为3000
UPDATE db_02.`employee` a
SET a.`salary` = 3000
WHERE a.`user_name` = '小妖怪';
# 老妖怪增加1000
UPDATE db_02.`employee` a
SET a.`salary` = a.`salary` + 1000
WHERE a.`user_name` = '老妖怪';

修改字段的注意细节

修改表的注意细节

UPDATE db_02.`employee` a
SET a.`salary` = a.`salary` + 1000,a.`job` = '先吃唐僧'
WHERE a.`user_name` = '老妖怪';

删除字段

删除表

#delete语句
DELETE FROM db_02.employee 
WHERE user_name = '老妖怪';

删除语句细节

# 将某一列置空，只能用update
UPDATE db_02.`employee`
SET job = '';
-- 删除表(结构整个删除)
DROP TABLE db_02.`user_bf`;

查询语句

注意事项

课堂练习

****创建新的表(student)********

# 查询所有学生信息
SELECT * FROM db_02.`student`;

# 查询姓名和英语成绩
SELECT a.`name`,a.`english` FROM db_02.`student` a;

# 过滤表中的重复数据
SELECT DISTINCT a.`english` FROM db_02.`student` a;

注意事项2

-- select 语句的使用
# 统计每个学生的总分
SELECT 
  a.`name`,
  SUM(
    a.`chinese` + a.`english` + a.`math`
  ) SUM
FROM
  db_02.student a 
GROUP BY a.`name` ;

# 给所有学生的总分加10分
# 使用别名表示学生的分数
SELECT `name`,
  (
    a.`chinese` + a.`english` + a.`math`
  ) SUM,
  (
    a.`chinese` + a.`english` + a.`math` + 10
  ) sum_bf 
FROM
  db_02.`student` a;

where子句经常使用的运算符

课后练习

课后练习1

课后练习

# 查询赵云的成绩
SELECT a.name,a.`chinese`,a.`english`,a.`math` FROM db_02.`student` a
WHERE a.`name` = '赵云';

#  查询英语成绩大于90分的同学
SELECT a.name,a.`english`  FROM db_02.`student` a
WHERE a.`english`>90;

#查询总分大于200分的所有同学
SELECT 
  a.name,
  (
    a.`chinese` + a.`english` + a.`math`
  ) total_score 
FROM
  db_02.`student` a 
  WHERE (a.`chinese` + a.`english` + a.`math`) > 200
  ORDER BY total_score;
  
  # 查询math大于60，id大于4的学生成绩
  SELECT a.* FROM db_02.`student` a
  WHERE a.`math` > 60
  AND a.`id` > 4;
  
  # 查询英语成绩大于语文成绩的
  SELECT a.* FROM db_02.`student` a
  WHERE a.`english` > a.`chinese`;
  
  # 查询总分大于200分的 并且数学成绩小于语文成绩，姓赵的
  SELECT 
  a.name,
  (
    a.`chinese` + a.`english` + a.`math`
  ) total_score 
FROM
  db_02.`student` a 
  WHERE (a.`chinese` + a.`english` + a.`math`) > 200
  AND a.`math` < a.`chinese`
  AND a.`name` LIKE '赵%';

课后练习2

-- select语句
-- 课堂练习
-- 1.查询英语分数在80 - 90之间的同学。
-- between …… and 是闭区间
SELECT 
  * 
FROM
  db_02.`student` a 
WHERE a.`english` BETWEEN 80 
  AND 90 ;

-- 2.查询数学分数为89,90,91的同学。
SELECT 
  * 
FROM
  db_02.`student` a 
WHERE a.`math` IN (80, 90, 91) ;

-- 3.查询所有姓韩的学生成绩。
SELECT 
  * 
FROM
  db_02.`student` a 
WHERE a.`name` LIKE '韩%' ;

-- 4.查询数学分> 80,语文分> 80的同学。
SELECT 
  * 
FROM
  db_02.`student` a 
WHERE a.`math` > 80 
  AND a.`chinese` > 80 ;

-- 课堂练习[学员自己练习]
-- 1.查询语文分数在70 - 80之间的同学。
SELECT 
  * 
FROM
  db_02.`student` a 
WHERE a.`chinese` BETWEEN 80 
  AND 90 ;

-- 2.查询总分为189, 190,191的同学。
SELECT 
  * 
FROM
  db_02.`student` a 
WHERE (
    a.`chinese` + a.`english` + a.`math`
  ) IN (189, 190, 191) ;

-- 3.查询所有姓李或者姓宋的学生成绩。
SELECT 
  * 
FROM
  db_02.`student` a 
WHERE a.`name` LIKE '李%' 
  OR a.`name` LIKE '宋%' ;

-- 4.查询数学比语文多30分的同学。
SELECT 
  * 
FROM
  db_02.`student` a 
WHERE (a.`math` - a.`chinese`) = 30 ;

order by 子句

# 演示order by的使用

-- 对数学成绩排序后输出[升序]
SELECT * FROM db_02.`student` a
ORDER BY a.`math`;

-- 对总分按从高到低的顺序输出(降序)
SELECT a.name, (a.`math` + a.`chinese` + a.`english`) total FROM db_02.`student` a
ORDER BY total DESC;

-- 对姓韩的学生成绩排序输出(升序)
SELECT a.name,(a.`chinese` + a.`math` + a.`english`) total FROM db_02.`student` a
WHERE a.`name` LIKE '韩%'
ORDER BY total;

合计/统计函数

count函数

合计/统计函数

-- 课堂练习: statistics.sql
-- 课堂练习：stattics.sql
-- 统计一个班级共有多少学生?
SELECT COUNT(a.id) FROM db_02.`student` a;
-- 统计数学成绩大于90的学生有多少个？
SELECT COUNT(a.id) FROM db_02.`student` a
WHERE a.`math` > 90;

-- 统计总分大于250的人数有多少？
SELECT COUNT(a.id) FROM db_02.`student` a
WHERE (a.`math`+ a.`chinese` + a.`english`) > 250;

-- count(*)和count(列)的区别
-- count(*)返回满足条件的记录的行数
-- count(列) 统计满足条件的某列有多少个，但是会排除为null的
CREATE TABLE t15
(`name` VARCHAR(20));

INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);

SELECT * FROM t15;

SELECT COUNT(*) FROM t15; -- 返回4
SELECT COUNT(`name`) FROM t15; -- 返回3

sum函数

-- 课堂练习:
-- 统计一个班级数学总成绩?
SELECT SUM(a.`math`) FROM db_02.`student` a;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(a.`chinese`),SUM(a.`english`),SUM(a.`math`) FROM db_02.`student` a;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(a.`chinese` + a.`math` + a.`english`) FROM db_02.`student` a;
-- 统计一个班级语文成绩平均分
SELECT (SUM(a.`chinese`) / COUNT(a.`id`)) FROM db_02.`student` a;
SELECT AVG(a.`chinese`) FROM db_02.`student` a;
-- 注意: sum仅对数值起作用，没有意义。

-- 注意:对多列求和，“”号不能少。

avg函数

-- 练习:
-- 求一个班级数学平均分?
SELECT AVG(a.`math`)FROM db_02.`student` a;
-- 求-一个班级总分平均分
SELECT AVG(a.`math` + a.`chinese` + a.`english`) FROM db_02.`student` a

Max/Min函数

-- 练习: 求班级最高分和最低分 (数值范围在统计中特别有用)
SELECT MAX(a.`chinese` + a.`english` + a.`math`)high,
MIN(a.`math` + a.`chinese` + a.`english`)low 
FROM db_02.`student` a;

-- 班级数学最高分和最低分
SELECT MAX(a.`math`),MIN(a.`math`) FROM db_02.`student` a

group by子句

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

INSERT INTO dept
VALUES
(10,'ACCOUNTING','NEW YORK'),
(20,'RESSEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');

SELECT * FROM db_02.`dept`;

-- 员工表
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利 奖金*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);

-- 添加测试数据
 INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);

SELECT * FROM db_02.emp;

-- 工资级别
#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/ 
losal DECIMAL(17,2)  NOT NULL, /* 该级别的最低工资 */
hisal DECIMAL(17,2)  NOT NULL /* 该级别的最高工资*/
);

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

SELECT * FROM db_02.`salgrade`;

-- group by用于对查询的结果分组统计，(示意图)
-- having子包用于限制分组显示结果.
-- 如何显示每个部门的平均工资和最高工资
SELECT AVG(a.`sal`), b.`dname` FROM emp a,dept b
WHERE a.`deptno` = b.`deptno`
GROUP BY a.`deptno`,b.`dname`;

SELECT MAX(a.`sal`),MIN(a.`sal`),b.`dname` FROM emp a,dept b
WHERE a.`deptno` = b.`deptno`
GROUP BY a.`deptno`,b.`dname`;

SELECT AVG(a.`sal`),MAX(a.`sal`),MIN(a.`sal`), b.`dname` 
FROM emp a,dept b
WHERE a.`deptno` = b.`deptno`
GROUP BY a.`deptno`,b.`dname`;




-- 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(a.`sal`),a.`job` FROM emp a,dept b
WHERE a.`deptno` = b.`deptno`
GROUP BY a.`job`;

SELECT MIN(a.`sal`),a.`job` FROM emp a,dept b
WHERE a.`deptno` = b.`deptno`
GROUP BY a.`job`;

SELECT AVG(a.`sal`),MIN(a.`sal`),a.`job`, a.`deptno` FROM emp a
GROUP BY a.`job`,a.`deptno`;

-- 显示平均工资低于2000的部门号和它的平均工资//别名
SELECT c.deptno,c.avg_sal FROM(
SELECT a.`deptno`,AVG(a.`sal`) avg_sal FROM emp a
GROUP BY a.`deptno`) c
WHERE c.avg_sal < 2000;


SELECT a.`deptno`,AVG(a.`sal`) avg_sal FROM emp a
GROUP BY a.`deptno`
HAVING avg_sal < 2000;

字符串函数

#演示字符串相关的函数
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(a.`ename`) FROM db_02.`emp` a;

-- CONCAT (string2 [.... ]) 连接字串!
SELECT CONCAT(a.`ename`,' 工作是 ', a.`job`) FROM db_02.`emp` a;

-- INSTR (string ,substring ) 返回substring在string中出现的位置没有返回0
SELECT INSTR('hanshunping','ping') FROM DUAL;


-- UCASE (string2 ) 转换成大写
SELECT UCASE(a.`ename`) FROM db_02.`emp` a;

-- LCASE (string2 ) 转换成小写
SELECT LCASE(a.`ename`) FROM db_02.`emp` a;

-- LEFT (string2, length ) 从string2中的左边起取length个字符
SELECT LEFT(a.`ename`,2) FROM db_02.`emp` a;

-- RIGHT(string2, length)
SELECT RIGHT(a.ename,2) FROM db_02.`emp` a;

-- LENGTH (string ) string长度[按照字节]
SELECT LENGTH('韩顺平') FROM DUAL; -- 9
SELECT LENGTH('hsp') FROM DUAL; -- 3

-- REPLACE (str ,search_str,replace_ _str ) 在str中用replace_ str 替换search_ str
SELECT REPLACE(a.`job`,'MANAGER','经理') FROM db_02.`emp` a;

-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小，
-- 相等返回0
-- 后面大返回-1
-- 前面大返回1
SELECT STRCMP('str','string') FROM DUAL;

-- SUBSTRING (str，position [,length]) 从str的position开始[从1开始计算] ,取length个字符
SELECT SUBSTRING(a.`ename`,1,3) FROM db_02.`emp` a;



-- LTRIM (string2 ) RTRIM (string2 ) trim 去除前端空格或后端空格
SELECT LTRIM(a.`ename`) FROM db_02.`emp` a;
SELECT LTRIM('  韩顺平') FROM db_02.`emp` a;


SELECT RTRIM(a.`ename`) FROM db_02.`emp` a;
SELECT LTRIM('韩顺平  ') FROM db_02.`emp` a;


-- 练习:以首字母小写的方式显示所有员工emp表的姓名str.sql
-- 使用两种方式5min
SELECT 
  CONCAT(
    LCASE(SUBSTRING(a.`ename`, 1, 1)),
    SUBSTRING(a.`ename`, 2)
  ) 
FROM
  db_02.`emp` a; 
  
SELECT 
  CONCAT(
    LCASE(LEFT(a.`ename`, 1)),
    SUBSTRING(a.`ename`, 2)
  ) 
FROM
  db_02.`emp` a ;

数学函数

-- 演示数学相关函数
-- ABS (num)绝对值
SELECT ABS(-10) FROM DUAL;

-- BIN (decimal number ) 十进制转二进制
SELECT BIN(10) FROM DUAL;

-- CEILING(number2)向上取整，得到比num2大的最小整数
SELECT CEILING(7.8) FROM DUAL; -- 8

SELECT CEILING(-12.43) FROM DUAL;
SELECT CEILING(-12.98) FROM DUAL;


-- CONV (number2 , from base, to base)进 制转换
-- 把8当成10进制的转换为2进制输出
SELECT CONV(8,10,2) FROM DUAL;
SELECT CONV(8,16,2) FROM DUAL;
SELECT CONV(8,16,10) FROM DUAL;


-- FLOOR (number2 )向 下取整,得到比num2小的最大整数
SELECT FLOOR(10.2) FROM DUAL; -- 10
SELECT FLOOR(-10.2) FROM DUAL; -- -11


-- FORMAT (number , decimal_ places )保留小数位数
SELECT FORMAT(11.375,1) FROM DUAL; -- 11.4
SELECT FORMAT(78.12345,0) FROM DUAL; -- 78


-- HEX (DecimalNumber )转十六进制
SELECT HEX(10) FROM DUAL;

-- LEAST (number ，number2 [, . .])求最小值
SELECT LEAST(10,20,13,11,22) FROM DUAL;

-- MOD (numerator , denominator )求余
SELECT MOD(10,3) FROM DUAL;

-- RAND([seed]) RAND([seed]) 其范围为0 S v≤1.0
SELECT RAND() FROM DUAL;

SELECT RAND(3) FROM DUAL; -- 随机数返回后，固定不变

时间日期相关函数

时间日期相关函数
时间和日期函数2

-- CURRENT_DATE()当前日期
SELECT CURRENT_DATE() FROM DUAL;

-- CURRENT_TIME()当前时间 时分秒
SELECT CURRENT_TIME FROM DUAL;

-- CURRENT_TIMESTAMP()当前时间戳
SELECT CURRENT_TIMESTAMP FROM DUAL;

CREATE TABLE mes(id INT , content VARCHAR(30), sendtime DATETIME);
SELECT * FROM db_02.`mes`;

INSERT INTO mes VALUES(1,'北京新闻', CURRENT_TIMESTAMP);
INSERT INTO mes VALUES(2,'上海新闻', NOW());
INSERT INTO mes VALUES(3,'广州新闻', '2022-11-2');




-- 查询
-- 1.显示所有留言信息，发布日期只显示日期，不用显示时间.
-- DATE (datetime)返回datetime的日期部分
SELECT a.`id`,a.`content`,DATE(a.`sendtime`) FROM db_02.`mes` a

-- 请查询在10分钟内发布的帖子

-- DATE_ADD(date2, INTERVAL d_valued_ type)在date2中加上日期或时间
-- DATE_SUB (date2, INTERVAL d_valued_type)在date2上减去一个时间
SELECT * FROM db_02.`mes` a
WHERE DATE_ADD(a.`sendtime`,INTERVAL 10 MINUTE) >= NOW();

SELECT * FROM db_02.`mes` a
WHERE DATE_SUB(a.`sendtime`,INTERVAL 3 MINUTE) <= NOW();

SELECT * FROM db_02.`mes` a
WHERE a.`sendtime` <= DATE_SUB(NOW(),INTERVAL 1 MINUTE);


-- 3.请在mysql 的sq|语句中求出 2011-11-11和1990-1-1 相差多少天
-- DATEDIFF (date1 ,date2)两个日期差(结果是天)
SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL;

-- 4.请用mysql 的sq|语句求出你活了多少天? [练习]
-- TIMEDIFF(date1,date2)两个时间差(多少小时多少分钟多少秒)
SELECT DATEDIFF('2022-11-2','1997-2-27') FROM DUAL;
SELECT TIMEDIFF(NOW(),a.`sendtime`) FROM db_02.`mes` a;


-- 5.如果你能活80岁，求出你还能活多少天.[练习]
-- NOW()当前时间
SELECT NOW() FROM DUAL;
-- YEAR |Month|DATE (datetime)FROM_ UNIXTIME()年月日
SELECT 
  DATEDIFF(
    DATE_ADD('1997-2-27', INTERVAL 80 YEAR),
    NOW()
  ) 
FROM
  DUAL ;

SELECT TIMEDIFF('10:11:11','9:50:10') FROM DUAL;

时间日期函数

SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;

-- 1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;--1667406201

SELECT UNIX_TIMESTAMP()/(24*3600*365) FROM DUAL;

-- 把UNIX_TIMESTAMP()时间戳转换成日期
-- 开发中可以存一个整数，然后转换
SELECT FROM_UNIXTIME(1667406201,'%Y-%m-%d %H:%i:%s') FROM DUAL;

加密函数

-- 演示加密函数和系统函数
-- USER()查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL;

-- DATABASE()数据库名称
SELECT DATABASE() FROM DUAL;

-- MD5(str)为字符串算出一个MD5 
-- 32的字符串，(用户密码)加密
SELECT MD5('wtui3285') FROM DUAL;
SELECT LENGTH(MD5('wtui3285')) FROM DUAL;


-- 基本使用 
CREATE TABLE users (
  id INT,
  NAME VARCHAR (32) NOT NULL DEFAULT '',
  pwd CHAR (32) NOT NULL DEFAULT ''
) ;

-- 添加一个用户名
SELECT * FROM db_02.`users` a
WHERE a.`name` = '韩顺平'
AND a.`pwd` = MD5('hsp');

INSERT INTO db_02.`users`
VALUES(100,'韩顺平',MD5('hsp'));

-- PASSWORD(str) SELECT * FROM mysql.user \G从原文密码str计算并返回密码字符串,
-- 通常用于对mysql数据库的用户密码加密
SELECT PASSWORD('hsp') FROM DUAL;

SELECT * FROM mysql.user;
-- 这里发现两者相同，说明mysql本身就是用的PASSWORD()加密的
*FE105FE7208F708B850C66B725E60A6C4FF4BD8B
*FE105FE7208F708B850C66B725E60A6C4FF4BD8B

流程控制函数

#流程控制语句
-- IF(expr1,expr2,expr3)如果expr1为True ,则返回expr2否则返回expr3
SELECT IF(TRUE,'北京','上海') FROM DUAL;
SELECT IF(FALSE,'北京','上海') FROM DUAL;

-- IFNULL(expr1,expr2)如果expr1不为空NULL,则返回expr1,否则返回expr2
SELECT IFNULL('jack','tom') FROM DUAL;
SELECT IFNULL(NULL,'this') FROM DUAL;

-- SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
-- 如果expr1为TRUE,则返回expr2,如果expr2为,返回expr4,否则返回expr5


-- 先看两个需求:
-- 1.查询emp表，如果comm是null，否则显示0.0
SELECT IFNULL(a.`comm`,0.0)  FROM db_02.`emp` a;
-- 判断是否为空要用is null
-- 判断不为空要用is not null
SELECT 
  a.`ename`,
  IF(a.`comm` IS NULL, 0.00,a.`comm`) 
FROM
  db_02.`emp` a; 


-- 2.如果emp表的job是CLERK则显示职员，如果是MANAGER则显示经理
-- 如果是SALESMAN则显示销售人员，其它正常显示，
SELECT 
  a.ename,
  (SELECT 
    CASE
      WHEN a.`job` = 'CLERK' 
      THEN '职员' 
      WHEN a.`job` = 'MANAGER' 
      THEN '经理' 
      WHEN a.`job` = 'SALESMAN' 
      THEN '销售人员' 
      ELSE a.job END) job
FROM
  db_02.`emp` a ;

心向阳光的天域

关注

9
点赞
踩
33

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL数据库基础01 韩顺平自学笔记

添加jar包：mysql-connector-java-5.1.37-bin.jar代码示例:数据库操作创建数据库查看、删除数据库备份和恢复数据库备份恢复数据库的表Mysql常用数据类型数值型(bit)的基本使用数值型(小数)的基本使用字符串的基本使用字符串的使用细节日期类的基本使用课后练习修改表修改表的基本介绍数据库CRUD语句插入语句的细节修改字段修改语句的基本操作修改字段的注意细节删除字段删除语句细节查询语句注意事
复制链接

扫一扫