MySQL语言指令笔记（2024/4/21）更新练习

CREATE TABLE employees (  
    employee_id INT PRIMARY KEY,  
    first_name VARCHAR(50),  
    last_name VARCHAR(50),  
    email VARCHAR(100) UNIQUE NOT NULL,  
    phone_number VARCHAR(20),  
    hire_date DATE,  
    salary DECIMAL(10, 2)  
);

CREATE TABLE employees：表示创建一个名为employees的表。
括号内的内容是表的列定义。
employee_id INT PRIMARY KEY：定义了一个名为employee_id的列，其数据类型为INT，并且这个列被设置为主键（PRIMARY KEY）。
first_name VARCHAR(50)：定义了一个名为first_name的列，其数据类型为可变长度的字符串（VARCHAR），最大长度为50个字符。
last_name VARCHAR(50)：定义了last_name列。
email VARCHAR(100) UNIQUE NOT NULL：定义了email列，其最大长度为100个字符，并设置了UNIQUE约束（确保值唯一）和NOT NULL约束（该列不能包含NULL值）。
phone_number VARCHAR(20)：定义了phone_number列。
hire_date DATE：定义了hire_date列，其数据类型为日期（DATE）。
salary DECIMAL(10, 2)：定义了salary列，其数据类型为十进制数（DECIMAL），总共有10位数字，其中2位是小数。

SQL标准支持多种数据类型，如INT、VARCHAR、DATE、DECIMAL等，

多种约束，如PRIMARY KEY、UNIQUE、NOT NULL、FOREIGN KEY等

后面会进行详细讲解。

DDL语言修改表（alter，drop）

增：ALTER TABLE 表名 add 字段名类型(长度) [comment 注释] [约束];

改：1.类型： ALTER TABLE 表名 modify 字段新类型（长度）

2.字段名/类型：

ALTER TABLE 表名旧字段新字段类型（长度）[comment 注释] [约束];

改（表名）：ALTER TABLE 表名 rename to 新表名

删：ALTER TABLE 表名 drop 字段

删表：1.DROP TABLE [if exists] 表名

2.truncate table 表名; --删除并创建

DML数据操纵语言

`INSERT`（插入数据）

关键字INSERT INTO

1.插入一行数据

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

2.指定列插入数据

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

3.批量插入

INSERT INTO table_name (column1, column2, column3)  
VALUES   
    (value1a, value2a, value3a),  
    (value1b, value2b, value3b),  
    ...;

注意事项：

确保为每列提供的数据类型与列定义中指定的数据类型相匹配。
如果某列设置了NOT NULL约束，你必须为该列提供一个值，否则插入操作会失败。
如果某列有默认值，并且你没有为该列提供值，那么该列将使用默认值。
插入数据时，要考虑性能和并发性。如果一次性插入大量数据，可能需要考虑分批插入或使用批量插入优化技术。

`UPDATE`（更新数据）

UPDATE 表名 SET column1 = value1, column2 = value2, ... WHERE condition;

UPDATE students  
SET age = 25  
WHERE name = 'John Doe';

`DELETE`（删除数据）

删：delete form 表名 [where 条件]

DQL数据查询语言 `SELECT`（查询数据）

DQL（Data Query Language）即数据查询语言，是SQL（Structured Query Language）中的一个重要组成部分，用于从数据库中检索数据。

逻辑顺序：

FROM 和 JOIN：
首先，通过FROM子句指定要查询的表。
如果存在JOIN操作（如INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN等），则按照指定的连接条件将多个表组合起来。
WHERE：
接下来，应用WHERE子句中的条件来过滤结果集，只保留符合条件的行。
GROUP BY：
对经过WHERE过滤后的结果集按照一个或多个列进行分组。
HAVING：
在分组后，使用HAVING子句进一步过滤分组。这与WHERE类似，但HAVING用于分组后的条件过滤，而WHERE用于分组前的行级过滤。
SELECT：
选择要显示的列。这包括使用聚合函数（如SUM、COUNT、AVG等）对分组后的数据进行计算。
如果使用了别名，也会在这个阶段应用。
ORDER BY：
对结果集进行排序。你可以按照一个或多个列进行升序（ASC）或降序（DESC）排序。
LIMIT / OFFSET（某些DBMS中）：
限制返回的记录数（LIMIT）以及从哪里开始返回记录（OFFSET），常用于分页查询。

三、附录

条件

在SQL中，WHERE子句用于过滤查询结果，只返回满足指定条件的记录。WHERE子句通常与SELECT、UPDATE和DELETE语句一起使用，以限制受影响的行数。

以下是WHERE子句的一些基本用法和示例：

1. 基本比较运算符

等于：=
不等于：<> 或 !=
大于：>
大于等于：>=
小于：<
小于等于：<=

示例：

SELECT * FROM employees WHERE age >= 30;

这条语句会返回employees表中所有年龄大于或等于30的员工记录。

2. 逻辑运算符

AND：所有条件都必须为真。
OR：至少有一个条件为真。
NOT：对条件取反。

示例：

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

这条语句会返回employees表中所有在销售部门且薪水超过50,000的员工记录。

3. IN 运算符

用于匹配列表中的任何一个值。

示例：

SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

这条语句会返回employees表中所有在销售或市场部门的员工记录。

4. BETWEEN 运算符

用于在指定范围内过滤值。

示例：

SELECT * FROM employees WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

这条语句会返回employees表中所有在2022年雇佣的员工记录。

5. LIKE 运算符

用于在字符串中搜索模式，通常与通配符一起使用。

%：代表零个、一个或多个字符。
_：代表一个单一字符。

示例：

SELECT * FROM employees WHERE first_name LIKE 'Jo%';

这条语句会返回employees表中所有名字以"Jo"开头的员工记录。

6. NULL 值处理

IS NULL：检查列的值是否为NULL。
IS NOT NULL：检查列的值是否不为NULL。

示例：

SELECT * FROM employees WHERE address IS NULL;

这条语句会返回employees表中所有地址字段为NULL的员工记录。

注意事项：

在使用WHERE子句时，确保列名和条件与数据库中的实际数据匹配。
使用索引列作为过滤条件通常可以提高查询性能。
复杂的WHERE子句可能导致查询性能下降，因此应尽量避免不必要的复杂条件。

数据类型

数据库字段类型表格汇总如下：

字段类型	描述	范围/长度	示例
INT	整数类型	-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647)	存储年龄、数量等
TINYINT	小整数类型	0 到 255 (UNSIGNED)，-128 到 127 (SIGNED)	存储小范围整数
SMALLINT	中等整数类型	-32,768 到 32,767 (SIGNED)，0 到 65,535 (UNSIGNED)	存储中等范围整数
MEDIUMINT	中等大小整数类型	-8,388,608 到 8,388,607 (SIGNED)，0 到 16,777,215 (UNSIGNED)	存储较大范围整数
BIGINT	大整数类型	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 (SIGNED)，0 到 18,446,744,073,709,551,615 (UNSIGNED)	存储大范围整数
FLOAT	单精度浮点数类型	近似值	存储小数
DOUBLE	双精度浮点数类型	近似值	存储需要高精度的小数
DECIMAL(M,D) 或 NUMERIC(M,D)	精确小数类型	最大数字数为 M，小数点右边数字数为 D	存储精确的小数，如货币
CHAR(M)	定长字符串类型	长度为 M 的字符串，不足部分用空格填充	存储固定长度的字符串，如邮编
VARCHAR(M)	变长字符串类型	最大长度为 M 的字符串	存储可变长度的字符串，如姓名
TINYTEXT	非常小的文本类型	最大长度为 255 个字符	存储小文本内容
TEXT	文本类型	最大长度为 65,535 个字符	存储文本内容
MEDIUMTEXT	中等大小的文本类型	最大长度为 16,777,215 个字符	存储中等大小的文本内容
LONGTEXT	大文本类型	最大长度为 4,294,967,295 个字符	存储大文本内容
DATE	日期类型	'YYYY-MM-DD' 格式	存储日期值
TIME	时间类型	'HH:MM:SS' 格式	存储时间值
DATETIME	日期和时间类型	'YYYY-MM-DD HH:MM:SS' 格式	存储日期和时间值
TIMESTAMP	时间戳类型	'YYYY-MM-DD HH:MM:SS' 格式，与 UNIX 时间戳相关	存储时间戳值
YEAR	年份类型	'YYYY' 格式，范围 1901 到 2155	存储年份值
ENUM('value1','value2',...)	枚举类型	从预定义的值集合中选择一个值	存储预定义值集合中的一个值
SET('value1','value2',...)	集合类型	可以选择预定义值集合中的一个或多个值	存储预定义值集合中的一个或多个值
BLOB	二进制大对象类型	最大长度为 65,535 个字节	存储二进制数据
TINYBLOB	非常小的二进制大对象类型	最大长度为 255 个字节	存储小二进制数据
MEDIUMBLOB	中等大小的二进制大对象类型	最大长度为 16,777,215 个字节	存储中等大小的二进制数据
LONGBLOB	大二进制对象类型	最大长度为 4,294,967,295 个字节	存储大二进制数据

请注意，不同的数据库系统（如 MySQL、Oracle、SQL Server 等）可能具有略有不同的字段类型或额外的属性。上述表格提供的是通用的字段类型描述，实际应用中需根据具体数据库系统的文档来确定可用的字段类型及其特性。

四、DQL/DCL练习笔记

# DML

use dept_emp;

# 添加数据
insert into emp (name, age, dept_id) VALUE ("syx",25,2);

insert into emp values (8,"syxia",25,1);

insert into emp (name, age, dept_id) VALUES ("syx1",26,null),("syx2",27,null);

# 修改

update emp set name='wangbada' where name="syx1";
update emp set name='wang9dan' where id=10;


select id from emp where name="syx";

# 删除
delete from emp where name='wangbada';
delete from emp ;

truncate table emp;

# DQL
select * from emp;

select id,name from emp;

select id,name from emp where dept_id=1;
select id,name from emp where dept_id=1 and age=25;

select id as"编号",name as"姓名" from emp where dept_id=1 and age=25;

select distinct name from emp where dept_id=1 and age=25;

# where条件运用
# between and  or  not      like  in <=>  <>  not >
select  id,name from emp where age between 18 and 25;
select  id,name from emp where age=25 or age=33;
select  id,name from emp where age<>25 and age<>33;
select  id,name from emp where name like "___";
select  id,name from emp where name like "s%";

select name from emp where dept_id is not null ;

# sum
select sum(age),avg(age) from emp where name='syx';
select sum(age),avg(age) from emp;

select dept_id as"部门",max(age) from emp  group by dept_id;
select name,age from emp where age=(select max(age) from emp);
# 查询每个部门年龄最大的姓名，年龄是什么

select dept_id,name,age from emp group by dept_id having max(age) ;

select dept_id,max(age) from emp group by dept_id;



SELECT
    p1.dept_id,
    p1.name AS max_age_person_name,
    p1.age AS max_age
FROM
    emp p1
inner join (
    SELECT
        dept_id,
        MAX(age) AS max_age
    FROM
        emp
    GROUP BY
        dept_id
) p2 ON p1.dept_id = p2.dept_id AND p1.age = p2.max_age;


 SELECT
        dept_id,
        MAX(age) AS max_age
    FROM
        emp
    GROUP BY
        dept_id;

select emp.name,dept.name from emp ,dept where emp.dept_id=dept.id;
select a.*,b.name from emp a  left outer join dept b on a.dept_id = b.id;
select a.*,b.name from emp a  right outer join dept b on a.dept_id = b.id;
select a.name,e.* from dept a right outer join emp e on a.id = e.dept_id;


select name from emp limit 6,3;

# having,order by
# 查询组员人数大于2的部门名称
select dept_id from emp group by dept_id having count(*)>=2;

select *from emp order by age asc;
select *from emp order by age asc,id desc;


# DCL
# 查权限

show grants for "root"@"localhost";

create user "hfy"@"localhost";
show grants for "hfy"@"localhost";
grant select on dept_emp.* to "hfy"@"localhost";
revoke select on dept_emp.* from "hfy"@"localhost";

DCL语用，创建数据库用户

# DCL
# 查权限

show grants for "root"@"localhost";

create user "hfy"@"localhost";
show grants for "hfy"@"localhost";
grant select on dept_emp.* to "hfy"@"localhost";
revoke select on dept_emp.* from "hfy"@"localhost";

# alter user "hfy"@"localhost"  identified by "123456";
use mysql;
select *from user;

set password for "hfy"@"localhost" = password('新密码');


alter user 'hfy'@'localhost' identified by '123'

五、MySQL DDL、DCL、DQL、DML综合题

一、DDL（数据定义语言）

创建一个名为sample_db的数据库。
在sample_db数据库中创建一个名为employees的表，包含以下字段：id（整数类型，主键，自增）、name（字符串类型，最大长度50）、age（整数类型）、email（字符串类型，最大长度100）、hire_date（日期类型）。

二、DCL（数据控制语言）

创建一个用户new_user，并为其设置密码password123。
授予new_user对sample_db数据库中employees表的SELECT和INSERT权限。

三、DQL（数据查询语言）

查询employees表中所有员工的姓名和年龄。
查询employees表中年龄大于30岁的员工姓名和邮箱。

四、DML（数据操作语言）

向employees表中插入一条新记录，姓名为"John Doe"，年龄为35，邮箱为"johndoe@example.com"，入职日期为当前日期。
更新employees表中姓名为"John Doe"的员工的年龄为36。
删除employees表中年龄小于30岁的所有员工记录。

答案及解析

一、DDL

创建数据库：
```
CREATE DATABASE sample_db;
```
创建表：

USE sample_db;  
CREATE TABLE employees (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    name VARCHAR(50),  
    age INT,  
    email VARCHAR(100),  
    hire_date DATE  
);

二、DCL

创建用户并设置密码：

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password123';

授予权限：

GRANT SELECT, INSERT ON sample_db.employees TO 'new_user'@'localhost';

三、DQL

查询姓名和年龄：
```
SELECT name, age FROM employees;
```
查询年龄大于30岁的员工姓名和邮箱：
```
SELECT name, email FROM employees WHERE age > 30;
```

四、DML

插入新记录：

INSERT INTO employees (name, age, email, hire_date) VALUES ('John Doe', 35, 'johndoe@example.com', CURDATE());

更新年龄：

UPDATE employees SET age = 36 WHERE name = 'John Doe';

删除年龄小于30岁的员工：

UPDATE employees SET age = 36 WHERE name = 'John Doe';

请注意，上述操作应在具有相应权限的MySQL用户下执行，并且为了简化，未考虑事务处理、错误处理及异常处理等方面的细节。在实际应用中，应确保操作的正确性和安全性。

古韵堂-晓宇

关注

18
点赞
踩
8

收藏

觉得还不错? 一键收藏
2
评论
MySQL语言指令笔记（2024/4/21）更新练习

DDL、DML、DQL和DCL是SQL语言中的四个主要类别，分别用于定义和管理数据库对象、操作数据库中的数据、查询数据以及控制数据库权限。这些语言类别共同构成了SQL的完整功能集，使得用户可以对数据库进行各种复杂的操作和管理。DDL（Data Definition Language，数据定义语言）是用于定义或改变表（TABLE）的结构、数据类型、表之间的链接和约束等初始化工作的语言。这些操作大多在建立表时使用，主要用于定义数据库对象或改变表的结构和数据类型。命令（关键字）功能。
复制链接

扫一扫