数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。

Text 类型：

Number 类型：

* 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

Date 类型：

* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

------创建数据库表等操作------

创建数据库

CREATE DATABASE my_db

创建表

CREATE TABLE table_name

(

Id_P int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

数据类型

DROP

可以轻松地删除索引、表和数据库。

--删除数据库

DROP DATABASE db_name

--删除表

DROP TABLE table_name

--清空表数据

TRUNCATE TABLE table_name

ALTER

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN column_name)。

mysql在nivicat15能删除列，但是修改格式失败

--添加列

ALTER TABLE table_name

ADD column_name datatype

--删除列

ALTER TABLE table_name

DROP COLUMN column_name

--修改列（修改时如果数据类型无法转换会失败）

ALTER TABLE table_name

ALTER COLUMN column_name datatype

--删除索引

ALTER TABLE table_name DROP INDEX index_name

约束

• NOT NULL - 指示某列不能存储 NULL 值。（不为空）
• UNIQUE - 保证某列的每行必须有唯一的值。（唯一）
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。（不为空+唯一）
• FOREIGN KEY- 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。（符合条件）
• DEFAULT - 规定没有给列赋值时的默认值。（默认）

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

PRIMARY KEY与UNIQUE

PRIMARY KEY = UNIQUE + NOT NULL

UNIQUE：每个元素都唯一，可以为空

PRIMARY KEY：复合主键，和唯一就可以

区别

1.作为Primary Key的域/域组不能为null，而Unique Key可以。

2.一个表里只允许一个Primary Key，但可以多个Unique Key。

3.Primary Key是列和唯一，Unique Key每列都唯一。

--Unique

--设置UNIQUE 约束--是一个索引

ALTER TABLE table_name

ADD UNIQUE(id)

--设置多个UNIQUE 约束（多个约束放一起，但每个约束都唯一）

ALTER TABLE table_name

ADD CONSTRAINT idss UNIQUE (id,ids)

--撤销UNIQUE 约束

ALTER TABLE table_name

DROP INDEX id

--Primary Key

--设置单主键约束

ALTER TABLE table_name

ADD PRIMARY KEY (id)

--设置联合约束

ALTER TABLE table_name

ADD CONSTRAINT book_id PRIMARY KEY (id,name)

--撤销主键约束(该写法mysql撤销单主键和联合主键都可以)

ALTER TABLE table_name

DROP PRIMARY KEY

自动设置not null

在mysql设自动递增需要设置主键

FOREIGN KEY

FOREIGN KEY 约束用于预防破坏表之间连接的行为。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

--设置外键

--前提：依赖表需要设置该列为主键会默认起个外键名tbl_book_copy1_ibfk_1

ALTER TABLE table_name

ADD FOREIGN KEY (ids)

REFERENCES 依赖表(ids)

--建议使用下面这种

ALTER TABLE table_name

ADD CONSTRAINT book_ids

FOREIGN KEY (ids)

REFERENCES 依赖表(ids)

--撤销外键

ALTER TABLE table_name

DROP FOREIGN KEY book_ids

CHECK

CHECK 约束用于限制列中的值的范围。

--单个限制成功

ALTER TABLE table_name

ALTER name DROP DEFAULT

--多个约束同时失败

ALTER TABLE table_name

ADD CHECK (ids<45)

ADD CONSTRAINT ck_book CHECK (ids<45 AND type='小说')

这个约束在mysql有点特别，且在navicat上无法查看

但可以采用其他方式实现对数据限制约束

有两种方法：

• 在 MySQL 种约束，如使用ENUM 类型或者触发器。
• 在应用程序里面对数据进行检查再插入。

ENUM 实现限制功能

CREATE TABLE person(

id tinyint(4) NOT NULL AUTO_INCREMENT,

   name varchar(16) NOT NULL,

   sex enum('男','女') NOT NULL

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DEFAULT

DEFAULT 约束用于向列中插入默认值。

--设置默认

ALTER TABLE table_name

ALTER COLUMN name SET DEFAULT'java'

--撤销默认

ALTER TABLE table_name

ALTER name DROP DEFAULT

索引

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

您可以在表中创建索引，以便更加快速高效地查询数据。

用户无法看到索引，它们只能被用来加速搜索/查询。

有索引的表更新时花费时间也更多，因此在常用的列上添加索引

--简单索引---允许使用重复的值

CREATE INDEX index_name

ON table_name (column_name)

--唯一索引

CREATE UNIQUE INDEX index_name

ON table_name (column_name)

--降序索引

CREATE INDEX index_name

ON table_name (column_name DESC)

--多列单索引

CREATE INDEX index_name

ON table_name (column_name_01, column_name_02)

--删除索引

ALTER TABLE table_name

DROP INDEX index_name

AUTO_INCREMENT

Auto-increment 会在新记录插入表中时生成一个唯一的数字。

注意：我们通常希望在每次插入新记录时，自动地创建主键字段的值。

因此该关键字只能用于主键，只能有一个主键

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

Incorrect table definition; there can be only one auto column and it must be defined as a key

--创建表时设置，记得主键设置

CREATE TABLE Persons

(

P_Id int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

PRIMARY KEY (P_Id)

)

--修改下一次插入时的起始值，不影响之前的

ALTER TABLE Persons AUTO_INCREMENT=100

ORDER BY

ORDER BY 语句用于对结果集进行排序。

left join 和 right join 、inner join的区别

• INNER JOIN（内连接）中和，两边都有的显示
• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN（全连接）: 只要其中一个表中存在匹配，就返回行

SELECT * FROM `tb_user`

LEFT JOIN tb_brand

on tb_user.id=tb_brand.id

SELECT * FROM `tb_user`

RIGHT JOIN tb_brand

on tb_user.id=tb_brand.id

SELECT * FROM `tb_user`

INNER JOIN tb_brand

on tb_user.id=tb_brand.id

分页

limit

mybatis-注解实现

mybatis-xml实现

mybatis框架pageHelper插件分页

Like-通配符

SELECT * FROM `tbl_book` WHERE id NOT LIKE '%1'

BETWEEN ... AND

不同数据库操作符的处理方式是有差异的，对两边数据是否包含有不同操作

//以外使用NOT BETWEEN

SELECT * FROM `tbl_book` WHERE id BETWEEN 1 AND 10

SQL Alias（别名）

UNION 和 UNION ALL

合并

前者会去重，后者全合并

注意：UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

VIEW（视图）

什么是视图？

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

注意！！不要用视图来更新，可以改变视图的数据多少，但不要改变具体的数据

视图的作用，只是用来简化嵌套查询，把一些常用的，相对简单的查询结果做成视图，一来查询简便，二来，可以简化嵌套查询。

如果视图中数据是来自于一个表时，修改视图中的数据，表数据会更新。而且修改表中数据时，对应视图也会更新。但是如果视图数据来源于两个表时，修改视图数据时会报错，无法修改。

注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。

--创建视图

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

--删除视图

DROP VIEW view_name

--修改视图

ALTER VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

原因

某些视图是可更新的。也就是说，可以使用 UPDATE、DELETE 或 INSERT 等语句更新基本表的内容。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。

还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：

• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
• DISTINCT 关键字。
• GROUP BY 子句。
• HAVING 子句。
• UNION 或 UNION ALL 运算符。
• 位于选择列表中的子查询。
• FROM 子句中的不可更新视图或包含多个表。
• WHERE 子句中的子查询，引用 FROM 子句中的表。
• ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

Date

MySQL Date 函数

SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
• YEAR - 格式 YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式 YYYY-MM-DD
• DATETIME - 格式: YYYY-MM-DD HH:MM:SS
• SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式: 唯一的数字

注意格式必须一致，如果不一致查询结果会为空

--模板

SELECT * FROM table_name

WHERE column_name='YYYY-MM-DD'

--举例

SELECT * FROM tbl_book_copy1

WHERE birthday='2022-07-31'

NULL

• NULL 值是遗漏的未知数据。
• 注释：无法比较 NULL 和 0；它们是不等价的。
• --------------------------------------------
• ISNULL(expr)
• 如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。
• IFNULL(expr1,expr2)
• 假如expr1不为NULL，则 IFNULL() 的返回值为expr1; 否则其返回值为 expr2。
• NULLIF(expr1,expr2)
• 如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为expr1。这和CASE  WHEN expr1 = expr2 THEN NULL ELSE   expr1 END相同。

--查找NULL

SELECT * FROM table_name

WHERE column_name IS NULL

--不为NULL

SELECT * FROM table_name

WHERE column_name IS NOT NULL

--希望 NULL 值为 0时--IFNULL

SELECT IFNULL(column_name,0) FROM table_name

--可以嵌套使用的

SELECT IFNULL(NULLIF(id,ids),1) FROM tbl_book_copy1

查询表名

查询某个数据库中某个表的所有列名

SELECT COLUMN_NAME FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tb_name';

查询某个数据库中某个表的所有列名,并用逗号连接

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tb_name';

注意：只需要替换db_name(数据库名)和tb_name(表名)

MYSQL函数

SQL函数

SUM()

DATE_FORMAT(date,format)

--查询2020年9月的流水

select

DATE_FORMAT(create_time,'%Y-%m-%d') date,

SUM(pay_price) as pay ,

SUM(refund_price) as refund

from yx_store_order

where DATE_FORMAT(create_time,'%Y') = 2020

AND DATE_FORMAT(create_time,'%m') = 09

GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d')

--查询2020年每个月的流水

select

DATE_FORMAT(create_time,'%Y-%m') date,

SUM(pay_price) as pay ,

SUM(refund_price) as refund

from yx_store_order

where DATE_FORMAT(create_time,'%Y') = 2020

GROUP BY DATE_FORMAT(create_time,'%Y-%m')

AVG()

AVG 函数返回数值列的平均值。NULL 值不包括在计算中。

SELECT AVG(column_name) FROM table_name

--举例--查询订货高于平均的顾客名

SELECT Customer FROM Orders

WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

COUNT()

COUNT() 函数返回匹配指定条件的行数。

--返回行数

SELECT COUNT(column_name) FROM table_name

where 条件

--返回不重复的行数

SELECT COUNT(DISTINCT column_name) FROM table_name

where 条件

FIRST()与LAST()（mysql无）

FIRST() 函数返回指定的字段中第一个记录的值。

LAST() 函数返回指定的字段中最后一个记录的值。

提示：可使用 ORDER BY 语句对记录进行排序。排序后来找第一个或者最后一个

SELECT FIRST(column_name) FROM table_name

SELECT LAST(column_name) FROM table_name

SELECT ... INTO-建表结构及数据

MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT

select into from 要求目标表不存在，因为在插入时会自动创建；insert into select from 要求目标表存在。

1. 复制表结构及其数据：

create table table_name_new as select * from table_name_old

2. 只复制表结构：

create table table_name_new like table_name_old

3. 只复制表数据：

如果两个表结构一样：

insert into table_name_new select * from table_name_old

如果两个表结构不一样：

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old