数据库的相关知识

数据库的相关知识

1.数据库能够做什么？

存储大量数据，方便检索和访问保持数据信息的一致、完整共享和安全通过组合分析，产生新的有用信息

2.数据库作用？

存储数据、检索数据、生成新的数据

3.数据库要求？

统一、安全、性能等

4.概念？

数据库就是“数据”的“仓库”数据库中包含表、关系以及操作对象数据存放在表中

5.操作数据库用户

工具操作：Navicat for MySQL

如何创建用户？

点击用户，点击新建用户，常规输入完，点击保存就可以了。

如何连接新建？

创建用户完之后，点击连接，输入新建的用户名和密码

设置权限：

找到新建，点击编辑用户，点击服务器权限，全部授予，点击SQL预览，复制

点击查询，新建查询，粘贴到上面，把权限改为ALL

重新连接一下就可以了

创建数据库：

1.打开连接，点击数据库，右键点击后新建数据库，把字符集改为

点击确定就新建好了

2.打开新建查询，输入CREATE DATABASE +名称；点击连接刷新

3.使用命令列介面，输入CREATE DATABASE +名称；点击连接刷新

查看数据库：

打开新建查询，输入SHOW DATABASES;

使用命令列介面，点击连接，右键打开

选择数据库：

1.双击点数据库，打开

2.使用命令列介面，输入USE +名称

如何删除数据库？

1.直接右键点击删除数据库

2.使用命令列介面，输入 drop database+名称

刷新连接，显示删除

如何创建

数据完整性就是要求数据库中的数据具有准确性。准确性是通过数据库表的设计和约束来实现的。

种类型的约束保证完整性：

实体完整性：约束方法：唯一约束、主键约束、标识列

域完整性：约束方法：限制数据类型、检查约束、外键约束、默认值、非空约束

引用完整性：约束方法：外键约束

自定义完整性：约束方法：规则、存储过程、触发器

SQL中的运算符

算术运算符：

SELECT 3+2;
SELECT 50-3;
SELECT 32*2;
SELECT 2/10;
SELECT 10%2;

赋值运算符:

=:把一个数或变量或表达式赋值给另一变量

逻辑运算符:

AND:当且仅当两个布尔表达式都为true时，返回TRUE。

OR:当且仅当两个布尔表达式都为false，返回FALSE

NOT：布尔表达式的值取反

比较运算符：

-- 比较运算符
SELECT 3>2;
SELECT 3<5;
SELECT 4>=2;
SELECT 3<=5;
SELECT 3!=4;
SELECT 4<>5;

数值类型

INT：标准整数

VARCHAR[(M)]：可变字符串

CHAR[(M)]：定长字符串

创建表

-- 创建表
-- create table 表名(
-- 字段   类型,
-- 字段   类型,
-- 字段   类型,
-- )

多字段使用逗号分隔

保留字用撇号括起来

单行注释：#…

多行注释：/……/

查看表

运行创建的语法，在所写的数据库表刷新

删除表

语法： DROP TABLE [IF EXISTS] 表名;

修改表名

语法：ALTER TABLE 旧表名 RENAME [TO] 新表名;

添加字段

语法：ALTER TABLE 表名 ADD 字段名 数据类型 [属性];

修改字段

语法：ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

删除字段

语法：ALTER TABLE 表名 DROP 字段名

not null 不能为空

数据库约束

ZEROFILL 补零

DEFAULT 默认值

注释：COMMENT

auto_increment 自增

PRIMARY KEY 主键

主键 1.唯一，2.非空 3.int类型可以自增

外键

概念：是另一张表的主键，用来和其他表建立关系用的

作用：保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值或使用空值。

语法

FOREIGN KEY( 子表名) REFERENCES 主表 (主表名)

添加数据

INSERT INTO 表名(字段,字段…) VALUES(值,值…);

字段和值必须一一对应 column count doesn’t

有外键时,必须符合条件

主键不能重复

INSERT INTO student(student_no,login_pwd,student_name,
sex,grade_id,phone,address,birthday,email)
VALUES('bq0120','123456','张铮','男',1,'123635343',
'河北','2024-10-14','1234@qq.com');

字段不需要全部都写,必须要写的字段(没有自增的主键,非空的字段,外键)

INSERT INTO student(studen_id,gradet_id)
VALUES('bq0120',9);

不写字段时,必须按顺序添加所有数据

INSERT INTO student
VALUES('bq0020','123456','张三','男',1,'1234566','河北','2024-10-14','1234@qq.com');

有自增主键的表,可以忽略主键,也可以使用默认值

INSERT INTO grade(grade_name) VALUES('鸿蒙1班');

有默认值的字段可以不写内容

有默认值的字段可以代替

多行添加

INSERT INTO subjectes(subject_id,subject_name,subject_hour)
VALUES(null,'as',120),(null,'按时',120),(null,'若凡',120),
(null,'恶妇',120),(null,'合格',120),(null,'方式',120);

主键语法允许修改,但是项目中一般不允许修改主键

UPDATE student SET login_pwd='zhangsan'
 where student_name='张三';

删除

DELETE FROM result where student_result<60;

清空表

TRUNCATE TABLE result 

事务

什么是事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作多个操作作为一个整体向系统提交，要么都执行、要么都不执行 事务是一个不可分割的工作逻辑单元

事务必须具备以下四个属性，简称ACID 属性：

原子性：事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行

一致性：当事务完成时，数据必须处于一致状态

隔离性：并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务

持久性：事务完成后,它对数据库的修改被永久保持

如何创建事务

可以在命令行语句里

BEGIN;开始事务（指定事务从此处开始，后续的SQL语句都是一个整体）

UPDATE bank SET morey=morey-500 WHERE name=‘张三’;
UPDATE bank SET morey=morey+500 WHERE name=‘李四’;

COMMIT;提交事务，事务结果

例：如果使用事务解决转账问题

ROLLBACK;回滚事务，数据恢复到原始状态

BEGIN;
UPDATE bank SET morey=morey-500 WHERE name='张三';
DELETE FROM bank WHERE id=1;
UPDATE bank SET morey=morey+500 WHERE name='李四';
ROLLBACK;

自动关闭和开启事务

默认情况下，每条单独的SQL语句视为一个事务关闭默认提交状态后，可手动开启、关闭事务

关闭/开启自动提交状态 SET autocommit = 0|1;

值为0：关闭自动提交

值为1：开启自动提交

# 关闭自动提交
SET autocommit=0;
BEGIN;
UPDATE bank SET morey=morey-500 WHERE name='张三';
DELETE FROM bank WHERE id=1;
UPDATE bank SET morey=morey+500 WHERE name='李四';
ROLLBACK;
-- 打开自动提交
set autocommit=1;

SQL查询

语法

SELECT <列名>

FROM <表名>

WHERE <查询条件表达式>

查询全部的行和列

*代表所有列 ，一般在项目中不允许使用，按需搜索

SELECT * FRO

M Students

where 查询的条件

SELECT student_no,studen_name,sex FROM student3
WHERE sex='男';

列别名

使用AS命名列

SELECT student_no as 学号,studen_name as 姓名,sex as 性别
 FROM student3
WHERE sex='男';

查询null null是从来没有添加过数据，或者添加null

SELECT * from student3 WHERE sex is NULL;

被清空的列 使用’’

SELECT * FROM student3 WHERE sex='';
SELECT * FROM student3 WHERE sex='' OR sex IS NULL;

Null 和 ‘ ’的区别

null是空值，表示未被赋值

‘ ’是空字符串，表示非空，只不过值是一个非空字符串而已

分页 (开始，数量)

SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5,5;

limit 要放在语句的最后

SELECT * FROM student3 WHERE sex='男' LIMIT 0,5;

模糊查询

（项目中尽量不要使用模糊查询） LIKE

SELECT * FROM StudentsWHERE 姓名 like ‘张%’

通配符：

_:一个字符

%：任意长度的字符串 %:0或多个 不限长度的字符

一类字符，代替一个或多个真正的字符 与LIKE关键字一起使用

模糊查询:BETWEEN

查询某一列中内容与所列出的内容列表匹配的记录

SELECT * FROM student3 WHERE studen_name in('张铮','张三','李四');

BETWEEN 什么到什么之间

-- 60到80之间
SELECT * FROM result WHERE studen_result BETWEEN 60 and 80;

排序 默认是升序 ASC

降序 DESC

SELECT * FROM student3 ORDER BY born_date ASC; 
# 根据多个字段去排序
SELECT * FROM student3 ORDER BY born_date DESC ,sex;
# 获取成绩前2名的信息
SELECT * FROM result ORDER BY studen_result DESC LIMIT 2;

SQL查询2

字符串函数：用于控制返回给用户的字符串

拼接字符串 CONCAT（）连接成字符串

SELECT CONCAT('糯米','阿萨德','史蒂夫');

字符长度 LENGTH（） 返回传递给它的字符串长度

SELECT LENGTH('asdfghjhgfdsa');

转大写：UPPER（） 把传递给它的字符串转为大写

转小写：LOWER（）把传递给它的字符串转为小写

-- 转大写
SELECT UPPER('asdf');
-- 转小写
SELECT LOWER('ASD');

去除左边空格 LTRIM（）清除字符左边空格

去除右边的空格 RTRIM（）清除字符右边的空格

-- 去除左边空格
SELECT LTRIM('  asdf  ');
-- 去除右边的空格·
SELECT RTRIM('  asdf  ');

替换 REPLACE（）替换一个字符串中的字符

有条件的替换：INSERT（）将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果

-- 替换 ('替换前的'，'替换的','替换的什么')
SELECT REPLACE('hello word','word','世界');
-- 有条件的替换
SELECT INSERT('hello word',6,1,'世界');

日期函数：用于操作日期值

系统日期 CURDATE()

日期时间 now()

时间 CURTIME()

-- 系统日期
SELECT CURDATE();
-- 日期时间
SELECT now();
-- 时间
SELECT CURTIME();

添加日期

DATE_ADD（）

-- 添加1个月
SELECT DATE_ADD(now(),INTERVAL 1 MONTH);

减少日期

DATE_SUB（）

-- 减少5天
SELECT DATE_SUB(NOW(),INTERVAL 5 day);

日期的格式化 Y大写整年 y小写两位数的年

-- 年月日时分秒
SELECT DATE_FORMAT(now(),'%Y年%m月%d日%h点%i分%s秒');

数学函数：用于对数值进行代数运算

随机数 RAND() 返回从 0 到 1 之间的随机 float 值

绝对值 ABS() 取数值表达式的绝对值

最小整数 CEILING() 取大于或等于指定数值、表达式的最小整数

最大整数 FLOOR() 取小于或等于指定表达式的最大整数

表达式的幂值 POWER() 取数值表达式的幂值

四舍五入 ROUND() 将数值表达式四舍五入为指定精度

对于正数+1 对于负数-1，对于0返回0 SIGN()

平方根 SQRT() 取浮点表达式的平方根

-- 0到1之间的随机数
SELECT RAND();
-- 绝对值
SELECT ABS(12);
-- 最小整数
SELECT CEILING(23.4);
-- 最大整数
SELECT FLOOR(23.4);
-- 表达式的幂值
SELECT POWER(5,2);
-- 四舍五入的精度
SELECT ROUND(234.2444);
-- 对于正数+1 对于负数-1，对于0返回0
SELECT SIGN(-23);
-- 平方根
SELECT SQRT(10);

聚合函数

sum,avg,max,min,count

总分：sum,平均分 avg最大值 max, 最小值 min,计数 count

-- 总分
SELECT SUM(studen_result) FROM result;
SELECT student_no, SUM(studen_result) FROM result
where student_no='1001';
-- 平均分 avg
select avg(studen_result) from result;

-- 最大值 max 最小值 min
select max(studen_result),min(studen_result) from result;
-- 计数 count
select count(*) from student3;
select count(*) from student3 where gradeld=1;

分组查询

分组查询语句:SELECT …… FROM <表名> WHERE ……GROUP BY ……

select gradeld,count(*) from student3 group by gradeld;

查询每一个分组

SELECT gradeld,sex,COUNT(*) FROM student3 GROUP BY gradeld,sex
order by gradeld;

分组筛选

语法：SELECT …… FROM <表名>WHERE ……GROUP BY ……HAVING……

#我的男生学生的总人数大于15的班级
SELECT gradeld,COUNT(*),sex FROM student3 GROUP BY gradeld,sex
HAVING COUNT(*)>1 and sex='男';

SQL查询3

王五的出生日期

旧方法：

SELECT born_date FROM student3 WHERE studen_name='王五';
-- 2. 根据日期查询剩余的学生信息
SELECT * FROM student3 WHERE born_date>'2000-02-14';

新方法：

子查询
比较运算符时，查询结果不能大于1

查询的字段只能有一个

SELECT * FROM student3 WHERE born_date>(
SELECT born_date FROM student3 WHERE studen_name='王五'
);

-- 查找和王月同班的学生的学号和姓名
-- 1.王月的班级
SELECT grade_id FROM student WHERE student_name='嘎嘎嘎';
-- 2.根据班级编号查询学生
SELECT * FROM student WHERE grade_id=3
-- 3.合并 
SELECT * FROM student WHERE grade_id=(
SELECT grade_id FROM student WHERE student_name='嘎嘎嘎'
) AND student_name !='嘎嘎嘎';

删除查询

-- .查询和张三同班的年龄最大的学生信息
SELECT * FROM student3 WHERE gradeld=(
SELECT gradeld FROM student3 WHERE studen_name='张三')
ORDER BY born_date DESC  LIMIT 1;

联合查询(多表联查)

-- .查询张三班级信息
SELECT * FROM student3 s,grades g WHERE s.gradeld=g.gradeld;
SELECT s.student_no,s.studen_name,g.grade_name FROM student3 s,grades g WHERE s.gradeld=g.gradeld
and s.studen_name='张三';

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型同时，每条 SELECT 语句中的列的顺序必须相同当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行

-- 消除重复行   消除两个表重复数据
SELECT studen_name,phone FROM student3 UNION 
SELECT studen_name,phone FROM phones

通过其他表的部分内容创建表

CREATE TABLE phones(
SELECT studen_name,phone FROM student3
);

in

-- 大于张三所有成绩的学生
SELECT * FROM student3 WHERE student_no in(
 SELECT student_no FROM result WHERE studen_result>all(
   SELECT studen_result FROM result WHERE student_no=(
    SELECT student_no FROM student3 WHERE studen_name='王五'
   )
 )
);

SQL查询4

常用的多表连接查询

内连接 等值连接

INNER JOIN

内连接使用比较运算符根据每个表的通用列中的值匹配两个表中的行

SELECT* FROM student3 s INNER JOIN grades g 
on s.gradeld=g.gradeld;

三表内连接

-- 查询学生学号，姓名，考试科目名称及成绩
SELECT s.student_no,s.studen_name,su.subject_name,r.studen_result 
FROM result r
INNER JOIN subjects su on r.subjectld=su.subject_id
INNER JOIN student3 s on s.student_no=r.student_no;

外连接

左外连接 LEFT JOIN

两种情况：

1、匹配，返回到结果集

2、无匹配，NULL值返回到结果集

主表数据逐条匹配子表中的数据

-- 查询所有学生的考试成绩
SELECT * FROM student3 s LEFT JOIN result r on
s.student_no= r.student_no;

右外连接 RIGHT JOIN

右外连接的原理与左外连接相同右表逐条去匹配记录；否则NULL填充

-- 不建议用，和左外连接没区别
SELECT * FROM result r RIGHT JOIN student3 s ON
s.student_no=r.student_no;

自连接

通过起别名的方式把一张表 当做多个表使用

-- 查找比张三年龄大的学生
SELECT s2.* FROM student3 s1,student3 s2
WHERE s1.studen_name='张三' and s1.born_date>s2.born_date;

自然连接

自然连接通过MySql自己的判断完成连接过程，不需要指定连接条件。MySql会使用表内的，相同的字段，作为连接条件

自然连接分为内外之分。

内：natural join

外：左外natural left join、右外natural right join

-- 自然内连接
SELECT s.studen_name,r.studen_result FROM student3 s 
NATURAL JOIN result r;

-- 自然外连接
SELECT s.studen_name,r.studen_result FROM student3 s 
NATURAL left JOIN result r;