小白带你学习linux 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；

show databases；

2、创建

默认字符集          create database 数据库名称；               默认是latin1

create database liujuntao;

指定字符集           create database 数据库名称 character set utf8;          使用uft8格式的字符集

create database ljt character set utf8;

3、进入

use 数据库名称；

use liujuntao

4、删除

drop database 数据库名称；

drop database ljt;

5、更改

库名称        进入到数据库的目录中修改数据库的名称

字符集       ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATEutf8mb4_unicode_ci;

去当前目录更改

cd /var/lib/mysql

mv liujuntao   ljt

6、练习

创建2个数据库，一个全写，一个缩写（指定字符集）

第一个

第二个

删除缩写的，将全写的名称改为缩写的

删除第二个

三、 数据表操作

1、数据类型

1.1 数值类型

TINYINT

1个字节，范围为 -128 到 127（有符号）或 0 到 255（无符号）

可以使用 TINYINT UNSIGNED 来存储年龄（无符号）或温度（有符号）等小数值

SMALLINT

2个字节，范围为 -32,768 到 32,767（有符号）或 0 到 65,535（无符号）

可以使用 SMALLINT 存储商品数量， 或者使用 SMALLINT UNSIGNED 存储区域编号（无符号）

INT

4个字节，范围为 -2,147,483,648 到 2,147,483,647（有符号）或 0 到 4,294,967,295（无符号）

这是最常用的整数类型，它可以被用于许多方面，例如存储订单号或者用户的数量

BIGINT

8个字节           存储很大的数值，例如资金、人口等

FLOAT（M,D）

单精度浮点数，M是总位数，D是小数位数                可以使用 FLOAT(8,2) 来存储商品的价格

1.2时间\日期类型

DATE

用来存储日期，格式为’YYYY-MM-DD’               可以使用 DATE 存储出生日期或者过期日期等

TIME

用来存储时间，格式为’HH:MM:SS’             可以使用 TIME 存储过去一段时间内花费的小时数，分

钟数或秒数等

DATETIME

用来存储日期和时间，格式为’YYYY-MM-DD HH:MM:SS’       可以使用 DATETIME 存储订单时间

或者统计报告生成时间等

TIMESTAMP

用来存储日期和时间，通常被用于记录特定事件的时间戳。使用UNIX的日期和时间格式，从1970

年1月1日午夜开始计算

可以使用 TIMESTAMP 存储用户上次登录的时间戳

1.3字符串类型

CHAR

用来存储定长字符串，最大长度为255个字符         可以使用 CHAR(10) 存储用户的性别、婚姻状

况等数据

VARCHAR

用来存储可变长度字符串，最大长度为65535个字符           可以使用 VARCHAR(255) 存储用户输

入的文本内容、地址等数据

TEXT

用来存储大型字符数据，最大长度为2^16-1个字符

可以使用 TEXT 存储文章、评论等大型文本数据

1.4二进制类型

BINARY

用来存储固定长度二进制数据，最大长度为255个字节             可以使用 BINARY(16) 存储UUID

VARBINARY

用来存储可变长度二进制数据，最大长度为65535个字节

可以使用 VARBINARY(256) 存储不定长度的二进制数据，例如图片和音频等文件

BLOB

用来存储大型二进制对象数据，最大长度为2^16-1个字节

可以使用 BLOB 存储音视频等媒体文件

2、查看

2.1查看表内的数据

select * from tables_name;

describe class;

select column1,column2 from tables_name where 条件；

2.2查看表格的属性

describe tables_name;

describe class;

3、创建

第一种表格

create table tables_name(

第一列 类型属性，

第二列 类型属性，

......

) character set utf8mb4 collate utf8mb4_unicode_ci;

进入数据库

创建数据

第二种表格

create table tables_name(

第一列 类型属性，

第二列 类型属性 character set utf8mb4 collate utf8mb4_unicode_ci，

......

);

3.1character set utf8mb4 collate utf8mb4_unicode_ci        写在列之后，声明列的字符集

                                                                                           写在表之后，声明表的字符集

3.2创建数据表时，添加约束条件

创建数据表时添加

CREATE TABLE students (
 id INT PRIMARY KEY,  -- 定义 id 列为主键
name VARCHAR(20) NOT NULL,
age INT
);

修改现有的数据表

为数据表添加新的列

alter  table table_name  add new  新的列名 列表属性；

查看数据表

输入命令

alter table sss add id1 int(20);

删除新添加的列

alter table 表名  drop column  列名；

alter table class drop column  id1;

4、删除

4.1删除数据表

drop table tables_name;

4.2删除数据表数据，但是保留表结构

delete from tables_name;

5、更改

5.1表

名称

alter table old_table_name rename to new_table_name;

注意事项：如果现在的表正在被其他表或程序应用，那么可能会导致无法正常引用。

字符集

alter table my_table convert to character set utf8mb4 collate utf8mb4_unicode_ci;

5.2列

名称

alter table table_name change old_name new_name 属性；

属性

alter table my_table modify 修改的列  修改的属性；

字符集

alter table  users modify name varchar(50) character set utf8mb4;

6、练习

创建两个表：

1、包含编号、姓名、性别

2、包含姓名、年龄、出生年月

四、数据操作

1、增

insert into table_name (column1, column2, ...) values (value1, value2, ...);

insert into class(编号,姓名,性别) values(1,'zhangsan','nan');

2、删

delete from table_name where 条件;

3、改

update table_name set column1 = value1, column2 = value2, ... where 条件;

4、查

select * from tables_name;

select column1,column2 from tables_name where 条件；

还可以去掉*号换成其余的几项注意lisi要带引号

5、查询数据（查）

5.1单表查询

全表查询

在查询中没有指定任何的限制条件，会返回整张表的所有行

条件查询

条件表达式

运算符

等于=

select * from kkkk where xingbie='woman';

不等于!=或<>

大于>update

大于等于>=

小于<

小于等于<=

between

BETWEEN运算符用于表示在一个范围内的值,AND

in          IN运算符用于比较一个表达式是否与一组表达式中的任意一个相匹配

通配符

通常会和like一起使用

%       %通配符匹配任意数量（包括0个）的字符

_         _通配符匹配一个任意字符

查询类型

where子句

查询时，指定要返回符合条件的行，后面跟条件

排序查询

排序查询是通过SQL查询语句将所查询的结果按照指定的排序方式排列

升序(默认)        ASC

降序                   DESC

select * from test order by colume1;

select * from test order by colume1 DESC, colume2 ASC;

分组查询

主要用于统计分析，生成对应报表

count(*)函数用于统计出现过的记录总和

group by用于按照特定字段进行分组

select class, count(*) from test group by class;

去重查询

用于从结果集中删除重复的行，只返回不同的值

select distinct colume1 from test;

如果查询多个列，则会显示两列的组合，每个组合只会出现一次

分页查询

用于在SQL语句中限制返回数据的条数该技术可用于显示诸如Web页面之类的大量数据

limit    表示要返回的记录数

offset    表示查询结果的起始位置或查询结果的偏移量（备注：在SQL中，行编号是从0开始的，而不是从1开始的。）

select * from test limit 10 offset 10;

子查询

在一个SQL语句中嵌套使用另一个完整的SQL查询语句。子查询通常用作主查询的查询条件或结果过滤条件，以及用于提供主查询需要的一些数据

select * from test where age > (select avg(age) from test);

这样就查出来了比平均值大的列表了

select avg(age) from students;
 select * from students where age >(select avg(age) from students);

函数查询

聚合函数

备注（可以配合where子句，条件匹配使用）

SUM

求某一列的值的总和

select sum(colume1) from test;

得出了列表的总和为82

AVG

求某一列值的平均值

select avg(colume1) from test;

平均值就是20.5

如果某一列中的值包含数值0，该如何计算？

COUNT

用于计算指定列中的行数，不包含非空行

select count(id) from test;

算出来为四行

MAX

用于计算指定列中的最大值

MAX函数适用于任何数据类型，无论列中包含的是数字、文本还是其他类型的数据

如果是文本字符串类型，则按照字符串的字典序进行排序

select max(id) from test;

MIN

用于计算指定列中的最小值

MIN函数适用于任何数据类型，无论列中包含的是数字、文本还是其他类型的数据

如果是文本字符串类型，则按照字符串的字典序进行排序

select min(id) from test;

字符串函数

CONCAT

连接两个或多个字符串，并返回合成后的新字符串

select concat('hello',' ','World');

LENGTH

返回字符串的长度（字符数）

select length('Hello World');

UPPER

将字符串转换为大写字母

select upper(Hello World);

LOWER

将字符串转换为小写字母

select lower(Hello World);

SUBSTR

返回指定字符串中的一部分，可以使用起始位置和长度指定要返回的子字符串

select substr('Hello World',7,5);

REPLACE

将指定字符串中的一部分替换为新字符串，并返回新的字符串

select replace('Hello World','Hello','Hi');

日期时间函数

数学函数

多表查询

备注（多表查询是指在关系型数据库中，查询多个表的信息并进行关联、筛选和排序等操作的过程或语句。多表查询可以用来解决某些查询需求）

内连接查询

备注（内连接查询（INNER JOIN）：显示两个表中满足条件的行，并且只显示这两个表中共有的列）

首先创建两个有关联的表

创建表命令

CREATE TABLE courses ( id INT PRIMARY KEY, name VARCHAR(50), teacher VARCHAR(50) );
INSERT INTO courses (id, name, teacher) VALUES (1, 'Mathematics', 'Mr. Smith'), (2, 'English', 'Ms. Johnson'), (3, 'Science', 'Dr. Brown');

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT, gender VARCHAR(10), course_id INT, FOREIGN KEY (course_id) REFERENCES courses(id) );
INSERT INTO students (id, name, age, gender, course_id) VALUES (1, 'Alice', 20, 'Female', 1), (2, 'Bob', 22, 'Male', 2), (3, 'Charlie', 19, 'Male', 1), (4, 'Diana', 21, 'Female', 3);

开始内连接查询

SELECT *

FROM table1

INNER JOIN table2

ON table1.column1 = table2.column2;

select * 
from students
inner join courses
on students.id = courses.id;

外连接查询

左外连接查询

备注（左外连接查询（LEFT JOIN）：显示左表中所有行和右表中满足条件的行，如果右表没有匹配的行，则显示null值/）

SELECT *

FROM table1

LEFT JOIN table2

ON table1.column1 = table2.column2;

select *
from students 
left join courses
on students.id = courses.id;

右外连接查询

备注（右外连接查询（RIGHT JOIN）：与左外连接查询类似，但显示右表中所有行和左表中满足条件的行）

SELECT *

FROM table1

RIGHT JOIN table2

ON table1.column1 = table2.column2;

select *
from students
right join courses
on students.id = courses.id;