SQL基础语法

1. SQL的概念

Structured Query Language 结构化查询语言

1.1 SQL的作用

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

1.2 SQL语句分类

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

1.3 MySQL 的语法

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

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

2. DDL操作数据库

2.1 创建数据库

创建数据库

CREATE DATABASE 数据库名;

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

CREATE DATABASE IF NOT EXISTS 数据库名;

创建数据库并指定字符集

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

-- 直接创建数据库 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 修改数据库

修改数据库默认的字符集

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

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

alter database db3 character set utf8;

2.4 删除数据库

删除数据库的语法

DROP DATABASE 数据库名;

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

删除 db2 数据库

drop database db2

2.5 使用数据库

查看正在使用的数据库

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

使用/切换数据库

USE 数据库名;

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

3. DDL操作表结构

前提先使用某个数据库

3.1 创建表

创建表的格式

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

关键字说明：

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

3.2 MySQL数据类型

分类	类型名称	类型说明
整数	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 字节

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

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

3.3 查看表

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

SHOW TABLES;

查看表结构

DESC 表结构

查看创建表的 SQL 语句

SHOW CREATE TABLE 表名;

查看 db3 数据库中的所有表

use db3;
show tables;

查看 student 表的结构

desc student;

执行结果

查看 student 的创建表 SQL 语句

show create table student;

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

CREATE TABLE 新表名 LIKE 旧表名;

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

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

3.5 删除表

DROP TABLE 表名;

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

DROP TABLE IF EXISTS 表名;

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

与直接删除的区别:如果表不存在，不删除，存在则删除

3.6 修改表结构

添加表列 ADD

ALTER TABLE 表名 ADD 列名 类型;

修改列类型 MODIFY

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

修改列名 CHANGE

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

删除列 DROP

ALTER TABLE 表名 DROP 列名;

修改表名

RENAME TABLE 表名 TO 新表名;

修改字符集 character set

ALTER TABLE 表名 character set 字符集;

4. DML 操作表中的数据

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

4.1 插入记录

所有的字段名都写出来

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

不写字段名

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

插入部分数据

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

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

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

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

insert 的注意事项：

• 插入的数据应与字段的数据类型相同
• 数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
• 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。在 mysql 中可以使用 value，但不建议使用，功能与 values 相同。
• 字符和日期型数据应包含在单引号中。MySQL 中也可以使用双引号做为分隔符。
• 不指定列或使用 null，表示插入空值。

乱码产生的原因:

查看 MySQL 内部设置的编码

show variables like 'character%';

解决方案

修改 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.4 更新表记录

不带条件修改数据

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

带条件修改数据

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

4.5 删除表记录

不带条件删除数据

DELETE FROM 表名;

带条件删除数据

DELETE FROM 表名 WHERE 字段名=值;

使用 truncate 删除表中所有记录

TRUNCATE TABLE 表名;

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

5. DQL 查询表中的数据

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

5.1 简单查询

使用*表示所有列

SELECT * FROM 表名;

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

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

5.2 指定列的别名进行查询

使用关键字

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

语法：

对列指定别名

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

对列和表同时指定别名

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

5.3 清除重复值

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

SELECT DISTINCT 字段名 FROM 表名;

5.4 查询结果参与运算

某列数据和固定值运算

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

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

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

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

5.5 条件查询

为什么要条件查询

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

条件查询的语法

SELECT 字段名 FROM 表名 WHERE 条件;

运算符

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

逻辑运算符

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

in 关键字

in 里面的每个数据都会作为一次条件，只要满足条件的就会显示

SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);

like 关键字

LIKE 表示模糊查询

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

通配符	说明
%	匹配任意多个字符串
_	匹配一个字符