数据库DataBase(DB)

特点

• 持久化存储数据
• 方便存储和管理
• 使用了统一的方式-SQL
  sql，结构化查询语言
• 数据库是文件夹
• 表是文件
• 表中的数据就是数据

通用语法

SQL分类

操作数据库:crud

• C:创建
• R:查询
• U：修改
• D：删除

查询所有数据库的名称:

show databases;

创建数据库，判断不存在，再创建：

create database if not exists 数据库名称;

修改删除和使用

改数据库的字符集

alter database 数据库名称 character set 字符集名称;

删除数据库

drop database if exists 数据库名称;

查询当前正在使用的数据库名称

select database();

使用数据库

use 数据库名称;

数据库表的CRUD操作

• 创建，最后一列不用加“，”

create table 表名(
					列名1 数据类型1,
					列名2 数据类型2,
					....
					列名n 数据类型n
				);

create table student(
					id int,
					name varchar(32),
					age int ,
					score double(4,1),
					birthday date,
					insert_time timestamp
				);

数据类型

• int
• double，score double(5,2),数字有5位，保留到小数点后两位
• date，只包含年月日yyyy-mm-dd
• datetime，包含年月日时分秒
• timestamp，默认用添加时的，系统时间
• varchar字符串类型，name varchar(20),最大20个字符

复制表

create table 表名 like 被复制的表名;

删除表

 drop table  if exists 表名 ;

表修改

• 修改表名
• 修改表的字符集
• 添加一列
• 修改列名称 类型
• 删除列

修改表名

alter table 表名 rename to 新的表名;

修改表的字符集

alter table 表名 character set 字符集名称;

添加一列

alter table 表名 add 列名 数据类型;

修改列名称 类型

alter table 表名 change 列名 新列别 新数据类型;

删除列

alter table 表名 drop 列名;

增删改表中的数据

• 添加数据
• 删除数据
• 修改数据

添加数据

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
INSERT INTO stu VALUES(2,'aaa',12,33.2,null,null)

删除数据

delete from 表名 [where 条件]
delete from 表名 where id=1;

删除表格中的所有数据

TRUNCATE TABLE 表名; 

修改数据

update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
UPDATE stu SET age=117,score=100 WHERE id=3;

查询表中的数据

select * from 表名;

语法

• select 字段列表
• from 表名列表
• where 条件列表
• group by 分组字段
• having 分组之后的条件
• order by 排序
• limit 分页限定

基础查询

• 多个字段的查询

select 字段名1，字段名2... from 表名；

• 查询时去除重复

select distinct adress student;

• 计算列

select Name,math,english,math+ifnull(english,0) From Student;

• 起别名
  用空格隔开，数学，英语，总分就成了查询别名

select Name,math 数学,english 英语,math+ifnull(english,0) 总分 From Student;

条件查询

• 运算符
  查询年龄大于20岁

SELECT * FROM student WHERE age > 20;

查询年龄等于20岁

SELECT * FROM student WHERE age >= 20;

查询年龄等于20岁

SELECT * FROM student WHERE age = 20;

查询年龄不等于20岁

SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;

查询年龄大于等于20 小于等于30

SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

查询年龄22岁，18岁，25岁的信息

SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);

查询英语成绩为null

SELECT * FROM student WHERE english = NULL;
SELECT * FROM student WHERE english IS NULL;

查询英语成绩不为null

SELECT * FROM student WHERE english  IS NOT NULL;

模糊查询

查询姓马的有哪些？ like

SELECT * FROM student WHERE NAME LIKE '马%';

查询姓名第二个字是化的人

SELECT * FROM student WHERE NAME LIKE "_化%";

查询姓名是3个字的人

SELECT * FROM student WHERE NAME LIKE '___';

查询姓名中包含德的人

SELECT * FROM student WHERE NAME LIKE '%德%';

复杂查询

• 排序查询

排序方式：

• ASC：升序，默认的
• DESC：降序
• 一样的时候才去执行排序方式2

order by 排序字段1 排序方式1 ，排序字段2 排序方式2...		

• 聚合函数
  将一列数据作为一个整体，进行纵向的计算。

• count：计算个数

select count(ifnull(name,0)) FROM student;

• max：计算最大值

select max(math) FROM student;

• min：计算最小值
• sum：计算和

select sum(math) FROM student;

• avg：计算平均值

select avg(math) FROM student;

• 分组查询

分别查询男、女同学的平均分

select sex，AVG(math),count(id) from student group by sex；

分数低于70分不参与排序

select sex，AVG(math),count(id) from student where>70 group by sex；

分数低于70分不参与排序,分完组后一组人数要大于2个人,where在分组前限定，having在分组后限定，where后不能跟聚合函数，having后可跟聚合函数

select sex，AVG(math),count(id) from student where>70 group by sex Having COUNT(id)>2；

• 分页查询

limit 开始的索引,每页查询的条数;

两个数字分别表示，展示索引为1的数据，每页显示3条记录

select*from student limit 0,3;

约束

• 对表中的数据进行限定，保证数据的正确性、有效性和完整性。

1. 主键约束：primary key
2. 非空约束：not null
3. 唯一约束：unique
4. 外键约束：foreign key

非空约束

• 创建表时添加约束

CREATE TABLE stu(
				id INT,
				NAME VARCHAR(20) NOT NULL -- name为非空

删除约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

添加表后添加约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

唯一约束

CREATE TABLE stu(
				id INT,
				phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
			
			);

删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

在创建表后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

主键约束

• 非空且唯一
• 一张表只能有一个字段为主键

create table stu(
				id int primary key,-- 给id添加主键约束
				name varchar(20)
			);

删除主键

ALTER TABLE stu DROP PRIMARY KEY;

建完表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

自动增长

• id
• 在创建表时，添加主键约束，并且完成主键自增长

create table stu(
				id int primary key auto_increment,-- 给id添加主键约束

外键约束
在创建表时，可以添加外键

create table 表名(
					....
					外键列
					constraint 外键名称(起什么名称都可以) foreign key (外键列名称) references 主表名称(主表列名称)
				);

 - 先创建部门表
 - 防止把外键删掉

员工表

create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int, -- 外键对应主表的主键
	-- 创建外键约束
	constraint emp_depid_fk foreign key (dep_id) references department(id)
)

部门表

create table department(
	id int primary key auto_increment,
	dep_name varchar(20),
	dep_location varchar(20)
);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

创建表之后添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

添加级联操作

FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;

多表操作

• 数据库的设计
  多表之间的关系

• 一对一，人和身份证
  

• 一对多的关系，一个部门有多个员工
  

• 多对多的关系，学生和课程
  利用中间表，1个人可以选不同的课，sid是1，cid可以是1，sid是2，cid可以是2
  
  案例

• 旅游线路分类

• 旅游线路

• 用户
  
  创建旅游线路分类表 tab_category

CREATE TABLE tab_category (
				cid INT PRIMARY KEY AUTO_INCREMENT,
				cname VARCHAR(100) NOT NULL UNIQUE
			);

创建旅游线路表 tab_route

CREATE TABLE tab_route(
				rid INT PRIMARY KEY AUTO_INCREMENT,
				rname VARCHAR(100) NOT NULL UNIQUE,
				price DOUBLE,
				rdate DATE,
				cid INT,
				FOREIGN KEY (cid) REFERENCES tab_category(cid)
			);

用户表

CREATE TABLE tab_user (
				uid INT PRIMARY KEY AUTO_INCREMENT,
				username VARCHAR(100) UNIQUE NOT NULL,
				PASSWORD VARCHAR(30) NOT NULL,
				NAME VARCHAR(100),
				birthday DATE,
				sex CHAR(1) DEFAULT '男',
				telephone VARCHAR(11),
				email VARCHAR(100)
			);

创建收藏表 (中间表)

CREATE TABLE tab_favorite (
				rid INT, -- 线路id
				DATE DATETIME,
				uid INT, -- 用户id
				-- 创建复合主键
				//他们两个不能重复
				//如两个rid=1，uid=1，这种情况是不可取的
				PRIMARY KEY(rid,uid), -- 联合主键
				FOREIGN KEY (rid) REFERENCES tab_route(rid),
				FOREIGN KEY(uid) REFERENCES tab_user(uid)
			);

三大范式

• 第一范式:每一列都是不可分割的原子数据项

• 第二范式：消除部分依赖

• 第三范式：消除传递依赖
  概念

• 函数依赖， A–>B,例如：如学号–>姓名

• 完全函数依赖， 例如：（学号，课程名称） --> 分数

• 部分函数依赖,例如：（学号，课程名称） – > 姓名

• 传递函数依赖,例如：学号–>系名，系名–>系主任

• 码,例如：该表中码为：（学号，课程名称）

• 主属性：码属性组中的所有属性

• 非主属性：除过码属性组的属性

• 非主属性完全依赖于主属性

数据库的备份和还原

备份

mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

多表查询

• 笛卡尔积，由两个集合取这两个集合的所有组成情况
• 内连接查询， 消除无用的数据
• 外连接查询
• 子查询

使用where连接消除无用信息，隐式内连接查询

SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;

取别名

SELECT 
					t1.name, -- 员工表的姓名
					t1.gender,-- 员工表的性别
					t2.name -- 部门表的名称
FROM
//替换两个表格名字
					emp t1,
					dept t2
WHERE 
					t1.dept_id = t2.id;

显示内连接查询，查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称

 * SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;	

外连接

• 左外连接，左边表的所有数据加上右表交集的数据

SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

• 右外连接,原来右边表的所有数据加上右表交集的数据

SELECT 	* FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;

子查询

• 嵌套查询
  查询工资最高的员工信息
  1 查询最高的工资是多少，得到结果是9000

SELECT MAX(salary) FROM emp;

2查询员工信息，并且工资等于9000的

SELECT * FROM emp WHERE emp.`salary` = 9000;

• 将上述两步操作，合并成一步

SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

子查询的不同情况

• 子查询的结果是单行单列的
  查询员工工资小于平均工资的人

SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

• 子查询的结果是多行单列的
  查询’财务部’和’市场部’所有的员工信息

SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

使用子查询的写法

//in表示dept的id=3或者等于2
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

• 子查询的结果是多行多列的
  查询员工入职日期是2011-11-11日之后的员工信息和部门信息

SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

事务

• 同时成功同时失败
• 开启事务
• 回滚
• 提交

使用事务管理，Mysql事务默认自动提交

1. 开启事务： start transaction;
2. 回滚：rollback;
3. 提交：commit;

修改事务的默认提交方式

set @@autocommit = 0;

事务四大特征

1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
3. 隔离性：多个事务之间。相互独立。
4. 一致性：事务操作前后，数据总量不变
   DCL

JDBC

• 用JAVA操作数据库
• 定义了一套规则接口
• 我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类
  

public class JdbcDemo1 {
    public static void main(String[] args) throws Exception {

        //1. 导入驱动jar包
        //2.注册驱动
        // Class.forName("com.mysql.jdbc.Driver");
        //3.获取数据库连接对象
//        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
        Connection conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");
        //4.定义sql语句
//        String sql = "update account set balance = 2000 where id = 1";
        String sql = "update account set balance = 2000";
        //5.获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();
        //6.执行sql
        int count = stmt.executeUpdate(sql);
        //7.处理结果
        System.out.println(count);
        //8.释放资源
        stmt.close();
        conn.close();

    }
}

数据库连接池

c3p0代码

public class C3P0Demo2 {

    public static void main(String[] args) throws SQLException {
       /* //1. 获取DataSource，使用默认配置
        DataSource ds  = new ComboPooledDataSource();

        //2.获取连接

        for (int i = 1; i <= 11 ; i++) {
            Connection conn = ds.getConnection();
            System.out.println(i+":"+conn);

            if(i == 5){
                conn.close();//归还连接到连接池中
            }
        }*/

        //testNamedConfig();

    }


    public static void testNamedConfig() throws SQLException {
        // 1.1 获取DataSource，使用指定名称配置
        DataSource ds  = new ComboPooledDataSource("otherc3p0");
        //2.获取连接
        for (int i = 1; i <= 10 ; i++) {
            Connection conn = ds.getConnection();
            System.out.println(i+":"+conn);
        }
    }

}