【MySQL】SQL的概念

一、基本概念 1.1 什么是 SQL

Structured Query Language 结构化查询语言

1.2 SQL作用

（1） 是一种所有关系型数据库的查询规范，不同的数据库都支持。

（2） 通用的数据库 *** 作语言，可以用在不同的数据库中。

（3）不同的数据库 SQL 语句有一些区别。

1.3 SQL 语句分类

（1）Data Definition Language (DDL 数据定义语言) 如：建库，建表

（2）Data Manipulation Language(DML 数据 *** 纵语言)， 如：对表中的记录 *** 作增删改

（3）Data Query Language(DQL 数据查询语言)，如：对表中的查询 *** 作

（4）Data Control Language(DCL 数据控制语言)，如：对用户权限的设置

也就是SQL语句主要是围绕上述四种语句。

1.4 MySQL 的语法

（1）每条语句以分号结尾，如果在 SQLyog 中不是必须加的。

（2）SQL 中不区分大小写，关键字中认为大写和小写是一样的

（3）3 种注释

注释的语法	说明
--空格	单行注释
/* */	多行注释
#	这是 mysql 特有的注释方式

二 、DDL *** 作数据库 2.1 创建数据库 2.1.1 创建数据库的几种方式 创建数据库

CREATE DATABASE 数据库名;

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

CREATE DATABASE IF NOT EXISTS 数据库名;

创建数据库并指定字符集

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

2.1.2 具体 *** 作

-- 直接创建数据库 db1
create database db1;


-- 判断是否存在，如果不存在则创建数据库 db2
create database if not exists db2;


-- 创建数据库并指定字符集为 gbk
create database db3 default character set gbk;

2.2 查看数据库

按 tab 键可以自动补全关键字

-- 查看所有的数据库
show databases;


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

2.3 修改数据库 2.3.1 修改数据库默认的字符集

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
alter database 数据库名 default character set 字符集;

2.3.2 具体 *** 作

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

alter database db3 character set utf8;

2.4 删除数据库 2.4.1 删除数据库的语法

DROP DATABASE 数据库名;
drop database 数据库名;

2.4.2 具体 *** 作

每行 SQL 语句需要选中再执行，可以按 F9

删除 db2 数据库

drop database db2;

2.5 使用数据库 2.5.1 查看正在使用的数据库

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

2.5.2 使用/切换数据库

USE 数据库名;

2.5.3 具体 *** 作

-- 查看正在使用的数据库
select database();


-- 改变要使用的数据库
use db4;

2.5.4 面试题

在 MySQL 数据库软件中，有如下三个数据库：test1、test2、test3

登录数据库之后，输入语句： select database test2; 运行结果是什么?

这是一条错误的语句，如果要选中一个数据库，应用使用： use test2;
 

三、DDL *** 作表结构

前提先使用某个数据库

3.1 创建表 3.1.1 创建表的格式

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

3.1.2 关键字说明

创建表的关键字	说明
CREATE	创建
TABLE	表

3.2 MySQL 数据类型 3.2.1 常使用的数据类型如下

3.2.2 详细的数据类型如下

整数：

类型名称	类型说明
tinyInt	微整型：很小的整数(占 8 位二进制)
smallint	小整型：小的整数(占 16 位二进制)
mediumint	中整型：中等长度的整数(占 24 位二进制)
int(integer)	整型：整数类型(占 32 位二进制)

小数

float	单精度浮点数，占 4 个字节
double	双精度浮点数，占 8 个字节

日期

time	表示时间类型
datetime	同时可以表示日期和时间类型
date	表示日期类型

字符串

char(m)	固定长度的字符串， 无论使用几个字符都占满全部， M 为 0~255 之间的整 数
varchar(m)	可变长度的字符串， 使用几个字符就占用几个， M 为 0~65535 之间的整数

大二进制

tinyblob Big Large Object	允许长度 0~255 字节
blob	允许长度 0~65535 字节
mediumblob	允许长度 0~167772150 字节
longblob	允许长度 0~4294967295 字节

大文本

tinytext

允许长度 0~255 字节

text	允许长度 0~65535 字节
mediumtext	允许长度 0~167772150 字节
longtext	允许长度 0~4294967295 字节

3.2.3 具体 *** 作

创建 student 表包含 id,name,birthday 字段

create table student (
id int, -- 整数
name varchar(20), -- 字符串
birthday date -- 生日，最后没有逗号
);

3.3 查看表 3.3.1 查看某个数据库中的所有表

SHOW TABLES;

3.3.2 查看表结构

DESC 表名;

3.3.3 查看创建表的 SQL 语句

SHOW CREATE TABLE 表名;

3.3.4 具体 *** 作：

查看 day21 数据库中的所有表

use day21;
show tables;

查看 student 表的结构

desc student;

 查看 student 的创建表 SQL 语句

show create table student;

执行结果

存在的目的是为了避免关键字的冲突

CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.4 快速创建一个表结构相同的表 3.4.1 语法

CREATE TABLE 新表名 LIKE 旧表名;

3.4.2 具体 *** 作

创建 s1 表， s1 表结构和 student 表结构相同

-- 创建一个 s1 的表与 student 结构相同
create table s1 like student;

desc s1;

3.5 删除表 3.5.1 直接删除表

DROP TABLE 表名;

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

DROP TABLE IF EXISTS 表名;

3.5.3 具体 *** 作

-- 直接删除表 s1 表
drop table s1;

-- 判断表是否存在并删除 s1 表
drop table if exists `create`;

与直接删除的区别

如果表不存在，不删除，存在则删除

3.6 修改表结构 3.6.1 添加表列 ADD

ALTER TABLE 表名 ADD 列名 类型;

3.6.2 修改列类型 MODIFY

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

3.6.3 修改列名 CHANGE

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

3.6.4 删除列 DROP

ALTER TABLE 表名 DROP 列名;

3.6.5 修改表名

RENAME TABLE 表名 TO 新表名;

将学生表 student 改名成 student2

rename table student to student2;

3.6.6 修改字符集 character set

ALTER TABLE 表名 character set 字符集;

将 student2 表的编码修改成 gbk

alter table student2 character set gbk;

四、DML *** 作表中的数据

用于对表中的记录进行增删改 *** 作

4.1 插入记录

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

4.1.1 插入全部字段

所有的字段名都写出来

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

不写字段名

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

4.1.2 插入部分数据

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

注：没有添加数据的字段会使用 NULL

4.1.3 具体 *** 作:

插入所有的列，向学生表中

insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');
insert into student (id,name,age,sex) values (2, '孙悟天', 16, '男');

向表中插入所有字段

-- 插入所有列
insert into student values (3, '孙悟饭', 18, '男', '龟仙人洞中');


-- 如果只插入部分列，必须写列名
insert into student values (3, '孙悟饭', 18, '男');

4.2 DOS 命令窗口 *** 作数据乱码问题的解决

当我们使用 DOS 命令行进行 SQL 语句 *** 作如有有中文会出现乱码

 4.2.1 insert 的注意事项

1) 插入的数据应与字段的数据类型相同

2) 数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。

3) 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。在 mysql 中可以使用

value， 但不建议使用， 功能与 values 相同。

4) 字符和日期型数据应包含在单引号中。 MySQL 中也可以使用双引号做为分隔符。

5) 不指定列或使用 null，表示插入空值。

4.2.2 乱码产生的原因

 4.2.3 查看 MySQL 内部设置的编码

查看包含 character 开头的全局变量

show variables like 'character%';

4.2.4 解决方案

修改 client、 connection、 results 的编码为 GBK，保证和 DOS 命令行编码保持一致

单独设置	说明
set character_set_client=gbk;	修改客户端的字符集为 GBK
set character_set_connection=gbk;	修改连接的字符集为 GBK
set character_set_results=gbk;	修改查询的结果字符集为 GBK

同时设置三项

set names gbk;

注意：退出 DOS 命令行就失效了，需要每次都配置

4.3 蠕虫复制 4.3.1 什么是蠕虫复制

将一张已经存在的表中的数据复制到另一张表中。

4.3.2 语法格式

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

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

只复制部分列

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

4.3.3 具体 *** 作

-- 创建 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;

4.4 更新表记录

UPDATE 表名 SET 列名=值 [WHERE 条件表达式]
UPDATE: 需要更新的表名
SET: 修改的列值
WHERE: 符合条件的记录才更新
你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件

4.4.1 不带条件修改数据

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

4.4.2 带条件修改数据

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

4.4.3 具体 *** 作

-- 不带条件修改数据，将所有的性别改成女
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.5 删除表记录

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

4.5.1 不带条件删除数据

DELETE FROM 表名;

4.5.2 带条件删除数据

DELETE FROM 表名 WHERE 字段名=值;

4.5.3 使用 truncate 删除表中所有记录

TRUNCATE TABLE 表名;

4.5.4 truncate 和 delete 的区别

truncate 相当于删除表的结构，再创建一张表

truncate:会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表；

delete:逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据；

4.5.5 具体 *** 作

-- 带条件删除数据，删除 id 为 1 的记录
delete from student where id=1;


-- 不带条件删除数据,删除表中的所有数据
delete from student;

五、DQL 查询表中的数据

查询不会对数据库中的数据进行修改.只是一种显示数据的方式

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

5.1 简单查询 5.1.1 查询表所有行和列的数据

使用*表示所有列

SELECT * FROM 表名;

查询所有的学生

select * from student;

5.1.2 查询指定列

查询指定列的数据,多个列之间以逗号分隔

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

查询 student 表中的 name 和 age 列

select name,age from student;

5.2 指定列的别名进行查询 5.2.1 使用关键字

使用别名的好处： 显示的时候使用新的名字，并不修改表的结构

5.2.2 语法

对列指定别名

SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;

对列和表同时指定别名

SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;

5.2.3 具体 *** 作

-- 使用别名
select name as 姓名,age as 年龄 from student;

-- 表使用别名
select st.name as 姓名,age as 年龄 from student as st;

表使用别名的原因：用于多表查询 *** 作

5.3 清除重复值 5.3.1 查询指定列并且结果不出现重复数据

SELECT DISTINCT 字段名 FROM 表名;

5.3.2 具体 *** 作

查询学生来至于哪些地方

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


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

5.4 查询结果参与运算 5.4.1 某列数据和固定值运算

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

5.4.2 某列数据和其他列数据参与运算

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

注意: 参与运算的必须是数值类型

5.4.3 需求

准备数据： 添加数学，英语成绩列,给每条记录添加对应的数学和英语成绩，查询的时候将数学和英语的成绩相加

5.4.4 实现

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;

5.5 条件查询 5.5.1 为什么要条件查询

如果没有查询条件，则每次查询所有的行。实际应用中，一般要指定查询的条件。对记录进行过滤。

5.5.2 条件查询的语法

SELECT 字段名 FROM 表名 WHERE 条件;
流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

准备数据

创建一个学生表，包含如下列

CREATE TABLE student3 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

运算符

比较运算符	说明
>、 <、 <=、 >=、 =、 <>	<>在 SQL 中表示不等于，在 mysql 中也可以使用!= 没有==
BETWEEN...AND	在一个范围之内，如： between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾
IN(集合)	集合表示多个值，使用逗号分隔
LIKE '张%'	模糊查询
IS NULL	查询某一列为 NULL 的值，注：不能写=NULL

具体 *** 作

-- 查询 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;

逻辑运算符

逻辑运算符	说明
and 或 &&	与， SQL 中建议使用前者，后者并不通用。
or 或 ||	或
not 或 !	非

具体 *** 作

-- 查询 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 范围，包头又包尾
比如： age BETWEEN 80 AND 100 相当于： age>=80 && age<=100

查询 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 '马_';