Java学习笔记（十二）：SQL

写在前面：

基本信息

mysql登录	mysql -uroot -p密码
引号	单双都可以
date格式	yyyy-MM-dd

	增	删	改	查
库	create	delete	alter	show  select  正在使用的库
表	create     (like 复制)	drop	alter	show desc  表结构
表列	alter ...  add	alter ... drop	alter ... change  列名 alter ... modify  只修改类型	-----
内容	insert into	delete from	update	select

事务

start transaction

rollback

commit

表连接

where / join

left join

distinct 列

ifnull (列,0)

列1+列2

列1=列1+10

in (xx,xx,xx)

like '_xxx%'

limit 0,3

约束

	添加	删除
primary key	modify	drop primary key
unique （可以有多个null）	modify	drop index 列
not null	modify	modify
auto_increment	modify	modify
foreign key	add foreign key (列) references 主表(主表列)	drop foreign key 外键名

 

 

---

1. 一些MySQL基础操作

下载安装（mac）：https://blog.csdn.net/poppy_rain/article/details/87643248

终端登录：

mysql --host=ip --user=root --password=密码

mysql -uroot -p密码

忘记root密码：

终端执行 net stop mysql 停服务

mysql --skip-grant-tables 无验证登录

再重新开个终端窗口执行 mysql 直接进入

改密码，关闭窗口，停止服务

备份还原：

备份：mysqldump -u用户名 -p密码 数据库名 > 保存路径

还原：新建同名数据库；使用数据库；source 文件路径

SQL一些语法：

不区分大小写

注释：-- （杠杠加空格）、# （井号，mysql特有）、/* 多行注释 */

单引号、双引号都可以

2. SQL中的数据类型

int

double(5,2)  -- 整数部分5位，小数部分2位

date   -- 格式yyyy-MM-dd

datetime    -- 格式yyyy-MM-dd HH:mm:ss

timestamp    -- 不赋值时，默认当前系统时间.                            ???

varchar(20)   -- 字符串；举例：zhang 5个字符，张 1个字符

 

3. DDL 数据库和表的操作

• 库（假设数据库db1）

创建：create database [if not exists] db1 [character set gbk] ;  -- 最后可以指定字符集

查看：show databases ;

show create database db1 ;  -- 查看创建数据库的语句，包含字符集

select database() ;  --查询正在使用的数据库

修改：alter database db1 character set utf8 ;   -- 修改数据库字符集 （注意utf8，中间没有横杠）

删除：delete database [if exists] db1 ;

使用：use db1 ;

 

• 表（假设表t1）

创建：create table t1 (

列名1  类型1 [约束],   --添加约束

列名2  类型2 [default 'xx']    -- 给默认值

) ;

复制：create table t2 like t1 ;   -- 不含数据

查询：show tables ;

desc t1 ;  -- 查询表结构

删除：drop table [if exists] t1 ;

修改：alter table t1 rename to t11 ;   -- 重命名

alter table t1 character set utf8 ;   -- 修改字符集

alter table t1 add 列名3 类型3 ;   -- 添加列

alter table t1 drop 列名1 ;   -- 删除列

alter table t1 change 列名1 新列名11 新类型11 ;   -- 修改列

alter table t1 modify 列名1 新类型11 ;   -- 修改列的类型

4. DML 数据的增删改

添加：insert into t1(列名1, 列名2......) values(值1, 值2......)   -- 列名与值一一对应，列名为空时(into t1 values)表示给所有列赋值

删除：delete from t1 [where 条件]

修改：update t1 set 列=值, 列=值 [where 条件]    -- 可以有：列=列+10；列=null

5. DQL 数据查询

• 基本语法：

select  ...  from ...  where ...  group by ...  having ...  order by ...  limit  ... 

• 每部分条件

select ：

去重：distinct 列

四则运算：列1+列2

ifnull：ifnull (列, 0)   -- 如果列值为空，则替换为0

where：

运算符：<>   =   !=   >   <=   ........

between  xx  and  xx

is null        is not null

like '_xx%'   -- 占位符：_ 一个字符，% 多个字符

in (xx,xx,xx)

where处的列名可以用select处起的别名（having也可以）

不能有聚合函数

聚合函数：

将列作为整体，进行纵向运算，都排除了null值

count(列名)   -- 个数，统计所有列：count(*) (不推荐)、count(非空列)（推荐用主键）、count(ifnull(列,0))

sum(列名)    

max(列名)    

min(列名)    

avg(列名)    

group by :

group by 分组字段    -- 将这个字段分组作为整体

select 后只能跟 分组字段和聚合函数

分组之前的条件限定用where，不满足条件的不参与分组

分组之后的条件限定用having，不满足条件的分组不被查处，having后可以跟聚合函数

order by：

order by 列名 排序方式, 列名 排序方式 ......

排序方式：desc 倒序，asc 正序（默认）

分页查询：（不同数据库用法不同，下面用法为mysql）

limit  0,3       -- 从0索引开始，查3条

• 多表查询（消除笛卡尔积的无用部分）

查询时需要分析：查询几个表、查询条件是什么

内连接：

隐式：where

显示：[inner] join     -- 结果与where相同，查询两表交集部分

外连接：

左外连接：left [outer] join    -- 查询左表，及两表交集部分

右外连接：right [outer] join

子查询（嵌套）：

查询结果单行单列：可作为结果，用运算符判断比较

查询结果单行多列：可作为结果，用in判断

查询结果多行多列：可作为一张虚拟表，参与查询

 

6. DCL 管理用户授权

管理用户（mysql库的user表）：

查询：select * from user;  -- 通配符%表示可以在任意主机使用用户名登陆数据库

创建用户：create user '用户名'@'主机名'  identified by '密码'

删除：drop user  '用户名'@'主机名'

修改密码：update user set password=password('密码') where user='用户名'

                   set password for  '用户名'@'主机名' =password('密码')

权限管理：

查询权限：show grants for '用户名'@'主机名'

授予：grants  权限列表  on 数据库.表  to '用户名'@'主机名'   -- 所有数据库的所有表：*.* 所有权限：all

撤销：revoke 权限列表  on 数据库.表  to '用户名'@'主机名'

7. 约束

主键：primary key   非空且唯一

添加：modify

删除：drop primary key

非空：not null

添加：modify

删除：modify

唯一：unique   值唯一，但可以有多个null

添加：modify

删除：drop index 列名

自增：auto_increment   数值类型列

添加：modify

删除：modify

赋值时可以用null，会自动转为自增；自增只跟上一个值有关

外键：foreige key   可以为null，但不可以为不存在的值

设置外键：[constraint 外键名称] foreign key (外键列名) references 主表名(主表列名)

添加：add [constraint 外键名称] foreign key (外键列名) references 主表名(主表列名)

            on update cascade    -- 设置级联更新

            on dalete caascade    -- 设置级联删除

删除：drop foreign key 外键名

 

8. 事务

多个步骤被事务管理，则操作不是同时失败，就是同时成功

开启了事务，没有提交，关闭窗口后，会自动回滚

修改默认提交方式：set @@autocommit=0   -- 1自动提交（mysql默认），0手动提交（oracle默认）

 

开启事务： start transaction

失败，回滚事务：rollback

成功，提交事务：commit

 

四大特征：

原子性（不可分割）

持久性（提交或回滚后的数据会永久保存）

隔离性（多个事务之间相互独立---隔离级别）

一致性（总量不变）

 

（了解）

隔离级别：多个事务（A和B），操作同一批数据，会出现一些问题

脏读：A读到B中没有提交的数据

虚读：A中，两次读到的数据不一样

幻读：A操作数据库中的所有数据，B进行了添加，则A查不到自己的修改

 

读未提交：read uncommited   产生问题：脏读、虚读、幻读

读已提交：read commited   产生问题：虚读、幻读（oracle默认）

可重复度：repeatable read   产生问题：幻读（mysql默认）

串行化（锁）：serializable   A事务在读取数据，不允许B事务操作

 

设置隔离级别：set global transaction isolation level 级别

查看隔离级别：select @@tx_isolation 

 

举例：设置隔离级别为读已提交，开启两个事务AB

1. A开启事务；3. A执行操作；5. A提交事务

2. B开启事务；4. B读取数据；6. B再次读取数据