SQL基础语句练习-CSDN博客

本文链接：https://blog.csdn.net/2401_86223056/article/details/147877821

文章目录

通用语法
1. 单行或多行，分号结尾
2. 空格或缩进提高阅读性
3. 不区分大小写，关键字建议大写
4. 注释：
```
--这是注释
# 这也是注释
```

SQL数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）unsigned	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值 M是总位数，D是小数点后位数	依赖于M和D的值	小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。

具体指假如n为10，对char来说未满10个字符的内容将用空格补全，而varchar是存几个就是几个

在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

# 设计员工表
create table emp(
	id int comment'编号',
    number varchar(10) comment'工号',
    gender char(1) comment'性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号'
)comment '员工表';

DDl

数据定义语言，用来定义数据库对象（数据库，表，字段）

DDL-数据库操作

SHOW DATABASES;
--查询所有数据库
CREATE DATABASES；
--创建
use 数据库名;
--使用

select database();
--查看当前数据库是谁

drop 数据库名;
--删除(永久删除）


-- 显示当前数据库中所有的表
SHOW TABLES;

-- 创建一个新表，表名为“表名”，并定义表中的字段和字段类型
-- 注意：这里应该是CREATE TABLE，而不是CREATE ABLE
CREATE TABLE 表名 (
    字段1 字段类型1,
    字段2 字段类型2,
    ........
);

-- 查看表的结构，包括字段名、字段类型、是否为主键等信息
DESC 表名;

-- 显示创建表的SQL语句，包括表的结构、字段定义、表的注释等
SHOW CREATE TABLE 表名;

-- 修改表结构，可以添加字段、修改字段、更改字段名称或重命名表
-- 注意：ALTER TABLE的语法需要更明确，以下是具体操作的语法：
-- 添加字段：ALTER TABLE 表名 ADD 字段名 字段类型;
-- 修改字段类型：ALTER TABLE 表名 MODIFY 字段名 新字段类型;
-- 更改字段名称：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新字段类型;
-- 重命名表：ALTER TABLE 表名 RENAME TO 新表名;

-- 删除表，删除后表中的所有数据也会被清空，不可恢复
DROP TABLE 表名;

DML

数据操作语言，用来对表中的数据进行增删改查

DQL

数据查询语言，用来查询数据库表中的记录

DCL

数据控制语言，用来创建数据库应用，控制数据库的访问权限

练习源码

-- 切换到 test 数据库
use test;

-- 创建一个名为 employees 的表
create table employees
(
    id          int primary key auto_increment,
    emp_id      int,
    emp_name    varchar(50),
    department  varchar(50),
    salary      decimal(10, 2),
    hire_date   date
);

-- 插入一些示例数据
insert into employees (emp_id, emp_name, department, salary, hire_date) values
(1, 'Alice', 'HR', 5000.00, '2020-01-15'),
(2, 'Bob', 'Engineering', 8000.00, '2018-03-22'),
(3, 'Charlie', 'Marketing', 6000.00, '2019-07-10'),
(4, 'Diana', 'Engineering', 9000.00, '2021-11-05'),
(5, 'Eve', 'HR', 5500.00, '2022-05-18'),
(6, 'Frank', 'Finance', 7000.00, '2017-09-12'),
(7, 'Grace', 'Engineering', 8500.00, '2020-06-30'),
(8, 'Hank', 'Marketing', 6200.00, '2019-12-01'),
(9, 'Ivy', 'HR', 5300.00, '2021-03-25'),
(10, 'Jack', 'Finance', 7500.00, '2018-08-19');





-- 查询操作
-- 1.查询指定字段 as关键字可以给字段起别名,可以省略
select emp_id as'顺序', emp_name as '姓名', department as '部门' from employees;
-- 2.查询所有字段 *是通配符，可以用来表示所有字段
select * from employees;
-- 或者写：select id, emp_id, emp_name, department, salary, hire_date from employees;
-- 3.查询指定字段，去除重复值 distinct 关键字可以去除重复值
select distinct department from employees;

-- 条件查询 where + 运算符
-- 1.查询工资大于6000的员工
select salary,emp_name as '姓名' from employees where salary > 6000;
-- 2.查询工资大于6000并且部门为HR的员工
select emp_name as '姓名', salary from employees where salary > 6000 and department = 'HR';
-- 3.查询工资大于6000或者部门为HR的员工
select emp_name as '姓名', salary,department as '部门' from employees where salary > 6000 or department = 'HR';
/* 比较运算符有：=、!=、>、<、>=、<=
between ... and ... 关键字可以查询范围内的值,前面的值小于后面的值
in (...) 关键字可以查询指定值(多选1）
like 占位符号可以模糊查询(例如 可以查询包含某个字符的值)
    %表示任意多个字符
    _表示一个字符
    [abc]表示a、b、c中的任意一个字符
    [a-z]表示a到z之间的任意一个字符
    [^abc]表示不是a、b、c中的任意一个字符
is null 可以查询空值 is not null 可以查询非空值
逻辑运算符有：and、or、not（&&、||、！）
 */

 -- 聚合函数:将{一列}数据作为一个整体进行计算，返回一个单一的值
    # 细节：nulll值会被忽略
-- 1.查询员工总数, count() 函数可以统计行数
select count(emp_id) as '员工总数' from employees;
-- 2.查询工资总和, sum() 函数可以统计总和
select sum(salary) as '工资总和' from employees;
-- 3.查询平均工资, avg() 函数可以统计平均值
select avg(salary) as '平均工资' from employees;
-- 4.查询最高工资, max() 函数可以统计最大值
select max(salary) as '最高工资' from employees;
-- 5.查询最低工资, min() 函数可以统计最小值
select min(salary) as '最低工资' from employees;
-- 6.查询工资大于6000的员工总数
select count(*) as '工资大于6000的员工总数' from employees where salary > 6000;
-- 7.查询每个部门的员工总数
select department as '部门', count(*) as '员工总数' from employees group by department;
-- 8.查询每个部门的平均工资
select department as '部门', avg(salary) as '平均工资' from employees group by department;
-- 9.查询每个部门的最高工资
select department as '部门', max(salary) as '最高工资' from employees group by department;

-- 分组查询
    # where和having的区别 where>聚合函数>having
    # where 关键字可以在分组之前进行过滤
    # having 关键字可以在分组之后进行过滤
-- 1.按照部门分组，查询每个部门的员工总数
select department as '部门', count(*) as '部门人数' from employees group by department having count(*)>=2;
-- 2.按照部门分组，查询每个部门的平均工资,并显示部门人数
select department as '部门', avg(salary) as '平均工资', count(department) as '部门人数' from employees group by department having count(department)>=2;

-- 排序查询
    # order by 关键字可以对查询结果进行排序
    # oder by 关键字可以指定排序的字段和排序方式
    # asc 升序（默认） desc 降序
-- 1.按照工资升序排序
select emp_name as '姓名', salary as '工资' from employees order by salary asc;
-- 2.按照时间降序排序
select emp_name as '姓名', hire_date as '入职时间' from employees order by hire_date desc;
-- 3.按照部门升序，工资降序排序。多组排序方式用逗号隔开
select emp_name as '姓名', department as '部门', salary as '工资' from employees order by department asc, salary desc;

-- 分页查询
    # limit 关键字可以限制查询结果的行数（这是mysql的语法）
    # limit n,m n表示从第几行开始，m表示查询多少行
-- 1.查询前5行数据
select emp_name as '姓名', salary as '工资' from employees limit 0, 5;
-- 2.查询第6到第10行数据
select emp_name as '姓名', salary as '工资' from employees limit 5, 5;

-- select emp_name as'姓名', salary as ’gongzi' from employees limit 10,5;
/*  DQL执行顺序：
    1. from
    2. where
    3. group by
    4. having
    5. select
    6. order by
    7. limit
    # 注意：以上顺序是从上到下执行的顺序，而不是从上到下的书写顺序
    # 例如：select emp_name as'姓名', salary as ’gongzi' from employees limit 10,5;
 */
-- 更新操作
-- 1.更新工资大于6000的员工的工资加1000
update employees set salary = salary + 1000 where salary > 6000;
-- 2.更新部门为HR的员工的工资加500
update employees set salary = salary + 500 where department = 'HR';
-- 3.更新部门为HR的员工的工资加500，并且入职时间在2020年之前
update employees set salary = salary + 500 where department = 'HR' and hire_date < '2020-01-01';
-- 4.更新部门为HR的员工的工资加500，并且入职时间在2020年之前，员工姓名为Alice
update employees set salary = salary + 500 where department = 'HR' and hire_date < '2020-01-20' and emp_name = 'Alice';
-- 查询更新后的数据
select * from employees;
select emp_name as '姓名', salary as '工资' from employees;