Java ｜ MySQL 基础篇

博主： Hang
发布时间：2022 年 08 月 15 日
104 次浏览
暂无评论
10869字数
分类： Java

MySQL概述

什么是数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

数据表的基本操作

数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意：在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作，否则会抛出“No database selected”错误。

create table 表名(字段1 字段类型,字段2 字段类型,字段3 字段类型,);

示例：

CREATE TABLE `emp` (
    id INT,
    `name` VARCHAR ( 32 ),
    sex CHAR ( 1 ),
    brithday DATE,
    entry_date DATETIME,
    job VARCHAR ( 32 ),
    salary DOUBLE,
    `resume` TEXT 
) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;-- 添加
INSERT INTO `emp`
VALUES
    ( 1, '哪吒', '男', '2000-11-11', '2010-10-10 11:11:11', '开车', '3000', '啦啦啦' );
SELECT    * FROM    `emp`;

查看数据表

show tables;

查看表的字段信息

desc 表名;

修改数据表

修改表名
alter table 表名 rename to re名字

修改字段名

alter table stu change name sname varchar(10);

修改字段数据类型

alter table stu modify sname int;

增加字段

alter table stu add address varchar(50);

删除数据表

drop table 表名;
示例：

SELECT * FROM `emp`
-- 修改表名
RENAME table `emp` to `emps`
-- 添加列
ALTER table `emps` ADD (hobby VARCHAR(32));
-- 查询表结构
DESC `emps`
-- 修改job列
ALTER TABLE `emps` MODIFY job VARCHAR(60)
-- 在job列后添加img列
ALTER table `emps` ADD img VARCHAR(32) AFTER job;
DESC `emps`
-- 删除hobby列
ALTER TABLE emps DROP hobby
-- 更改字符集
ALTER TABLE emps CHARACTER SET utf8

增删改查

Insert

插入数据

INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

同时插入多条语句

INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;

示例：

CREATE TABLE `goods` (
    id INT,
    goods_name VARCHAR(10),
    ptice DOUBLE;
)
INSERT INTO `goods` (id,goods_name,proce) 
    VALEES(10, '华为', 12000),(20, '菠萝', 2000);

注意事项：

插入的数据应与字段的数据类型相同
数据的长度应在规定的范围内
在values中类列出的数据位置必须与被加入的列的排列位置相对应
字符和日期型数据应包含在单引号中
列可以插入控制[前提是该字段允许为空]
如果是给表中所有字段添加数据，可以不写字段名称

update

UPDATE tb_name SET 字段名=修改值 WHERE [条件]

示例：

-- 将所有员工薪水改为500
UPDATE emp SET salary = 500;
-- 将ocean员工的薪水改为3000
UPDATE emp SET salary = 3000
    WHERE user_name = 'ocean';
-- 将ocean的薪水增加100
UPDATE emp SET salary = salary + 100
    WHERE user_name = 'ocean';

使用细节：

update可以用新值更新原有表中行中的各列
set子句指示要修改哪个
where子句指定更新哪行，没有where会修改所有列

delete

DELETE FROM 表名 [WHERE 条件表达式];

示例：

-- 删除员工ocean
DELETE FROM emp
    WHERE user_name = 'ocean';
-- 删除表中所有数据
DELETE FROM emp;

使用细节：

如果不使用where子句，将删除表中的所有数据
delete语句不能删除某一列的值
使用delete语句进删除记录，不能删除表本身。删除表本身需使用drop table 表名]

selete

基本用法
使用表达式对查询的列进行运算
起别名

示例：
– 查询表中所有学生的信息。
– 查询表中所有学生的姓名和对应的英语成绩。
– 过滤表中重复数据 distinct
– 要查询的记录，每个字段都相同，才会去重
– 统计每个学生的总分
– 在所有学生总分加 10 分的情况
– 使用别名表示学生分数。

CREATE TABLE student( 
    id INT NOT NULL DEFAULT 1, 
    NAME VARCHAR(20) NOT NULL DEFAULT '', 
    chinese FLOAT NOT NULL DEFAULT 0.0, 
    english FLOAT NOT NULL DEFAULT 0.0, 
    math FLOAT NOT NULL DEFAULT 0.0 
    );
    -- 查询表中所有学生的信息。 
    SELECT * FROM student; 
    -- 查询表中所有学生的姓名和对应的英语成绩。 
    SELECT `name`,english FROM student; 
    -- 过滤表中重复数据 distinct
    SELECT DISTINCT english FROM student; 
    -- 要查询的记录，每个字段都相同，才会去重 
    SELECT DISTINCT `name`, english FROM student
    -- 统计每个学生的总分 
    SELECT `name`, (chinese+english+math) FROM student; 
    -- 在所有学生总分加 10 分的情况 
    SELECT `name`, (chinese + english + math + 10) FROM student; 
    -- 使用别名表示学生分数。 
    SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score FROM student;

注意事项：

select指定查询哪些列的数据
column指定列名
*代表查询所有列
From指定查询哪张表
DISTINCT可选，指显示结果时，是否去掉重复数据

在where子句中经常使用的运算符

示例：

SELECT * FROM student
    WHERE `name` = `ocean`;
SELECT * FROM student
    WHERE english > 90;
-- 查询总分大于 200 分的所有同学 
SELECT * FROM student WHERE (chinese + english + math) > 200 
-- 查询 math 大于 60 并且(and) id 大于 4 的学生成绩 
SELECT * FROM student WHERE math >60 AND id > 4 
-- 查询英语成绩大于语文成绩的同学 
SELECT * FROM student WHERE english > chinese 
-- 查询总分大于 200 分 并且 数学成绩小于语文成绩,的姓赵的学生. 
-- 赵% 表示 名字以韩开头的就可以 
SELECT * FROM student WHERE (chinese + english + math) > 200 AND math < chinese AND `name` LIKE '赵%' 
-- 查询英语分数在 80－90 之间的同学。 
SELECT * FROM student WHERE english >= 80 AND english <= 90; SELECT * FROM student WHERE english BETWEEN 80 AND 90; 
-- between .. and .. 是 闭区间 
-- 查询数学分数为 89,90,91 的同学。 
SELECT * FROM student WHERE math = 89 OR math = 90 OR math = 91; SELECT * FROM student WHERE math IN (89, 90, 91);

使用order by子句排序查询

order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名

ASC 升序[默认] 、DESC降序

order by子句应位于select语句结尾
示例：

-- 对数学成绩升序输出
SELECT * FROM student
    ORDER BY math
-- 总分降序输出
SELECT `name`, (chinese+math+english) AS `总分` FROM student
    ORDER BY `总分` DESC

group by
分页查询
select ... limit start,rows
从start+1 行开始取，取出rows行

-- 第一页
SELECT * FROM emp
    ORDER BY id
    limit 0, 3
-- 第二页
SELECT * FROM emp
    ORDER BY id
    limit 3， 3
-- 推导公式
SELECT * FROM emp
    ORDER BY id
    limit 显示行数*(第几页-1), 显示行数

合计/统计函数

count：返回行的总数
sum：返回满足where条件的行的和
avg：返回满足where条件的一列的平均值
max\min：返回满足where条件的一列的最大最小值
count(*)和count(列)的区别：
- count(*)返回满足条件的记录的行数
- count(列)统计满足条件的某列有多少个，但是会排除为null的情况

用法示例:

-- 统计一个班级共有多少学生
SELECT　COUNT(*) student
-- 统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student
    WHERE math>90
    -- 演示 sum 函数的使用 
    -- 统计一个班级数学总成绩？
    SELECT SUM(math) FROM student; 
    -- 统计一个班级语文、英语、数学各科的总成绩
    SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;
    -- 统计一个班级语文、英语、数学的成绩总和 
    SELECT SUM(math + english + chinese) FROM student; 
    -- 统计一个班级语文成绩平均分 
    SELECT SUM(chinese)/ COUNT(*) FROM student; SELECT SUM(`name`) FROM student; 
    -- 演示 avg 的使用 
    -- 练习： 
    -- 求一个班级数学平均分？ 
    SELECT AVG(math) FROM student; 
    -- 求一个班级总分平均分 
    SELECT AVG(math + english + chinese) FROM student; 
    -- 演示 max 和 min 的使用 
    -- 求班级最高分和最低分（数值范围在统计中特别有用） 
    SELECT MAX(math + english + chinese), MIN(math + english + chinese) FROM student; 
    -- 求出班级数学最高分和最低分 
    SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre FROM student;

数据表的约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下：

约束条件说明

PRIMARY KEY 主键约束用于唯一标识对应的记录
FOREIGN KEY 外键约束
NOT NULL 非空约束
UNIQUE 唯一性约束
DEFAULT 默认值约束，用于设置字段的默认值

以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。

主键约束

主键约束（primary key）用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为null,主键的值是不可以重复的。

一张表最大只能有一个主键，但可以是复合主键

字段名数据类型 primary key;

-- 方式一
    create table student(
    id int primary key,
    name varchar(20)
);
-- 方式二
create table student01(sql
    id int
    name varchar(20),
    primary key(id)
);
-- 复合主键
CREATE TABLE emp(
    id INT,
    `name` VARCHAR(32),
    salary DOUBLE,
    PRIMARY KEY(id,`name`)
    
)

非空约束

非空约束（NOT NULL）指的是字段的值不能为空
字段名数据类型　NOT NULL

create table student02(
id int
name varchar(20) not null
);

默认值约束

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值

字段名数据类型 DEFAULT 默认值;

create table student03(
id int,
name varchar(20),
gender varchar(10) default 'male'
);

唯一性约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现

字段名数据类型 UNIQUE

create table student04(
id int,
name varchar(20) unique
);

使用细节：

如果没有指定not null，则unique字段可以有多个null
一张表可以有多个unique字段

外键约束

外键约束即FOREIGN KEY常用于多张表之间的约束

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是null

create table student04(
id int,
name varchar(20) unique
);

示例：

CREATE TABLE ee(
    id INT(4) primary key auto_increment,
    cname VARCHAR(20) NOT Null,
    `age` INT(2),
    cateid INT(4),
  FOREIGN KEY (cateid) REFERENCES dd(did)
    )

数据一致性

建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。

删除外键

alter table 从表名 drop foreign key 外键名;

细节

从表里的外键通常为主表的主键
从表里外键的数据类型必须与主表中主键的数据类型一致
主表发生变化时应注意主表与从表的数据一致性问题
外键指向的表的字段，要求是primary key或者是unique
表单类型是innodb，才能支持外键
外键字段的类型要和主键字段的类型一致(长度可不同)
外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]
一旦简历主外键关系，数据就不能随意删除

check约束

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在该范围内就会提示出错

注：mysql5.7不支持check，只做语法校验，但不会生效；mysql8.0支持

CREATE TABLE t23 ( 
    id INT PRIMARY KEY, 
    `name` VARCHAR(32) , 
    sex VARCHAR(6) CHECK (sex IN('man','woman')), 
    sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);
-- 添加数据 
INSERT INTO t23 VALUES(1, 'jack', 'mid', 1); 
SELECT * FROM t23;

mysql 表外连接

左外连接（左侧的表完全显示）

右外连接（右侧的表完全显示）

左外连接

SELECT ...FROM 表1 LEFT JOIN 表2 ON 条件

SELECT `name`, stu.id, grade
 FROM stu
 LEFT JOIN exam
 ON stu.id = exam.id

多表查询

多表查询是指基于两个和两个以上的表查询
使用条件约束，防止笛卡尔集出现
多表查询的条件不能少于（表的个数-1），否则会出现笛卡尔集

-- 显示部门号为 10 的部门名、员工名和工资 
SELECT ename,sal,dname,emp.deptno 
 FROM emp, dept 
 WHERE emp.deptno = dept.deptno 
 AND emp.deptno = 10
-- 显示各个员工的姓名、工资、及其工资的级别
-- 思路：先写简单；然后写过滤条件
 SELECT ename, sal, grade
     FROM emp,salgrade
     WHERE sal BETWEEN losal AND hisal

自连接

特点：

把同一张表当作两张表使用
需要给表分别取别名
列名不明确，可以指定列的别名

使用示例：


-- 显示公司员工姓名和他的上级名字
/*
    分析：
    员工名字在emp，上级名字在emp
    员工和上级通过emp表的mgr列关联
*/
SELECT worker.ename AS '职员名',boss.ename AS '上级名'
    FROM emp worker,emp boss
    WHERE worker.mgr = boss.empno

表子查询

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

单行子查询
单行子查询是指只返回一行数据的子查询语句

多行子句查询
多行子句查询值返回多行数据的子查询使用关键字IN

使用示例：

-- 显示与SMITH同一部门的所有员工
/*
    1.先查询到SMITH的部门号
    2.把该语句当作子查询
*/
SELECT deptno
    FROM emp
    WHERE ename = 'SMITH'
    
SELECT *
    FROM emp
    WHERE deptno = (
            SELECT deptno
            FROM emp
            WHERE ename = 'SMITH'        
        )
-- 课堂练习:如何查询和部门 10 的工作相同的雇员的 
-- 名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员.        
SELECT DISTINCT job
    FROM emp
    WHERE deptno = 10
        
SELECT ename, job, sal, deptno
    FROM emp
    WHERE job IN (
        SELECT DISTINCT job
        FROM emp
        WHERE deptno = 10    
    )AND deptno <> 10

子查询当做临时表使用

-- 查询ecshop中各类别中，价格最高的商品
-- 先得到各个类别中，价格最高的商品 max+group by cat_id 来当临时表
-- 把子查询当作一张临时表可以解决很多复杂的查询
SELECT cat_id,MAX(shop_price)
    FROM ecs_goods
    GROUP BY cat_id
    
SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price
    FROM(
            SELECT cat_id,MAX(shop_price) AS max_price
            FROM ecs_goods
            GROUP BY cat_id
    ) temp,ecs_goods
    WHERE temp.cat_id = ecs_goods.cat_id
    AND temp.max_price = ecs_goods.shop_price

all 和 any的使用
all：所有

any：任一

多列子查询

示例：

查询与allen部门和岗位完全相同的所有雇员(不包含ALLEN本人)

(字段 1，字段 2 …) = (select 字段 1，字段 2 from …)

-- 得到ALLEN的部门和岗位
-- 把该语句当作子查询来使用，并使用多列子查询的语法进行匹配
SELECT deptno, job
 FROM emp
 WHERE ename = 'ALLEN'

SELECT * 
 FROM emp
 WHERE (deptno , job) = (
     SELECT deptno, job
     FROM emp
     WHERE ename = 'ALLEN'
 ) AND ename <> 'ALLEN'
-- 请查询 和宋江数学，英语，语文 -- 成绩 完全相同的学生 
SELECT * 
 FROM student 
 WHERE (math, english, chinese) = ( 
     SELECT math, english, chinese 
     FROM student 
     WHERE `name` = '宋江' )

练习

查找每个部门工资高于本部平均工资的人
查找每个部门工资最高的人的详细信息

查找每个部门的信息(部门名，编号，地址)和人员数量

-- 查找每个部门工资高于本部平均工资的人
SELECT deptno, AVG(sal)
 FROM emp
 GROUP BY deptno

SELECT ename, sal, emp.deptno, temp.avg_sal
 FROM emp, (
     SELECT deptno, AVG(sal) AS avg_sal
     FROM emp
     GROUP BY deptno
 ) temp
 WHERE emp.deptno = temp.deptno
 AND emp.sal > temp.avg_sal
-- 查找每个部门工资最高的人的详细信息
SELECT deptno, MAX(sal) AS max_sal
 FROM emp
 GROUP BY deptno

SELECT ename ,sal,emp.deptno
 FROM emp,(
     SELECT deptno, MAX(sal) AS max_sal
     FROM emp
     GROUP BY deptno
 )temp
 WHERE emp.deptno = temp.deptno
 AND emp.sal = temp.max_sal
-- 查找每个部门的信息(部门名，编号，地址)和人员数量
SELECT COUNT(*), deptno
 FROM emp
 GROUP BY deptno
-- 当多个表的列不重复时，才可以之间写列名
SELECT temp.*, dname, loc
 FROM dept, (
     SELECT COUNT(*) AS nums, deptno
     FROM emp
     GROUP BY deptno
 )temp
 WHERE temp.deptno = dept.deptno

最后修改：2023 年 01 月 09 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

memset0
该评论仅登录用户及评论双方可见
我tm来啦
??
XiaoSheng
6
奥利给
为啥我的kafka创建topic总是连接超时，啊啊啊啊啊啊啊啊...
SensenPlayer
空白占位符

Java ｜ MySQL 基础篇

Hang • 2022 年 08 月 15 日

<h1>MySQL概述</h1><h3>什么是数据库</h3><p>数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。<br>每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。<br>我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。</p><p>所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。</p><hr><h1>数据表的基本操作</h1><p><strong>数据库创建成功后可在该数据库中创建数据表(简称为表)存储数据。请注意：在操作数据表之前应使用“USE 数据库名;”指定操作是在哪个数据库中进行先关操作，否则会抛出“No database selected”错误。</strong></p><p><code>create table 表名(字段1 字段类型,字段2 字段类型,字段3 字段类型,);</code></p><p>示例：</p><pre><code>CREATE TABLE `emp` (
    id INT,
    `name` VARCHAR ( 32 ),
    sex CHAR ( 1 ),
    brithday DATE,
    entry_date DATETIME,
    job VARCHAR ( 32 ),
    salary DOUBLE,
    `resume` TEXT 
) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;-- 添加
INSERT INTO `emp`
VALUES
    ( 1, '哪吒', '男', '2000-11-11', '2010-10-10 11:11:11', '开车', '3000', '啦啦啦' );
SELECT    * FROM    `emp`;</code></pre><h3>查看数据表</h3><p><code>show tables;</code></p><h3>查看表的字段信息</h3><p><code>desc 表名;</code></p><h3>修改数据表</h3><p>修改表名<br><code>alter table 表名 rename to re名字</code></p><h3>修改字段名</h3><p><code>alter table stu change name sname varchar(10);</code></p><h3>修改字段数据类型</h3><p><code>alter table stu modify sname int;</code></p><h3>增加字段</h3><p><code>alter table stu add address varchar(50);</code></p><h3>删除数据表</h3><p><code>drop table 表名;</code><br>示例：</p><pre><code>SELECT * FROM `emp`
-- 修改表名
RENAME table `emp` to `emps`
-- 添加列
ALTER table `emps` ADD (hobby VARCHAR(32));
-- 查询表结构
DESC `emps`
-- 修改job列
ALTER TABLE `emps` MODIFY job VARCHAR(60)
-- 在job列后添加img列
ALTER table `emps` ADD img VARCHAR(32) AFTER job;
DESC `emps`
-- 删除hobby列
ALTER TABLE emps DROP hobby
-- 更改字符集
ALTER TABLE emps CHARACTER SET utf8</code></pre><h1>增删改查</h1><h3>Insert</h3><h3>插入数据</h3><p><code>INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);</code></p><h3>同时插入多条语句</h3><p><code>INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;</code></p><p>示例：</p><pre><code>CREATE TABLE `goods` (
    id INT,
    goods_name VARCHAR(10),
    ptice DOUBLE;
)
INSERT INTO `goods` (id,goods_name,proce) 
    VALEES(10, '华为', 12000),(20, '菠萝', 2000);
</code></pre><h3>注意事项：</h3><ul><li>插入的数据应与字段的数据类型相同</li><li>数据的长度应在规定的范围内</li><li>在values中类列出的数据位置必须与被加入的列的排列位置相对应</li><li>字符和日期型数据应包含在单引号中</li><li>列可以插入控制[前提是该字段允许为空]</li><li>如果是给表中所有字段添加数据，可以不写字段名称</li></ul><h3>update</h3><p><code>UPDATE tb_name SET 字段名=修改值 WHERE [条件]</code></p><p>示例：</p><pre><code>-- 将所有员工薪水改为500
UPDATE emp SET salary = 500;
-- 将ocean员工的薪水改为3000
UPDATE emp SET salary = 3000
    WHERE user_name = 'ocean';
-- 将ocean的薪水增加100
UPDATE emp SET salary = salary + 100
    WHERE user_name = 'ocean';</code></pre><h3>使用细节：</h3><ul><li>update可以用新值更新原有表中行中的各列</li><li>set子句指示要修改哪个</li><li>where子句指定更新哪行，没有where会修改所有列</li></ul><h3>delete</h3><p><code>DELETE FROM 表名 [WHERE 条件表达式];</code></p><p>示例：</p><pre><code>-- 删除员工ocean
DELETE FROM emp
    WHERE user_name = 'ocean';
-- 删除表中所有数据
DELETE FROM emp;
</code></pre><p>使用细节：</p><ul><li>如果不使用where子句，将删除表中的所有数据</li><li>delete语句不能删除某一列的值</li><li>使用delete语句进删除记录，不能删除表本身。删除表本身需使用drop table 表名]</li></ul><h3>selete</h3><ul><li>基本用法</li><li>使用表达式对查询的列进行运算</li><li>起别名</li></ul><p>示例：<br>– 查询表中所有学生的信息。<br>– 查询表中所有学生的姓名和对应的英语成绩。<br>– 过滤表中重复数据 distinct<br>– 要查询的记录，每个字段都相同，才会去重<br>– 统计每个学生的总分<br>– 在所有学生总分加 10 分的情况<br>– 使用别名表示学生分数。</p><pre><code>CREATE TABLE student( 
    id INT NOT NULL DEFAULT 1, 
    NAME VARCHAR(20) NOT NULL DEFAULT '', 
    chinese FLOAT NOT NULL DEFAULT 0.0, 
    english FLOAT NOT NULL DEFAULT 0.0, 
    math FLOAT NOT NULL DEFAULT 0.0 
    );
    -- 查询表中所有学生的信息。 
    SELECT * FROM student; 
    -- 查询表中所有学生的姓名和对应的英语成绩。 
    SELECT `name`,english FROM student; 
    -- 过滤表中重复数据 distinct
    SELECT DISTINCT english FROM student; 
    -- 要查询的记录，每个字段都相同，才会去重 
    SELECT DISTINCT `name`, english FROM student
    -- 统计每个学生的总分 
    SELECT `name`, (chinese+english+math) FROM student; 
    -- 在所有学生总分加 10 分的情况 
    SELECT `name`, (chinese + english + math + 10) FROM student; 
    -- 使用别名表示学生分数。 
    SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score FROM student;</code></pre><p>注意事项：</p><ul><li>select指定查询哪些列的数据</li><li>column指定列名</li><li>*代表查询所有列</li><li>From指定查询哪张表</li><li>DISTINCT可选，指显示结果时，是否去掉重复数据</li></ul><h3>在where子句中经常使用的运算符</h3><p>示例：</p><pre><code>SELECT * FROM student
    WHERE `name` = `ocean`;
SELECT * FROM student
    WHERE english &gt; 90;
-- 查询总分大于 200 分的所有同学 
SELECT * FROM student WHERE (chinese + english + math) &gt; 200 
-- 查询 math 大于 60 并且(and) id 大于 4 的学生成绩 
SELECT * FROM student WHERE math &gt;60 AND id &gt; 4 
-- 查询英语成绩大于语文成绩的同学 
SELECT * FROM student WHERE english &gt; chinese 
-- 查询总分大于 200 分 并且 数学成绩小于语文成绩,的姓赵的学生. 
-- 赵% 表示 名字以韩开头的就可以 
SELECT * FROM student WHERE (chinese + english + math) &gt; 200 AND math &lt; chinese AND `name` LIKE '赵%' 
-- 查询英语分数在 80－90 之间的同学。 
SELECT * FROM student WHERE english &gt;= 80 AND english &lt;= 90; SELECT * FROM student WHERE english BETWEEN 80 AND 90; 
-- between .. and .. 是 闭区间 
-- 查询数学分数为 89,90,91 的同学。 
SELECT * FROM student WHERE math = 89 OR math = 90 OR math = 91; SELECT * FROM student WHERE math IN (89, 90, 91);</code></pre><h3>使用order by子句排序查询</h3><p>order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名</p><p>ASC 升序[默认] 、DESC降序</p><p>order by子句应位于select语句结尾<br>示例：</p><pre><code>-- 对数学成绩升序输出
SELECT * FROM student
    ORDER BY math
-- 总分降序输出
SELECT `name`, (chinese+math+english) AS `总分` FROM student
    ORDER BY `总分` DESC </code></pre><p>group by<br>分页查询<br><code>select ... limit start,rows</code><br>从start+1 行开始取，取出rows行</p><pre><code>-- 第一页
SELECT * FROM emp
    ORDER BY id
    limit 0, 3
-- 第二页
SELECT * FROM emp
    ORDER BY id
    limit 3， 3
-- 推导公式
SELECT * FROM emp
    ORDER BY id
    limit 显示行数*(第几页-1), 显示行数
</code></pre><h3>合计/统计函数</h3><ul><li>count：返回行的总数</li><li>sum：返回满足where条件的行的和</li><li>avg：返回满足where条件的一列的平均值</li><li>max\min：返回满足where条件的一列的最大最小值</li><li><p>count(*)和count(列)的区别：</p><ul><li>count(*)返回满足条件的记录的行数</li><li>count(列)统计满足条件的某列有多少个，但是会排除为null的情况</li></ul></li></ul><p>用法示例:</p><pre><code>-- 统计一个班级共有多少学生
SELECT　COUNT(*) student
-- 统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student
    WHERE math&gt;90
    -- 演示 sum 函数的使用 
    -- 统计一个班级数学总成绩？
    SELECT SUM(math) FROM student; 
    -- 统计一个班级语文、英语、数学各科的总成绩
    SELECT SUM(math) AS math_total_score,SUM(english),SUM(chinese) FROM student;
    -- 统计一个班级语文、英语、数学的成绩总和 
    SELECT SUM(math + english + chinese) FROM student; 
    -- 统计一个班级语文成绩平均分 
    SELECT SUM(chinese)/ COUNT(*) FROM student; SELECT SUM(`name`) FROM student; 
    -- 演示 avg 的使用 
    -- 练习： 
    -- 求一个班级数学平均分？ 
    SELECT AVG(math) FROM student; 
    -- 求一个班级总分平均分 
    SELECT AVG(math + english + chinese) FROM student; 
    -- 演示 max 和 min 的使用 
    -- 求班级最高分和最低分（数值范围在统计中特别有用） 
    SELECT MAX(math + english + chinese), MIN(math + english + chinese) FROM student; 
    -- 求出班级数学最高分和最低分 
    SELECT MAX(math) AS math_high_socre, MIN(math) AS math_low_socre FROM student;
    </code></pre><hr><h1>数据表的约束</h1><p>为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。常见约束如下：</p><p>约束条件    说明</p><ul><li>PRIMARY KEY    主键约束用于唯一标识对应的记录</li><li>FOREIGN KEY    外键约束</li><li>NOT NULL    非空约束</li><li>UNIQUE    唯一性约束</li><li>DEFAULT    默认值约束，用于设置字段的默认值</li></ul><p>以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。</p><h3>主键约束</h3><p>主键约束（primary key）用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为null,主键的值是不可以重复的。</p><p>一张表最大只能有一个主键，但可以是复合主键</p><p><code>字段名 数据类型 primary key;</code></p><pre><code>-- 方式一
    create table student(
    id int primary key,
    name varchar(20)
);
-- 方式二
create table student01(sql
    id int
    name varchar(20),
    primary key(id)
);
-- 复合主键
CREATE TABLE emp(
    id INT,
    `name` VARCHAR(32),
    salary DOUBLE,
    PRIMARY KEY(id,`name`)
    
)
</code></pre><h3>非空约束</h3><p>非空约束（NOT NULL）指的是字段的值不能为空<br><code>字段名 数据类型　NOT NULL</code></p><pre><code>create table student02(
id int
name varchar(20) not null
);
</code></pre><h3>默认值约束</h3><p>默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值</p><p><code>字段名 数据类型 DEFAULT 默认值;</code></p><pre><code>create table student03(
id int,
name varchar(20),
gender varchar(10) default 'male'
);
</code></pre><h3>唯一性约束</h3><p>唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现</p><p><code>字段名 数据类型 UNIQUE</code></p><pre><code>create table student04(
id int,
name varchar(20) unique
);</code></pre><p>使用细节：</p><ul><li>如果没有指定not null，则unique字段可以有多个null</li><li>一张表可以有多个unique字段</li></ul><h3>外键约束</h3><p>外键约束即FOREIGN KEY常用于多张表之间的约束</p><p>用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是null</p><pre><code>create table student04(
id int,
name varchar(20) unique
);</code></pre><p>示例：</p><pre><code>CREATE TABLE ee(
    id INT(4) primary key auto_increment,
    cname VARCHAR(20) NOT Null,
    `age` INT(2),
    cateid INT(4),
  FOREIGN KEY (cateid) REFERENCES dd(did)
    )</code></pre><h3>数据一致性</h3><p>建立外键是为了保证数据的完整和统一性。但是，如果主表中的数据被删除或修改从表中对应的数据该怎么办呢？很明显，从表中对应的数据也应该被删除，否则数据库中会存在很多无意义的垃圾数据。</p><h3>删除外键</h3><p><code>alter table 从表名 drop foreign key 外键名;</code></p><hr><h1>细节</h1><ol><li>从表里的外键通常为主表的主键</li><li>从表里外键的数据类型必须与主表中主键的数据类型一致</li><li>主表发生变化时应注意主表与从表的数据一致性问题</li><li>外键指向的表的字段，要求是primary key或者是unique</li><li>表单类型是innodb，才能支持外键</li><li>外键字段的类型要和主键字段的类型一致(长度可不同)</li><li>外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]</li><li>一旦简历主外键关系，数据就不能随意删除</li></ol><h3>check约束</h3><p>用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在该范围内就会提示出错</p><p>注：mysql5.7不支持check，只做语法校验，但不会生效；mysql8.0支持</p><pre><code>CREATE TABLE t23 ( 
    id INT PRIMARY KEY, 
    `name` VARCHAR(32) , 
    sex VARCHAR(6) CHECK (sex IN('man','woman')), 
    sal DOUBLE CHECK ( sal &gt; 1000 AND sal &lt; 2000)
);
-- 添加数据 
INSERT INTO t23 VALUES(1, 'jack', 'mid', 1); 
SELECT * FROM t23;</code></pre><h1>mysql 表外连接</h1><ol><li>左外连接（左侧的表完全显示）</li><li><p>右外连接（右侧的表完全显示）</p><h3>左外连接</h3><p><code>SELECT ...FROM 表1 LEFT JOIN 表2 ON 条件</code></p><pre><code>SELECT `name`, stu.id, grade
 FROM stu
 LEFT JOIN exam
 ON stu.id = exam.id</code></pre><p></p><h1>多表查询</h1><p>多表查询是指基于两个和两个以上的表查询<br>使用条件约束，防止笛卡尔集出现<br>多表查询的条件不能少于（表的个数-1），否则会出现笛卡尔集</p><pre><code>-- 显示部门号为 10 的部门名、员工名和工资 
SELECT ename,sal,dname,emp.deptno 
 FROM emp, dept 
 WHERE emp.deptno = dept.deptno 
 AND emp.deptno = 10
-- 显示各个员工的姓名、工资、及其工资的级别
-- 思路：先写简单；然后写过滤条件
 SELECT ename, sal, grade
     FROM emp,salgrade
     WHERE sal BETWEEN losal AND hisal</code></pre></li></ol><h3>自连接</h3><p>特点：</p><ul><li>把同一张表当作两张表使用</li><li>需要给表分别取别名</li><li>列名不明确，可以指定列的别名</li></ul><p>使用示例：</p><pre><code>
-- 显示公司员工姓名和他的上级名字
/*
    分析：
    员工名字在emp，上级名字在emp
    员工和上级通过emp表的mgr列关联
*/
SELECT worker.ename AS '职员名',boss.ename AS '上级名'
    FROM emp worker,emp boss
    WHERE worker.mgr = boss.empno</code></pre><h3>表子查询</h3><p>子查询<br>子查询时值嵌入在其他sql语句中的select语句，也叫嵌套查询</p><p>单行子查询<br>单行子查询是指只返回一行数据的子查询语句</p><p>多行子句查询<br>多行子句查询值返回多行数据的子查询 使用关键字IN</p><p>使用示例：</p><pre><code>-- 显示与SMITH同一部门的所有员工
/*
    1.先查询到SMITH的部门号
    2.把该语句当作子查询
*/
SELECT deptno
    FROM emp
    WHERE ename = 'SMITH'
    
SELECT *
    FROM emp
    WHERE deptno = (
            SELECT deptno
            FROM emp
            WHERE ename = 'SMITH'        
        )
-- 课堂练习:如何查询和部门 10 的工作相同的雇员的 
-- 名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员.        
SELECT DISTINCT job
    FROM emp
    WHERE deptno = 10
        
SELECT ename, job, sal, deptno
    FROM emp
    WHERE job IN (
        SELECT DISTINCT job
        FROM emp
        WHERE deptno = 10    
    )AND deptno &lt;&gt; 10</code></pre><p>子查询当做临时表使用</p><pre><code>-- 查询ecshop中各类别中，价格最高的商品
-- 先得到各个类别中，价格最高的商品 max+group by cat_id 来当临时表
-- 把子查询当作一张临时表可以解决很多复杂的查询
SELECT cat_id,MAX(shop_price)
    FROM ecs_goods
    GROUP BY cat_id
    
SELECT goods_id,ecs_goods.cat_id,goods_name,shop_price
    FROM(
            SELECT cat_id,MAX(shop_price) AS max_price
            FROM ecs_goods
            GROUP BY cat_id
    ) temp,ecs_goods
    WHERE temp.cat_id = ecs_goods.cat_id
    AND temp.max_price = ecs_goods.shop_price  </code></pre><p>all 和 any的使用<br>all：所有</p><p>any：任一</p><h3>多列子查询</h3><p>示例：</p><ol><li>查询与allen部门和岗位完全相同的所有雇员(不包含ALLEN本人)</li><li><p>(字段 1， 字段 2 …) = (select 字段 1，字段 2 from …)</p><pre><code>-- 得到ALLEN的部门和岗位
-- 把该语句当作子查询来使用，并使用多列子查询的语法进行匹配
SELECT deptno, job
 FROM emp
 WHERE ename = 'ALLEN'

SELECT * 
 FROM emp
 WHERE (deptno , job) = (
     SELECT deptno, job
     FROM emp
     WHERE ename = 'ALLEN'
 ) AND ename &lt;&gt; 'ALLEN'
-- 请查询 和宋江数学，英语，语文 -- 成绩 完全相同的学生 
SELECT * 
 FROM student 
 WHERE (math, english, chinese) = ( 
     SELECT math, english, chinese 
     FROM student 
     WHERE `name` = '宋江' ) </code></pre><p>练习</p></li><li>查找每个部门工资高于本部平均工资的人</li><li>查找每个部门工资最高的人的详细信息</li><li><p>查找每个部门的信息(部门名，编号，地址)和人员数量</p><pre><code>-- 查找每个部门工资高于本部平均工资的人
SELECT deptno, AVG(sal)
 FROM emp
 GROUP BY deptno

SELECT ename, sal, emp.deptno, temp.avg_sal
 FROM emp, (
     SELECT deptno, AVG(sal) AS avg_sal
     FROM emp
     GROUP BY deptno
 ) temp
 WHERE emp.deptno = temp.deptno
 AND emp.sal &gt; temp.avg_sal
-- 查找每个部门工资最高的人的详细信息
SELECT deptno, MAX(sal) AS max_sal
 FROM emp
 GROUP BY deptno

SELECT ename ,sal,emp.deptno
 FROM emp,(
     SELECT deptno, MAX(sal) AS max_sal
     FROM emp
     GROUP BY deptno
 )temp
 WHERE emp.deptno = temp.deptno
 AND emp.sal = temp.max_sal
-- 查找每个部门的信息(部门名，编号，地址)和人员数量
SELECT COUNT(*), deptno
 FROM emp
 GROUP BY deptno
-- 当多个表的列不重复时，才可以之间写列名
SELECT temp.*, dname, loc
 FROM dept, (
     SELECT COUNT(*) AS nums, deptno
     FROM emp
     GROUP BY deptno
 )temp
 WHERE temp.deptno = dept.deptno
 </code></pre></li></ol>

MySQL概述

什么是数据库

数据表的基本操作

查看数据表

查看表的字段信息

修改数据表

修改字段名

修改字段数据类型

增加字段

删除数据表

增删改查

Insert

插入数据

同时插入多条语句

注意事项：

update

使用细节：

delete

selete

在where子句中经常使用的运算符

使用order by子句排序查询

合计/统计函数

数据表的约束

主键约束

非空约束

默认值约束

唯一性约束

外键约束

数据一致性

删除外键

细节

check约束

mysql 表外连接

左外连接

多表查询

自连接

表子查询

多列子查询

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Java ｜ MySQL 基础篇

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款