MySQL_note

MySQL基础

SQL语句可以单行或多行书写，以分号结尾

MySQL数据库SQL语句不区分大小写，关键字建议大写

注释：单行-- 注释内容（空格） 或 #注释内容（MySQL特有）

​ 多行/*注释内容*/

DDL(Data Definition Language)

操作数据库

---

1.查询

SHOW databases;

2.创建

• 创建数据库

CREATE database 数据库名称;

• 创建数据库（判断，如果不存在则创建）

CREATE database IF NOT EXISTS 数据库名称;

3.删除

• 删除数据库

DROP database 数据库名称;

• 删除数据库（判断，如果存在则删除）

DROP database IF EXISTS 数据库名称;

4.使用数据库

• 查看当前使用的数据库

SELECT database();

• 使用数据库

USE 数据库名称;

操作表

---

1.查询表

• 查询当前数据库下所有表名称

SHOW tables;

• 查询表结构

DESC 表名称;

2.创建表

CREATE 表名 (
字段名1 数据类型1,
字段名2 数据类型2,
···
字段名n 数据类型n
);	#最后一行末尾不加逗号

数据类型

数据类型	大小	描述
TINYINT	1 byte	小整数值
SMALLINT	2 bytes	大整数值
MEDIUMINT	3 bytes	大整数值
INT或INTEGER	4 bytes	大整数值
BIGINT	8 bytes	极大整数值
FLOAT	4 bytes	单精度浮点数值
DOUBLE	8 bytes	双精度浮点数值
DECIMAL		小数值
DATE	3	日期值
TIME	3	时间值或持续时间
YEAR	1	年份值
DATETIME	8	混合日期和时间值
TIMESTAMP	4	混合日期和时间值，时间戳
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

age int
score double(总长度,小数点后保留位数)
birthday date
name char(大小)		存储性能高，浪费空间
name varchar(大小)	存储性能低，节约空间

#练习
create table student(

id int,

name varchar(10),

gender char(1),

birthday date,

score double(5,2),

email varchar(64),

tel varchar(15),

status tinyint

);

3.删除表

• 删除表

DROP table 表名;

• 删除表时判断·表是否存在

DROP table IF EXISTS 表名;

4.修改表

• 修改表名

ALTER table 表名 RENAME TO 新表名;

• 添加一列

ALTER table 表名 ADD 列名 数据类型;

• 修改数据类型

ALTER table 表名 MODIFY 列名 新数据类型;

• 修改列名和数据类型

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

• 删除列

ALTER table 表名 DROP 列名;

DML(Data Manipulation Language)

1.添加数据

• 给指定列添加数据

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,...)...;

2.修改数据

• 修改表数据

UPDATE 表名 SET 列名1=值1,列名2=值2,...[where 条件];

3.删除数据

DELETE FROM 表名 [WHERE 条件];

修改和删除如果不加条件则是对表全部数据做处理！

DQL(Data Query Language)

基础查询

1.查询多个字段

SELECT 字段(列名)列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据

2.去除重复记录

SELECT DISTINCT 字段(列名)列表 FROM 表名;

3.起别名

AS 可省略，一个空格代替

条件查询

1.语法

SELECT 字段(列名)列表 FROM 表名 WHERE 条件列表;

2.条件

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN…AND…	在某个范围内（含边界）
IN(…)	多选一
LIKE 占位符	模糊查询_单个%多个任意字符
IS NULL	是NULL
IS NOT NULL	不是NULL
AND 或 &&	并且
OR 或 ||	或者
NOT 或 !	非，不是

排序查询

1.语法

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

• 排序方式：
  • ASC：升序（default）
  • DESC：降序

多个排序条件下，前一排序结果相同，再进行下一排序

分组查询

聚合函数

• 概念：将一列数据作为一个整体，进行纵向计算。
• 分类：
  • count(列名)，统计数量（选取非null列）
  • max(列名)，最大值
  • min(列名)，最小值
  • sum(列名)，求和
  • avg(列名)，平均值
• 语法：

SELECT 聚合函数名(列名) FROM 表;

null值不参与聚合函数的运算

1.语法

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

• where和having区别：
  • 执行时机不一样，分别为分组前后
  • 可判断条件不同：where不能对聚合函数判断
  • where > 聚合函数 > having

分页查询

1.语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目;

起始索引 = （当前页码-1）* 每页显示条目

MySQL高级

约束

从数据库层面在添加数据的时候进行限制

分类

约束名称	描述	关键字
非空约束	保证列中所有的数据不能有null值	NOT NULL
唯一约束	保证列中所有数据各不相同	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
检查约束	保证列中的值满足某一条件	CHECK
默认约束	保存数据时，未指定值则采用默认值	DEFAULT
外键约束	两个表的数据建立链接，保证数据的一致完整性	FOREIGN KEY

Mysql不支持检查约束

• 语法

  • 添加约束

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

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

  • 删除约束

  ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

• 使用实例

-- 员工表
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
);

自增长： auto_increment 当列是数字类型并且唯一约束

外键约束

• 添加外键约束

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

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

• 删除外键约束

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

多表查询

1.连接查询

• 内连接

#隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;
#显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

A、B集合交集

• 外连接

#左外连接：A集合+交集部分
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
#右外连接：交集部分+B集合
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

• 子查询

  • 单行单列：作为条件值，使用=、!=、>、<等条件判断

  SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

  • 多行单列：作为条件值，使用in等关键字进行条件判断

  SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

  • 多行多列：作为虚拟表

  SELECT 字段列表 FROM (z) WHERE 条件;

事务

• 一组数据库操作命令，同时成功或失败

#开启事务
START TRANSACTION;或BEGIN;
#回滚事务
ROLLBACK;
#提交事务
COMMIT;

• Mysql默认自动提交事务

#查看事务默认提交方式
SELECT @@autocommit;
-- 1->自动提交，2->手动提交
#修改提交方式
set @@autocommit = 0;