SQL数据库语法mysql，Java语言，个人整理资料

结构化语言
Sql（structured query language）结构化查询语言

DML 用来操作数据库中所包含的数据 （数据操作语言）

Update 数据修改

 

DDL 用于创建和删除数据库对象等操作 （数据定义语言）

Insert 数据新增

Delete 数据删除

 

DQL 用来对数据库中的数据进行查询 （数据查询语言）

Create 创建数据库或表

Drop 删除数据库或表

Alter 修改数据库或表信息

Select 查询数据库

 

DCL 用来控制数据库组件的存储许可、存储权限等 （数据控制语言）

Commit 提交数据库

Rollback 回滚数据

Grant 授权

 

优势
运行速度快

体积小成本低

可移植性好

适用用户广

作用
存储数据、便于数据检索

保证数据完整、一致

共享、安全

对数据进行重新组合

数据库和应用之间的关系
数据库：存储数据，将数据响应给应用程序

应用程序：向数据库发请求，将数据库响应的信息进行展示

流行的数据库
Oracle SQL server mysql

配置
Prot：设置端口号

Default-charactrt-set设置编码格式

Datadir：设置数据保存路径

服务 启动方式
1 我的电脑-管理服务-对应服务右键启动

2 管理员cmd-net start 服务名称

3 管理员cmd-net stop 服务名称

登录 mysql -h地址 -u 用户名 -p 密码 （-h可省略，用于异地登录）

退出 quit

运算符
逻辑运算符
AND 并且

OR 或者

NOT 非

比较运算符
= 等于

< 小于

大于

<> 不等于（常用）

= 大于等于

<= 小于等于

!= 不等于

数据库操作
Database（删除创建，判断是否存在）
Create database数据库名 （创建数据库）

Drop database 数据库名 （删除数据库）

Drop database if exists数据库名 （如果存在删除数据库）

Table
Use 数据库名 （指定数据库）

Create table 表名 （创建表名）

Create table 表名（头大1 定义类型（长度），头大1 定义类型（长度），……）

Show tables （查看表）

Desc 表名 （查看指定表内容）

 

Insert into 表名 values（值1，值2） 主要顺序、数量、类型要与表字段一致

Select * from 表名 查看表内所有字段内容（查询数据）

Select 字段名 from 表名

 

Delete from 表名 删除表内所有内容

Delete from 表名 where字段=值 删除指定内容

Delete from 表名 where字段1=值 and 字段2=值 删除指定多个内容

 

Truncate table 表名 （清空数据并将标识恢复到默认开始）

 

Update 表名 set 字段=值，字段=值 where 字段=值 修改内容

 

创建表后添加另一个表的数据：insert into 表1  select * from 表2;

 

重命名： alter table 表名 rename【to】 新表名

添加字段： alter table 表名 add 字段 类型 【属性】

修改字段： alter table 表名 change 原字段 新字段 类型 【属性】

删除字段： alter table 表名 drop 字段名称

约束
Unique key 唯一约束

Not null 非空约束

Default 默认约束

Primary key 主键约束

Foreign key 外键约束

Constraint fk自启表名 foreign key(要设置的字段) references 主表名（被设置字段）

Auto_increment 自动增长

 

在创建表结构后添加约束

添加主键

alter table 表名 add Constraint 约束名 primary key 表名（主键字段）

添加外键

alter table 表名 add Constraint 约束名 foreign key(要设置的外键字段) references 主表名（被设置字段）

删除主外键

Alter table 表名 drop constraint 约束名

帮助
Help data types 查看数据类型

Help 类型 查看具体信息

设置编码格式：set names gbk

Comment：注释 每个字段添加（注释）

引擎
指定存储引擎
常见存储引擎：inniDB支持事物用于增删 MyISAM不支持实物适用于查询

Create table 表名（……）engine=存储引擎

 

配置

My.ini文件

Default storage engine = innoDB；

查看

Show variable like storage_engines;

数据查询
Select 列名 from 表名 where 查询条件 order by 排序的列名 asd或desc

 

查询使用别名

Select 字段 ’编号’ ，字段名 as ‘名字’from 表名

 

非空查询

Select 字段 from 表名Where 字段 is not bull

Select * from 表名Where 字段 is not bull

聚合函数
Avg 平均值

Count 字段行数

Max 最大值

Min 最小值

Sum 和

 

Select 聚合函数（字段） from 表名;

字符串
Select Concat（‘字符’，‘字符’） 拼接字符串

Select Lower（‘字符’） 转换小写

Select Upper（‘字符’） 转换大写

Select Substr（‘字符’，截取开始，截取长度） 截取字符串

Select insert（‘字符’，替换开始，替换长度，‘字符’） 替换指定区间字符

日期
获取当前时间：

Select NOW（）； 当前日期时间

Select curdate（）； 当前日期

Select curtime（）； 当前时间

拆分当前时间：

select YEAR(NOW()); 当前年份

select MONTH(NOW()); 当前月

select bay(NOW()); 当前当前日

select WEEK(NOW()); 当前周

计算时间：

Select darediff（‘时间’,now()）； 两个时间的间隔

select adddate(NOW(),n); 获得那天后的日期

Mysql数据库的链接
创建用户
Create user 用户名；

管理员登录

Grant select on . to 用户名 @对方地址 identified by 密码

Grant 权限 on 数据库.表 to 用户名 @l主机地址 identified by 密码

主机地址：主机名、ip、%（允许任意地址远程访问）

删除用户：
访问 mysql.user 表，通过delete删除（注意where条件）

修改密码
Mysqladmin -uroot -p（‘修改后的密码’）

 

忘记密码情况下修改密码：

停止服务 在my.ini配置文件中写入skip-grant-tables启动服务dos中使用mysql -uroot -p

在输入updare mysql user set password=PASSWORD(‘新密码’)where user=’root’ and host=’ 地址’；

Quit退出

停止服务奖my.ini中的skip-grant-tables注掉

启动服务正常登陆

 

Update user 用户名 set password=PASSWORD（‘修改后的密码’）

Update user set password=PASSWORD（‘修改后的密码’）

授权
All所有权限

Grant 授权（以逗号隔开）on 数据库名. 表名 to 用户名

 

收回

Revoke 权限（以逗号隔开）on 数据库名. 表名 from 用户名 

设置密码
Set password for 用户名=password（‘密码’）；

操作技巧
发现执行命令有错误用ctrl+c强制退出

光标上下键访问用过的命令

 

Flush privileges 命令更新（刷新）

 

数据库操作
导出数据库
Mysqldump -u用户名 -p 密码 数据库名>导出的文件名

 

导出一个代表
Mysqldump -u用户名 -p 密码 数据库名 表名>导出的文件名

 

导出一个数据库结构
Mysqldump -u用户名 -p 密码 -d -add-drop-table 表名>文件名.sql

-d没有数据

-add-drop-table在每个create语句之前增加一个drop table

 

数据传入
Load data local infile “文件为位置（TXT文件）” into table 表名

导入sql文件
Mysqldump -u用户名 -p 密码 表名>文件名.sql

排序
Order by 字段名称 [排序方式（可省略）]

Asc升序排练

Desc降序排练

 

如：select * from pen order by cityed ASC;

select * from pen where cityed=2 order by id desc;

 

分组
max（）最大值

Min（）最小值

Count（）个数

Avg（）平均数

Sum（）求和

Select 函数（字段） from 表名 group by 字段

 

条件

Select 字段1，函数（字段） from 表名 group by 字段1  having 字段1=条件

多表联查 链接查询
select 字段,字段,字段 from 表名1 表别名1,表名2 表别名2 where 字段=别名.字段;

 

内联Inner join 表名 on 条件

左外接Left  join 表名 on 条件

右外接Right  join 表名 on 条件

select 别名.字段,字段,字段 from 表名1 表别名1 inner join 表名2 表别名2 on 表别名1 .字段=表别名2.字段 ;

事物
事物具有：原子性，一致性，隔离性，持久性

步骤
开始事物（begin或start transaction）——提交事物（commit）——回滚事物（rollbank）；

 

关闭/开启自动提交：set autocommit=0|1；

视图
筛选表中的行

防止未经许可的用户访问敏感数据

将多个物理数据表抽象为一个逻辑数据表

 

查看所有视图名称
Use 数据库

Select 表名 from views

 

创建视图
View

 

Use 数据库

Create view 视图名1  as select * from 表名

select * from 视图名1  

 

索引
Index

 

Create 索引类型（可不写）index 索引名 on 表名（字段）

 

Drop index 索引名 on 表

 

Show index 索引名

 

提高数据库的检索速度

 

标准
平凡搜索的列

规范化
关系
一对一，一对多，多对一，多对多

三大范式
1.确定

 

Java连接数据库
添加jar包
在项目根目录创建lib文件夹将jar包考入

在jar包上右键选择Build Path——Add Build Path

注：加载驱动的类在——com.mysql.jdbc.Driver

编写链接类
注：链接数据库使用JavaAPI（Java Application Program interface）

Connection：链接

Statement：执行sql语句

ResultSet：结果集

具体步骤
1加载驱动创建链接
加载驱动

Class.forName(“com.mysql.jdbc.Driver”);

创建链接

DriverManager.getConnection（“jdbc：mysql：//数据库地址：端口号/数据库名”，“用户名”，“密码”）

编写sql并执行
创建statement对象

Statement st=conn.createStatement();

通过对象调用执行方法

Execute(sqlstr);返回boolean类型

ExecuteQuery(sqlstr);返回ResultSet类型，结果集

executeUpdate(sqlstr);返回int类型，受影响行数

处理返回结果
Boolean、int直接赋值对应类型变量 用于判断或输出返回

ResultSet通过while循环便利取值

next（）：返回boolean类型 判断结果集中是否有数据存在

取值：gettype（int/string）

Type对应数据库的类型自定义如：getInt（int/string）

int/string取值时可使用字段的索引位置（索引默认从1开始）或直接写字段

 

事例：

package demo;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class demo {

/**

 * java对应的Api

 * connection：负责java链接数据库

 * statement：负责执行sql语句

 * resultset：处理结果

 */

public static Connection conn;

public static Statement stat;

public static ResultSet res;

public static Connection getConnection() throws Exception{

//加载驱动

Class.forName("com.mysql.jdbc.Driver");

//获取链接（指定数据库:地址,名称，用户名，密码）

return DriverManager.getConnection("jdbc:mysql://localhost:3306/mybank","root","296768022");

}

public static void main(String[] args) {

try {

//创建链接(获取到链接对象后一定要赋值)

conn=getConnection();

System.out.println("链接成功");

//编写sql语句

Statement st=conn.createStatement();

boolean isOk=st.execute("select * from school");

//查看结果

res=st.executeQuery("select * from school");

System.out.print(isOk);

while(res.next()){

System.out.println(res.getInt("sch")+"——"+res.getString("schName")+"——"+res.getString("schId"));

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

System.out.println("链接失败");

}

}

}

注意事项
链接字符穿的拼写

创建链接对象是注意赋值给成员变量conn=getConnection（）；

异常处理
驱动字符串拼写错误

链接字符串拼写错误

链接对象未赋值错误

Sql语句拼写错误

遍历结果集时字段名称不符或索引位置错误

Sql注入
PreparedStatement

 

Sql语句的执行条件通过变量传值方式注入到sql语句中产生一个新的sql语句

将Statement改换为PreparedStatement接口

将sql语句的查询条件赋值方式作调整：变为占位符？，通过对象的setXXX（）方法赋值

prepareIndex默认从1开始

 

Mybatis映射文件中常用节点
增（insert）删（delete）改（update）查（select）

辅助：if、trim、set、where

常用：

Sql：组织SQL语句的编写一般结合使用

Sql内容可以自定义，提前是最终拼接成完整的sql即可

Sql数量不限可多个

Bind:将复杂的Sql进行拆分

Foreach：

Collection：必须项，入参为List则为“list”，入参为数组则为“array”，入参为多参时则需要Map封装处理

Item集合中每个元素迭代时的别名

Index集合中每个元素迭代时的下标

Open表示该语句以什么开始

Close表示该语句以什么结束

Separator表示每次迭代之间以什么符号作为分割符