MySQL 基础

user8288612657632
172 阅读15分钟

注释

  • 单行注释: -- 注释内容 或 #注释内容(MySQL 特有)
  • 多行注释: /* 注释 */

注意：使用-- 添加单行注释时，--后面一定要加空格，而#没有要求

数据类型

MySQL 支持多种类型，可以分为三类：

  • 数值
tinyint : 小整数型，占一个字节 
int ： 大整数类型，占四个字节 
eg ： age int 
double ： 浮点类型 使用格式： 字段名 double(总长度,小数点后保留的位数) 
eg ： score double(5,2)
  • 日期
date ： 日期值。只包含年月日 
eg ：birthday date ： 
datetime ： 混合日期和时间值。包含年月日时分秒
  • 字符串
char ： 定长字符串。 优点：存储性能高 缺点：浪费空间 
eg ： name char(10) 如果存储的数据字符个数不足10个，也会占10个的空间 
varchar ： 变长字符串。 优点：节约空间 缺点：存储性能底 
eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间

SQL分类

  • DDL(Data Definition Language) ： 数据定义语言，用来定义数据库对象：数据库，表，列等 DDL简单理解就是用来操作数据库，表等
  • DML(Data Manipulation Language) 数据操作语言，用来对数据库中表的数据进行增删改 DML简单理解就对表中数据进行增删改
  • DQL(Data Query Language) 数据查询语言，用来查询数据库中表的记录(数据) DQL简单理解就是对数据进行查询操作。从数据库表中查询到我们想要的数据。
  • DCL(Data Control Language) 数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

DDL:操作数据库

  • 查询所有的数据库
SHOW DATABASES;
  • 创建数据库
CREATE DATABASE 数据库名称;
  • 创建数据库(判断，如果不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;
  • 删除数据库
DROP DATABASE 数据库名称;
  • 删除数据库(判断，如果存在则删除)
DROP DATABASE IF EXISTS 数据库名称;
  • 使用数据库
USE 数据库名称;
  • 查看当前使用的数据库
SELECT DATABASE();

DDL:操作表

  • 查询表
SHOW TABLES;
  • 查询表结构
DESC 表名称;
  • 创建表
CREATE TABLE 表名 ( 字段名1 数据类型1, 字段名2 数据类型2, … 字段名n 数据类型n );

注意：最后一行末尾，不能加逗号

create table tb_user ( id int, username varchar(20), password varchar(32) );

案例：

需求：设计一张学生表，请注重数据类型、长度的合理性

  1. 编号
  2. 姓名，姓名最长不超过10个汉字
  3. 性别，因为取值只有两种可能，因此最多一个汉字
  4. 生日，取值为年月日
  5. 入学成绩，小数点后保留两位
  6. 邮件地址，最大长度不超过 64
  7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符
  8. 学生状态（用数字表示，正常、休学、毕业...）

语句设计如下：

create table student ( 
    id int, 
    name varchar(10), 
    gender char(1), 
    birthday date, 
    score double(5,2), 
    email varchar(15), 
    tel varchar(15), 
    status tinyint 
);
  • 删除表
DROP TABLE 表名;
  • 删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;
  • 修改表
ALTER TABLE 表名 RENAME TO 新的表名; 
-- 将表名student修改为stu 
alter table student rename to stu;
  • 添加一列
ALTER TABLE 表名 ADD 列名 数据类型; 
-- 给stu表添加一列address，该字段类型是varchar(50) 
alter table stu add address varchar(50);
  • 修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 将stu表中的address字段的类型改为 char(50) 
alter table stu modify address char(50);
  • 修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型; 
-- 将stu表中的address字段名改为 addr，类型改为varchar(50) 
alter table stu change address addr varchar(50);
  • 删除列
ALTER TABLE 表名 DROP 列名; 
-- 将stu表中的addr字段 删除 
alter table stu drop addr;

DML

  • DML主要是对数据进行增（insert）删（delete）改（update）操作。

  • 给指定列添加数据

INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
  • 给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,…);
  • 批量添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;   
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
  • 练习
select * from stu;
-- 给指定列添加数据 
INSERT INTO stu (id, NAME) VALUES (1, '张三'); 
-- 给所有列添加数据，列名的列表可以省略的 
INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1); 
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1); 
-- 批量添加数据 
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1), (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1), (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
  • 修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;

注意：
修改语句中如果不加条件，则将所有数据都修改！ 像上面的语句中的中括号，表示在写sql语句中可以省略这部分

  • 练习
  • 将张三的性别改为女
update stu set sex = '女' where name = '张三';
  • 将张三的生日改为 1999-12-12 分数改为99.99
update stu set birthday = '1999-12-12', score = 99.99 where name = '张三';
  • 注意：如果update语句没有加where条件，则会将表中所有数据全部修改！
update stu set sex = '女';
  • 删除数据
DELETE FROM 表名 [WHERE 条件] ;
  • 练习
-- 删除张三记录 
delete from stu where name = '张三'; 
-- 删除stu表中所有的数据 
delete from stu;

DQL

  • 查询的完整语法：
SELECT 
    字段列表 
FROM 
    表名列表 
WHERE 
    条件列表 
GROUP BY 
    分组字段 
HAVING 
    分组后条件 
ORDER BY 
    排序字段 
LIMIT 
    分页限定
  • 准备表及一些数据
-- 删除stu表 drop table if exists stu; 
-- 创建stu表 
CREATE TABLE stu ( id int, -- 编号 
name varchar(20), -- 姓名 
age int, -- 年龄 
sex varchar(5), -- 性别 
address varchar(100), -- 地址 
math double(5,2), -- 数学成绩 
english double(5,2), -- 英语成绩 
hire_date date -- 入学时间 ); 
-- 添加数据 
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) VALUES (1,'马运',55,'男','杭州',66,78,'1995-09-01'), (2,'马花疼',45,'女','深圳',98,87,'1998-09-01'), (3,'马斯克',55,'男','香港',56,77,'1999-09-02'), (4,'柳白',20,'女','湖南',76,65,'1997-09-05'), (5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'), (6,'刘德花',57,'男','香港',99,99,'1998-09-01'), (7,'张学右',22,'女','香港',99,99,'1998-09-01'), (8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');
  • 查询多个字段
SELECT 字段列表 FROM 表名; 
SELECT * FROM 表名; -- 查询所有数据
  • 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
  • 起别名
AS: AS 也可以省略

  • 练习

  • 查询name、age两列

select name,age from stu;
  • 查询所有列的数据，列名的列表可以使用*替代
select * from stu;
  • 查询地址信息
select address from stu;
  • 去除重复记录
select distinct address from stu;
  • 查询姓名、数学成绩、英语成绩。并通过as给math和english起别名（as关键字可以省略）
select name,math as 数学成绩,english as 英文成绩 from stu; 
select name,math 数学成绩,english 英文成绩 from stu;
  • 条件查询
  • 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;

  • 条件查询练习

  • 查询年龄大于20岁的学员信息

select * from stu where age > 20;
  • 查询年龄大于等于20岁的学员信息
select * from stu where age >= 20;
  • 查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
select * from stu where age >= 20 && age <= 30; 
select * from stu where age >= 20 and age <= 30;

上面语句中 && 和 and 都表示并且的意思。建议使用 and 。 也可以使用 between ... and 来实现上面需求

select * from stu where age BETWEEN 20 and 30;
  • 查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息
select * from stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';
  • 查询年龄等于18岁的学员信息
select * from stu where age = 18;
  • 查询年龄不等于18岁的学员信息
select * from stu where age != 18; 
select * from stu where age <> 18;
  • 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
select * from stu where age = 18 or age = 20 or age = 22; 
select * from stu where age in (18,20 ,22);
  • 查询英语成绩为 null的学员信息
  • null值的比较不能使用 = 或者 != 。需要使用 is 或者 is not
select * from stu where english = null; 
-- 这个语句是不行的 
select * from stu where english is null; 
select * from stu where english is not null;
  • 模糊查询练习

模糊查询使用like关键字，可以使用通配符进行占位:
（1）_ : 代表单个任意字符
（2）% : 代表任意个数字符

  • 查询姓'马'的学员信息
select * from stu where name like '马%';
  • 查询第二个字是'花'的学员信息
select * from stu where name like '_花%';
  • 查询名字中包含 '德' 的学员信息
select * from stu where name like '%德%';
  • 排序查询
  • 语法
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

上述语句中的排序方式有两种，分别是：

  • ASC ： 升序排列 （默认值）
  • DESC ： 降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

  • 练习

  • 查询学生信息，按照年龄升序排列

select * from stu order by age ;
  • 查询学生信息，按照数学成绩降序排列
select * from stu order by math desc ;
  • 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select * from stu order by math desc , english asc ;

聚合函数

  • 聚合函数分类
函数名功能
count(列名)统计数量（一般选用不为null的列）
max(列名)最大值
min(列名)最小值
sum(列名)求和
avg(列名)平均值
  • 聚合函数语法
SELECT 聚合函数名(列名) FROM 表;

注意：null 值不参与所有聚合函数运算

  • 练习
  • 统计班级一共有多少个学生
select count(id) from stu; 
select count(english) from stu;

上面语句根据某个字段进行统计，如果该字段某一行的值为null的话，将不会被统计。所以可以在count() 来实现。 表示所有字段数据，一行中也不可能所有的数据都为null，所以建议使用 count(*)

select count(*) from stu;
  • 查询数学成绩的最高分
select max(math) from stu;
  • 查询数学成绩的最低分
select min(math) from stu;
  • 查询数学成绩的总分
select sum(math) from stu;
  • 查询数学成绩的平均分
select avg(math) from stu;
  • 查询英语成绩的最低分
select min(english) from stu;

分组查询

  • 语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

  • 练习

  • 查询男同学和女同学各自的数学平均分

select sex, avg(math) from stu group by sex;
  • 注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
select name, sex, avg(math) from stu group by sex; -- 这里查询name字段就没有任何意义
  • 查询男同学和女同学各自的数学平均分，以及各自人数
select sex, avg(math),count(*) from stu group by sex;
  • 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select sex, avg(math),count(*) from stu where math > 70 group by sex;
  • 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的
select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*) > 2;

where 和 having 区别：

  • 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
  • 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

分页查询

  • 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;

注意： 上述语句中的起始索引是从0开始

  • 练习

  • 从0开始查询，查询3条数据

select * from stu limit 0 , 3;
  • 每页显示3条数据，查询第1页数据
select * from stu limit 0 , 3;
  • 每页显示3条数据，查询第2页数据
select * from stu limit 3 , 3;
  • 每页显示3条数据，查询第3页数据
select * from stu limit 6 , 3;

从上面的练习推导出起始索引计算公式：

起始索引 = (当前页码 - 1) * 每页显示的条数

三大范式

  • 1NF(第一范式)：属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。
  • 2NF(第二范式)：2NF 要求数据库表中的每个实例或行必须可以被惟一地区分，2NF 在 1NF 的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。
  • 3NF(第三范式)：3NF 在 2NF 的基础之上，要求每列都和主键列直接相关，而不是间接相关，即不存在其他表的非主键信息。

在开发过程中，并不一定要满足三大范式，有时候为了提高查询效率，可以在表中冗余其他表的字段。

DML 语句和 DDL 语句区别

DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。

DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。

主键和外键的区别

  • 主键：用于唯一标识一行数据，不能有重复，不允许为空，且一个表只能有一个主键；
  • 外键：用来和其他表建立联系，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键；

drop、delete、truncate 区别

（1）用法不同

  • drop(丢弃数据): drop table 表名 ，直接将表结构都删除掉，在删除表的时候使用。
  • truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
  • delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。

（2）属于不同的数据库语言

  • truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
  • delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segment 中，事务提交之后才生效。

（3）执行速度不同

  • delete命令执行的时候会产生数据库的binlog日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复。
  • truncate命令执行的时候不会产生数据库日志，因此比delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。
  • drop命令会把表占用的空间全部释放掉。

一般来说：drop > truncate > delete

avatar
文章
阅读
粉丝