搭建学生管理系统

1. 建立数据库
   mysql –u root –p 连接数据库服务器，用户名是root
   
   CREATE DATABASE student_system 创建名字为student_system的数据库
   
   Show databases; 查询数据库是否创建成功
   
2. USE student_system; 进入该数据库
   
   根据需求建立《学生信息表》
   

《学生成绩表》

查询表是否创建成功：

3. 学生信息插入数据
单条数据输入：

多条数据输入：

查询studentinfo的数据：SELECT * FROM studentinfo;

查询数据条数：SELECT COUNT(*) FROM studentinfo;

查询部分信息：SELECT stu_id,stu_name,stu_code FROM studentinfo;

显示条数查询：SELECT * FROM studentinfo LIMIT 0,100;

4. 学生成绩输入

联合查询：
SELECT stu_id,stu_name,stu_sex,stu_num,stu_code
FROM studentinfo
UNION
SELECT ALL FROM studentrecord;

可以选择任意相同数量的列进行联合查询

带条件查询
SELECT * FROM studentinfo LIMIT 0,100;
SELECT *
FROM studentinfo
WHERE stu_name = “黄三”;

模糊查询 LIKE
SELECT * FROM studentinfo LIMIT 0,100;
SELECT *
FROM studentinfo
WHERE stu_name LIKE “黄%”;

排序 ORDER BY
SELECT field1, field2,…fieldN table_name1, table_name2…
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2…] [ASC [DESC][默认 ASC]]

SELECT stu_num,match_record,datasec_record
FROM studentrecord
WHERE match_record >= 90
ORDER BY match_record;

分组 GROUP BY：
SELECT match_record,COUNT(*) datasec_record
FROM studentrecord
GROUP BY match_record;

删除数据
DELETE FROM studentinfo WHERE stu_name = “黄三”;

更新数据
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
UPDATE studentinfo
SET stu_name=“李四”
WHERE stu_name = “李三”;