SQL

什么是 SQL

Structured Query Language 结构化查询语言

SQL 作用

1. 是一种所有关系型数据库的查询规范，不同的数据库都支持。
2. 通用的数据库操作语言，可以用在不同的数据库中。
3. 不同的数据库 SQL 语句有一些区别

SQL 语句分类

1. DDL(Data Definition Language)数据定义语言
   用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
2. DML(Data Manipulation Language)数据操作语言
   用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
3. DQL(Data Query Language)数据查询语言
   用来查询数据库中表的记录(数据)。关键字：select, where 等
4. DCL(Data Control Language)数据控制语言
   用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

MySQL 的语法

1. 每条语句以分号结尾，如果在 SQLyog 中不是必须加的。
2. SQL 中不区分大小写，关键字中认为大写和小写是一样的
3. 3 种注释：

单行注释: – 注释内容 或 # 注释内容(mysql 特有)
多行注释: /* 注释 */

创建数据库

创建数据库的几种方式

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

-- 判断数据库是否已经存在，不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
-- 判断是否存在，如果不存在则创建数据库 db2
create database if not exists db2;

-- 创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
-- 创建数据库并指定字符集为 utf8mb4
create database db3 default character set utf8mb4;

查看数据库

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

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

修改数据库

-- 修改数据库默认的字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
-- 将 db3 数据库的字符集改成 utf8
alter database db3 character set utf8;

删除数据库

DROP DATABASE 数据库名;

-- 如果存在就删除 db2 数据库
drop database if exists db2;

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

使用数据库

-- 查看正在使用的数据库
SELECT DATABASE(); #使用的一个 mysql 中的全局函数
-- 查看正在使用的数据库
select database();

-- 使用/切换数据库
USE 数据库名;
-- 改变要使用的数据库
use db4;

DDL 操作表结构

创建表

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

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

注意：最后一列，不需要加逗号（,）

MySQL 数据类型

1. int:整数类型 age int,
2. double:小数类型 score double(5,2)
3. date:日期，只包含年月日，yyyy-MM-dd
4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
   如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6. varchar：字符串
   name varchar(20):姓名最大20个字符
   zhangsan 8个字符 张三 2个字符

分类	类型名称	类型说明
整数	tinyInt	微整型：很小的整数(占 8 位二进制)
	smallint	小整型：小的整数(占 16 位二进制)
	mediumint	中整型：中等长度的整数(占 24 位二进制)
	int(integer)	整型：整数类型(占 32 位二进制)
小数	float	单精度浮点数，占 4 个字节
	double	双精度浮点数，占 8 个字节
日期	time	表示时间类型
	date	表示日期类型
	datetime	同时可以表示日期和时间类型
字符串	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 字节

查看表

-- 查看某个数据库中的所有表
SHOW TABLES;
-- 查看 db1 数据库中的所有表
use db1;
show tables;

-- 查看表结构
DESC 表名;
-- 查看 student 表的结构
desc student;

-- 查看创建表的 SQL 语句
SHOW CREATE TABLE 表名;
-- 查看 student 的创建表 SQL 语句
show create table student;

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

CREATE TABLE 新表名 LIKE 旧表名;
-- 创建一个 s1 的表与 student 结构相同
create table s1 like student;

删除表

-- 直接删除表
DROP TABLE 表名;
-- 直接删除表 s1 表
drop table s1;

--判断表是否存在，如果存在则删除表
DROP TABLE IF EXISTS 表名;
-- 判断表是否存在并删除 s1 表
drop table if exists `create`;

修改表结构

-- 添加表列 ADD
ALTER TABLE 表名 ADD 列名 类型;
--为学生表添加一个新的字段 remark,类型为 varchar(20)
alter table student add remark varchar(20);

-- 修改列类型 MODIFY
-- 将 student 表中的 remark 字段的改成 varchar(100)
alter table student modify remark varchar(100);

-- 修改列名 CHANGE
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
-- 将 student 表中的 remark 字段名改成 intro，类型 varchar(30)
alter table student change remark intro varchar(30);

--删除列 DROP
ALTER TABLE 表名 DROP 列名;
--删除 student 表中的字段 intro
alter table student change remark intro varchar(30);

--修改表名
RENAME TABLE 表名 TO 新表名;
--将学生表 student 改名成 student2
rename table student to student2;

--修改字符集 character set
ALTER TABLE 表名 character set 字符集;
--将 student2 表的编码修改成 gbk
alter table student2 character set gbk;

DML 操作表中的数据

插入记录

INSERT [INTO] 表名 [字段名] VALUES (字段值)
--插入全部字段
--所有的字段名都写出来
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3);
--不写字段名
INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);

--插入部分数据
INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);
--注：没有添加数据的字段会使用 NULL

--插入所有的列，向学生表中
insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');

-- 插入所有列
insert into student values (3, '孙悟饭', 18, '男', '龟仙人洞中');
-- 如果只插入部分列，必须写列名
insert into student (id,name,age) values (3, '孙悟饭', 18);

更新表记录

UPDATE 表名 SET 列名=值 [WHERE 条件表达式]

--不带条件修改数据
UPDATE 表名 SET 字段名=值; -- 修改所有的行
-- 不带条件修改数据，将所有的性别改成女
update student set sex = '女';

-- 带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
-- 带条件修改数据，将 id 号为 2 的学生性别改成男
update student set sex='男' where id=2;
-- 一次修改多个列，把 id 为 3 的学生，年龄改成 26 岁，address 改成北京
update student set age=26, address='北京' where id=3;

删除表记录

DELETE FROM 表名 [WHERE 条件表达式]

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

--带条件删除数据
DELETE FROM 表名 WHERE 字段名=值;
-- 带条件删除数据，删除 id 为 1 的记录
delete from student where id=1;

--使用 truncate 删除表中所有记录
TRUNCATE TABLE 表名;

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

DQL 查询表中的数据

SELECT 列名 FROM 表名 [WHERE 条件表达式]
--使用*表示所有列
SELECT * FROM 表名;
--查询所有的学生：
select * from student;


--查询指定列的数据,多个列之间以逗号分隔
SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
--查询 student 表中的 name 和 age 列
select name,age from student;

指定列的别名进行查询

--对列指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
-- 使用别名
select name as 姓名,age as 年龄 from student;

--对列和表同时指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
-- 表使用别名
select st.name as 姓名,age as 年龄 from student as st

清除重复值

查询指定列并且结果不出现重复数据
SELECT DISTINCT 字段名 FROM 表名;

-- 查询学生来至于哪些地方
select address from student;
-- 去掉重复的记录
select distinct address from student;

查询结果参与运算

某列数据和固定值运算
SELECT 列名 1 + 固定值 FROM 表名;
某列数据和其他列数据参与运算
SELECT 列名 1 + 列名 2 FROM 表名;

-- 给所有的数学加 5 分
select math+5 from student;
-- 查询 math + english 的和
select * from student;
select *,(math+english) as 总成绩 from student;
-- as 可以省略
select *,(math+english) 总成绩 from student;

条件查询

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