使用终端操作数据库MySQL

使用终端操作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

1. 查看有什么数据库

show databases;

2. 选择数据库

use databasesName;

3. 查看该数据库中有哪些表

show tables;

4. 查询表中的数据

select * from tableName;

5. 退出数据库服务器

exit;

6. 在数据库服务器中创建自己的数据库

create database databaseName;

6. 创建一个数据表，（创建一个名为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()则是不定长的,没有达到”()"中的上限则会自动去掉后面的空格;
• 定义最后一个字段的时候不要加",";

7. 查看数据表的架构

describe tableName;
desc pet;

8. 插入数据

INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);

9. 修改数据

UPDATE pet SET name = 'squirrel' where owner = 'Diane';

10. 删除数据

DELETE FROM pet where name = 'squirrel';

11. 删除表

DROP TABLE myorder;

二、建表约束

1. 主键约束
   – 使某个字段不重复且不得为空，确保表内所有数据的唯一性。

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

2. 联合主键
   – 联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。

CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);

3. 自增约束
   – 自增约束的主键由系统自动递增分配。

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

4. 添加主键约束
   – 如果忘记设置主键，还可以通过SQL语句设置（两种方式）：

ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

5. 删除主键

ALTER TABLE user drop PRIMARY KEY;

6. 唯一主键

CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

7. 添加唯一主键
   – 如果建表时没有设置唯一建，还可以通过SQL语句设置（两种方式）：

ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

8. 删除唯一主键

ALTER TABLE user DROP INDEX name;

9. 建表时添加非空约束
   – 约束某个字段不能为空

CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

10. 移除非空约束

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 要和 classes 中的 id 字段相关联
    class_id INT,
    -- 表示 class_id 的值必须来自于 classes 中的 id 字段值
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

– 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
– 2. 主表中的记录被副表引用时，主表不可以被删除。

---

三、三大范式

• 1NF
  只要字段值还可以继续拆分，就不满足第一范式。
  范式设计得越详细，对某些实际操作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。

• 2NF
  在满足第一范式的前提下，其他列都必须完全依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下：

-- 订单表
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, -- 教师编号
    -- 表示该 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;

– 查询 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;
-- c_no 课程编号
+------+-------+--------+
| 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 |
+------+-------+--------+

-- 首先把 c_no, AVG(degree) 通过分组查询出来
SELECT c_no, AVG(degree) FROM score GROUP BY c_no

-- 再查询出至少有 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%';

索引
数据结构是B-Tree(自平衡二叉树)
考虑给字段添加索引的条件：

1. 数据量庞大
2. 该字段经常出现在where之后，一条件的形式存在。
3. 该字段很少进行DML（insert delete update）操作。因为DML之后索引需要重新排序。
   建议不要随意添加索引，索引是需要维护的，太多反而会降低系统性能。建议通过主键查询，效率是比较高的。

创建索引

--给table_name 表的column_name添加索引indexName
CREATE INDEX indexName ON table_name (column_name)

删除索引

DROP INDEX [indexName] ON mytable; 

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

用EXPLAIN查看所使用的SQL语句是否使用了索引

EXPLAIN SELECT * FROM score WHERE c_no = '3-105';

索引失效
模糊查询中使用以%开始，索引会失效。

SELECT * FROM score WHERE c_no LIKE '%5';

使用OR的时候，两边存在没有索引的条件，索引会失效

SELECT * FROM score WHERE c_no ='3-105' OR c_no ='3-106';

使用复合索引的时候，没有使用左侧的列，索引失效。

CREATE INDEX indexName ON table_name (column_name1，column_name2);
--用column_name1进行索引，没问题
--用column_name2进行索引，失效

在where当中索引列参与了运算，索引失效

SELECT * FROM score WHERE degree + 1  ='85';