MySQL基础语法

MySQL作为一个常用的关系型数据库，其语法也是比较简单，但是知识点较多，所以为了巩固记忆，写此文章，方便复习。本文直接上干货，历史之类的就不过多介绍。

数据库的类型

MySQL数据库存储数据时，不同的数据类型决定了 MySQL存储数据方式的不同。为此，MySQL数据库提供了多种数据类型，其中包括整数类型、浮点数类型、定点 数类型、日期和时间类型、字符串类型、二进制…等等数据类型。

1.整数类型

2.浮点数类型和定点数类型

在MySQL数据库中使用浮点数和定点数来存储小数。

浮点数的类型有两种：

单精度浮点数类型：FLOAT

双精度浮点数类型：DOUBLE

定点数类型：DECIMAL（M,D）

• DECIMAL类型的有效取值范围是由M和D决定的。其中，M表示的是数据的长 度，D表示的是小数点后的长度。比如，将数据类型为DECIMAL(10,2)的数据10.54321 插人数据库后显示的结果为10.54.

3.字符串类型

表示字符串的类型有两种：

CHAR：当数据为CHAR(M)类型时，不管插入值的长度是实际是多少它所占用的存储空间都是M个字节。

VARCHAR：VARCHAR(M)所对应的数据为所占用的字节数为实际长度加1

例如：

4.文本类型

文本类型用于表示大文本数据，例如，文章内容、评论、详情等，它的类型分为如下4种：

5.日期与时间类型

MySQL提供的表示日期和时间的数据类型分别是：

YEAR：表示年份，日期格式：YYYY，取值范围是1901~2155

DATE：表示日期和时间，显示形式为：YYYY-MM-DD，取值范围：1000-01-01~9999-12-31

TIME：表示日期和时间，显示形式为：HH:MM:SS,HH表示小时，MM表示分钟，SS表示秒，使用CURRENT_TIME或NOW()输人当前系统时间。

DATETIME：表示日期和时间，它的显示形式为YYYY-MM-DD HH: MM:SS，使用NOW来输人当前系统的日期和时间。

TIMESTAMP：表示日期和时间，它的显示形式与DATETIME相同但取值范围比DATETIME小。

• 使用CURRENT_TIMESTAMP输人系统当前日期和时间。
• 输人NULL时系统会输人系统当前日期和时间。
• 无任何输人时系统会输入系统当前日期和时间。

6.二进制类型

在MySQL中常用BLOB存储二进制类型的数据，例如：图片、PDF文档等。BLOB类型分为如下四种：

数据库、表的基本操作

1、数据库

创建数据库：

1

create database 数据库名称;

查看数据库基本信息：

1

show create database 数据库名称;

删除数据库：

1

drop database 数据库名称;

修改数据库字符集：

1

alter database db1 character set gbk;

使用数据库 or 切换数据库

1

use db1;

2.数据表的基本操作

创建完数据库后一般来说就是要开始创建表来存储数据了。

PS：如果你使用的是命令行来操作MySQL的话，在创建表前请使用USE 数据库名; 来指定是在哪个数据库中进行操作，否则会抛出错误。

创建表：

1
2
3
4
5
6

create table 表名(
	字段1 字段类型, 
	字段2 字段类型,
	… 
	字段n 字段类型
);

例如：创建一个学生表

1
2
3
4
5
6

create table student( 
	id int, 
	name varchar(20), 
	gender varchar(10), 
	birthday date 
);

查看表基本信息（查看建表SQL语句）：

1

show create table student;

查看表的字段信息：

1

desc student;

3.修改数据表

修改表名：

1

alter table student rename to stu;

修改字段名 MySQL命令：

1

alter table stu modify sname int;

增加字段 MySQL命令：

1

alter table stu add address varchar(50);

删除字段 MySQL命令：

1

alter table stu drop address;

删除数据表:

1

drop table 表名;

2023.9.4 20:46 写到这里，听会歌喝口水休息一会儿。

数据库的约束

约束条件其实就是限制一些不是表需要的数据插入到表中去，保护数据库中的数据是唯一的，可以理解为限制条件，下面列举了常用的约束条件。

1.主键约束

主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的。
主键约束基本语法：

1

字段名 数据类型 primary key;

创建表的时候添加主键：

1
2
3
4

create table student(
id int primary key,
name varchar(20)
);

or

1
2
3
4
5

create table student01( 
	id int,
	name varchar(20), 
	primary key(id) 
);

2.非空约束

所谓非空约束，就是让字段的值不能是空的。

1

字段名 数据类型 NOT NULL;

example：

1
2
3
4
5

create table student02( 
	id int,
	name varchar(20) not null,
	sex int not null
);

3.默认值约束

默认值约束就是给字段添加一个默认值。

1

字段名 数据类型 DEFAULT 默认值；

4.唯一性约束

唯一性约束是用UNIQUE，用于保证数据表中字段的唯一性，表中字段的值不能重复出现。

1

字段名 数据类型 UNIQUE;

5.外键约束

外键约束即FOREIGN KEY常用于多张表之间的约束。

1
2
3
4

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
-- 将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

exmaple:

1. 创建一个学生表 MySQL命令：

1
2
3
4

create table student05(
	id int primary key,
	name varchar(20)
);

2. 创建一个班级表 MySQL命令：

1
2
3
4

create table class(
	classid int primary key,
	studentid int
);

3. 学生表作为主表，班级表作为副表设置外键， MySQL命令：

1

alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);

删除外键

语法如下：

1

alter table 从表名 drop foreign key 外键名；

示例：删除外键 MySQL命令：

1

alter table class drop foreign key fk_class_studentid;

外键约束需要注意的细节：

1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

增删改查

数据库的增删改查是指对数据库中的记录进行增加、删除、修改和查询的操作。其中，增加操作包括INSERT语句，删除操作包括DELETE语句，修改操作包括UPDATE语句，查询操作包括SELECT语句。

INSERT 插入

MySQL中的插入语句用于向数据库表中添加新记录。INSERT INTO语句的语法如下：

1
2

INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (value1, value2, value3, ...);

其中，table_name是要插入数据的表名，column1、column2、column3等是表中的列名，value1、value2、value3等是要插入的数据值。

假设我们有一个名为students的表，其中包含id、name和age三个列，我们要向其中插入一条新记录，可以使用以下语句：

1

INSERT INTO students (id, name, age) VALUES (1, '张三', 18);

同时插入多条数据：

1
2
3
4

INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (value1, value2, value3, ...), 
(value4, value5, value6, ...), 
(value7, value8, value9, ...);

DELETE 删除

MySQL中的删除语句用于从数据表中删除记录。可以使用以下语法：

1

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

example:

删除所有age等于14的数据：

1

delete from student where age=14;

删除全部数据：

1
2
3
4

DELETE FROM 表名;

-- 例如：删除学生表
DELETE FROM student;

TRUNCATE和DELETE都是MySQL中删除数据的语句，但是它们之间有一些区别。

• TRUNCATE是删除表中的所有数据，但不删除表结构，速度比DELETE快。DELETE可以带WHERE子句，条件删除；而TRUNCATE只能删除整个表。
• DELETE是可以恢复删除的数据，而TRUNCATE和DROP不能恢复删除的数据。
• 执行速度方面，DROP > TRUNCATE >> DELETE。

UPDATE 更新

用于更新字段中的内容。

1

UPDATE 表名 SET 字段名1 = 值1[,字段名2 =值2,…] [WHERE 条件表达式];

字段名1、字段名2…用于指定要更新的字段名称；值1、值 2…用于表示字段的新数据；WHERE 条件表达式 是可选的，它用于指定更新数据需要满足的条件。

example：

将name为tom的记录的age设置为20并将其gender设置为female

1

update student set age=20,gender='female' where name='tom';

将所有记录的age设置为18

1

update student set age=18;

2023.9.4 21:27 今天先不写了，去背会儿单词。

数据表查询

1.简单查询

我们新建立了一个数据表

1
2
3
4
5
6

CREATE TABLE student ( 
	sid CHAR(6), 
	sname VARCHAR(50),
	age INT, 
	gender VARCHAR(50) DEFAULT 'male' 
);

查询表中所有字段：

1

select * from student;

查询指定字段（sid，sname）

1

select sid,sname from student;

常数查询：

example：在后面加上查询日期

1

select sid,sname,'2023-09-05' from student;

结果会在最后一列每一行都增加日期。

从查询结果中过滤重复数据

1

select distinct gender from student;

查询前加上 distinct 即可。

算术运算符

在SELECT查询语句中可以使用加减乘除运算符。

1

select sname,age+10 from student;

2.聚合函数

有些公司会有这种需求：统计某个字段的最大值、最小值、 平均值等等。

为了简化操作，MySQL为我们提供了聚合函数来实现这些操作。

聚合函数意思为把多行的数据变为一行。

count()

统计表中数据的行数或者统计指定列其值不为NULL的数据个数

查询学生总数：

1

select count(*) from student;

max()

计算指定列的最大值，如果指定列是字符串类型则使用字符串排序运算

查询年龄最大学生：

1

select sname,max(age) from student;

min()

计算指定列的最小值，如果指定列是字符串类型则使用字符串排序运算

查询年龄最小学生：

1

select sname,min(age) from student;

sum()

计算指定列的数值和，如果指定列类型不是数值类型则计算结果为0

查询表中所有学生年龄总和：

1

select sum(age) from student;

avg（）

计算指定列的平均值，如果指定列类型不是数值类型则计算结果为0

查询学生平均年龄：

1

select avg(age) from student;

3.时间函数

可自行测试：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

4.字符串函数

1
2
3
4
5
6
7
8

--连接函数 
SELECT CONCAT () 

--查看字符串第一次出现位置
SELECT INSTR (); 

--统计长度 
SELECT LENGTH();

CONCAT()函数：这个函数用于连接两个或更多的字符串。

其基本语法是 CONCAT(string1, string2, …, string_n)，其中string1、string2等为要连接的字符串。

例如：SELECT CONCAT('Hello', ' ', 'World'); 将返回 “Hello World”。

INSTR()函数：这个函数用于返回子字符串在字符串中第一次出现的位置。

如果子字符串不存在于字符串中，则返回0。其基本语法是 INSTR(string, substring)，其中string是要搜索的字符串，substring是要查找的子字符串。

例如：SELECT INSTR('Hello World!', 'World'); 将返回 7，因为 “World” 在 “Hello World!” 中第一次出现的位置是 7。

LENGTH()函数：这个函数用于返回字符串的长度（字符数）。

其基本语法是 LENGTH(string)，其中string是要计算长度的字符串。

例如：SELECT LENGTH('Hello World!'); 将返回 12，因为 “Hello World!” 包含 12 个字符。

5.数学函数

1
2
3
4
5
6

-- 绝对值 
SELECT ABS(-136); 
-- 向下取整 
SELECT FLOOR(3.14); 
-- 向上取整 
SELECT CEILING(3.14);

条件查询

数据库中存有大量数据，我们可根据需求获取指定的数据。此时，我们可在查询语句中通过WHERE子句指定查询条件对查询结果进行过滤。

关系运算符

在WHERE中可使用关系运算符进行条件查询

查询年龄等于或大于17的学生的信息 MySQL命令：

1

select * from student where age>=17;

IN关键字查询

IN关键字用于判断某个字段的值是否在指定集合中。如果字段的值恰好在指定的集合中，则将字段所在的记录将査询出来。

查询sid为S_1002和S_1003的学生信息 MySQL命令：

1

select * from student where sid in ('S_1002','S_1003');

查询sid为S_1001以外的学生的信息 MySQL命令：

1

select * from student where sid not in ('S_1001');

BETWEEN AND关键字查询

BETWEEN AND用于判断某个字段的值是否在指定的范围之内。如果字段的值在指定范围内，则将所在的记录将查询出来

查询15到18岁的学生信息 MySQL命令：

1

select * from student where age between 15 and 18;

查询不是15到18岁的学生信息 MySQL命令：

1

select * from student where age not between 15 and 18;

使用空值查询

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。请注意：空值NULL不同于0，也不同于空字符串

查询sname不为空值的学生信息 MySQL命令：

1

select * from student where sname is not null;

使用AND关键字查询

在MySQL中可使用AND关键字可以连接两个或者多个查询条件。

查询年纪大于15且性别为male的学生信息 MySQL命令：

1

select * from student where age>15 and gender='male';

使用OR关键字查询

在使用SELECT语句查询数据时可使用OR关键字连接多个査询条件。在使用OR关键字时，只要记录满足其中任意一个条件就会被查询出来

查询年纪大于15或者性别为male的学生信息 MySQL命令：

1

select * from student where age>15 or gender='male';

使用LIKE关键字查询

LIKE通常与%（表示任意多个字符）和_（表示一个字符）通配符一起使用,可以判断两个字符串是否相匹配。

查询所有以”张”开头的姓名：

1

sql SELECT * FROM users WHERE name LIKE '张%';

查询名字中包含”小”的所有用户：

1

SELECT * FROM users WHERE name LIKE '%小%';

查询名字长度为3个字符的所有用户：

1

SELECT * FROM users WHERE name LIKE '___';

查询电话号码以”138”开头的所有用户：

1

SELECT * FROM users WHERE phone LIKE '138%';

请注意，LIKE操作符对大小写敏感。如果需要进行不区分大小写的搜索，可以使用LOWER()或UPPER()函数将列值和搜索模式转换为相同的大小写，例如：

1

SELECT * FROM users WHERE LOWER(name) LIKE '%张%';

使用LIMIT限制查询结果的数量

当执行查询数据时可能会返回很多条记录，而用户需要的数据可能只是其中的一条或者几条

查询学生表中年纪最小的3位同学 MySQL命令：

1

select * from student order by age asc limit 3;

使用GROUP BY进行分组查询

将表中的数据进行分组，再进行查询等操作。可以理解为通过GROUP BY将原来的表拆分成了几张小表。

GROUP BY和聚合函数一起使用

统计各部门员工个数 MySQL命令：

1

select count(*), departmentnumber from employee group by departmentnumber;

统计部门编号大于1001的各部门员工个数 MySQL命令：

1

select count(*), departmentnumber from employee where departmentnumber>1001 group by departmentnumber;

GROUP BY和聚合函数以及HAVING一起使用

统计工资总和大于8000的部门 MySQL命令：

1

select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

使用ORDER BY对查询结果排序

从表中査询出来的数据可能是无序的或者其排列顺序不是我们期望的。为此，我们可以使用ORDER BY对查询结果进行排序

其语法格式如下所示：

1
2
3

SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];

在该语法中：字段名1、字段名2是查询结果排序的依据；参数 ASC表示按照升序排序，DESC表示按照降序排序；默认情况下，按照ASC方式排序。通常情况下，ORDER BY子句位于整个SELECT语句的末尾。

查询所有学生并按照年纪大小升序排列 MySQL命令：

1

select * from student order by age asc;

查询所有学生并按照年纪大小降序排列 MySQL命令：

1

select * from student order by age desc;

表别名

在字段名后加一个as即可更名，或者不加也可以直接加一个空格。

1

select name as '姓名',id from student;

2023.9.5 11:23 记录此刻。