数据库DataBase(DB)

最新推荐文章于 2024-05-15 16:51:07 发布

woshiwu6666

最新推荐文章于 2024-05-15 16:51:07 发布

阅读量3.3k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/woshiwu6666/article/details/119109264

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

特点

持久化存储数据
方便存储和管理
使用了统一的方式-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;

约束

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

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：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事务默认自动提交

开启事务： start transaction;
回滚：rollback;
提交：commit;

修改事务的默认提交方式

set @@autocommit = 0;

事务四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间。相互独立。
一致性：事务操作前后，数据总量不变
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);
        }
    }

}

woshiwu6666

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据库DataBase(DB)

特点持久化存储数据方便存储和管理使用了统一的方式-SQLsql，结构化查询语言数据库是文件夹表是文件表中的数据就是数据通用语法SQL分类操作数据库:crudC:创建R:查询U：修改D：删除查询所有数据库的名称:show databases;创建数据库，判断不存在，再创建：create database if not exists 数据库名称;修改删除和使用改数据库的字符集alter database 数据库名称 character set 字符集名称;
复制链接

扫一扫