数据库笔记

一、数据库常用操作

建库：create database 库名 default character set utf8;

删库：drop database 库名;

查看所有数据库：show databases;

使用数据库：use 库名;

创建表：create table 表名(字段 字段类型(字段长度))

修改表：(增)alter table 表名 add column 字段 字段类型(字段长度)

(删)alter table 表名 drop 字段

删除表：drop table 表名;

查看表：show tables;

查看表结构：desc 表名;

表记录的常用操作

插入记录：insert into 表名 values(数据,数据,数据);

查询记录：select * from 表名;

修改记录：update 表名 set 字段1=值 where 字段2=值;(2改成1)

删除记录：delete from 表名 where 字段=值;（删除这一行的内容）

二、数据类型

命名规则

• 字段名以字母开头；

• 长度不能超过30个字符；

• 不能使用SQL的保留字,如where,order,group；

• 只能使用如下字符a~z,A~Z,0~9,$等；

• Oracle习惯全大写:USER_NAME，mysql习惯全小写:user_name；

• 多个单词用下划线隔开,而非java语言的驼峰规则。

字符

• 字符长度固定时用char；

• 字符长度不确定时用varchar。

数字

• tinyint,int整数类型；

• float,double小数类型；

• numeric(5,2) decimal(5,2)-表示小数,表示总共5位,其中可以有两位小数；

• numeric和decimal表示精确的整数数字。

日期

• data包含年月日；

• time时分秒；

• datetime包含年月日和时分秒；

• timestamp时间戳,不是日期,而是从1970年到指定日期的毫秒数。

图片

• 只存储其访问路径,文件则存放在磁盘上。

三、字段约束

主键约束

主键约束：如果为一个列添加了主键约束,那么这个列就是主键,主键的特点是唯一且不能为空。通常情况下,每张表都会有主键。

(创建表 加在字符长度后面,create table 表名(字段 字段类型(字段长度) primary key))

主键自增策略

数据库会在表中保存一个auto_increment变量值,初始值为1,当需要id值,不需要我们指定值,且每次用完auto_increment值,都会自增1。

(创建表 加在字符长度后面,create table 表名(字段 字段类型(字段长度) primary key auto_increment))

非空约束

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

(创建表 加在字符长度后面,create table 表名(字段 字段类型(字段长度) not null))

唯一约束

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

(创建表 加在字符长度后面,create table 表名(字段 字段类型(字段长度) unique))

四、基础函数

lower(转小写)：select lower(‘XYZ’) from user;

upper(转大写)：select upper(‘abc’) from user;

length(数据长度)：select length(字段名称) from user;

substr(截取)：select substr(a,b,c) from user;

(a是字段名称，b是从第几个开始，c是要截取的长度)

concat(拼接)：select concat(a,b,c) from user;

(a是字段名称，b和c是要拼接的数据)

replace(替换)：select replace(a,b,c) from user;

(a是字段名称，b是要把谁替换成c)

ifnull(为null时替换)：select ifnull(a,b) from user;

(a是字段名称，b是要把null替换成b)

1、小数处理

round：round(a)四舍五入，把a的值取整

            round（a,b）四舍五入,把a的值保留b位小数

ceil：直接向上取整 

floor：直接向下取整

2、条件查询

distinct(去重)：select distinct 字段名 from 表名；

where(满足条件)：select 字段名 from 表名 where 字段名=字段值

• where后不能用别名和聚合函数

• 如果查询条件有多个,使用 and并且  or或者 连接

• 向user表中，录入数据,测试where也可以对增删改设置条件

修改数据语句：update 表名 set 字段名=字段值 where 字段名=字段值

删除数据：delete from 表名 where 字段名=字段值

like(模糊查询)：%是通配符(0~n个字符),_是通配符(1个字符)

select 字段名 from 表名 where 字段名 like ‘t%’//以t开头

null(对null特殊处理)：select 字段名 from 表名 where 字段名 is null;

between and(区间范围，两边都包含)：

select 字段名 from 表名 where 字段名(指定范围) between X and X;

limit(分页)：select * from 表名 limit 2 //取前两条

select * from 表名 limit 0,2 //从第一条开始,取两条

select * from 表名 limit 1,3 //从第二条开始,取三条

order by(字典顺序排序,默认升序ASC  结尾加DESC为降序)：

select * from 表名 order by 字段名/字段名 desc

五、聚合函数

max(最大值)  min(最小值)  sum(总和)  avg(平均值)

语法：select max(字段名) from 表名;

count(个数):select count(*/1) from 表名;

where不可以直接连接聚合函数

--分组

group by(当查询结果中，出现了混合列的时候，必须分组！)

语法：select 非聚合列，聚合列 from 表名 group by 非聚合列(分组依据)

where子句 = 指定行所对应的条件

having子句 = 指定组所对应的条件

group by的作用是限定分组条件，而having则是对group by中分出来的组进行条件筛选

即用having的时候一定要有group by。

六、事务

用来保证多条SQL要么全成功要么全失败。

1.概述

四大特征：ACID

1.原则性：多条SQL是一个密不可分的整体。

2.一致性：分布式系统里，数据的一致性。

3.隔离性：数据库支持高并发，使用了锁的机制保证了数据的安全。

4.持久性：是指对数据的增删改是持久生效的。

隔离级别

读未提交（read uncommitted）：读未提交，安全性最差，但是效率高。

读已提取（read committed）：读已提交，安全性较好，但是效率较差，也是Oracle的默认级别。

可重复读（repeatable read）：

可重复读，安全性适中，但是效率一般，也是MySQL的默认级别。

串行化（serializable）：串行化，安全性最高，但是效率低。

2.测试

1.开启事务：start transaction

2.执行SQL：增删改的SQL

3.结束事务：commit提交

七、字段约束

1. 默认约束：给字段添加默认值，使用default设置默认值。
2. 检查约束：使用check检查字段值是否合法。
3. 外键约束：

• 约束情况1：子表中的主键的值必须取自主表，

• 约束情况2：主表的记录想要删除，必须保证子表没用过。

创建外键：foreign key(当前表的主键名) references 对方表名(对方表主键)

八、多表联查

查询时，可能一张表无法满足查询需求，就需要联合多张表进行查询。

方式1：笛卡尔积

#多表联查方式1：笛卡尔积

#练习1：查询课程表和教师表的所有数据

SELECT * FROM courses,teachers;

SELECT * FROM courses,teachers

       #表名.字段名   表名.字段名

WHERE  courses.tno =teachers.tno

#添加查询条件，表名两张表的关系

方式2：连接查询 - 推荐！！！高效

语法：多表之间用join连接，用on来描述两张表的关系，用where表示更多的条件

连接查询：内连接，外连接

内连接：inner join取交集，可以简写成join

左连接：left join取左表的所有和右表满足的

右连接：right join取右表的所有和左表满足的

方式3：子查询

也叫嵌套查询，把上次的查询结果当做这次的查询条件在用

九、索引

1、概述：

作用就是快速的查找数据。

优点：查的快。

缺点：本身索引是一张表需要空间存放，表越大查的越慢。

使用：如果经常按照指定字段来查询，这个字段添加索引。

分类：

1. 单值索引：给字段添加索引，并且这个索引只包含着一个字段。
2. 复合索引：给字段添加索引，并且这个索引只包含着多个字段。
3. 唯一索引：给字段添加索引，并且这个索引只包含着一个字段，但是字段的值不能相同。

2、创建普通索引

1）查询索引：表里的主键数据库已经自己创建好了索引。

SHOW INDEX FROM 表名

2）创建索引：不是所有字段都适合加索引，可以给经常查的字段加索引。

create index 索引名 on 表名（字段名）

复合索引添加多个字段名即可，使用复合索引必须包含最左边的元素（字段名）

3）使用索引：

select * from 表名 where 字段名=‘’

4）查看SQL的执行计划 / 性能，观察查询结果里的key的值

explain
select * from 表名 where 字段名=''

5）删除索引：

alter table 表名 drop index 索引名

十、扩展视图

1.概述

缓存了上一次的查询结果,当做一张表来用

Select * from 视图名

步骤:1.创建视图  2.使用视图

2.测试

#视图:优点是避免了次次写复杂的SQL,屏蔽了业务表的复杂性,被所有用户共享

      #缺点:数据都是重复的,SQL无法被优化

#1.创建视图

#语法:create view 视图名 as  SQL语句

#练习:查询名字里包含a的员工信息

CREATE VIEW enama_view AS

  SELECT * FROM emp WHERE ename LIKE '%a%'

#2.查视图,当做表来查

SELECT * FROM enama_view

十一、扩展SQL优化

1.测试

1)用字段名称代替*。

2)where里，能用=就别用！=，能用and就别用or，能用=就别用in。

3)字段的类型，能用varchar就别用char，字段的值能用数字就别用字符串。

4)索引，单表的索引最多5个。

5)模糊查询，最好确定以啥开头，高效而且索引会生效

6)字符串，SQL对于数字不严格，where name=123，应该把123加引号，

因为name是把人设为varchar类型。

#1.模糊查询的索引失效问题
#创建索引:
CREATE INDEX sname_index ON students(sname)
#查看索引:
SHOW INDEX FROM students
#使用索引:
EXPLAIN SELECT * FROM students WHERE sname LIKE '李%';#索引生效,高效
EXPLAIN SELECT * FROM students WHERE sname LIKE '%李%';#索引失效
EXPLAIN SELECT * FROM students WHERE sname LIKE '%李';#索引失效
#2.字符串的特殊现象
SHOW INDEX FROM dept
EXPLAIN SELECT * FROM dept WHERE dname=123#索引失效
EXPLAIN SELECT * FROM dept WHERE dname='123'#索引生效
#3.批量插入:
#向dept表插入两条数据
INSERT INTO dept VALUES(NULL,'ios','深圳')
#只给指定字段设置值
INSERT INTO dept(dname,loc) VALUES('ios2','深圳2')
INSERT INTO dept(dname) VALUES('ios3')
INSERT INTO dept(dname) VALUES('ios4')
#一次性插入多条数据,优化了事务管理,只需要开启和关闭一次事务
INSERT INTO dept(dname) VALUES('ios13'),('ios14')

2.简单了解表设计的三范式原则

作用就是用来：优化表的结构，减少数据的冗余。

第一范式1NF：保证字段的值是最小单位不可再被分割。

第二范式2NF：必须先保证遵循了第一范式，要求每张表都要有主键/主属性/主字段，

非主属性的值必须围绕主属性展开。

第三范式3NF：必须先保证遵循了第二范式，减少字段间的依赖传递。

十二、JDBC

1.概述

专门用java程序操作数据库的一个技术，是java程序连接数据库的一套标准。

本质上就是一堆的API，由于操作步骤复杂，将来会被框架封装。

2.开发步骤

1)创建工程

2)导入jar包(一堆工具类的压缩包)

3)在java中，连接数据库：用户名root 密码2323 端口号3306

3.创建工程

File - New - Project - 选择java - next - 输入工程名称 - Finish

4.导入jar包，并编译

把jar包复制到工程里。(mariadb10.5以下用5.X的jar包版本，10.5为8.0版本)

十三、SQL攻击/注入

1.概述

在SQL语句拼接参数的过程中，出现了特殊的符号#，改变了SQL的语义。

案例中，当用户输入固定的用户名：账号’#时，不必输入密码竟然可以登录！！！

2.解决方案

使用新的传输器PreparedStatement代替现在的传输器Statement，

解决了SQL攻击的问题，把特殊符号#当做一个普通的字符在用，不会当做注释来解析。

3.新传输器的步骤(需要解决异常try catch包裹)

1)注册驱动--参数是类的全路径。

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

2)获取连接(用户名 密码 端口号)

//String url = "协议://IP地址:端口号/数据库的名字";
  String url = "jdbc:mysql://localhost:3306/lsy1999?characterEncoding=utf8";
  Connection c = DriverManager.getConnection(url, "root", "2323");

3)执行SQL -- 对表进行的操作

 String sql="sql语句";

4)获取传输器

PreparedStatement p = c.prepareStatement(sql);

5)返回结果集来保存(此处为增删，还可以为查询executeQuery)

p.executeUpdate();

6)关闭资源(也需要进行处理异常)

变量.close;