Java补充之MySQL入门必备知识
文章和代码已经归档至【Github仓库:https://github.com/timerring/java-tutorial 】或者公众号【AIShareLab】回复 java 也可获取。
零基础学MySQL
解决之道
文件、数据库
为了解决上述问题,使用更加利于管理数据的东东-数据库,它能更有效的管理数据。
举一个生活化的案例说明:如果说图书馆是保存书籍的,那么数据库就是保存数据的。
MySQL 数据库的安装和配置
mysql5.5 mysql5.6 mysqI5.7(稳定) mysql8 更高版本
使用命令行窗口连接MYSQL 数据库
mysql -h主机名-P端口-u用户名-p密码
登录前,保证服务启动
net stop mysql服务名
net start mysql服务名
操作示意图
- Navicat
介绍: 图形化MySQL 管理软件
- SQLyog
数据库三层结构
- 所谓安装Mysql数据库,就是在主机安装一个数据库管理系统(DBMS),这个管理程序可以管理多个数据库。DBMS(database manage system)。
- 一个数据库中可以创建多个表,以保存数据(信息)。
- 数据库管理系统(DBMS)、数据库和表的关系如图所示;示意图。
数据在数据库中的存储方式
SQL 语句分类
DDL:数据定义语句[create表,库…]
DML:数据操作语句[增加insert,修改update,删除 delete]
DQL:数据查询语句[select ]
DCL:数据控制语句[管理数据库: 比如用户权限 grant revoke ]
创建数据库
- CHARACTER SET:指定数据库采用的字符集,如果不指定字符集,默认utf8
- COLLATE:指定数据库字符集的校对规则(常用的utf8 bin[区分大小写]
utf8_general_ci[不区分大小写] 注意默认是 utf8 general_ci )
# 演示数据库的操作
#创建一个名称为db01的数据库。[图形化和指令 演示]
#使用指令创建数据库
CREATE DATABASE db01;
#删除数据库指令
DROP DATABASE db01
#创建一个使用utf8字符集的hsp_db02数据库
CREATE DATABASE db02 CHARACTER SET utf8
#创建一个使用utf8字符集,并带校对规则的hsp_db03数据库
CREATE DATABASE db03 CHARACTER SET utf8 COLLATE utf8_bin
#校对规则 utf8_bin 区分大小 默认utf8_general_ci 不区分大小写
#下面是一条查询的sql , select 查询 * 表示所有字段 FROM 从哪个表
#WHERE 从哪个字段 NAME = 'tom' 查询名字是tom
SELECT *
FROM t1
WHERE NAME = 'tom'
查看、删除数据库
显示数据库语句:
SHOW DATABASES
显示数据库创建语句:
SHOW CREATE DATABASE db_name
数据库删除语句[一定要慎用]:
DROP DATABASE [IF EXISTS] db_ name
#演示删除和查询数据库
#查看当前数据库服务器中的所有数据库
SHOW DATABASES
#查看前面创建的hsp_db01数据库的定义信息
SHOW CREATE DATABASE `hsp_db01`
#在创建数据库,表的时候,为了规避关键字,可以使用反引号解决,比如有数据库的名称就叫做CREATE,那么就需要使用` `解决。
#删除前面创建的hsp_db01数据库
DROP DATABASE hsp_db01
备份恢复数据库
备份数据库(注意:在DOS执行)命令行
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n >文件名.sql
恢复数据库(注意:进入Mysql命令行再执行)
Source 文件名.sql
#练习 : database03.sql 备份db02 和 db03 库中的数据,并恢复
#备份, 要在Dos下执行mysqldump指令其实在mysql安装目录\bin
#这个备份的文件,就是对应的sql语句
mysqldump -u root -p -B db02 db03 > d:\\bak.sql
DROP DATABASE ecshop;
#恢复数据库(注意:进入Mysql命令行再执行)
source d:\\bak.sql
#第二个恢复方法, 直接将bak.sql的内容放到查询编辑器中,执行,相当于重新执行了一遍
备份恢复数据库的表
备份库的表
mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > d:\\文件名.sql
创建表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype
)character set 字符集 collate 校对规则 engine 存储引擎
- field:指定列名 datatype:指定列类型(字段类型)
- character set:如不指定则为所在数据库字符集
- collate:如不指定则为所在数据库校对规则
- engine:引擎(这个涉及内容较多)
#指令创建表
#注意:db02创建表时,要根据需保存的数据创建相应的列,并根据数据的类型定义相应的列类型。例:user表 (快速入门案例 create_tab01.sql)
#id 整形 [图形化,指令]
#name 字符串
#password 字符串
#birthday 日期
CREATE TABLE `user` (
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
Mysql 常用数据类型(列类型)
数值型(整数)的基本使用
#演示整型的是一个
#使用tinyint 来演示范围 有符号 -128 ~ 127 如果没有符号 0-255
#说明: 表的字符集,校验规则, 存储引擎,老师使用默认
#1. 如果没有指定 unsinged , 则TINYINT就是有符号
#2. 如果指定 unsinged , 则TINYINT就是无符号 0-255
CREATE TABLE t3 (
id TINYINT);
CREATE TABLE t4 (
id TINYINT UNSIGNED);
INSERT INTO t3 VALUES(127); #这是非常简单的添加语句
SELECT * FROM t3
INSERT INTO t4 VALUES(255);
SELECT * FROM t4;
定义一个无符号的整数
create table t10 (id tinyint );//默认是有符号的
create table t11 (id tinyint unsigned);//无符号的 (后加unsigned就行)
数值型(bit)的使用
1.基本使用
mysql > create table t05 (num bit(8));
mysql > insert into t05 (1, 3);
mysql > insert into t05 values(2, 65);
2.细节说明bit.sql
- bit字段显示时,按照位的方式显示.
- 查询的时候仍然可以用使用添加的数值
- 如果一个值只有0,1可以考虑使用bit(1), 可以节约空间
- 位类型。M指定位数,默认值1,范围1-64
#演示bit类型使用
#说明
#1. bit(m) m 在 1-64
#2. 添加数据 范围 按照你给的位数来确定,比如 m = 8 表示一个字节 0~255
#3. 显示按照bit
#4. 查询时,仍然可以按照数来查询
CREATE TABLE t05 (num BIT(8));
INSERT INTO t05 VALUES(255); # 按位显示 255 就是全1 (b'11111111')
SELECT * FROM t05;
SELECT * FROM t05 WHERE num = 1;
数值型(小数)的基本使用
FLOAT/DOUBLE [UNSIGNED]
Float单精度精度,Double双精度.
DECIMAL[M,D] [UNSIGNED]
- 可以支持更加精确的小数位。M是小数位数(精度)的总数,D是小数点(标度)后面的位数。
- 如果D是0,则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略,默认是0。如果M被省略,默认是10。
- 建议:如果希望小数的精度高,推荐使用decimal
#演示decimal类型、float、double使用
#创建表
CREATE TABLE t06 (
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20));
#添加数据
INSERT INTO t06 VALUES(88.12345678912345, 88.12345678912345,88.12345678912345);
SELECT * FROM t06;
#decimal可以存放很大的数
CREATE TABLE t07 (
num DECIMAL(65));
INSERT INTO t07 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t07;
CREATE TABLE t08(
num BIGINT UNSIGNED)
INSERT INTO t08 VALUES(8999999933338388388383838838383009338388383838383838383);
SELECT * FROM t08;
字符串的基本使用
CHAR(size)
固定长度字符串最大255字符
VARCHAR(size)0~65535
可变长度字符串最大65532字节【utf8编码最大21844字符((65535-3) / 3)其中1-3个字节用于记录大小】
#演示字符串类型使用char varchar
#注释的快捷键 shift+ctrl+c , 注销注释 shift+ctrl+r
-- CHAR(size)
-- 固定长度字符串 最大255 字符
-- VARCHAR(size) 0~65535字节
-- 可变长度字符串 最大65532字节 【utf8编码最大21844字符 1-3个字节用于记录大小】
-- 如果表的编码是 utf8 varchar(size) size = (65535-3) / 3 = 21844
-- 如果表的编码是 gbk varchar(size) size = (65535-3) / 2 = 32766
CREATE TABLE t09 (
`name` CHAR(255));
CREATE TABLE t10 (
`name` VARCHAR(32766)) CHARSET gbk;
DROP TABLE t10;
字符串使用细节
1.细节1
- char(4) //这个4表示字符数(最大255),不是字节数,不管是中文还是字母都是放四个,按字符计算.
- varchar(4) //这个4表示字符数,不管是字母还是中文都以定义好的表的编码来存放数据
- 不管是中文还是英文字母,都是最多存放4个,是按照字符来存放的.
2.细节2
- char(4)是定长(固定的大小),就是说,即使你插入’aa’,也会占用分配的4个字符的空间.
- varchar(4)是变长(变化的大小),就是说,如果你插入了’aa’,实际占用空间大小并不是4个字符,而是按照实际占用空间来分配(说明:varchar本身还需要占用1-3个字节来记录存放内容长度)
L(实际数据大小)+(1-3)字节
3.细节3
什么时候使用char,什么时候使用varchar
- 如果数据是定长,推荐使用char,比如md5的密码,邮编,手机号,身份证号码等. char(32)
- 如果一个字段的长度是不确定,我们使用varchar ,比如留言,文章
查询速度: char > varchar
4.细节4
在存放文本时,也可以使用Text数据类型。可以将TEXT列视为VARCHAR列,注意Text 不能有默认值。大小0-2^16字节。如果希望存放更多字符,可以选择
MEDIUMTEXT 0O-2^24 或者LONGTEXT 0~2^32
#演示字符串类型的使用细节
#char(4) 和 varchar(4) 这个4表示的是字符,而不是字节, 不区分字符是汉字还是字母
CREATE TABLE t11(
`name` CHAR(4));
INSERT INTO t11 VALUES('你好你好');
SELECT * FROM t11;
CREATE TABLE t12(
`name` VARCHAR(4));
INSERT INTO t12 VALUES('你好你好');
INSERT INTO t12 VALUES('ab北京');
SELECT * FROM t12;
#如果varchar 不够用,可以考试使用mediumtext 或者longtext,
#如果想简单点,可以使用直接使用text
CREATE TABLE t13( content TEXT, content2 MEDIUMTEXT , content3 LONGTEXT);
INSERT INTO t13 VALUES('你好你教育', '你好你教育100', '你好你教育1000~~');
SELECT * FROM t13;
日期类型的基本使用
CREATE TABLE birthday6( t1 DATE, t2 DATETIME,
t3 TIMESTAMP NOT NULL DEFAULTCURRENT TIMESTAMP ON UPDATE
CURRENT TIMESTAMP ); timestamp时间戳
# NOT NULL DEFAULTCURRENT TIMESTAMP ON UPDATE CURRENT TIMESTAMP 意思是没有默认值的话会根据当前时间自动更新
mysql> INSERT INTO birthday (t1,t2)
VALUES('2022-11-11',2022-11-11 10:10:10');
日期类型的细节说明
TimeStamp在Insert和update时,自动更新。
#演示时间相关的类型
#创建一张表, date , datetime , timestamp
CREATE TABLE t14 (
birthday DATE , -- 生日
job_time DATETIME, -- 记录年月日 时分秒
login_time TIMESTAMP
NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP); -- 登录时间, 如果希望login_time列自动更新, 需要配置
SELECT * FROM t14;
INSERT INTO t14(birthday, job_time)
VALUES('2022-11-11','2022-11-11 10:10:10');
-- 如果我们更新 t14 表的某条记录,login_time列会自动的以当前时间进行更新
创建表练习
#创建表的课堂练习
-- 字段 属性
-- Id 整形
-- name 字符型
-- sex 字符型
-- brithday 日期型(date)
-- entry_date 日期型 (date)
-- job 字符型
-- Salary 小数型
-- resume 文本型
-- 自己一定要练习一把
CREATE TABLE `emp` (
id INT,
`name` VARCHAR(32),
sex CHAR(1),
brithday DATE,
entry_date DATETIME,
job VARCHAR(32),
salary DOUBLE,
`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
-- 添加一条
INSERT INTO `emp`
VALUES(100, '小妖怪', '男', '2000-11-11',
'2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山');
SELECT * FROM `emp`;
修改表-基本介绍
修改表-课堂练习
NOT NULL DEFAULT ''
不允许为空NULL,默认是一个空的’ '字符。
#修改表的操作练习
-- 员工表emp的上增加一个image列,varchar类型(要求在resume后面)。
ALTER TABLE emp
ADD image VARCHAR(32) NOT NULL DEFAULT ''
AFTER RESUME
DESC employee -- 显示表结构,可以查看表的所有列
-- 修改job列,使其长度为60。
ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
-- 删除sex列。
ALTER TABLE emp
DROP sex
-- 表名改为employee。
RENAME TABLE emp TO employee
-- 修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8
-- 列名name修改为us er_name
ALTER TABLE employee
CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''
DESC employee
数据库C[create]R[read]U[update]D[delete]语句
- Insert语句 (添加数据)
- Update语句 (更新数据)
- Delete语句 (删除数据)
- Select语句 (查找数据)
Insert 语句
使用INSERT 语句向表中插入数据。
#练习insert 语句
-- 创建一张商品表goods (id int , goods_name varchar(10), price double );
-- 添加2条记录
CREATE TABLE `goods` (
id INT ,
goods_name VARCHAR(10), -- 长度10
price DOUBLE NOT NULL DEFAULT 100 );
-- 添加数据
INSERT INTO `goods` (id, goods_name, price)
VALUES(10, '华为手机', 2000);
INSERT INTO `goods` (id, goods_name, price)
VALUES(20, '苹果手机', 3000);
SELECT * FROM goods;
CREATE TABLE `goods2` (
id INT ,
goods_name VARCHAR(10), -- 长度10
price DOUBLE NOT NULL DEFAULT 100 );
细节说明
#说明insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同。
-- 比如 把 'abc' 添加到 int 类型会错误
INSERT INTO `goods` (id, goods_name, price)
VALUES('abc', '小米手机', 2000);
-- 2. 数据的长度应在列的规定范围内,例如:不能将一个长度为80的字符串加入到长度为40的列中。
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo手机vovo手机vovo手机vovo手机vovo手机', 3000);
-- 3. 在values中列出的数据位置必须与被加入的列的排列位置相对应。
INSERT INTO `goods` (id, goods_name, price) -- 不对
VALUES('vovo手机',40, 2000);
-- 4. 字符和日期型数据应包含在单引号中。
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, vovo手机, 3000); -- 错误的 vovo手机 应该 'vovo手机'
-- 5. 列可以插入空值[前提是该字段允许为空(如果指定 not null 则不可以)],insert into table value(null)
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, 'vovo手机', NULL);
-- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录
INSERT INTO `goods` (id, goods_name, price)
VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800);
-- 7. 如果是给表中的所有字段添加数据,可以不写前面的字段名称
INSERT INTO `goods`
VALUES(70, 'IBM手机', 5000);
-- 8. 默认值的使用,当不给某个字段值时,如果有默认值就会添加默认值,否则报错
-- 如果某个列 没有指定 not null ,那么当添加数据时,没有给定值,则会默认给null
-- 如果我们希望指定某个列的默认值,可以在创建表时指定
INSERT INTO `goods` (id, goods_name)
VALUES(80, '格力手机');
SELECT * FROM goods;
INSERT INTO `goods2` (id, goods_name)
VALUES(10, '顺平手机');
SELECT * FROM goods2;
update 语句
使用 update 语句修改表中数据
基本使用
-- 演示update语句
-- 要求: 在上面创建的employee表中修改表中的纪录
-- 1. 将所有员工薪水修改为5000元。[如果没有带where 条件,会修改所有的记录,因此要小心]
UPDATE employee SET salary = 5000
-- 2. 将姓名为 小妖怪 的员工薪水修改为3000元。
UPDATE employee
SET salary = 3000
WHERE user_name = '小妖怪'
-- 3. 将 老妖怪 的薪水在原有基础上增加1000元
INSERT INTO employee
VALUES(200, '老妖怪', '1990-11-11', '2000-11-11 10:10:10', '捶背的', 5000, '给大王捶背', 'd:\\a.jpg');
UPDATE employee
SET salary = salary + 1000
WHERE user_name = '老妖怪'
-- 可以修改多个列的值
UPDATE employee
SET salary = salary + 1000 , job = '出主意的'
WHERE user_name = '老妖怪'
SELECT * FROM employee;
使用细节
- WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行(记录)。
- 如果需要修改多个字段,可以通过
set 字段1=值1,字段2=值2...
delete 语句
使用delete 语句删除表中数据
删除只能按照行删除,不能按照列删除。
-- delete 语句演示
-- 删除表中名称为’老妖怪’的记录。
DELETE FROM employee
WHERE user_name = '老妖怪';
-- 删除表中所有记录, 老师提醒,一定要小心
DELETE FROM employee;
-- Delete语句不能删除某一列的值(可使用update 设为 null 或者 '')
UPDATE employee SET job = '' WHERE user_name = '老妖怪';
SELECT * FROM employee
-- 要删除这个表
DROP TABLE employee;
使用细节
- 如果不使用where子句,将删除表中所有数据。
- Delete语句不能删除某一列的值(可使用update设为null 或者"")
- 使用delete语句删除记录,不删除表本身。如要删除表,使用droptable语句。
drop table 表名
;
select 语句
基本语法
注意事项(创建测试表学生表)
- Select 指定查询哪些列的数据。
- column指定列名。
- *号代表查询所有列。
- From指定查询哪张表。
- DISTINCT可选, 指显示结果时,是否去掉重复数据。
练习
-- select 语句【重点 难点】
CREATE TABLE student(
id INT NOT NULL DEFAULT 1,
NAME VARCHAR(20) NOT NULL DEFAULT '',
chinese FLOAT NOT NULL DEFAULT 0.0,
english FLOAT NOT NULL DEFAULT 0.0,
math FLOAT NOT NULL DEFAULT 0.0
);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);
SELECT * FROM student;
-- 查询表中所有学生的信息。
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT `name`,english FROM student;
-- 过滤表中重复数据 distinct 。
SELECT DISTINCT english FROM student;
-- 要查询的记录,每个字段都相同,才会去重
SELECT DISTINCT `name`, english FROM student;
使用表达式对查询的列进行运算
在select 语句中可使用as 语句
练习
-- select 语句的使用
-- 统计每个学生的总分
SELECT `name`, (chinese+english+math) FROM student;
-- 在所有学生总分加10分的情况
SELECT `name`, (chinese + english + math + 10) FROM student;
-- 使用别名表示学生分数。
SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score
FROM student;
在where 子句中经常使用的运算符
其中的BETWEEN … AND 是闭区间。
使用where 子句,进行过滤查询
_
:匹配任意一个字符;%
:匹配0个或多个字符;[ ]
:匹配[ ]中的任意一个字符(若要比较的字符是连续的,则可以用连字符“-”表 达 );[^ ]
:不匹配[ ]中的任意一个字符。
-- select 语句
-- 查询姓名为赵云的学生成绩
SELECT * FROM student
WHERE `name` = '赵云'
-- 查询英语成绩大于90分的同学
SELECT * FROM student
WHERE english > 90
-- 查询总分大于200分的所有同学
SELECT * FROM student
WHERE (chinese + english + math) > 200
-- 查询math大于60 并且(and) id大于4的学生成绩
SELECT * FROM student
WHERE math >60 AND id > 4
-- 查询英语成绩大于语文成绩的同学
SELECT * FROM student
WHERE english > chinese
-- 查询总分大于200分 并且 数学成绩小于语文成绩,的姓赵的学生.
-- 赵% 表示 名字以赵开头的就可以
SELECT * FROM student
WHERE (chinese + english + math) > 200 AND
math < chinese AND `name` LIKE '赵%'
-- 查询英语分数在 80-90之间的同学。
SELECT * FROM student
WHERE english >= 80 AND english <= 90;
SELECT * FROM student
WHERE english BETWEEN 80 AND 90; -- between .. and .. 是 闭区间
-- 查询数学分数为89,90,91的同学。
SELECT * FROM student
WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student
WHERE math IN (89, 90, 91);
-- 查询所有姓李的学生成绩。
SELECT * FROM student
WHERE `name` LIKE '韩%'
-- 查询数学分>80,语文分>80的同学
使用order by 子句排序查询结果
- Order by 指定排序的列,排序的列既可以是表中的列名,也可以是select
语句后指定的列名。 - Asc升序[默认]、Desc降序
- ORDER BY子句应位于SELECT语句的结尾。
-- 演示order by使用
-- 对数学成绩排序后输出【升序】。
SELECT * FROM student
ORDER BY math;
-- 对总分按从高到低的顺序输出 [降序] -- 使用别名排序
SELECT `name` , (chinese + english + math) AS total_score FROM student
ORDER BY total_score DESC;
-- 对姓韩的学生成绩[总分]排序输出(升序) where + order by
SELECT `name`, (chinese + english + math) AS total_score FROM student
WHERE `name` LIKE '韩%'
ORDER BY total_score;
合计/统计函数
count
-- 演示mysql的统计函数的使用
-- 统计一个班级共有多少学生?
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于90的学生有多少个?
SELECT COUNT(*) FROM student
WHERE math > 90
-- 统计总分大于250的人数有多少?
SELECT COUNT(*) FROM student
WHERE (math + english + chinese) > 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(math) FROM student;
-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;
-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student;
-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/ COUNT(*) FROM student;
SELECT SUM(`name`) FROM student;
-- 演示avg的使用
-- 练习:
-- 求一个班级数学平均分?
SELECT AVG(math) FROM student;
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;
-- 演示max 和 min的使用
-- 求班级最高分和最低分(数值范围在统计中特别有用)
SELECT MAX(math + english + chinese), MIN(math + english + chinese)
FROM student;
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre
FROM student;
sum
Sum函数返回满足where条件的行的和 一般使用在数值列。
avg
AVG函数返回满足where条件的一列的平均值
max/min
Max/min函数返回满足where条件的一列的最大/最小值
使用group by 子句对列进行分组
使用having 子句对分组后的结果进行过滤
group by 用于对查询的结果分组统计,(示意图)
having 子句用于限制分组显示结果
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, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
SELECT * FROM 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 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 salgrade;
SELECT * FROM dept;
SELECT * FROM emp;
# 演示group by + having
GROUP by用于对查询的结果分组统计, (示意图)
-- having子句用于限制分组显示结果.
-- ?如何显示每个部门的平均工资和最高工资
-- 老韩分析: avg(sal) max(sal)
-- 按照部分来分组查询
SELECT AVG(sal), MAX(sal) , deptno
FROM emp GROUP BY deptno;
-- 使用数学方法,对小数点进行处理
SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno
FROM emp GROUP BY deptno;
-- ?显示每个部门的每种岗位的平均工资和最低工资
-- 老师分析 1. 显示每个部门的平均工资和最低工资
-- 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, job
FROM emp GROUP BY deptno, job;
-- ?显示平均工资低于2000的部门号和它的平均工资 // 别名
-- 老师分析 [写sql语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在1的结果基础上,进行过滤,保留 AVG(sal) < 2000
-- 3. 使用别名进行过滤
SELECT AVG(sal), deptno
FROM emp GROUP BY deptno
HAVING AVG(sal) < 2000;
-- 使用别名
SELECT AVG(sal) AS avg_sal, deptno
FROM emp GROUP BY deptno
HAVING avg_sal < 2000;
字符串相关函数
-- 演示字符串相关函数的使用 , 使用emp表来演示
-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp;
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp;
-- INSTR (string ,substring ) 返回substring在string中出现的位置,没有返回0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL;
-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp;
-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp;
-- LEFT (string2 ,length ) 从string2中的左边起取length个字符
-- RIGHT (string2 ,length ) 从string2中的右边起取length个字符
SELECT LEFT(ename, 2) FROM emp;
-- LENGTH (string ) string长度[按照字节]
SELECT LENGTH(ename) FROM emp;
-- REPLACE (str ,search_str ,replace_str )
-- 在str中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('hsp', 'hsp') FROM DUAL;
-- SUBSTRING (str , position [,length ])
-- 从str的position开始【从1开始计算】,取length个字符
-- 从ename 列的第一个位置开始取出2个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp;
-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM(' 韩顺平教育') FROM DUAL;
SELECT RTRIM('韩顺平教育 ') FROM DUAL;
SELECT TRIM(' 韩顺平教育 ') FROM DUAL;
-- 练习: 以首字母小写的方式显示所有员工emp表的姓名
-- 方法1
-- 思路先取出ename 的第一个字符,转成小写的
-- 把他和后面的字符串进行拼接输出即可
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2)) AS new_name
FROM emp;
SELECT CONCAT(LCASE(LEFT(ename,1)), SUBSTRING(ename,2)) AS new_name
FROM emp;
数学相关函数
rand()
返回一个随机浮点值v,范围在0到1之间(即,其范围为0<=v<=1.0)。若已指定一个整数参数N, 则它被用作种子值,用来产生重复序列。
-- 演示数学相关函数
-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL;
-- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL;
-- CEILING (number2 ) 向上取整, 得到比num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;
-- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的8, 转成 2进制输出
SELECT CONV(8, 10, 2) FROM DUAL;
-- 下面的含义是 8 是16进制的8, 转成 2进制输出
SELECT CONV(16, 16, 10) FROM DUAL;
-- FLOOR (number2) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL;
-- HEX (DecimalNumber ) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL;
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL;
-- RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 1. 如果使用 rand() 每次返回不同的随机数 ,在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果seed不变,该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT CURRENT_TIMESTAMP() FROM DUAL;
- 点赞
- 收藏
- 关注作者
评论(0)