常见概念

启动MySQL服务：net start mysql
停止MySQL服务：net stop mysql
登录MySQL：mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -P端口号(默认3306)
SQL 语句可以单行或多行书写，以;结尾
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
单行注释:--（后面加一个空格然后再加注释内容）注释内容或#注释内容(MySQL 特有)
多行注释: /* */

DDL（数据定义语言）

操作数据库

查询所有的数据库：SHOW DATABASES
创建数据库：①CREATE DATABASE 数据库名称 ②CREATE DATABASE IF NOT EXISTS 数据库名称 推荐第二种
删除数据库：①DROP DATABASE 数据库名称 ②DROP DATABASE IF EXISTS 数据库名称 推荐第二种
使用数据库：USE 数据库名称
查看当前使用的数据库：SELECT DATABASE()

操作表

查询当前数据库下所有表名称：SHOW TABLES
查询表结构：DESC 表名称
创建表：CREATE TABLE 表名 ( 字段名1 数据类型1, 字段名2 数据类型2, … 字段名n 数据类型n ); 举例： create table users ( id int, username varchar(20), password varchar(32) );

注意：最后一行末尾，不能加逗号

删除表：①DROP TABLE 表名②DROP TABLE IF EXISTS 表名推荐第二种
修改表名：ALTER TABLE 表名 RENAME TO 新的表名
添加一列：ALTER TABLE 表名 ADD 列名数据类型
修改某一列的数据类型：ALTER TABLE 表名 MODIFY 列名新数据类型
修改某一列的列名和数据类型：ALTER TABLE 表名 CHANGE 列名新列名新数据类型
删除列：ALTER TABLE 表名 DROP 列名

DML（数据操纵语言）

增加数据

给指定列添加数据：INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…)
给全部列添加数据：INSERT INTO 表名 VALUES(值1,值2,…)
批量添加数据：INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)… 或者INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…

删除数据

DELETE FROM 表名 [WHERE 条件]

修改数据

UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件]

修改语句中如果不加条件，则将所有数据都修改！

像上面的语句中的中括号，表示在写sql语句中可以省略这部分

DQL（数据查询语言）

完整查询语法

SELECT 
    字段列表
FROM 
    表名列表 
WHERE 
    条件列表
GROUP BY
    分组字段
HAVING
    分组后条件
ORDER BY
    排序字段
LIMIT
    分页限定

基础查询

查询字段
SELECT 字段1,字段2... FROM 表名
SELECT * FROM 表名 #查询所有数据
去除重复记录
SELECT DISTINCT 字段1,字段2... FROM 表名
起别名
AS AS 也可以省略
举例：

select name,math as 数学成绩,english as 英文成绩 
from stu

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表

符号	功能
>	大于
<	小于
<=	小于等于
>=	大于等于
=	等于
<>或!=	不等于
between…and…	在某个范围之内（闭区间）
in（…）	多选一
like占位符	模糊查询 _ 代表单个任意字符 % 代表多个任意字符
is null	是空
is not null	非空
and 或 &&	与
or 或 \|\|	或
not 或 !	非

排序查询

SELECT 字段列表 
FROM 表名 
ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

上述语句中的排序方式有两种，分别是：

ASC ：升序排列（默认值）
DESC ：降序排列
注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序
举例：

select * from stu order by math desc , english asc ;

聚合函数

将一列数据作为一个整体，进行纵向计算

函数名	功能
count(列名)	统计数量（一般选用不为null的列）
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

用法：SELECT 聚合函数名(列名) FROM 表
注意：null 值不参与所有聚合函数运算

举例：

查询数学成绩的平均分
select avg(math) 
from stu

分组查询

语法：

SELECT 字段列表 
FROM 表名 
[WHERE 分组前条件限定] 
GROUP BY 分组字段名 
[HAVING 分组后条件过滤]

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

举例：查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的才展示

SELECT sex,AVG(math),COUNT(*)
FROM stu
WHERE math > 70
GROUP BY sex
HAVING COUNT(*) > 2

where 和 having 区别：

执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

分页查询

语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数（每页的条目数）

注意：上述语句中的起始索引是从0开始
可以推导得到：起始索引 = (当前页码 - 1) * 每页显示的条数

约束

概念：约束是作用于表中列上的规则，用于限制加入表的数据。约束的存在保证了数据库中数据的正确性、有效性和完整性。

非空约束

关键字是 NOT NULL
非空约束用于保证列中所有数据不能有NULL值
添加约束

-- 创建表时添加非空约束
CREATE TABLE 表名(
   列名 数据类型 NOT NULL,
   …
);

-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

删除约束

ALTER TABLE 表名 MODIFY 字段名 数据类型;

唯一约束

关键字是 UNIQUE
唯一约束用于保证列中所有数据各不相同
添加约束

-- 创建表时添加唯一约束
CREATE TABLE 表名(
   列名 数据类型 UNIQUE [AUTO_INCREMENT],
   -- AUTO_INCREMENT: 当不指定值时自动增长
   …
); 
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [约束名称] UNIQUE(列名)
);

-- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

删除约束

ALTER TABLE 表名 DROP INDEX 字段名;

主键约束

关键字是 PRIMARY KEY
主键是一行数据的唯一标识，要求非空且唯一，一张表只能有一个主键
添加约束

若选择了自动增长AUTO_INCREMENT，则添加数据时，在主键位置填null即可，或者插入位置不填主键名称及对应值。

-- 创建表时添加主键约束
CREATE TABLE 表名(
   列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
   …
); 
CREATE TABLE 表名(
   列名 数据类型,
   [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);

-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

检查约束

关键字是 CHECK
保证列中的值满足某一条件。例如：我们可以给age列添加一个范围，最低年龄可以设置为1，最大年龄就可以设置为300，这样的数据才更合理些。

注意：MySQL不支持检查约束。这样是不是就没办法保证年龄在指定的范围内了？从数据库层面不能保证，以后可以在java代码中进行限制，一样也可以实现要求。

默认约束

关键字是 DEFAULT
保存数据时，未指定值则采用默认值
添加约束

-- 创建表时添加默认约束
CREATE TABLE 表名(
   列名 数据类型 DEFAULT 默认值,
   …
);

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

外键约束

关键字是 FOREIGN KEY
外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。
添加约束

-- 创建表时添加外键约束
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
);

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

删除约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

约束练习

要求：根据需求，为表添加合适的约束

CREATE TABLE emp (
	id INT,  -- 员工id，主键且自增长
    ename VARCHAR(50), -- 员工姓名，非空且唯一
    joindate DATE,  -- 入职日期，非空
    salary DOUBLE(7,2),  -- 工资，非空
    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0
);

答案

CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 员工id，主键且自增长
    ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空且唯一
    joindate DATE NOT NULL,  -- 入职日期，非空
    salary DOUBLE(7,2) NOT NULL,  -- 工资，非空
    bonus DOUBLE(7,2) DEFAULT 0  -- 奖金，如果没有奖金默认为0
);

外键约束练习

员工的dept_id与部门的id联系起来

-- 部门表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	addr VARCHAR(20)
);
-- 员工表 
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);

数据库设计

一对一

举例：用户和用户详情
一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能。
实现方式
在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

-- 用户详情表
create table tb_user_desc (
	id int primary key auto_increment,
	city varchar(20),
	edu varchar(10),
	income int,
	status char(2),
	des varchar(100)
);
-- 用户表
create table tb_user (
	id int primary key auto_increment,
	photo varchar(100),
	nickname varchar(50),
	age int,
	gender char(1),
	desc_id int unique,
	-- 添加外键
	CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)	
);

一对多

举例：部门和员工
一个部门对应多个员工，一个员工对应一个部门。
实现方式
在多的一方（员工）建立外键，指向一的一方（部门）的主键

-- 部门表
CREATE TABLE tb_dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)	
);

多对多

举例：商品和订单
一个商品对应多个订单，一个订单包含多个商品
实现方式
建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

-- 订单表
CREATE TABLE tb_order(
	id int primary key auto_increment,
	payment double(10,2),
	payment_type TINYINT,
	status TINYINT
);

-- 商品表
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,
	count 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);

多表查询

内连接

内连接查询：相当于查询AB交集数据

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

外连接

左外连接查询：相当于查询A表所有数据和交集部门数据
右外连接查询：相当于查询B表所有数据和交集部分数据

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

子查询

查询中嵌套查询，称嵌套查询为子查询。
子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断

-- 查询工资高于猪八戒的员工信息（员工表和部门表）
SELECT * 
FROM emp 
WHERE salary > 
(SELECT salary FROM emp WHERE NAME = '猪八戒');

子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断

-- 查询 '财务部' 和 '市场部' 所有的员工信息（员工表和部门表）
SELECT *
FROM emp
WHERE dep_id IN (
	SELECT did
	FROM dept
	WHERE dname='财务部' OR dname='市场部'
)

子查询语句结果是多行多列，子查询语句作为虚拟表

-- 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息

-- 查询入职日期是 '2011-11-11' 之后的员工信息
SELECT * 
FROM emp 
WHERE join_date > '2011-11-11' ;

-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
SELECT * 
FROM (SELECT * FROM emp WHERE join_date > '2011-11-11' ) t1, dept 
WHERE t1.dep_id = dept.did;

事务

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。事务是一个不可分割的工作逻辑单元。
事务的特征

原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation） :多个事务之间，操作的可见性
持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

语法
开启事务：START TRANSACTION 或者 BEGIN
提交事务：commit;
回滚事务：rollback;
说明：
mysql中事务是自动提交的。
也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。
可以通过下面语句查询默认提交方式：
SELECT @@autocommit;
查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式
set @@autocommit = 0;

~~~~~~~~~~~~~~~~完结撒花❀~~~~~~~~~~~~~~~~

Q.E.D.

你好，朋友！

MySQL

常见概念

DDL（数据定义语言）

操作数据库

操作表

DML（数据操纵语言）

增加数据

删除数据

修改数据

DQL（数据查询语言）

完整查询语法

基础查询

条件查询

排序查询

聚合函数

分组查询

分页查询

约束

非空约束

唯一约束

主键约束

检查约束

默认约束

外键约束

约束练习

外键约束练习

数据库设计

一对一

一对多

多对多

多表查询

内连接

外连接

子查询

事务

JDBC

吉他小白的学习之路

修娇花