使用终端操作MySQL
MySQL安装教程参考菜鸟教程：https://www.runoob.com/mysql/mysql-install.html
内容参考网站：
https://github.com/hjzCy/sql_node/blob/master/mysql/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0.md
一、基本操作
win下cmd进入方法：mysql -uroot -p
- 查看有什么数据库
show databases;
- 选择数据库
use databasesName;
- 查看该数据库中有哪些表
show tables;
- 查询表中的数据
select * from tableName;
- 退出数据库服务器
exit;
- 在数据库服务器中创建自己的数据库
create database databaseName;
- 创建一个数据表，（创建一个名为pet的表）
create TABLE pet(
name VARCHAR(20),
owner VARCHAR(20),
specise VARCHAR(20),
sex CHAR(1),
brith DATE,
death DATE );
注意事项:
- var()与varchar()的区别在于var()是定常的,哪怕存储的字符串没有达到"()“中数字的上限,var()依然会占用空格来填充空间.而varchar()则是不定长的,没有达到”()"中的上限则会自动去掉后面的空格;
- 定义最后一个字段的时候不要加",";
- 查看数据表的架构
describe tableName;
desc 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;
二、建表约束
- 主键约束
– 使某个字段不重复且不得为空，确保表内所有数据的唯一性。
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;
- 唯一主键
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;
- 建表时添加非空约束
– 约束某个字段不能为空
CREATE TABLE user (
id INT,
name VARCHAR(20) NOT NULL
);
- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);
外键约束
– 班级
CREATE TABLE classes (
id INT PRIMARY KEY,
name VARCHAR(20)
);
– 学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20),
class_id INT,
FOREIGN KEY(class_id) REFERENCES classes(id)
);
– 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
– 2. 主表中的记录被副表引用时，主表不可以被删除。
三、三大范式
CREATE TABLE myorder (
product_id INT,
customer_id INT,
product_name VARCHAR(20),
customer_name VARCHAR(20),
PRIMARY KEY (product_id, customer_id)
);
实际上，在这张订单表中，product_name 只依赖于 product_id ，customer_name 只依赖于 customer_id 。也就是说，product_name 和 customer_id 是没用关系的，customer_name 和 product_id 也是没有关系的。
这就不满足第二范式：其他列都必须完全依赖于主键列！
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(20)
);
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20)
);
拆分之后，myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键，而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计！
- 3NF
在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone VARCHAR(15)
);
表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20),
phone VARCHAR(15)
);
修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！
四、查询练习
准备数据
– 创建数据库
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,
FOREIGN KEY(t_no) REFERENCES teacher(no)
);
– 成绩表
CREATE TABLE score (
s_no VARCHAR(20) NOT NULL,
c_no VARCHAR(20) NOT NULL,
degree DECIMAL,
FOREIGN KEY(s_no) REFERENCES student(no),
FOREIGN KEY(c_no) REFERENCES course(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;
– 查询 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 开头的课程的平均分数。
SELECT * FROM score;
+
| s_no | c_no | degree |
+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2
AND c_no LIKE '3%';
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
|