MySQL - 常用的命令

当涉及到具体的数据库操作时，我会给出实际的示例，以更清楚地说明每个命令的用法。

CREATE DATABASE students;

SHOW DATABASES;

USE students;

CREATE TABLE student_info (    id INT PRIMARY KEY,    name VARCHAR(50),    age INT,    gender ENUM('男', '女', '未知'),    course VARCHAR(50));

SHOW TABLES;

SHOW COLUMNS FROM student_info;

SHOW INDEX FROM student_info;

SHOW TABLE STATUS FROM test;

SHOW TABLE STATUS FROM test LIKE '%test%';

INSERT INTO student_info (id, name, age, gender, course) VALUES(1, '张三男', 21, '男', '数学'),(2, '张三女', 22, '女', '语文'),(3, '李四未知', 20, '未知', '英语');

SELECT * FROM student_info;

UPDATE student_info SET course = '语文' WHERE id = 1;

DELETE FROM student_info WHERE id = 3;

SELECT * FROM student_info WHERE age > 20 AND gender = '男';

SELECT * FROM student_info ORDER BY age DESC;

SELECT COUNT(*) AS total_students FROM student_info;SELECT AVG(age) AS avg_age FROM student_info;SELECT SUM(age) AS total_age FROM student_info;

SELECT student_info.name, student_courses.course_nameFROM student_infoJOIN student_courses ON student_info.course = student_courses.course_name;

假设我们有一个名为"student_courses"的表，它保存课程信息，结构如下：

CREATE TABLE student_courses (    course_id INT PRIMARY KEY,    course_name VARCHAR(50));

并且表中的数据如下：

INSERT INTO student_courses (course_id, course_name) VALUES(1, '数学'),(2, '语文'),(3, '英语');

来源地址：https://blog.csdn.net/qq_43116031/article/details/131905182