一、安装好MySQL数据库之后,进入MySQL命令界面,使用命令配置数据库。(基本数据表可以网上查找资源,也可自己创建)
1、创建一个名为xscj的数据库(学生成绩缩写)并使用该数据库。
mysql> CREATE DATABASE database_xscj;
mysql> use database_xscj;
2、在里面创建一些学生成绩字段,中文名字,有数学语文英语三门课的成绩。成绩随机,学生人数10人。
创建表名为source的成绩表,并设置字段类型,添加数据进去:
mysql> INSERT INTO scores (name, math_score, chinese_score, english_score)
-> VALUES
-> ('张三', 85, 90, 92),
-> ('李四', 78, 88, 85),
-> ('王五', 92, 87, 90),
-> ('赵六', 80, 85, 82),
-> ('何七', 88, 92, 89),
-> ('肖八', 90, 91, 88),
-> ('谢九', 86, 89, 92);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
3、查看数据。
mysql> SELECT * FROM scores;
+----+--------+------------+---------------+---------------+
| id | name | math_score | chinese_score | english_score |
+----+--------+------------+---------------+---------------+
| 1 | 张三 | 85 | 90 | 92 |
| 2 | 李四 | 78 | 88 | 85 |
| 3 | 王五 | 92 | 87 | 90 |
| 4 | 赵六 | 80 | 85 | 82 |
| 5 | 何七 | 88 | 92 | 89 |
| 6 | 肖八 | 90 | 91 | 88 |
| 7 | 谢九 | 86 | 89 | 92 |
+----+--------+------------+---------------+---------------+
7 rows in set (0.01 sec)
二、进行基本查询
1、查询数学成绩大于等于80分的学生
mysql> SELECT * FROM scores WHERE math_score >= 80;
+----+--------+------------+---------------+---------------+
| id | name | math_score | chinese_score | english_score |
+----+--------+------------+---------------+---------------+
| 1 | 张三 | 85 | 90 | 92 |
| 3 | 王五 | 92 | 87 | 90 |
| 4 | 赵六 | 80 | 85 | 82 |
| 5 | 何七 | 88 | 92 | 89 |
| 6 | 肖八 | 90 | 91 | 88 |
| 7 | 谢九 | 86 | 89 | 92 |
+----+--------+------------+---------------+---------------+
6 rows in set (0.00 sec)
查询到了六条数据,数学分数大于等于80。
2、查询分数语文成绩在70到90之间的同学名字和成绩。
mysql> SELECT * FROM scores WHERE chinese_score BETWEEN 70 AND 90;
+----+--------+------------+---------------+---------------+
| id | name | math_score | chinese_score | english_score |
+----+--------+------------+---------------+---------------+
| 1 | 张三 | 85 | 90 | 92 |
| 2 | 李四 | 78 | 88 | 85 |
| 3 | 王五 | 92 | 87 | 90 |
| 4 | 赵六 | 80 | 85 | 82 |
| 7 | 谢九 | 86 | 89 | 92 |
+----+--------+------------+---------------+---------------+
5 rows in set (0.00 sec)
SELECT查询语句,可以筛选,条件查询。
三、集合函数演示
计算数学成绩的平均值
mysql> SELECT AVG(math_score) AS avg_math_score FROM scores;
+----------------+
| avg_math_score |
+----------------+
| 85.5714 |
+----------------+
1 row in set (0.01 sec)
计算语文成绩的最高值
mysql> SELECT MAX(chinese_score) AS max_chinese_score FROM scores;
+-------------------+
| max_chinese_score |
+-------------------+
| 92 |
+-------------------+
1 row in set (0.00 sec)
计算英语成绩的总和
mysql> SELECT SUM(english_score) AS total_english_score FROM scores;
+---------------------+
| total_english_score |
+---------------------+
| 618 |
+---------------------+
1 row in set (0.00 sec)
四、嵌套查询
mysql> SELECT * FROM scores WHERE math_score = (SELECT MAX(math_score) FROM scores);
+----+--------+------------+---------------+---------------+
| id | name | math_score | chinese_score | english_score |
+----+--------+------------+---------------+---------------+
| 3 | 王五 | 92 | 87 | 90 |
+----+--------+------------+---------------+---------------+
1 row in set (0.02 sec)
查询数学成绩最高的学生信息
五、联合查询
代码示例;
-- 将两个表的数据合并查询
SELECT student_name, score FROM scores
UNION ALL
SELECT student_name, score FROM other_scores;
六、组合查询
查询数学成绩大于等于80分并且语文成绩大于等于70分的学生
mysql> SELECT id, math_score,chinese_score
-> FROM scores
-> WHERE math_score >=80
-> AND chinese_score >=70;
+----+------------+---------------+
| id | math_score | chinese_score |
+----+------------+---------------+
| 1 | 85 | 90 |
| 3 | 92 | 87 |
| 4 | 80 | 85 |
| 5 | 88 | 92 |
| 6 | 90 | 91 |
| 7 | 86 | 89 |
+----+------------+---------------+
6 rows in set (0.00 sec)
七、截取函数示例
SUBSTRING函数
mysql> SELECT SUBSTRING(name, 1, 3) AS extracted_name
-> FROM students;
+----------------+
| extracted_name |
+----------------+
| 张三 |
| 李四 |
| 王五 |
| 赵六 |
| 刘七 |
+----------------+
5 rows in set (0.00 sec)
LEFT函数
mysql> SELECT LEFT(name, 2) AS extracted_name
-> FROM students;
+----------------+
| extracted_name |
+----------------+
| 张三 |
| 李四 |
| 王五 |
| 赵六 |
| 刘七 |
+----------------+
5 rows in set (0.01 sec)
RIGHT函数
mysql> SELECT RIGHT(name, 2) AS extracted_name
-> FROM students;
+----------------+
| extracted_name |
+----------------+
| 张三 |
| 李四 |
| 王五 |
| 赵六 |
| 刘七 |
+----------------+
5 rows in set (0.00 sec)