mysql 指令格式

关系型:以行作为记录,列数相同
非关系型:以列作为记录,行数随便

服务器->数据库->表(行列组成的二维表格)->行
client操作服务器通过命令登录操作:mysql -u用户名(默认root) -p密码

SQL语句:每个命令执行结束加分号结束
查询所有数据库:show databases;
切换数据库:use 库命名;
创建数据库:create database [IF NOT EXISTS] 库名;
删除数据库:drop database [IF EXISTS] 库名;
查询数据库创建:show 建库语句;
指定数据库采用的字符集:CHARACTER SET
注意:不要修改mysql服务器的编码集,表的编码集默认和库一致

建表
格式:
create table [if exists] 表名(
字段1 数据类型 字段属性,
字段2 数据类型 字段属性,
…
字段N 数据类型 字段属性
)engine=引擎 default charset=编码集;
查看当前数据库:select database();
查看建表语句:show create table 表名;
查看表结构:desc 表名;
删除:drop table [if exists] 表名;

字段属性:
	
	not null:没给值数据为默认值(varchar默认值为空)
	AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1
	PRIMARY KEY关键字用于定义列为主键,您可以使用多列来定义主键，列间以逗号分隔
	ENGINE 设置存储引擎，CHARSET 设置编码
	default null:没给值数据就是null
	default 值:设置字段的默认值
注意:主键不重复的列

修改表结构:alter table
修改表名:rename table 旧表名 to 新表名;
添加字段:alter table 表名 add 字段 字段数据类型 属性;
修改字段:alter table 表名 change 旧字段 新字段 数据类型 属性;
修改字段:alter table 表名 modify 字段 数据类型 属性;
注意:
change:修改所有(字段名,数据类型,属性)
modify:修改一部分(数据类型,属性)

增删改查:字符串全部使用’'包起来
增:
格式:
insert into 表名(字段) values(值),(值)…(值);
例如:
insert into stduent(name) values(‘zs’);
insert into stduent(name) values(‘zs’),(‘ls’);
删
格式:
delete from 表名 where 子句;
改
格式:
update 表名 set 字段1=值1,字段2=值2…字段N=值N where 子句;

查
	格式:
		select 字段 from 表名 where 子句;
	注意:
		*表示所有字段

as起别名
格式:
字段 as 名称
注意:
as 也加可不加
子句:
> < <= >= = <> 大于、小于、大于(小于)等于、不等于
between …and… 显示在某一区间的值(含头含尾)
in(set) 显示在in列表中的值，例：in(100,200)只能匹配100或200
like ‘张_’ 模糊查询 使用% 和 _(%表示匹配所有 _匹配一个)
Is null 判断是否为空

and	多个条件同时成立
or	多个条件任一成立
not	不成立，例：where not(expection>10000);

limit分页
格式:
语句 limit 开始下标,长度;
注意:
如果数据量不够,显示全部
例如:从下标为0开始显示10条
select * from stduents limit 0,10;

去重
格式:
DISTINCT 字段1,字段2…字段N
注意:
字段不能在DISTINCT之前,只能在DISTINCT后面
DISTINCT之后有多个字段,按照所有字段进行去重

聚合函数:
count(字段):求多少行数据
sum(字段):求和
avg(字段):平均数
max(字段):最大值
min(字段):最小值
注意:
varchar能比较大小,不能获取avg(没有任何意义)
如果值为Null不参与计算
sum和avg字段的数据不是数值,结果都是0
字段进行算术运算
格式:
(字段 符号 字段)
例如:
select (name+age) from students;
注意:
字符串参与运算字符串为0参与运算

拼接:
格式:
concat(str1,str2…)
例如:把name和age以-拼接显示
select concat(name,’-’,age)from students;
格式:
concat_WS(separator,str1,str2,…)
例如:把name和age以-拼接显示
select concat(’-’,name,age)from students;

日期函数
获取当前日期:
current_timestamp;–所有
current_timestamp();–所有
CURRENT_DATE();-- 年月日
CURRENT_DATE;-- 年月日
CURRENT_TIME();-- 时分秒
CURRENT_TIME;-- 时分秒

时间转str
	格式:
		date_format(date,format)
		date:时间
		format:格式
str转日期
	格式:
		str_to_date(str,formaat)

日期相减
格式:
datediff(expr1,expr2);
注意:只能相减年月日,时分秒参与运算结果为null

函数向日期添加指定的时间间隔
格式:
DATE_ADD(date,INTERVAL expr unit);
date:时间
INTERVAL:关键字
expr:间隔的数值
unit:年月日时分秒(…,…,day,…,…,…)

数组计算
round(x,d):四舍五入
x:值
d:保留几位小数点

ceil(x):向上取整
floor(x):向下取整
rand():随机数(0-1之间)

排序
格式:
order by 字段1 asc|desc,字段2 asc|desc…字段n asc|desc;
例如:按照age进行降序排列,age相同按照id进行降序排列
select * from students order by age desc,id desc;
注意:
默认升序asc,降序desc
如果有多个字段,按照先后顺序依次排序
group by 分组
格式:
group by 字段1,字段2…字段n;
注意:
多个字段,按照所有字段进行分组(一起分组)
有多少组显示多少条数据(默认情况下,没有经过条件筛选)
每组显示的数据为每组中默认第一条数据
gruop by 通常和聚合函数一起使用

筛选:where having
区别:having可以使用聚合函数
例如:
select * from students where age>=25;可以
select * from students having age>=25;可以
select sex,count() c from students group by sex where c>4;不可以
select sex,count() c from students group by sex having c>4;可以

TopN:前几条数据
1.TopN age最大的前三个
select * from students order by age desc limit 0,3;
2.分组Top1 按sex分组后,求分组中年龄最大的一个
1.select * from students where age in (select max(age) m from students group by sex);
2.select * from students as stu1 where age=(select max(age) from students as stu2 where stu1.sex=stu2.sex);

2.分组TopN 按sex分组后,求分组中年龄最大的三个
	select * from students as stu1 where 3>(select count(*) students as stu2 where stu1.sex=stu2.sex and stu1.age<stu2.age);

mysql三大范式
1.原子性:字段不可在分割
2.唯一性:字段依赖于主键
3.冗余性:数据量过大

连表联查
union:结果集进行合并(纵向合并)
格式:
查询语句 union 查询语句
注意:
查询列数必须相同
字段为第一个sql语句的字段
union默认去重
union all不去重

left join(以左表为基准关联右表中的数据)
	格式:
		select * from 左表 left join 右表 on 关联条件;
	注意:
		左表匹配不到右表,以null不全
		右表匹配不到左表,不显示
right join(以右表为基准关联左表中的数据)
	格式:
		select * from 右表 left join 左表 on 关联条件;
	注意:
		左表匹配不到右表,不显示
		右表匹配不到左表,以null不全

inner join(求两张表的交集)
	格式:
		select * from 右表 inner join 左表 on 关联条件;
	注意:
		关联条件可写可不写
		如果不写可以写为select * from 表1,表2 where 子句;

case:值的替换
格式:
case when 条件 then 为true的结果 [when 条件 then 为true的结果] [else 为false的结果] end

从其他表加载数据
格式1:
create table 表名 as 查询语句;
格式2:
insert into 表名 查询语句;
注意:字段属性和数据类型和其他表保持一致

视图:由查询结果得到的一张虚拟表(临时表,虚表),虚拟表和基本表有一对一和一对多的关系
创建:
create view 视图名称 as 查询语句
一对一(数据和基本表一样):增删改查
一对一(数据由基本表聚合):查
一对多(数据由基本表聚合):查
一对多(数据由连表联查):查改
注意:虚拟表不存储任何数据,数据存储在基本表中,基本表当中数据发生改变虚拟表中的数据也能发生改变
创建视图,查询语句中不能出现子查询(不能作为一张表,可以作为结果进行筛选),把子查询作为视图之后再创建视图

查询视图结构:
	desc 视图名称;
	show create view/table 视图名称;

删除视图:
	drop view 视图名称;

执行计划
from–where–group by–having–select–order by
from:需要从哪个数据表检索数据
where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据

索引
添加:
alter table 表名 add index 索引名称(字段);
删除:
alter table 表名 drop index 索引名称;
注意:每个字段都可以添加索引
主键默认含有索引
不要每个字段都添加索引(索引也需要消耗资源),只需要给常用字段添加索引

事务:批处理,保证多个sql语句一起执行成功或一起执行失败(回滚撤销当前操作)
开始事务:
begin;
回滚事务:
rollback;
提交事务:
commit;
结束事务:
end;

注意:没有commit或end之前,事务中的所有操作都是临时的(在缓存中的)
commit或end之后,把这些临时的(在缓存中的)数据提交到原始数据中进行修改
end默认执行commit;

shell操作mysql
#!/bin/sh
MYSQL=“mysql -hmaster -uroot -p123456”
sql=“select * from shujia.student where sex=‘0’”
result="$($MYSQL -e “$sql")"echo"$result”

java操作mysql
导入第三方工具包
file->project structure->Modules->Dependencies->±>JARs or dir…->选择包->apply

1.加载第三方工具
	Class.forname("com.mysql.jdbc.Driver");
2.获取连接
    String url="jdbc:mysql://master:3306/shujia";
    String username="root";
    String password="123456";
    Connection connection = DriverManager.getConnection(url, username, password);
3.获取执行器 createStatement(会出现sql注入不使用)和prepareStatement
	PreparedStatement ps = connection.prepareStatement(sql);//给sql的格式(模板)
    ps.setString(1,"1012");
    ps.setString(2,"test");
4.获取结果(sql语句为增删改查操作,不需要解析结果,使用executeUpdate())
	ResultSet rs = ps.executeQuery();
    while (rs.next()){
        String name = rs.getString("name");
        System.out.println(name);
    }
5.关闭连接(从下向上关闭)
	rs.close();
	ps.close();
	conn.close();

sql注入:参数中有mysql命令,而mysql把这些关键字当做命令去执行
prepareStatement:避免了sql注入,首先发送sql的格式,然后在传递参数(参数中有关键字也作为参数执行)
prepareStatement传参:通过set数据类型(int prepareIndex,数据类型 x)
注意:index从1开始

创建连接和关闭需要写很多次,可以把连接和关闭写入到工具类中,再次使用时,直接调用工具类,避免多次书写创建连接和关闭
public class JDBCUtil {
private static String driver=“com.mysql.jdbc.Driver”;
private static String url="+

+";
private static String username="root";
private static String password="123456";
static {
    try {
        Class.forName(driver);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}
//获取连接的方法
public static Connection getConnection() throws Exception {
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
}
public static void closeAll(PreparedStatement ps,Connection conn)throws Exception{
    ps.close();
    conn.close();
}
public static void closeAll(ResultSet rs,PreparedStatement ps, Connection conn)throws Exception{
    rs.close();
    ps.close();
    conn.close();
}

}

创建资源目录
1.创建普通目录(建议名称为 resource)
2.通过project st…设置为资源目录

创建配置文件(创建在资源目录中)
1.格式必须后缀为properties