本文以t_student为例，简单记录下MySQL的基本操作。

1. 创建表

下面以t_student为例创建一张包含学生信息的表：

  • id: 自增主键
  • name: 学生姓名
  • age: 学生年龄
  • sex: 学生性别
CREATE TABLE `t_student` (
	`id` INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR ( 12 ) NOT NULL,
	`age` TINYINT DEFAULT '18',
	`sex` TINYINT DEFAULT NULL 
);

2. 列操作

列操作包括列的新增、修改和删除：

-- 新增score列存储学生成绩，类型设置为double
ALTER TABLE t_student ADD COLUMN score DOUBLE;

-- 修改类型为float
ALTER TABLE t_student MODIFY COLUMN score FLOAT;

3. 插入数据

-- 插入数据
INSERT INTO t_student ( NAME, age, sex, score ) VALUES( 'zhangsan', 10, 1, 89.3 );
INSERT INTO t_student ( NAME, age, sex, score ) VALUES ( 'lisi', 16, 0, 78.3 );
INSERT INTO t_student ( NAME, age, sex, score ) VALUES ( 'wang5', 17, 1, 95 );
INSERT INTO t_student ( NAME, age, sex, score ) VALUES ( 'zhansi', 23, 1, 59 );
INSERT INTO t_student ( NAME, age, sex, score ) VALUES ( 'wangliu', 31, 0, 85 );
INSERT INTO t_student ( NAME, age, sex, score ) VALUES ( 'qianda', 63, 0, 88 );
INSERT INTO t_student ( NAME, age, sex, score ) VALUES ( 'suner', 25, 1, 67 );
INSERT INTO t_student ( NAME, age, sex, score ) VALUES ( 'zhouwu', 20, 0, 98 );

4. 查询操作

4.1 查询所有数据

SELECT * FROM t_student;

使用SELECT * FROM table查询会返回表中的所有列，这在某些情况下可能会导致以下弊端：

  1. 性能问题：当表中包含大量列或者某些列的数据量较大时，使用SELECT *查询会导致查询结果集的大小增加，从而降低查询性能。此外，如果查询结果集中包含大量不需要的列，还会增加网络传输的开销。
  2. 可读性问题：当表中包含大量列时，使用SELECT *查询会导致查询结果集的可读性降低。这会使得开发人员在查看查询结果时更加困难，增加调试和维护的难度。
  3. 维护问题：当表结构发生变化时，使用SELECT *查询可能会导致查询结果的列发生变化，从而影响应用程序的正常运行。为了避免这种情况，需要在表结构发生变化时修改相关的查询语句，增加维护的难度。

4.2 查询列

-- 选择列
SELECT `name` 姓名, age 年龄 FROM t_student;
-- 为表设置别名
SELECT t.`name`,t.age FROM t_student t;

4.3 查询行

-- 范围查找
SELECT `name` 姓名, age 年龄 FROM t_student WHERE age > 23;
SELECT `name` 姓名, age 年龄 FROM t_student WHERE age >= 23 AND age <= 50;
SELECT `name` 姓名, age 年龄 FROM t_student WHERE age BETWEEN 23 AND 50;
SELECT `name` 姓名, age 年龄 FROM t_student WHERE age IN (23,50);
SELECT `name` 姓名, age 年龄 FROM t_student WHERE age >= 23 OR age <= 50;

-- 分组查询
SELECT COUNT(sex),sex FROM t_student GROUP BY sex;

-- 模糊查找
SELECT * FROM t_student WHERE `name` LIKE 'zha%';
SELECT * FROM t_student WHERE `name` LIKE '_ha%';

-- 排序
SELECT * FROM t_student ORDER BY age;
-- 分页查询
-- LIMIT 起始页码，返回的行数
SELECT * FROM t_student LIMIT 2,4;

5. 聚合函数

-- 平均数
SELECT AVG(score) FROM t_student;
SELECT AVG(age) FROM t_student;

-- 累计
SELECT SUM(age) FROM t_student;

-- 统计总数
SELECT COUNT(*) FROM t_student;

-- 最大最小，计算差额
SELECT MAX(age) 最大年龄,MIN(age) 最小年龄,MAX(age)-MIN(age) 年龄差 FROM t_student;

6. CASE语句

CASE是MySQL中的一个控制流语句，用于根据条件来返回不同的结果。CASE语句可以用于SELECT、UPDATE和DELETE语句中，以及在存储过程和函数中。

-- 等值比较
SELECT
	t.`name`,
CASE
		t.sex 
		WHEN 1 THEN
		'男' 
		WHEN 0 THEN
		'女' 
	END AS 性别 
FROM
	t_student t;

-- 条件匹配
SELECT 
	t.`name`,
	CASE
		t.sex 
		WHEN 1 THEN
		'男' 
		WHEN 0 THEN
		'女' 
	END AS 性别,
	t.score,
	CASE 
	WHEN t.score > 90 THEN 'A'
	WHEN t.score > 80 THEN 'B'
	WHEN t.score > 70 THEN 'C'
	WHEN t.score > 60 THEN 'D'
	ELSE
		'E'
END AS 成绩
FROM t_student t;

7. 扩展：行列转换

在MySQL中，可以使用CASE语句和聚合函数（如SUM、COUNT等）来实现行列转换。以下是一个简单的示例，展示了如何将表中的行数据转换为列数据。

假设有一个名为sales的表，包含以下字段：id（销售人员ID）、year（销售年份）和amount（销售额）。表结构如下：

CREATE TABLE t_sales (
  id INT,
  year INT,
  amount DECIMAL(10, 2)
);

现在，要将销售额按年份进行汇总，并将每个年份的销售额作为一个单独的列显示。可以使用以下查询：

SELECT
  id,
  SUM(CASE WHEN year = 2018 THEN amount ELSE 0 END) AS '2018',
  SUM(CASE WHEN year = 2019 THEN amount ELSE 0 END) AS '2019',
  SUM(CASE WHEN year = 2020 THEN amount ELSE 0 END) AS '2020'
FROM
  t_sales
GROUP BY
  id;

在这个查询中，使用CASE语句和SUM聚合函数将每个年份的销售额作为一个单独的列显示。当year字段的值等于指定的年份时，CASE语句返回amount字段的值，否则返回0。然后，使用SUM聚合函数对每个年份的销售额进行求和。

需要注意的是，这种方法适用于已知的列数。如果需要动态生成列，可以使用存储过程或者在应用程序中进行处理。在实际应用中，需要根据具体需求选择合适的方法来实现行列转换。

孟斯特

声明：本作品采用署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)进行许可，使用时请注明出处。

Author: mengbin

blog: mengbin

Github: mengbin92

cnblogs: 恋水无意

腾讯云开发者社区：孟斯特