【MySQL】数据库可视化工具，MySQL命令

什么是数据库？ 数据库就是存储数据的“仓库”

为什么要使用数据库，数据存在哪里？ 数据持久化，数据库来存储数据

数据库的作用： (1) 存储大量数据，对数据进行管理；(2) 保证数据的完整性；(3) 安全与共享；(4) 数据可以进行组合，产生新的数据( 进行数据分析 )

数据库的历史？ (1) 层次模型；(2) 网状模型；(3) 关系型数据：表格是关系型数据库的核心单元( 1对多 1对1 多对多 )

什么是SQL？ SQL是结构化查询语言：(1) 数据定义语言 DDL；(2) 数据的查询语言 DQL；(3) 数据操作语言 DML；(4) 数据控制语言 ACL

数据管理： 不管是创建数据库，还是创建表格，必须 不能是中文

数据类型：

（1）整数数据：INT（2）浮点数据：float - 7位小数double - 15位小数decimal - 30位小数（3）字符串：char(N)：0-255varchar(N)：0-65535（4）日期数据：DATE：YYYY-MM-DD  (1000-01-01 ~ 9999-12-31)DATETIME：YYYY-MM-DD HH:MM:SS （1000-01-01 00:00:00 ~ 9999-12-31 23:59:59）

Navicat ( 可视化工具 )

数据库的可视化使用数据库管理工具 Navicat ， 它提供直观的可视化界面，支持多种数据库系统，如 MySQL 、 MariaDB 、 MongoDB 、 SQL Server 、 Oracle 、 PostgreSQL 及 SQLite 等，用户可以通过它轻松连接数据库、执行 SQL 查询、管理数据库结构、导入/导出数据以及进行数据同步等操作

下载地址：https://www.navicat.com.cn/products

-- 单行注释/*多行注释*/

MySQL ( 下载 / 启动 )

下载地址：https://dev.mysql.com/downloads/

CMD命令行操作:

SQL ( 结构化查询语言 )

创建数据库:

CREATE DATABASE 数据库名;

使用数据库:

USE 数据库名;

查看数据库表:

show tables;

删除数据库:

DROP DATABASE 数据库名;

操作表

创建表格:

CREATE TABLE 表名(列名1 列的数据类型 列的约束,列名2 列的数据类型 列的约束,......)例如：CREATE TABLE biao_1(id INT,stuName VARCHAR(10))

列的约束：主键 / 自增长 / 唯一 / 不为空 / 默认值 / 外键 / 外键约束

-- 主键约束，当设置某一列为主键之后，该列存储的条件就变为非空不重复
CREATE TABLE biao_2(
id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键列：PRIMARY KEY     自增长：AUTO_INCREMENT
stuName VARCHAR(10),
phone VARCHAR(11) UNIQUE NOT NULL,   -- 唯一约束：UNIQUE     不为空：NOT NULL
sex CHAR(4) DEFAULT '男'   -- 默认约束，可以用双引号或单引号设置：DEFAULT 
)-- 创建主表（班级表）
CREATE TABLE t_class(
c_id INT PRIMARY KEY,
c_name VARCHAR(20)
)
-- 创建从表（有依赖关系的学生表）
CREATE TABLE t_student7(
id INT PRIMARY KEY AUTO_INCREMENT,
stuname VARCHAR(10),
phone INT UNIQUE NOT NULL,
sex CHAR(5) DEFAULT '男',
classid INT REFERENCES t_class(c_id),   -- 添加外键
CONSTRAINT FOREIGN KEY(classid) REFERENCES t_class(c_id)   -- 添加外键约束（添加外键约束之后classid的值必须是t_class表中的c_id列的值）
)

查看表结构:

DESC 表名;
-- 或
SHOW COLUMNS FROM 表名;

修改表

设置自增长的起始位置:

ALTER TABLE 表名 AUTO_INCREMENT=数字;

添加列:

ALTER TABLE 表名 ADD 列名 列的数据类型;

删除列:

ALTER TABLE 表名 DROP COLUMN 列名;

删除表：有主从关系的表，删除的时候是先删除从表再删除主表

DROP TABLE 表名;

为部分列添加数据:

INSERT INTO 表名(grade,stuname,phone,classid) VALUES(1,"大黄",789,5);INSERT INTO 表名(stuname,phone,classid) VALUES("太阳",711,8);

为所有列添加数据:

INSERT INTO 表名 VALUES(NULL,"月亮",99,6,60);

同时添加多行记录:

INSERT INTO 表名 VALUES(NULL,"月亮2",991,5,61),
(NULL,"月亮3",992,6,62),
(NULL,"月亮4",993,8,63),
(NULL,"月亮5",994,6,64);

修改整列的值:

UPDATE 表名 SET grade=100,列名=值;

数据筛选/过滤:

UPDATE 表名 SET grade=66 WHERE stuname="大黄";

删除指定数据:

DELETE FROM 表名 WHERE stuname="dd";

删除该表所有数据（并非连同表格一起删除）:

DELETE FROM 表名;  -- 数据可以复原

清空所有数据，无法复原:

TRUNCATE TABLE 表名;

表格备份:

CREATE TABLE t_student_beifen SELECT * FROM t_student;  -- 会将原表的内容一起备份CREATE TABLE t_student_beifen1 SELECT * FROM t_student WHERE 1=0;  -- 只要表结构，不要表内容的备份

简单查询

select 列名1,列名2 ... from 表名

SELECT stuname,phone from t_student;  -- select 和 from中间的列表示投影的结果列，也可以用通配符 * 表示所有列 

条件的筛选: where

SELECT * from t_student WHERE id=2;  -- WHERE  执行顺序：1.from  2.where  3.select

去重: distinct

SELECT DISTINCT sex FROM t_student;  -- DISTINCT

条件：并且and / 或者or

SELECT * FROM t_student WHERE sex="女" AND age=4;
SELECT * FROM t_student WHERE sex="女" OR age=4;-- and 和 or 同时存在的时候 and的优先级更高
SELECT * FROM t_student WHERE age<=20 OR age>=18 AND sex="女";-- 查询学生年龄为18或者19或者20岁的
SELECT * FROM t_student WHERE age=18 OR age=19 OR age=20;

上一个查询语句等同于: in()

SELECT * FROM t_student WHERE age IN(18,19,20);

排除括号内的条件（取反）: not in()

SELECT * FROM t_student WHERE age NOT IN(18,19,20);

and and :

SELECT * FROM t_student WHERE age>=16 AND age<=22;

上一个查询语句等同于: between and

SELECT * FROM t_student WHERE age BETWEEN 16 AND 22;

模糊查询: 百分号% 下划线_

-- 模糊查询用到的 % 符号表示 0-N个字符
-- 模糊查询用到的 _ 符号表示 1个字符-- 查询姓“方”的学生
SELECT * FROM t_student WHERE stuname LIKE "方%";-- 名字以“甜”结尾的
SELECT * FROM t_student WHERE stuname LIKE "%甜";-- 名字包含“小”的
SELECT * FROM t_student WHERE stuname LIKE "%小%";-- 查询所有姓“小”且名字长度是三个字的学生
SELECT * FROM t_student WHERE stuname LIKE "小__";
SELECT * FROM t_student WHERE stuname LIKE "_小_";

为空的查询:

SELECT * FROM t_student WHERE age IS NULL;  -- 表格没有数据，没有填写
SELECT * FROM t_student WHERE age="";  -- 空格，空格也可以=0

排序: ASC默认升序 / DESC降序

SELECT * FROM t_student ORDER BY age ASC;  -- 默认升序SELECT * FROM t_student ORDER BY age DESC;  -- 降序-- 年龄升序，classid降序（使用情况少）
SELECT * FROM t_student ORDER BY age ASC,classid DESC;

指定返回信息的条数: limit

SELECT * FROM t_student LIMIT 3;-- 指定从哪里开始返回，返回几条
SELECT * FROM t_student LIMIT 0,3;  -- 1页
SELECT * FROM t_student LIMIT 3,3;  -- 2页

对筛选的结果进行: 计算 / 取别名AS

SELECT age AS '年龄',age+10 AS '十年后' FROM t_student;  -- as 表示为指定列起别名，别名不会影响表格列名SELECT a.age AS '年龄',age+10 AS '十年后' FROM t_student AS a;  -- 表别名SELECT * FROM t_student WHERE age>18 ORDER BY age LIMIT 3;
-- 执行顺序：
SELECT *   -- 3,6
FROM t_student   -- 1
WHERE age>18   -- 2
ORDER BY age   -- 4
LIMIT 3   -- 5

聚合函数: 计数 count(指定列名/*)

SELECT COUNT(*) AS '学生数量' FROM t_student;  -- 9SELECT COUNT(age) FROM t_student;  -- 8，count计算结果不包含null

聚合函数: 求和 sum()

SELECT SUM(age) FROM t_student;

聚合函数: 求平均值 avg()

SELECT AVG(age) FROM t_student;  -- 平均值的计算结果不包含nullSELECT SUM(age)/COUNT(*) FROM t_student;-- 求平均值的时候，还可以使用 ifnull 如果为空可以指定替换数值
SELECT AVG(IFNULL(age,20)) FROM t_student;

聚合函数: 最大max / 最小值min

SELECT MAX(age),MIN(age) FROM t_student;

分组: group by (当使用group by的时候，select后面所跟随的查询内容必须是聚合函数和被group by所指定的内容)

-- 查询每一个班的人数
SELECT classid,COUNT(*) FROM t_student GROUP BY classid;-- 查询每一个班男生的数量
SELECT classid,COUNT(*) FROM t_student WHERE sex='男' GROUP BY classid;

二次筛选: having

-- 查询男生数量大于1的班级
SELECT classid,COUNT(*) FROM t_student WHERE sex='男' GROUP BY classid HAVING COUNT(*)>1;-- 查询男生数量大于1的班级并且按照人数排列
SELECT classid,COUNT(*) FROM t_student WHERE sex='男' GROUP BY classid HAVING COUNT(*)>1 ORDER BY COUNT(*);-- 查询男生数量大于1的班级并且按照人数排列，只显示前两条
SELECT classid,COUNT(*) FROM t_student 
WHERE sex='男' GROUP BY classid 
HAVING COUNT(*)>1 ORDER BY COUNT(*) 
LIMIT 2;-- 执行顺序
SELECT classid,COUNT(*)  -- 4，8
FROM t_student  -- 1
WHERE sex='男'  -- 2
GROUP BY classid  -- 3
HAVING COUNT(*)>1  -- 5
ORDER BY COUNT(*)  -- 6
LIMIT 2  -- 7

嵌套查询

创建主表 / 从表:

-- 创建主表
CREATE TABLE t_class(
c_id INT PRIMARY KEY,
c_clsname VARCHAR(50)
)INSERT INTO t_class(c_id,c_clsname) VALUES
(1,'一班'),
(2,'二班'),
(3,'三班'),
(4,'四班');-- 创建从表
CREATE TABLE t_student(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_stuname VARCHAR(10),
s_phone INT UNIQUE,
s_gender VARCHAR(5) DEFAULT "男",
s_classid INT REFERENCES t_class(c_id),
CONSTRAINT FOREIGN KEY(s_classid) REFERENCES t_class(c_id),
s_age INT,
s_grade INT
)INSERT INTO t_student(s_stuname,s_phone,s_classid,s_age,s_grade) VALUES
('郑二',124,1,20,60),
('王三',122,2,25,99),
('赵六',334,3,22,60),
('张三三',555,2,17,89),
('李四',1224,2,26,76),
('赵',5566,1,19,67),
('三娃',NULL,1,20,80),
('一',NULL,3,21,60),
('二',NULL,1,22,90),
('三',NULL,3,34,60),
('四',NULL,1,20,54),
('五',NULL,1,23,88),
('六',NULL,1,17,62)

select 嵌套：子查询依赖主查询，不能够独立运行，主查询执行一次，子查询也会执行一次

-- 查询所有学生姓名，性别，分数，班级名称
-- 1.select s_stuname,s_gender,s_grade,s_classid from t_student;
-- 2.select c_clasname from t_class where c_id=t_student.s_classid;SELECT s_stuname,s_gender,s_grade,(SELECT c_clsname FROM t_class c WHERE c.c_id=s.s_classid) FROM t_student s;

from 嵌套：子查询只需要执行一次，子查询不依赖主查询能独立运行，但是from嵌套必须给子查询起别名

-- 查询性别为女并且姓名为张三的学生
-- 1.先查到有哪些女学生
-- 2.从女学生中去找叫张三的人
SELECT * FROM t_student WHERE s_gender='女';
SELECT * FROM 女学生的表 WHERE s_stuname='张三';SELECT * FROM (SELECT * FROM t_student WHERE s_gender='女') AS t WHERE s_stuname='张三';

where 嵌套

-- 查询“一班”的学生有哪些
-- 1.首先在班级表查询“一班”对应的id
-- 2.在学生表中查询 id 对应的学生有哪些
SELECT c_id FROM t_class WHERE c_clsname='一班';
SELECT * FROM t_student WHERE s_classid=查出来的id;SELECT * FROM t_student WHERE s_classid=(SELECT c_id FROM t_class WHERE c_clsname='一班');-- 查询和张三同学考同样分数的同学信息
SELECT * FROM t_student WHERE s_grade=(SELECT s_grade FROM t_student WHERE s_stuname='张三') AND s_stuname!='张三';-- 查询哪个班没有学生
SELECT * from t_class WHERE c_id NOT IN(SELECT DISTINCT s_classid from t_student);-- 查询比所有名字叫“张三”的学生年龄都要大的学生信息
SELECT * FROM t_student WHERE s_age>ALL(SELECT s_age FROM t_student WHERE s_stuname='张三'); -- 大于所有名字叫张三的人的年龄SELECT * FROM t_student WHERE s_age>ANY(SELECT s_age FROM t_student WHERE s_stuname='张三'); -- 大于任意一个名字叫张三的人的年龄

表连接

内连接：inner join ...on（条件），通常省略 inner

SELECT * FROM (连接之后的表);
SELECT s_stuname,c_clsname FROM (t_class c JOIN t_student s ON c.c_id =s.s_classid);-- 查询“一班”的所有学生
SELECT * FROM (t_class c JOIN t_student s ON c.c_id =s.s_classid) WHERE c_clsname="一班";

外连接: 优先完整展示左边还是右边

-- 1) 左连接  left outer join  通常省略 outer
-- 2) 右连接  right outer join -- 查询所有班级信息，没有学生的班级也要显示 
SELECT * FROM (t_student s RIGHT JOIN t_class c ON c.c_id = s.s_classid )-- 员工和直接上级的姓名
SELECT e2.ename AS "员工姓名",e1.ename AS "上级姓名" FROM emp e1 JOIN emp e2 ON e2.mgr=e1.empno;