启动数据库服务指令：

连接到MySQL的指令：（登录前，需要保证服务启动）

数据库三层结构
可以理解为客户端发送sql指令，数据库管理系统通过端口3306监听到该条指令，翻译后操作数据库表。

#使用指令创建数据库 
CREATE DATABASE hsp_db01; 
#删除数据库指令 
DROP DATABASE hsp_db01 
#创建一个使用 utf8 字符集的 hsp_db02 数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8 
#创建一个使用 utf8 字符集，并带校对规则的 hsp_db03 数据库 
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin 
#校对规则 utf8_bin 区分大小 默认 utf8_general_ci 不区分大小写

查看删除数据库
老师说明 在创建数据库,表的时候，为了规避关键字，可以使用反引号解决

#演示删除和查询数据库 
#查看当前数据库服务器中的所有数据库 
SHOW DATABASES 
#查看前面创建的 hsp_db01 数据库的定义信息 SHOW CREATE DATABASE `hsp_db01` 

#删除前面创建的 hsp_db01 数据库 
DROP DATABASE hsp_db01

备份恢复数据库
备份数据库，在管理员模式下运行
恢复数据库：在MySQL命令行执行,进入命令：
mysql -u root -p

#备份
mysqldump -u root -pliying -B liying_db02 liying_db03 > d:\\bak.sql
 
# 恢复
source d:\\bak.sql

数据库的表

创建表

注意：用MySQL关键字如name作列名时，可以用``括住

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

列类型
MySQL的列类型即MySQL的数据类型

1. 数值类型：
   整型，
   tinyint 一个字节
   smallint 2个字节
   mediumint 3个字节
   int 4个字节（最常用）
   bigint 8个字节
   小数类型
   float 单精度 4个字节
   double 双精度 8个字节 (常用）
   decimal[M, D] 大小不确定 （最常用）
2. 文本类型（字符串类型）
   char 0- 255 一个字节 （最常用）
   varchar 0-65535 两个字节 （最常用）
   text 0- 2¹⁶ - 1 两个字节 （最常用）
   longtext 0- 2³² - 1 四个字节
3. 二进制数据(使用不多)
   blob 0 - 2¹⁶ - 1
   longblob 0 - 2³² - 1
4. 日期类型
   data [日期 年月日]
   time [时间 时分秒]
   datatime [年月日时分秒 YYYY-MM-DD HH:MM:SS] （最常用）
   timestamp: [时间戳] （最常用）
   year [年]
   
   
   数值型（整数）的基本使用

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

数值型（小数）的基本使用

字符串的基本使用

• 注意：varchar的最大长度是65532字节（三个字节记录大小），不同的编码类型，可以容纳的字符数不同，如utf-8的大小为三字节，所以可以容纳的字符数为21844字符

  
  

• varchar占用的空间看编码的类型，如utf-8单字符是三字节，gbk是二字节。

  
  
• 所以char可能造成空间的浪费，
• varchar（）相当于规定最大长度
• varchar占用的空间看实际占用的空间，不一定
  
  
  
  
  日期类型的使用
  

修改表

DESC employee -- 显示表结构，可以查看表的所有列
ALTER TABLE employee 
CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''

操作表的内容

数据库 C[create]R[read]U[update]D[delete]语句
insert语句

说明 insert 语句的细节
-- 1.插入的数据应与字段的数据类型相同，（如果要接收的类型是int，输入'30'，会添加成功，MySQL底层会尝试将其转换为int，会成功。）
-- 2. 数据的长度应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
--3. 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。
-- 4. 字符和日期型数据应包含在单引号中。
-- 5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null)
-- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录
-- 7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
-- 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错 -- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给 null -- 如果我们希望指定某个列的默认值，可以在创建表时指定。

update语句

select 语句

• distinct 取消重复， 只显示一个

• 使用表达式对查询的列进行运算 ：

  
  

• 在 select 语句中可使用 as 语句

  
  

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

• order by 语句

• 统计/合计函数
  

• count() 和 count(列) 的区别
  -- 解释 :count() 返回满足条件的记录的行数
  -- count(列): 统计满足条件的某列有多少个，但是会排除 为 null 的情况

• sum函数

  
  

• avg函数

  
  

• max/min函数

  
  

• 分组统计 group-by

  
  

• having相当于group by之后进行过滤，相当于where（个人理解）

• 字符串函数

  
  
  
  

SELECT CONCAT(LCASE(LEFT(ename, 1)), SUBSTRING(ename, 2)) AS `name` FROM emp;

SELECT REPLACE(ename, LEFT(ename, 1), LCASE(LEFT(ename, 1))) FROM emp;

• 数学函数

  
  

-- CONV(number2,from_base,to_base) 进制转换
 -- 下面的含义是 8 是十进制的 8, 转成 2 进制输出 
SELECT CONV(8, 10, 2) 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 id, content, DATE(send_time) FROM mes; 
-- 请查询在 10 分钟内发布的新闻, 思路一定要梳理一下. 
SELECT * FROM mes 
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW() 
SELECT * FROM mes 
WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)

unix时间戳就是指用unix_timestamp()生成的时间戳，可以用from_unixtime()函数按特定的格式还原

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

• 加密和系统函数

  
  

-- USER() 查询用户 -- 可以查看登录到 mysql 的有哪些用户，以及登录的 IP 
SELECT USER() FROM DUAL; -- 用户@IP 地址 
-- DATABASE()查询当前使用数据库名称 SELECT DATABASE(); 
-- MD5(str) 为字符串算出一个 MD5 32 的字符串，
-- PASSWORD(str) -- 加密函数, MySQL 数据库的用户密码就是 PASSWORD 函数加密

• 流程控制函数

  
  

• 单表查询增强
  ■ 使用 where 子句 -- ?如何查找 1992.1.1 后入职的员工
  -- 老师说明： 在 mysql 中,日期类型可以直接比较, 需要注意格式
  SELECT * FROM emp WHERE hiredate > '1992-01-01'
  -- ■ 如何使用 like 操作符(模糊)
  -- %: 表示 0 到多个任意字符
  _: 表示单个任意字符
  -- ?如何显示首字符为 S 的员工姓名和工资
  SELECT ename, sal FROM emp
  WHERE ename LIKE 'S%'
  -- ■ 如何显示没有上级的雇员的情况
  SELECT * FROM emp
  WHERE mgr IS NULL;

• 注意，查看值是否等于null,要用Is。 而不是=
  -- ■ 查询表结构
  DESC emp

• 分页查询

  
  

• group by 增强查询
  -- (1) 显示每种岗位的雇员总数、平均工资。

SELECT COUNT(*), AVG(sal), job 
FROM emp 
GROUP BY job;

理解： group by排序字符时，将相同字符的放在一行，count和AVG 与 group by 合作，有奇效

-- (2) 显示雇员总数，以及获得补助的雇员数。
-- 思路: 获得补助的雇员数 就是 comm 列为非 null, 就是 count(列)，如果该列的值为 null, 不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(), COUNT(comm)
FROM emp
-- 老师的扩展要求：统计没有获得补助的雇员数
SELECT COUNT(), COUNT(IF(comm IS NULL, 1, NULL))
FROM emp
SELECT COUNT(), COUNT() - COUNT(comm)
FROM emp
-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr)
FROM emp;

• 注意：

  
  
  
  

  having可以操作统计函数和普通过滤语句，而where不可以操作

• 多表查询

1. 多表查询是指基于两个和两个以上的表查询，在实际应用中，查询单个表可能不能满足需求。
2. 如果直接查询多个表，会出现笛卡尔集，集表的每一行都会和其他表的每一行匹配。
3. 想要显示想要的结果，需要使用条件来过滤或限制行。
4. 多表查询的条件不能少于表的个数 - 1，否则会出现笛卡尔集。

-- ?如何显示部门号为 10 的部门名、员工名和工资 SELECT ename,sal,dname,emp.deptno 
FROM emp, dept 
WHERE emp.deptno = dept.deptno AND emp.deptno = 10 
-- ?显示各个员工的姓名，工资，及其工资的级别 
-- 思路 姓名，工资 来自 emp 13 
-- 工资级别 salgrade 5 
-- 写 sql , 先写一个简单，然后加入过滤条件... 
select ename, sal, grade 
from emp , salgrade 
where sal between losal

• 自连接
  自连接是指同一张表的连接查询，把一张表看做是两张表.
  -- 这里老师小结：
  -- 自连接的特点

1. 把同一张表当做两张表使用 --
2. 需要给表取别名 表名 表别名 --
3. 列名不明确，可以指定列的别名 列名 as 列的别名

-- 思考题: 显示公司员工名字和他的上级的名字 
-- 老韩分析： 员工名字 在 emp, 上级的名字的名字 emp 
-- 员工和上级是通过 emp 表的 mgr 列关联 
SELECT worker.ename AS '职员名' , boss.ename AS '上级名' 
FROM emp worker, emp boss 
WHERE worker.mgr = boss.empno; 

• 子查询

什么是子查询？
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

1. 单行子查询
   单行子查询是指只返回一行数据的子查询语句

如何显示与 SMITH 同一部门的所有员工? 
/* 1. 先查询到 SMITH 的部门号得到 
2. 把上面的 select 语句当做一个子查询来使用 */ 
SELECT deptno 
FROM emp 
WHERE ename = 'SMITH' 
-- 下面的答案. 
SELECT * 
FROM emp 
WHERE deptno = ( 
SELECT deptno 
FROM emp 
WHERE ename = 'SMITH' )

2. 多行子查询
   是指返回多行数据的子查询，使用关键字In

/* 1. 查询到 10 号部门有哪些工作 
2. 把上面查询的结果当做子查询使用 */ 
select distinct job 
from emp 
where deptno = 10; 
-- 下面语句完整 
select ename, job, sal, deptno 
from emp 
where job in ( 
SELECT DISTINCT job 
FROM emp 
WHERE deptno = 10 ) and deptno <> 10

• 子查询当做临时表

-- 查询ecshop中各个类别中，价格最高的商品

-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询

select cat_id , max(shop_price) 
    from ecs_goods
    group by cat_id
    
    
-- 这个最后答案   
select goods_id, ecs_goods.cat_id, goods_name, shop_price 
from (
        SELECT cat_id , MAX(shop_price) as max_price
        FROM ecs_goods
        GROUP BY cat_id
    ) temp , ecs_goods
    where  temp.cat_id = ecs_goods.cat_id 
    and temp.max_price = ecs_goods.shop_price 

• 子查询中使用all any操作符

-- all 和 any 的使用 
-- 请思考:显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号 
SELECT ename, sal, deptno 
FROM emp 
WHERE sal > ALL( 
SELECT sal 
FROM emp  
WHERE deptno = 30 )
 -- 可以这样写 
SELECT ename, sal, deptno 
FROM emp 
WHERE sal > ( SELECT MAX(sal) 
                        FROM emp 
                        WHERE deptno = 30 ) 
-- 请思考:如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号 
SELECT ename, sal, deptno 
FROM emp WHERE sal > any( 
SELECT sal 
FROM emp 
WHERE deptno = 30 ) 

SELECT ename, sal, deptno
FROM emp 
WHERE sal > ( 
SELECT min(sal) 
FROM emp 
WHERE deptno = 30 )

• 多列子查询
  

  多列子查询是指查询返回多个列数据的子查询语句

-- 请思考如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人) 
-- (字段 1， 字段 2 ...) = (select 字段 1，字段 2 from 。。。。) 
-- 分析: 1. 得到 smith 的部门和岗位 
SELECT deptno , job
FROM emp 
WHERE ename = 'ALLEN' 
-- 分析: 2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配 
SELECT * 
FROM emp 
WHERE (deptno , job) = ( SELECT deptno , job FROM emp 
WHERE ename = 'ALLEN' ) AND ename != 'ALLEN

• 在from子句中使用子查询

-- 查询每个部门的信息(包括：部门名,编号,地址)和人员数量,我们一起完成。
-- 1. 部门名,编号,地址 来自 dept 表
-- 2. 各个部门的人员数量 -》 构建一个临时表
-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化 sql 语句
-- 在多表查询中，当多个表的列不重复时，才可以直接写列名
SELECT tmp.* , dname, loc
FROM dept, (
SELECT COUNT(*) AS per_num, deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno

• 表复制
  -- 为了对某个 sql 语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

CREATE TABLE my_tab01
( id INT, 
`name` VARCHAR(32), 
sal DOUBLE, 
job VARCHAR(32), 
deptno INT);

-- 1. 先把 emp 表的记录复制到 my_tab01
INSERT INTO my_tab01
(id, `name`, sal, job,deptno)
SELECT empno, ename, sal, job, deptno FROM emp; 
-- 2. 自我复制
INSERT INTO my_tab01
SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;

• 如何删除一个表的重复记录
  -- 1. 先创建一张表 my_tab02,
  -- 2. 让 my_tab02 有重复的记录
  CREATE TABLE my_tab02 LIKE emp;
  -- 这个语句 把 emp 表的结构(列)，复制到 my_tab02
  -- 3. 考虑去重 my_tab02 的记录
  /*
  思路
  (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
  (2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
  (3) 清除掉 my_tab02 记录
  (4) 把 my_tmp 表的记录复制到 my_tab02
  (5) drop 掉 临时表 my_tmp
  CREATE TABLE tmp LIKE my_tab02;

INSERT INTO tmp
SELECT DISTINCT * FROM my_tab02;

DELETE FROM my_tab02;

INSERT INTO my_tab02
SELECT * FROM tmp;

DROP TABLE tmp;