【韩老师零基础30天学会Java 24】mysql安装，数据类型：数值，文本，日期时间，创建备份数据库和表，crud，排序统计函数，分组。

mysql介绍 和 安装

创建
查看、删除数据库备份恢复数据库

统计函数

时间日期

字符串函数

数学函数

流程控制

约束

• not null
  primary key
  unique
  foreign key
  check 检测
  自增长

主键索引

• 唯一索引(UNIQUE)

• 普通索引(INDEX)

• 全文索引

事务

3个基本命令

mysql5.5 mysql5.6 mysql5.7(稳定) mysql8更高版本

使用命令行窗口连接MYSQL数据库

mysql -h 主机名 -P 端口 -u 用户名 -p密码
mysql -h localhost -P 3306 -u root -p123456

• p和密码之间无空格

启动mysql数据库的常用方式:[Dos命令]

1. 服务方式启动（界面)
2. net stop mysql服务名
3. net start mysql服务名

下载解压

解压版：

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

如果安装过Mysql过程中，出错了或者想重新再来一把

sc delete rmysql #【删除已经安装好的 mysql】

2.解压的路径最好不要有中文和空格

4．添加环境变量︰电脑-属性-高级系统设置-环境变量，在 Path 环境变量增加 mysql的安装目录\bin目录，如下图

C:\MySoft\mysql-5.7.19-winx64\bin

配置初始化启动

5.在 D:\hspmysql\mysql-5.7.19-winx64目录下下创建my.ini文件。在5.5版本，都是 默认生成的这个文件。

• 实际工作中，3306 一般修改。

[client]
port=3306
default-character-set=utf8
[mysqld]
# 设置为自己MYSQL的安装目录
basedir=D:\hspmysql\mysql-5.7.19-winx64\
# 设置为MYSQL的数据目录
datadir=D:\hspmysql\mysql-5.7.19-winx64\data\
port=3306
character_set_server=utf8
#跳过安全检查
skip-grant-tables

1. 使用****管理员身份****打开 cmd , 并切换到 D:\hspmysql\mysql-5.7.19-winx64\bin 目录下, 执行mysqld -install

2. 初始化数据库: mysqld --initialize-insecure --user=mysql

3. 启动mysql 服务: net start mysql 【停止mysql服务指令 net stop mysql】, 如果成功:

在任务管理器，能找到这个服务

登录改密码重启

1. 进入mysql 管理终端: mysql -u root -p 【当前root 用户密码为 空】

2. 修改root 用户密码

use mysql;  
update user set authentication_string=password('hsp') where user='root' and Host='localhost';
老韩解读: 上面的语句就是修改 root用户的密码为 hsp
注意：在后面需要带 分号，回车即可执行该指令

执行: flush privileges; 刷新权限
退出: quit

1. 修改my.ini , 再次进入就会进行权限验证了

#skip-grant-tables    注销这句话

1. 重新启动mysql

net stop mysql

net start mysql

mysql -u root -p

使用

基本命令

Navicat 创建数据库，utf8，排序规则，utf8_bin

show databases;
use mysql;
show tables;

create database db02;
use db02;

CREATE TABLE users (
	id INT,
NAME VARCHAR ( 255 ),
address VARCHAR ( 255 ));

INSERT INTO user2 VALUES(1,'张三','北京')

三层结构

数据库三层结构-破除MySQL神秘
1.所谓安装Mysql数据库，就是在主机安装
-个数据库管理系统(DBMS)，这个管理
程序可以管理多个数据库。DBMS(datab
pase manage system)
2.一个数据库中可以创建多个表，以保存数据(信息).
3.数据库管理系统(DBMS)、数据库和表的关系如图所示:

表
视图
存储过程
函数
触发器
事件

DBMS 对应的 比如：mysqld.exe 当然还可能有其他的程序

数据库，就在配置的 安装目录的 data下：db01,db02

表就是：data目录里面。users.ibd 和 users.frm

数据库-表的本质仍然是文件

内存级别的数据库，常用 H2。磁盘和内存相结合的。

行 row

列 column

SQL语句分类
. DDL:数据定义语句[创建表 数据库]
· DML:数据操作语句[增加，修改，删除]
· DQL:数据查询语句[select ]
. DCL:数据控制语句[管理数据库，如管理用户权限]

• 授权 grant
• 撤回 revoke

JDBC 极简代码

mysql-connector-java-5.1.37-bin.jar

        //1. 使用反射，加载一下驱动的Class类。加载类，得到mysql连接
        Class.forName("com.mysql.jdbc.Driver");
		//2. DriverManager 获取 连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/hsp_db02", "root", "123456");

        //创建一个商品hsp_goods表, 选用适当的数据类型
        //添加2条数据
        //删除表goods

        //这里可以编写sql 【create , select , insert , update ,delete ...】
        //String sql = "create table hsp_goods ( id int, name varchar(32), price double, introduce text)" ;
        String sql = "insert into hsp_goods values(1, '华为手机', 2000, '这是不错的一款手机')";
        //String sql = "drop table hsp_goods" ;

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

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

CREATE TABLE hsp_goods (
	id INT,
	NAME VARCHAR ( 32 ),
	price DOUBLE,
	introduce text
)

drop table hsp_goods

创建数据库

CREATE DATABASE [IF NOT EXISTS] db name
CHARACTER SET xx
COLLATE xx

1. CHARACTER SET:指定数据库采用的字符集,如果不指定字符集，默认utf8

2. COLLATE:指定数据库字符集的校对规则(
   常用的utf8_bin(区分大小写)、utf8_general_ci（不区分）注意默认是utf8_general_ ci)

specification
n.
规格，规范，明细单，说明书；明确说明，详述；（申请专利用的）发明物说明书

collate
英
/kəˈleɪt
vt.
核对，校对；校勘

#删除数据库指令
DROP DATABASE hsp_db01;

#创建一个使用utf8字符集，并带校对规则的hsp_db03数据库
CREATE DATABASE hsp_db01;
create database test2 character set utf8 collate utf8_bin
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin

#校对规则 utf8_bin 区分大小 默认utf8_general_ci 不区分大小写。
#重要重要：如果不指定表的校对规则，默认使用数据库的。创建表的时候可以指定，指定后就确定，在修改没用的。

#不区分大小写。 TOM也会查出来。
SELECT *  
	FROM t1 
	WHERE NAME = 'tom'

查看删除数据库

显示数据库语句:
SHOW DATABASES

显示数据库创建语句:
SHOW CREATE DATABASE db_name
# 显示出查询。40100数据库版本，要在4以上，才能执行这个指令。
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */

数据库删除语句:
DROP DATABASE [IF EXISTS] db_name

备份恢复

备份数据库(注意:在DOS执行)）

• mysqldump 是在 安装目录bin下的一个程序，所以要 dos下执行。

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n >文件名.sql

mysqldump -u root -p -B hsp_db02 > d:\\bak.sql

source d:\\bak.sql

恢复数据库(注意:进入Mysql命令行再执行，就是cmd连接上mysql)
source文件名.sql

• 备份库的表，比：备份数据库 少了 -B

mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > d:\文件名.sql
mysqldump -u root -p123456 hsp_db02 hsp_goods > d:\backtables.sql

创建表

CREATE TABLE table_name(
	field1 datatype,
    field3 datatype
)character set 字符集 collate 校对规则 engine 引擎

field: 指定列名 
datatype: 指定列类型(字段类型)
character set :如不指定则为所在数据库字符集
collate:如不指定则为所在数据库校对规则
engine:引擎(后面单独讲解)

CREATE TABLE user2 (
	id INT,
	`name` VARCHAR ( 250 ),
	`password` VARCHAR ( 255 ),
	birthday DATE 
) CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE INNODB

练习一下

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; --`resume`不是关键字，无需引号。
-- 添加一条
INSERT INTO `emp`
	VALUES(100, '小妖怪', '男', '2000-11-11', 
		'2010-11-10 11:11:11', '巡山的', 3000, '大王叫我来巡山');

常用数据类型

分类
数据类型
说明

数值类型
文本、二进制类型
时间日期

数值类型

BIT(M)
位类型。M指定位数，默认值1，范围1-64

• （M+7）/8 个字节

TINYINT[UNSIGNED]占1个字节
带符号的范围是-128到127。无符号0到255。默认是有符号

• tiny int

SMALLINT [UNSIGNED] 2个字节
带符号是负的2^15到215-1,无符号0到2^16-1

• small int

MEDIUMINT[UNSIGNED]3个字节
带符号是负的2^23到223-1,无符号0到2^24-1

• medium int

INT[UNSIGNED]4个字节
带符号是负的2^31到231-1,无符号0到2^32-1

• int

BIGINT TUNSIGNED]8个字节
带符号是负的2^63到263-1,无符号0到2^64-1

• big int

FLOAT [UNSIGNED]
占用空间4个字节

• float

DOUBLE[UNSIGNED]
表示比float精度更大的小数,占用空间8个字节

• double

DECIMAL(M,D) [UNSIGNED]
定点数M指定长度，D表示小数点的位数，

• decimal

• 比如：把D写成0，把M写成最大。那就是 比 bigint还要大很多的整数。

medium
英
/ˈmiːdiəm
n.
媒介，媒体；方法，手段；（艺术创作）材料，素材；灵媒，巫师；培养基；环境；中等，中号；存储（或打印）介质；（颜料）溶剂（如油或水）；（品质、状态）中等，中庸
adj.
中等的，中间的，适中的；五分熟的，半熟的；（程度、强度或数量）平均的；（颜色）不深不浅的，适中的；（投球，投球手）中速的

• 从0开头的 就是 无符号的。
  • 如果 age 这样，可以使用 samllint。够用的情况下，尽量用 占用空间小的类型。
  • 如果是普通的标志：123等。最好用 TINYINT，当然下面的big(7) 也行。

类型	字节	最小值	最小值
		带符号的/无符号的	带符号的/无符号的
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

#使用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;

位类型使用

#演示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);  #b'11111111'，就是8个全1
SELECT * FROM t05;
SELECT * FROM t05 WHERE num = 1;

bit字段显示时，按照位的方式显示.
查询的时候仍然可以用使用添加的数值
如果一个值只有0,1可以考虑使用bit(1),可以节约空间位类型。

M指定位数,默认值1，范围1-64
使用不多

数值型(小数)

1. FLOAT/DOUBLE [UNSIGNED]Float单精度精度，Double双精度.

2. DECIMAL[M,D] [UNSIGNED]

· 可以支持更加精确的小数位。M是小数位数(精度)的总数，D是
小数点(标度)后面的位数。

• MD如果为：5.2 就是表示：最大为5位，最大2位是小数点。

· 如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。

• 如果D被省略,默认是0。如果M被省略，默认是10。

· 建议:如果希望小数的精度高，推荐使用decimal

DECIMAL

#创建表
CREATE TABLE t06 (
	num1 FLOAT,
	num2 DOUBLE,
	num3 DECIMAL(30,20));
#添加数据
#88.1235    88.12345678912345    88.12345678912345000000
INSERT INTO t06 VALUES(88.12345678912345, 88.12345678912345,88.12345678912345);
SELECT * FROM t06;

#decimal可以存放很大的数
CREATE TABLE t07 (
	num DECIMAL(65));#使用 BIGINT UNSIGNED ，依然不行。
INSERT INTO t07 VALUES(89999999333383883883...);

文本 二进制类型

CHAR(size) char(20)
固定长度字符串最大255

• char

VARCHAR(size) varchar(20)
可变长度字符串0~65535[即:2^16-1]

• var char
• 没法用完，预留了几个

TEXT LONGTEXT
文本Text 0~2^16 LONGTEXT 0~2^32

• text 和 carchar 长度等价
• longtext

BLOB LONGBLOB
二进制数据 BLOB 0~2^16-1 LONGBLOB 0~2^32-1

• blob

• long blob

• 还有：tiny text 和 tiny blob

• medium text 和 medium blog

• long text 和 long blog

字符串最大长度

CHAR(size)
固定长度字符串最大255字符

VARCHAR(size)
可变长度字符串最大65532字节【utf8编码最大21844字符 1-3字节用于记录大小】

• 不同的编码下，每一个字符 占用的字节 不同。
• 65532 / 3
  • 如果是 gbk 要 /2

#注释的快捷键 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;


CREATE TABLE t11 (
	`name` VARCHAR(32766)  CHARSET gbk
)CHARACTER SET utf8
	
	
CREATE TABLE t12 (
	`name` VARCHAR(21844)  CHARACTER SET utf8
)

CHARSET == CHARACTER SET  后面+ 编码

字符串使用细节

char(4)//这个4表示字符数(最大255)，不是字节数,不管是中
文还是字母都是放四个,按字符计算.

varchar(4)//这个4表示字符数
不管是字母还是中文都以定义好的表的编码
,来存放数据.
不管是中文还是英文字母,都是最多存放4个,是按照字符来存放的.

2.细节2
char(4)是定长，就是说，即使你插入’aa’，也会占用分配的4个字符
varchar(4)是变长，就是说,
如果你插入了’aa’,实际占用空间大小并不是4
个字符，而是按照实际占用空间来分配(老韩说明:varchar.
本身还需要占用1-3个字节来记录存放内容长度)

• 1-3 个字节，是预占用的。根据数据大小，还决定 预占用大小。

3.细节3
什么时候使用char,什么时候使用varchar
1.如果数据是定长,推荐使用char,比如md5的密码,邮编,手机号,身份证号码等. char(32)
2.如果一个字段的长度是不确定,我们使用varchar,比如留言,文章

查询速度:char > varchar

4.细节4
在存放文本时，也可以使用Text数据类型.可以将TEXT列视为VARCHAR列,注意Text不能有
默认值.大小0—2^16字节
如果希望存放更多字符，可以选择
MEDIUMTEXT O-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)); #如果表的编码是 utf8 varchar(size) size = (65535-3) / 3 = 21844 最大这个字符大小。

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~~');
# 和 varchar 还是有区别的：韩顺平教育100 为 18B。3*5+3=18。其他和varchar一样，是动态字节。
SELECT * FROM t13;

日期时间

DATE/DATETIME/TimeStamp

• date 日期类型(YYYY-MM-DD) 3个字节
• time 只能存放时间 3个字节
• date timem 日期时间 (YYYY-MM-DD HH:MM:SS), 8个字节
• TimeStamp表示时间戳,它可用于自动记录insert.update操作的时间 4个字节
  • 配置后，更新时 可以自动更新。添加时 自动插入。
• year 存放年 1个字节。

create table birthday6 (
	t1 date,
	t2 datetime,
	t3 timestamp not null default current_timestamp --不为空，默认当前时间戳，在更新的时候 更新为当前时间戳。
	 on update current_timestamp
);

INSERT INTO birthday6 ( t1, t2 )
VALUES
	( '2022-11-11', '2022-11-11 10:10:10' );

-- 此时 t3字段更新成了 当前时间 2022-12-27 22:08:10
-- TimeStamp在lnsert和update时，自动更新

常用的有：

• tiny int 存储标志

• int

• double

• decimal

• char

• varcha

• text

• blob

• datatime

• timestamp

修改表和CRUD

修改表

使用ALTER TABLE语句追加,修改，或删除列的语法.

ALTER TABLE tablename
ADD	(column datatype [DEFAULT expr] [,column datatype]...);
或者：
MODIFY	(column datatype);
或者：
DROP	(column) ;

desc 表名。-- 查看表结构
修改表名: Rename table 表名 to 新表名
修改表字符集:alter table 表名 character set 字符集;

• 在上面创建表的 练习一下的基础上

--  员工表emp的上增加一个image列，varchar类型(要求在resume后面)。
ALTER TABLE emp 
	ADD image VARCHAR(32) NOT NULL DEFAULT '' 
	AFTER RESUME --after
ALTER TABLE empy ADD ( image VARCHAR ( 255 ) ); --我这样写，竟然可以。但修改时 不可以。

DESC employee -- 显示表结构，可以查看表的所有列

--  修改job列，使其长度为60。modify 旧字段 新类型
ALTER TABLE emp 
	MODIFY job VARCHAR(60) NOT NULL DEFAULT ''

--  删除sex列。
ALTER TABLE emp DROP sex

--  表名改为employee。rename table 旧名 to 新名
RENAME TABLE emp TO employee

--  修改表的字符集为utf8 
ALTER TABLE employee CHARACTER SET utf8

--  列名name修改为user_name。
--  change 旧名 新名 新名类型
ALTER TABLE employee 
	CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''

CRUD

R 可以叫 read

1. Insert语句
   (添加数据

2. Update语句
   (更新数据

3. Delete语句
   (删除数据

4. Select语句
   (查找数据

Insert

INSERT INTO
tablename [ (column [, column. . .]) ]
VALUES
(value [ , value. . .]);

1. 插入的数据应与字段的数据类型相同。
   比如把’abc’添加到int类型会错误

2. mysql底层为尝试转换。 ‘30’ 加了引号也可以当做int

3. 数据的长度应在列的规定范围
   内，例如:不能将一个长度为801
   的字符串加
   入到长度为40的列中。

4. 在values中列出的数据位置必须与被加入的列的排列位置相对
   )应。

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

6. 字符类型，必须用引号。

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

8. insert into tab name(列名…) values (),() 形式添加多条
   条记录

INSERT INTO `goods` (id, goods_name, price) 
	VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800);

7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称

8. 默认值的使用,当不给某个字段值时，如果有默认值就会添加
   否则报错

Update

UPDATE tbl_name
SET col_name1 = expr1 [,col_name2 = expr2 ... ]
[WHERE where_definition]

UPDATE employee 
	SET salary = 3000 
	WHERE user_name = '小妖怪'
	
UPDATE employee 
	SET salary = salary + 1000 , job = '出主意的'
	WHERE user_name = '老妖怪' 

Delete

使用delete语句删除表中数据。

不加条件，删除表中所有记录。

delete from tbl_name
[WHERE where_definition]

--  删除表中名称为’老妖怪’的记录。
DELETE FROM employee 
	WHERE user_name = '老妖怪';

1. 如果不使用where子句，将删除表中所有数据。
2. Delete语句不能删除某一列的值(可使用update设为null 或者"
3. 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop
   table语句。drop table 表名;

UPDATE employee SET job = '' WHERE user_name = '老妖怪';

-- 要删除这个表
DROP TABLE employee;

Select

去重 as 记录求和

SELECT [DISTINCT] * | { column1 , column2. column3. .}
FROM tablename;

*号所有列。
DISTINCT可选，指显示结果时,是否去掉重复数据

-- 要查询的记录，每个字段都相同，才会去重。如果任一一个 不相同，不去重。
SELECT DISTINCT `name`, english FROM student;

SELECT co1umnname as 别名 from 表

-- 统计每个学生的总分
SELECT `name`, (chinese+english+math) FROM student;

-- 使用别名表示学生分数。
SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score 
	FROM student;

运算符

比较运算符

大于、小于、大于(小于)等于、不等于
> < <= >= =
<> !=

BETWEEN ...AND... 显示在某一区间的值

IN(set)
显示在in列表中的值,例:in(100,200)

LIKE '张pattern'
NOT LIKE
模糊查询

IS NULL 判断是否为空

逻辑运算符

and
多个条件同时成立

or
多个条件任一成立

not
不成立，例: where not(salary>100); 取反 薪水>100，就是 不大于100的

-- 查询总分大于200分 并且 数学成绩小于语文成绩,的姓赵的学生.
-- 赵% 表示 名字以赵开头的就可以
SELECT * FROM student
	WHERE (chinese + english + math) > 200 AND 
		math < chinese AND `name` LIKE '赵%'

百分号(%)	替代0个、1个或多个字符
下划线(_)	仅替代一个字符

-- 查询英语分数在 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);

案例SQL

-- 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 column1 , column2 , column3
FROM table;
order by column asc \ desc,

-- 对姓韩的学生成绩[总分]排序输出(升序) where + order by
SELECT `name`, (chinese + english + math) AS total_score FROM student
	WHERE `name` LIKE '韩%'
	ORDER BY total_score;

统计函数

count *和列的区别

select count(*) | count(列名)
from tablename
[WHERE where_definition]

-- 统计总分大于250的人数有多少？
SELECT COUNT(*) FROM student
	WHERE (math + english + chinese) > 250
	
-- count(*) 和 count(列) 的区别 
-- 解释 :count(*) 返回满足条件的记录的行数
-- count(列): 统计满足条件的某列有多少个，但是会排除 为null的情况。count(id) 就过滤id为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

合计函数

Sum函数返回满足where条件的行的和-一般使用在数值列

Select sum(列名) {, sum(列名)...} 
from tablename
[WHERE where definition]

-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;

-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student; -- 只会返回一个值

SELECT math + english + chinese FROM student; -- 这样是 每一行 相加在一起，有8条数据，就8行

-- 统计一个班级语文成绩平均分
SELECT SUM(chinese)/ COUNT(*)  FROM student;

-- SELECT SUM(`name`) 没意义，返回0

Avg和 Max和Min

-- 求一个班级数学平均分？
SELECT AVG(math) FROM student;
-- 求一个班级总分平均分。相当于 先求出 比如总共8行数据的 总分。然后 相加在一起。在 除以/8
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;
	
-- 显示具体的人是谁，可以用两个查询，下面查了最高分，最低分为 asc
SELECT *,( math + english + chinese ) AS total 
FROM
	student 
ORDER BY
	total DESC 
	LIMIT 1

练习的基本表

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);

-- 工资级别
#工资级别表
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);

group by

SELECTcolumn1 , column2. column3. . 
FROM table
group by column having...

group by用于对查询的结果分组统计,(示意图)

having子句用于限制分组显示结果.

• 分组后的结果进行过滤

-- 这样求的是 整个表的平均和最大值。
SELECT
	AVG( sal ),MAX( sal ) 
FROM emp

-- ?如何显示每个部门的平均工资和最高工资
-- 使用数学方法，对小数点进行处理
SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno
	FROM  emp GROUP BY deptno;
-- 2916.66,5000.00,10   10号部门 和 20号部门。平均和最大值。如果没有编号为40的，则不会查询出来。
-- 2443.75,3000.00,20


-- ?显示每个部门的每种岗位的平均工资和最低工资
-- 老师分析 1. 显示每个部门的平均工资和最低工资
--          2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, job
	FROM  emp GROUP BY deptno, job;
-- 10号部门有3个岗位。20号部门，也有3个岗位。
-- 1300.000000,1300.00,10,CLERK
-- 2450.000000,2450.00,10,MANAGER
-- 5000.000000,5000.00,10,PRESIDENT


-- ?显示平均工资低于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;

• 分组的规则是：使用了聚合函数 avg min max的可以直接用。如：AVG(sal)

  • 没有使用聚合函数的如： SELECT deptno，则一定要 分组。

• 就是 表里面有 假如有10个人。分别在3个部门。就可以 根据部门分组。查出3条数据。然后 在对这 3条数据过滤。