MySQL学习笔记（超级详细）

想学习MySQL的同学建议先安装Navicat,建议网上搜集资料下载破解版。

没有Navcat的同学，（前提是已经安装了mysql数据库）打开命令提示符，以管理员身份什么运行。

1.DML操作数据（添加、修改、删除）

启动数据库

net start mysql

登录数据库 mysql -u用户名 -p密码，一般自己安装都会把用户名设置成root，密码设置成123456

mysql -urooot -p123456

连接别人的mysql

mysql -uroot -p密码 -h别人的ip地址 -p端口号（一般为3306）

查询所有的数据库

show databases;

创建数据库

create database 数据库名称;

如果数据库存在了,就不创建了，如果数据库不存在，就要创建。

create database if not exists 数据库名称;

删除数据库

drop database 数据库名称;

如果数据库存在可以删除，如果不存在，就不能删除。
 

drop database if exists 数据库名称;

要想查询数据库里面表格，首先要进入数据库

use 数据库名称;

查看当前使用的数据库名称

select database();

查看当前数据库下所有的表

show tables;

查看具体的某一张表格

desc 表名称;

创建表格（以学生表stu为例）

create table stu(
                 id int primary key,
                 name varchar(30) not null,
                 age int not null,
                 sex char(1) not null,
                 admission date not null,
                 class varchar(10) not null,
                 xuefei double(6,2) not null
);
                 
                 

讲解一下double(6,2)第二个数字2代表保留两位小数，如果学费为5000元，5000元由四个数字组成，5、0、0、0，所以4+2=6，组成前面的6；  

插入数据

insert into stu(id,name,age,sex,admission,class,xuefei) 
values (1,'张三',20,'男','2022-10-12','2班',5000);

删除表

drop table 表名;

删除表时，判断表是否存在

drop table if exists 表名;

修改表名

alter table 表名 rename to 新的表名;

在现有的表中添加新的一列

alter table 表名 add 列名 数据类型;

修改列的数据类型

alter table 表名 modify 列名 新的数据类型;

同时修改列名和数据类型

alter table 表名 change 列名 新列名 新数据类型;

删除某一列

alter table 表名 drop 列名;

2.DQL（基础内容查询）

查看表的所有内容

select * from 表名;

我先创建一个学生表，然后围绕此学生表进行举例说明。

create table student(
                     id int primary key,
                     name varchar(55) not null,
                     sex char(5) not null,
                     math int DEFAULT(0),
                     english int DEFAULT(0)
);

insert into student(id,name,sex,math,english) values 
(1,'张三','男',89,78),
(2,'王五','男',99,67),
(3,'李四','女',67,76),
(4,'武溜','男',96,95),
(5,'蔡徐坤','女',93,82),
(6,'王元','女',98,73);

查看男同学和女同学的数学平均分（group by后面是分组的条件，图中为性别分组，所以跟着sex）

select sex,avg(math) from student group by sex;

查看男同学和女同学的数学平均分，以及男同学的人数和女同学的人数count(*)是统计分组后，各组的人数

SELECT sex,AVG(math),COUNT(*) from student GROUP BY sex;

查看男同学和女同学的数学平均分，以及分组后各组人数，分数低于80分的不参与分组。

SELECT sex,AVG(math),COUNT(*) from student WHERE math>=80 GROUP BY sex;

查看男同学和女同学的数学平均分，以及分组后各组人数，分数低于80分的不参与分组，且分组后的人数大于2人。

SELECT sex,AVG(math),COUNT(*) from student WHERE math>=80 GROUP BY sex HAVING COUNT(*)>2;

where 是分组之前进行限定，不满足where条件，不能分组，而having是分组之后对结果进行过滤。用到count函数，统计某一列时，这一列不能有null。null值不参与聚合函数的运算。
查看学生信息，按数学成绩升序，当数学成绩相同时按英语成绩降序排名

SELECT*FROM student ORDER BY math asc,english desc;

如果不表明升序和降序，就是asc默认的升序。

分页查询语法（起始索引=(当前页码数-1)*每页显示的条数）

select*from 表名 limit 起始索引，查询条目数;
SELECT*from student LIMIT 0,3;

模糊查询

查询姓‘李’的同学

select * from student where name like '李%';

查询第二个字是‘四’的同学

select * from student where name like '_四%';

查询名字中含有‘元’字的同学

select * from student where name like '%元%';

DQL小结

select 
        字段列表
from
        表名列表
where
          条件列表
group by 
             分组字段
having
           分组后的条件
order by
             排序字段
limit
       分页限定
null是空值不是默认值

auto_increment  自动增长，要求当列是数字类型且唯一约束。

CREATE TABLE stu(
id int PRIMARY KEY auto_increment,
name VARCHAR(15) NOT NULL,
age int not NULL);

INSERT INTO stu (id,name,age)VALUES(null,'张三',18);
INSERT INTO stu (id,name,age)VALUES(null,'李四',21);
INSERT INTO stu (name,age)VALUES('王元',22);

id设置为null值，以及省略id值，id会自动补充，并排序。

 外键约束

外键是让两个表之间建立链接。

添加约束，可以创建表时，添加约束，也可以建完表之后添加约束。

创建一个员工表和一个部门表，并在创建员工表的同时添加约束。

CREATE TABLE dept(
id int PRIMARY KEY auto_increment,
dep_name VARCHAR(15) NOT NULL,
addr VARCHAR(15) not NULL);

CREATE TABLE emp(
id int PRIMARY KEY auto_increment,
name VARCHAR(15) NOT NULL,
age int NOT NULL,
dep_id int,
CONSTRAINT fk_dept_emp FOREIGN KEY(dep_id) REFERENCES dept (id)
);

代码中fk_dept_emp是关联名，是自己随意命名的。

添加外键，也可以创建完表格之后添加外键。

alter table user add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) 
REFERENCES dept(id);

数据库设计多表之间的实现

一对多

在多的一方建立外键

多对多的关系(建立中间表)

建立3个表格，订单表，商品表，订单商品中间表

CREATE TABLE tb_order(
id int PRIMARY KEY auto_increment,
payment DOUBLE(10,2),
payment_type VARCHAR(20),
status VARCHAR(20)
);
CREATE TABLE tb_goods(
id int PRIMARY KEY auto_increment,
title VARCHAR(100),
price DOUBLE(10,2)
);
CREATE TABLE tb_order_goods(
	id int PRIMARY key auto_increment,
	order_id int,
	goods_id int,
	cout int
	);
alter TABLE tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter TABLE tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

 一对一（在任意一方建立外键）

建立人员信息表tb_user和人员信息详细表tb_user_desc

CREATE TABLE tb_user(
	id int PRIMARY KEY auto_increment,
	name VARCHAR(15) NOT NULL,
	age int NOT NULL,
	sex char(5) NOT NULL,
	desc_id int NOT NULL,
	UNIQUE KEY UNIQUE_desc_id(desc_id)
	);
	CREATE TABLE tb_user_desc(
	id int PRIMARY KEY auto_increment,
	city VARCHAR(35) not null,
	edu VARCHAR(10),
	status VARCHAR(15)
	);
	ALTER TABLE tb_user add CONSTRAINT fk_desc_id FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id);

注意代码中UNIQUE的用法，想让desc_id的键是唯一的，将代码这样写。UNIQUE KEY UNIQUE_desc_id(desc_id)

在一对一建立外键中，外键必须用unique修饰。

多表查询，内连接、外连接以及子查询

创建两张表，员工表和部门表

CREATE TABLE yuangong(
id int PRIMARY KEY auto_increment,
name VARCHAR(35) NOT NULL,
age int NOT NULL,
salary int NOT NULL,
bumen_id int 
);
CREATE TABLE bumen(
id int PRIMARY KEY auto_increment,
dname VARCHAR(35));
ALTER TABLE yuangong add CONSTRAINT fk_yuangong_bumen FOREIGN KEY(bumen_id) REFERENCES bumen(id);

在插入数据时，一定要先添加部门表，再添加员工表数据。员工表中添加了外键，已经关联起来，先添加员工表一定会报错。

INSERT INTO bumen(id,dname)VALUES
(1,'表演部'),
(2,'研究部'),
(3,'财务部'),
(4,'销售部');
INSERT into yuangong(id,name,age,salary,bumen_id)VALUES
(1,'王俊凯',20,8000,1),
(2,'王源',21,7000,2),
(3,'易烊千玺',20,6500,3),
(4,'张三',32,10000,2),
(5,'李四',34,9000,1);

多表查询

select * from yuangong,bumen;

 当我用select*from yuangong,bumen;查询时会有很多无效的信息。只有当bumen_id=id(1)时，才是有用的信息。

 内连接语法分为隐式内连接和显式内连接。内连接是查询两张表的交集。

隐式内连接语法

select * from yuangong,bumen where yuangong.bumen_id=bumen.id;

还可以给表起别名（自己随意起一个简单的名字）

SELECT*FROM yuangong t1,bumen t2 WHERE t1.bumen_id=t2.id;

显式内连接

SELECT*FROM yuangong INNER JOIN bumen ON yuangong.bumen_id=bumen.id;

显式内连接也可以对表起别名，选用不同字段，inner也可以省略。

外连接，分为左外连接和右外连接，其实基本相同，可以相互实现

左外连接

SELECT*FROM yuangong LEFT JOIN bumen on yuangong.bumen_id=bumen.id;

 

右外连接

SELECT*FROM yuangong RIGHT JOIN bumen on yuangong.bumen_id=bumen.id;

 

 A集合为yuangong表，B集合为bumen表，左外连接就是查询A集合的全部以及A∩B，右外连接查询B集合的全部以及A∩B。一般左连接比较常用。

子查询   

子查询其实就是嵌套查询

单行单列（作为条件值使用 =、!=、<、> 等条件进行判断）

查询比王源工资高的员工的所有信息（强调的所有）在这里我引用了隐式内连接法。

SELECT*FROM yuangong,bumen WHERE yuangong.bumen_id=bumen.id 
AND (salary>(SELECT salary from yuangong WHERE name='王源'));

多行单列（作为条件值，使用in关键字进行判断）

查询财务部和表演部所有员工信息

SELECT * FROM yuangong WHERE bumen_id 
IN(SELECT id FROM bumen WHERE(dname='表演部' OR dname='财务部'));

多行多列（作为虚拟表）

查询年龄大于25岁的员工信息和部门信息

SELECT*FROM (SELECT* FROM yuangong WHERE age >25) t1,bumen
 WHERE t1.bumen_id=bumen.id;

(SELECT* FROM yuangong WHERE age >25)这个()内查询的信息整体为虚拟表，这个虚拟表一点要有一个新的名字。这里我起名为t1，后面引用内连接，t1,bumen_id=bumen.id