MySQL的基础

MySQL是一种开源的关系型数据库管理系统（RDBMS），它是目前最常用和广泛应用的数据库之一。MySQL以其高性能、稳定可靠以及丰富的功能而受到广泛欢迎，被广泛用于Web应用程序的开发。数据库的存储，根据存储结构的不同，分为关系型数据库和非关系型数据库。关系型数据库：基本单位是表，表与表之间存在关联关系。MySQL是一种关系型数据库管理系统，它基于关系模型来组织和管理数据。关系模型使用表（Tab

文章共5,772字 · 阅读需要大约20分钟

一键AI生成摘要，助你高效阅读

问答

小菜鸟的进阶之路

1303人浏览 · 2023-07-13 15:13:48

小菜鸟的进阶之路 · 2023-07-13 15:13:48 发布

3.2在此数据库当中创建表

4.1 数据库设计规范

一.MySQL的基础概述

1.1基本概述

MySQL是一种开源的关系型数据库管理系统（RDBMS），它是目前最常用和广泛应用的数据库之一。MySQL以其高性能、稳定可靠以及丰富的功能而受到广泛欢迎，被广泛用于Web应用程序的开发。

数据库的存储，根据存储结构的不同，分为关系型数据库和非关系型数据库。

关系型数据库：基本单位是表，表与表之间存在关联关系。MySQL是一种关系型数据库管理系统，它基于关系模型来组织和管理数据。关系模型使用表（Table）来表示数据，每个表都由行（Row）和列（Column）组成，其中行表示记录，列表示字段。

非关系型数据库是进行数据临时存储的。

MySql软件是一种开放源码软件,你可以修改源码来开发自己的 Mysql 系统。

MySql数据库服务器具有快速、可靠和易于使用的特点。

MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言。

1.2SQL语句

MySQL使用结构化查询语言（SQL）进行数据库操作。SQL是一种标准化的数据编程语言，用于定义和操作关系型数据库中的数据结构、数据操作和数据查询等。

SQL语句的优点：

结构化查询语言（SQL）易于学习和使用，相对简单直观。
SQL语句能够方便地进行数据的检索、更新、删除等操作。
SQL语句可以在不同的数据库系统之间进行通用性的操作，具有较高的跨平台兼容性。
SQL语句可以通过索引提高数据库的性能，加快查询速度。
SQL语句的标准化使得它们易于阅读、维护和优化。

SQL语句的缺点：

随着数据量的增加，复杂的SQL语句查询性能可能下降，难以满足高并发需求。
SQL语句可能出现安全隐患，如未经过滤的用户输入可能导致SQL注入攻击。
SQL语句不支持动态结构的数据处理，限制了其在某些场景下的灵活性。
SQL语句的编写需要一定的专业知识和经验，对初学者来说可能存在一定的学习门槛。
SQL语句在处理大数据量或复杂数据关系时，效率可能不及一些分布式计算工具或NoSQL数据库。

DDL语言：创建和修改数据和表结构的语言（数据）结构定义语言。

1.3MySQL的特性

MySQL具有许多强大的特性和功能，包括事务支持、ACID（原子性、一致性、隔离性和持久性）特性、索引优化、存储过程和触发器、数据备份和恢复等。此外，MySQL还支持多种存储引擎，如InnoDB、MyISAM和Memory等，可根据需求选择合适的存储引擎来优化数据库性能。

二.MySQL的语句

2.1查询语句：

SELECT：从一个或多个表中获取数据。
FROM：指定要查询的表名。
WHERE：设置条件，筛选满足条件的行。
ORDER BY：按指定的列对结果进行排序。
LIMIT：限制结果集的数量。

2.2插入语句：

INSERT INTO：将数据插入到表中。
VALUES：为每个插入的列指定对应的值。
SET：为每个插入的列指定对应的值，可用于批量插入。

2.3更新语句：

UPDATE：更新表中的数据。
SET：为要更新的列指定新的值。
WHERE：设置条件，仅更新满足条件的行。

2.4删除语句：

DELETE FROM：从表中删除数据。
WHERE：设置条件，仅删除满足条件的行。

2.5连接查询：

INNER JOIN：根据两个表之间的相同值连接两个表。
LEFT JOIN：返回左表（第一个表）中所有记录和右表（第二个表）中匹配的记录。
RIGHT JOIN：返回右表（第二个表）中所有记录和左表（第一个表）中匹配的记录。

三.MySQL的运用

3.1创建一个数据库

CREATE DATABASE IF NOT EXISTS studentdb  -- 创建一个数据库，如果有这个名字的数据库，就不会在创建

3.2在此数据库当中创建表

CREATE TABLE student( 
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
  NAME VARCHAR(6) NOT NULL COMMENT '姓名',
  gender VARCHAR(2) NOT NULL COMMENT '性别', 
  age VARCHAR(3) COMMENT '年龄', 
  birthday DATE COMMENT '生日',
  phone CHAR(11) UNIQUE COMMENT '电话', 
  hight DOUBLE(3,2) COMMENT '身高', 
  location VARCHAR(20) COMMENT '地址', 
  registrationtime DATETIME COMMENT '登记时间'
  );
-- comment 是用于添加注释的
-- primary key 是用于给表设置主键的
-- auto_increment 是用于让此列自然增长的

3.3向表中添加数据

插入语句四种方式：

（1）insert into 表名（列1名，列2名····）value（‘ ’，‘ ’，‘ ’·····）

这种添加的方式一次只能添加一组数据。

（2）insert into set 列1名 = ‘ ’，列2名 = ‘ ’·····

这种添加方式一次也只能添加一组数据，但是需要一一对应。

（3）insert into 表名（列1名，列2名····）

values（‘ ’，‘ ’，‘ ’·····），

（‘ ’，‘ ’，‘ ’·····）

这种添加方式一次可以添加多组数据。

（4）insert into 被插入的列表名（列1名，列2名·····）select 列1名，列2名····from 表名

具体案例代码如下：

  -- 添加信息的方式一
  INSERT INTO student(NAME,gender,phone,registrationtime)
                VALUE('小四','男',"11122223333",NOW())
  -- 添加信息的方式二
  INSERT INTO student SET NAME = 'tom',gender = '男',phone = '11155559999'
  -- 添加信息的方式三
  INSERT INTO student(NAME,gender,phone,registrationtime)
                VALUES('小五','男',"11122223733",NOW()),
                ('小liu','男',"11122283733",NOW())
  -- 添加信息的方式四
  INSERT INTO stu(NAME,gender,phone,registrationtime) 
  SELECT NAME,gender,phone,registrationtime FROM student

3.4修改表

（1）修改数据

update 表名 set 列名 = ‘ ’ ，····· where 索引

UPDATE student SET NAME = '赵子武' WHERE id = 5

（2）删除语句

delete from 表名 where 索引

delete from student where id= 5

（3）删除表

drop table student

drop table student

（4）修改表名

rename table 旧表名 to 新表名

rename table stu to student

（5）复制表的结构

create table stu（新表）like 被复制的表

create table stu like student

（6）给表中在添加列

alter table 表名 add column 添加列名类型

alter table student add

（7）去除表中的重复元素

select distinct 列名·····from student

select distinct name，gender from student

（8）清空表

truncate table 表名 -- 清空表中的数据，是在表的结构上操作

truncate table student -- 清空表

（9）去除字符串前面的空格或者子串

select trim（列名）from student

select trim（name）from student

3.5查询表

单表查询：

（1）查询单标全部列

select *from 表名

select * from student -- 查询表

（2）查询单行

select * from 表名 where 索引

select * from student where id = 4

（3）查询单行指定列

select 列名，列名 ··· from 列名 where 索引

select name ，gender from student where id = 5

（4）运算符查询

select 可算数运算的列名（运算符）数字 from 表名

select age+10 from student

（5）拼接查询

select concat（需要拼接的列名）from 表名

select concat（name，“：”，gender）from student

（6）填充字符

左填充：

lpad （列名，长度，"填充字符串"）from 表名

select lpad（name，5，“a”）from student

右填充：

rpad（列名，长度，"填充字符串"）from 表名

select rpad（name，5，“a”）from student

（7）将字符串转换大小写

转大写：

select upper(列名) from 表名（where 索引）

select upper(name) from student

转小写

select lower（列名）from 表名（where 索引）

select lower(name) from student

（8）查询时截取制定长度

select substring（列名，起始位置，结束位置）from 表名

select substring（name，1,2）from student

（9）查询指定字符（返回索引位置）

select instr（列名，‘指定字符’）from 表名 (where 索引)

select instr（name，'i'）from student where id = 7

(10)查询时去除字符串前的空格和子串

select trim（列名）from 表名（where 索引）

select trim（name）from student where id = 5

（11）逻辑查询，多个条件

select case（when 条件 then 结果

when 条件 then 结果

else 结果

end）from 表名（where 索引）

select case（when height >=1.9 then '大高个'

when height>1.7 and height <1.9 then '高个'

else '小个子'

end）from student （where gender = '男'）

（12）查询是否为空

select ifnull（列名， '默认值'）from 表名（where 索引）

select ifnull（name，'未知'）from 表名（where id > 1）

(13)数值保留（四舍五入）

select round（列名）from 表名（where 索引）

select round（age）from student （where id = 3）

（14）数值保留（保留小数点位数）

select truncate（列名，保留位数）from 表名（where 索引）

select truncate（height，2）from student （where gender = '男'）

（15）获取随机数（返回0-1之间的数）

select rand（列名）from 表名（where 索引）

select rand（age）from student where id > 2

（16）日期时间查询

返回当前的日期+时间： select now()

返回当前日期： select curdate（）

返回当前时间：select curtime()

获取指定日期的部分

获取指定的年份： select year(列名)

获去指定的月份：select month（列名）

获取指定的天：select day（列名）

（17）日期列查询格式化

select str_to_date（列名，格式化格式）from 表名

select str_to_date(birthday,"%Y-%m-&d") from student

（18）返回日期天数差

select datediff（两个日期）from 表名（where 索引）

select datediff（now（），birthday）from student （where id > 2）

（19）功能查询

求和：select sum（l列名）from 表名

求平均值：select avg（列名）from 表名

求最大值：select max（列名）from 表名

求最小值：select min（列名）from 表名

求行数:select count（列名）from 表名

（20）条件查询

格式：select 结果 from 数据源（表）where 条件

条件的逻辑有：

与（and）或（or）不相等（<>）非（not）包含（in）不包含（not in）列名为空（is null）

列名不为空（is not null）

模糊查询 eg:name like '张%'（%：匹配多个字符）/ '张_'（_：匹配一个字符）

（21）合并

合并时候去除重复元素:union

eg:

select * from student where gender = '男'

union

select * from student where id > 2

合并时不去除重复元素：union all

eg:

select * from student where gender = '男'

union all

select * from student where id > 2

（22）排序

查询完之后在对查询结果进行排序，默认排序都是升序。

eg：

select * from student where id>2 order by height ASC -- 升序

eg：

select * from student where id>2 order by height DESC -- 降序

order by 之后可以跟多个排序索引，先按前面的排序，相同的按后面得物排序。

eg：

SELECT * FROM student ORDER BY age, hight

（23）查询数量限制

eg：select * from student where id > 2 limit n,m -- n（查询结果开始截取的位置）m(结束位置)

（24）分组

group by 对相同索引进行分组

eg:

select count（gender）from student group by gender

四.多表查询

4.1数据库设计规范

数据库设计的三个范式是：

第一范式（1NF）：确保数据库表中的每一列都是原子性的，即每个字段都不能再分解。这意味着每个字段应该只包含一个值，并且不应该包含重复组或多个值。第一范式消除了数据冗余和重复，并使得数据更加规范化。
第二范式（2NF）：在满足第一范式的基础上，要求表中的非主键字段完全依赖于全部主键而不是部分主键。换句话说，如果表中存在复合主键，则非主键字段必须完全取决于所有复合主键的组合，而不是仅仅依赖于其中的一部分。第二范式消除了部分依赖，进一步减少了数据冗余。
第三范式（3NF）：在满足第二范式的基础上，要求表中的每个非主键字段都不传递依赖于其他非主键字段。换句话说，每个非主键字段应该直接依赖于主键，而不是间接地依赖于其他非主键字段。第三范式消除了传递依赖，进一步提高了数据结构的灵活性和效率。

通过遵循这三个范式，可以实现数据库的规范化设计，减少数据冗余和不一致性，提高数据查询和修改的效率，并确保数据的一致性和完整性。然而，在具体的数据库设计中，有时候需要权衡不同的需求，可能会选择放弃某些范式来满足特定的业务需求和性能要求。

4.2 表与表之间的关联

在数据库中，表与表之间的关联关系可以分为强关联和弱关联。

强关联（Strong Relationship）：
强关联是指两个表之间的关系非常紧密，一个表的记录在另一个表中必须存在对应的记录。这种关联关系通常通过在一个表中定义外键来实现，外键指向另一个表中的主键。强关联确保了数据的完整性和一致性，要求两个表之间的关联记录必须存在，否则会违反关联约束而导致操作失败。
弱关联（Weak Relationship）：
弱关联是指两个表之间的关系相对较松散，一个表的记录在另一个表中可以存在对应的记录，也可以不存在。这种关联关系通常通过在一个表中定义可为空的外键来实现，外键指向另一个表中的主键。弱关联允许某些情况下两个表之间的关联记录不存在，但也需要在查询和操作时进行额外的判断和处理。
强关联和弱关联在数据库设计和数据操作中有不同的应用场景和影响：
强关联适用于两个表之间存在必须一一对应的关系，例如订单和订单详情之间的关系，每个订单必须对应一个订单详情。
弱关联适用于两个表之间存在可选的关系，在数据库中，表与表之间的关联关系可以分为强关联和弱关联。

给两个表之间添加强关联同时添加约束：