MySQL_note

本文最后更新于:2 个月前

MySQL基础

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

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

注释:单行-- 注释内容(空格) 或 #注释内容(MySQL特有)

​ 多行/*注释内容*/

DDL(Data Definition Language)

操作数据库


1.查询

1
SHOW databases;

2.创建

  • 创建数据库
1
CREATE database 数据库名称;
  • 创建数据库(判断,如果不存在则创建)
1
CREATE database IF NOT EXISTS 数据库名称;

3.删除

  • 删除数据库
1
DROP database 数据库名称;
  • 删除数据库(判断,如果存在则删除)
1
DROP database IF EXISTS 数据库名称;

4.使用数据库

  • 查看当前使用的数据库
1
SELECT database();
  • 使用数据库
1
USE 数据库名称;

操作表


1.查询表

  • 查询当前数据库下所有表名称
1
SHOW tables;
  • 查询表结构
1
DESC 表名称;

2.创建表

1
2
3
4
5
6
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 极大文本数据
1
2
3
4
5
age int
score double(总长度,小数点后保留位数)
birthday date
name char(大小) 存储性能高,浪费空间
name varchar(大小) 存储性能低,节约空间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#练习
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.删除表

  • 删除表
1
DROP table 表名;
  • 删除表时判断·表是否存在
1
DROP table IF EXISTS 表名;

4.修改表

  • 修改表名
1
ALTER table 表名 RENAME TO 新表名;
  • 添加一列
1
ALTER table 表名 ADD 列名 数据类型;
  • 修改数据类型
1
ALTER table 表名 MODIFY 列名 新数据类型;
  • 修改列名和数据类型
1
ALTER table 表名 change 列名 新列名 新数据类型;
  • 删除列
1
ALTER table 表名 DROP 列名;

DML(Data Manipulation Language)

1.添加数据

  • 给指定列添加数据
1
INSERT INTO 表名(列名1,列名2,...) values(值1,值2,...);
  • 给全部列添加数据
1
INSERT INTO values(值1,值2,...);
  • 批量添加数据
1
2
INSERT INTO 表名(列名1,列名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
INSERT INTO values(值1,值2,...),(值1,值2,...),(值1,值2,...)...;

2.修改数据

  • 修改表数据
1
UPDATE 表名 SET 列名1=值1,列名2=值2,...[where 条件];

3.删除数据

1
DELETE FROM 表名 [WHERE 条件];

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

DQL(Data Query Language)

基础查询

1.查询多个字段

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

2.去除重复记录

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

3.起别名

1
AS 可省略,一个空格代替

条件查询

1.语法

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

2.条件

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

排序查询

1.语法

1
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1[排序方式1],排序字段名2[排序方式2]...;
  • 排序方式:
    • ASC:升序(default)
    • DESC:降序

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

分组查询

聚合函数

  • 概念:将一列数据作为一个整体,进行纵向计算。
  • 分类:
    • count(列名),统计数量(选取非null列)
    • max(列名),最大值
    • min(列名),最小值
    • sum(列名),求和
    • avg(列名),平均值
  • 语法:
1
SELECT 聚合函数名(列名) FROM 表;

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

1.语法

1
SELECT 字段列表 FROM 表名 [WHERE 分组前限定条件] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
  • where和having区别:
    • 执行时机不一样,分别为分组前后
    • 可判断条件不同:where不能对聚合函数判断
    • where > 聚合函数 > having

分页查询

1.语法

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

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

MySQL高级

约束

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

分类

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

Mysql不支持检查约束

  • 语法

    • 添加约束

      1
      2
      3
      4
      5
      -- 创建表时添加默认约束
      CREATE TABLE 表名(
      列名 数据类型 DEFAULT 默认值,

      );
      1
      2
      -- 建完表后添加默认约束
      ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
    • 删除约束
    1
    ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
  • 使用实例

1
2
3
4
5
6
7
8
-- 员工表
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 当列是数字类型并且唯一约束

外键约束

  • 添加外键约束
1
2
3
4
5
6
-- 创建表时添加外键约束
CREATE TABLE 表名(
列名 数据类型,

[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
1
2
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
  • 删除外键约束
1
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

多表查询

1.连接查询

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

A、B集合交集

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

    • 单行单列:作为条件值,使用=、!=、>、<等条件判断
    1
    SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
    • 多行单列:作为条件值,使用in等关键字进行条件判断
    1
    SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
    • 多行多列:作为虚拟表
    1
    SELECT 字段列表 FROM (z) WHERE 条件;

事务

  • 一组数据库操作命令,同时成功或失败
1
2
3
4
5
6
#开启事务
START TRANSACTION;或BEGIN;
#回滚事务
ROLLBACK;
#提交事务
COMMIT;
  • Mysql默认自动提交事务
1
2
3
4
5
#查看事务默认提交方式
SELECT @@autocommit;
-- 1->自动提交,2->手动提交
#修改提交方式
set @@autocommit = 0;

本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!