java---sql主要以mysql为例一：select 语句 select的基本语法条件查询排序查询常见函数

主要以mysql为例

一：select 语句

select的基本语法
条件查询
排序查询
常见函数
分组查询
连接查询
子查询
分页查询
联合查询

二：增删改语句

insert into
update
delete

三：DDL、DML语言

库
表
数据类型
约束

select 语句

1. select的基本语法

1.1 select 查询内容（字段、常量、表达式、函数）

select * from table
select  列1,列2 from table
select  常量值(100)/表达式(100+90)/函数

1.2 别名（as可以省略）

select  列1 as 别名1,列2 as 别名2 from table

1.3 去重（distinct）

//注意  name,gender都一致的才会被过滤掉，否则有一者不一样就会显示在结果中。
select DISTINCT name,gender from "public"."user"

1.4 + 和 concat（拼接）

// 列1和 列2都不为null情况下，那 列1和 列2会转换为数字，转换不成功就为0
// 列1和 列2 其中一方若为null, 则结果就为null。
select 列1 + 列2 from table
select '33' + '10'    //43
select 'n' + 10       //10
select null + 10     //null
// concat（拼接）
select  concat(列1, 列2, 列3) as 别名  from table

2. 条件查询（select 查询内容 from table where 筛选条件）

2.1 按照条件表达式筛选

// >   <    <=   >=   =   <>

2.2 按照逻辑运算符筛选

// and  or   not
//查询部门编号不是在90到110之间，或者工资高于15000的员工
select * from table  where not(id >=90 and id<=110) or salary > 15000

2.3 按照模糊查询筛选

like (常跟_  %配合)/ between and(包含边界值) /  in(字段的值在in中的某一项)  / is null /  is not null
// like  跟通配符 %(任意多个字符含0个字符)  _(单个字符)
//查询员工名中第3个字符为a, 第5个字符为b的员工
select * from table where name like '__a_b%'
//查询员工名中第2个字符为_的员工
select * from table where name like '_\_%'
// 查询部门编号是在90到110之间的员工  
select * from table  where  id between 90 and 110;
// 查询有奖金的员工  
select * from table where pct is not null;

3. 排序查询（select 查询列表 from 表【where 条件】order by 排序列表【asc/desc】）

//按照年薪的高低显示员工  ifnull(列，0) 若列为null 则取值为0
select salary*12*(1+ifnull(pct,0)) as total from table order by total desc
//按照年龄降序再按照id升序，支持按照多个字段进行排序
select * from "user" order by age desc, id asc
//按照名字长度降序
select * from "user" order by "length"("name") DESC
//总结：排序支持 单个字段、多个字段、表达式、函数、别名（order by 默认是升序）

4. 常见函数

单行函数

######4.1 字符函数（传进入一个参数，返回一个结果）

length(列) utf-8字符集下一个汉字=3个字节, 返回字节的长度
concat(a,b,c) 拼接
upper/lower 转大写小写
substr(列, 起始位置，长度) 截取字符串，起始位置从1开始
trim() 去前后空格
replace 替换(text, source, target) 匹配上的全部替换

select concat(upper( substr(name,1,1) ), '_', lower(substr(name, 2))) from table
// 将 xxx  替换成 bwf
select replace('wmyxxx', 'xxx',' bwf')

4.2 数学函数

round(a, m) 四舍五入 round(2.344, 1) 结果是2.3 （m表示小数点后面的位数）
ceil() 向上取整
floor() 向下取整

4.3 日期函数

now() 格式yyyy-MM-dd HH:mm:ss
curdate() 格式yyyy-MM-dd
curtime HH:mm:ss
year(now()) yyyy month(now()) MM
str_to_date 字符串转时间 format %Y %c(不带有0的月份) %m(带有0的月份) %d %H(24小时制) %h(12小时制) %i(分钟) %s

select  str_to_date('2020-03-02', '%Y-%c-%d')

4.4 流程控制函数

if函数 if(条件，a, b)
case when 条件1 then 结果1 when 条件2 then 结果2 else 结果3 end as 别名

select pct, name , if(pct is null, '没有奖金', '有奖金') as 备注 from table
// 如果工资>20000 显示A级别， 如果工资大于> 10000 显示B，其他显示C
select salary,  case when salary > 20000 then 'A'  when salary > 10000 then 'B' else 'C' end as "工资级别" from table

| salary | 工资级别 | | ------ | -------- || | 20001 | A | | 2000 | C | | 12000 | B |

聚合函数（输出结果1个）

sum() 求和处理数值型，表中列值若无null, 则不参与运算，会被忽略
avg() 求平均值处理数值型该列若无null会被忽略
min()
max()
count() 计算个数

4.5 可以和distinct配合

//查看部门的个数
select  count(distinct dep_id) from table

4.6 count(*)

// count(*)  该行的所有列只要有一列不为null,  就会被统计到
// count(1) 
// count(列) 该列若为null  会被忽略。只统计该列不为null 的个数
// 总结： 一般使用count(*)统计行数（count(1)结果和count(*)结果一致）

4.7 能和聚合函数一同查询的字段要求是 group by 后的字段

5. 分组查询（select 聚合函数，列A from 表【where 条件】group by 列A 【order by 列】）

注意：

除了聚合函数，若还有其他列，则查询的列必须是group by 后面的列
select 聚合函数，列A from 表【where 条件】group by 列A 【having 条件】【order by 列】
分组前筛选用where 条件(原表中就有的字段)，对分组后的结果筛选用having 条件
分组查询支持按多列分组group by a, b ; 列a和列b相同的行为一组。

// 查询每个工种的最高工资
select max(salary), dep_id from table group by dep_id

| max(salary) | dep_id | | ----------- | ------ || | 20001 | 1 | | 2000 | 3 | | 12000 | 2 |

// 查询每个部门下员工个数大于50的部门的员工个数
select  count(*), dep_id from table group by dep_id having count(*) > '50'
// 查询每个部门每个工种的员工的平均工资，平均工资高于10000并且按照平均工资高低显示，
select avg(salary), dep_id, job_id from table where dep_id is not null group by dep_id, job_id having avg(salary) > 10000 order by avg(salary)  desc

6.连接查询（多表查询）

一：sql92语法（多表之间逗号隔开，连接条件和筛选条件都放到where之后）

6.1等值连接( 连接条件为 = )

多表等值连接的结果为多表的交集部分
n个表连接查询，至少需要n-1个连接条件
多表的顺序没有要求

// 查询有奖金的每个部分的部门名和部分领导者编号和该部门的最低工资
select min(salary), dep_name, d.manager_id from depar_table d, employes e  where d.dep_id = e.dep_id and pct is not null  group by dep_name, d.manager_id

6.2 非等值连接( 连接条件不为 = )

结果为多表的交集部分

// 查询员工工资和对应的级别
select salary, level from s, l where  s.salary between l.low_sal and  l.high_sal

6.3 自连接 (把一张表当多张表使用)

不是任何表都可以自连接
结果为多表的交集部分

// 查询员工名以及上级的领导名称
select e.emply_name, m.emply.name from employee e,  employee m where e.manager_id = m.employ_id

二：sql99语法（多表之间用 join, 连接条件放on之后，筛选条件放where之后）

内连接（多表交集部分）

select 查询列 from t1 【lnner】 join t2 on 连接条件【where 筛选条件】【group by】
连接条件放到on后面，筛选条件放到where后面
内连接查询结果和92语法等值连接结果一致，都是查询多表交集部分。

// 查询部门个数大于3的城市和部门个数
select count(depart_id), city from t1 inner join t2 on t1.id = t2.id group by city having count(depart_id) > 3
// 非等值连接  查询工资级别个数 > 2的级别个数，并且按照工资级别降序
select count(*), salary_level from t1 inner join t2 on t1.salary beween t2.lower and t2.high group by salary_level having count(*) > 2 order by salary_level desc

外连接（主表中所有的记录，从表中没有和它匹配的则从表列信息显示为null）

select 查询列 from t1 left / right【outer】 join t2 on 连接条件【where 筛选条件】【group by】
外连接的查询结果为主表中所有的记录，从表中没有和它匹配的则从表列信息显示为null。
left join 左边是主表，右边是从表

连接查询总结

// 1.查询a b表交集部分 inner join
select * from a inner join b on a.key = b.key【where 筛选条件】
// 2.查询a表所有,且b表中没有匹配到的显示null  a left join b
select * from a left join b on a.key = b.key 【where 筛选条件】
// 3.查询a表所有,且b表中没有匹配到的，对应的行就不显示在结果集中  a left join b 
select * from a left join b on a.key = b.key where b.id is not null 【and  筛选条件】

7.子查询

where 或 having后面的子查询
标量子查询（子查询结果为单行单列）配合 > < = <> 等操作符使用
列子查询（子查询结果为多行单列）配合 in、not in、any/some（可用min替代）、all（可用max 替代）使用

// 1.返回 job_id和141号员工相同，salary比143号员工多的  员工信息
select * from employees where job_id = (select jog_id from employees where e_id = 141)
and salary > (select salary from employees where e_id = 143)
// 2. 返回location_id是1400或1700的部门中所有员工姓名
select name from employees where department_id in ( select department_id from deps where location_id in (1400, 1700)  )

8.分页查询

select 查询项 from 表【where 筛选条件】【group by 】【having 条件】【order by】limit 【offset(索引从0开始), 】size（条数）
若offset为0，则可用省略 offset, select 查询项 from 表 limit size
公式( page size ) select * from table limit (page-1)*size, size

9.联合查询

查询语句1 union 查询语句2 union 查询语句3 （结果为多个查询语句的并集）
查询的列个数、顺序、字段所表示的信息必须一致
union 会默认去重（去掉多个查询语句结果完全重复的行）union all(可以包含重复项)
查询结果为第一个查询语句中的列

// 查询中国用户表中男性的用户信息 和  外国用户表中男性的用户信息
select name, sex from china where sex = '男' union select uname, usex from uni where sex = '男'

| name | sex | | ---- | --- || | 张三 | 男 | | lucy | 女 | | mary | 女 |

增删改语句

1. insert into

insert into table (列1，列2...) values (值1，值2，...) , (值11，值22，...)
不能为null的列除非设置默认值，否则必须插入值。
列的顺序和值的顺序以及个数必须一致
可以省略列，那就表示插入表中所有列，而且顺序必须跟表中字段顺序一致。

2. update

update table set 列1 = 值1，列2 = 值2 where 筛选条件
匹配到的就会更新

3. delete

delete from 表名 where 筛选条件
匹配到的就会删除

DDL语言

修改: alter 创建: create 删除: drop

1. 库

创建库 create database 【if not exists】库名
修改库字符集 alter database 库名 character set gbk(默认字符集utf8)
删除库 drop database 【if exists】库名

2. 表

展示库里所有表 show tables
desc 表名查看表结构
创建表 create table 表名（列名字段类型【长度】【约束】）
修改表 alter table 表名 add|drop|modify|change column 列名【列类型约束】
删除表 drop table 【if exists】表名
通用写法 drop table if exists 表名; create table 表名（）；

// 修改列名
alter table 表名 change column 原列名  新列名  新列的类型
// 修改列的类型和约束
alter table 表名  modify  column 列名 新类型
// 添加新列
alter table 表名 add column 新列名 类型 约束
// 删除列
alter table 表名 drop column 列名
// 修改表名
alter table 原表名  rename to 新表名

3.数据类型

3.1 数值(整型 + 浮点型 java层 Integer、Decimal、Float)

定点型decimal(M,d) 精确度比fload(M,d) double(m,d)高

// 如果插入的数据超出了整型的范围，会报警，并且插入的是临界值
// 默认是无符号位
// 整型  int---->Integer
// 浮点型 fload(M,d) double(m,d)
// 定点型 decimal(M,d) m表示总共位数(整数位数 + 小数位数)，d（小数位数），若插入超过限定位数，则会报警，值为临界值。

3.2 字符型 (char varchar text blob【图片】 java层String)

char varchar 较短的字符
text 较长的字符
char(M) M: 最多的字符数，固定的字符数，比较耗费空间，效率高（性别）(M可以省略，默认为1)
varchar(M) M:最多的字符数，可变长度的字符数，比较节省空间，效率低

3.3 日期型 (java层 Date)

date(日期) / time(时间) / year(年) / timestamp（日期+时间）/ datetime（日期+时间）
timestamp和datetime
set time_zone = '+8:00'

// datetime     8个字节     1000 --- 9999（年） 不受时区影响
// timestamp  4个字节     1970---2038（年）   受时区影响（推荐使用）

4.约束

create table 表名 (列类型长度列级约束，...，表级约束)
not null / default / primary key(唯一且不为空) / unique(唯一可以为空) / foreign key(用于限制两个表的关系，用于该表的字段的值必须来自主表的关联值)
not null / default 不可用于表级约束
foreign key 不可以用于列级约束

create table stu(
    id int primary key,
	stuname varchar(20) not null,
	majorid int,
	gender char(1) , 
	seat int ,
	age int default 18,
	// 添加表级约束： 【constraint 约束名】 约束类型（字段名）
   constraint  pk_id primary key (id), ---主键
   constraint uq_seat unique(seat),   ----唯一键
   constraint fk_majorid foreign key(majorid) references major(id) ---外键
)

create table major(
   id int,
   major varchar(20)，
   major_name  unique, --唯一键
   primary key(id, major) ---组合主键，不能插入id, major都相同的行，有一者相同是可以插入的
)

主键 primary key 和唯一键unique对比

// 主键：   保证唯一性、非空、一个表最多一个主键、组合主键（多个列构成一个主键）
// 唯一键：保证唯一性、可以为空（但是只能允许最多一行为空）、一个表可以允许多个、组合唯一键

外键 foreign key

// 外键所关联的主表列必须是主键或者唯一键或者外键
// 从表的外键列的类型必须和主表的关联列类型一致，名称可以不同
// 有外键时，必须先插入主表的数据，再插入从表的数据;  删除时，必须先删除从表的数据，再删除主表的数据
//

修改表时添加约束、删除约束

// 1.添加非空、默认约束
alter table stu modify column stuname  varchar(20) not null default 'zs'
// 2. 添加主键
alter table stu modify column id int primary key;
alter table stu add primary key(id)
// 3. 添加唯一约束
alter table stu modify column seat int unique
alter table stu add unique(seat)
// 4.添加外键 主表是major  从表是stu
alter table stu add 【constraint fk_stu】 foreign key(majorid) references major(id)

DROP TABLE IF EXISTS "public"."student";
CREATE TABLE "public"."student" (
    "id_s" varchar(32) NOT NULL DEFAULT sys_guid(),
    "s_no" varchar(10) not null,  -- String
    "s_name" varchar(20) not null,
    "s_sex" varchar(1) not null,
    "s_money" decimal(20,2), --BigDecimal  包括整数位总共20位数字
    "is_delete" char(1) default '0',
    "created_date" timestamp default now() not null,  --Date
    "updated_date" timestamp default now() not null ,
    "created_by" varchar(100) default 'admin' not null,
    "updated_by" varchar(100) default 'admin' not null,
    constraint pk_id_s primary key("id_s")  --主键
)
with (
OIDS = FALSE
);

--授权
ALTER TABLE public.student owner to admin;
-- 添加注释
COMMENT ON table "public"."student" is '学生信息表';
COMMENT ON COLUMN "public"."student"."id_s" is '主键';
COMMENT ON COLUMN "public"."student"."s_no" is '学号';
COMMENT ON COLUMN "public"."student"."s_name" is '姓名';
COMMENT ON COLUMN "public"."student"."s_sex" is '性别(0：男 1：女)';
COMMENT ON COLUMN "public"."student"."s_money" is '金额';
COMMENT ON COLUMN "public"."student"."is_delete" is '逻辑删除标识(0：未删除 1：删除)';

--创建唯一索引、普通索引
create unique index uk_s_no on "public"."student"("s_no");
create index idx_s_sex on "public"."student"("s_sex");

--授权 操作权限
grant all on table public.student to admin;
grant select,update,insert,delete on table public.student to admin_dml;
grant select on table public.student to admin_qry;

项目中的应用

insert into 插入集合数据

// 其中 list 为mapper中@Param("list")
<insert id="batchInsertStudent" parameterType="com.entity.Student">
    insert into "public"."student" (s_no,s_name) values
    <foreach collection="list" separator="," item="item">
        (#{item.sNo},#{item.sName})
    </foreach>
</insert>

插入单个bean

<insert id="insertStudent" parameterType="com.entity.Student">
    insert into "public"."student" (s_no,s_name) values (#{sNo},#{sName})
</insert>

插入map中的集合

// 其中userList为map中对应集合的key名
<insert id="insertUserList" parameterType="map">
    insert into "public"."user" (username,age,sex,hobbits) VALUES 
    <foreach collection="userList" open="" close="" separator="," item="item">
        (#{item.username},#{item.age},#{item.sex},#{item.hobbits})
    </foreach>
</insert>

update

<update id="updateById">
    update sys_user
    set
    <if test="userName != null and userName !=''">
        user_name =#{userName},
    </if>
    user_password =#{userPassword},
    where id =#{id}
</update>

select 返回一般数据类型的值(Integer 、String 、Date 、int等)

mapper层中传入多个参数的方法
User Sel(int id); //根据id查询
<select id="Sel" resultType="java.lang.String"> //注意这个写了类全名 
    select username from user_test where id = #{id}
</select>

当返回类型是javaBean

User Sel(int id); //根据id查询
<select id="Sel" resultType="com.tx.springboottestdemo.entity.User">
    select * from user_test where id = #{id}
</select>

当返回是List类型

List<User> getUsers();
<select id="getUsers" resultType="com.tx.entity.User">
select * from user
</select>

mapper层中传入多个参数的方法

public List<Us> findU( @Param("name1") String name1, @Param("name2") String name2);

<select id="findU" resultType="com.entity.Us">
    select * from user_test where userName = #{name1} and realName = #{name2}
</select>

java---sql