【Java基础】SQL语法（不定时更新收集向）

MySql

• 连接Mysql

  mysql -u root -proot    -h连接地址
  quit; exit;		 退出
  

• 查看当前所有的数据

  show databases;
  

• 创建数据库

  create database 数据库名;
  create database test;
  

• 删除数据库

  drop database 数据库名;
  drop database test;
  

• 设置编码 UTF-8

  create database 数据库名 character set 编码;
  create database test character set utf8;
  

• 创建数据库并设置

  CREATE DATABASE mydb3 character SET utf8 COLLATE utf8_general_ci;
  

• 如果不存在数据库，创建数据库

  create database IF NOT EXISTS test character set utf8;
  

• 切换数据库(一定要注意，在操作表的时候。一定要先选择数据库)

  use 数据库名;
  use test;
  

• 查询当前使用的数据库

  select database();
  

• 修改数据库的编码

  alter database 数据库名 character set 编码格式
  alter database test character set gbk;
  

DDL

• 查看当前数据库下所有的表

  show tables;
  

• 创建表

  create table 表名(字段名 字段的数据类型 [约束] , ....);
  

• 删除表

  drop table 表名;
  drop table user;
  

• 查看表的结构

  desc 表名;
  desc user;
  

对表操作

• 添加一个字段 （生日：birthday date）

  alter table 表名 add 字段名 字段的类型;
  alter table user add birthday date;
  

• 修改字段的类型

  alter table 表名 modify 字段名 字段的类型;
  alter table user modify name varchar(20);
  

• 修改字段的名字

  alter table 表名 change 旧字段名 新字段名 字段的类型;
  alter table user change name username varchar(20);
  

• 删除字段

  alter table 表名 drop 字段名;
  alter table user drop age;
  

• 修改表的名字

  rename table 旧表名 to  新表名;
  rename table user to tb_user;
  

DML 增删改

• 添加数据

  //添加数据
    insert into 表名(字段1,字段2....)values(值1,值2,...);
    insert into student(id,name,indate)values(1,'xx','2016-09-10');
  //简写
    insert into 表名 values (值1,值2....);值要与字段一致
  //批量添加数据
    insert into 表名 values (值1,值2,...),(值1,值2,...)....;
  

• 删除

  // 删除数据 (不带条件的删除) 谨慎删除
    delete from 表名;
  // 带条件删除
    delete from 表名 where 条件;
    delete from student where id=1 ;
  

• 修改

  //不带条件的修改 修改表的全部数据
  	update 表名 set 字段名 字段值,字段名1 字段值2,...;
  //待条件修改
  	update 表名 set 字段名 字段值,字段名1 字段值2,...where 条件;
  	update student set age=23 where id = 5;
  

DQL 查询

• 查询指定的字段

  select*from 表名;
  

• 查询指定的字段

  select 字段名1,字段名2,...from 表名
  

• 查询 and 并且

  select * from stu where gender = 'female' and age < 50;
  

• 查询 or 或者

  select * from stu where  sid = 'S_1001' or sname = 'liSi';
  

• 查询多个数据(包括)

  // 方法1：使用or
  select * from stu where sid = 'S_1001' or sid = 'S_1002' or sid = 'S_1003';
  //  方法2：使用in
  select * from stu where sid in('S_1001','S_1002','S_1003');
  

• 查询多个数据(不包括)

  select * from stu where sid not in('S_1001','S_1002','S_1003');
  

• 查询数据(不包括)

  select * from stu where gender = 'female';
  select * from stu where gender != 'male';
  select * from stu where gender <> 'male';
  

• 查询 null

  SELECT * from stu where age is NuLl;
  

• 查询不为null

  select * from stu where sname is not null;
  

• 查询范围的数据

  //方法1: 使用and
    select * from stu where age >=20 and age <= 40; 
  //方法2：使用between and   (包括首尾的两值)
    select * from stu where age between 20 and 40;
  

• 模糊查询

  模糊查询的通配符
  % 0个或者多个字符
  _ 确定是一个字符

  //  查询带s的信息
  	select * from 表名 where 字段名 like  值;
  	select * from stu where sname like  '%s%';
  //  查询第三字母带s的信息
  	select * from stu where sname like '__s%';
  //  注意:如果模糊查询中没有通配符，那么其含义相等于  sname = 'cxk'
  	select * from stu where sname like 'cxk';
  

• 去重查询

  select distinct 字段名,字段名,.. from 表名
  select distinct sal,comm,ename from emp;
  

• 计算查询

  select ename,sal+comm from emp;
  

• 数字类型与null计算的结果为null

  select ename,sal+ifnull(comm,0) from emp;
  

• 取别名

  select ename,sal+ifnull(comm,0) as '薪资' from emp;
  

• 简写方式

  select ename,sal+ifnull(comm,0) '薪资' from emp;
  

• 排序 desc 表示降序 asc 表示升序(默认)

  //降序
    select * from 表名 order by 字段名 [排序方式]; 
    select * from emp order by sal desc;
  //降序 如果工资相等再按照入职日期升序排序
    select * from emp order by sal desc,hiredate asc;
  

• 分页查询

  //  分页查询
  	select * from 表名 limit n;   n查询条数
  	select * from 表名 limit n,m;   n位置， m查询条数
  //  查询前五条数据
  	select * from emp limit 5;  
  //  查询前五条数据 第二种方式
  	select * from emp limit 0,5;  
  // 查询第二页的五条数据 
  	select * from emp limit 5,5;  
  // 查询第三页的五条数据 
  	select * from emp limit 10,5;  
  // 分页公式   已知条件：当前页currentPage   每一页的条数 pageSize
  	select * from emp limit (currentPage-1)*pageSize,pageSize;  
  

• 聚合函数

  //求最大值max(字段)
  	select max(sal) from emp;
  //求最小值min(字段)
  	select min(sal) from emp;
  //求平均值avg(字段)
  	select avg(sal) from emp;
  //求和    sum(字段)
  	select sum(sal) from emp;
  //求个数  count(字符)  不包含为null的数据 *统计函数
  	select count(*) from emp;
  

• 分组查询

  // 分组查询 
  select * from 表名 group by 字段
  // 查询每个部门的部门编号和每个部门的工资和：
  	select deptno,sum(sal) from emp group by deptno;
  // 查询每个部门的部门编号以及每个部门的人数：
  	select deptno,count(*) from emp group by deptno;
  // 查询每个部门的部门编号以及每个部门工资大于1500的人数：
  	select deptno,count(*) from emp where sal > 1500 group by deptno;
  // 查询工资总和大于9000的部门编号以及工资和
  	select * from 表名 group by 字段 having 条件
  	select deptno,sum(sal) from emp group by deptno having sum(sal) > 9000;
  

多表查询

• 连接查询
  内连接

  // 方法1
  	select 查询的字段|*from 主表,从表 where 主表.主键=从表.外键;
  	select 查询的字段|*from 主表 主别名,从表 从别名 where 主别名.主键=从别名.外键;
  // 方法2
  	select 查询的字段|*from 主表 join 从表 on 主表.主键=从表.外键;
  

  外连接

  // 左外连接
  	select 查询的字段|*from 左表 left join 右表 on 主表.主键=从表.外键;
  // 右外链接
  	select 查询的字段| * from 左表 right join 右表 on  主表.主键=从表.外键;
  

  全连接(MySQL不支持)

  // 左连接+右连接 (去重)
  	左连接  union  右连接			
  	union：合并两结果集(去重)
  	union all:和并两个结果集(不会去重)		
  

• 子查询
  在一个select中出现另外一个select语句
  子查询一般用在where 子句后面from 子句后面
• 子查询的结果
  单行单列 (用于条件)
  多行单列 (用于条件)
  多行多列 (from子句)
• 关键字的用法 all any
  all 大于最大的 < all 小于最小的
  any 大于最小 < any 小于最大的

注：having与where的区别:
1.h集合aving是在分组后对数据进行过滤,where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)，where后面不可以使用分组函数。
聚合函数不能在where子句中使用
总结：
sql简单查询
④select 字段1,字段2,字段3,… | *
①from 表名
②where 判断条件
③group by 字段名 having 判断条件
⑤order by 字段名
⑥limit n,m;

约束

• 主键 primary key (非空+唯一)

  // 方式1
  	create table 表名(
  		字段名 数据类型 primary key
  	);
  // 方式2
  	create table 表名(
  		字段名 字段类型,
  		..........,
  		primary key(字段名)
  	);
  // 方式3
  	alter table 表名 add primary key(字段);
  

• 联合主键

  create table 表名(
  	字段名 数据类型,
  	字段名 数据类型,
  	.....,
  	primary key(字段1,字段2)			
  );
  

• 唯一约束 unique

  create table 表名(
  	字段名 字段类型 unique,
  	....
  );
  

• 主键自增长 auto_increment

  create table(
  	字段名 字段类型 primary  key auto_increment
  );
  

• 域完整性

  //非空约束not null 
  create table 表名(
  	字段名 字段类型 not null,
  );
  

  默认值约束 default [值]

  create table 表名(
  	字段名 字段类型 default 值,
  );
  

• 外键约束

  //从表
  create table 表名 (
  	字段名  字段类型 主键
  	CONSTRAINT fk_主表_外键 foreign key(外键) references 主表(主键)
  );-设计主表
  create table 表名 (
  	字段名  字段类型 主键
  );