【Mysql学习笔记】1

一、Mysql5.7安装配置

下载后会得到zip 安装文件
解压的路径最好不要有中文和空格
这里我解压到 D:\hspmysql\mysql-5.7.19-winx64 目录下【根据自己的情况来指定目录,尽量选择空间大的盘】

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

在D:\hspmysql\mysql-7.19-winx64 目录下下创建 my.ini 文件, 需要我们自己创建

[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

使用管理员身份打开 cmd , 并切换到 D:\hspmysql\mysql-5.7.19-winx64\bin 目录下, 执行mysqld -install
初始化数据库: mysqld --initialize-insecure --user=mysql

如果执行成功，会生成 data目录:

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

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

use mysql;

update user set authentication_string=password('hsp') where user='root' and Host='localhost';

老韩解读: 上面的语句就是修改 root用户的密码为 hsp

注意：在后面需要带分号，回车即可执行该指令

执行: flush privileges; 刷新权限

退出: quit

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

#skip-grant-tables

重新启动mysql

net stop mysql

net start mysql

韩顺平提示: 该指令需要退出mysql ，在Dos下执行.

再次进入Mysql, 输入正确的用户名和密码

mysql -u root -p

密码正确，进入mysql

密码错误，提示如下信息

安装过程中，一定要按照老师的步骤来，不然会错误.

如果真的错误了，清除mysql服务, 再次安装.

二、连接mysql

1.命令行连接mysql

2.Navicat图形化操作数据库

Navicat 安装&配置教程

安装步骤
下载后会得到exe 安装文件
使用管理员身份安装
这里我安装到 D:\program\Navicat 15 for MySQL 目录下【根据自己的情况来指定目录】
双击运行, 配置连接

和老师讲解的命令行是一样，只是变成的图形化.

输入正确的密码即可登录MySQL

韩顺平老师提示：一定要保证MySQL 服务是运行的状态

3.SQLyog的安装和使用

下载后会得到SQLyog-13.6-0.x64Community.exe 安装文件
使用管理员身份安装
这里我安装到 D:\program\SQLyog Community 目录下【根据自己的情况来指定目录】
双击运行, 配置连接

输入正确的密码即可登录MySQL

韩顺平老师提示：一定要保证MySQL 服务是运行的状态

三、Mysql的基础介绍

1.Mysql的三层结构

1.所谓安装Mysql数据库，就是在主机安装一个数据库管理系统(DBMS),这个管理程序可以管理多个数据库。DBMS(database manage system)

2. 一个数据库中可以创建多个表，以保存数据(信息)。

3.数据库管理系统(DBMS)、数据库和表的关系如图所示:示意图

2.数据以表的形式存放

3.sql语句分类

DDL :数据定义语句[create表，库]

DML :数据操作语句[增加insert，修改update，删除delete]

DQL :数据查询语句[select]

DCL :数据控制语句[管理数据库 grant revoke]

四、体会通过Java操作Mysql

1.创建一个商品hsp goods表，选用适当的数据类型

2.添加2条数据

3.删除表goods

五、数据库的基本操作

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] db_ name

[create_ specification [, create_ specification] ..

create_ specification:

[DEFAULT] CHARACTER SET charset_ name

[DEFAULT] COLLATE collation_ name

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

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

练习:

1.创建一个名称为hsp db01的数据库。[图形化和指令演示]

2.创建个使用utf8字符集的hsp db02数据库

3.创建一个使用utf8字符集，并带校对规则的hsp db03数据库

2.查看删除数据库

显示数据库语句:SHOW DATABASES

显示数据库创建语句:SHOW CREATE DATABASE db name

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

练习: database02.sql

1.查看当前数据库服务器中的所有数据库

2.查看前面创建的hsp db01数据库的定义信息

3.删除前面创建的hsp db01数据库

#演示删除和查询数据库
#查看当前数据库服务器中的所有数据库
SHOW DATABASES
#查看前面创建的hsp_db01数据库的定义信息
SHOW CREATE DATABASE `hsp_db01`
#老师说明 在创建数据库,表的时候，为了规避关键字，可以使用反引号解决
CREATE DATABASE `CREATE`
#删除前面创建的hsp_db01数据库
DROP DATABASE hsp_db01

3.备份和恢复数据库

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

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

恢复数据库(注意:进入SQLyog再执行)

Source文件名.sql

练习: database03.sql备份hsp db02和hsp db03库中的数据，并恢复

备份数据库的表

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

六、库的表的基本操作

1.创建表

CREATE TABLE tablename

(

        field1 datatype,

        field2 datatype,

        field3 datatype

)character set字符集 collate校对规则 engine引擎

field:指定列名 datatype: 指定列类型(字段类型)

character set :如不指定则为所在数据库字符集

collate:如不指定则为所在数据库校对规则

engine:引擎(这个涉及内容较多，后面单独讲解)

注意:yh_db02创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相
应的列类型。例:(图形化指令)

通过指令

CREATE TABLE `user` (id INT,`name` VARCHAR(255),`password` VARCHAR(255),`birthday` DATE)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

2.修改表和删除表

指令

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

ALTER TABLEtablename --添加列

ADD (column da ta type [DEFAULT expr]

[，column da tatype] . . .) ;

ALTER TABLE tablename --修改列

MODIFY(column da ta type [DEFAULT expr]

[，column datatype] . . .) ;

ALTER TABLE tablename--删除列

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
DESC employee -- 显示表结构，可以查看表的所有列
--  修改job列，使其长度为60。
ALTER TABLE emp MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
--  删除sex列。
ALTER TABLE empDROP sex
--  表名改为employee。
RENAME TABLE emp TO employee
--  修改表的字符集为utf8 
ALTER TABLE employee CHARACTER SET utf8
--  列名name修改为user_name
ALTER TABLE employee CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''
DESC employee

七、Mysql常用的数据类型

常用的列类型 int double decimal char varchar text datetime timestamp

1.如何定义一个无符号的整数

int.sql文件

create table t10 (id tinyint ); //默认是有符号的

create table t11 (id tinyint unsigned );无符号的

#演示整型的是一个
#使用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 t3INSERT INTO t4 VALUES(255);
SELECT * FROM t4;

2.数值型(bit)的使用

1.基本使用

mysq|> create table t02 (num bit(8));

mysq|> insert into t02 (1, 3);

mysq|> insert into t02 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); 
SELECT * FROM t05;
SELECT * FROM t05 WHERE num = 1;

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

1. FLOAT/DOUBLE (UNSIGNED)

Float单精度精度，Double 双精度.

2. 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;

4.字符串的基本使用

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

VARCHAR(size) 0~65535（理论上能存的字节数）可变长度字符串最大65532字节[utf8编码最大21844字符1-3个字节用于记录大小] (utf8 3个字节1个字符 / gbk 2个字节1个字符

#演示字符串类型使用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;

4.1字符串使用细节

1. 细节1 (字符所占用的字节是根据编码格式而改变的)

char(4) //这个4表示字符数(最大255)，不是字节数,不管是中文还是字母都是放四个按字符计算，根据编码格式计算字节大小

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

#演示字符串类型的使用细节
#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;

2.细节2

char(4)是定长(固定的大小)，就是说，即使你插入'aa' ,也会占用分配的4个字符的空间

varchar(4)是变长，就是说，如果你插入了'aa' ,实际占用空间大小并不是4个字符，而是按照实际占用空间来分配(说明: varchar本身还需要占用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 0-2^24或者LONGTEXT 0~2^32

#如果varchar 不够用，可以考虑使用mediumtext 或者longtext, 
#如果想简单点，可以使用直接使用text
CREATE TABLE t13( content TEXT, content2 MEDIUMTEXT , content3 LONGTEXT);
INSERT INTO t13 VALUES('韩顺平教育', '韩顺平教育100', '韩顺平教育1000~~');
SELECT * FROM t13;

5.日期时间类型的基本使用

CREATE TABLE birthday6

( t1 DATE, t2 DATETIME,

t3 TIM ESTAMP NOT NULL DEFAULT

CURRENT TIMESTAMP ON UPDATE

CURRENT TIMESTAMP ) ; timestamp时间戳

mysq|> INSERT INTO birthday (t1,t2)

VALUES('2022-11-11 2022-11-11 10:10:10');

#演示时间相关的类型
#创建一张表, 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列会自动的以当前时间进行更新

6.创建表的练习

创建一个员工表emp，选用适当的数据类型

#创建表的课堂练习
-- 字段	属性
-- 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`;

八、数据库CRUD语句

1. Insert语句(添加数据)

INSERT INTO table name (colum [， column...])
VALUES (value[， value...] ) ;

#练习insert 语句
-- 创建一张商品表goods (id  int , goods_name varchar(10), price double );
-- 添加2条记录
CREATE TABLE `goods` (id INT ,goods_name VARCHAR(10), -- 长度10price 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), -- 长度10price DOUBLE NOT NULL DEFAULT 100 );

1.1Insert细节说明

1.插入的数据应与字段的数据类型相同。

比如把'abc'添加到int类型会错误

2.数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加

入到长度为40的列中。

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

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

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

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

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

8.默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错，如果希望有默认值，需要在创建表的时候添加进去

#说明insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同。
--       比如 把 'abc' 添加到 int 类型会错误
INSERT INTO `goods` (id, goods_name, price) VALUES('韩顺平', '小米手机', 2000);--底层会尝试把字符串转为int型，如果能转则不会报错，如果是'30'就不会报错-- 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. 列可以插入空值[前提是该字段允许为空]，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;

2. Update语句(更新数据)

UPDATE tb1_name

SET col_name1 = expr1 [ ，col name2=expr2 . . .]

[WHERE where defini tion ]

-- 演示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;

2.1Update语句使用细节

1. UPDATE语法可以用新值更新原有表行中的各列。

2. SET子句指示要修改哪些列和要给予哪些值。

3. WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

4.如果需要修改多个字段，可以通过set字段1 =值1,字段2 =值2...

3. Delete语句(删除数据)

delete from tb1 name

[WHERE where defini tion]

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

3.1 Delete语句使用细节

1.如果不使用where子句，将删除表中所有数据。

2.Delete语句不能删除某一列的值(可使用update 设为null或者")

3.使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop
table语句。drop table表名;

4. Select语句(查找数据)(单表)

基本语法

SELECT [DISTINCT] * | { columnl，co1umn2. column3. . }

FROM tablename;

注意事项(创建测试表学生表)

1. Select指定查询哪些列的数据。

2. column指定列名。

3.*号代表查询所有列。

4.From指定查询哪张表。

5.DISTINCT可选，指显示结果时，是否去掉重复数据

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*| { column1| expression, column2 | expression,...}

FROM tablename;

在select语句中可使用as语句

SELECT column_name as 别名 from 表名;

-- select 语句的使用SELECT * FROM student;
-- 统计每个学生的总分
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子句中经常使用的运算符

-- 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 studentWHERE math >60 AND id > 4
-- 查询英语成绩大于语文成绩的同学
SELECT * FROM studentWHERE english > chinese
-- 查询总分大于200分 并且 数学成绩小于语文成绩,的姓赵的学生.
-- 赵% 表示 名字以赵开头的就可以
SELECT * FROM studentWHERE (chinese + english + math) > 200 AND math < chinese AND `name` LIKE '赵%'
-- 查询英语分数在 80－90之间的同学。
SELECT * FROM studentWHERE english >= 80 AND english <= 90;
SELECT * FROM studentWHERE english BETWEEN 80 AND 90; -- between .. and .. 是 闭区间
-- 查询数学分数为89,90,91的同学。
SELECT * FROM student WHERE math = 89 OR math = 90 OR math = 91;-- 等价与下面的IN语句
SELECT * FROM student WHERE math IN (89, 90, 91);
-- 查询所有姓韩的学生成绩。
SELECT * FROM student WHERE `name` LIKE '韩%'
-- 查询数学分>80，语文分>80的同学
SELECT * FROM student WHERE math > 80 AND chinese > 80

SELECT * FROM student WHERE chinese BETWEEN 80 AND 90SELECT * FROM studentWHERE (math + chinese + english) IN (189,190,191)SELECT * FROM studentWHERE `name` LIKE '李%' OR `name` LIKE '宋%'SELECT * FROM studentWHERE (math - chinese) > 0

使用order by子句排序查询结果

SELECT column1， column2. column3 ...
FROM table;
order by colum asc | desc,...

1. Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。
2. Asc 升序[默认]、Desc 降序
3. 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 studentWHERE `name` LIKE '韩%'ORDER BY total_score;

九、数据库函数

1.统计函数

合计/统计函数- count

Count返回行的总数

Select count(*)I count (列名) from tablename

[WHERE where defini tion]

-- 统计一个班级共有多少学生？
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于90的学生有多少个？
SELECT COUNT(*) FROM studentWHERE math > 90
-- 统计总分大于250的人数有多少？
SELECT COUNT(*) FROM studentWHERE (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

Sum函数返回满足where条件的行的和一般使用在数值列(否则会报错)

Select sum (列名) {,sum (列名).. from tablename

[WHERE where_definition]

-- 演示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(列名) {, avg(列名...} from tablename
[WHERE where defini tion]

-- 演示avg的使用
-- 练习：
-- 求一个班级数学平均分？
SELECT AVG(math) FROM student;
-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;

合计函数-max/min

Max/min函数返回满足where条件的一列的最大/最小值
Select max (列名) from tablename
[WHERE where defini tion]

-- 演示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_socreFROM student;

分组查询-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, '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 deptnoHAVING AVG(sal) < 2000;
-- 使用别名		
SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptnoHAVING avg_sal < 2000;

2.字符串相关函数

-- 演示字符串相关函数的使用  ， 使用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_nameFROM emp;  SELECT CONCAT(LCASE(LEFT(ename,1)),  SUBSTRING(ename,2)) AS new_nameFROM emp;

3.数学相关函数

-- 演示数学相关函数-- 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;

4.时间相关函数

-- 日期时间相关函数-- 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), send_time DATETIME);-- 添加一条记录
INSERT INTO mes VALUES(1, '北京新闻', CURRENT_TIMESTAMP()); 
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());SELECT * FROM mes;
SELECT NOW() FROM DUAL;-- 上应用实例
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间.
SELECT id, content, DATE(send_time) FROM mes;
-- 请查询在10分钟内发布的新闻, 思路一定要梳理一下.
SELECT * FROM mesWHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()SELECT * FROM mesWHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE) -- 请在mysql 的sql语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
-- 请用mysql 的sql语句求出你活了多少天? [练习] 1986-11-11 出生
SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;
-- 如果你能活80岁，求出你还能活多少天.[练习] 1986-11-11 出生
-- 先求出活80岁 时, 是什么日期 X
-- 然后在使用 datediff(x, now()); 1986-11-11->datetime
-- INTERVAL 80 YEAR ： YEAR 可以是 年月日，时分秒
-- '1986-11-11' 可以date,datetime timestamp 
SELECT DATEDIFF(DATE_ADD('1986-11-11', INTERVAL 80 YEAR), NOW()) FROM DUAL;SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL;
-- unix_timestamp() : 返回的是1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日
-- 意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
--   
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;SELECT * FROM mysql.user \G

5.加密和系统函数

-- 演示加密函数和系统函数-- USER()	查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL; -- 用户@IP地址
-- DATABASE()	查询当前使用数据库名称
SELECT DATABASE();-- MD5(str)	为字符串算出一个 MD5 32的字符串，常用(用户密码)加密
-- root 密码是 hsp -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('hsp') FROM DUAL;
SELECT LENGTH(MD5('hsp')) FROM DUAL;-- 演示用户表，存放密码时，是md5
CREATE TABLE hsp_user(id INT , `name` VARCHAR(32) NOT NULL DEFAULT '', pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hsp_user VALUES(100, '韩顺平', MD5('hsp'));
SELECT * FROM hsp_user; -- csdnSELECT * FROM hsp_user  -- SQL注入问题WHERE `name`='韩顺平' AND pwd = MD5('hsp')  -- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密SELECT PASSWORD('hsp') FROM DUAL; -- 数据库的 *81220D972A52D4C51BB1C37518A2613706220DAC-- select * from mysql.user \G 	从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表 
SELECT * FROM mysql.user

6.流程控制函数

# 演示流程控制语句# IF(expr1,expr2,expr3)	如果expr1为True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE, '北京', '上海') FROM DUAL;
# IFNULL(expr1,expr2)	如果expr1不为空NULL,则返回expr1,否则返回expr2
SELECT IFNULL( NULL, '韩顺平教育') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果expr1 为TRUE,则返回expr2,如果expr2 为t, 返回 expr4, 否则返回 expr5SELECT CASE WHEN TRUE THEN 'jack'  -- jackWHEN FALSE THEN 'tom' ELSE 'mary' END-- 1. 查询emp 表, 如果 comm 是null , 则显示0.0
--    老师说明，判断是否为null 要使用 is null, 判断不为空 使用 is not
SELECT ename, IF(comm IS NULL , 0.0, comm)FROM emp;
SELECT ename, IFNULL(comm, 0.0)FROM emp;
-- 2. 如果emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
--     如果是 SALESMAN 则显示 销售人员，其它正常显示SELECT ename, (SELECT CASE WHEN job = 'CLERK' THEN '职员' WHEN job = 'MANAGER' THEN '经理'WHEN job = 'SALESMAN' THEN '销售人员' ELSE job END) AS 'job'FROM emp; SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;