MySQL数据库基础01

1.数据库基础

1.数据库介绍

什么是数据库，相当于办公软件的Excel表格

• 存储数据的仓库
• 本质上是一个文件系统，还是以文件的方式存在服务器的电脑上的。
• 所有的关系型数据库都可以使用通用的 SQL 语句进行管理 DBMS DataBase Management System

2.常见数据库

• MySQL：开源免费的数据库，小型的数据库
• Oracle：收费的大型数据库，Oracle 公司的产品。
• DB2 ：IBM 公司的数据库产品,收费的。常应用在银行系统中。
• SQL Server：MicroSoft 公司收费的中型的数据库。
• C#、.net 等语言常使用。
• SQLite: 嵌入式的小型数据库，应用在手机端，如：Android。

3.SQL 语句分类

1.Data Definition Language (DDL 数据定义语言) 如：建库，建表
2.Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改
3.Data Query Language(DQL 数据查询语言)，如：对表中的查询操作
4.Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

2.数据库的DDL操作

1.DDL 操作数据库

1.创建数据库

1.基本创建数据库

CREATE DATABASE 数据库名;

2.判断数据库是否已经存在，不存在则创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名;

3.创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

代码示例

-- 直接创建数据库 db1
create database db1;
-- 判断是否存在，如果不存在则创建数据库 db2
create database if not exists db2;
-- 创建数据库并指定字符集为 gbk
create database db3 default character set gbk;

2.查看数据库

1.查看所有的数据库

show databases;

2.查看某个数据库的定义信息

show create database 数据库名;

代码示例

-- 查看所有的数据库
show databases;
-- 查看某个数据库的定义信息
show create database db3;

3.修改数据库

1.修改数据库默认的字符集

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;

例如：将 db3数据库的字符集改成 utf8

alter database db3 character set utf8;

4.删除数据库

1.删除数据库

DROP DATABASE 数据库名;

5.使用数据库

1.查看正在使用的数据库

SELECT DATABASE(); 使用的一个 mysql 中的全局函数

2.使用/切换数据库

USE 数据库名;

代码示例

-- 查看正在使用的数据库
select database();
-- 改变要使用的数据库
use db4;

2.DDL 操作表结构

1.创建表

1.创建一个表

CREATE TABLE 表名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
);

MySQL常用数据类型

附：详细的数据类型如下

2.创建表并指定该表的字符集编码

CREATE TABLE 表名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
)ENGINE=INNODB DEFAULT CHARSET=utf8;
---------------------
如下
CREATE TABLE student(
id INT,
NAME VARCHAR(20)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

2.查看表

1.查看某个数据库中的所有表

SHOW TABLES;

2.查看表结构

DESC 表名;

3.查看创建表的 SQL 语句

SHOW CREATE TABLE 表名;

4.快速创建一个表结构相同的表

CREATE TABLE 新表名 LIKE 旧表名;

3.删除表

1.直接删除表

DROP TABLE 表名;

2.判断表是否存在，如果存在则删除表

DROP TABLE IF EXISTS 表名;

4.修改表结构

1.添加表列项(ADD)

ALTER TABLE 表名 ADD 列名 类型;

2.修改列表项类型(MODIFY)

ALTER TABLE 表名 MODIFY 列名 新的类型;

2.修改列表项名(CHANGE)

ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

3.删除列表项(DROP)

ALTER TABLE 表名 DROP 列名;

4.修改列表项名

RENAME TABLE 表名 TO 新表名;

5.修改表的字符集(character set)

ALTER TABLE 表名 character set 字符集;

3.DML 操作表中的数据

1.插入记录

1.插入数据

INSERT [INTO] 表名 [字段名] VALUES (字段值)

INSERT INTO 表名：表示往哪张表中添加数据
(字段名 1, 字段名 2, …)：要给哪些字段设置值
VALUES (值 1, 值 2, …)：设置具体的值

2.插入全部字段

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

也可以向下面这样简化写

INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);

3.插入部分数据（必须要写列名，不能简化）

INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);

2.复制记录

复制记录也称蠕虫复制，什么是蠕虫复制，将一张已经存在的表中的数据复制到另一张表中。

将表名2中的所有的列复制到表名1中

1.复制全部

INSERT INTO 表名 1 SELECT * FROM 表名 2;

2.只复制部分列

INSERT INTO 表名 1(列 1, 列 2) SELECT 列 1, 列 2 FROM student;

具体操作

-- 创建 student2 表，student2 结构和 student 表结构一样
drop table student2;
create table student2 like student;
-- 将 student 表中的数据添加到 student2 表中
insert into student2 select * from student;
-- 如果只想复制 student 表中 name,age 字段数据到 student2 表中，两张表都写出相应的列名
insert into student2 (name,age) select name,age from student;
select * from student2;

3.更新表记录

UPDATE 表名 SET 列名=值 [WHERE 条件表达式]
UPDATE: 需要更新的表名
SET: 修改的列值
WHERE: 符合条件的记录才更新

1 不带条件修改数据

UPDATE 表名 SET 字段名=值; -- 修改所有的行

2 带条件修改数据

UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

代码示例

-- 不带条件修改数据，将所有的性别改成女
update student set sex = '女';
-- 带条件修改数据，将 id 号为 2 的学生性别改成男
update student set sex='男' where id=2;
-- 一次修改多个列，把 id 为 3 的学生，年龄改成 26 岁，address 改成北京
update student set age=26, address='北京' where id=3;

4.删除表记录

DELETE FROM 表名 [WHERE 条件表达式]
如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件

1.不带条件删除数据

DELETE FROM 表名;

2.带条件删除数据

DELETE FROM 表名 WHERE 字段名=值;

3.使用 truncate 删除表中所有记录

TRUNCATE TABLE 表名;

truncate 和 delete 的区别
truncate 相当于删除表的结构，再创建一张表。
代码示例

-- 带条件删除数据，删除 id 为 1 的记录
delete from student where id=1;
-- 不带条件删除数据,删除表中的所有数据
delete from student;

4.DQL 查询表中的数据

查询介绍

SELECT 列名 FROM 表名 [WHERE 条件表达式]
1) SELECT 命令可以读取一行或者多行记录。
2) 你可以使用星号（*）来代替其他字段，SELECT 语句会返回表的所有字段数据
3) 你可以使用 WHERE 语句来包含任何条件。

1.简单查询

1.查询表所有行和列的数据

使用*表示所有列(*是通配符)
SELECT * FROM 表名;

例如
select * from student;

2.查询指定列

SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;

例如
select name,age from student;

3.指定列的别名进行查询(取别名)

对列指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
对列和表同时指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;

代码示例

-- 表使用别名
select name as 姓名,age as 年龄 from student;
-- 表和列使用别名(表使用别名的原因：用于多表查询操作)
select st.name as 姓名,age as 年龄 from student as st

2.清除重复值

1.查询指定列并且结果不出现重复数据

SELECT DISTINCT 字段名 FROM 表名;

代码示例

-- 查询学生来至于哪些地方
select address from student;

-- 去掉重复的记录
select distinct address from student;

3.查询结果参与运算

1.某列数据和固定值运算

SELECT 列名 1 + 固定值 FROM 表名;

2.某列数据和其他列数据参与运算

SELECT 列名 1 + 列名 2 FROM 表名;

代码示例

基础查询
select * from student;

 给所有的数学加 5 分
select math+5 from student;

查询 math + english 的和
select * from student;
select *,(math+english) as 总成绩 from student;

 as 可以省略如下
select *,(math+english) 总成绩 from student;

4.条件查询(运算符)

条件查询是对数据进行过滤

SELECT 字段名 FROM 表名 WHERE 条件;

常用运算符

代码示例

-- 查询 math 分数大于 80 分的学生
select * from student3 where math>80;
-- 查询 english 分数小于或等于 80 分的学生
select * from student3 where english <=80;
-- 查询 age 等于 20 岁的学生
select * from student3 where age = 20;
-- 查询 age 不等于 20 岁的学生，注：不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;

逻辑运算符

-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)
select * from student3 where age>35 and sex='男';
-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
select * from student3 where age>35 or sex='男';
-- 查询 id 是 1 或 3 或 5 的学生
select * from student3 where id=1 or id=3 or id=5;

in 关键字

SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
in 里面的每个数据都会作为一次条件，只要满足条件的就会显示

代码示例

-- 查询 id 是 1 或 3 或 5 的学生
select * from student3 where id in(1,3,5);
-- 查询 id 不是 1 或 3 或 5 的学生
select * from student3 where id not in(1,3,5);

范围查询

BETWEEN 值 1 AND 值 2
表示从值 1 到值 2 范围，包头又包尾

查询 english 成绩大于等于 75，且小于等于 90 的学生
select * from student3 where english between 75 and 90;

like 关键字

LIKE 表示模糊查询
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

MySQL 通配符

代码示例

-- 查询姓马的学生
select * from student3 where name like '马%';

select * from student3 where name like '马';
-- 查询姓名中包含'德'字的学生
select * from student3 where name like '%德%';
-- 查询姓马，且姓名有两个字的学生
select * from student3 where name like '马_';

5.排序查询语句

单列排序
只按某一个字段进行排序，单列排序。

通过 ORDER BY 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
ASC: 升序，默认值
DESC: 降序

组合排序
同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
例如
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;

聚合函数
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。
五个聚合函数

语法示例

-- 查询学生总数
select count(id) as 总人数 from student;

select count(*) as 总人数 from student;

-- 查询年龄大于 20 的总数
select count(*) from student where age>20;

-- 查询数学成绩总分
select sum(math) 总分 from student;

-- 查询数学成绩平均分
select avg(math) 平均分 from student;

-- 查询数学成绩最高分
select max(math) 最高分 from student;

-- 查询数学成绩最低分
select min(math) 最低分 from student

我们发现对于 NULL 的记录不会统计，建议如果统计个数则不要使用有可能为 null 的列，但如果需要把 NULL
也统计进去呢？
IFNULL(列名，默认值) 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。

-- 查询 id 字段，如果为 null，则使用 0 代替
select ifnull(id,0) from student;

我们可以利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏
select count(ifnull(id,0)) from student;

6.分组查询

分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组
SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

• GROUP BY 怎么分组的？
  将分组字段结果中相同内容作为一组，如按性别将学生分成 2 组。

原理图

 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;

效果如下

实现原理

查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据

错误写法

SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2;

正确写法

-- 对分组查询的结果再进行过滤
SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex having COUNT(*) >2;

实现原理

having 与 where 的区别

having 与 where 的区别

• where 子句

1. 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤
   再分组。
2. where 后面不可以使用聚合函数

• having 子句

1. having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
2. having 后面可以使用聚合函数

limit 语句

limit 的作用：LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子 句][LIMIT 子句];

LIMIT 语法格式

LIMIT offset,length;
offset：起始行数，从 0 开始计数，如果省略，默认就是 0
length： 返回的行数

语句示例

-- 查询学生表中数据，从第 3 条开始显示，显示 6 条。
select * from student3 limit 2,

LIMIT 的使用场景
分页：比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。 假设我们每页显示 5 条记录的方式来分页。

SQL 语句如下

-- 如果第一个参数是 0 可以省略写：	
select * from student3 limit 5;
-- 最后如果不够 5 条，有多少显示多少
select * from student3 limit 10,5;