网站制作网站搭建重庆建工

网站制作网站搭建,重庆建工,湖南省专业建设公司网站,临沂网站建设哪家好引言在上一篇文章中我们已经完成了使用Mysql库前的所有准备了#xff0c;并且也初步了解了Mysql库#xff0c;接下来就让我们一起正式的开启Mysql的学习吧。一、MySQL基础操作实战1、创建数据库与表在Navicat Premium中#xff0c;创建数据库和表有两种常用方法#xff1a;…引言在上一篇文章中我们已经完成了使用Mysql库前的所有准备了并且也初步了解了Mysql库接下来就让我们一起正式的开启Mysql的学习吧。一、MySQL基础操作实战1、创建数据库与表在Navicat Premium中创建数据库和表有两种常用方法一是通过图形界面操作即右键点击连接后新建数据库再在库内右键新建表并设计字段二是直接执行 SQL 命令例如创建 school 数据库后依次创建我们所需要的表这些表通过主键与外键建立关联。1)、图形界面创建1.右键连接 → 新建数据库2.输入数据库名xxx字符集utf8mb43.双击打开xxx数据库4.右键表 → 新建表2)、SQL命令创建在 Navicat 中使用SQL命令创建数据库及表时可以依次执行以下操作首先通过 CREATE DATABASE school CHARACTER SET utf8mb4; 创建名为 school 的数据库并指定字符集接着使用 USE school; 切换到该数据库。随后创建三个核心数据表1) students 学生表以 stu_id 为主键包含姓名、性别、年龄、专业、班级、入学年份等字段并设置创建时间自动记录2) subjects 课程表以自增的 subject_id 为主键包含课程名称、学分和开课学期3) scores 成绩表用于关联学生与课程包含分数限定0-100分、考试类型和考试日期并通过外键 stu_id 和 subject_id 分别引用前两张表的主键确保数据完整性。-- 创建数据库 CREATE DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE school; -- 创建学生表 CREATE TABLE students ( stu_id BIGINT(10) PRIMARY KEY COMMENT 学生学号, stu_name VARCHAR(50) NOT NULL COMMENT 学生姓名, gender VARCHAR(10) COMMENT 学生性别, age INT COMMENT 学生年龄, major VARCHAR(100) COMMENT 学生专业, clazz VARCHAR(50) COMMENT 学生班级, enrollment_year INT COMMENT 入学年份, created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表; -- 创建课程表 CREATE TABLE subjects ( subject_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 课程ID, subject_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit INT DEFAULT 2 COMMENT 学分, semester INT COMMENT 开课学期 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT课程信息表; -- 创建成绩表 CREATE TABLE scores ( score_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 成绩ID, stu_id BIGINT(10) NOT NULL COMMENT 学生学号, subject_id INT NOT NULL COMMENT 课程ID, score DECIMAL(5,2) COMMENT 分数, -- 移除了CHECK约束 exam_type VARCHAR(20) DEFAULT 期末 COMMENT 考试类型, exam_date DATE COMMENT 考试日期, FOREIGN KEY (stu_id) REFERENCES students(stu_id), FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成绩表;2、插入数据在Navicat中我们可以通过SQL语句执行插入操作。使用INSERT INTO命令可以单条或批量地向数据库表中添加数据。对于学生表students可以采用单条插入方式指定学号、姓名、性别、年龄、专业、班级和入学年份等字段并为其赋值也可以通过一条批量插入语句一次性添加多条学生记录每条记录的值用括号括起并用逗号分隔。同样地课程表subjects和成绩表scores也可使用批量插入分别添加课程信息课程名、学分、学期和成绩信息学号、课程ID、分数、考试类型、考试日期。1)、单条插入INSERT INTO students (stu_id, stu_name, gender, age, major, clazz, enrollment_year) VALUES (20250101, 张三, 男, 20, 计算机科学与技术, 计科2501, 2025);2)、批量插入INSERT INTO students VALUES (20250101, 张三, 男, 20, 计算机科学与技术, 计科2501, 2025, NOW()), (20250102, 李四, 女, 20, 计算机科学与技术, 计科2501, 2025, NOW()), (20250103, 王五, 男, 19, 计算机科学与技术, 计科2501, 2025, NOW()), (20250201, 赵六, 女, 20, 软件工程, 软工2502, 2025, NOW()), (20250202, 孙七, 男, 20, 软件工程, 软工2502, 2025, NOW());3)、插入课程数据INSERT INTO subjects (subject_name, credit, semester) VALUES (数据库原理, 3, 3), (数据结构, 4, 2), (人工智能导论, 2, 4), (机器学习, 3, 5), (Python程序设计, 3, 1);4)、插入成绩数据INSERT INTO scores (stu_id, subject_id, score, exam_type, exam_date) VALUES (20250101, 1, 85.5, 期末, 2025-01-15), (20250101, 2, 92.0, 期末, 2025-01-16), (20250102, 1, 78.0, 期末, 2025-01-15), (20250102, 3, 88.5, 期末, 2025-01-18), (20250201, 4, 95.0, 期末, 2025-01-20);3、基础查询操作在 Navicat 中可以使用 SQL 的 SELECT 语句执行基础查询SELECT * FROM students 可查询表中所有数据若只需特定列可指定列名如 SELECT stu_id, stu_name, major FROM students通过 WHERE 子句可添加条件进行筛选例如 WHERE age 20 查找年龄为20岁的学生WHERE age 20 AND major 计算机科学与技术 组合多个条件或使用 BETWEEN 范围条件如 WHERE age BETWEEN 19 AND 21。1)、查询所有学生SELECT * FROM students;2)、查询特定列SELECT stu_id, stu_name, major FROM students;3)、带条件的查询-- 查询年龄等于20岁的学生 SELECT stu_name, age, major FROM students WHERE age 20; -- 查询年龄大于等于20岁的计算机专业学生 SELECT * FROM students WHERE age 20 AND major 计算机科学与技术; -- 查询年龄在19到21之间的学生 SELECT * FROM students WHERE age BETWEEN 19 AND 21;4、高级查询技巧模糊查询使用LIKE运算符配合通配符%匹配任意字符序列如%计算机%查找包含该词的专业_匹配单个字符如_三%查找姓名中第二个字为“三”的学生。排序查询通过ORDER BY子句实现可使用ASC升序默认或DESC降序并支持多列排序如先按年龄降序再按学号升序LIMIT子句可限制返回行数如取年龄最大的3人。聚合查询则利用函数对数据汇总COUNT()统计行数AVG()计算平均值MAX()找出最大值常与GROUP BY子句结合以分组统计如按专业统计人数并用HAVING对分组结果进行过滤如筛选最大年龄超过19的专业。1)、模糊查询-- 查询专业包含计算机的学生 SELECT * FROM students WHERE major LIKE %计算机%; -- 查询姓张的学生使用通配符 SELECT * FROM students WHERE stu_name LIKE 张%; -- 查询姓名第二个字是三的学生 SELECT * FROM students WHERE stu_name LIKE _三%;2)、排序查询-- 按年龄升序排列 SELECT * FROM students ORDER BY age ASC; -- 按年龄降序排列年龄相同按学号升序 SELECT * FROM students ORDER BY age DESC, stu_id ASC; -- 查询年龄最大的3名学生 SELECT * FROM students ORDER BY age DESC LIMIT 3;3)、聚合函数-- 统计学生总数 SELECT COUNT(*) AS 学生总数 FROM students; -- 计算平均年龄 SELECT AVG(age) AS 平均年龄 FROM students; -- 统计各专业学生人数 SELECT major, COUNT(*) AS 人数 FROM students GROUP BY major ORDER BY 人数 DESC; -- 查询各专业最大年龄HAVING子句过滤 SELECT major, MAX(age) AS 最大年龄 FROM students GROUP BY major HAVING MAX(age) 19;二、多表连接查询1、内连接INNER JOIN内连接INNER JOIN将学生表students、成绩表scores和课程表subjects关联起来获取了完整的学生成绩详情。先将学生表与成绩表通过学号stu_id进行连接再将结果与课程表通过课程 IDsubject_id连接最终选取学号、姓名、专业、课程名称、分数和考试日期等字段并按学号和考试日期排序输出。-- 查询学生成绩详情学生成绩课程 SELECT s.stu_id, s.stu_name, s.major, su.subject_name, sc.score, sc.exam_date FROM students s INNER JOIN scores sc ON s.stu_id sc.stu_id INNER JOIN subjects su ON sc.subject_id su.subject_id ORDER BY s.stu_id, sc.exam_date;2、左连接与右连接左连接LEFT JOIN和右连接RIGHT JOIN是两种重要的 SQL 外连接操作用于关联多个表并保留某些表中的全部记录。左连接会返回左表FROM 后的表的所有记录以及右表中匹配的记录。若右表无匹配则相关字段显示为 NULL。右连接与左连接相反它会返回右表的所有记录以及左表中匹配的记录。若左表无匹配则对应字段为 NULL。-- 左连接查询所有学生及他们的成绩即使没有成绩也显示 SELECT s.stu_id, s.stu_name, sc.score, su.subject_name FROM students s LEFT JOIN scores sc ON s.stu_id sc.stu_id LEFT JOIN subjects su ON sc.subject_id su.subject_id; -- 右连接查询所有成绩对应的学生信息 SELECT sc.score, su.subject_name, s.stu_name FROM scores sc RIGHT JOIN students s ON sc.stu_id s.stu_id RIGHT JOIN subjects su ON sc.subject_id su.subject_id;3、UNION操作联合查询UNION用于垂直合并多个SELECT的结果集UNION会去重而UNION ALL则保留所有行包括重复。-- 合并两个查询结果自动去重 SELECT stu_id, stu_name FROM students WHERE major 计算机科学与技术 UNION SELECT stu_id, stu_name FROM students WHERE age 19; -- 合并两个查询结果不去重 SELECT stu_name FROM students WHERE gender 男 UNION ALL SELECT stu_name FROM students WHERE age 20;三、表结构修改与管理1、添加字段-- 添加邮箱字段 ALTER TABLE students ADD COLUMN email VARCHAR(100); -- 添加带默认值的字段 ALTER TABLE students ADD COLUMN status VARCHAR(20) DEFAULT 在读; -- 在指定位置添加字段 ALTER TABLE students ADD COLUMN phone VARCHAR(20) AFTER stu_name;2、修改字段-- 修改字段类型 ALTER TABLE students MODIFY COLUMN major VARCHAR(150); -- 修改字段名和类型 ALTER TABLE students CHANGE COLUMN clazz class_name VARCHAR(50); -- 修改字段默认值 ALTER TABLE students ALTER COLUMN status SET DEFAULT 正常;3、删除字段-- 删除字段 ALTER TABLE students DROP COLUMN phone;4、表重命名与删除-- 重命名表 ALTER TABLE students RENAME TO student_info; -- 或 RENAME TABLE students TO student_info; -- 删除表谨慎操作 DROP TABLE IF EXISTS temp_table; -- 清空表数据不可恢复 TRUNCATE TABLE temp_table;四、SQL语言分类详解1、数据查询语言DQL数据查询语言DQL的核心是使用SELECT语句从数据库中检索信息首先进行基础查询以获取全部数据其次通过WHERE子句实现条件过滤再次利用GROUP BY进行分组并结合COUNT等聚合函数来统计各专业的学生人数最后通过JOIN连接多个表。-- 基础查询 SELECT * FROM students; -- 条件查询 SELECT * FROM students WHERE age 20; -- 分组聚合 SELECT major, COUNT(*) FROM students GROUP BY major; -- 连接查询 SELECT s.stu_name, sc.score FROM students s JOIN scores sc ON s.stu_id sc.stu_id;2、数据操作语言DML数据操作语言DML主要用于对数据库表中的记录进行增、改、删操作首先使用 INSERT 语句向 students 表中插入一条包含学号、姓名、性别、年龄等完整信息的新记录接着通过 UPDATE 语句并结合 WHERE 条件可以更新特定学生的年龄信息最后DELETE 语句同样依据条件删除指定的记录。-- 插入数据 INSERT INTO students VALUES (20250501, 测试, 男, 21, 测试专业, 测试班, 2025); -- 更新数据 UPDATE students SET age 22 WHERE stu_id 20250101; -- 删除数据 DELETE FROM students WHERE stu_id 20250501;3、数据定义语言DDL数据定义语言DDL用于定义和修改数据库中的结构首先使用 CREATE TABLE 语句创建一个名为 test_table 的新表并定义其初始结构包含 id 和 name 两个字段然后通过 ALTER TABLE 语句对该表进行结构修改为其新增一个 age 字段最后可以使用 DROP TABLE 语句将该表及其所有数据从数据库中彻底删除。-- 创建表 CREATE TABLE test_table (id INT, name VARCHAR(50)); -- 修改表 ALTER TABLE test_table ADD COLUMN age INT; -- 删除表 DROP TABLE test_table;4、数据控制语言DCL数据控制语言DCL专注于数据库的访问权限与用户管理其核心操作构成了一个完整的管理流程首先使用 CREATE USER 语句创建一个名为 test_user 且仅限本地登录的新用户并为其设置登录密码接着通过 GRANT 语句授予该用户对 school 数据库的所有表进行查询和插入数据的权限随后可使用 REVOKE 语句撤销其先前拥有的插入权限最后通过 DROP USER 语句可以彻底删除该用户账户。-- 创建用户 CREATE USER test_userlocalhost IDENTIFIED BY password123; -- 授权 GRANT SELECT, INSERT ON school.* TO test_userlocalhost; -- 撤销权限 REVOKE INSERT ON school.* FROM test_userlocalhost; -- 删除用户 DROP USER test_userlocalhost;5、事务控制语言TCL事务控制语言TCL的核心在于通过事务来确保一系列数据库操作的完整性和一致性。首先使用 START TRANSACTION 语句显式地开启一个事务然后在该事务内执行一连串的数据库修改最后如果所有操作均符合预期则使用 COMMIT 语句提交事务使所有更改永久生效如果在过程中出现问题则可以使用 ROLLBACK 语句回滚事务撤销该事务内所有尚未提交的操作使数据恢复到事务开始前的状态。-- 开始事务 START TRANSACTION; -- 执行一系列操作 UPDATE students SET age age 1 WHERE stu_id 20250101; INSERT INTO log_table (action, time) VALUES (更新年龄, NOW()); -- 提交事务永久保存 COMMIT; -- 或回滚事务取消所有操作 -- ROLLBACK;到这里我们就学完了Mysql中所有的基本内容了后续还需要用大量的练习来巩固自己所学的知识。