17_数据库初步 - 课程笔记 - 《JavaSE 阶段》

— show databases : 展示所有库列表
show databases;
— use 库名 : 使用哪个库
use ruizhi;
— show tables : 展示库中所有的表
show tables;
— desc:展示表结构
desc t_user;

SQL语句
数据库
    创建数据库
    删除数据库
表
    创建表
    修改表
    删除表
    导入/导出
数据:CURD 增删改查
    新增
    删除
    修改
    查询数据
        简单查询 单值查询 多值查询 匹配where查询
        limit分页查询
        排序
        分组查询
            分组筛选
        关联查询
            子查询
            内连接
            左连接(用ORM框架才能体会到,JDBC操作左连接起来都比较累)

        <br />--------------------------------------------------------------------<br />-- 数据库名字:英文 数值 下划线 组合<br />-- if not exists:可选的 如果不存在则创建<br />create database if not exists day0408;

— 1007:错误码 MySQL错误码都是固定的可以通过错误码去查阅文档
create database day0408;
— ERROR 1007 (HY000): Can’t create database ‘day0408’; database exists

— 指定编码

CREATE DATABASE  IF NOT EXISTS mall2
DEFAULT CHARACTER SET 'utf8'
DEFAULT COLLATE 'utf8_general_ci';

— 删除数据库
DROP DATABASE if exists mall2;

— 使用数据库
use ruizhi2;

—创建表
— 表名: 英文数字下划线组合
— 规范: 项目名称_表名称为了不冲突
— id一般都是Java中 Long MySQL BIGINT
— id一般都是不为空 not null:不为空
— null:可以为空
— primary key:代表主键唯一不为空
— AUTO_INCREMENT:自增类似于++ 每次加1
— varchar:Java中的String 可变字符串使用多少开辟多大空间类似于Java中可变数组
— COMMENT:注释

create table if not exists t_user(
id bigint(10) not null AUTO_INCREMENT primary key COMMENT 'ID主键',
user_name varchar(50) null COMMENT '用户名',
password varchar(50) DEFAULT '123456' COMMENT '密码 默认值123456'
);

drop table if exists t_user;

-- PRIMARY KEY (id):选择哪个列作为主键
create table if not exists t_goods(
id bigint(10) not null AUTO_INCREMENT COMMENT 'ID主键',
goods_name varchar(100) null COMMENT '商品名称',
goods_price NUMERIC(8) null COMMENT'商品名称',
PRIMARY KEY (id)
);

主键、外键都是表中数据的约束
主键：唯一不重复的
当主键重复会有提示
外键：两个表建立关键关系

购物：
User
OrderForm

Goods

user(
id,
name,
pwd
)

orderform(
id,
addTime,
user_id :代表这一行数据属于User表中这个id的
)

create table t_user(
id bigint(10) primary key,
user_name varchar(20)
);
create table t_orderform(
id bigint(10) primary key,
orderform_name varchar(10) null,
user_id bigint(10)
);

-- 没有添加外键之前数据没有约束 删除的时候没有提示
-- 需要在主数据删除的时候 从数据也删除
-- 如果主数据删除 从数据没有删除 就出现了多余的数据 -----数据冗余
-- FK_ID:外键名字
-- 添加外键需要注意：主表有关联数据
ALTER TABLE  表名 add constraint FK_ID foreign key(外键字段名) 
REFERENCES 外表表名(对应的表的主键字段名);
alter table t_orderform add constraint fk_user_id foreign key(user_id)
REFERENCES t_user(id);
-- FOREIGN KEY fk_user_id:外键名称 (外键列)
-- references 主表(主表中的列)
create table t_orderform(
id bigint(10) primary key,
orderform_name varchar(50),
user_id bigint(10),
FOREIGN KEY fk_user_id(user_id)
references t_user(id)
);

QQ图片20220408104655.png

-- 查看表关系
desc t_orderform;

外键带来的优势：数据约束

外键带来的问题：
删除主表数据，需要先删除子表数据，一般我们开发中主、子表关联关系会非常复杂，如果每次删除数据，都去
找子表删除数据，会非常麻烦。
在真实开发中为了省事，不去使用外键，主表数据删除，子表数据就让他存在———数据冗余（垃圾数据）

数据冗余（垃圾数据）只是占用空间，其他都不影响

500G
几亿条数据压缩之后可能就几十M

create table t_address(
id bigint(10) auto_increment primary key,
address varchar(50) not null default '未设置名字',
code int(5) default 0
);

数据库引擎：
MySQL有很多，常见的就是InnoDB、MyISAM
InnoDB：支持事务，同时成功或者同时失败，增删改查都可以的，效率比较低，因为需要操作事务
MyISAM：不支持事务，适合于：查询多，插入少
MEMORY：内存引擎，数据存在内存中，使用完成后就清除掉了

每个数据引擎都是一种数据结构，不同的数据结构查询效率不一样
InnoDB：B树
主要是体现在查询上面，数据量多的情况就要看哪个数据结构比较合适

-- 给表添加字段
ALTER TABLE <表名> ADD <新字段名> <数据类型>;
alter table t_user add password varchar(50);
-- 修改字段数据类型
ALTER TABLE <表名> MODIFY <字段名> <数据类型>;
alter table t_user modify password int(10);
alter table t_user modify password varchar(100);
-- 删除字段
ALTER TABLE <表名> DROP <字段名>;
alter table t_user drop password;
alter table t_user drop;
-- 修改字段名称
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
alter table t_user change user_name userName varchar(50);
-- 修改表名称
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
alter table t_user rename to t_emp;

-- 添加外键：
-- 学生 
create table t_student(
id bigint(10) auto_increment primary key,
stu_name varchar(50) null
);
-- 课程
create table t_class(
id bigint(10) auto_increment primary key,
class_name varchar(20) null,
student_id bigint(10),
CONSTRAINT fk_student_id FOREIGN KEY (student_id )
references t_student(id)
);

ALTER [IGNORE] TABLE tbl_name DROP FOREIGN KEY fk_symbol
alter table t_class drop FOREIGN KEY fk_student_id(student_id);
alter table t_class drop foreign key t_class_ibfk_1;

ALTER TABLE table 表名 add constraint FK_ID foreign key(外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
Alter table
ALTER TABLE orderitems ADD CONSTRAINT 
fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

添加外键两种方式：
1、创建表的时候添加
2、修改表结构的形式：alter table。。。，可以设置外键名字

create database company;
-- 导入数据 不需要登录进去
-- mysql -u用户名 -p 数据库名 < 数据库名.sql
mysql -u root -p company < d:/sql/create.sql
-- mysqldump -u [数据库用户名] -p [要备份的数据库名称]>[备份文件的保存路径]
mysqldump -u root -p company > d:/sql/company.sql

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
-- 一种是列全部加上
insert into t_emp(id,userName) values(null,'张三');                       
-- 不写列 让值默认去对应
insert into t_emp values(null,'小张同学');

-- 批量插入:插入多行数据
insert into t_emp(id,userName) values
(null,'A'),
(null,'B'),
(null,'D'),
(null,'C'),
(null,'E');

-- 从另一张表查询出来结果 再去插入
INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table; 
create table t_emp_plus(
id bigint(10) auto_increment primary key,
userName varchar(50) null
);
-- 数据拷贝:从一张表向另一张表拷贝数据;从t_emp向t_emp_plus插入数据
insert into t_emp_plus(id,userName) select id,userName from t_emp;

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
update t_emp set userName='小强';
update t_emp set userName='小张' where id = 2;
update emp set ename='小黑',job='开发人员',sal=3000   where empno = 7369 or empno = 7521 ;

DELETE FROM table_name [WHERE Clause]
-- 删除需要慎重
delete from t_emp;
delete from t_emp_plus where userName='张三';
delete from t_emp_plus where id > 5;
--:> < != <>

--查询:检索
SELECT 列1,列2,列3 FROM table_name [WHERE Clause];
-- *:代表所有列都搜索出来
select * from emp;
--指定列
select empno,ename,sal from emp;
-- 薪水>
select empno,ename,sal from emp where sal>=3000 and sal<5000;

SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count; 
--offset:其实位置 从0开始
--count:返回多少条数据
-- 从0位置 返回5条数据
select * from emp limit 0,5;
select * from emp limit 5,5;
select * from emp limit 10,5;
-- 分页
-- pageNo->1 2
-- pageSize:5
select * from emp limit (pageNo-1) * pageSize,pageSize;
select * from emp limit (pageNo-1) * pageSize,pageSize;
1->(1-1) * 5 : 0
2->(2-1) * 5 : 5
3->(3-1) * 5 : 10
SELECT 
    column1,column2,...
FROM
    table
LIMIT count; 
select * from emp limit 10;

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 小黑   | 开发人员  | 7902 | 1980-12-17 | 3000.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | 小黑   | 开发人员  | 7698 | 1981-02-22 | 3000.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-07 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],... 
--asc:正序 desc:逆序 
select * from emp order by empno asc;
select deptno,empno,ename from emp order by deptno asc;
-- 前面根据部门编号排序后 再根据员工编号排序 
select deptno,empno,ename from emp order by deptno asc,empno desc;
-- 多列排序 前面先排序好之后 再去排序后面字段列
-- 如果order by 字段 后面没有写asc/desc 默认是正序排序
select deptno,empno,ename from emp order by deptno;

-- 排序和limit查询最大值、最小值
limit 1
select * from emp order by sal desc limit 1;
select * from emp order by sal asc limit 1;

-- where 条件
SELECT 
    select_list
FROM
    table_name
WHERE
    search_condition;
-- =判断
select * from emp where ename = '小黑';    
-- 不等于判断:!=、<>
select * from emp where ename != '小黑';   
select * from emp where sal <> 5000 order by sal desc;
-- BETWEEN … AND … 两边的值都是带上的
select * from emp where sal between 3000 and 5000;
-- 部门10、20 薪水最高、最低的人
select * from emp where deptno=10 order by sal asc limit 1;

SELECT子句及其顺序
SELECT	要返回的列或表达式
FROM	从中检索数据的表
WHERE	行级过滤
ORDER BY	输出排序顺序
LIMIT	要检索的行数

-- 为空null判断:is null,不为空判断:is not null
select * from emp where comm is null;
select * from emp where comm is not null;

-- and  薪水在2000 到 3000之间
select * from emp where sal > 2000 and sal<=3000;
-- 部门20 薪水在2000 到 3000之间
select * from emp where deptno = 20 and (sal > 2000 and sal <= 3000);
-- 名字叫 小黑 comm不为空
select * from emp where ename='小黑' and comm is not null;
-- 搜索部门10 或者 20
select * from emp where deptno=10 or deptno=20;
-- 部门10 20 中薪水最高的人
select * from emp where deptno = 10 or deptno =20 order by sal desc;
-- 当我们的WHERE SQL容易产生歧义 我们可以通过加() 分开计算
select * from emp where deptno =10 and deptno=20 or sal < 3000;
--empno 7369、7654  薪水 < 3000
select * from emp where (empno < 7654 and empno > 7369) and sal < 3000;

员工号 7698、7782、7839，并且薪资小于3000
拿到薪资大于3000，或者薪资小于2000
select * from emp where (empno=7698 or empno=7782 or empno = 7839) and sal <3000;
mysql> select * from emp where empno=7698 or empno=7782 or empno = 7839 and sal > 3000;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-07 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
mysql> select * from emp where (empno=7698 or empno=7782 or empno = 7839) and sal > 3000;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-07 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)
select * from emp where sal > 3000 or sal < 2000;

select * from emp where (empno=7698 or empno=7782 or empno = 7839) and sal <3000;
select * from emp where empno in (7698,7782,7839) and sal < 3000;
select * from emp where ename in ('小黑','JONES');
select * from emp where ename = '小黑' or ename='JONES';
--in比or更加便捷

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | 小黑   | 开发人员  | 7902 | 1980-12-17 | 3000.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | 小黑   | 开发人员  | 7698 | 1981-02-22 | 3000.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-07 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
-- 筛选部门 ACCOUNTING 或者 SALES 中的员工出来
select * from emp 
where deptno in ( select deptno from dept where dname in ('ACCOUNTING','SALES') );
1、拿到ACCOUNTING 或者 SALES 部门编号 10/30
select deptno from dept where dname in ('ACCOUNTING','SALES');
+--------+
| deptno |
+--------+
|     10 |
|     30 |
+--------+
2、从emp中使用in来根据部门编号筛选
select * from emp where deptno in (10,30);
mysql> select * from emp where deptno in (10,30);
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-07 | 5000.00 |    NULL |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | 小黑   | 开发人员  | 7698 | 1981-02-22 | 3000.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
9 rows in set (0.00 sec)
3、整合到一起
select * from emp 
where deptno in (select deptno from dept where dname in ('ACCOUNTING','SALES'));

-- like 'XX%'
-- like '%XX';
-- like 'XX%YYY%MMM'
-- %:代表任意长度数据
-- 名字是J开头的
select * from emp where ename like 'J%';
--名字前面是任意的 后面是TT结尾
select * from emp where ename like '%TT';
--中间带O 前后是任意的
select * from emp where ename like '%O%';
-- %:任意字符 长度也是任意的
-- _:任意字符 长度是一个长度
-- 名字是SCOT_ 
select * from emp where ename like 'SCOT_';

小黑:开发人员:3000
select concat(ename,':',job) as ej from emp;
select concat(ename,':',job,':',sal) as ej from emp;
(小黑+开发人员+3000)
select concat('(',ename,'+',job,'+',sal,')') as ej from emp;
select concat('abc=','def',ename) from emp;
-- 薪水+奖金comm
select (sal + comm) as '薪水奖金' from emp where comm is not null;
select (sal - comm) as '薪水奖金' from emp where comm is not null;
select (sal * comm) as '薪水奖金' from emp where comm is not null;
select (sal / comm) as '薪水奖金' from emp where comm is not null;
select (sal % comm) as '薪水奖金' from emp where comm is not null;
as/AS:重新起一个别名
select ename as '员工编号', ename as '员工姓名' from emp;

id bigint(20)       '主键id，这里为自增类型',
addTime datetime     '添加时间',
ac_title varchar(255)     ' 活动标题',
create table activity(
  id bigint(10) not null auto_increment primary key COMMENT '主键ID',
  addTime datetime null COMMENT '入库时间',
  ac_title varchar(255) null COMMENT '活动标题'
);
-- now():返回当前时间
insert into activity(addTime,ac_title) values
(now(),'活动A'),
(now(),'活动B'),
(now(),'活动C'),
(now(),'活动D'),
(now(),'活动E');
-- limit offset,count
-- offset:从0开始 从第一行开始
-- count:返回数量
-- pageNo:分页码 从1开始的
-- pageSize:每页多少条数据 == count
limit (pageNo - 1) * pageSize,pageSize 
select * from emp where deptno=10 order by sal desc limit 1;

2022年4月9日09:23:09
知识点回顾：
1、创建数据库、删除数据库
2、创建表、删除表、修改表结构，主键、外键关键关系
3、插入数据、修改数据、查询数据（简单查询、带条件查询、limit、排序）


CREATE  TABLE IF NOT EXISTS tbl_name  (column_name column_type);
-- auto_increment:自增 从1开始 每次增加1个
create table if not exists t_user(
id bigint(10) not null auto_increment primary key,
userName varchar(50) null,
pwd varchar(50) null
);
insert into t_user(userName,pwd) values
('张三','123'),
('李四','123'),
('王五','123'),
('赵六','123'),
('小明','123')
;
create table t_orderform(
id bigint(10) not null auto_increment primary key,
sn varchar(10) null,
user_id bigint(10)
);
insert into t_orderform(sn,user_id) values
('1234561',1),
('1234562',2),
('1234563',3),
('1234564',4),
('1234565',5);

主键：唯一不重复的
外键：为了和主表建立关联关系（就是为了数据有约束）
用户表
订单表
没有外键的时候：主表删除，从表不会受影响，也没有任何提示，因为两个表就完全不相关。

ALTER TABLE  表名 add 
constraint FK_ID foreign key(外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
alter table t_orderform add
constraint fk_user_id foreign key(user_id) REFERENCES t_user(id);

外键带来的问题：如果系统比较庞大，表结构比较复杂，有外键的情况下，删除数据会非常麻烦。删除的时候需要先删除子表。
一般开发中为了省事，不用外键，删除主表，子表数据还是存在的，属于没有用的垃圾数据——数据冗余

数据冗余/垃圾数据：问题，只有占用物理空间

-- mysql -u用户名 -p 数据库名 < 数据库名.sql
mysql -u root -p ruizhi < d:/sql/create.sql
mysql -u root -p ruizhi_bak < d:/sql/ruizhi.sql
-- mysqldump -u [数据库用户名] -p [要备份的数据库名称]>[备份文件的保存路径]
mysqldump -u root -p ruizhi > d:/sql/ruizhi.sql

insert into t_user(id,userName,pwd) values(null,'小张同学','123');
insert into t_user(userName,pwd) values('小张同学','123');
insert into t_user(userName,pwd) values
('小张同学','123'),
('小张同学','123'),
('小张同学','123'),
('小张同学','123'),
('小张同学','123');
create table t_user2(
id bigint(10) not null auto_increment primary key,
userName varchar(50) null,
pwd varchar(50) null
);
INSERT INTO table_name(column_list)
SELECT 
   select_list 
FROM 
   another_table; 
insert into t_user2(userName,pwd) select userName,pwd from t_user;

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
-- 一般做update、delete会把数据更新出问题,要做备份(导出)
-- 在表中加字段 状态值 stuts:0已删除 1未删除 
-- t_user2 t_user
update t_user set pwd='123456',userName='未命名';
update t_user2 set userName='未命名',pwd='123456' where id=10;
DELETE FROM table_name [WHERE Clause]
delete from t_user2;
delete from t_user where id=1;

SELECT 列1,列2,列3 FROM table_name [WHERE Clause];
-- *:查询所有列
select * from emp;
select empno,ename,job as '工作' ,sal '薪水' from emp;
select empno,ename,job as '工作' ,sal '薪水' from emp where empno=7934;

SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count; 
--offset:从0开始
--count:返回多少数据量
select * from emp limit 0,10;
SELECT 
   select_list
FROM
   table
LIMIT count; --只带一个count 表示直接返回多少数据量
select * from emp limit 10;

SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],... 
-- asc:正序 默认就是正序 可以不写
-- desc:逆序
select * from emp order by empno asc;
select * from emp order by empno asc,deptno desc;
-- 多列排序 前面排序完成后 后面再去排序

select * from emp where sal = 3000;
select * from emp where sal > 2000;
select * from emp where sal >= 2000;
select * from emp where sal < 2000;
select * from emp where sal <= 2000;
select * from emp where sal <> 3000;
select * from emp where sal != 3000;
select * from emp where sal between 3000 and 5000;
-- 空值查询 is null
-- 非空值查询 is not null
select * from emp where comm is null;
select * from emp where comm is not null;
-- and or ()
select * from emp where sal >3000 or sal < 2000;
select * from emp where (sal > 3000 or sal < 2000) and deptno=10;

-- in : 和or类似的
select * from emp where deptno=10 or deptno = 20;
select * from emp where deptno in (10,20);
--SALES  OPERATIONS
select deptno from dept where dname in('SALES','OPERATIONS');
select * from emp where deptno in ( select deptno from dept where dname in('SALES','OPERATIONS') );

-- like:模糊匹配 %:代表0~n任意字符
-- like '%abcdef'
-- like 'abcdef%'
-- like '%abcdef%'
-- like 'a%bc%def'
select * from emp where ename like 'J%';
select * from emp where ename like '%S';
select * from emp where ename like '%O%';
-- _:下划线 代表1个任意字符
select * from emp where ename like 'WAR_';
-- 员工姓名-员工编号-薪水
select concat( ename,'-',empno, '-',sal,deptno) from emp;

Length():计算字符串长度的
select length(ename) as '名字长度',ename from emp;
Lower():转换为小写
select lower(ename),ename from emp;
-- 在所有ename后面加一个字符串 abc
update emp set ename=concat(ename,'abc'); 
MySQL 中替换函数 REPLACE(s，s1，s2) 使用字符串 s2 替换字符串 s 中所有的字符串 s1。
update emp set ename = replace(ename,'abc','');
update emp set ename = replace(ename,' ','');
update emp set ename=concat(' ',ename,' '); 
select substring(ename,1,3) from emp;

-- now():当前时间 2022年4月9日11:06:41
create table t_goods(
id bigint(10) not null auto_increment primary key,
addTime datetime null default now(),
goodsName varchar(50) null
);
insert into t_goods(goodsName) values
('华为手机'),
('苹果手机'),
('小米手机'),
('一加手机'),
('锤子手机');
select id , date_format(addTime,'%Y/%m/%d %H:%i:%s') , goodsName from t_goods;
select id , date_format(addTime,'%Y-%m-%d %H-%i-%s') , goodsName from t_goods;
select id , date_format(addTime,'%Y:%m:%d %H:%i:%s') , goodsName from t_goods;

-- avg() count()
select avg(sal) as '平均薪水' , count(*) as '员工人数' from emp;
-- count:count(*) == count(ename) == count(1) :用法都是一样的
-- count(*):统计所有列 效率比较低
select avg(sal) as '平均薪水' , count(ename) as '员工人数' from emp;
select avg(sal) as '平均薪水' , count(1) as '员工人数' from emp;
-- max、min 计算最大值 最小值 会过滤掉null
select max(comm) from emp;
select min(comm) from emp;
-- sum() 统计和
select sum(sal) from emp;
-- 计算部门10 薪水总和
select sum(sal) from emp where deptno=10;
select 
avg(sal) '平均薪水',
count(1) '人员数量',
max(sal) '薪水最大值',
min(sal) '薪水最小值',
sum(sal) '薪水总和'
from emp;

-- 分组函数:将数据分组 group by 列1,列2...
-- select 分组函数,[group by 后面的列],(其他列可以放,但是不符合逻辑) from group by 分组列
1、emp表中 按照部门分组 分组后 计算这个部门平均薪水、部门人数、薪水最大值
select avg(sal) as '部门平均薪水',deptno from emp group by deptno;
select 
avg(sal) as '部门平均薪水',
max(sal) as '部门薪水最大',
min(sal) as '部门薪水最低',
count(empno) as '部门人数',
sum(sal) as '部门薪水总和',
deptno
from emp group by deptno;
2、having 如果直接使用 就是where
select * from emp having sal > 3000;
3、分组后过滤/筛选 group by having 
筛选是从分组后的数据进行筛选 
+--------------+--------------+--------------+----------+--------------+--------+
| 部门平均薪水 | 部门薪水最大 | 部门薪水最低 | 部门人数 | 部门薪水总和 | deptno |
+--------------+--------------+--------------+----------+--------------+--------+
|  2916.666667 |      5000.00 |      1300.00 |        3 |      8750.00 |     10 |
|  2175.000000 |      3000.00 |       800.00 |        5 |     10875.00 |     20 |
|  1566.666667 |      2850.00 |       950.00 |        6 |      9400.00 |     30 |
+--------------+--------------+--------------+----------+--------------+--------+
select 
avg(sal) as '部门平均薪水',
max(sal) as '部门薪水最大',
min(sal) as '部门薪水最低',
count(empno) as '部门人数',
sum(sal) as '部门薪水总和',
deptno
from emp group by deptno having avg(sal) > 2000;
select 
avg(sal) as '部门平均薪水',
max(sal) as '部门薪水最大',
min(sal) as '部门薪水最低',
count(empno) as '部门人数',
sum(sal) as '部门薪水总和',
deptno
from emp group by deptno having avg(sal) > 2000 and max(sal) > 3000 ;
select 
avg(sal) as '部门平均薪水',
max(sal) as '部门薪水最大',
min(sal) as '部门薪水最低',
count(empno) as '部门人数',
sum(sal) as '部门薪水总和',
deptno
from emp group by deptno having avg(sal) > 2000 and max(sal) > 3000 and deptno=10;
4、分组后排序
select 
avg(sal) as '部门平均薪水',
max(sal) as '部门薪水最大',
min(sal) as '部门薪水最低',
count(empno) as '部门人数',
sum(sal) as '部门薪水总和',
deptno
from emp group by deptno having avg(sal) > 2000
order by avg(sal) desc;
5、分组后limit
select 
avg(sal) as '部门平均薪水',
max(sal) as '部门薪水最大',
min(sal) as '部门薪水最低',
count(empno) as '部门人数',
sum(sal) as '部门薪水总和',
deptno
from emp group by deptno having avg(sal) > 2000
limit 0,2;

-- 按照部门分组 筛选平均薪水大于2000
select 分组函数(avg、count、max、min、sum),分组列 from 表 group by 分组列 
select avg(sal),deptno from emp group by deptno;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2916.666667 |     10 |
| 2175.000000 |     20 |
| 1566.666667 |     30 |
+-------------+--------+
3 rows in set (0.00 sec)
select 分组函数,分组列 from 表 having 分组函数,列;
select 分组函数(avg、count、max、min、sum),分组列 from 表 group by 分组列 having 分组函数,分组列
select avg(sal),deptno from emp group by deptno having avg(sal)>2000;
-- 按照工作分组 求每种工作最大薪水、平均薪水、最低薪水
select 
job as '工作',
max(sal) as '最高薪水',
avg(sal) as '平均薪水',
min(sal) as '最低薪水'
from emp 
group by job 
having max(sal)>2000 or max(sal)<1000;

--子查询 in = 
-- in 相当于 or
-- 部门 是10 或者 20
select * from emp where deptno=10 or deptno=20;
select * from emp where deptno in (10,20);
-- in比or更加便捷 查询效率上in更高
--知道部门名称 根据部门查询员工
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
ACCOUNTING、RESEARCH
1、将部门编号查询出来
select deptno from dept where dname in ('ACCOUNTING','RESEARCH');
2、将查询出来的部门编号放到员工表中进行查询
select * from emp where deptno in (10,20);
3、将上面两条sql语句整合成一条sql
select * from emp 
where 
deptno in ( select deptno from dept where dname in ('ACCOUNTING','RESEARCH') );
===============================================================================
in 可以支持多个值的
= 只能支持一个值
select * from emp 
where 
deptno = ( select deptno from dept where dname in ('ACCOUNTING','RESEARCH') );
-- ERROR 1242 (21000): Subquery returns more than 1 row
-- 通过员工编号 查找员工所属部门名称
1、通过员工编号查找部门编号
select deptno from emp where empno=7782;
2、通过部门编号查找部门名称
select * from dept where deptno = 10;
3、两条sql语句整合成一条sql
select * from dept where deptno = (select deptno from emp where empno=7782);

in、=
1、知道部门名称，查找员工

2、知道员工编号，查找部门名称

-- 用户表:商品
create table t_user(
id bigint(10) not null auto_increment primary key,
addTime datetime null default now(),
userName varchar(50) null
);
insert into t_user(userName) values('张三'),('李四'),('王五'),('小明');
create table t_goods(
id bigint(10) not null auto_increment primary key,
addTime datetime null default now(),
goodsName varchar(50) null,
user_id bigint(10) 
);
insert into t_goods(goodsName,user_id) values
('苹果手机',1),
('小米手机',1),
('一加手机',2),
('锤子手机',3),
('华为手机',4);
---------------------------------------------
1、张三有哪些商品
select id from t_user where userName='张三';
select * from t_goods where user_id in ( select id from t_user where userName='张三' );
2、苹果手机属于谁的
select user_id from t_goods where goodsName='苹果手机';
select * from t_user where id in ( select user_id from t_goods where goodsName='一加手机' );

-- 多表连接查询
-- select 列 from 表1,表2 where 关联条件
-- select 列 from 表1 join 表2 on 关联条件
-- select 列 from 表1 left join 表2 on 关联条件
-- 员工信息 + 部门信息 都需要显示
--差生两个表所有数据一一去匹配,两个表数据量相乘:笛卡尔 积
select * from emp as e,dept as d;
-- 表别名 因为两个表可能存在列名相同
select * from emp as e,dept as d where e.deptno = d.deptno;
用户-商品
select * from t_user u,t_goods g where u.id = g.user_id;
select 
u.id as '用户ID',
u.addTime as '用户添加时间',
u.userName as '用户名',
g.id as '商品ID',
g.addTime as '商品添加时间',
g.goodsName as '商品名称'
from t_user u,t_goods g where u.id = g.user_id;
1、用户ID=1 显示用户信息+商品信息
select 
u.id as '用户ID',
u.addTime as '用户添加时间',
u.userName as '用户名',
g.id as '商品ID',
g.addTime as '商品添加时间',
g.goodsName as '商品名称'
from t_user u,t_goods g where u.id = g.user_id and u.id = 1;
2、工作是 MANAGER 员工信息+部门信息
select 
e.empno as '员工编号',
e.ename as '员工名称',
e.job   as '员工工作',
d.deptno as '部门编号',
d.dname as '部门名称'
from emp e,dept d where e.deptno = d.deptno and e.job='MANAGER';

-- select 列.. from 表1 join 表2 on 关联条件 这种用法和where是一样的
-- 员工信息 + 部门信息 都需要显示
select * from emp e join dept d on e.deptno = d.deptno;
1、查询员工、部门信息,工作是 ANALYST
select * from emp e ,    dept d where e.deptno = d.deptno and e.job='ANALYST';
select * from emp e join dept d on    e.deptno = d.deptno and e.job='ANALYST';
2、查询用户、商品信息,用户名是张三
select * from t_user u join t_goods g on    u.id = g.user_id and u.userName='张三';
select * from t_user u ,    t_goods g where u.id = g.user_id and u.userName='张三';

-- left join
-- 用户表有10个人 只有前面4个人有商品
-- 需求：将所有用户查询出来 并且有商品也出来 没有商品的用户也查询出来
select * from t_user;
--where
select * from t_user u,t_goods g where u.id = g.user_id;
select * from t_user u join t_goods g on u.id = g.user_id;
-- left join:以左边为主 进行关联查询
mysql> select * from t_user u left join t_goods g on u.id = g.user_id;
+----+---------------------+----------+------+---------------------+-----------+---------+
| id | addTime             | userName | id   | addTime             | goodsName | user_id |
+----+---------------------+----------+------+---------------------+-----------+---------+
|  1 | 2022-04-09 15:06:44 | 张三     |    2 | 2022-04-09 15:07:52 | 小米手机  |       1 |
|  1 | 2022-04-09 15:06:44 | 张三     |    1 | 2022-04-09 15:07:52 | 苹果手机  |       1 |
|  2 | 2022-04-09 15:06:44 | 李四     |    3 | 2022-04-09 15:07:52 | 一加手机  |       2 |
|  3 | 2022-04-09 15:06:44 | 王五     |    4 | 2022-04-09 15:07:52 | 锤子手机  |       3 |
|  4 | 2022-04-09 15:06:44 | 小明     |    5 | 2022-04-09 15:07:52 | 华为手机  |       4 |
|  5 | 2022-04-09 16:09:35 | 大明     | NULL | NULL                | NULL      |    NULL |
|  6 | 2022-04-09 16:09:41 | Jack     | NULL | NULL                | NULL      |    NULL |
|  7 | 2022-04-09 16:09:43 | Tony     | NULL | NULL                | NULL      |    NULL |
|  8 | 2022-04-09 16:09:46 | Mark     | NULL | NULL                | NULL      |    NULL |
|  9 | 2022-04-09 16:09:48 | Tom      | NULL | NULL                | NULL      |    NULL |
| 10 | 2022-04-09 16:09:50 | Cat      | NULL | NULL                | NULL      |    NULL |
+----+---------------------+----------+------+---------------------+-----------+---------+
--select 列... from 表1 left join 表2 on 关联条件
-- 左边表数据全部列出来 右边表数据符合条件就列出来 不符合条件就不列出来
customers cust_id
orders cust_id
customers为主表 orders为从表
主表全部显示 
select 
c.cust_id,
c.cust_name,
o.order_num,
o.order_date
from customers c left join orders o on c.cust_id = o.cust_id;
User(张三)
  List<Goods>
  华为手机
  苹果手机
  锤子手机
select * from t_user u left join t_goods g on u.id = g.user_id
where u.userName = '张三';
-- left join 查询完后结果集 可以通过 where 再去筛选

1 2022-04-09 15:06:44 张三苹果手机
2 2022-04-09 15:06:44 李四苹果手机
3 2022-04-09 15:06:44 王五苹果手机
4 2022-04-09 15:06:44 小明苹果手机
5 2022-04-09 16:09:35 大明
6 2022-04-09 16:09:41 Jack
7 2022-04-09 16:09:43 Tony
8 2022-04-09 16:09:46 Mark
9 2022-04-09 16:09:48 Tom
10 2022-04-09 16:09:50 Cat

-- union:去除重复数据 union all:显示所有的不会去除重复数据
create table t_user2 select id,addTime,userName from t_user;
-- 两个表关联
select id,userName,addTime from t_user
union
select id,addTime,userName from t_user2
group by id
order by id desc;
-- 三个表关联
select id,addTime,userName from t_user
union
select id,addTime,userName from t_user2
union
select id,addTime,userName from t_user2;
select id,addTime,userName from t_user
union all
select id,addTime,userName from t_user2
union all
select id,addTime,userName from t_user2
union all
select id,addTime,userName from t_user2;