【javaWeb】

SQL通用语法

1. SQL语句可以进行单行或多行书写，以分号为结尾。
2. MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
3. 注释

• 单行注释：-- 注释内容 或 #注释内容(mySQL特有)
• 多行注释：/* 注释 */

SQL数据库初始目录解析

1. infomation_scheme 视图，不存在目录里.特殊的逻辑表
2. mysql 核心信息 权限 安全等
3. performance_scheme MYSQL性能相关信息
4. sys 系统相关信息

SQL数据类型

SQL分类

1. DDL: 操作数据库，表等
2. DML: 对表中数据进行增删改
3. DQL: 对表中数据进行查询
4. DCL: 对表中数据进行权限控制

DDL常用命令

1. 操作数据库

SHOW DATABASE; -- 查询所有数据库名称
CREATE DATABASE db1; -- 创建数据库，注意：如果数据库已被创建，会报错
CREATE DATABASE IF NOT EXISTS db1; -- 加判断，若存在，不创建，不存在，则创建
DROP DATABASE db2;  -- 删除数据库 注意：如果数据库不存在，会报错
DROP DATABSE IF EXISTS db2; -- 加判断，若存在，则删除，不存在，不删除
SELECT DATABASE(); #查看当前使用的数据库
USE db1; #使用这个数据库

2. 操作表

SHOW TABLES; #查询当前数据库下所有表名称
DESC tableName; #查询表结构 （tableName为表名）
CREATE TABLE tableName (
     字段1 int,
     字段2 varchar(16),
     字段3 varchar(20)     
); -- 创建表并定义字段 注意：最后一行末尾，不加逗号。
DROP TABLE tb1; -- 删除表名为tb1的表，不存在则报错
DROP TABLE IF EXISTS tb1; -- 加判断，若存在，则删除，不存在，不删除

2.1 修改表-ALTER

ALTER TABLE tableName RENAME TO newTablesName; -- 修改表名 
ALTER TABLE tableName ADD 列名 数据类型; -- 添加一列
ALTER TABLE tableName MODIFY 列名 新数据类型; -- 修改列的数据类型
ALTER TABLE tableName CHANGE 列名 新列名 新数据类型; -- 修改列名和数据类型
ALTER TABLE tableName DROP 列名; -- 删除列

2.2 案例 创建学生表

CREATE TABLE student(
  id int, 
  name varchar(10),
  gender char(1),
  birthday date,
  score double(5,2),
  email varchar(64),
  tel varchar(15),
  status tinyint
);

DML常用命令

1.添加数据-INSERT

INSERT INTO tableName(列名1,列名2，...) values(值1,值2,...); --给指定列添加数据
INSERT INTO tableName(列名1,列名2,列名3,列名4) values(值1,值2,值3,值4) --给所有列添加数据
INSERT INTO tableName values(值1,值2,值3,值4) --给所有列添加数据后，可省略列名，但不建议。
INSERT INTO tableName(列名1,列名2,列名3,列名4) values(值1,值2,值3,值4),(值1,值2,值3,值4); --批量添加

2.修改数据-UPDATE

UPDATE STU SET SEX = '女' WHERE ID = '4'; -- 修改stu表ID为4的SEX字段改为女
UPDATE STU SET birthday = '2022-10-15', score = '99' where id = '5'; --修改stu表id为5的数据
UPDATE STU SET birthday = '2022-10-15', score = '99'--修改stu表所有的birthday 字段和score字段，**慎用

3.删除数据-DELETE

DELETE from stu where id = 4; -- 删除stu表id为4的数据
DELETE from stu; -- 删除stu表所有数据  **慎用

DQL常用命令

1.查询数据-SELECT

1.1 基础查询

SELECT * FROM tableName; -- 查询表中所有数据，列表的所有列名可以用符号*表示  不要使用！！
SELECT NAME,AGE FROM tableName; -- 查询表中name,age字段
SELECT DISTINCT ADDRESS FROM tableName;-- 查询表中ADDRESS字段,去重
SELECT USERNAME AS 姓名,SCORE AS 成绩,AGE AS 年龄 FROM tableName;  --查询表中姓名，成绩，年龄为别名的字段
SELECT USERNAME 姓名,SCORE 成绩,AGE 年龄 FROM tableName;  --查询表中姓名，成绩，年龄为别名的字段,AS可以省略掉，别名必须
--和列名间隔一个空格以上

1.2 条件查询 WHERE

--查询年龄大于20岁的学员信息
select * from stu where age>20; 
--查询年龄大于等于20岁的学员信息
select * from stu where age>= 20;
--查询年龄大于等于20岁并且小于等于30岁的学员信息
select * from stu where age>= 20 && age<= 30;
select * from stu where age>= 20 and age<= 30;
select * from stu where age BETWEEN 20 AND 30;
--查询出生日期2019-08-01 到 2019-08-31的学员信息
select * from stu where birthday BETWEEN '2019-08-01' AND '2019-08-31';
--查询年龄等于18岁的学员信息
select * from stu where age = 18;
--查询年龄不等于18岁的学员信息
select * from stu where age != 18;
select * from stu where age <> 18;
--查询年龄等于18岁或21或33的学员信息
select * from stu where age = 18 or age = 21 or age = 33;
select * from stu where age  in(18,21,33);
--查询地址为null的学员信息 注意null值的比较不能使用= ,!=。需要使用is is not
select * from stu where address is null; -- 是 null
select * from stu where address is not null; -- 不是  null
-- 查询姓张的学员信息
select * from stu where username like '张%';
-- 查询第二个字为二的学员信息
select * from stu where username like '_二%';
-- 查询名字包含二的学员信息
select * from stu where username like '%二%';
-- 

1.3 排序查询 ORDER BY

*（如果有多个排序条件，当前面条件相同，才会根据第二个条件排序）

-- 1.查询学生信息，按年龄升序 
select * from stu order by age asc; 
select * from stu order by age; --默认就是按照升序，asc可以省略
-- 2.查询学生信息，按年龄降序
select * from stu order by age desc; 
-- 3.查询学生信息，按score降序，若一样，按照math升序排
select * from stu order by score desc, math asc; 

1.4 聚合函数

-- 1.统计班级所有学生
SELECT COUNT(*) FROM STU; -- COUNT(*) COUNT(主键)
-- 2.查询数学最高分
SELECT max(math) FROM STU; -- max(math)
-- 3.查询数学最低分
SELECT min(math) FROM STU; -- min(math)
-- 4.查询数学成绩总分
SELECT sum(math) FROM STU; -- sum(math)
-- 5.查询数学平均分
SELECT avg(math) FROM STU; -- avg(math)
-- 6.查询英语最低分 （列里含有NULL）
SELECT avg(min) FROM STU; -- min(math) MULL不参与运算

1.5 分组查询 GROUP BY

语法：
SELECT 字段列名, 聚合函数 FROM 表明 WHERE 分组前限定条件 GROUP BY 分组字段名 HAVING 分组后条件过滤;
*注意：分组之后，查询的字段为聚合函数的分组字段，其他字段无意义

WHERE和 HAVING区别：
执行时间不一样：WHERE分组前限定，不满足提哦啊见，则不参与分组。HAVING分组之后对结果进行过滤
可判断条件不一样： WHERE不能对聚合函数进行判断，HAVING可以

执行顺序：
where>聚合函数>having

-- 1.查询男女各自数学平均分 
SELECT SEX, AVG(MATH) FROM STU GROUP BY SEX; 
-- 2.查询男女各自数学平均分，及人数
SELECT SEX, AVG(MATH) COUNT(*) FROM STU GROUP BY SEX; 
-- 3.查询男女各自数学平均分，及人数,大于70
SELECT SEX, AVG(MATH) COUNT(*) FROM STU WHERE MATH > 70 GROUP BY SEX; 
-- 4.查询男女各自数学平均分，及人数,大于70,分组之后人数大于两个
SELECT SEX, AVG(MATH) COUNT(*) FROM STU WHERE MATH > 70 GROUP BY SEX having count(*) > 2; 

1.6 分页查询 LIMIT

语法：
SELECT 字段列表 FROM 表明 LIMIT 起始索引,查询条目数*起始索引; (从0开始)

计算公式
起始索引 = (当前页码-1) * 每页条数

tips：
limit是mysql方言
Oracle 使用 rownumber
SQL Server 使用 top

-- 1.从0开始查询，查询三条数据
select * from stu LIMIT 0,3;
-- 2.每页三条，查第一页
select * from stu LIMIT 0,3;
-- 3.每页三条，查第二页
select * from stu LIMIT 3,3;
-- 4.每页三条，查第三页
select * from stu LIMIT 6,3;