MySQL基础

最新推荐文章于 2022-10-29 20:00:21 发布

我愿成为你头顶的云

最新推荐文章于 2022-10-29 20:00:21 发布

阅读量195

点赞数

分类专栏：数据库SQL

本文链接：https://blog.csdn.net/qq_42640067/article/details/115033434

版权

数据库SQL 专栏收录该内容

15 篇文章 2 订阅

订阅专栏

1、MySQL介绍

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统(RDBMS)之一。虽然它在某些地方并没有Oracle数据库专业，毕竟Oracle对电脑硬件有要求，而且要收费，收费才能提供更好的服务。MySQL很好的地方是完全开源免费，而且对于一般开发完全够用。

2、MySQL使用

下载安装的过程就完全跳过了，记录一下最基础的使用。

2.1、MySQL规范

良好的编程习惯是按照官方提供的规范进行，这样才显得专业。MySQL规范如下：

关键字与函数名称全部大写。
数据库名称、表名称、字段名称全部小写。
SQL语句必须以英文分号结尾。

## 查看MySQL版本
SELECT version() MySQL版本;
## 查看当前系统时间
SELECT now() 现在时间;
## 查看当前用户
SELECT user() 当前用户;

在这里插入图片描述

2.2、数据库操作

MySQL中没有表空间，所有表的载体是数据库，可以建立多个数据库。

创建数据库：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name 
[DEFAULT] CHARACTER SET [=] charset_name;

说明：{}为必选项，[]为可选项。

查看当前服务器的数据库列表：

SHOW {DATABAES | SCHEMAS} [LIKE ‘pattern’| WHERE expr];

注意：默认安装后是自带4个数据库的。

数据库修改：

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name;

删除数据库：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

测试：

## 查看当前用户下所有数据库
SHOW DATABASES;

在这里插入图片描述

## 创建数据库
CREATE DATABASE
IF
	NOT EXISTS yanchengzhi CHARACTER 
	SET = utf8;

在这里插入图片描述

## 修改数据编码
ALTER DATABASE yanchengzhi CHARACTER 
SET = gbk;
ALTER DATABASE yanchengzhi CHARACTER 
SET = utf8;

在这里插入图片描述

## 删除数据库
DROP DATABASE yanchengzhi;

在这里插入图片描述

2.3、数据类型

数据类型是指列存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。MySQL中的数据类型和Oracle中有一点区别。

2.3.1、整型

TINYINT：有符号值范围-128到127，无符号值范围0到255。
SMALLINT：有符号值范围-32768到32767，无符号值范围0到65535。
MEDIUMINT：有符号值范围-2^23到 2^23 - 1，无符号值范围0到2^24 - 1。
INT：有符号值范围-2^31 到 2^31 - 1，无符号值范围0到2^32 - 1。
BIGINT：有符号值范围-2^63 到 2^63 - 1，无符号值范围0到2^64 - 1。

2.3.2、浮点型

FLOAT[(M,D)]：单精度，M是数字总位数，D是小数点后面的位数。如果M和D被省略，则会根据硬件允许的限制来保存值，单精度浮点精确到大约7位小数。
DOUBLE：双精度。

2.3.3、日期时间型

YEAR：年份。
TIME：时间。
DATE：日期。
DATETIME：日期时间。
TIMESTAMP：时间戳，从1970-01-01开始至今。

2.3.4、字符型

CHAR(M)：M个字节，M属于[0,255]。
VARCHAR(M)：L+1个字节，其中L<=M，M属于[0,65535]。
TINYTEXT：L+1个字节，L<2^8。
TEXT：L+2个字节，L<2^16。
MEDIUMTEXT：L+3个字节，L<2^24。
LONGTEXT：L+4个字节，L<2^32。
ENUM(‘value1’,‘value2’,…)：1或2个字节，取决于枚举值的个数（最多65535个值）。
SET(‘value1’,‘value2’,…) ：1、2、3、4或者8个字节，取决于set成员的数目（最多64个成员）。

2.4、数据表

数据表（表）是数据库最重要的组成部分之一，是其他对象的基础。

创建数据表：

CREATE TABLE [IF NOT EXISTS] table_name (
column_name data_type,...
);

查看数据表：

SHOW TABLES [FROM dn_name] [LIKE 'pattern' | WHERE expr];

查看数据表结构：

SHOW COLUMNS FORM tb_name;

记录插入：

INSERT [INTO] tb_name [(col_name,...)] VALUES (val,...);

记录查找：

SELECT expr,...FROM tb_name;

测试：

## 表创建
CREATE TABLE tb1 ( 
username VARCHAR ( 20 ), 
age TINYINT UNSIGNED, 
salary FLOAT ( 6, 2 ) UNSIGNED 
);

在这里插入图片描述

## 查看当前数据库的所有表
SHOW TABLES FROM yanchengzhi;
## 查看mysql数据库的所有表
SHOW TABLES FROM mysql;
## 查看数据表结构
SHOW COLUMNS FROM	tb1;

在这里插入图片描述

## 插入数据到表中
## 全字段插入
INSERT INTO tb1 VALUES ( 'ycz', 25, 5000.32 );
INSERT INTO tb1 VALUES ( 'hhh', 19, 3021.312 );
##部分字段插入
INSERT INTO tb1 ( username, age ) VALUES ( 'zzz', 22 );

在这里插入图片描述

## 全字段查询
SELECT
	* 
FROM
	tb1;
## 部分字段查询，字段使用别名
SELECT
	username 姓名,
	age 年龄 
FROM
	tb1;

在这里插入图片描述

## 建表
CREATE TABLE tb2 ( 
username VARCHAR ( 20 ) NOT NULL, 
age TINYINT UNSIGNED NULL 
);
## 批量插入记录
INSERT INTO tb2
VALUES
	( 'ycz', 25 ),('ddd',null),('ttt',null);
## 查询	
SELECT
	* 
FROM
	tb2;

在这里插入图片描述

2.5、约束

约束保证数据的完整性和一致性。约束分为表级约束和列级约束。约束的类型有：NOT NULL（非空约束）、PRIMARY KEY(主键约束)、UNIQUE KEY(唯一约束)、DEFAULT(默认约束)、FOREIGN KEY(外键约束)。

表级约束和列级约束：

列级约束：对一个数据列建立的约束，称为列级约束。列级约束既可以在列定义的时候声明，也可以在列定以后声明。
表级约束：对多个数据列建立的约束，称为表级约束。表级约束只能列定以后声明。比如外键约束。

空值和非空值：

NULL：字段值可以为空。
NOT NULL：字段值禁止为空。

自动编号：

auto_increment：自动编号，且必须与主键组合使用，默认情况下，起始值为1，每次增量为1。

主键约束：

PRIMARY KEY：每张数据表中只能存在一个主键，主键保证了记录的唯一性，主键会自动为NOT NULL非空。

外键约束，关键字FOREIGN KEY。外键约束的要求如下：

父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
数据表的存储引擎只能为InnoDB。
外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同。
外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

外键约束的参照操作：

CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。
SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定的NOT NULL。
RESTRICT：拒绝对父表的删除或更新操作。
NO ACTION：标准SQL关键字，在MySQL中与RESTRICT相同。

唯一约束：

UNIQUE KEY：唯一约束可以保证记录的唯一性，唯一性约束的字段可以为NULL空值，每张数据表可以存在多个唯一约束。

默认约束：

DEFAULT：当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

测试：

## 建表
CREATE TABLE tb3 ( 
## UNSIGNED是无符号，auto_increment是主键自动增长，设置主键列
id SMALLINT UNSIGNED auto_increment PRIMARY KEY, 
## 此列允许为空
username VARCHAR ( 30 ) NULL 
);
## 插入记录
INSERT INTO tb3 ( username )
VALUES
	( 'aaa' ),
	( 'bbb' ),
	( 'ccc' ),
	( 'ddd' ),
	( 'eee' );
## 查询	
SELECT
	* 
FROM
	tb3;

在这里插入图片描述

## 建表
CREATE TABLE tb5 ( 
id SMALLINT UNSIGNED auto_increment PRIMARY KEY, 
## 此列设为唯一约束，值不允许重复
username VARCHAR ( 20 ) NOT NULL UNIQUE KEY, 
age TINYINT UNSIGNED NULL 
);
## 插入记录
INSERT INTO tb5 ( username, age )
VALUES
	( 'dadad', 25 ),
	( 'add', 19 ),
	( 'sss', 22 );
## 查询
SELECT
	* 
FROM
	tb5;

在这里插入图片描述

CREATE TABLE tb6 (
id SMALLINT UNSIGNED auto_increment PRIMARY KEY,
username VARCHAR ( 20 ) NOT NULL UNIQUE KEY,
## 此列设置默认约束
sex enum ( '男', '女' ) DEFAULT '男' 
);
INSERT INTO tb6 ( username )
VALUES
	( 'yyy' ),('111');
SELECT
	* 
FROM
	tb6;

在这里插入图片描述

## 建表
CREATE TABLE provinces ( 
id SMALLINT UNSIGNED auto_increment PRIMARY KEY, 
pName VARCHAR ( 20 ) NOT NULL 
);
## 查看建表语句
SHOW CREATE TABLE provinces;
## 建表
CREATE TABLE users (
id SMALLINT UNSIGNED auto_increment PRIMARY KEY,
username VARCHAR ( 10 ) NOT NULL,
pid SMALLINT UNSIGNED,
## 设置外键，需要引用主键列
FOREIGN KEY ( pid ) REFERENCES provinces ( id ) 
);
## 查看索引
SHOW indexes 
FROM
	provinces;
## 查看索引
SHOW indexes 
FROM
	users;

在这里插入图片描述

## 插入记录
INSERT INTO provinces ( pName )
VALUES
	( '湖北' ),
	( '广东' ),
	( '湖南' ),
	( '江西' ),
	( '广西' ),
	( '重庆' ),
	( '四川' );
## 查询
SELECT
	* 
FROM
	provinces;

在这里插入图片描述

## 建表
CREATE TABLE users1 (
id SMALLINT UNSIGNED auto_increment PRIMARY KEY,
username VARCHAR ( 10 ) NOT NULL,
pid SMALLINT UNSIGNED,
## cascade关联是主表删除或更新列时，从表匹配行也会删除或更新
FOREIGN KEY ( pid ) REFERENCES provinces ( id ) ON DELETE CASCADE 
);
## 插入数据
INSERT INTO users1 ( username, pid )
VALUES
	( 'aaa', 1 ),
	( 'sda', 4 ),
	( 'da1da', 6 ),
	( 'vhjsia', 6 ),
	( 'cll', 7 );
## 查询
SELECT
	* 
FROM
	users1;

在这里插入图片描述

## 删除
DELETE 
FROM
	provinces 
WHERE
	pName = '重庆';

再查询user1表：
在这里插入图片描述
外键与删除记录主键关联的记录自动删除掉了，这就是cascade级联删除。

2.6、数据表的修改

添加单列：

ALTER TABLE tb_name ADD [COLUMN] col_name 
column_definition [FIRST | AFTER col_name];

添加多列：

ALTER TABLE tb_name ADD [COLUMN] (col_name column_definition,...);

删除单列：

ALTER TABLE tb_name DROP [COLUMN] col_name;

删除多列：

ALTER TABLE tb_name DROP [COLUMN] col_name1,DROP col_name2;

添加主键约束：

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] 
PRIMARY KEY [index_type](index_col_name,...);

添加唯一约束：

ALTER TABLE tb_name ADD UNIQUE(col_name);

添加外键约束：

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] 
FOREIGN KEY [index_name](index_col_name,...) 
reference_definition;

添加/删除默认约束：

ALTER TABLE tb_name ALTER [COLUMN] col_name 
{SET DEFAULT literal | DROP DEFAULT};

删除主键约束：

ALTER TABLE tb_name DROP PRIMARY KEY;

删除唯一约束：

ALTER TABLE tb_name DROP {INDEX|KEY} index_name;

删除外键约束：

ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol;

修改列定义：

ALTER TABLE tb_name MODIFY [COLUMN] col_name 
column_definition [FIRST | ALTER col_name];

修改列名称：

ALTER TABLE tb_name CHANGE [COLUMN] old_col_name new_col_name 
column_definition [FIRST|ALTER col_name];

数据表更名：

## 第一种
ALTER TABLE tb_name RENAME [TO|AS] new_tb_name;
## 第二种
RENAME TABLE tb_name TO new_tb_name [tb_name2 TO new_tb_name2]...

测试：

## 添加新列，默认加在最后一列后面
ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18;
## 指定加在哪一列后面
ALTER TABLE users1 ADD password VARCHAR ( 20 ) NOT NULL AFTER username;
## 加在第一列
ALTER TABLE users1 ADD truename VARCHAR ( 20 ) NOT NULL FIRST;
## 查看表列
SHOW COLUMNS 
FROM
	users1;

在这里插入图片描述

## 分别删除每一列
ALTER TABLE users1 DROP truename;
ALTER TABLE users1 DROP password;
## 添加多列
ALTER TABLE users1 ADD password VARCHAR ( 20 ) NOT NULL AFTER username,
ADD truename VARCHAR ( 20 ) NOT NULL FIRST;
## 删除多列
ALTER TABLE users1 DROP truename,
DROP password;
## 查看表列
SHOW COLUMNS 
FROM
	users1;

在这里插入图片描述

CREATE TABLE users2 ( 
username VARCHAR ( 20 ) NOT NULL, 
pid SMALLINT UNSIGNED 
);
## 添加新列在第一列
ALTER TABLE users2 ADD id SMALLINT UNSIGNED FIRST;
## 添加主键
ALTER TABLE users2 ADD CONSTRAINT pk_id PRIMARY KEY ( id );
## 添加唯一约束
ALTER TABLE users2 ADD UNIQUE ( username );
## 添加外键约束
ALTER TABLE users2 ADD FOREIGN KEY ( pid ) REFERENCES provinces ( id );
## 添加新列并且添加默认约束
ALTER TABLE users2 ADD age SMALLINT UNSIGNED NOT NULL DEFAULT 18 AFTER username;
## 查看表结构
SHOW COLUMNS 
FROM
	users1;

在这里插入图片描述

## 删除默认约束
ALTER TABLE users2 ALTER age DROP DEFAULT;
## 删除主键约束
## 可以看到执行此句后，username自动变为了主键
ALTER TABLE users2 DROP PRIMARY KEY;
SHOW indexes 
FROM
	users2;
## 所以还需要操作额外的一步，删除索引
ALTER TABLE users2 DROP INDEX username;

## 删除外键约束，注意需要知道外键名
SHOW CREATE TABLE users2;
ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
SHOW COLUMNS 
FROM
	users2;
## 修改列定义
ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
## 修改列名
ALTER TABLE users2 CHANGE pid p_id SMALLINT UNSIGNED NOT NULL;
## 数据表更名
## 第一种方式
ALTER TABLE users2 RENAME usernew;
## 第二种方式
RENAME TABLE usernew TO users2;
SHOW COLUMNS 
FROM
	users2;

在这里插入图片描述

2.7、操作数据表记录

插入记录：

## 第一种
INSERT [INTO] tb_name [(col_name,...)] {VALUES|VALUE} 
({expr|DEFAULT},...),(...),...
## 第二种
INSERT [INTO] tb_name SET col_name={expr|DEFAULT},...
## 第三种
INSERT [INTO] tb_name [(col_name),...] SELECT...

注意：第一种可一次插入多条记录，而第二种只能一次插入一条记录，第三种是使用子查询（SubQuery）。

更新记录：

UPDATE [LOW_PRIORITY] [IGNORE] table_references 
SET col_name={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition];

删除记录（：

DELETE FROM tb_name [WHERE where_condition];

查找记录：

SELECT select_expr [,select_expr,...]
[FROM table_references 
[WHERE where_condition] 
[GROUP BY{col_name|position} [ASC|DESC],...] [HAVING where_condition] 
[ORDER BY {col_name|expr|position}[ASC|DESC],...]
[LIMIT {[offset,]}row_count|row_count OFFSET offset]
];

查询表达式：每一个表达式表示想要的一列，必须至少有一个。多个列之间用英文逗号分开，星号（*）表示所有列。tb_name.*可以表示命名表的所有列。查询表达式可以使用[AS] alias_name为其赋予别名。别名可以用于GROUP BY,ORDER BY或HAVING子句。

条件表达式：对记录进行过滤，如果没有指定WHERE子句，则显示所有记录。在WHERE表达式中，可以使用MySQL支持的函数或运算符对结果集进行过滤。

查询结果分组：

[GROUP BY {col_name|position} [ASC|DESC],...];

条件分组：

[HAVING where_condition];

查询结果排序：

[ORDER BY{col_name|expr|position}[ASC|DESC],...];

限制查询结果返回的数量（分页查询）：

[LIMIT {[offset,]row_count|row_count offset}];

测试：

## 建表
CREATE TABLE users3 (
id SMALLINT UNSIGNED PRIMARY KEY auto_increment,
username VARCHAR ( 20 ) NOT NULL,
PASSWORD VARCHAR ( 32 ) NOT NULL,
age TINYINT UNSIGNED NOT NULL DEFAULT 18,
sex TINYINT UNSIGNED 
);
## 插入多条记录
INSERT INTO users3
VALUES
	( NULL, 'ycz', 'ycz123456', 25, 1 ),
	( NULL, 'wcj', 'wcj123456', 25, 1 ),
	( NULL, 'mzj', 'mzj123456', 24, 1 ),
	( NULL, 'aaa', 'hl123456', 20, 0 ),
	( NULL, 'yyiz', 'yzy123456', 22, 1 );
SELECT
	* 
FROM
	users3;
## 更新记录
UPDATE users3 
SET sex = 0 
WHERE
	id = 5 
	OR id = 7;
SELECT
	* 
FROM
	users3;
## 添加唯一约束
ALTER TABLE users3 ADD UNIQUE ( username );
## 更新记录
UPDATE users3 
SET age = age + 3;
SELECT
	* 
FROM
	users3;

在这里插入图片描述

## 删除记录，名字中带有c的
DELETE 
FROM
	users3 
WHERE
	username LIKE '%c%';
SELECT
	* 
FROM
	users3;

在这里插入图片描述

## 简单查询
SELECT
	id,
	username,
	sex 
FROM
	users3;
## 使用别名
SELECT
	id 用户 ID,
	username 用户姓名,
	sex 性别,
	age 年龄 
FROM
	users3;
## where子句过滤记录
## 查询性别是女并且年龄小于25的所有记录
SELECT
	* 
FROM
	users3 
WHERE
	age < 25 
	AND sex = 0;
## 查询结果分组group by，相当于执行了一次distinct去重复操作
SELECT
	* 
FROM
	users3 
GROUP BY
	sex;
## 查询分组并添加having子句限制
## 统计各个年龄的人数
SELECT
	age,
	count( age ) 
FROM
	users3 
GROUP BY
	age 
HAVING
	age > 20 
ORDER BY
	age DESC;
## order by进行排序，按照年龄降序
SELECT
	* 
FROM
	users3 
ORDER BY
	age DESC;
## limit限制查询条目数
SELECT
	* 
FROM
	users3;
## 限制只显示1条查询结果,前面省略了1个0
SELECT
	* 
FROM
	users3 
	LIMIT 1;
## 从第1条记录开始查询1条
SELECT
	* 
FROM
	users3 
	LIMIT 1,
	1;
## 从第2条记录开始查询1条
SELECT
	* 
FROM
	users3 
	LIMIT 2,
	1;
## 按照id倒序排列
SELECT
	* 
FROM
	users3 
ORDER BY
	id DESC;

在这里插入图片描述

## 建表
CREATE TABLE test ( 
id TINYINT UNSIGNED PRIMARY KEY auto_increment, 
username VARCHAR ( 20 ) 
);
## 查询插入
INSERT INTO test ( username ) SELECT
username 
FROM
	users3 
WHERE
	username LIKE '%z%';
SELECT
	* 
FROM
	test;

在这里插入图片描述

2.8、子查询

子查询（Subquery）是指出现在其他SQL语句内的SELECT子句。如下代码：

SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

其中SELECT * FROM t1称为Outer Query，即外查询，SELECT col2 FROM t2称为SubQuery，即子查询。

子查询指嵌套在查询内部，且必须始终出现在圆括号内。子查询可以包含多个关键字或条件，如DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等。子查询的外层查询可以是SELECT、INSERT、UPDATE、SET、DO。

子查询返回值：

查询结果可以返回标量、一行、一列或子查询。

子查询使用比较运算符：

=、>、<、>=、<=、<>、!=、<=>。语法结构如下：

operand comparision_operator subquery

ANY、SOME、ALL修饰子查询：

语法如下：

operand comparision_operator ANY
operand comparision_operator SOME
operand comparision_operator ALL

返回值规律如下表：
在这里插入图片描述
子查询使用[NOT] IN：

语法：

operand comparision_operator [NOT] IN (subquery) = ...

说明：ANY运算符与IN等效。!=ALL或<>ALL与NOT IN等效。

子查询使用[NOT] EXISTS：

如果子查询返回任何行，EXISTS将返回TRUE;否则返回FALSE。

查询结果写入到表：

## 查询结果插入到已有表
INSERT [INTO] tab_name [(col_name,...)] SELECT ...
## 建表同时将查询结果写入到表
CREATE TABLE [IF NOT EXIXTS] tb_name [(create_definition,...)] select_statement;

多表更新：

UPDATE table_references 
SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition];

多表删除：

DELETE tb_name[.*][,tb_name[.*]]... FROM table_references 
[WHERE where_condition];

测试：

## 建表
CREATE TABLE IF NOT EXISTS tdb_goods(
    goods_id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    goods_name  VARCHAR(150) NOT NULL,
    goods_cate  VARCHAR(40)  NOT NULL,
    brand_name  VARCHAR(40)  NOT NULL,
    goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
    is_show     BOOLEAN NOT NULL DEFAULT 1,
    is_saleoff  BOOLEAN NOT NULL DEFAULT 0
 );
 
 ## 添加记录
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','199',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);
 
SELECT
	* 
FROM
	tdb_goods;

在这里插入图片描述
一共23条记录。

## 查看商品记录条目数
SELECT
	count( goods_id ) 商品条目数
FROM
	tdb_goods;
## 查看平均价格
SELECT
	avg( goods_price ) 商品平均价格
FROM
	tdb_goods;
## 查看平均价格，保留两位小数
SELECT
	round( avg( goods_price ), 2 ) 平均价格 
FROM
	tdb_goods;
## 查询所有价格大于平均价格的商品，并且按价格降序排序
SELECT
	goods_id,
	goods_name,
	goods_price 
FROM
	tdb_goods 
WHERE
	goods_price > ( SELECT avg( goods_price ) FROM tdb_goods ) 
ORDER BY
	goods_price DESC;

在这里插入图片描述

# 查询所有超级本
SELECT
	* 
FROM
	tdb_goods 
WHERE
	goods_cate = '超级本';
## ANY用法，>ANY是大于最小值
SELECT
	* 
FROM
	tdb_goods 
WHERE
	goods_price > ANY ( SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本' ) 
ORDER BY
	goods_price DESC;
## in的用法，=any和=some相当于in 
SELECT
	goods_id,
	goods_name,
	goods_price 
FROM
	tdb_goods 
WHERE
	goods_price IN ( SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本' ) 
ORDER BY
	goods_price DESC;
## ALL用法，>ALL是大于最大值
SELECT
	* 
FROM
	tdb_goods 
WHERE
	goods_price > ALL ( SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本' ) 
ORDER BY
	goods_price DESC;
## SOME用法 >SOME是大于最小值
SELECT
	* 
FROM
	tdb_goods 
WHERE
	goods_price > SOME ( SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本' ) 
ORDER BY
	goods_price DESC;

在这里插入图片描述

## 建表
CREATE TABLE tdb_goods_cates ( 
cate_id SMALLINT UNSIGNED PRIMARY KEY auto_increment, 
cate_name VARCHAR ( 40 ) 
);
## 按类别分组，实际上，group by操作已经执行了一次distinct去重操作
SELECT
	goods_cate 
FROM
	tdb_goods 
GROUP BY
	goods_cate;
## 子查询插入记录
INSERT INTO tdb_goods_cates ( cate_name ) SELECT
goods_cate 
FROM
	tdb_goods 
GROUP BY
	goods_cate;
SELECT
	* 
FROM
	tdb_goods_cates;

在这里插入图片描述

## update执行多表更新，要进行连接
UPDATE tdb_goods
INNER JOIN tdb_goods_cates ON goods_cate = cate_name 
SET goods_cate = cate_id;
SELECT
	* 
FROM
	tdb_goods;

在这里插入图片描述
商品分类名称已替换为分类id。

## 按照品牌分组
SELECT
	brand_name 
FROM
	tdb_goods 
GROUP BY
	brand_name;
## 建表
CREATE TABLE tdb_goods_brands ( 
brand_id SMALLINT UNSIGNED PRIMARY KEY auto_increment, 
brand_name VARCHAR ( 40 ) NOT NULL 
); 
## 查询插入记录
INSERT INTO tdb_goods_brands(brand_name) SELECT
brand_name 
FROM
	tdb_goods 
GROUP BY
	brand_name;
SELECT
	* 
FROM
	tdb_goods_brands;

在这里插入图片描述

## update多表更新
UPDATE tdb_goods g
INNER JOIN tdb_goods_brands s ON g.brand_name = s.brand_name 
SET g.brand_name = s.brand_id;
## 根据更新后的表记录修改对应字段的名称和类型
ALTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
SELECT
	* 
FROM
	tdb_goods;

在这里插入图片描述

## 插入3条记录到表tdb_goods_cates
INSERT INTO tdb_goods_cates ( cate_name )
VALUES
	( '路由器' ),
	( '交换机' ),
	( '网卡' );
## 插入3条记录到表tdb_goods_brands
INSERT INTO tdb_goods_brands ( brand_name )
VALUES
	( '海尔' ),
	( '清华同方' ),
	( '神州' );
SELECT
	* 
FROM
	tdb_goods_cates;
SELECT
	* 
FROM
	tdb_goods_brands;
## 插入1条记录到tdb_goods表
INSERT INTO tdb_goods ( goods_name, cate_id, brand_id, goods_price )
VALUES
	( 'LaserJet Pro P1606dn 黑白激光打印机', '12', '4', '1849' );
SELECT
	* 
FROM
	tdb_goods;

在这里插入图片描述

2.9、表连接查询

MySQL在SELECT语句、多表更新、多表删除中支持JOIN操作。

语法：

table1 {[INNER|CROSS] JOIN} | {LEFT|RIGHT}[OUTER] JOIN} table2 
ON conditional_expr;

连接类型：

INNER JOIN：内连接，在MySQL中，JOIN,CROSS JOIN和INNER JOIN是等价的。内连接显示两表的交集。
LEFT [OUTER] JOIN：左外连接，显示左表中的全部记录及右表符合连接条件的记录。
RIGHT [OUTER] JOIN：右外连接，显示右表中的全部记录及左表符合连接条件的记录。

连接条件：

使用ON关键字来设定连接条件，也可以使用WHERE来代替。通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤。

数据表参照：

table_reference tab_name [[AS] alias] table_subquery [AS] alias;

数据表可以使用tb_name AS alias_name或tb_name alias_name赋予别名。table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

关于连接的说明：

A LEFT JOIN B join_condition：数据表B的结果集依赖数据表A。数据表A的结果集根据左连接条件依赖所有数据表（B表除外）。左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将会生成一个所有列为空的额外B行。
关于外连接：如果使用内连接查找的记录在数据表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL时，如果col_name定义为NOT NULL，MySQL将在找到符合连接条件的记录后停止搜索更多的行。

自身连接：

同一个数据表对其自身进行连接。可以当做左表和右表都是同一个表，进行左
右外连接。

测试：

## 内连接查询，连接三表
SELECT
	g.goods_id 商品ID,
	g.goods_name 商品名称,
	c.cate_name 所属类别,
	b.brand_name 品牌,
	g.goods_price 价格 
FROM
	tdb_goods g
	INNER JOIN tdb_goods_cates c ON g.cate_id = c.cate_id
	INNER JOIN tdb_goods_brands b ON g.brand_id = b.brand_id;
## 左外连接查询，三表
SELECT
	g.goods_id 商品ID,
	g.goods_name 商品名称,
	c.cate_name 所属类别,
	b.brand_name 品牌,
	g.goods_price 价格 
FROM
	tdb_goods g
	LEFT JOIN tdb_goods_cates c ON g.cate_id = c.cate_id
	LEFT JOIN tdb_goods_brands b ON g.brand_id = b.brand_id;
## 右外连接查询，三表
SELECT
	g.goods_id 商品ID,
	g.goods_name 商品名称,
	c.cate_name 所属类别,
	b.brand_name 品牌,
	g.goods_price 价格 
FROM
	tdb_goods g
	RIGHT JOIN tdb_goods_cates c ON g.cate_id = c.cate_id
	RIGHT JOIN tdb_goods_brands b ON g.brand_id = b.brand_id;

在这里插入图片描述
内连接只显示交集。

左外连接以左表为主。

右外连接以右表为主。

## 建表
CREATE TABLE tdb_goods_types ( 
type_id SMALLINT UNSIGNED PRIMARY KEY auto_increment, 
type_name VARCHAR ( 20 ) NOT NULL, 
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 
);
## 添加记录
INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);	
SELECT
	* 
FROM
	tdb_goods_types;

在这里插入图片描述

## 自连接查询
## 查找所有分类及其父类
SELECT
	s.type_id 类型id,
	s.type_name 分类名称,
	p.type_name 父分类 
FROM
	tdb_goods_types s
	LEFT JOIN tdb_goods_types p ON s.parent_id = p.type_id;
## 查找所有分类及其子类
SELECT
	p.type_id 类型id,
	p.type_name 父分类名称,
	s.type_name 子分类名称 
FROM
	tdb_goods_types p
	LEFT JOIN tdb_goods_types s ON s.parent_id = p.type_id;

在这里插入图片描述

## 按父分类分组
SELECT
	p.type_id 父类id,
	p.type_name 父分类名称,
	s.type_name 子分类名称 
FROM
	tdb_goods_types p
	LEFT JOIN tdb_goods_types s ON s.parent_id = p.type_id 
GROUP BY
	p.type_name 
ORDER BY
	p.type_id;
## 统计各个父类下子类的数目
SELECT
	p.type_id 父分类id,
	p.type_name 父分类名称,
	count( s.type_name ) 子分类数目
FROM
	tdb_goods_types p
	LEFT JOIN tdb_goods_types s ON s.parent_id = p.type_id 
GROUP BY
	p.type_name 
ORDER BY
	p.type_id;

## 按照商品名称分组
SELECT
	goods_id 商品id,
	goods_name 商品名称
FROM
	tdb_goods 
GROUP BY
	goods_name;
## having子句限制查询名称重复的商品
SELECT
	goods_id 商品id,
	goods_name 商品名称
FROM
	tdb_goods 
GROUP BY
	goods_name 
HAVING
	count( goods_name ) >= 2;
## 删除掉表中重复的商品,删掉ID号较大的那个
DELETE t1 
FROM
	tdb_goods t1
	LEFT JOIN ( SELECT goods_id, goods_name FROM tdb_goods GROUP BY goods_name HAVING count( goods_name ) >= 2 ) t2 ON t1.goods_name = t2.goods_name 
WHERE
	t1.goods_id > t2.goods_id;
SELECT
	* 
FROM
	tdb_goods;

在这里插入图片描述

id为22和23的删除掉了，因为和id为18、19的重复了。

2.10、函数和运算符

字符函数：

CONCAT()：字符连接。
CONCAT_WS()：使用指定的分隔符进行字符连接。
FORMAT()：数字格式化。
LOWER()：转换成小写字母。
UPPER()：转换成大写字母。
LEFT()：获取左侧字符。
RIGHT()：获取右侧字符。
LENGTH()：获取字符串长度。
LTRIM()：删除前导空格。
RTRIM()：删除后续空格。
TRIM() ：删除前导和后续空格。
TRIM(LEADING ‘’ FROM ‘’)：删除前导指定字符。
TRIM(TRAILING ‘’ FROM ‘’)：删除后续指定字符。
TRIM(BOTH ‘’ FROM ‘’)：删除前导或后续指定字符。
SUBSTRING() ：字符串截取，起始位置可以为负，截取长度不能为负值。这里要注意，与Java中略有不同，Java中是从0开始，这里是从1开始。
[NOT] LIKE：模式匹配。
REPLACE()：字符串替换。

注意：%（百分号）代表任意个字符，_（下划线）代表任意一个字符。

测试：

## 字符连接
SELECT
	concat( 'yan', 'chengzhi' );
SELECT
	concat( 'yan', '-', 'cheng', '-', 'zhi' );

CREATE TABLE test2 ( 
firstname VARCHAR ( 20 ) NULL, 
lastname VARCHAR ( 10 ) NULL 
);
INSERT INTO test2
VALUES
	( 'A', 'B' );
INSERT INTO test2
VALUES
	( 'C', 'D' );
INSERT INTO test2
VALUES
	( 'tom%', '123' );
INSERT INTO test2 ( lastname )
VALUES
	( '11' );
SELECT
	* 
FROM
	test2;

在这里插入图片描述

## concat函数连接字符
SELECT
	concat( firstname, lastname ) fullname 
FROM
	test2;
## concat_ws用指定的字符连接字符
SELECT
	concat_ws( '_', 'yan', 'cheng', 'zhi' );
## format函数保留小数，会四舍五入
SELECT
	format( 123.31633, 2 );
SELECT
	format( 123.31633, 3 );
## lower函数转换为小写
SELECT
	lower( 'yanCHENGzhi' );
## upper函数转换为大写
SELECT
	upper( 'yanCHENGzhi' );
## left函数获取左侧指定长度字符
SELECT LEFT
	( 'MySQL', 2 );
## right函数获取右侧指定长度字符
SELECT RIGHT
	( 'MySQL', 3 );
## 获取右侧字符并且转换为小写	
SELECT
	lower( RIGHT ( 'MySQL', 3 ) );
## length函数获取字符串长度
SELECT
	length( ' My  SQL ' );
## ltrim函数删除左导空格	
SELECT
	ltrim( ' My  SQL ' );
select length(ltrim(' My  SQL '));
## trim函数删除右导空格，中间空格会保留
SELECT
	rtrim( ' My  SQL ' );
SELECT
	length( rtrim( ' My  SQL ' ) );
SELECT
	trim( ' My  SQL ' );
SELECT
	length( trim( ' My  SQL ' ) );
## trim leading删除前导指定的任意字符
SELECT
	trim( LEADING '*' FROM '**yan**cheng**zhi**' );
## trim trailing删除后导指定的任意字符
SELECT
	trim( TRAILING '*' FROM '**yan**cheng**zhi**' );
## trim both删除前导后导指定的任意字符
SELECT
	trim( BOTH '*' FROM '**yan**cheng**zhi**' );
## replace方法替换掉指定字符
SELECT REPLACE
	( '**yan**cheng**zhi**', '*', '' );
## substring函数截取字符串，第一个参数是起始位，第二个参数是截取长度
## 只有一个参数时指定的是起始位，截取到字符串末尾
## 起始位为负数时，从末尾开始截取，截取长度不能为非负数
SELECT
	substring( 'MySQL', 1, 2 );
SELECT
	substring( 'MySQL', 3, 3 );
SELECT
	substring( 'MySQL', 2 );
SELECT
	substring( 'MySQL',- 3, 3 );
## like方法模式匹配，只会匹配到第一个满足的
SELECT
	'MySQLMyReids' LIKE 'My%';
## 匹配第一个名字中包含o的所有记录
SELECT
	* 
FROM
	test2 
WHERE
	firstname LIKE '%o%';
## 匹配第一个名字中包含任意字符的所有记录，不含空值
SELECT
	* 
FROM
	test2 
WHERE
	firstname LIKE '%%%';
## 匹配第一个名字中包含字符%的所有记录
SELECT
	* 
FROM
	test2 
WHERE
	firstname LIKE '%l%%' ESCAPE 'l';

在这里插入图片描述

数值运算符和函数：

CEIL()：进一取整，大于等于一个数的最小整数。
DIV()：整数除法。
FLOOR()：舍一取整，小于等于一个数的最大整数。
MOD()：取余数（取模）。
POWER()：幂运算。
ROUND()：四舍五入。
TRUNCATE()：数字截取。

测试：

## 数值运算符和函数
SELECT
	3 * 12;
## 进一取整，即返回大于等于一个参数的最小整数
SELECT
	ceil( 3.312 );
## 舍一取整，即返回小于等于一个参数的最大整数
SELECT
	floor( 3.312 );
SELECT
	5 / 2;
## mod取模
SELECT
	5 MOD 2;
## power指数运算，第一个参数作为底数，第二个参数作为指数
SELECT
	power( 2, 3 );
## round函数保留小数，四舍五入
SELECT
	round( 3.1415926, 3 );
## 如果只有一个参数，则默认为保留0位小数
SELECT
	round( 3.1415926 );
## truncate函数截取小数，不进行四舍五入
SELECT TRUNCATE
	( 3.1415926, 3 );
## 第二个参数为0则是不截取小数
SELECT TRUNCATE
	( 3.1415926, 0 );
## 参数为负表示截取整数部分
SELECT TRUNCATE
	( 13.1415926,- 1 );
## div是整数除法
SELECT
	5 DIV 3;

在这里插入图片描述

比较运算符和函数：

[NOT] BETWEEN…AND…： [不]在范围之内。
[NOT] IN()：[不]在列出值范围内。
IS [NOT] NULL：[不]为空。

测试：

## between and判定在范围内，返回1，否则返回0
SELECT
	14 BETWEEN 1 
	AND 20;
## not between and判定不在范围内，返回1，否则返回0
SELECT
	15 NOT BETWEEN 1 
	AND 20;
## in判定是否包含给定值
SELECT
	10 IN ( 10, 20, 30, 40, 50 );
## 包含返回1，否则返回0
SELECT
	11 IN ( 10, 20, 30, 40, 50 );
## is null判定是否为空
## is not null判定不为空
SELECT NULL IS NULL;
SELECT
	'' IS NOT NULL;
## 查询第一个名字是空的所有记录
SELECT
	* 
FROM
	test2 
WHERE
	firstname IS NULL;
## 查询第一个名字不是空的所有记录
SELECT
	* 
FROM
	test2 
WHERE
	firstname IS NOT NULL;

在这里插入图片描述

日期时间函数：

NOW()：当前日期和时间。
CURDATE()：当前日期。
CURTIME()：当前时间。
DATE_ADD()：日期变化。
DATEDIFF()：日期差值。
DATE_FORMAT()：日期格式化。

测试：

## 查询当前日期时间
SELECT
	now( );
## 查询当前日期
SELECT
	curdate( );
## 查询当前时间
SELECT
	curtime( );
## 指定日期加上多少天
SELECT
	date_add( '2018-10-1', INTERVAL 365 DAY );
## 指定日期减去多少年
SELECT
	date_add( '2018-10-1', INTERVAL - 2 YEAR );
## datediff返回两个日期之间的差值，第一个减去第二个日期
SELECT
	datediff( '2018-10-1', '2018-10-24' );
SELECT
	datediff( '2018-10-24', '2018-10-1' );
## date_format格式化日期
SELECT
	date_format( '2018-10-1', '%Y年%m月%d日' );

在这里插入图片描述

信息函数：

CONNECTION_ID()：连接ID。
DATABASE()：当前数据库。
LAST_INSERT_ID()：最后插入记录的ID号,这里要注意一点，如果同时插入多条记录，则只返回插入的第一条记录的ID，而不是所有的。
USER()：当前用户。
VERSION()：版本信息。

测试：

## connection_id连接ID
SELECT
	connection_id();
## 查询当前数据库
SELECT DATABASE();
desc test2;
## 新增一个字段
ALTER TABLE test2 ADD id SMALLINT UNSIGNED PRIMARY KEY auto_increment FIRST;
SELECT
	* 
FROM
	test2;
## 添加记录
INSERT INTO test2 ( firstname, lastname )
VALUES
	( 'y', 'cz' ),
	( 'g', 'as' );
## 查询最后插入记录的ID值
SELECT
	last_insert_id();
## 查询当前用户
SELECT USER
	( );
## 查询当前MySQL的版本
SELECT
	version();

在这里插入图片描述

聚合函数：

AVG()：平均值。
COUNT()：计数。
MAX()：最大值。
MIN()：最小值。
SUM()：求和。

加密函数：

MD5()：信息摘要算法。
PASSWORD()：密码算法。

测试：

## avg统计平均数
SELECT
	avg( age ) 平均年龄 
FROM
	users3;
## 查询商品平均价格
SELECT
	avg( goods_price ) 平均价格 
FROM
	tdb_goods;
## 查询商品平均价格并保留两位小数
SELECT
	round( avg( goods_price ), 2 ) 平均价格 
FROM
	tdb_goods;
## 查询商品种类数，一般以唯一值主键为基准
SELECT
	count( goods_id ) 商品种类 
FROM
	tdb_goods;
## max查询最大值
SELECT
	max( goods_price ) 最高价格 
FROM
	tdb_goods;
## min查询最大值
SELECT
	min( goods_price ) 最低价格 
FROM
	tdb_goods;
## sum查询总共价格
SELECT
	sum( goods_price ) 总共价格 
FROM
	tdb_goods;
## 信息摘要算法
SELECT
	md5( 'yanchengzhi' );

在这里插入图片描述

2.11、自定义函数

用户自定义函数全称user-defined function,简称为UDF，是一种MySQL扩展的途径，其用法和内置函数相同。

自定义函数的两个必要条件：

参数：参数可选，有参无参都可以。
返回值：返回值是必须的，参数有时可不带，参数与返回值之间没有必然的联系。
函数可以返回任意类型的值，同样可以接收这些类型的参数。

函数创建：

CREATE FUNCTION function_name 
RETURNS {STRING|INTEGER|REAL|DECIMAL} 
routine_body;

关于函数体：

函数体由合法的SQL语句构成。
函数体可以是简单的SELECT或INSERT语句。
函数体如果为复合结构则使用BEGIN…END语句。
复合结构可以包含声明，循环，控制结构。

函数调用：

SELECT function_name(args);

函数删除：

DROP FUNCTION [IF EXISTS] function_name;

测试：

## 设置为信任函数的创建者
SET GLOBAL log_bin_trust_function_creators = TRUE;
## 自定义函数
## 无参函数的创建
CREATE FUNCTION fun_demo1 () 
RETURNS VARCHAR (30) 
RETURN date_format(now(), '%Y年%m月%d日 %H时%i分%s秒');
## 调用函数
SELECT fun_demo1 ();

在这里插入图片描述

## 创建有参函数
CREATE FUNCTION fun_demo2 ( m SMALLINT UNSIGNED, n SMALLINT UNSIGNED ) 
RETURNS FLOAT ( 10, 2 ) UNSIGNED 
RETURN ( m + n ) / 2;
## 调用函数
SELECT fun_demo2 ( 24, 20 );

在这里插入图片描述

## 创建复合结构体的自定义函数
CREATE FUNCTION adduser ( username VARCHAR ( 20 ) ) 
RETURNS INT UNSIGNED 
BEGIN
  INSERT INTO test (username) VALUES (username );
RETURN last_insert_id( );
END;
SELECT
	* 
FROM
	test;
## 调用定义的复合函数进行数据插入操作并查询
SELECT
	adduser ( 'yyss' );
SELECT
	adduser ( 'zdhjka' );
SELECT
	adduser ( 'dashui1' );
## 删除函数
DROP FUNCTION
IF EXISTS fun_demo2;

在这里插入图片描述

2.12、存储过程

MySQL的执行流程，由用户输入SQL命令，然后MySQL引擎分析SQL命令是否正确，如果正确则编译成MySQL引擎可识别的命令，然后执行，最后将执行的结果返回给客户端。如果可以省略掉中间的语法分析和编译过程，可提高MySQL的效率，而要达到这种效果，要用到存储过程。

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。

优点：

增强SQL语句的功能和灵活性。
实现较快的执行速度。
减少了网络流量。

存储过程的创建：

CREATE [DEFINER={user|CURRENT_USER}] PROCEDURE sp_name 
(proc_parameter[,...]]) [characteristic ...] routine_body;

参数：

IN：表示该参数的值必须在调用存储过程时指定。
OUT：表示该参数的值可以被存储过程改变，并且可以返回。
INOUT：表示该参数在调用时指定，并且可以被改变和返回。

特性：

COMMENT 'string' | {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA} | 
SQL SECURITY {DEFINER|INVOKER};

说明：

COMMENT：注释。
CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句。
NO SQL：不包含SQL语句。
READS SQL DATA：包含读数据的语句。
MODIFIES SQL DATA：包含写数据的语句。
SQL SECURITY {DEFINER|INVOKER}：指明谁有权限来执行。

过程体：

过程体由合法的SQL语句构成，过程体可以是任意SQL语句，过程体如果为复合结构则使用BEGIN…END语句。复合结构可以包含声明，循环，控制结构。

调用存储过程

## 有参数
CALL sp_name([parameter[,..]]);
## 无参数
CALL sp_name[()];

注意：可以使用DELIMITER命令可以修改SQL的定界符。

修改存储过程：

ALTER PROCEDURE sp_name [characteristic ...] 
COMMENT 'string'| {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA} | 
SQL SECURITY 
{DEFINER|INVOKER};

注意：修改存储过程只能修改一些简单特性，并不能修改存储体，要想修改存储体，只能把存储过程先通过DROP PROCEDURE语句删除掉，再重新创建，创建的时候修改，相对来说较麻烦，所以书写存储体的时候要注意。

删除存储过程：

DROP PROCEDURE [IF EXISTS] sp_name;

存储过程和自定义函数的区别：

存储过程实现的功能要复杂一些，而函数的针对性更强。
存储过程可以返回多个值，函数只能有一个返回值。
存储过程一般独立的来执行，而函数可以作为其他SQL语句的组成部分来出现。

注意：创建存储过程或者定义函数时需要通过DELIMITER来修改定界符，因为SQL语句中的定界符和函数中的终止语句分隔符重叠了，都是分号，所以要先修改SQL语句中的定界符，否则会起冲突。然后如果函数体或者过程体中有多个语句时，需要包含在BEGIN...END语句块中。存储过程的调用使用CALL命令。

测试：

## 创建不带参数的存储过程
CREATE PROCEDURE pro_demo1()
SELECT
	version();
## 调用存储过程
CALL pro_demo1();

在这里插入图片描述

## 修改界定符
DELIMITER //;
SELECT
	* 
FROM
	test;
## 创建带IN类型参数的存储过程
CREATE PROCEDURE remove_user_by_id ( IN p_id INT UNSIGNED ) 
BEGIN
 DELETE FROM test WHERE id = p_id;
END;
## 调用存储过程
CALL remove_user_by_id ( 25 );
CALL remove_user_by_id ( 26 );
SELECT
	* 
FROM
	test;

在这里插入图片描述

id为25、26的记录成功删除掉了。

## 创建带IN和OUT的存储过程
CREATE PROCEDURE remove_user_and_return_nums ( 
IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED ) 
BEGIN
 DELETE FROM test WHERE	id = p_id;
 SELECT count( id ) FROM test INTO userNums;
END;
## 查询执行存储过程前表的记录条目
SELECT
	count( id ) 当前用户数
FROM
	test;
## 执行一次存储过程
## @nums参数用来接收输出结果
CALL remove_user_and_return_nums ( 14, @nums );
## 查询第二个参数值，及剩余记录条目
SELECT
	@nums;
SELECT
	row_count( );

在这里插入图片描述

## 清除test表记录
DELETE 
FROM
	test;
## 添加记录
INSERT INTO test ( username )
VALUES
	( 'AAA' ),
	( 'BBB' ),
	( 'CCC' ),
	( 'ycz' );
SELECT
	row_count( );
## 更新表记录
UPDATE test 
SET username = concat( username, '--lalala' ) 
WHERE
	id < 34;
SELECT
	* 
FROM
	test;

在这里插入图片描述

## 创建存储过程
CREATE PROCEDURE remove_user_by_age_and_return_infos ( 
IN p_age SMALLINT UNSIGNED, OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED ) 
BEGIN
 DELETE FROM users1 WHERE	age = p_age;
 SELECT	row_count( ) INTO deleteUsers;
 SELECT count( id ) FROM users1 INTO userCounts;
END;
## 添加记录
INSERT INTO users1 ( username, PASSWORD, pid, age )
VALUES
	( 'aaaa', '123456', 3, 31 ),
	( 'bbbb', '123456', 2, 23 );
SELECT
	* 
FROM
	users1;	
SELECT
	count( id ) 
FROM
	users1
WHERE
	age = 23;
## 调用存储过程 
CALL remove_user_by_age_and_return_infos ( 23, @a, @b );
SELECT
	@a,
	@b;
SELECT
	* 
FROM
	users1;

在这里插入图片描述

2.13、存储引擎

MySQL可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

## 查看表建立过程
SHOW CREATE TABLE users3;

在这里插入图片描述
这里默认使用的引擎是InnoDB。

MySQL支持的存储引擎：

MyISAM。
InnoDB。
Memory。
CSV。
Archive。

并发控制：

当多个连接对记录进行修改时保证数据的一致性和完整性。

锁：

共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
排他锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。

锁颗粒：

表锁：是一种开销最小的锁策略。
行锁：是一种开销最大的锁策略。

## 创建表的同时指定存储引擎
CREATE TABLE tp1 ( 
s1 VARCHAR ( 30 ) 
) ENGINE = myisam;
SHOW CREATE TABLE tp1;

在这里插入图片描述
现在的引擎是MyISAM。

存储引擎的修改：

## 通过修改MySQL配置文件实现
default-storage-engine=engine
## 通过创建数据表命令实现
CREATE TABLE tab_name(
...
..
)ENGINE=engine;
## 通过修改数据表命令实现
ALTER TABLE tab_name ENGINE[=]engine_name;

测试：

## 修改表的存储引擎
ALTER TABLE tp1 ENGINE = INNODB;
SHOW CREATE TABLE tp1;

在这里插入图片描述
现在的引擎是InnoDB。

各存储引擎的特点：

存储限制：MyISAM存储引擎存储限制为256TB，InnoDB为64TB，Archive没有限制。
事务安全：只有InnoDB引擎支持事务安全。
索引支持：只有Archive和CSV不支持索引。
锁颗粒：MyISAM和Memory是表锁，InnoDB和Archive是行锁。
数据压缩：只有MyISAM和Archive支持数据压缩。
外键支持：只有InnoDB引擎支持外键。

注意：用的较多的存储引擎是MyISAM和InnoDB引擎，MyISAM适用于事务处理不多的情况，而InnoDB适用于事务处理较多，需要有外键支持的情况。

2.14、事务

事务用于保证数据库的完整性。
一个简单例子：用户A向用户B转账200元，实现步骤如下：

从用户A的账户上减掉200元（账户余额不小于200元，否则不能进行转账操作）
在用户B的账户上增加200元。

事务的4大特性：

原子性
一致性
隔离性
持久性

2.15、MySQL数据库管理工具

PHPMyAdmin
Navicat
MySQLWorkbench

Navicat用的比较多，但是市面上的Navicat是收费的，可以用破解版的。
在这里插入图片描述
界面非常友好，推荐使用，大部分操作不用写SQL语句，十分方便。

我愿成为你头顶的云

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础

MySQL基础
复制链接

扫一扫

专栏目录