MySQL学习记录

原创 已于 2023-12-08 16:59:38 修改 · 165 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #学习 #数据库

于 2023-11-04 00:24:50 首次发布

文章目录

前言

安装过程可查询其他文章或Mac安装mysql及设置
数据库学习记录，以创建学校学生数据为例，数据库名为 school

统一规范
表名、字段名使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只有数字
正例：sys_name、sys_email、sys09_name
反例：SysName、sysEmail、sys_09_name

导入数据库脚本

  • 命令行执行 mysql -uroot <test.sql
  • source test.sql

MySQL 连接

连接root服务

mysql -u root -p

在这里插入图片描述

退出连接

exit

在这里插入图片描述

数据库数据类型

1Bytes=八位二进制 即 11111111 = 255
1Bytes=一个字符（一个汉字占两个字节）
1024Bytes称为一个千字节，记为1KB
1024KB称为一个兆字节，记为1MB
1024MB称为一个吉字节，记为1GB

数值类型

Bytes–>B

类型大小范围（有符号SIGNED）范围（无符号UNSIGNED ）用途
TINYINT（tinyint）1 B-128, 1270, 255小整数值
SMALLINT（smallint）2B-32768, 327570, 65535大整数值
MEDIUMINT（mediumint）3B-8388608, 83886070, 16777215大整数值
INT（int）或INTEGER（integer）4B-2147483648, 21474836470, 4294967295大整数值
BIGINT（bigint）8B-9,223,372,036,854,775,808，9 223 372 036 854 775 8070，18 446 744 073 709 551 615极大整数值
FLOAT（float）4B(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度-浮点数值
DOUBLE（double）8B(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度-浮点数值
DECIMAL（decimal）对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

日期和时间类型

类型大小（Bytes）范围格式用途
DATE（date）31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME（time）3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间
YEAR（year）11901/2155YYYY年份值
DATETIME8‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’YYYY-MM-DD hh:mm:ss混合日期和时间值
TIMESTEAMP4‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYY-MM-DD hh:mm:ss混合日期和时间值，时间戳

字符串类型

类型大小(bytes)用途
CHAR（char）0-255定长字符串
VARCHAR（varchar）0-65536变长字符串
TINYBLOB（tinyblob）0-255不超过255个字符的二进制字符串
TINYTEXT（tinytext）0-255短文本字符串
BLOB（blob）0-65535二进制新式额长文本数据
TEXT（text）0-65535长文本数据
MEDIUMBLOB（mediumblob）0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT（mediumtext）0-16 777 215 bytes中等长度文本数据
LONGBLOB（longblob）0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT（longtext）0-4 294 967 295 bytes极大文本数据

数据库操作（新增及简单查询）

a、创建数据库

CREATE DATABASE 数据库名

![在这里插入图片描述](https://img-blog.csdnimg.cn/a4cd063389ff4619825d23e5f3f5740b.png

b、删除数据库

DROP DATABASE 数据库名

在这里插入图片描述

Or 使用mysqladmin -u root -p drop RUNOOB直接删除操作
在这里插入图片描述

c、使用数据库

use 数据库名

在这里插入图片描述

数据类型可参考MySQL 数据类型

创建数据表

通用写法 CREATE TABLE table_name (column_name column_type);

  • 创建数据表
CREATE TABLE grade(
    serial INT AUTO_INCREMENT,
    class TINYINT UNSIGNED NOT NULL,
    number SMALLINT UNSIGNED NOT NULL,
    boy SMALLINT UNSIGNED NOT NULL,
    girl SMALLINT UNSIGNED NOT NULL,
    intake DATE NOT NULL,
    PRIMARY KEY (
        serial,
        class
    )
)
ENGINE=INNODB DEFAULT CHARSET=UTF8;

如果不想字段为NULL, 可以设置字段的属性为 NOT NULL，在操作数据库是如果输入改字段的数据为NULL，就会报错
AUTO_INCREMENT定义列为自增的属性，一般用于主键（设置了此属性需定义为主键，否则会报错），数值会自动加1
PRIMARY KEY 关键字用于定义列为主键，可使用多列来定义主键，列之间以逗号分隔
ENGINE 设置存储引擎，CHARSET 设置编码‘

在这里插入图片描述

  • 查看数据表
    使用show tables;查看当前创建有多少数据表
    在这里插入图片描述
  • 查看表结构
    使用desc grade;ordescribe grade查看grade表结构
    在这里插入图片描述

删除数据表

DROP TABLE GRADE ;

在这里插入图片描述

插入数据

  • 表中插入数据格式如下
INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );
  • 插入1-6年级
INSERT INTO grade
(class, number, boy, girl, intake)
VALUES
(1, 0, 0, 0, '2017-09-01'),
(2, 0, 0, 0, '2016-09-01'),
(3, 0, 0, 0, '2015-09-01'),
(4, 0, 0, 0, '2014-09-01'),
(5, 0, 0, 0, '2013-09-01'),
(6, 0, 0, 0, '2012-09-01');

在这里插入图片描述

查看表数据

  • 通用查询语句如下
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

查询语句中可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

  • 指定查询某些字段select class, number from grade where class=1 limit 2 offset 0;在这里插入图片描述

  • 查询所有字段select * from grade where class=1 limit 2 offset 0;
    在这里插入图片描述

  • 查询所有数据select * form grade
    在这里插入图片描述

数据库查询及数据操作

WHERE子句

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

  • 查询表所有数据
select * from grade;

在这里插入图片描述

  • 根据条件查询数据
select * from grade where class = 1;

在这里插入图片描述

MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

UPDATE 更新

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。

  • 不加指定条件，所有表内数据对应字段均会修改
update grade set number = 10

在这里插入图片描述
多个数据设置，使用逗号隔开
在这里插入图片描述

update grade set boy = 40, girl = 60;
  • 结合update、where更新数据，加指定条件，符合条件对应字段均会修改
update grade set number = 11 where class = 1

在这里插入图片描述

DELETE 语句

drop > truncate >> DELETE

删除语句类型执行效果InnoDBMyISAM备注
deleteDML只删数据，走事务，触发trigger。将所删除数据缓存到rollback segement中，事务commit之后生效标记为已删除（不释放磁盘内存，但插入数据会覆盖这部分空间。使用optimize table table_name会释放）立刻释放磁盘空间（带条件的删除不会）；使用optimize table table_name会释放delete 操作是一行一行执行删除的，并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，生成的大量日志也会占用磁盘空间。
truncateDDL不走事务，立即生效，无法找回立刻释放磁盘空间立刻释放磁盘空间删除整个表
dropDDL不走事务，立即生效，无法找回立刻释放磁盘空间立刻释放磁盘空间删除整个表
DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

delete from grade where class=2;

在这里插入图片描述

LIKE 子句

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在 WHERE 子句中指定任何条件。
你可以在 WHERE 子句中使用LIKE子句。
你可以使用LIKE子句代替等号 =。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用 AND 或者 OR 指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

select * from grade where intake LIKE "%12%";

在这里插入图片描述

select * from grade where intake LIKE "%12%" and number=10;

在这里插入图片描述

UNION 操作符

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
expression1, expression2, … expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选， 检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

  • 新建同学信息表
create table class(
    serial int auto_increment,
    class tinyint unsigned not null,
    name char(20) not null,
    sex char(2) not null,
    age smallint unsigned not null,
    birthday DATE not null,
    primary key (
        serial, class, name
    )
)
engine=InnoDB default charset=utf8;

在这里插入图片描述

  • 插入多条数据
INSERT INTO class
(class, name, sex, age, birthday)
VALUES
(1, "小明", "男", 7, "2012-12-01"),
(1, "小红", "女", 7, "2012-01-01"),
(1, "小江", "男", 7, "2012-03-01");

在这里插入图片描述

  • 查询grade和class表所有数据
select * from grade
union
select * from class;

在这里插入图片描述

  • 查询所有在2012入学
select * from class
union all
select * from grade where intake like "2012%"

在这里插入图片描述

order by排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
你可以添加 WHERE…LIKE 子句来设置条件。

select * from class order by birthday desc;

在这里插入图片描述

如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序

SELECT * 
FROM runoob_tbl
ORDER BY CONVERT(runoob_title using gbk);

GROUP BY 语句

结合上述新建的grade和class表，使用group by查询年级对应人数和不同性别人数等

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

插入一、二、三班数据

INSERT INTO class
(class, name, sex, age, birthday)
VALUES
(1, '张阿三', '男', 7, '2016-09-01'),
(1, '李四', '男', 7, '2016-04-01'),
(1, '阿三', "女", 7, '2016-12-01'),
(2, '张阿三', '男', 7, '2015-09-01'),
(2, '李四', '男', 7, '2015-04-01'),
(2, '阿三', "女", 7, '2015-12-01'),
(3, '张阿三', '男', 7, '2014-09-01'),
(3, '李四', '男', 7, '2014-04-01'),
(3, '阿三', "女", 7, '2014-12-01');

在这里插入图片描述

  • 查询姓名相同的个数
SELECT name, COUNT(*) FROM class GROUP BY name;

在这里插入图片描述

  • 查询对应分组的和
SELECT Age, SUM(Age) FROM class GROUP BY Age;
SELECT Name, SUM(Age) FROM class GROUP BY Name;

在这里插入图片描述

  • 查询相同的组合的指定字段的平均值
SELECT Name, AVG(Age) FROM class GROUP BY Name;

在这里插入图片描述

SELECT Class, AVG(Age) FROM class GROUP BY Class;

在这里插入图片描述

  • 使用 WITH ROLLUP
    WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计
    在这里插入图片描述

  • 可以使用 coalesce
    来设置一个可以取代 NUll 的名称，coalesce 语法：select coalesce(a,b,c);

SELECT coalesce(Name, '总数'), SUM(Age) FROM class GROUP BY Name WITH ROLLUP;

在这里插入图片描述

相对复杂操作

MySQL 连接的使用

  • 可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

  • 查询每个人的入学时间
select a.class, a.name, a.birthday, b.intake from class a inner join grade b on  a.class=b.class;

上述等价于这个查询

select a.class, a.name, a.birthday, b.intake from class a, grade b where a.class=b.class;

在这里插入图片描述

MySQL NULL 值处理

MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:

  • IS NULL: 当列的值是 NULL,此运算符返回 true。
  • IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
  • <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

注意：
select * , columnName1+ifnull(columnName2,0) from tableName;
columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。

select a.class, a.name, a.birthday, b.intake from class a right join grade b on  a.class=b.class is null;

在这里插入图片描述

select * from class where name is not null;

在这里插入图片描述

MySQL 正则表达式

模式描述
^匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。
[...]字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
*匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n}n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m}m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。
  • 查询姓名 小
select * from class where name REGEXP '^小';

在这里插入图片描述

  • 查询‘-12-01’出生的同学
select * from  class where birthday regexp '-12-01$';

在这里插入图片描述

MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

  • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

  • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

  • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

  • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务；

  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

  • ROLLBACK TO identifier 把事务回滚到标记点；

  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

事务操作

  • 新建事务测试表
create table transaction_test (
    id int(5)
)
engine=innodb;

在这里插入图片描述
在这里插入图片描述

MySQL ALTER命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到 MySQL ALTER 命令。
MySQL 的 ALTER 命令用于修改数据库、表和索引等对象的结构。
ALTER 命令允许你添加、修改或删除数据库对象，并且可以用于更改表的列定义、添加约束、创建和删除索引等操作。
ALTER 命令非常强大，可以在数据库结构发生变化时进行灵活的修改和调整。

默认值设置

  • 修改默认值
ALTER TABLE table_name ALTER column_name SET DEFAULT 1000;
  • 删除默认值
ALTER TABLE table_name ALTER column_name DROP DEFAULT

修改表结构

  • 添加新列：
ALTER TABLE table_name
ADD column_name data_type; // ADD name int
// ADD column_name data_type FIRST; // 设置列在首列
// ADD column_name data_type AFTER column_x; // 设置某列后面
  • 修改列定义：
ALTER TABLE table_name
MODIFY column_name new_data_type;
  • 修改列名称：
ALTER TABLE table_name
CHANGE old_column_name new_column_name data_type;
  • 删除列：
ALTER TABLE table_name
DROP column_name;

添加约束

  • 添加主键：
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
  • 添加外键：（被外链的表字段需创建索引，否则会出现错误）
ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES referenced_table(ref_column_name);
  • 添加唯一约束：
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

创建索引

  • 创建普通索引：
ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • 创建唯一索引：
ALTER TABLE table_name
ADD UNIQUE INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • 删除索引：
ALTER TABLE table_name
DROP INDEX index_name;
  • 重命名表：
ALTER TABLE old_table_name
RENAME TO new_table_name;

修改表存储引擎

ALTER TABLE table_name ENGINE = new_storage_engine;

注意：
但在使用 ALTER 命令时要格外小心，因为一些操作可能需要重建表或索引，这可能会影响数据库的性能和运行时间。
在进行重要的结构修改时，建议先备份数据，并在生产环境中谨慎操作。

MySQL 索引

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。

索引分单列索引和组合索引：

  • 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
  • 组合索引，即一个索引包含多个列。

需要注意以下几点：

  • 索引需要占用额外的存储空间。
  • 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。
  • 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

显示索引信息

SHOW INDEX FROM table_name\G

创建索引

CREATE INDEX 创建

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE INDEX: 用于创建普通索引的关键字。
  • index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
  • table_name: 指定要在哪个表上创建索引。
  • (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数- 据类型通常是数值、文本或日期。
  • ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

ALTER 命令创建

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

创建表的时候直接指定

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

删除索引

DROP INDEX 语法
DROP INDEX index_name ON table_name;
  • DROP INDEX: 用于删除索引的关键字。
  • index_name: 指定要删除的索引的名称。
  • ON table_name: 指定要在哪个表上删除索引。
使用 ALTER TABLE 语句删除索引的语法如下：
ALTER TABLE table_name
DROP INDEX index_name;

创建唯一索引

CREATE INDEX 创建

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
  • CREATE UNIQUE INDEX:: 用于创建普通索引的关键字。
  • index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
  • table_name: 指定要在哪个表上创建索引。
  • (column1, column2, …): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数- 据类型通常是数值、文本或日期。
  • ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

ALTER 命令创建

ALTER TABLE table_name 
ADD CONSTRAINT index_name UNIQUE (column);

创建表的时候直接指定

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);
  • CONSTRAINT: 用于添加约束的关键字。

ALTER 删除（参考 ALTER命令部分）

MySQL 临时表

基本同新建表，只是CREATE TABLE替换为了CREATE TEMPORARY TABLE
新建临时表，使用SHOW TABLES;不会显示

MySQL 复制表

  • 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
  • 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
  • 如果你想复制表的内容，你就可以使用 INSERT INTO … SELECT 语句来实现。

查看新建表命令

show create table class;

复制对应命令

CREATE TABLE `copy_class` (
  `serial` int NOT NULL AUTO_INCREMENT,
  `class` tinyint unsigned NOT NULL,
  `name` char(20) NOT NULL,
  `sex` char(2) NOT NULL,
  `age` smallint unsigned NOT NULL,
  `birthday` date NOT NULL,
  PRIMARY KEY (`serial`,`class`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3 ;

拷贝对应表数据

insert into copy_class (class, name, sex, age, birthday)
select class, name, sex, age, birthday from class;

MySQL 元数据

  • 查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
  • 数据库和数据表的信息： 包含了数据库及数据表的结构信息。
  • MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。

获取服务器元数据

命令描述
SELECT VERSION( )服务器版本信息
SELECT DATABASE( )当前数据库名 (或者返回空)
SELECT USER( )当前用户名
SHOW STATUS服务器状态
SHOW VARIABLES服务器配置变量

MySQL 序列使用

使用 AUTO_INCREMENT

  • 可在创建数据库表的时候设置AUTO_INCREMENT从指定值开始
CREATE TABLE `temp` (
  `serial` int NOT NULL AUTO_INCREMENT,
  `class` tinyint unsigned NOT NULL,
  PRIMARY KEY (`serial`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3 ;
  • 也可在创建好表之后再设置
ALTER TABLE t AUTO_INCREMENT = 100;

MySQL 处理重复数据

防止表中出现重复数据

  • MySQL 数据表中设置指定的字段为PRIMARY KEY（主键）或者UNIQUE（唯一）索引来保证数据的唯一性。
  • 如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。
  • INSERT IGNORE INTO 与INSERT INTO 的区别就是INSERT IGNORE INTO会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
  • INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

统计重复数据

使用count关键字统计，并使用repetitions 计算重复数据

select count(*) as repetitions, sex, age
 from class
 group by sex, age
 having repetitions > 1;

在这里插入图片描述

过滤重复数据

  • SELECT 语句中使用DISTINCT关键字来过滤重复数据。
select distinct name, age from class;

在这里插入图片描述

  • 也可以使用 GROUP BY 来读取数据表中不重复的数据
select class from class group by (class);

在这里插入图片描述

删除重复数据

  • 删除数据表中的重复数据，你可以使用以下的SQL语句
CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME TO person_tbl;
  • 在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录
ALTER IGNORE TABLE person_tbl
ADD PRIMARY KEY (last_name, first_name);

总结

失眠时间
关注
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值