Mysql 数据库基本操作

秃头的赌徒

于 2023-05-22 21:54:10 发布

阅读量142

点赞数 1

分类专栏：数据结构文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/weixin_70564024/article/details/130691951

版权

数据结构专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、基本操作

1.1别名查询

select id,name,chinese+math+english 总分 from 表名；

1.2 去重查询：distinct

select distinct math from 表名

1.3 排序：order by

select ... from  表名 where... order by 列名 [asc|desc];

--查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示
select name , math , english, chinese from table_name order by math desc,english,chinese;

注：ASC 为升序（从小到大）；DESC为降序(从大到小）。默认为ASC。

1.4 条件查询：where

逻辑运算符：

注：

1. where 条件可以使用表达式，但不能使用别名。

2. and的优先级高于OR，在同时使用时，需要使用小括()包裹优先执行的部分

部分使用案例：

1. IN

--查询数学成绩是58 或者 59 或者 98 或者 99 的同学及数学成绩
select name,math from table_name where math in(58,59,98,99);

--使用 OR 也可以实现
select name , math from table_name where math=58 or math=59 ro math=98 or math=99;

2. 模糊查询：like

-- % 匹配任意多个( 包括 0 个字符)
select name from table_name where name like '孙%';--匹配到孙悟空，孙权

-- _匹配严格的一个字符
select name from table_name where name like '孙_';--匹配到孙权

3.NULL的查询：is [not] null

--查询 qq_mail 已知的同学的姓名
select name,qq_mail from student where qq_email is not null;

--查询 qq_mail 未知的同学姓名
select name,qq_mail from student where qq_mail is null;

1.5 分页查询：LIMIT

--起始下标为0

-- 从0开始，筛选 n 条结果
select ... from table_name [where ...] [order by ...] limit n;

--从s 开始，筛选 n 条结果
select ... from table_neme [where ...] [order by ...] limint s,n;

--从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
select ... from table_name [where ...] [order by ...] limit n offset s

1.6 修改（Update）

语法：

update table_name set column =...[where ...] [order by ...][limit ...]

1.7 删除

语法：

delete from table_name [where ...] [order by ...] [limit ...]

二、数据库的约束

2.1 约束类型

1.NOT NULL -指示某列不能存储NULL值

2.UNIQUE -保证某列的每行的值必须是惟一的

3.DEFAULT -规定没有给列赋值时的默认值

4.PRIMARY KEY -NOT NULL 和 UNIQUE 的结合，确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的纪录

5.FOREIGN KEY -保证一个表中的数据匹配另一个表中的值的参照完整性

6.CHECK -保证列中的值符合指定的条件。对于MySQL数据库，对CHECK字句进行分析，但是忽略CHECK字句。

2.1.1NULL约束

创建表时，可以指定某列不为空

 create table student(id int not null,sn int,name varchar(20),qq_mail varchar(20));

 insert into student(sn,name,qq_mail) values(23,'zhangsan','353323@qq.com');
 Field 'id' doesn't have a default value

2.1.2 UNIQUE :唯一约束

指定sn 为唯一的

 create table  student(id int not null,sn int unique,name varchar(20),qq_mail varchar(20));

 insert into student values (1,2,'zhangsan','235@qq.com'),(2,2,'lisi','352@qq.com');
ERROR 1062 (23000): Duplicate entry '2' for key 'sn'

2.1.3 DEFAULT:默认值约束

指定插入数据是，name 列为空，默认值为unknown；

create table student (id int not null,sn int unique,name varchar(20) default 'unknow',qq_mail varchar(20));

 insert into student (id,sn,qq_mail)values (1,3,'35452@qq.com');

2.1.4 primary key: 主键约束

指定 id 列为主键

create table student (id int primary key auto_increment,sn int unique, name varchar(20) default'unknown',qq_mail varchar(20));

主键是not null 和unique的结合，可以不用not null

三、新增

插入查询结果

 create table test_user(id int primary key auto_increment,
                        name varchar(20),
                        age int,
                        email varchar(20),
                        sex varchar(5),
                        mobile varchar(20));

--将学生表中的所有数据赋值到用户表

insert into test_user(name,email) select name,qq_mail from student;

四、查询

4.1 聚合查询

常见的统计总数、计算平均值，可以使用聚合函数来实现，常见的聚合函数有：

案列：

COUNT

--统计班级共有多少同学
select count(*) from student;

--统计班级收集的qq_mail有多少个，qq_mail为null的数据不会计入结果
select count(qq_mail) from student;

SUM

-- 统计数学成绩总分
select sum(math) from exam_result;

-- 不及格 <60 的总分，没有结果，返回null
select sum(math) from exam_result where math <60;

AVG

-- 统计平均总分
select AVG(chinese + math + english) 平均总分 from exam_result;

MAX

-- 返回英语最高分
select max(english) from exam_result;

MIN

-- 返回>70 分以上的数学的最低分
select min(math) from exam_result where math >70;

4.2 GROUP BY 子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中

语法：

select column1,sum(column2),... from table group by column1,coulmn3;

示例：

create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);

select role ,max(salary),min(salary),avg(salary) from emp group by role;

4.3 HAVING

GROUP BY 子句进行分组后，需要对分组结果再进行条件过滤时，不能使用WHERE 语句，而需要用HAVING

显示平均工资低于1500 的角色和它的平局工资

 select role ,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

4.3 联合查询

4.3.1 内连接

示例：

（1）查询“许仙”同学的成绩

 select score.score from student join score on student.id=score.student_id and student.name='许仙

（2）查询所有同学的总成绩，及同学的个人信息：

select stu.sn,stu.name,stu.qq_mail,sum(sco.score) from student stu join score sco on stu.id =sco.student_id group by sco.student_id;

+-------+-----------------+-----------------+----------------+
| sn    | name            | qq_mail         | sum(sco.score) |
+-------+-----------------+-----------------+----------------+
| 09982 | 黑旋风李逵      | xuanfeng@qq.com |          300.0 |
| 00835 | 菩提老祖        | NULL            |          119.5 |
| 00391 | 白素贞          | NULL            |          200.0 |
| 00031 | 许仙            | xuxian@qq.com   |          218.0 |
| 00054 | 不想毕业        | NULL            |          118.0 |
| 51234 | 好好说话        | say@qq.com      |          178.0 |
| 83223 | tellme          | NULL            |          172.0 |
+-------+-----------------+-----------------+----------------+

（3）查询所有的同学的成绩，及同学的个人信息

select stu.id ,stu.sn,stu.name,stu.qq_mail,sco.score,sco.course_id,cou.name from student stu join score sco on stu.id=sco.student_id join course cou on sco.course_id =cou.id order by stu.id;
+----+-------+-----------------+-----------------+-------+-----------+--------------------+
| id | sn    | name            | qq_mail         | score | course_id | name               |
+----+-------+-----------------+-----------------+-------+-----------+--------------------+
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |  70.5 |         1 | Java               |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |  98.5 |         3 | 计算机原理         |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |  33.0 |         5 | 高阶数学           |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |  98.0 |         6 | 英文               |
|  2 | 00835 | 菩提老祖        | NULL            |  60.0 |         1 | Java               |
|  2 | 00835 | 菩提老祖        | NULL            |  59.5 |         4 | 语文               |
|  3 | 00391 | 白素贞          | NULL            |  33.0 |         1 | Java               |
|  3 | 00391 | 白素贞          | NULL            |  68.0 |         3 | 计算机原理         |
|  3 | 00391 | 白素贞          | NULL            |  99.0 |         5 | 高阶数学           |
|  4 | 00031 | 许仙            | xuxian@qq.com   |  67.0 |         1 | Java               |
|  4 | 00031 | 许仙            | xuxian@qq.com   |  23.0 |         3 | 计算机原理         |
|  4 | 00031 | 许仙            | xuxian@qq.com   |  56.0 |         5 | 高阶数学           |
|  4 | 00031 | 许仙            | xuxian@qq.com   |  72.0 |         6 | 英文               |
|  5 | 00054 | 不想毕业        | NULL            |  81.0 |         1 | Java               |
|  5 | 00054 | 不想毕业        | NULL            |  37.0 |         5 | 高阶数学           |
|  6 | 51234 | 好好说话        | say@qq.com      |  56.0 |         2 | 中国传统文化       |
|  6 | 51234 | 好好说话        | say@qq.com      |  43.0 |         4 | 语文               |
|  6 | 51234 | 好好说话        | say@qq.com      |  79.0 |         6 | 英文               |
|  7 | 83223 | tellme          | NULL            |  80.0 |         2 | 中国传统文化       |
|  7 | 83223 | tellme          | NULL            |  92.0 |         6 | 英文               |
+----+-------+-----------------+-----------------+-------+-----------+--------------------+

4.3.2 外连接

外连接分为左外连接和右外连接，如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

语法：

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

示例：

--"老外学中文"同学，没有考试成绩，也显示出来了
select * from student left join score on student.id=score.student_id;

--对应的右外连接为
 select * from score right join student on student.id=score.student_id;

4.3.3 自连接

自连接是指在同一张表连接自身进行查询

示例：

显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

select s1.* from score s1 ,score s2 where s1.student_id= s2.student_id and s1.score<s2.score and s1.course_id=1 and s2.course_id=3;

注：自连接必须使用别名

4.3.4 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

示例：

（1）单行子查询：返回一行纪录的子查询

查询与“不想毕业”同学的同班同学：

mysql> select * from student where classes_id =(select classes_id from student where name='不想毕业');
+----+-------+-----------------+-----------------+------------+
| id | sn    | name            | qq_mail         | classes_id |
+----+-------+-----------------+-----------------+------------+
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |          1 |
|  2 | 00835 | 菩提老祖        | NULL            |          1 |
|  3 | 00391 | 白素贞          | NULL            |          1 |
|  4 | 00031 | 许仙            | xuxian@qq.com   |          1 |
|  5 | 00054 | 不想毕业        | NULL            |          1 |
+----+-------+-----------------+-----------------+------------+

（2）多行子查询：返回多行纪录的子查询

查询“语文” 或“英文”课程的成绩信息

使用 IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');

-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文')

[NOT] EXISTS 关键字：

 select * from score sco where exists (select cou.id from course cou where (name='语文' or name='英文') and cou.id=sco.course_id);
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.0 |          1 |         6 |
|  59.5 |          2 |         4 |
|  72.0 |          4 |         6 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+

 select * from score sco where not exists (select cou.id from course cou where (name='语文' or name='英文') and cou.id=sco.course_id);
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  70.5 |          1 |         1 |
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  60.0 |          2 |         1 |
|  33.0 |          3 |         1 |
|  68.0 |          3 |         3 |
|  99.0 |          3 |         5 |
|  67.0 |          4 |         1 |
|  23.0 |          4 |         3 |
|  56.0 |          4 |         5 |
|  81.0 |          5 |         1 |
|  37.0 |          5 |         5 |
|  56.0 |          6 |         2 |
|  80.0 |          7 |         2 |
+-------+------------+-----------+

FROM

在from子句中使用子查询：子查询语句出现在from子句中。这里是把一个子查询当做一个临时表使用。

示例：

查询所有比“中文系2019级3班”平均分高的成绩信息：

-- 获取“中文系2019级3班”的平均分，将其看作临时表
SELECT
avg( sco.score ) score
FROM
score sco
JOIN student stu ON sco.student_id = stu.id
JOIN classes cls ON stu.classes_id = cls.id
WHERE
cls.NAME = '中文系2019级3班';

查询成绩表中，比以上表平均分高的成绩：

SELECT
*
FROM
score sco,
(
SELECT
avg( sco.score ) score
FROM
score sco
JOIN student stu ON sco.student_id = stu.id
JOIN classes cls ON stu.classes_id = cls.id
WHERE
cls.NAME = '中文系2019级3班'
) tmp
WHERE
sco.score > tmp.score;

4.3.5 合并查询

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

示例：查询id小于3，或者名字为“Java”的课程

-- 可以看到结果集中出现重复数据Java
 select * from course where id<3 union all select * from course where name ='Java';
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | Java               |
|  2 | 中国传统文化       |
|  1 | Java               |
+----+--------------------+

union和关键字or 使用方法一样，用于取得两个结果集的并集。当使用该操作符时，会去掉结果集中的重复行（or 只能用于一个表，二union能用于多个表，当union作用于多个表时需要多个结果列对应）

五、索引事务

5.1 索引

5.1.1 使用

创建主键约束、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应的索引

--查看索引
show index from 表名;

--创建索引
create index 索引名 on 表名(字段名);

--删除索引
drop index 索引名 on 表名;

5.1.2 索引背后的数据结构

索引背后使用的数据结构为B+树，在了解B+树之前，我们先认识一下B- 树。B- 树也称为B树，实际上是一个N叉搜索树。当树的高度越高，进行查询比较的时候访问磁盘的次数就越多，IO流的访问次数也就越高，导致速度变慢，因此N叉搜索树降低了树的高度，也就意味着降低了访问磁盘的次数，加快了访问速度。

B树：

B+树：

B+树的特点

1.一个节点可以存储N个key，N个key划分出了N个区间，（而不是N+1个区间）

2.每个节点中的key的值，都会在子节点中存在（同时key是结点的最大值）

3.B+ 树的叶子节点，是首尾相连，类似于一个链表~

4.由于叶字节点是完整的数据集合，只在叶子节点这里存储数据表的每一行的数据，而非叶字节点，只存key值本身即可

B+树的优点：

1.当前一个节点保存更多的key，最终树的高度是相对更矮的，查询的时候减少了IO访问次数（和B B树是一样的）

2.所有的查询最终都会落到叶字节点。（查询任何一个数据，经过的IO访问次数，是一样的）

3.B+树的所有叶子节点构成链表，此时比较方便范围查询

4.由于数据都在叶字节点上，非叶子节点，只存储key,导致非叶子节点，占用空间是比较小的，这些非叶子节点就可能在内存中缓存(或者是缓存一部分)。又进一步减少了IO的次数!!!

总结:

(1) 对于插入、删除数据频率高的表，不适合用索引

(2) 对于某列修改频率高的，该列不适合用索引

(3) 通过某列或某几列的条件查询频率高的，可以对这些列创建索引

5.2 事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。

在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

5.2.1 使用

（1）开启事务：start transaction；

（2）执行多条sql 语句

（3）回滚或提交：rollback/commit；

说明：rollback即是全部失败，commit即是全部成功。

start transaction；

--阿里巴巴账户减少2000
update account set money=money -2000 where name='阿里巴巴';

--四十大盗账户增加2000
update account set money=money+2000 where name ='四十大盗';

commit;

5.2.2 特性

1.原子性：原子性要求所有语句要么全部成功，要么全部不成功，不能部分成功

2.一致性：规定了事物提交前后，永远只可能存在事物提交前的状态和事物提交后的状态，从一个一致性的状态到另一个一致性状态，而不可能出现中间的过程态。

3.持久性：事务修改的内容是写到硬盘上的，持久存在的，重启也不丢失

4.隔离性：为了解决“并发”执行事务，引起的问题

并发执行事务可能产生的问题：

（1）脏读问题（给写加锁）

一个事务A正在对数据进行修改的过程中，还没提交之前，另外一个事务B，也对一个数据进行了读取，此时B的读操作成为“脏读”，读到的数据也成为“脏数据”。“脏”是“无效”的意思，因为在事务A未提交之前，A可能会又把数据改了。为了解决脏读问题，MySQL引入了“写操作加锁”这样的机制。这样当事务A写的时候，事务B就没法读，意味着“写操作”和“读操作”不能并发了。这个给写加锁的操作，就降低了并发程度（降低了效率），提高了隔离性（提高了数据的准确性）。

（2）不可重复读（给读加锁）*

事务1 已经提交了数据，此时数据2 开始读取数据，在读取过程中，事务 3 又提交了新的数据，此时意味着同一个事务2 之内，多次读取数据，读出来的结果是不相同的（预期是一个事务中，多次读取结果是一样的），就叫做“不可重复读”。此时便需要通过读加锁（在事务2读取数据的过程中，事务 3 不能对数据进行修改），进一步的降低了事务的并发处理能力（处理效率也降低）

（3）幻读（彻底串行化）

当事务B读取student.java 时，事务A就创建了一个Teacher.java，此时导致事务B读取的结果集不同。

在读和写加锁的前提下，一个事务两次读取同一个数据，发现读取的数据值是一样的，但是结果集不一样（Student.java）代码内容不变，但是第一次看到的是只有Student.java这个文件，第二次看到的是Student.java 和 Teacher.java 了。这种就成为“幻读”。数据库使用“串行化”这样的方式来解决幻读，彻底放弃并发处理事务，一个接一个的串行的处理事务，这样做，并发程度是最低的（效率最慢的），隔离性是最高的（准确性也是最高的）

对应上述问题，MySQL提供了4中隔离级别，就对应上面的几个情况：

read uncommitted 没有进行任何锁限制，并发最高（效率最高），隔离性最低（准确性最低）

read committed 给写加锁了，并发程度降低，隔离性提高了

repeatable read 给写和读都加锁并发程度有降低，隔离性又提高了

serializable 串行化，并发程度最低，隔离性最高

六、JDBC编程

6.1 Java的数据库编程：JDBC

JDBC,即Java Database Connectivity,java数据库链接。是一种用于执行SQL语句的Java API，它是Java中的数据库链接规范。这个API有java.sql.*,javax.sql.* 包中的一些类和接口组成。

JDBC编程需要导入相应的jar包，在高版本的idea中，需要导入相对应的jar包。

导包：

首先需要在项目中new一个Directory，

然后将jar包所在的路径直接粘贴在我们建好的Directory包中

把这个目录记作项目的库

6.2 数据库连接 Connection

Connection 接口实现类由数据库提供，获取Connection对象通常有两种方式

一种是通过DriverManger(驱动管理类)的静态方法获取：

//加载JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");

//创建数据库连接
Connection connection =DriverManager.getConnection(url);

一种是通过DataSource(数据源）对象获取。实际应用中会使用DataSource对象

         DataSource dataSource =new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");//固定为“root”
        ((MysqlDataSource)dataSource).setPassword("123456");//安装数据库时设置的密码

以上两种方式的区别是：

1.DriverManager类来获取的Connection连接，是无法重复利用的，每次使用完以后释放资源时，通过connection.close()都是关闭物理连接。

2.DataSource是提供连接池的支持。连接池在初始化时将创建一定数量的数据库连接，这些连接是可以复用的，每次使用完数据库连接，释放资源调用connection.close()都是将Connection 连接对象回收

6.3 Statement对象

statement对象主要是将SQL语句发送到数据库中，JDBC API中主要提供了三种Statement对象。

Statement: 用于执行不带参数的简单SQL语句

PreparedStatement: 用于执行带或者不带参数的SQL语句；SQL语句会预编译在数据库系统；执行速度快于Statement对象

CallabelStatement:用于执行数据库存储过程的调用

主要掌握两种执行SQL的方法

（1）executeQuery()方法执行后返回单个结果集的，通常用于select语句

（2）executeUpdate()方法返回值是一个整数，指示受影响的行数，通常用于update、insert、delete语句

6.4 ResultSet 对象

ResultSet 对象它被称为结果集，它代表符合SQL语句条件的所有行，并且它通过一套getxxx方法提供了对这些中数据的访问

ResultSet 里的一行一行排列，每行有多个字段，并且有一个纪录指针，指针所指的数据行叫做当前数据行，我们只能来操作当前行的数据行。我们如果想要取得某一条纪录，就要使用ResultSet 的next()方法，如果我们想要得到ResultSet里的所有纪录，就应该使用while循环。

插入代码示例：

public class JDBCInsert {
    public static void main(String[] args) throws SQLException {
        //JDBC 需要通过以下步骤来完成开发
        //1.创建并初始化一个数据源
        Scanner scanner=new Scanner(System.in);
        DataSource dataSource =new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");//固定为“root”
        ((MysqlDataSource)dataSource).setPassword("123456");//安装数据库时设置的密码
        //可以直接创建MysqlDataSource对象
        //MysqlDataSource dataSource=new MysqlDataSource();

        //2.和数据库服务器建连接
        Connection connection =dataSource.getConnection();//此时需要使用java.sql中的Connection

        //3.构造 SQL 语句
        /*//此时代码是写死的
        String sql="insert into student values(1,'张三')";
        PreparedStatement statement=connection.prepareStatement(sql);*/
        System.out.println("请输入学生姓名：");
        String name=scanner.next();
        System.out.println("请输入学号：");
        int id=scanner.nextInt();

        String sql="insert into student value(?,?)";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.setString(2,name);
        System.out.println(statement);

        //4.执行 SQL 语句
        int ret=statement.executeUpdate();
        System.out.println("ret="+ret);

        //5.释放必要的资源
        statement.close();
        connection.close();
    }
}

查询代码示例：

public class JDBCSelect {
    public static void main(String[] args) throws SQLException {
        MysqlDataSource mysqlDataSource=new MysqlDataSource();
        mysqlDataSource.setURL("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&useSSL=false");
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("123456");

        Connection connection=mysqlDataSource.getConnection();

        String sql="select * from student";
        PreparedStatement statement =connection.prepareStatement(sql);

        ResultSet resultSet=statement.executeQuery();

        while (resultSet.next()){
            //
            int id=resultSet.getInt("id");
            String name=resultSet.getString("name");
            System.out.println("id="+id+"name="+name);
        }

    }
}