1.创建数据库,并指定字符集
create database 数据库名称 character set 字符集名;
2.修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
3.数据库类型:
1. int:整数类型
age int,
2. double:小数类型
score double(5,2)
3. date:日期,只包含年月日,yyyy-MM-dd
4. datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5. timestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值
6. varchar:字符串
name varchar(20):姓名最大20个字符
zhangsan 8个字符 张三 2个字符
4.基础查询
1.去除重复:
* distinct
SELECT distinct address FROM student3;
2.起别名:
* as:as也可以省略
3.查询姓名第二个字是化的人
SELECT * FROM student WHERE NAME LIKE "_化%";
4.排序
ASC:升序,默认的。DESC:降序。
如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
SELECT * FROM student3 ORDER BY math DESC, english DESC;
5.聚合函数:将一列数据作为一个整体,进行纵向的计算。
1. count:计算个数
1. 一般选择非空的列:主键
SELECT COUNT(*) AS '总人数' FROM student3;
2. max:计算最大值
SELECT MAX(age) AS '最大值' FROM student3;
3. min:计算最小值
SELECT MIN(age) AS '最大值' FROM student3;
4. sum:计算和
SELECT SUM(age) AS '最大值' FROM student3;
5. avg:计算平均值
SELECT AVG(age) AS '最大值' FROM student3;
5.分组查询:
1. 语法:group by 分组字段;
1. where 在分组之前进行限定,如果不满足条件,则不参与分组。
having在分组之后进行限定,如果不满足结果,则不会被查询出来。
2.按照性别分组。分别查询男、女同学的平均分,人数
要求:分数低于70分的人,不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) 人数
FROM student WHERE math > 70
GROUP BY sex HAVING 人数 > 2;
6.分页查询
limit 开始的索引(从0开始),每页查询的条数;
SELECT * FROM student LIMIT 0,3; -- 第1页
7.添加删除修改
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
delete from 表名 [where 条件]
update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
5.约束:对表中的数据进行限定,保证数据的正确性、有效性和完整性。
非空约束删除
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束删除
ALTER TABLE stu DROP INDEX phone_number;
主键约束删除
ALTER TABLE stu DROP PRIMARY KEY;
外键约束删除
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
1. 非空约束:not null,值不能为null
创建表时添加约束
NAME VARCHAR(20) NOT NULL -- name为非空
创建表完后,添加非空约束(也恶意在创建表时添加)
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
2. 唯一约束:unique,值不能重复
创建表时,添加唯一约束
phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
在创建表后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
3. 主键约束:primary key 主键就是表中记录的唯一标识,一张表只能有一个字段为主键,非空且唯一
id是主键
在创建表时,添加主键约束
id int primary key,-- 给id添加主键约束
删除主键
ALTER TABLE stu DROP PRIMARY KEY;
创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
自动增长
auto_increment
删除自动增长
ALTER TABLE stu MODIFY id INT;
添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
4.外键约束:让表于表产生关系,从而保证数据的正确性。
在创建表时,可以添加外键
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称)
REFERENCES 主表名称(主表列名称);
级联操作:要删把约束相关的数据一起删,定义外键的表执行就行了
1. 级联更新:ON UPDATE CASCADE
2. 级联删除:ON DELETE CASCADE
ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称)
REFERENCES 主表名称(主表列名称)
ON UPDATE CASCADE
ON DELETE CASCADE ;
6.数据库的设计
1.一对一
人和身份证
一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键
2.一对多(多对一):
部门和员工
在多的一方建立外键,指向一的一方的主键。
3.多对多
学生和课程
多对多关系实现需要借助第三张中间表。
中间表至少包含两个字段,
这两个字段作为第三张表的外键,分别指向两张表的主键
4.数据库设计的范式
第一范式(1NF):每一列都是不可分割的原子数据项
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码
(在1NF基础上消除非主属性对主码的部分函数依赖)
1.函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A
学号-->姓名
2. 完全函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。
(学号,课程名称) --> 分数
3. 部分函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。
(学号,课程名称) -- > 姓名
4. 传递函数依赖:A-->B, B -- >C .
如果通过A属性(属性组)的值,可以确定唯一B属性的值,
在通过B属性(属性组)的值可以确定唯一C属性的值,
则称 C 传递函数依赖于A
学号-->系名,系名-->系主任
5.码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,
则称这个属性(属性组)为该表的码
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
7.多表查询
(1. 内连接查询:
1. 隐式内连接:使用where条件消除无用数据,连接条件是:WHERE t1.`dept_id` = t2.`id`; 两张表连接到了一起,不符合条件的数据不在里边
查询所有员工信息(名称,性别)和对应的部门信息(名称)
SELECT
t1.name, -- 员工表的姓名
t1.gender,-- 员工表的性别
t2.name -- 部门表的名称
FROM
emp t1,
dept t2
WHERE
t1.`dept_id` = t2.`id`;
2. 显式内连接:(写不写inner都一样)where换成了:JOIN dept ON emp.`dept_id` = dept.`id`;
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
(2. 外链接查询:
1. 左外连接:select 字段列表 from 表1 left [outer] join 表2 on 条件;
查询的是左表所有数据以及其交集部分。
就是左边的表连接右边的表,条件是:join 表2 on 条件;,左边表所有数据都有
查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门
SELECT
t1.*,
t2.`name`
FROM
emp t1
LEFT JOIN
dept t2
ON
t1.`dept_id` = t2.`id`;
2. 右外连接:* 语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;
* 查询的是右表所有数据以及其交集部分。
SELECT
*
FROM
dept t2
RIGHT JOIN
emp t1
ON t1.`dept_id` = t2.`id`;
(3.子查询:
概念:查询中嵌套查询,称嵌套查询为子查询。
查询工资最高的员工信息
SELECT
*
FROM
emp
WHERE
emp.`salary` = (SELECT
MAX(salary)
FROM
emp);
子查询不同情况
1. 子查询的结果是单行单列的:
子查询可以作为条件,使用运算符去判断。 运算符: > >= < <= =
比如:查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2. 子查询的结果是多行单列的:
子查询可以作为条件,使用运算符in来判断
比如:查询'财务部'和'市场部'所有的员工信息
SELECT
*
FROM
emp
WHERE
dept_id IN (SELECT
id
FROM
dept
WHERE
NAME = '财务部' OR NAME = '市场部');
3. 子查询的结果是多行多列的:
子查询可以作为一张虚拟表参与查询
比如:查询员工入职日期是2011-11-11日之后的员工信息和部门信息
SELECT
*
FROM
dept t1 ,(SELECT * FROM emp
WHERE emp.`join_date` > '2011-11-11') t2
WHERE t1.id = t2.dept_id;
8.事务的基本介绍
(1. 概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
(2. 操作:
1. 开启事务: start transaction;
2. 回滚:rollback;
3. 提交:commit;
MySQL数据库中事务默认自动提交
Oracle 数据库默认是手动提交事务
修改事务的默认提交方式:
查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
修改默认提交方式: set @@autocommit = 0;
(3. 事务的四大特征:
1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
2. 持久性:当事务提交或回滚后,数据库会持久化的保存数据。
3. 隔离性:多个事务之间。相互独立。
4. 一致性:事务操作前后,数据总量不变
(4. 事务的隔离级别(了解)
概念:多个事务之间隔离的,相互独立的。
但是如果多个事务操作同一批数据,则会引发一些问题,
设置不同的隔离级别就可以解决这些问题。
存在问题:
1. 脏读:一个事务,读取到另一个事务中没有提交的数据
2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样。
3. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。
隔离级别:
read uncommitted:读未提交
产生的问题:脏读、不可重复读、幻读
read committed:读已提交 (Oracle)
产生的问题:不可重复读、幻读
repeatable read:可重复读 (MySQL默认)
产生的问题:幻读
serializable:串行化
可以解决所有的问题
隔离级别从小到大安全性越来越高,但是效率越来越低
9.JDBC
//1. 导入驱动jar包
//2.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//3.获取数据库连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
//4.定义sql语句
String sql = "update account set balance = 500 where id = 1";
//5.获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
//6.执行sql
int count = stmt.executeUpdate(sql);
//7.处理结果
System.out.println(count);
//8.释放资源
stmt.close();
conn.close();