MySQL数据库SQL语句
目录
一、SQL语句类型
1、DDL
DDL(Data Definition Language,数据定义语言):用于定义数据库中的各种对象,包括数据库、表、视图、触发器等,常见的 DDL 命令有 CREATE、ALTER、DROP
2、DML
DML(Data Manipulation Language,数据操作语言):用于操作表格中的数据,进行新增、查询、更新、删除等操作,常见的 DML 命令有 SELECT、INSERT、UPDATE、DELETE
3、DCL
DCL(Data Control Language,数据控制语言):用于管理数据库的权限和安全性,包括授权、回收权限等操作,常见的 DCL 命令有 GRANT、REVOKE
4、DQL
DQL(Data Query Language,数据查询语言)是 SQL 的一个子集,主要用于查询数据库中的数据,常见的 DQL 命令包括 SELECT
二、数据库操作
1、查看
show databases;
2、创建
2.1、默认字符集
create database 数据库名称;
默认不进行改动是latin1
create database back;
2.2、指定字符集
create database 数据库名称 character set utf8;
使用uft8格式的字符集
create database bak character set utf8;
3、进入
use 数据库名称;
use bak;
4、删除
drop database 数据库名称;
drop database bak;
5、更改
库名称
进入到数据库的目录中修改数据库的名称
cd /var/lib/mysql
更改数据库名称
mv back bak
回到数据库进行查看
show databases;
字符集
ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
6、练习
创建2个数据库,一个全写,一个缩写(指定字符集)
删除缩写的,将全写的名称改为缩写的
三、数据表操作
(一)数据类型
1、数值类型
1.1、TINYINT
1个字节,范围为 -128 到 127(有符号)或 0 到 255(无符号)
可以使用 TINYINT UNSIGNED 来存储年龄(无符号)或温度(有符号)等小数值
1.2、SMALLINT
2个字节,范围为 -32,768 到 32,767(有符号)或 0 到 65,535(无符号)
可以使用 SMALLINT 存储商品数量, 或者使用 SMALLINT UNSIGNED 存储区域编号(无符号)
1.3、INT
4个字节,范围为 -2,147,483,648 到 2,147,483,647(有符号)或 0 到 4,294,967,295(无符号)
这是最常用的整数类型,它可以被用于许多方面,例如存储订单号或者用户的数量
1.4、BIGINT
8个字节
存储很大的数值,例如资金、人口等
1.5、FLOAT(M,D)
单精度浮点数,M是总位数,D是小数位数
可以使用 FLOAT(8,2) 来存储商品的价格
2、时间\日期类型
2.1、DATE
用来存储日期,格式为’YYYY-MM-DD’
可以使用 DATE 存储出生日期或者过期日期等
2.2、TIME
用来存储时间,格式为’HH:MM:SS’
可以使用 TIME 存储过去一段时间内花费的小时数,分钟数或秒数等
2.3、DATETIME
用来存储日期和时间,格式为’YYYY-MM-DD HH:MM:SS’
可以使用 DATETIME 存储订单时间或者统计报告生成时间等
2.4、TIMESTAMP
用来存储日期和时间,通常被用于记录特定事件的时间戳。使用UNIX的日期和时间格式,从1970年1月1日午夜开始计算
可以使用 TIMESTAMP 存储用户上次登录的时间戳
3、字符串类型
3.1、CHAR
用来存储定长字符串,最大长度为255个字符
可以使用 CHAR(10) 存储用户的性别、婚姻状况等数据
3.2、VARCHAR
用来存储可变长度字符串,最大长度为65535个字符
可以使用 VARCHAR(255) 存储用户输入的文本内容、地址等数据
3.3、TEXT
用来存储大型字符数据,最大长度为2^16-1个字符
可以使用 TEXT 存储文章、评论等大型文本数据
4、二进制类型
4.1、BINARY
用来存储固定长度二进制数据,最大长度为255个字节
可以使用 BINARY(16) 存储UUID
4.2、VARBINARY
用来存储可变长度二进制数据,最大长度为65535个字节
可以使用 VARBINARY(256) 存储不定长度的二进制数据,例如图片和音频等文件
4.3、BLOB
用来存储大型二进制对象数据,最大长度为2^16-1个字节
可以使用 BLOB 存储音视频等媒体文件
(二)创建
create table tables_name(
第一列 类型属性,
第二列 类型属性,
) character set utf8mb4 collate utf8mb4_unicode_ci;
character set utf8mb4 collate utf8mb4_unicode_ci 写在表之后,声明表的字符集
create table a1(
编号 int not null,
姓名 varchar(20) not null,
性别 varchar(10) not null,
出生年月 date not null
)character set utf8mb4 collate utf8mb4_unicode_ci;
create table tables_name(
第一列 类型属性,
第二列 类型属性 character set utf8mb4 collate utf8mb4_unicode_ci,
);
character set utf8mb4 collate utf8mb4_unicode_ci, 写在列之后,声明列的字符集
create table a2(
编号 int not null,
姓名 varchar(20) character set utf8mb4 collate utf8mb4_unicode_ci,
性别 varchar(10) not null
);
创建数据表时,添加约束条件
创建数据表时添加
CREATE TABLE 数据表名 (
id INT PRIMARY KEY, -- 定义 id 列为主键
name VARCHAR(20) NOT NULL,
age INT
);
create table a3(
编号 int not null primary key,
姓名 varchar(20) not null,
性别 varchar(10) not null
);
修改现有的数据表
(三)查看
1、添加表内的数据
INSERT INTO a4 (编号,姓名,性别) VALUES (1,'张三', '男'),
(2,'李四', '女'),
(3,'王五', '男'),
(4,'赵六', '男'),
(5,'小明', '男'),
(6,'小帅', '男'),
(7,'小美', '女'),
(8,'小红', '女'),
(9,'大明', '男'),
(10,'大帅', '男');
2、查看表内的数据
select * from tables_name;
select column1,column2 from tables_name where 条件;
select * from a4;
select 编号,姓名,性别 from a4 where 性别='男';
select 编号,姓名,性别 from a4 where 性别='女';
3、查看表格的属性
describe tables_name;
desc tables_name; 此为缩写命令
describe a4;
desc a4;
(四)删除
1、删除数据表
drop table tables_name;
drop table a3;
2、删除数据表数据,但是保留表结构
delete from tables_name;
(五)更改
1、数据表
1.1、名称
查看数据表目录
show tables;
更改数据表名称
alter table old_table_name rename to new_table_name;
注意事项:如果现在的表正在被其他表或程序应用,那么可能会导致无法正常引用。
检查验证
show tables;
1.2、字符集
alter table my_table convert to character set utf8mb4 collate utf8mb4_unicode_ci;
alter table a6 convert to character set utf8mb4 collate utf8mb4_unicode_ci;
2、数据表--列
1.1、名称
alter table table_name change old_name new_name 属性;
alter table a6 change age number int;
select * from a6;
1.2、属性
alter table my_table modify 修改的列 修改的属性;
alter table a6 modify name char(10);
检验数据表格属性
desc a6;
原有的表格属性
现在的表格属性
1.3、字符集
alter table users modify name varchar(50) character set utf8mb4;
这是一个SQL语句,用于修改表格"users"中的"name"字段的数据类型。它将"name"字段的数据类型从原来的varchar(50)修改为varchar(50) character set utf8mb4,其中utf8mb4是一种支持更广范围字符集的编码方式。修改字符集可以确保该字段能够存储包含特殊字符(如表情符号、不同语言的字符等)的数据。
alter table a6 modify name char(50) character set utf8mb4 not null;
这是一个SQL语句,用于修改表格"a6"中的"name"字段的数据类型和约束。它将"name"字段的数据类型从原来的char(50)修改为char(50) character set utf8mb4,其中utf8mb4是一种支持更广范围字符集的编码方式。同时,添加了"not null"约束,表示该字段不允许为空值。修改字符集可以确保该字段能够存储包含特殊字符(如表情符号、不同语言的字符等)的数据,而"not null"约束则要求该字段的值在插入或更新时不能为NULL。
(六)练习
创建两个表:
1、包含编号、姓名、性别
2、包含姓名、年龄、出生年月
四、数据操作
(一)增加数据
insert into table_name (column1, column2, ...) values (value1, value2, ...);
create table a6(
id int not null,
name varchar(20) not null,
age int not null
)character set utf8mb4 collate utf8mb4_unicode_ci;
INSERT INTO a6 (id,name,age) VALUES (1,'zhangsan', 15),
(2,'lisi', 18),
(3,'wangwu', 22),
(4,'xiaoming', 25),
(5,'xiaomei', 28);
(二)删除数据
delete from table_name where 条件;
select * from a6;
delete from a6 where id=5;
select * from a6;
(三)更改数据
update table_name set column1 = value1, column2 = value2, ... where 条件;
update a6 set id=5 where name='xiaoming';
select * from a6;
(四)查看数据
select * from tables_name;
select column1,column2 from tables_name where 条件;
select * from a6;
select id,name,age from a6 where age=18;
查询数据(查)
1、单表查询
1.1、全表查询
在查询中没有指定任何的限制条件,会返回整张表的所有行
1.2、条件查询
1.2.1、条件表达式
1.2.1.1、运算符
等于=
select id,name,age from a6 where age=18;
不等于!=或<>
select id,name,age from a6 where age!=18;
select id,name,age from a6 where age<>18;
大于>
select id,name,age from a6 where age>18;
大于等于>=
select id,name,age from a6 where age>=18;
小于<
select id,name,age from a6 where age<18;
小于等于<=
select id,name,age from a6 where age<=18;
between
BETWEEN运算符用于表示在一个范围内的值,AND
select * from a6 where age between 18 and 25;
select * from a6
where age between 18 and 25 (筛选年龄范围)
and score between 80 and 100;(筛选成绩范围)
多行内容写入以这种写法条理较为清晰
in
IN运算符用于比较一个表达式是否与一组表达式中的任意一个相匹配
select * from a6 where name in('li');
1.2.1.2、通配符 ( %和_ )
%通配符匹配任意数量(包括0个)的字符
select * from a6 where name like '%xi%';
通配符 _
_通配符匹配一个任意字符
通常会和like一起使用
select * from a6 where name like 'l_';
1.2.2、查询类型
1)where子句
查询时,指定要返回符合条件的行,后面跟条件
2)排序查询
排序查询是通过SQL查询语句将所查询的结果按照指定的排序方式排列
升序(默认) ASC
降序 DESC
select * from test order by colume1;
select * from test order by colume1 DESC, colume2 ASC;
select * from a6 order by age;
select * from a6 order by age asc;
注:升序排列为系统默认,所以加不加asc都可以
年龄降序
多条件查询排序
select * from a6 order by age asc,name desc;
3)分组查询
主要用于统计分析,生成对应报表
count(*)函数用于统计出现过的记录总和
group by用于按照特定字段进行分组
select class, count(*) from test group by class;
select age,count(*) from a6 group by age;
经过筛选查询:
年龄为14的记录有1个
年龄为15的记录有1个
年龄为18的记录有2个
年龄为22的记录有1个
年龄为25的记录有2个
年龄为26的记录有1个
年龄为35的记录有1个
4)去重查询
用于从结果集中删除重复的行,只返回不同的值
select distinct colume1 from test;
select distinct age from a6;
如果查询多个列,则会显示两列的组合,每个组合只会出现一次
select distinct colume1,colume2......... from test;
select distinct name,age from a6;
5)分页查询
用于在SQL语句中限制返回数据的条数该技术可用于显示诸如Web页面之类的大量数据
limit 表示要返回的记录数
offset 表示查询结果的起始位置或查询结果的偏移量
select * from test limit 10 offset 10;
查询5行数据,从初始行偏移一行显示
select * from a6 limit 5 offset 1;
6)子查询
在一个SQL语句中嵌套使用另一个完整的SQL查询语句。子查询通常用作主查询的查询条件或结果过滤条件,以及用于提供主查询需要的一些数据
select * from test where age > (select avg(age) from test);
select * from a6 where age > (select avg(age)from a6);
7)函数查询
7.1)聚合函数
7.1.1)SUM
求某一列的值的总和
select sum(colume1) from test;
select sum(age) from a6;
7.1.2)AVG
求某一列值的平均值
select avg(colume1) from test;
select avg(age) from a6;
如果某一列中的值包含数值0,该如何计算?
7.1.3)COUNT
用于计算指定列中的行数,不包含非空行
select count(id) from test;
select count(age) from a6;
7.1.4)MAX
用于计算指定列中的最大值
MAX函数适用于任何数据类型,无论列中包含的是数字、文本还是其他类型的数据
如果是文本字符串类型,则按照字符串的字典序进行排序
select max(id) from test;
select max(age) from a6;
7.1.5)MIN
用于计算指定列中的最小值
MIN函数适用于任何数据类型,无论列中包含的是数字、文本还是其他类型的数据
如果是文本字符串类型,则按照字符串的字典序进行排序
select min(id) from test;
select min(age) from a6;
7.2)字符串函数
7.2.1)CONCAT
连接两个或多个字符串,并返回合成后的新字符串
select concat('hello',' ','World');
select concat(name,' ',age) from a6;
7.2.2)LENGTH
返回字符串的长度(字符数)
select length('Hello World');
select length(name) from a6;
7.2.3)UPPER
将字符串转换为大写字母
select upper(Hello World);
select upper(name) from a6;
7.2.4)LOWER
将字符串转换为小写字母
select lower(Hello World);
select lower(name) from a6;
7.2.5)SUBSTR
返回指定字符串中的一部分,可以使用起始位置和长度指定要返回的子字符串
select substr('Hello World',7,5);
select substr(name,1,4) from a6;
7.2.6)REPLACE
将指定字符串中的一部分替换为新字符串,并返回新的字符串
select replace('Hello World','Hello','Hi');
select replace(name,'ln','zn') from a6;
7.3)日期时间函数
7.4)数学函数
7.5)拓展内容
2、多表查询
多表查询是指在关系型数据库中,查询多个表的信息并进行关联、筛选和排序等操作的过程或语句。在一个查询语句中同时查询多个表,并根据表之间的关联关系进行关联查询。通过多表查询,可以将多个表的数据进行联合,获取到需要的结果集,多表查询可以用来解决某些查询需求。
多表查询常用的关联方式包括:
内连接(INNER JOIN):根据两个表之间的关联字段进行查询,返回符合条件的数据。
左连接(LEFT JOIN):以左表为基准,将左表中的所有数据与右表中符合条件的数据进行关联查询,如果右表中没有符合条件的数据,则用NULL填充。
右连接(RIGHT JOIN):以右表为基准,将右表中的所有数据与左表中符合条件的数据进行关联查询,如果左表中没有符合条件的数据,则用NULL填充。
全连接(FULL JOIN):将左表和右表中的所有数据进行关联查询,如果左表和右表中没有符合条件的数据,则用NULL填充。
多表查询可以通过使用JOIN关键字来实现,其中可以通过ON关键字指定关联条件。
2.1.内连接查询
创建两个数据表写有数据
CREATE TABLE z1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age VARCHAR(50) NOT NULL
);
CREATE TABLE n2 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender CHAR(10) NOT NULL,
date DATE NOT NULL
);
INSERT INTO z1 (id, name, age) VALUES
(1,'zhangsan',18),
(2,'lisi',25),
(3,'wangwu',30);
INSERT INTO n2 (id,name, gender,date) VALUES
(1,'xiaoming','man','2000-05-17'),
(2,'xiaomei','woman','2013-06-30'),
(3,'xiaoshuai','man','2020-05-16'),
(4,'xiaonan','woman','1997-10-07');
SELECT *
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2;
内连接是一种联结操作,它根据两个表之间的关联关系将它们的记录进行匹配。在这个例子中,通过指定 ON 子句来定义连接条件,即 table1.column1 = table2.column2。这表示将会以 column1 和 column2 的值相等作为连接条件,将两个表中匹配的记录合并在一起。
这个查询可以用于在两个表之间建立关联关系,并获取相关联的记录,以便进行进一步的分析或操作。
select *
from z1
inner join n2
on z1.id = n2.id;
2.2、外连接查询
2.2.1、左外连接查询
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2;
这个查询语句表示在两个表(table1和table2)之间进行左连接(left join)操作。LEFT JOIN是一种关联操作,它会返回左表(table1)中的所有记录,以及右表(table2)中与左表关联列(column1)匹配的记录。通过指定"ON"关键词和关联条件(table1.column1 = table2.column2),可以确定两个表之间的关联关系。查询结果将包含所有匹配的记录,并且对于没有匹配记录的左表记录,右表会返回NULL值。
select *
from z1
left join n2
on z1.id = n2.id;
2.2.2、右外连接查询
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column2;
这个查询语句表示在两个表(table1和table2)之间进行右连接(right join)操作。RIGHT JOIN是一种关联操作,它会返回右表(table2)中的所有记录,以及左表(table1)中与右表关联列(column1)匹配的记录。通过指定"ON"关键词和关联条件(table1.column1 = table2.column2),可以确定两个表之间的关联关系。查询结果将包含所有匹配的记录,并且对于没有匹配记录的右表记录,左表会返回NULL值。
select *
from z1
right join n2
on z1.id = n2.id;