MySQL基础语法

发表于 分类于
MySQL基础语法

MySQL基础语法

一、数据库的介绍

1.1 数据库概述

数据库，顾名思义就是数据的仓库，用于保存数据的地方，回顾一下我们之前的数据是保存在哪里呢？

  • 1）内存：我们声明的变量、数组、集合等都是保存在内存中；缺点：当程序运行结束后，数据就丢失了
  • 2）文件：我们之前学过IO流，可以把数据存储到文件上；缺点：数据管理非常不方便
  • 3）数据库管理软件：把数据存储到磁盘上，并且提供强大的SQL语句使得我们管理数据非常方便

1.2 常见数据库

https://db-engines.com/en/ranking：权威的数据库热度排行榜

  • Microsoft SQL Server：微软开发的数据库产品，主要服务于中小型企业；

  • MySQL：最早由瑞典MySQL AB 公司开发，后来Sun公司收购了MySQL，Oracle又收购了Sun公司，现属于Oracle旗下产品，主要服务于中大型企业；

  • Oracle：Oracle公司开发的数据库产品，大型数据库，主要服务于大型企业；

1.3 登录MySQL数据库

安装【MySQL的安装与卸载】文档在系统中安装一个MySQL；安装成功后在系统的服务列表中会有一个MySQL服务（默认是开启状态）

1）登录方式一：打开cmd命令窗口：

1
2
3
mysql -u用户名 -p
回车
输入密码

2）登录方式二：打开cmd命令窗口：

1
2
3
4
mysql -u用户名 -p密码

示例:
mysql -uroot -padmin

1.4 Navicat图形化工具

Navicat是一套可创建多个连接的数据库管理工具，用以方便管理 MySQL、Oracle、PostgreSQL、SQLite、SQL Server、MariaDB 和/或 MongoDB 等不同类型的数据库，并支持管理某些云数据库，例如阿里云、‎腾讯云。Navicat 的功能足以符合专业开发人员的所有需求，但是对数据库服务器初学者来说又相当容易学习。Navicat 的用户界面 (GUI) 设计良好，让你以安全且简单的方法创建、组织、访问和共享信息。

1.4.1 使用Navicat连接数据库

打开Navicat：

建立一个MySQL连接：

1.5 MySQL与数据库的关系

其实严格意义上来说MySQL并不是一个”数据库”，他只是一个数据库管理软件，也叫数据库管理系统（DBMS），里面可以有非常多的数据库，数据库里面通常会建立表来存储数据，数据又是以行的形式存储在表中的，他们的关系大致如下：

1.5.1 数据库的结构：

我们安装好的MySQL是一个数据库管理系统，英文：DBMS(Database Management System)，是一个服务器端的软件

  1. 一个数据库管理系统中包含多个数据库

  2. 一个数据库中包含多张表

  3. 一张表包含多条记录

二、SQL语句简介

Structured Query Language：结构化查询语言，简称SQL；是一种通用的数据库查询语言，不只在MySQL中可以使用，其它数据库也可以使用。但不同的数据库之间SQL语句会有一些差别。

2.1 SQL语句分类

  1. Data Definition Language：简称DDL；数据定义语言，用于建库，建表等操作

  2. Data Manipulation Language：简称DML；数据操纵语言，用于对记录的增删改操作

  3. Data Query Language：简称DQL；数据查询语言，用于查询记录

  4. Data Control Language：简称DCL；数据控制语言，用于对用户进行操作，如：创建用户，给用户权限

2.1 MySQL注释

  1. 结尾：正常每行语言以分号结尾。但在Navicate客户端软件中，如果运行的时候只选择一行，可以省略。

  2. 大小写： 不区分大小写，大小写是一样的。SQL规范关键字建议使用大写。

  3. 3种注释：

注释的语法 说明
# 单行注释，但只在mysql中起作用
–空格 单行注释，通用的注释方式
/* */ 多行注释，通用的注释方式

三、DDL操作数据库

3.1 创建数据库

  • 创建数据库
1
2
3
CREATE DATABASE 库名;

create database db01;
  • 判断数据库是否已经存在，不存在则创建数据库
1
2
3
CREATE DATABASE IF NOT EXISTS 库名

create database if not exists db01;
  • 创建数据库并指定字符集
1
2
3
CREATE DATABASE 库名 CHARACTER SET 字符集

create database db01 character set utf8;

3.2 查看数据库

1
2
3
4
5
-- 查看所有的数据库
show databases;

-- 查看某个数据库的DDL语句
show create database db01;

3.3 修改数据库编码

  • 修改数据库默认的字符集
1
2
3
ALTER DATABASE 数据库 CHARACTER SET 字符集

alter database db01 character set utf8;

3.4 删除数据库

1
2
3
DROP DATABASE 数据库名;

drop database db01;

3.5 使用数据库

3.5.1 查看正在使用的数据库

1
select DATABASE();

3.5.2 使用/切换数据库

1
2
3
USE 数据库名;

use db01;

四、DDL操作表结构

操作表之前，先使用某个数据库。

4.1 创建表

4.1.1 创建表的格式

1
2
3
4
5
6
7
8
create table 表名(
	字段名 字段类型,
	字段名 字段类型,
    字段名 字段类型
);


create table 表名(字段名 字段类型,字段名 字段类型);

字段名就是表中的列名，字段类型指数据类型

4.2 MySQL数据类型

4.2.1 常使用的数据类型如下：

分类 类型名称 类型说明
整数 tinyInt 微整型：很小的整数(占8位二进制，1个字节)
smallint 小整型：小的整数(占16位二进制，2个字节)
mediumint 中整型：中等长度的整数(占24位二进制，3个字节)
int(integer) 整型：整数类型(占32位二进制，4个字节)
bigint 大整型：占64位二进制，8个字节
小数 float 单精度浮点数，占4个字节
double 双精度浮点数，占8个字节
decimal(m,n) 数值类型，m表示数值的长度，n表示小数的位数
日期 time 表示时间类型
date 表示日期类型
datetime 同时可以表示日期和时间类型
字符串 char(m) 固定长度的字符串，无论使用几个字符都占满全部，M为0~255之间的整数 如：char(20)，实际使用只用了1个字符，也占用20个字符
varchar(m) 可变长度的字符串，使用几个字符就占用几个，M为0~65535之间的整数。 如：varchar(20)，这个字符串最长是20，大于20会报错。 使用几个，占几个字符。

4.2.2 具体操作:

1
2
3
4
5
6
7
8
9
10
-- 创建student表包含id整数,name变长字符串长20,sex性别定长型1,birthday字段日期类型

use db01;

create table emp (
    id int,
    `name` varchar(20), -- 重音符号：在Esc下面，~ 在同一个键
    sex char(1),
	address varchar(20)
);

4.3 查看表

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

1
show tables;

4.3.2 查看表结构

1
2
3
desc 表名;

desc emp;

4.3.3 查看创建表的SQL语句

1
2
3
show create table 表名;

show create table emp;

4.3.4 复制表结构

1
2
3
4
5
6
7
CREATE TABLE 新表 LIKE 旧表;

-- 创建e1表，e1表结构和emp表结构相同
create table e1 like emp;

-- 查看e1表的表结构
desc e1;

4.4 删除表

4.4.1 直接删除表

1
2
3
DROP TABLE 表名; 

drop table e1;

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

1
2
3
DROP TABLE IF EXIST 表名;

drop table if exists e1;

4.5 修改表结构

4.5.1 添加表列ADD

1
ALTER TABLE 表名 ADD 列名 类型;
1
2
3
-- 为学生表添加一个新的字段age,类型为int

alter table emp add age int;

4.5.2 修改列类型MODIFY

  • 只修改数据类型
1
2
3
ALTER TABLE 表名 MODIFY 列名 新的类型;

alter table emp modify address varchar(100);

4.5.3 修改列名 CHANGE

  • 既修改字段名又修改类型
1
2
3
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

alter table emp change address addr varchar(80);

4.5.4 删除列 DROP

1
2
3
ALTER TABLE 表名 DROP 列名;

alter table emp drop age;

4.5.5 修改表名

  • 注：MySQL中没有直接修改库名的语句
1
2
3
4
5
6
7
RENAME TABLE 表名 TO 新表名;

-- 将emp表改为employee
rename table emp to employee;

-- 将employee表改为emp
rename table employee to emp;

4.5.6 修改字符集character set

1
2
3
4
5
6
7
8
9
10
ALTER TABLE 表名 character set 字符集;

-- 查看emp表的建表语句
show create table emp;

-- 修改emp表的字符集为gbk
alter table emp character set gbk;

-- 修改emp表的字符集为utf8
alter table emp character set utf8;

五、DML操作表中的数据

5.1 插入记录

5.1.1 插入全部字段

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

insert into emp values(1,'张三','男','河南汤阴',39);

5.1.2 插入部分数据

1
2
3
4
5
6
7
INSERT INTO 表名 (字段名1,字段名2) VALUES (值1，值2);

-- 插入指定的字段
insert into emp(id,name,sex) values(2,'李四','男');

-- 也可以不按照列的顺序插入
insert into emp(id,name,addr) values(3,'王五','湖南邵阳');

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

5.1.3 插入多条记录

1
2
3
4
5
6
7
INSERT INTO 表名 values (值1,值2),(值1,值2),(值1,值2);

INSERT INTO emp
VALUES
	( 4, '小红', '女', '四川南充', 25 ),
	( 5, '小明', '女', '河南平顶山', 24 ),
	( 6, '小李', '男', '陕西榆林', 20 );

5.1.4 插入记录规范：

  • 1）数据的大小应在列的规定范围内，例如：不能将一个长度为30的字符串加入到长度为20的列中。

  • 2）在values中列出的数据位置必须与被加入的列的排列位置相对应。

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

  • 4）不指定列或使用null，表示插入空值。

5.2 更新表记录

5.2.1 不带条件修改数据

  • 语法：
1
2
3
UPDATE 表名 SET 字段名=值,字段名=值;

update emp set sex='女',addr='广西来宾';

tips：不带条件表示更新所有的记录

5.2.2 带条件修改数据

  • 语法：
1
2
3
4
5
6
7
UPDATE 表名 SET 字段名=值 WHERE 条件表达式;

-- 一次修改一列
update emp set addr='甘肃白银' where id=2;

-- 一次修改多列
update emp set sex='男',addr='陕西忻州' where id=5;

5.3 删除表记录

5.3 不带条件删除数据

1
2
3
DELETE FROM 表名;

delete from emp;

tips：不带条件的删除会删除表中的所有记录，慎用！

准备数据：

1
2
3
4
5
6
7
INSERT INTO emp
VALUES
	( 1, '小明', '男', '山西铜川', 19 ),
	( 2, '小兰', '女', '江西萍乡', 20 ),
	( 3, '小龙', '男', '云南临沧', 23 ),
	( 4, '小红', '女', '贵州毕节', 18 ),
	( 5, '小龙', '男', '吉林白城', 22 );

5.3.2 带条件删除

  • 语法：
1
2
3
DELETE FROM 表名 WHERE 条件表达式;

delete from emp where id=5;

5.3.3 清空表

  • 语法：
1
2
3
truncate 表名;

truncate emp;

tips：先删除表结构,再创建一个相同的表结构 相当于drop table emp，再create table emp

5.4 增删改方法小结

功能 语句
添加记录(增) INSERT INTO 表名 VALUES (值1，值2)
更新记录(改) UPDATE 表名 SET 字段名=值 WHERE 条件表达式
删除记录(删) DELETE FROM 表名 WHERE 条件表达式

六、DQL查询表中的数据

6.1 查询表所有行和列的数据

建立测试数据：

1
2
3
4
5
6
7
INSERT INTO emp
VALUES
	( 1, '小明', '男', '辽宁盘锦', 26 ),
	( 2, '小兰', '女', '安徽亳州', 21 ),
	( 3, '小龙', '男', '湖北随州', 24 ),
	( 4, '小红', '女', '西藏林芝', 25 ),
	( 5, '小龙', '男', '福建宁德', 22 );
  • 语法
1
2
3
SELECT * FROM 表名;

select * from emp;

6.2 查询指定列

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

  • 语法：
1
2
3
SELECT 字段名1, 字段名2, 字段名3, ... FROM 表名;

select id,name,addr from emp;

6.3 指定列的别名进行查询

6.3.1 使用关键字AS

  • 使用别名的好处：可以让查询结果，列名显示为汉字，可读性好一些。后期也可以给表起别名，主要用于多表查询。

6.3.2 对列指定别名

  • 语法：
1
2
3
SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名;

select id as 编号,name as 姓名,addr 地址 from emp;

tips：as可以省略

6.4 清除重复值

添加数据：

1
2
3
4
INSERT INTO emp
VALUES
	( 1, '小陈', '男', '青海西宁', 28 ),
	( 7, '小辉', '男', '浙江衢州', 20 );

查询表数据：

  • 清除重复数据语法：
1
SELECT DISTINCT 字段名 FROM 表名;

去重id相同的行：

1
2
-- 只去重id相同的行
select distinct id from emp;

去重sex相同的行：

1
select distinct sex from emp;

6.5 查询结果参与运算

6.5.1 某列数据和固定值运算

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

select id,name,addr,age+1 from emp;

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

1
2
3
SELECT 列名1 + 列名2 FROM 表名; 
 
select id,name,addr,id+age from emp;

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

七、DQL条件查询

7.1 准备数据

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE student (  
    id int, -- 编号  
    `name` varchar(20), -- 姓名  
    age int, -- 年龄  
    sex char(1), -- 性别  
    address varchar(100), -- 地址  
    math int, -- 数学  
    english int -- 英语  
);  

-- 插入数据
insert into student values(1,'小明',20,'男','河北衡水',90,100);
insert into student values(2,'小红',26,'女','湖南衡阳',88,69);
insert into student values(3,'小龙',18,'男','内蒙包头',78,80);
insert into student values(4,'小丽',24,'女','陕西宝鸡',95,80);
insert into student values(5,'张三',22,'男','江西上饶',80,90);
insert into student values(6,'李四',19,'男','广西北海',100,95);
insert into student values(7,'王五',24,'男','黑龙江大庆',90,95);

7.2 比较运算符

比较运算符 说明
>、<、<=、>=、=、<> 不等于，在mysql中也可使用!=

练习：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 查询年龄大于25岁的学生

-- 查询数学成绩大于等于80分的学生

-- 查询英语成绩低于60的学生

-- 查询数学成绩大于英语成绩的学生

-- 查询性别不为男的学生

-- 查询性别不为男的学生(第二种写法)

-- 查询年龄大于25岁的学生
select * from student where age>25;

-- 查询数学成绩大于等于80分的学生
select * from student where math>=80;

-- 查询英语成绩小于等于90的学生
select * from student where english<=90;

-- 查询数学成绩大于英语成绩的学生
select * from student where math>english;

-- 查询性别不为男的学生
select * from student where sex!='男';

-- 查询性别不为男的学生(第二种写法)
select * from student where sex<>'男';

7.3 逻辑运算符

逻辑运算符 说明
and 或 && 与，全真为真
or 或 || 或，见真为真
not 或 ! 非，取反

练习：

1
2
3
4
5
6
7
8
-- 查询年龄大于22岁并且性别为男的学生
select * from student where age>22 and sex='男';

-- 查询数学成绩大于80并且性别为女的学生
select * from student where english>80 and sex='女'

-- 查询数学成绩大于90或者英语成绩大于90的学生
select * from student where math>60 or english>60;

7.4 in关键字

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

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

练习：

1
2
3
4
5
-- 查询id是1或2或5的学生
select * from student where id in (1,2,5);

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

7.5 范围查询

  • 语法：
1
BETWEEN 值1 AND 值2

表示从值1到值2范围，包头又包尾，值1要小于值2，类似于>=值1 and <=值2

1
2
3
4
-- 查询english成绩大于等于60，且小于等于90的学生
select * from student where english between 60 and 90;

select * from student where english >=60 and english <=90;

7.6 like 关键字

LIKE表示模糊查询

  • 语法：
1
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

7.6.1 MySQL通配符

通配符 说明
% 匹配零个或多个字符
_ 匹配一个字符 
1
2
3
4
5
6
7
8
-- 查询地址为'广'开头城市的学生
select * from student where address like '广%';

-- 查询地址中包含有'西'城市的学生
select * from student where address like '%西%';

-- 查询六个字地址城市的学生
select * from student where address like '_____';		-- 五个_

7.7 查询为空的列IS NULL

1
2
3
4
5
6
7
8
-- 查询性别成绩为NULL的学生
select * from student where sex is null;

-- 错误的
select * from student where sex = null;

-- 查询英语成绩不为NULL的学生
select * from student where sex is not null;

7.8 条件查询小结

比较运算符 说明
>、<、<=、>=、=、<>、!= 不等于
BETWEEN…AND 查询一个范围，包头又包尾
IN(集合) 条件在集合或数组中任何一个
LIKE ‘张%’ 模糊查询 % 匹配多个字符 _ 匹配1个字符
IS NULL 为空

八、MySQL字符集

8.1 DOS命令行下汉字乱码的问题

有的同学可能在MySQL控制台查询时，中文会出现乱码，如下图：

8.1.1 字符集相关参数：

在MySQL系统中维护了几个关于字符集的变量，我们通过如下命令可以查看到：

1
show variables like '%character%';

和我们有关的有如下参数：

  • character_set_client：客户端采用的字符集

  • character_set_connection：MySQL连接采用的字符集

  • character_set_database：MySQL数据库存储采用的字符集

  • character_set_results：SQL语句结果集采用的字符集

  • character_set_server：MySQL服务器采用的字符集

  • character_set_system：MySQL系统采用的字符集

8.1.2 乱码的原因

在windows下，cmd窗口默认采用的是GBK字符集编码的，MySQL将数据的结果全部以UTF8编码（character_set_results），然后数据到了cmd窗口时，cmd窗口以GBK进行解码，因此显示乱码，我们可以更改character_set_results参数：

1
2
3
4
5
-- 只在当前窗口剩下,窗口关闭后失效
set character_set_results=gbk;

-- 再次查询数据
select * from student;

此时数据的结果编码为GBK，windows窗口的编码也为GBK，因此显示正常；

8.1.3 客户端编码

我们更改解决了cmd窗口中文显示的乱码，但还有地方有问题；

我们尝试在cmd窗口执行如下SQL语句：

1
insert into student(id,name) values(8,"小军");

发现插入不进去，Incorrect string value

因为我们的cmd窗口是采用gbk字符集，我们客户端（character_set_client）采用的是utf8字符集，两个码表不一致，在我们输入中文完毕后，数据被cmd窗口用GBK编码了，但MySQL以为客户端采用的是UTF8编码，因此采用UTF8进行解码，然后进行数据的传递（character_set_connection）

我们可以修改character_set_client参数：

1
2
3
4
5
6
7
set character_set_client=gbk;

-- 插入数据
insert into student(id,name) values(8,"小军");

-- 再次查询数据
select * from student;

8.1.4 数据传递过程

数据首先被cmd窗口编码，之后MySQL服务器会根据character_set_client解码，然后根据character_set_connection编码传递到MySQL服务器，MySQL服务器最终按照character_set_server编码进行存储；

一般来说character_set_client、character_set_connection、character_set_results都应该保持一致，我们可以通过如下命令来统一设置这三个参数的值：

1
set names gbk;