欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

【MySQL】入门及基础指令

程序员文章站 2022-06-16 13:19:50
文章目录数据库简介关系型数据(SQL)非关系型数据库(NoSQL)新型数据库(NewSQL)MySQL优点:MySQL 缺点:基础指令Mysql的数据库类型整数类型浮点型日期和时间类型字符串类型枚举类二进制类型设计数据表数据表命名规范第一设计范式第二范式第三范式反范式设计用alter table 修改数据表的方法修改表名删除数据表insert 插入数据查询表中的所有数据删除数据数据库简介关系型数据(SQL)关系型数据库，是指采用了关系模型来组织数据的数据库，从语言功能层面来讲,一般是指用 SQL 语言...

文章目录

数据库简介

关系型数据(SQL)

关系型数据库，是指采用了关系模型来组织数据的数据库，从语言功能层面来讲,一般是指用 SQL 语言来进行操作管理的关系型数据库。常用的关系型数据库有 MySQL、Oracle、SQLServer、PostGreSQL。

非关系型数据库(NoSQL)

NoSQL 通常指的是 non-relational ，也可以理解为 Not Only SQL，泛指非关系型数据库。常见的非关系型数据库有 MongDB、HBase、Redis

新型数据库(NewSQL)

NewSQL 是对各种新的可扩展/高性能数据库的简称，这类数据库不仅具有 NoSQL 对海量数据的存储管理能力，还保持了传统数据库支持 ACID 和 SQL 等特性。常见的 NewSQL 有 Google Spanner/F1、阿里 OceanBase、腾讯 TDSQL、TiDB、Cockroach。

MySQL优点:

运行速度快：MySQL 体积小，命令执行的速度快；

使用成本低：MySQL 是开源的，且提供免费版本，对大多数用户来说大大降低了使用成本；

使用容易：与其他大型数据库的设置和管理相比，其复杂程度较低，易于使用；

可移植性强：MySQL 能够运行与多种系统平台上，如 Windouws，Linux，Unix 等；

适用更多用户：MySQL 支持最常用的数据管理功能，适用于中小型企业甚至大型网站应用。

MySQL 缺点:

MySQL 最大的缺点是其安全系统，主要是复杂而非标准，另外只有到调用 mysqladmin 来重读用户权限时才发生改变；

MySQL 没有一种存储过程（Stored ProcedureStored Procedure）语言，这是对习惯于企业级数据库的程序员的最大限制；

MySQL不支持热备份；

MySQL 的另一个主要的缺陷之一是缺乏标准的 RI（Referential Integrity-RI）机制；Rl 限制的缺乏（在给定字段域上的一种固定的范围限制）可以通过大量的数据类型来补偿；

基础指令

首先需要cd到安装Mysql的位置

  • 登录： mysql -uroot -p -P3306 -hlocalhost 命令中的 “-u” 后面表示登录用户名，"-p" 表示密码单独输入，"-P" 表示端口号，"-h" 表示主机地址，出现如下图所示表示需要输入密码

  • 创建子账号：create usee 'test'@'localhost' identified by '123456'; 创建一个子账号，可以给以不同的权限，并且给定密码。

  • 退出：quit;

  • 展示全部的数据库：show databases;

  • 新建数据库：create database try_batabase;

  • 删除数据库：drop database try_batabase;

  • 选中数据库：use try_batabase;

  • 展示表：show tables;

Mysql的数据库类型

整数类型

【MySQL】入门及基础指令

浮点型

float，double，decimal。

实际应用中要根据字段在业务中实际表示的含义来选择数值字段数据类型，例如年龄字段 age 选择无符号 TINYINT 类型，金额数值对精度要求比较高，所以选择 DECIMAL 类型比较合适，时间戳类型使用 INT，对于数据量比较大的表的自增主键 id 可选择 BIGINT，具体字段类型的选择，还需要根据实际情况来综合分析，合理选择即可，字段的默认值尽量避免 NULL。

日期和时间类型

【MySQL】入门及基础指令

字符串类型

【MySQL】入门及基础指令

枚举类

【MySQL】入门及基础指令

二进制类型

【MySQL】入门及基础指令

设计数据表

一个数据表主要包含信息有 : 表名、主键、字段、数据类型、索引，本节主要介绍表的命名规范、字段命名、字段的数据类型选择。

数据表命名规范

数据表命名是小写字母和下划线 _ 组成，用来分割不同单词之间的含义，例如 “student_course” 表示学生选课关联表，实际命名需要根据具体功能而定，好的命名规范在实际工作中也是很重要的。

CREATE TABLE `student_course`  ( # 其中 “student_course” 为表名称
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, # “UNSIGNED” 表示无符号，“AUTO_INCREMENT” 表示自增
  `student_id` int(10) UNSIGNED NOT NULL DEFAULT 0,# "NOT NULL DEFAULT 0 "表示默认不为空，且默认值为 0 。
  `course_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)# 表示设置 “id” 为业务主键
);

第一设计范式

第一设计范式要求表中字段都是不可再分的，如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息。

第二范式

第二设计范式要求表中必须存在业务主键，并且全部非主键依赖于业务主键。

第三范式

第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。很多时候我们为了满足第三范式往往会把一张表分成多张表。即满足第二范式前提**，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键**，这被称作传递依赖于主属性。 通俗解释就是一张表最多只存两层同类型信息。

下图就不符合
【MySQL】入门及基础指令
应该拆分为
【MySQL】入门及基础指令

反范式设计

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。

【MySQL】入门及基础指令

数据表的方法

修改表名

  • 修改表名
alter table student rename to new_name;
  • 查看建表sql语句
show create table new_student;
  • 修改字段的数据类型
alter table `new_student` 
modify column `name` char(50) 
#CHARACTER SET utf8 COLLATE utf8_general_ci 
not null default '无名' after `id`;
  • 新增表字段
 alter table `new_student` 
add column `sex` tinyint(2) unsigned not null default 1 comment '性别 : 1:男 2:女' after `id_number`;
  • 删除表字段
alter table `new_student`
drop column `sex`;
  • 修改表字段名字
alter table `new_student`
change column `name` `new_name` char(50) NOT NULL DEFAULT '无名' AFTER `id`;
  • 修改表字段默认值
alter table `new_student`
modify column `name` char(50) not null default '小花' after id;
  • 注意事项

需要注意的是，ALTER TABLE 操作的性能对于大表来说会存在问题，因此在实际生产环境中，ALTER TABLE 操作需要谨慎，MySQL 执行大部分修改表结构的方法是用新的结构创建一个空白表，从表汇总查出所有数据插入新表，这样操作对于大数据量的表来说可能需要花费很长时间，所以操作大表之前需要谨慎一些。

删除数据表

drop table new_student;

注意这里需要先选中数据库，然后才可以删除表。

如果需要删除数据表一定要谨慎，删除数据表之前最好做备份。

插入数据

INSERT INTO teacher
(name,age,id_number)
VALUES
('王小花',19,'42011720200604077X'),
('张晓丽',18,'42011720200604099X'),
('刘美丽',20,'42011720200604020X'),
('吴帅',21,'42011720200604022X'),
('张平',22,'42011720200604033X')

查询数据

  • 查询全部数据
SELECT * FROM teacher;
  • 查询指定条数的结果集

查询前10 条

SELECT * FROM teacher LIMIT 10;

从第10条开始查询

SELECT * FROM teacher LIMIT 10,10;

查询指定字段，注意更好的方法是进行命名。区分开。

#SELECT name,age FROM teacher LIMIT 6,5;
SELECT name AS new_name,age as new_age FROM teacher LIMIT 6,5;

删除数据

  • 删除一条
DELETE FROM teacher WHERE id = 8;
  • 删除全部
DELETE FROM teacher;
  • 清空数据表
TRUNCATE TABLE new_student;

TRUNCATE 清空表数据的实际过程是先删除数据表，然后新建一张和原来表结构一模一样的表来替代清空。

DELETE 删除表数据不会改变自增主键的增长值。

更新数据

  • 更新某字段的值

前三个用户的年龄更新为33.

update teacher set age = 33 limit 3;
  • 更新多列字段的值
update teacher set age = 18, id_name = '44444444440604099X' where id = 30;

模糊查询

  • 模糊查询表达式
    %表示任意内容，%小%表示包含小的表达式。
SELECT * FROM teacher WHERE name LIKE '王%';

需要注意的是实际业务中如非必要尽量避免使用模糊查询，如果必须要用,尽量选择最左匹配原则,因为这样可以使用到索引，形如 王%这种格式,否则一旦数据量很大，没有用到索引的模糊查询性能可能会很差。

条件查询

除了常用条件，还可以使用不等于号等。以及is null，is not null。对于多条件查找用and，or进行连接。

SELECT * FROM teacher WHERE age > 18 and age<23;

联合查询

  • 把满足两种查询条件的结果合并一起
    这样是不去重的。
SELECT * FROM teacher WHERE age > 20
UNION ALL
SELECT * FROM teacher WHERE age > 25;
  • 联合查询，去重
SELECT * FROM teacher WHERE age > 20
UNION
SELECT * FROM teacher WHERE age > 25;

排序

  • ASC 从小到大排序
 SELECT * FROM teacher ORDER BY age ASC;
  • DESC 从大到小排序
 SELECT * FROM teacher ORDER BY age desc;
  • 使用多字段混合排序
    将查询出来的结果集按照 age 从大到小排序之后，再按照 id 字段从小到大排序.
SELECT * FROM teacher ORDER BY age DESC,id ASC;
  • 对字符串类型字段排序
    ** 注意mysql中，单引号'表示字符串的引用，反引号`表示关键字。
INSERT INTO teacher
(name,age,id_number,email)
VALUES
('Tom',22,'42011720200604077X','tom@qq.com'),
('Jack',23,'42011720200604099X','jack@qq.com'),
('Mary',24,'42011720200604020X','mary@qq.com'),
('Timo',25,'42011720200604022X','timo@qq.com'),
('Faker',21,'42011720200604033X','faker@qq.com');

SELECT * FROM teacher ORDER BY name ASC;

表链接

  • LEFT JOIN左连接
    是以左边的表为基准，若右表没有对应的值，用 NULL 来填补。
SELECT c.id AS course_id,c.*,t.* FROM course c LEFT JOIN teacher t ON c.teacher_id=t.id;

c.id AS course_id表示将 course表 中 id 字段重命名为 course_id 展示，其目的是为了防止和 teacher表 中 id 字段混淆；
c.*表示 course 表所有字段数据；
t.* 表示 teacher 表字段所有数据；
ON 后面跟着的条件是连接表的条件；
course c 表示将 course 简写为 c， teacher t 表示将 teacher 简写为 t；
LEFT JOIN 为左连接，是以左边的表为’基准’，若右表没有对应的值，用 NULL 来填补。

  • INNER JOIN内连接
    展示的是左右两表都有对应的数据。
SELECT c.id AS course_id,c.*,t.* FROM course c INNER JOIN teacher t ON c.teacher_id=t.id;
  • RIGHT JOIN右连接
SELECT c.id AS course_id,c.*,t.* FROM course c RIGHT JOIN teacher t ON c.teacher_id=t.id;
  • 多表混合连接
# 多表混合连接查询时，后面可以把前面执行的结果集整体当成一个表
SELECT * FROM 
student a 
LEFT JOIN 
student_course b 
ON a.id=b.student_id 
RIGHT JOIN 
course c 
ON b.course_id=c.id 
INNER JOIN teacher d 
ON c.teacher_id=d.id;
WHERE a.age > 18 AND d.age < 55;

去重

  • group by 去重
    按照要求进行的了分组筛选
SELECT c.teacher_id,a.course_id,c.course_name,d.name
FROM 
student_course a 
INNER JOIN 
student b 
ON a.student_id=b.id 
INNER JOIN course c 
ON a.course_id=c.id 
INNER JOIN teacher d
ON c.teacher_id=d.id 
GROUP BY c.teacher_id,a.course_id;

聚合函数

  • 均值
SELECT AVG(age) FROM teacher;
  • 计数
# 会统计null值进去
SELECT COUNT(*) FROM student;
  • 求和
SELECT SUM(age) FROM teacher;
  • 最值
SELECT MIN(age) FROM teacher;
SELECT MAX(age) FROM student;
  • group by having
# 筛选出平均年龄大于20的课程
SELECT a.course_id,c.course_name,AVG(age) 
FROM 
student_course a 
INNER JOIN 
student b 
ON a.student_id=b.id 
INNER JOIN course c 
ON a.course_id=c.id
GROUP BY a.course_id,c.course_name
HAVING AVG(age) >= 20;

本文地址：https://blog.csdn.net/zcz5566719/article/details/108562099

相关标签： MySQL 数据库 java

上一篇： 在react中使用highlight.js将页面上的代码高亮的方法

下一篇： 各设计模式的总结和对比

推荐阅读