ToB企服应用市场:ToB评测及商务社交产业平台

标题: 4.数据库，SQL单表多表的学习 [打印本页]
作者: 郭卫东    时间: 2025-2-16 20:33
标题: 4.数据库，SQL单表多表的学习
一、数据库简介

数据在内存：
优点：读写速度快
缺点：步调竣过后数据丢失
生存到文件：
优点：数据可以永世生存
缺点：
1、频繁的IO操纵，服从不高（IO（输入/输出，Input/Output）操纵）
2、数据的管理非常不方便，需要把所有的数据整体都读取出来才气操纵
数据库：
1、数据永世生存
2、数据管理非常方便
数据库可以被明白为团结了内存和文件存储的优点。具体来说：
同时，数据库通过索引、优化查询等本领，能够在高效处置惩罚大量数据时，淘汰文件存储的服从问题。
数据库是以表为组织单位存储数据的

二、单表

PRIMARY KEY 主键，不能重复，唯一确定一条记录 （unique+not null）
表中的任何列都可以作为主键，只要它满足一下条件：
1、任意两行都不具有类似的主键值
2、每一行都必须具有一个主键值（主键列不允许空置NULL）
3、主键列中的值不允许修改或更新
4、主键值不能重用（假如某行从表中删除，它的主键不能赋给以后的新行）

AUTO_INCREMENT 自动增长
varchar(10) VARCHAR 是 "Variable Character"（可变字符）的缩写。它表示一种 可变长度的字符串数据类型。
char(10)区别：
类似点：都可以最大放10个字符
不同点：char(10)不管输入的是多少都会占10个字符，例如输入名字“张三”只有两个字符，
但是利用char(10)在数据库内里还是占10个字符的空间。
利用varchar(10)最大支持是10个字符，但是现实长度就是输入字符长度，例如输入名字“张三”只有两个字符，
那么在varchar(10)内里就只占两个字符。



数据库中以表为组织单位存储数据。
表类似我们的Java类，每个字段对应类内里的属性。
那么用我们认识的java步调来与关系型数据对比，就会发现以下对应关系。

列出所有的数据库：

  1. -- 列出所有的数据库
  2. SHOW DATABASES;
  3.    
复制代码
database 数据库
创建数据库：

  1. -- 创建数据库
  2. CREATE DATABASE study DEFAULT CHARACTER SET utf8;
复制代码
default
 缺省
 默认
character
 字符
UTF-8（Unicode Transformation Format 8-bit）是一种字符编码方式，用于表示文本数据。它是一种广泛利用的编码方式，可以表示世界上几乎所有语言的字符。
删除数据库：

  1. -- 删除数据库
  2. DROP DATABASE study;
复制代码
drop” (v.) “丢弃”、“丢掉”或“删除”
数据库表的操纵-- 切换数据库：

  1. -- ----------------------------------
  2. -- 数据库表的操作
  3. -- 切换数据库
  4. USE study;
  5. -- 创建表
  6. CREATE TABLE student(
  7.     id INT,
  8.     `name` CHAR(10),
  9.     age INT,
  10.     gender CHAR(1)
  11. );
复制代码
查察所有表：

  2. -- 查看所有表
  3. SHOW TABLES;
  4. -- 查看表的结构
  5. DESC student;
复制代码
DESC 是 description的缩写 译是“描述”或“说明”--查察

更改表的布局 （添加，删除，修改字段，修改表名）：

  2. - 更改表的结构
  3. -- 添加字段
  4. ALTER TABLE student ADD COLUMN address CHAR(10);
  5. -- 删除字段
  6. ALTER TABLE student DROP COLUMN address;
  7. -- 修改表的字段
  8. ALTER TABLE student CHANGE address addr CHAR(20);
  9. -- 修改表的名字
  10. ALTER TABLE student RENAME TO stu;
  11. c
复制代码
ALTER 译是 "改变" 或 "修改"
COLUMN ：
column
 行
 栏
RENAME：重定名
  1. -- 创建表
  2. CREATE TABLE student(
  3.     id INT PRIMARY KEY AUTO_INCREMENT,
  4.    `name` VARCHAR(10),
  5.     age INT,
  6.     gender CHAR(1)
  7. );
  8. -- * 代表查询所有的列
  9. SELECT * FROM student;
  10. -- 插入数据
  11. -- Duplicate entry '1' for key 'PRIMARY'
  12. INSERT INTO student(id,`name`,age,gender) VALUES(1,'wangwu',23,'男');
  13. INSERT INTO student(id,`name`,age,gender) VALUES(3,'赵六',23,'男');
  14. INSERT INTO student VALUES(4,'赵六22',33,'男');
  15. -- 插入部分字段值(必须把前面的字段名都写上)
  16. INSERT INTO student(`name`,age,gender) VALUES('小张11',23,'男');
  17. -- 一次插入多条数据
  18. INSERT INTO student(`name`,age,gender) VALUES('小张77',23,'男'),('小王',22,'男');
  19.   
  20. -- 修改数据
  21. UPDATE student SET age=age+1;
  22. UPDATE student SET age=age+1,name='zhangsan' WHERE id=7;
复制代码
PRIMARY KEY ： primary 主要的
"SELECT" 的翻译是 "选择" 或 "查询
VALUES" 的翻译是 "值" 或 "数值"----》这里应该是动词“输入数值”


删除数据：

  1. -- 删除数据
  2. DELETE FROM student; -- 删除表中所有数据（很少使用，是非常危险）
  3. DELETE FROM student WHERE age=24; -- 所有age是24的数据都被删除了，可能有多条数据都是age=24
  4. DELETE FROM student WHERE id=12; -- 因为id是主键是唯一的，所以根据id删除只能删除唯一的一条数据
  5. DELETE FROM student WHERE id=1 OR id=2;
  6. DELETE FROM student WHERE id IN(1,2);
  7. -- TRUNCATE删除表里面所有数据，自增的id会重新初始化为初始值1
  8. TRUNCATE TABLE student;
复制代码
TRUNCATE 截断

查询数据：

  2. -- 查询数据
  3. -- 显示所有列(字段)数据
  4. -- 学习时候可以写*，但是在企业开发中需要什么字段就写什么字段，检索不需要的列会降低检索速度和应用程序的性能
  5. SELECT * FROM student;
  6. SELECT id,`name`,age,gender FROM student;
  7. -- 查询指定列
  8. SELECT `name`,age FROM student;
  9. -- 查询时候添加常量列，通过as可以起别名
  10. SELECT id,`name`,age AS '年龄','java2403' AS '班级' FROM student;
  11. -- 查询时候合并列，字段可以当成java里面的变量来运算
  12. SELECT id,`name`,(php+java) AS '总成绩' FROM student;
  13. -- 查询时候去掉重复的记录
  14. SELECT DISTINCT address FROM student;
复制代码
  --查询数据：
  -- 显示所有列(字段)数据
  -- ‘*’ 是通配符，表示“所有列”。
  -- 学习时间可以写*，但是在企业开发中需要什么字段就写什么字段，检索不需要的列会降低检索速度和应用步调的性能
  
DISTINCT 不同的（不重复的）
AS ---用来重定名

   -- 计算字段（列）：
  -- 计算字段并不现实存在于数据库表中，计算字段是运行时在SELECT语句内创建的。
  -- 只有数据库知道SELECT语句中哪些列是现实的列表，哪些列是计算字段。
  -- 从客户端(如应用步调)来看，计算字段的数据与其他列的数据的返回方式类似。
  
条件查询 （where）：

用于指定查询的条件，用来过滤出满足特定条件的记录。
可以团结各种操纵符，如 =, >, ，来实现更复杂的条件查询。
  1. - 条件查询 where ,数据库表一般包含大量的数据，很少需要检索表中所有行
  2. SELECT * FROM student WHERE `name`='小王';
  3.   
  4. -- 逻辑条件: and（同时成立） or(只要有一个成立)
  5. SELECT * FROM student WHERE `name`='小王' AND address='青岛';
  6. SELECT * FROM student WHERE `name`='小王' OR address='北京';
  7.   
  8. -- 比较运算: >  <  >=  <=  != 不等于也可以写成：<>
  9. SELECT * FROM student WHERE java>=70 AND java<=80;
  10. -- between and (等价于>= and <=)
  11. SELECT * FROM student WHERE java BETWEEN 70 AND 80;
  12. -- 查询地址不是青岛的学生信息 <>
  13. SELECT * FROM student WHERE address != '青岛';
  15. -- 空值NULL：无值(no value)，它与字段包含0、空字符串或紧紧包含空格不同
  16. SELECT * FROM student WHERE address IS NULL; -- IS NOT NULL
复制代码
聚合查询：

  2. -- 聚合查询
  3. -- 聚合查询函数：sum(),avg(),max(),min(),count()
  4. -- 统计学生php的总成绩（sum求和）
  5. SELECT SUM(php) AS 'php总成绩' FROM student;
  6. -- 统计学生php的平均值
  7. SELECT AVG(php) AS 'php平均值' FROM student;
  8. -- 统计学生php的最大值
  9. SELECT MAX(php) AS 'php最大值' FROM student;
  10. -- 统计学生表里面一共有多少学生
  11. SELECT COUNT(*) AS '总人数' FROM student;
  12. SELECT COUNT(id) AS '总人数' FROM student;
  13. SELECT COUNT(address) AS '总人数' FROM student;
复制代码
  -- 注意：假如指定列明，则COUNT会忽略指定列的值为NULL的行，用*则不忽略。
  
查询排序 desc:descending asc:ascending:

-- 语法：order by 字段 asc/desc 默认是asc升序，可以不写
  1. -- 查询排序 desc:descending asc:ascending
  2. -- 语法：order by 字段  asc/desc  默认是asc升序，可以不写
  3. SELECT * FROM student ORDER BY php;
  4. SELECT * FROM student ORDER BY php ASC;
  5. SELECT * FROM student ORDER BY php DESC;
  6. -- 多个条件排序
  7. -- 需求：先按照php降序，java升序(整体是按照php降序，如果php相同的数据再按照java标准排序)
  8. SELECT * FROM student ORDER BY php DESC, java ASC;
  9. -- order by要放在sql语句的最后
复制代码
分组查询(group by):

  2. -- 分组查询(group by)
  3. -- 需求：查询男女分别有多少人
  4. -- 性别  人数
  5. -- 男    3
  6. -- 女    2
  7. SELECT gender AS '性别',COUNT(*) AS '人数'
  8. FROM student GROUP BY gender;
  9. -- 分组之后的条件筛选用HAVING
  10. SELECT gender AS '性别',COUNT(*) AS '人数'
  11. FROM student GROUP BY gender HAVING COUNT(*)>1;
复制代码

总结：
1、假如分组中包罗具有NULL值的行，则NULL将作为一个分组返回。
2、GROUP BY字句必须出如今WHERE字句之后，ORDER BY字句之前。
3、WHERE过滤行（行级过滤），而HAVING过滤分组（组级过滤），WHERE所有技能和选项都实用于HAVING，语法利用一样。

   字段属性设置:
  1、not null： 不为空，表示该字段不能放“null”这个值。不写，则默认是可以为空
  2、auto_increment: 设定int类型字段的值可以“自增长”，即其值无需“写入”，而会自动获得并增加
  此属性必须随同 primary key 或 unique key 一起利用。primary key = unique key + not null
  3、[primary] key： 设定为主键。是唯一键“加强”：不能重复而且不能利用null，而且可以作为确定任意一行数据的“关键值”，最常见的类似：where id= 8; 或 where user_name = ‘zhangsan’;
  通常，每个表都应该有个主键，而且大多数表，喜欢利用一个id并自增长类型作为主键。
  但：一个表只能设定一个主键。
  4、unique [key] : 设定为唯一键：表示该字段的所有行的值不可以重复（唯一性）。
  Duplicate entry 'zhangsan' for key 'name'
  5、default ‘默认值’： 设定一个字段在没有插入数据的时间自动利用的值。
  6、comment ‘字段表明’
   例：
  1. CREATE TABLE teacher(
  2.     id INT PRIMARY KEY AUTO_INCREMENT,
  3.    `name` CHAR(10) NOT NULL,
  4.     age INT COMMENT '年龄',
  5.     address CHAR(10) DEFAULT '中国', -- 插入数据时候如果不赋值，默认值是"中国"
  6.     UNIQUE KEY(`name`) -- 唯一键，代表这个字段不能重复
  7. );
  9. -- Duplicate entry 'zhangsan' for key 'name'
  10. INSERT INTO teacher(`name`) VALUES('zhangsan');
复制代码
三、多表

例如：学生表、班级表、课程表、班级课程表
即：一对一、一对多、多对多


关系型数据库：MySql、SqlServer、Oracle
类似的数据出现多次绝不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一个数据一个表，各表通过某些共同的值互相连接，所以才叫关系数据库。
将数据存储到多个表能更有效的存储，更方便的处置惩罚，但这些利益是有代价的：假如数据存储在多个表中，怎么用一条SELECT语句就检索出数据呢？答案是利用：子查询、联结

非关系型数据库：Redis ，MongoDB 速度非常快（日志信息）
可以明白为一个大的Map布局
根据表与表之间的关系布局完成代码：

多对多

  1. -- 多对多
  2. -- 班级表
  3. CREATE TABLE banji(
  4.      id INT PRIMARY KEY AUTO_INCREMENT,
  5.     `name` VARCHAR(10) NOT NULL
  6. );
  7. INSERT INTO banji(`name`) VALUES('java1807'),('java1812');
  9. SELECT * FROM banji;
  11. -- 学生表 reference:参考，引用
  12. CREATE TABLE student(
  13.     id INT PRIMARY KEY AUTO_INCREMENT,
  14.     `name` VARCHAR(10) NOT NULL,
  15.     age INT,
  16.     gender CHAR(1),
  17.     banji_id INT,
  18.     FOREIGN KEY(banji_id) REFERENCES banji(id)
  19. );
  20. INSERT INTO student(`name`,age,gender,banji_id)
  21. VALUES('张三',20,'男',1),('李四',21,'男',2),('王五',20,'女',1);
  22. -- Cannot add or update a child row: a foreign key constraint fails (`java1812`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`banji_id`) REFERENCES `banji` (`id`))
  23. INSERT INTO student(`name`,age,gender,banji_id)
  24. VALUES('张三',20,'男',3);
  26. SELECT * FROM student;
  28. -- 课程表
  29. CREATE TABLE course(
  30.     id INT PRIMARY KEY AUTO_INCREMENT,
  31.     `name` VARCHAR(10) NOT NULL,
  32.     credit INT COMMENT '学分'
  33. );
  34. INSERT INTO course(`name`,credit) VALUES('Java',5),('UI',4),('H5',4);
  36. SELECT * FROM course;
  38. -- 班级课程表
  39. CREATE TABLE banji_course(
  40.     -- id int PRIMARY KEY AUTO_INCREMENT,
  41.     banji_id INT,
  42.     course_id INT,
  43.     PRIMARY KEY(banji_id,course_id), -- 联合主键
  44.     FOREIGN KEY(banji_id) REFERENCES banji(id), -- banji_id既是联合主键又是外键
  45.     FOREIGN KEY(course_id) REFERENCES course(id) -- course_id既是联合主键又是外键
  46. );
  47. INSERT INTO banji_course(banji_id,course_id) VALUES(1,1),(1,3),(2,1),(2,2),(2,3);
  49. SELECT * FROM banji_course;
复制代码
子查询

查询一个值时用‘=’，二查询多个值时用‘IN’.
  1. -- 子查询：嵌套查询，一个查询语句结果是另一个查询语句的条件
  2. -- 查询班级是Java1812班所有学生信息
  3. SELECT * FROM student WHERE banji_id=2;
  4. SELECT id FROM banji WHERE `name`='Java1812';
  5. SELECT * FROM student WHERE banji_id=(SELECT id FROM banji WHERE `name`='Java1812');
  7. -- 班级是Java1807班或者Java1812班所有学生信息
  8. SELECT * FROM student WHERE banji_id=1 OR banji_id=2;
  9. SELECT * FROM student WHERE banji_id IN(1,2);
  10. SELECT id FROM banji WHERE `name`='Java1807' OR `name`='Java1812'; -- 1,2
  11. SELECT * FROM student WHERE banji_id IN(SELECT id FROM banji WHERE `name`='Java1807' OR `name`='Java1812');
  13. -- 计算字段使用子查询：班级id 班级名字 班级人数
  14. -- 执行这条查询遵循下面的步骤：
  15. -- 1、从banji表检索班级列表
  16. -- 2、对检索出的每个banji，统计其在student表中的数量
  17. SELECT id,`name`,(SELECT COUNT(*) FROM student WHERE student.banji_id=banji.id) AS total_count
  18. FROM banji
  19. ORDER BY `name`;
复制代码
  1. -- 计算字段使用子查询：班级id 班级名字 班级人数
  2. -- 执行这条查询遵循下面的步骤：
  3. -- 1、从banji表检索班级列表
  4. -- 2、对检索出的每个banji，统计其在student表中的数量
  5. SELECT id,`name`,(SELECT COUNT(*) FROM student WHERE student.banji_id=banji.id) AS total_count
  6. FROM banji
  7. ORDER BY `name`;
复制代码
ORDER BY 排序依据
   这条查询可以利用两次 FROM，是因为：
    第一个 FROM 是查询的主表 banji，也就是说，查询是从 banji 表中获取每个班级的 id 和 name。
    在 SELECT COUNT(*) 的部分，嵌套的子查询 (SELECT COUNT(*) FROM student2 WHERE student2.banji_id = banji.id) 也是一个独立的查询。子查询在这里用于计算与当前班级（banji.id）相关的学生数目。这个子查询独立实行，但由于它是嵌套在主查询中，所以它会被实行一次，计算每个班级的学生数目。
  关键点：
  
  
总结：
1、"="：要求子查询只有一个效果。 "in"：子查询可以有多个效果。
2、子查询的SELECT语句只能查询单个列，企图检索多个列将返回错误。
3、能嵌套的子查询的数目没有限制，不外在现实利用时由于性能的限制，不能嵌套太多的子查询。
子查询也可以利用下面的联结来实现

四、等值连接

从左表中取出每一条记录，去右表中与所有的记录举行匹配：匹配必须是某个条件在左表中与右表中类似最终才会保留效果，否则不保留

笛卡尔积：

由没有联结条件的表关系返回的效果为笛卡尔积。检索出的行的数目将时第一个表中的行数乘以第二个表中的行数。通俗的说就是查询所得的效果行数是两张表行数的乘积。
返回笛卡尔积的联结，也称为叉联结cross join。

！！！与所有的值举行连接 ，但是会出现很多没有意义的连接
等值连接:


注意：联结查询非常消耗资源，因此应该注意，不要联结不须要的表。联结的表越多，性能降落越锋利。
五、内联结

内连接有两种写法，一种是inner join，另一种是join，这两种写法都是一样的，可以明白为join是inner join的缩写。
等值连接和内连接的效果一样，但是开发中发起利用内连接


例：
-- 学生姓名 班级名称 课程名称 学分
分析：学生信息在student表中，班级名称在banji表中 课程信息在course表中，学分也在course表中
-- 而student与banji之间通过外键s.banji_id=b.id 相关联，而banji表与course表之间需要添加一个中心表关联

总结：多表查询主要是注意下面两点
1、整个查询涉及到几张表，涉及到几张表就连接这几张表。
2、假如涉及到这几张表的关系搞不清楚，画一下ER图，弄清楚表和表之间的关系（就是根据外键创建的关系）

  1. -- 班级id 班级名字 班级人数
  2. SELECT b.id,b.`name`,COUNT(*)
  3. FROM student AS s INNER JOIN banji AS b
  5. ON s.banji_id=b.id
  7. GROUP BY b.id;
复制代码

六、inner join on、left join on、right join on区别

inner join on 只有左右两个表有关联的才查询出来
left join on 左表中都显示出来，右表没有显示空
right join on 右表都显示，左表没有显示空
左连接，也成为左外连接：从左表那边返回所有的行，纵然在右表中没有匹配的行
右连接，也成为右外连接


SELECT * FROM student as s LEFT JOIN banji as b on s.banji_id=b.id;
七、含糊查找

语法情势：字段 like '要查找字符'
说明：
1、like含糊查找用于对字符类型的字段举行字符匹配查找。
2、要查找的字符中，有两个特殊含义的字符：% , _:
2.1: %含义是：代表0或多个的任意字符
2.2: _含义是：代表1个任意字符
3、语法：like '%关键字%'
  1. SELECT * FROM student WHERE `name` LIKE '张%'; -- 以张开头
  2. SELECT * FROM student WHERE `name` LIKE '张_'; -- 以张开头，而且名字是两个字
  3. SELECT * FROM student WHERE `name` LIKE '%张%'; -- 名字里面只要有张就可以
复制代码
  注意：
  SQL的通配符很有效，但这种功能是有代价的，即通配符搜刮要消耗更长的处置惩罚时间，利用通配符的本领：
  1、不要过分利用通配符。假如其他操纵符能达到类似的目的，应该利用其他操纵符。
  2、在确实需要利用通配符时，也尽量不要把它们用在搜刮模式的开始处 '%张'。把通配符置于开始处，搜刮起来是最慢的。
   select where常用运算符:


NOT可以否定IN、BETWEEN、EXISTS

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4