mysql 修改自增字段起始值不生效_MySql常用命令

最新推荐文章于 2024-04-18 16:05:18 发布

顾茈陌里

最新推荐文章于 2024-04-18 16:05:18 发布

阅读量642

点赞数

文章标签： mysql 修改自增字段起始值不生效

本文链接：https://blog.csdn.net/weixin_30095343/article/details/112289220

版权

本文介绍了MySQL数据库的基本操作，包括启动与停止服务、查看版本及用户信息。深入探讨了数据表的创建、修改与删除，特别是修改数据表自增字段的操作。此外，还涵盖了子查询、连接、运算符和函数、存储过程及存储引擎的相关知识，为MySQL数据库的管理和维护提供了全面的指导。

摘要由CSDN通过智能技术生成

1、数据库操作

MySql：开源关系型数据库，安装配置教程参考：https://zhuanlan.zhihu.com/p/72546009

以下命令在cmd中直接执行即可：

启动：net start mysql
停止：net stop mysql
查看版本：mysql -V
登录：mysql -uroot -p
更改提示符：PROMPT u@h d（u当前用户h服务器名称d当前数据库D完整的日期）

以下命令在MySql服务中执行：

查看当前服务器版本：SELECT VERSION();
显示当前用户：SELECT USER();
显示当前日期：SELECT NOW();
打开数据库：USE DATABASE 名称;
创建数据库：CREATE DATABASE 名称;
删除数据库：DROP DATABASE 名称;
显示数据库信息：SHOW CREATE DATABASE 名称;
查看当前服务器下的数据表列表：SHOW DATABASES;
查看警告信息：SHOW WARNINGS;
修改文字编码：ALTER DATABASE 名称 CHARACTER SET = utf8;
退出：exit;或quit;

MySql语法规范：

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

2、数据表操作

2.1、数据表概述

数据表（或称表）是数据库最重要的组成部分之一，是其他对象的基础。实质就是一张二维表，其中行称为记录，列称为字段。

MySql中基本数据类型有：整型、浮点型、日期时间型、字符型

FLOAT[(M,D)]m是数字总位数，D是小数点后面的位数

时间日期型

char（M）如果字符长度小于M ,将会自动补齐剩下字符；VARCHAAR(M) 不会补齐

2.2、修改数据表

(1)针对数据表的操作

创建数据表：CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,....);
查看数据表列表：SHOW TABLES [FROM db_name] [LIKE 'patten' | WHERE expr] ;
查看数据表结构：SHOW COLUMNS FROM tbl_name;
修改数据表名字：ALTER TABLE table_name RENAME [TO|AS] new_tbl_name；RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]...;

(2)针对字段的操作

添加单列：ALTER TABLE tbl_name ADD [COLUMN] col_name column_definiton [FIRST|AFTER col_name];
添加多列：ALTER TABLE table_name ADD [COLUMN] （column_name column_definition,...) ;
删除单列：ALTER TABLE table_name DROP [COLUMN] column_name;
修改列定义（名称没问题，数据类型或者列的位置有问题）：ALTER TABLE table_name MODIFY [COLUMN] column_name column_definition [FIRST | AFTER column_name];
修改列名称：ALTER TABLE table_name CHANGE [COLUMN] old_column_name new_column_name column_definition [FIRST | AFTER column_name];（CHANGE的功能大于MODIFY，可以在修改名称的同时，对类型、位置也做出修改）

(3)针对约束的操作

约束的目的是保证数据的完整性和一致性，约束的分类如下图所示，需要注意的是NOT NULL、DEFAULT 约束不存在表级约束。

首先，介绍下各约束的定义。

空值与非空：NULL和 NOT NULL ，NULL字段可以为空，NOT NULL 字段禁止为空。
主键约束：PRIMARY KEY，用于唯一的标识表中的某一条记录，为NOT NULL。
唯一约束：UNIQUE KEY，用来保证数据唯一性，可以有多个唯一约束，字段可以为空值。
默认约束：DEFAULT，当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。
默认约束：DEFAULT，当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。
自动编号：AUTO_INCREMENT，必须和主键组合使用，起始值为1，增量为1。
外键约束：FOREIGN KEY，实现一对一或一对多关系。有3个条件，1）父表和子表用相同存储引擎，且只能为innoDB；2）外键列和参照列要有相似数据类型，数字长度和是否有符号位必须相同，字符长度可以不同；3）外键列和参照列必须创建索引。如果外键列不存在索引，将自动创建。
外键约束定义：FOREIGN KEY (外键列名称) REFERENCES 父表名称 (参照列名称) （外键约束的参数）;
外键约束的参数：CASCADE:父表删除或者更新时子表也要自动删除更新行；SETNULL:父表删除更新行，要设置子表外键列为null，子表不能指定为NOT NULL；RESTRICT/NO ACTION:拒绝对父表删除更新操作

接下来，列举常用的约束操作语句：

以网格形式查看是否创建索引：SHOW INDEXES FROM table_nameG;
添加主键约束：ALTER TABLE table_name ADD [CONSTRAINT [symbol(给主键取的名称)]] PRIMARY KEY [index_type] (index_col_name,...);
添加唯一约束：ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...);
添加外键约束：ALTER TABLE table_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name) REFERENCES 参照表名 (参照列);
添加/删除默认约束：ALTER TABLE 表名 ALTER [COLUMN] 列名 {SET DEFAULT 设置默认值 | DROP DEFAULT删除默认值};
删除主键约束：ALTER TABLE table_name DROP PRIMARY KEY;
删除唯一约束：可以先查看唯一约束名字：show indexs from table_name，再删除：ALTER TABLE table_name DROP INDEX|KE index_name;
删除外键约束：可以先查看外键约束名字：show create table table_name，再删除：ALTER TABLE table_name DROP FOREING KEY fk_name;

(4)针对记录的操作

1)增INSERT：普通INSERT、INSERT SET、INSERT SELECT

普通INSERT：INSERT [INTO] tbl_name [(col_name,…)] {VALUES|VALUE} ({expr | DEFAULT},…),(…),…
INSERT SET：INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},..
INSERT SELECT：INSERT [INTO] tbl_name [(col_name,….)] SELECT col_name FROM tb2_name 说明:将查询结果插入到指定数据表的列

2)删DELETE：存在两种语法结构，单表删除、多表删除。

单表删除：DELETE FROM tbl_name [WHERE where_condition]

3)更UPDATE：单表更新和多表更新

单表更新：UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr|DEFAULT}[,col_name2={expr|DEFAULT}]…[WHERE where_condition]

4)查SELECT：SELECT expr,... FROM tbl_name;

常规：如SELECT VERSION();/SELECT NOW();/SELECT 3+5;
查询结果分组：[GROUP BY {col_name | position} [ASC | DESC], …]；（ASC为升序，DESC为降序）
分组条件：[ HAVING where_condition ]
查询结果排序：[ORDER BY { col_name | expr | position }[ ASC | DESC ], … ]
限制查询结果返回的数量：[ LIMIT { [ offset, ] row_count OFFSET offset } ]

3、子查询和连接

(1)子查询

子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。

例如：

SELECT * FROM t1 WHERE col1= ( SELECT col2 FROM t2 );
其中SELECT * FROM t1，称为Outer Query/Outer Statement（外查询）
SELECT col2 FROM t2, 称为SubQuery（子查询）

使用子查询时注意：

子查询嵌套在查询内部，并且子查询必须出现在圆括号内。
子查询可以包含多个关键字或条件，如DISTINCT、GROUT BY、ORDER BY、LIMIT,函数等。
子查询的外层查询可以是：SELECT,INSERT，UPDATE，SET或DO。
外查询这里指所有SQL命令的统称，因为SQL称为结构化查询语言。
子查询返回值：可以是返回标量、一行、一列、或者子查询，并且可以通过该返回值进行增、删、改、查。

使用子查询的环境：

比较运算符引发的子查询，=、>、<、>=、<=、<>、!=、<=>
in或not in引发的子查询，=ANY运算符与IN等效，!=ALL或<>ALL运算符与NOT IN等效
exist或not exist引发的子查询，如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE，使用较少

（2）连接

语法结构：基本类型为 A表链接类型 B表两张表的链接条

table_reference

{[INNER|CROSS] JOIN {LEFT|RIGHT} [OUTER] JOIN}

table_reference

ON conditional_expr

table_reference(数据表参照)说明：tbl_name [[AS] alias] | table_sabquery [AS] alias，数据表可以使用tbl_name AS alias_name或tbl_name alias_name 赋予别名；table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

连接的类型

内连接INNER JOIN：连接表示两个表的交集，使用ON关键字来设定连接条件，也可以使用WHERE来代替
外连接OUTER JOIN：左外连接：显示左表的全部记录和右表符合连接条件的记录；右外连接：显示右表的全部记录和左表符合连接条件的记录

4、运算符和函数

(1)字符函数

字符连接：CONCAT() 例如：SELECT CONCAT('mm','MYSQL');返回值：mmMYSQL
使用指定的分隔符进行字符连接：CONCAT_WS()
数字格式化：FORMAT()
转换成小写字母：LOWER()
转换成大写字母：UPPER()
获取左侧字符：LEFT()
获取右侧字符：RIGHT()
获取字符长度，空格也算：LENGTH()
删除前导空格：LTRIM()
删除后续空格：RTRIM()
删除前导和后续空格：TRIM()
字符串替换：REPLACE() 例如：SELECT REPLACE('??My??SQL???','?',''); 将？替换成空
字符串截取：SUBSTRING() 例如：SELECT SUBSTRING('MySQL',1,2); 从第一位开始截取两位
模式匹配：[NOT] LIKE，返回值为1，代表true；0代表false；例如：SELECT * FROM test WHERE username LIKE '%1%%' ESCAPE '1'; 代表查找用户名中包含%字符的结果，1后边的%不需要再进行解析，直接认为是字符%，其中1可以为任意的一个字符。

(2)数值运算符与函数

进一取整：CEIL()
舍一取整： FLOOR（）
整数除法：DIV
取余数：MOD
幂运算：POWER()
四舍五入：ROUND() 例如：SELECT ROUND(3.655,2); 四舍五入小数点后留两位
数字截取：TRUNCATE() 例如：SELECT TRUNCATE(3.655,2); 直接截图小数位数

(3)比较运算符与函数

[不]在范围之内：[NOT] BETWEEN...AND... 例如：SELECT 23 BETWEEN 1 AND 23; 闭合区间，[1,23]，返回值为1
[不]在取值范围内：[NOT] IN()
[不]为空：IS [NOT] NULL

(4)日期时间函数

返回当前日期和时间：SELECT NOW();
返回当前日期：SELECT CURDATE();
返回当前时间：SELECT CURTIME();
更改时间：SELECT DATE_ADD('2020-4-5', INTERVAL 365 DAY); 返回2021-4-5，可以加-365 DAY，1 YEAR，3 WEEK，对日期进行变化
返回两个日期的差值：SELECT DATEDIFF('2020-4-5','2022-3-16');返回710
对日期进行格式化：SELECT DATE_FORMAT('2020-4-5','%m/%d/%Y');返回04/05/2020

(5)信息函数

返回当前连接的ID，也是线程的ID：SELECT CONNECTION_ID();
返回当前打开的数据库的名称：SELECT DATABASE();
返回最后插入的记录的ID号：SELECT LAST_INSERT_ID();
返回当前用户的名称：SELECT USER();
返回当前MySQL数据库的版本号：SELECT VERSION();

(6)聚合函数

聚合函数典型特点：只有一个返回值。需要配合数据表使用

平均值：AVG() 例如：SELECT AVG(goods_price) AS avg_price FROM tdb_goods;返回所有价格的平均值
计数：COUNT() 例如：SELECT COUNT(goods_id) AS counts FROM tdb_goods;返回所有记录的条数
最大值：MAX()
最小值：MIN()
求和：SUM()

(7)加密函数

信息摘要算法：MD5() 例如：SELECT MD5('admin');返回32位的字符串
密码算法：PASSWORD() 例如：SET PASSWORD=PASSWORD('234567');修改密码

5、自定义函数

(1)自定义函数简介

用户自定义函数（UDF）是一种对MySql扩展的途径，其用法与内置函数相同。可以返回任意类型的值，同样可以接收这些类型的参数。

自定义函数的两个必要条件：参数和返回值。

创建自定义函数：

CREATE FUNCTION function_name

RETURNS {STRING|INTEGER|REAL|DECIMAL}

routine_body（函数体）

关于函数体：

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

删除函数：DROP FUNCTION [ IF EXISTS ] function_name

(2)创建不带参数的自定义函数

首先打开MySql，使用test数据库。本小节以创建一个显示“2020年1月7日12:26:11”时间的函数为例。

mysql> CREATE FUNCTION y1() RETURNS VARCHAR(30)
    -> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H:%I:%S');

(3)创建带参数的自定义函数

本小节以创建一个带有参数的求两个参数平均值的函数为例。

mysql> CREATE FUNCTION y2(num1 SMALLINT UNSIGNED , num2 SMALLINT UNSIGNED)
 -> RETURNS FLOAT(10) UNSIGNED
 -> RETURN (num1+num2)/2

(4)创建具有复合结构函数体的自定义函数体

本小节以创建一个为test表添加用户功能为例

mysql> DELIMITER //           - -设置只有//结尾时，才运行命令
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> BEGIN
    -> INSERT test(username) VALUES(username);
    -> RETURN LAST_INSERT_ID();
    -> END
    -> //
mysql> DELIMITER ;        - -恢复默认

6、MySql存储过程

(1)存储过程简介

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存放在数据库内，可以由应用程序调用执行，允许用户声明变量以及进行流程控制，可以接收参数（输入输出参数都可以接收）并且可以存在多个返回值；存储过程的效率要比单纯的SQL语句的执行效率高。

存储过程的优点：增强SQL语句的功能和灵活性；实现较快的执行速度(第一次执行除外)；减少网络流量

创建存储过程：创建存储过程语法结构基本和创建自定义函数语法结构相同，最大不同就是参数部分。

CREATE

[DEFINER={user|CURRENT_USER}]

PROCEDURE sp_name([proc_paremeter[,…]])

[characteristic…] routine_body （过程体）

首先可以指定创建者DEFINER，如果省略，则默认指向当前用户；sp_name指的是存储过程的名字，它后面的参数可以有多个，存储过程名使用PROCEDURE修饰，参数前面可以使用IN|OUT|INOUT修饰；存储过程的特性（【characteristic…】）与自定义函数特性是完全相同的；

关于proc_paremeter修饰：

IN：表示该参数的值必须在调用存储过程时指定（存储过程中该值不能返回，只能进不能出）；
OUT：表示该参数的值可以被存储过程代码改变，并且可以返回；
INOUT：表示该参数的可以被指定，并且可以被存储过程体改变和返回。

关于存储过程的特性（【characteristic…】）：

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

关于过程体：

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

(2)创建不带参数的存储过程

本小节以创建不带参数的存储过程（该存储过程获取MySql版本的功能）为例。

创建存储过程：

mysql> CREATE PROCEDURE sp1() SELECT VERSION();

调用存储过程:（两种方式）

CALL sp_name([parameter[,…]])
CALL sp_name[()]

删除存储过程：

DROP PROCEDURE [ IF EXISTS ] sp_name

(3)创建带有IN类型参数的存储过程

本小节以创建一个可以删除指定id记录的存储过程为例。

mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)   
    -> BEGIN
    -> DELETE FROM test WHERE id = p_id;
    -> END
    -> //
mysql> DELIMITER ;

注意事项：

p_id与id的名字不能一样，如果一样在调用此过程时将删除表的所有记录。
如果要修改存储过程的过程体：1）必须先删除存储过程；2）再重建存储过程。
每次自定义函数或存储过程之前：1）DELIMITER //；2）定义完后，再改成DELIMETER ；

(4)创建带有IN和OUT类型参数的存储过程

本小节以创建一个不仅可以根据id删除记录的过程，并且可以返回剩余记录的数量为例。

创建带有in和out参数类型的存储过程【输入+返回】

mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserAndReturnNum(IN p_id INT UNSIGNED , OUT num INT UNSIGNED)
 -> BEGIN
 -> DELETE FROM test WHERE id = p_id;
 -> SELECT COUNT(id) FROM test INTO num;
 -> END
 -> //
mysql> DELIMITER ;

过程调用

mysql> CALL removeUserAndReturnNum(1,@nums);
mysql> SELECT @nums;                - -将剩余的记录数量存储在nums变量之中

(5)创建带有多个OUT类型参数的存储过程

本小节以创建带有多个OUT类型参数的存储过程（创建存储过程根据年龄age字段删除记录，返回的信息有两个，一个是删除的用户数，一个是剩余的用户数）为例。

mysql> CREATE PROCEDURE removeUserReturnInfo(IN p_age INT UNSIGNED, OUT dels SMALLINT UNSIGNED, OUT rests SMALLINT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE age=p_age;
    -> SELECT ROW_COUNT() INTO dels;
    -> SELECT COUNT(id) FROM users INTO rests;
    -> END
    -> //

过程调用

mysql> CALL removeUserReturnInfo(13,@dels,@rests);
mysql> SELECT @dels,@rests;                  - -将删除用户和剩余用户数分别存储在变量dels、rests中

附加说明

ROW_COUNT（）：系统函数，作用为返回插入，删除或者更新被影响到的记录总数

(6)存储过程与自定义函数的区别

存储过程实现的功能要复杂一些；而函数的针对性更强（实际工作中很少使用函数针对表进行操作，都是使用存储过程对表进行操作）；
存储过程可以返回多个值，而函数只能有一个返回值；
存储过程一般独立的来执行（CALL 存储过程名），而函数一般作为SQL语句的组成来出现和内置函数功能一致(SELECT)；

如果开发过程中经常使用到某个功能，一般将它封装为存储过程。

7、MySql存储引擎

(1)存储引擎简介

MySql可以将数据不同的技术存储在文件（内存）中，这种技术就称为存储引擎。

关系型数据库中数据的存储以表的形式来实现的。所以存储引擎也可以称为表类型。

每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。

存储引擎为数据系统的心脏，实现数据存储和查询的功能。

(2)MySQL相关知识点

存储限制——数据库能存多少数据
事务安全——能否保证数据完整性
是否支持索引——是对数据表中一列或者多列的值进行排序的一种结构，能够实现快速查询
锁的粒度大小——用于并发控制，实现数据一致性和完整性(表锁、行锁)
是否支持数据压缩——用于数据压缩，能存储更多数据或者改变数据存储结构
是否支持外键——能够保证数据的一致性

(3)各个存储引擎的特点

最常用的为：MyISAM/InnoDB
MyISAM：适用于事务的处理不多的情况，存储闲置可达256TB，支持索引，表级锁定，数据压缩
InnoDB：适用于事务处理比较多，需要有外键支持的情况，存储闲置为64TB，支持事务和索引，锁颗粒为行锁
Memory 的存储限制是由内存的大小来决定
其他引擎：CSV 存储引擎不支持索引；BlackHole:黑洞引擎，写入的数据都会消失，一般用做数据复制的中继

(4)设置存储引擎

通过创建数据表命令实现：CREATE TABLE table_name(…)ENGINE=engine;
通过修改数据表命令实现：ALTER TABLE_name ENGINE [=] engine_name;

分享几个MySql图形化管理工具：Navicat for MySQL 、MySQL Workbench 、PHPMyAdmin Web

https://www.navicat.com.cn/www.navicat.com.cn

https://www.mysql.com/products/workbench/www.mysql.com https://planet.phpmyadmin.net/planet.phpmyadmin.net

顾茈陌里

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫