前言
SQL学习笔记
一、在终端中启动mysql
启动mysql
-
按WIN+R快捷键,输入cmd,点击确定。
-
在命令行输入"net start mysql",开启mysql服务。 停止服务可以输入"net stop mysql"。
命令行输入"net start mysql"可以看到如下图所示(前提是你已经安装了mysql,咋安装mysql的话,可以自行找度娘) -
输入"mysql -uroot -p",再输入数据库密码就可以登录数据库了;注意 输入格式是:mysql -uroot -p***** 后面的星号表示你的数据库的密码,然后你就能看到下面这张图了
现在我们就进入到了mysql中了,可以在里面输入符合mysql语法的语句了;退出的话输入 exit; 注意分号结尾,我们就可以退出mysql了,如下图
二、mysql基础语法及建表相关
1、基础语法
注意,每一条sql语句是要以分号结尾
-- 显示所有数据库
show databases;
-- 创建数据库
CREATE DATABASE test;
-- 切换数据库
use test;
-- 显示数据库中的所有表
show tables;
-- 创建数据表
CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
-- 查看数据表结构
-- describe pet;
desc pet;
-- 查询表
SELECT * from pet;
-- 插入数据
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);
-- 修改数据
UPDATE pet SET name = 'squirrel' where owner = 'Diane';
-- 删除数据
DELETE FROM pet where name = 'squirrel';
-- 删除表
DROP TABLE myorder;
2、建表约束
1)主键约束
-- 主键约束
-- 使某个字段不重复且不得为空,确保表内所有数据的唯一性。
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 联合主键
-- 联合主键中的每个字段都不能为空,并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
id INT,
name VARCHAR(20),
password VARCHAR(20),
PRIMARY KEY(id, name)
);
-- 自增约束
-- 自增约束的主键由系统自动递增分配。
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
-- 添加主键约束
-- 如果忘记设置主键,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;
-- 删除主键
ALTER TABLE user drop PRIMARY KEY;
2)唯一主键
-- 建表时创建唯一主键
CREATE TABLE user (
id INT,
name VARCHAR(20),
UNIQUE(name)
);
-- 添加唯一主键
-- 如果建表时没有设置唯一建,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;
-- 删除唯一主键
ALTER TABLE user DROP INDEX name;
3)非空约束
-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
id INT,
name VARCHAR(20) NOT NULL
);
-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);
4)默认约束
-- 建表时添加默认约束
-- 约束某个字段的默认值
CREATE TABLE user2 (
id INT,
name VARCHAR(20),
age INT DEFAULT 10
);
-- 移除非空约束
ALTER TABLE user MODIFY age INT;
5)外键约束
-- 班级
CREATE TABLE classes (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20),
-- 这里的 class_id 要和 classes 中的 id 字段相关联
class_id INT,
-- 表示 class_id 的值必须来自于 classes 中的 id 字段值
FOREIGN KEY(class_id) REFERENCES classes(id)
);
-- 1. 主表(父表)classes 中没有的数据值,在副表(子表)students 中,是不可以使用的;
-- 2. 主表中的记录被副表引用时,主表不可以被删除。
范式
注:范式设计得越详细,对某些实际操作可能会更好,但并非都有好处,需要对项目的实际情况进行设定
三、SQL查询练习
可以整一个 Navicat 连接本地的数据库,在Navicat里面编写sql查询语句可能会更加方便一些(习惯终端的大佬忽视。。。)
然后就看到了下面这张图
注意,这里的密码如果没有改过的话,就是安装mysql中初始化的时候得到的密码;要是忘记了,就只能再初始化一下数据了
1、创建用于查询的数据表
-- 创建数据库
CREATE DATABASE select_test;
-- 切换数据库
USE select_test;
-- 创建学生表
CREATE TABLE student (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE, -- 生日
class VARCHAR(20) -- 所在班级
);
-- 创建教师表
CREATE TABLE teacher (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE,
profession VARCHAR(20) NOT NULL, -- 职称
department VARCHAR(20) NOT NULL -- 部门
);
-- 创建课程表
CREATE TABLE course (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
t_no VARCHAR(20) NOT NULL, -- 教师编号
-- 表示该 tno 来自于 teacher 表中的 no 字段值
FOREIGN KEY(t_no) REFERENCES teacher(no)
);
-- 成绩表
CREATE TABLE score (
s_no VARCHAR(20) NOT NULL, -- 学生编号
c_no VARCHAR(20) NOT NULL, -- 课程号
degree DECIMAL, -- 成绩
-- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
FOREIGN KEY(s_no) REFERENCES student(no),
FOREIGN KEY(c_no) REFERENCES course(no),
-- 设置 s_no, c_no 为联合主键
PRIMARY KEY(s_no, c_no)
);
-- 查看所有表
SHOW TABLES;
-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');
-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');
-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');
-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');
-- 查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;
2、查询练习
1)单表查询常用操作
-- 查询 student 表的所有行
SELECT * FROM student;
-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;
-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;
-- 查询 score 表中成绩在60-80之间的所有行(区间查询和运算符查询)
-- BETWEEN xx AND xx: 查询区间, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;
-- 查询 score 表中成绩为 85, 86 或 88 的行
-- IN: 查询规定中的多个值
SELECT * FROM score WHERE degree IN (85, 86, 88);
-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
-- or: 表示或者关系
SELECT * FROM student WHERE class = '95031' or sex = '女';
-- 以 class 降序的方式查询 student 表的所有行
-- DESC: 降序,从高到低
-- ASC(默认): 升序,从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;
-- 以 c_no 升序、degree 降序查询 score 表的所有行
SELECT * FROM score ORDER BY c_no ASC, degree DESC;
-- 查询 "95031" 班的学生人数
-- COUNT: 统计
SELECT COUNT(*) FROM student WHERE class = '95031';
-- 查询 score 表中的最高分的学生学号和课程编号(子查询或排序查询)。
-- (SELECT MAX(degree) FROM score): 子查询,算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);
-- 排序查询
-- LIMIT r, n: 表示从第r行开始,查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;
-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';
-- GROUP BY: 分组查询
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
--模糊查询
--查询 score 表中至少有 2 名学生选修,并以 3 开头的课程的平均分数
-- 首先把 c_no, AVG(degree) 通过分组查询出来
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
+-------+-------------+
| c_no | AVG(degree) |
+-------+-------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+
-- 再查询出至少有 2 名学生选修的课程
-- HAVING: 表示持有
HAVING COUNT(c_no) >= 2
-- 并且是以 3 开头的课程
-- LIKE 表示模糊查询,"%" 是一个通配符,匹配 "3" 后面的任意字符。
AND c_no LIKE '3%';
-- 把前面的SQL语句拼接起来,
-- 后面加上一个 COUNT(*),表示将每个分组的个数也查询出来。
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
+-------+-------------+----------+
| c_no | AVG(degree) | COUNT(*) |
+-------+-------------+----------+
| 3-105 | 85.3333 | 3 |
| 3-245 | 76.3333 | 3 |
+-------+-------------+----------+
2)多表查询
--查询所有学生的 name,以及该学生在 score 表中对应的 c_no 和 degree
-- FROM...: 表示从 student, score 表中查询
-- WHERE 的条件表示为,只有在 student.no 和 score.s_no 相等时才显示出来。
SELECT name, c_no, degree FROM student, score
WHERE student.no = score.s_no;
+-----------+-------+--------+
| name | c_no | degree |
+-----------+-------+--------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
+-----------+-------+--------+
--查询所有学生的 no 、课程名称 ( course 表中的 name ) 和成绩 ( score 表中的 degree ) 列
-- 增加一个查询字段 name,分别从 score、course 这两个表中查询。
-- as 表示取一个该字段的别名。
SELECT s_no, name as c_name, degree FROM score, course
WHERE score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name | degree |
+------+-----------------+--------+
| 103 | 计算机导论 | 92 |
| 105 | 计算机导论 | 88 |
| 109 | 计算机导论 | 76 |
| 103 | 操作系统 | 86 |
| 105 | 操作系统 | 75 |
| 109 | 操作系统 | 68 |
| 103 | 数字电路 | 85 |
| 105 | 数字电路 | 79 |
| 109 | 数字电路 | 81 |
+------+-----------------+--------+
--查询所有学生的 name 、课程名 ( course 表中的 name ) 和 degree
-- 由于字段名存在重复,使用 "表名.字段名 as 别名" 代替;表也可以使用别名,只需要在表名的后面 空一格 再写别名即可(as可以省略)
SELECT student.name as s_name, course.name as c_name, degree
FROM student, score, course
WHERE student.NO = score.s_no
AND score.c_no = course.no;
--结果如下
+--------+------------+--------+
| s_name | c_name | degree |
+--------+------------+--------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
+--------+------------+--------+
--查询 95031 班学生每门课程的平均成绩
-- IN (..): 将筛选出的学生号当做 s_no 的条件查询
SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+-------+-------------+
| c_no | AVG(degree) |
+-------+-------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
+-------+-------------+
小结
以上为参考网上各位大佬笔记及相关视频,在这里仅做学习记录,欢迎各位大佬批评指正。
道阻且长,行则将至
参考
参考:https://www.bilibili.com/video/BV1Vt411z7wy?p=64&spm_id_from=pageDriver
参考:https://github.com/hjzCy/sql_node/blob/master/mysql/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0.md