数据库的增删改查优化

数据库

• 存储数据，管理数据的仓库
• 数据库是相关数据的集合，一个数据库含有各种成分，包括数据表、记录、字段、索引等
  组成：
  数据库(Database)
  数据表(Table)
  记录(Record)
  字段(Field)
  索引(Index)
  查询(Query)
  过滤器(Filter)
  视图(view)

常见数据库：

• 关系型数据库， Oracle、MySQL、SQLServer、Access
• 非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

Mysql数据库

1. mysql服务端，它来处理具体数据维护，保存磁盘
2. mysql客户端，CRUD新增，修改，删除，查询

MySQL数据存放位置：

• 在MySQL的配置文件my.ini中会进行默认配置
  MySQL服务端：
• mysql-5.5.27-winx64.msi

mysql数据库的安装

• 端口默认使用port：3306
• 软件：NTFS
• 存储位置：
  Mysql数据库默认的编码是latin1等价于iso-8859-1，修改为utf-8
• 服务名称：mysql
• 密码：统一设置成root或简单易懂密码谨防忘记
• 注意：配置完，mysql开始执行，最后一步出错有时仍可以使用，使用SQLyog工具测试，如不行，再执行安装程序，选择remove，删除，然后重新安装。同时注意必须是管理员权限

数据库的检验

1 MySQL客户端：DOS窗口

• mysql -uroot -proot
• 语法：mysql.exe执行文件

代表参数

• -u 用户名，紧接着写的
• -p 密码，紧接着写的

2 MySQL客户端：DOS窗口

• 保存链接
• 链接
• 测试连接
  数据库的结构
• database
• 表
• 数据

  修删改查

  创建表

create table CGB2106(
列名1 varchar(100),
列名2 varchar(50)
);

添加列

alter table 表名 add 列名 NUMERIC(数据)

删除表

drop table 表名

查看所有表

show tables;

查看表结构

desc 表名

插入记录
向 表名 中插入2条记录

insert into tb_door values('数据','数据','数据');
insert into tb_door values('数据','数据','数据');

查询 表名 中的所有记录

SELECT * FROM 表名

修改 表名 中 列名 为 列名1 的记录

update 表名 set 数据=多少 where 列名1;

删除 表名 中 列名 为 列名2 的数据

Delete from 表名 where 列名=多少

将 表名 记录按照 属性 排序

Select * from 表名 order by 属性 desc;

查询 表名 中的总记录数

Select count(*) from 表名;

例

创建表 并在表中增加数据

CREATE TABLE 表名(
列名 数据类型(字符长度),

列名 数据类型(字符长度),

列名 数据类型(字符长度)
);

在表中添加数据

INSERT INTO  表名  VALUES('列名1数据','列名2数据','列名3数据');

INSERT INTO  表名  VALUES('列名1数据','列名2数据','列名3数据');

INSERT INTO  表名  VALUES('列名1数据','列名2数据','列名3数据')

字段约束

主键约束

AUTO_INCREMENT=1

create table 表名(
列名 字符 primary key auto_increment
);
insert into 表名 values(数据);
insert into 表名 values(数据);
insert into 表名 values(数据);
select * from 表名;

非空约束
非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

create table 表名(
列名 字符 primary key auto_increment,
列名 varchar(数据) not null
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,123;);//OK

唯一约束
唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空

create table 表名(
列名 字符 primary key auto_increment,
列名 varchar(50) unique--唯一约束
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重复会报错的
select * from test;

基础函数

lower:数据转小写

SELECT 列名,LOWER(列名) from 表名; 

upper:数据转大写

SELECT 列名,upper(列名) from 表名;  

length:数据的长度

SELECT 列名,length(列名) from 表名; 

substr:截取

SELECT 列名,SUBSTR(列名,1,3) FROM 表名;

concat:拼接数据

select 列名,concat(列名,'123') X from 表名 

replace:替换字符

select 列名,replace(列名,('1','2') X from 表名 
把1字符替换成2

ifnull:判断,如果comm是null,用0替换

select ifnull(列名,0) 列名 from 表名

uuid返回

SELECT UUID()

now

select now() -- 年与日 时分秒

select curdate() --年与日

select curtime() --时分秒

year & month & day
hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

转义字符

select 'ab'cd' -- 单引号是一个SQL语句的特殊字符

select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符

条件查询

distinct:使用distinct关键字，去除重复的记录行

SELECT 列名 FROM 表名;

SELECT DISTINCT 列名 FROM 表名;

where
查询数据

SELECT * FROM 表名 WHERE 列名=数据
字段名=字符名

SELECT *  FROM 表名 WHERE 列名 ='数据'
两列数据中重复的数据

SELECT *FROM 表名 WHERE 列名='数据' AND 列名='数据'  
满足两个条件 与 和 的关系

SELECT *FROM 表名 WHERE 列名='数据' OR 列名='数据'  
 满足其中一个关系 或者的关系

SELECT *FROM  表名 WHERE 列名 IN(数据,数据,数据)
和or的用法和结果相同

SELECT * FROM 列名 WHERE 1=1
永远成立 没有意义

like

SELECT *FROM 表名  WHERE 列名 LIKE'%字符%'
like%字符% 的作用模糊查询 通常用%作为占位符 包含这个字符
SELECT *FROM 表名  WHERE 列名 LIKE'字符%'
以这个字符开头
SELECT *FROM 表名  WHERE 列名 LIKE'%字符'
以这个字符结尾

null

SELECT *FROM 表名 WHERE 列名 IS null
查询数据是null的列表用is

SELECT *FROM 表名 WHERE 列名 IS NOT null
查询数据是null的列表用is not

between and

SELECT *FROM emp WHERE 列名>数据 AND 列名 <数据 ;
SELECT *FROM 表名 WHERE 列名  BETWEEN 数据 AND 数据;
在什么和什么之间 包含

limit

SELECT *FROM 表名 LIMIT 展示条数 
展示几条数据

order by

SELECT *FROM 表名 ORDER BY 列名
默认正序排序
SELECT *FROM 表名 ORDER BY 列名 DESC
倒序排序

案例
入职案例

SELECT *FROM 表名 WHERE YEAR(列名)<2017
#2017之前入职
SELECT *FROM 列名 WHERE YEAR(列名) BETWEEN 2015 AND 2017
#2015到2017之间入职的

改名 用 as 别名 from
**as+别名 **
as 加列名 可以改名 as也可以省略

SELECT 列名1 ,列名2 ,(列名1+IFNULL (列名2 ,0))*12 AS 新列名 FROM 表名; 替换列名
ifull(列名,0) 把为空的换成0

聚合 aggregation

聚合函数
max / min
查询最大值max

SELECT 列名 FROM 表名 ORDER BY 列名 DESC LIMIT 1;
最大值
SELECT MAX(列名) FROM 表名;
最大值 

查询最小值min

SELECT 列名 FROM 表名 ORDER BY 列名  LIMIT 1;
最小值
SELECT  MIN(列名) FROM 表名;
最小值

求和与平均数
sum / avg
求和sum

SELECT  SUM(列名) FROM 表名;
求和
聚合列和非聚合列不能同时使用

平均数avg

SELECT  AVG(列名) FROM 表名;
平均数

求总个数count

SELECT COUNT(列名) FROM 表名;
低效 不统计null
SELECT COUNT(*) FROM 表名;
都统计
SELECT COUNT(1) FROM 表名;
都统计 高效

案例:

SELECT COUNT(1) FROM 列名 WHERE 表名>=<数据;

分组 group
-group by 分组
-按非聚合列分组

SELECT 列名 ,AVG(列名)  FROM 表名 GROUP BY 列名;
统计 列名  平均值分组
  按照列名分组

当查询结果中出现聚合列和非聚合列时必须分组,否则会报错
having 分组基础上在过滤

SELECT 列名 ,AVG(列名)FROM 表名 GROUP BY 列名 HAVING AVG(列名)>数据;
having 类似where的过滤返回的结果 

SELECT 列名,MAX(列名) FROM  表名 GROUP BY 列名 HAVING MAX(列名)>数据 ORDER BY MAX(列名)
先查询表 再过滤 再分组
此处不能用where,会出现语法错误,where里不能出现聚合函数,会报错!!!

SELECT COUNT(1),YEAR(HIREDATE) FROM EMP WHERE YEAR(HIREDATE)>2017 GROUP BY YEAR(HIREDATE) 
没有出现聚合函数可以用,更高效

事务 transaction

• 数据库事务(Database Transaction)
  指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

事务4个特性ACID

• 事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
• 原子性:一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
• 一致性:在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致
• 持久性:事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

隔离级别

• 读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
• 读提交（read committed） Oracle默认的隔离级别
• 可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
• 串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

查询mysql的隔离级别
MySQL每一条SQL语句,都是独立的必须手动开启或结束事务

• 开启事务：start transaction;

START TRANSACTION;

• 结束事务：commit（提交事务）或rollback（回滚事务）

COMMIT;
ROLLBACK;

执行SQL语句之前，先执行strat transaction，会开启一个事务（事务的起点），可以去执行多条SQL语句，最后要结束事务

commit表示提交,事务中的多条SQL语句所做出的影响会持久化到数据库中

rollback，表示回滚，回滚到事务的起点，之前做的所有操作都会被撤消

事务处理

1. 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务

2. 事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行

3. 事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的

4. MySQL默认数据库的事务是开启的，执行SQL后自动提交。

5. MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交

提交 commit

表强化：约束 constraints

非空约束 not null
唯一约束 unique
**展示表结构：

DESC tb_user;

主键约束 primary key

FOREIGN KEY()

外键约束 foreign key
描述两个表的关系
子表添加记录时,id必须来自主表,删除主表记录时子表必须没被使用着
默认约束 default

CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
sex VARCHAR(10)
);
CREATE TABLE tb_user_address(
user_id INT PRIMARY KEY AUTO_INCREMENT,
address VARCHAR(20),
#本表主键(FOREIGN KEY)   本表关联表(REFERENCES)
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);

检查约束 check

索引 index

• 优点:提高了SQL的查询效率
• 缺点:数据重复,浪费空间
• 索引是一种排好序的快速查找的数据结构,可以帮助数据库高效的进行数据的查找
  一般索引以文件的形式存放在磁盘中,大多数索引都采用BTree树方式构建
  分类

• 单值索引:只包含一列,
• 唯一索引:索引列的值必须唯一，允许有空值；主键会自动创建唯一索引,值不能重复
• 复合索引:同时包含很多列

创建索引

创建索引:自键会自动创建索引
单值索引

CREATE INDEX 索引名 ON 表名(列名);
创建单值索引

唯一索引

alter table 表名 add unique(列名);
创建唯一索引 索引值必须唯一

复合索引

CREATE INDEX 索引名 ADD INDEX 表名(列名,列名);
创建复合索引   复合索引使用完会失效

查看索引
索引里面数值不能重复

SHOW INDEX FROM 表名;

使用索引

SELECT *FROM 表名 WHERE 列名='数据'
索引直接使用就好了

删除索引

ALTER TABLE 表名 DROP INDEX 索引名;
删除索引

创建完索引记得查看 !!!

SHOW INDEX FROM 表名;
查看索引

最左特性

• explain 解释 (关键字) 用来观察SQL的执行计划 查看是否使用索引

视图

创建视图

CREATE
    /*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]*/
    VIEW `表名`.`视图名` 
    AS
(SELECT * FROM ...);

使用视图

SELECT *FROM 视图名;

MySQL的优化

• 查询SQL尽量不要使用select * ，而是具体字段
• 避免在where子句中使用or来连接条件
• 使用varchar代替char
• 尽量使用数值替代字符串类型
• 查询尽量避免返回大量数据
• 使用explain分析你SQL执行计划
• 是否使用了索引及其扫描类型
• 创建name字段的索引
• 优化like语句
• 字符串怪现象
• 索引不宜太多，一般5个以内
• 索引不适合建在有大量重复数据的字段上
• where限定查询的数据
• 避免在where中对字段进行表达式操作
• 避免在where子句中使用!=或<>操作符
• 去重distinct过滤字段要少
• where中使用默认值代替null
• 批量插入性能提升
• 批量删除优化
• 伪删除设计
• 提高group by语句的效率
• 复合索引最左特性
• 排序字段创建索引
• 删除冗余和重复的索引
• 不要有超过5个以上的表连接
• inner join 、left join、right join，优先使用inner join
• in子查询的优化
• 尽量使用union all替代union

SQL注入

• 非法字符集#,#在SQL里是注释的意思,会引起SQL注入攻击

SQL注入的解决方案

 String sql="select *from user where name = ? and  password= ? ";//骨架,?占位符
    PreparedStatement ps = c.prepareStatement(sql);//设置参数
    赋值
    ps.setString(1,a);//给第一个?设置a的值
        ps.setString(2,b);//给第二个?设置b的值
ResultSet r = ps.executeQuery();//执行SQL

• Statement:不安全,抵效,SQL拼接复杂

• PreparedStatement:安全高效,不需要拼接直接使用,占位符可以用 ? 设置值