mysql数据库20210502_数据库中拒绝更新表的命令-CSDN博客

本文链接：https://blog.csdn.net/weixin_47442524/article/details/116357786

数据库简介

数据库database：存储数据的仓库。为了方便数据的存储和管理，将数据按照特定的规律存储在磁盘上。通过数据库管理系统，有效地组织和管理存储在数据库中的数据。
数据库系统DBS：由数据库、数据库管理系统、应用开发工具组成。
数据库管理系统database management systemDBMS：用来定义数据、管理和维护数据的软件，是数据库系统的重要组成部分。

MySQL数据库

优点：开放源代码的数据库、跨平台性、开源免费、功能强大使用方便

SQL简介

SQL：structured query language，结构化查询语言，数据库管理系统通过SQL语言来管理数据库中的数据
SQL组成部分：
1）DDL：data definition language，数据定义语言，主要用于定义数据库、表、视图、索引和触发器
2）DML：data manipulation language，主要包括对数据的增删改。insert插入数据、update更新数据、delete删除数据
3）DQL：data query language，数据检索语句，用来从表中获取数据，确定数据怎样在应用程序中给出
4）DCL：data control language，数据控制语句，主要用来控制用户的访问权限

MySQL的安装与配置及登陆与退出

1）安装与配置MySQL
-安装目录简介：
bin目录，存储可执行文件；
data目录，存储数据文件；
include目录，存储包含的头文件；
lib目录，存储库文件；
docs目录，文档；
share目录，错误消息和字符集文件；
my.ini文件，MySQL的配置文件——设置字符集<客户端字符集mysql，default-character-set=utf8；服务端字符集mysql，character-set-server=utf8>
2）启动MySQL服务
3）登录/退出MySQL
a）登录——常用参数
-u，用户名
-p，密码
-h，服务器名称
-D、–database=name，打开指定数据库
–prompt=name，设置命令提示符
–delimiter=name，指定分隔符
-V、-version，输出版本信息并且退出
b）退出：exit、quit、\q
4）修改mysql命令提示符
-登录的操作：终端写入mysql -u root -q<然后输入密码>
-Commands end with ; or \g：告诉我们mysql的分隔符使用\g或者；
-Type ‘help;’ or ‘\h’ for help：通过使用help或者\h寻求帮助
-Type ‘\c’ to clear the current input statement：通过\c取消当前操作命令
-mysql>即默认的命令提示符，可通过mysql -u root -q --prompt=<想要修改的方式> 或者通过登录后通过prompt命令进行修改，可通过mysql>修改为原本的命令提示符
-命令提示符常用参数：
\D：完整的日期>
\d：当前数据库，若未打开数据库，则会输出none
\h：服务器名称
\u：当前用户名
5）MySQL常用命令 <不区分大小写>
selcet version()：显示当前版本
select now()：显示当前日期时间
select user()：显示当前用户
后需要添加分隔符
6）MySQL语句的规范
-关键字与函数名全部大写；
-数据库名称、表名称、字段名称等全部小写；
-SQL语句必须以分隔符结尾；
-SQL支持折行操作，只要不把单词、标记或引号字符串分割成两部分，可以在下一行续写；
-数据库名称、表名称、字段名称等尽量不要使用MySQL的保留字，如果需要使用的时候需要使用‘’将名称括起来；
！！！通过DELIMITER x，即将x作为分隔符。

数据库操作DDL

1）创建数据库：CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
其中，{}中的部分是必选项，{DATABASE|SCHEMA}表示二者选其一，[]中的是可选项。CHARACTER 部分是对数据库采用的字符集的设置。
2）查看当前服务器下的数据库列表：SHOW {DATABASES|SCHEMAS}
3）查看指定数据库的定义：SHOW CREATE {DATABASE|SCHEMA} db_name
4）修改指定数据库的编码方式：ALTER {DATABASE|SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name
5）打开指定数据库：USE db_name
6）删除指定数据库：DROP {DATABASE|SCHEMAS} [IF EXISTS] db_name
常用命令：
-SHOW WARMING：查看上一步操作产生的警告信息
-SELECT DATABASE()：查看当前操作的数据库名称

Mysql支持的数据类型

整数类型：
-TINYINT：有符号值-128到127(-2⁷，2⁷-1)；无符号值0到255(0，-2⁸-1)
-SMALLINT：有符号值-32768到32767(-2¹⁵，2¹⁵-1)；无符号值0到65535(0，-2¹⁶-1)
-MEDIUMINT：有符号值-8388608到8388607(-2²³，2²³-1)；无符号值0到16777215(0，-2²⁴-1)
-INT：有符号值-2147683648到2147683647(-2³¹，2³¹-1)；无符号值0到4294967295(0，-2³²-1)
-BIGINT：有符号值(-2⁶³，2⁶³-1)；无符号值0到4294967295(0，-2⁶⁴-1)
-BOOL,BOOLEAN：通过TINYINT(1)实现，0为False，其余为Ture
小数类型：
-FLOAT[(M,D)]：复数取值范围为-3.04E+38到-1.17E-38、0和1.17E-38到3.04E+38，字节4
-DOUBLE[(M,D)]：复数取值范围为-1.79E+308到-2.22E-308、0和2.22E-308到1.79E+308，字节8
-DECIMAL[(M,D)]：定点数，和DOUBLE一样，内部以字符串形式存储数值，字节M+2
-M表示总位数多少，D表示小数点位数多少，MD被省略，则根据硬件允许限制进行保存数值。单精度浮点数精确到大约7位小数。
字符串类型：
-CHAR(M)：不变字符串，M个字节，0≦M≦255
-VARCHAR(M)：可变字符串，L+1个字节，其中L≦M且0≦M≦65535
-TINYTEXT：L+1个字节，其中L<2⁸
-TEXT：L+2个字节，其中L<2¹⁶
-MEDIUMTEXT：L+3个字节,其中L<2²⁴
-LONGTEXT：L+4个字节，其中L<2³²
-ENUM(‘value1’,‘value2’…)：1或2个字节，取决于枚举值的个数（最多65535个值）
-SET(‘value1’,‘value2’…)：1、2、3、4或者8字节，取决于set成员的数目
日期类型：
-TIME：-838:59:59~838:59:59 <存储需求：3>
-DATE：1000-01-01～9999-12-31 <存储需求：3>
-DATETIME：1000-01-01 00:00:00～9999-12-31 23:59:59<存储需求：8>
-TIMESTAMP：1970-01-01 00:00:01 UTC～2038-01-19 03:14:07<存储需求：4>
-YEAR：1901-2155 <存储需求：1>
在开发中使用整形保存时间
二进制类型：保存二进制数据
！如何选择合适的数据类型
如何查看帮助手册
1）help + 语法；
2）？ + 语法；
3）/h + 语法；

Mysql中存储引擎

存储引擎：指定的类型。数据库的存储类型决定了表在计算机中的存储方式。用户可以根据不同的存储方式、是否进行事务处理等来选择合适的存储引擎。
如何查看mysql的存储引擎：
-查看mysql中的存储引擎：SHOW ENGINES
-查看显示支持的存储引擎信息：SHOW VARIABLES LIKE ‘have%’
-查看默认的存储引擎：SHOW VARIABLES LIKE ‘storage_engine’
mysql常用的存储引擎及特点<共九种>：
-InnoDB存储引擎<默认使用>：读写效率较低、占用空间较大
-MyISAM存储引擎：支持静态型、动态型、压缩型，磁盘占用空间较小、速率较快，但不支持事务处理
-MEMORY存储引擎：所有数据存放在内存中，服务器需要足够的内存维持使用，若不需要使用则需要释放。默认哈希索引。意外断电或故障会影响数据的完整性
选择合适的存储引擎

数据表的相关操作

表的含义：
-数据表是数据库的重要组成部分之一，是其他对象的基础
-数据表是存储数据的数据结构
-数据表是包含了特定实体类型的数据
-数据表由行和列构成的二维网络
-数据表一定先有表结构，再有数据
-数据表至少有一列，可以没有行或者多行
-数据表名称要求唯一，而且不要包含特殊字符
如何创建数据表：
-CREATE TABLE [IF NOT EXISTS] tbl_name(字段名称字段类型 [完整性约束条件]…) ENGINE=引擎名称 CHARSET=‘编码方式’。

CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT][[PRIMARY] KEY|UNIQUE [KEY]] [AUTO INCREMENT]
)ENGINE=INNODB CHARSET=UTF8 AUTO_INCREMENT=100;

-完整约束条件：
1）PRIMARY KEY 主键
2）AUTO_INCREMENT 自增长
3）FOREIGN KEY 外键
4）NOT NULL非空
5）UNIQUE KEY 唯一
6）DEFAULT 默认值
定义为主键的值不能重复，主动标注为非空。

如何查看数据库中的数据表以及表结构：
-查看数据库下的数据表：SHOW TABLES
-查看指定表的表结构：DESC tbl_name / DESCRIBE tbl_name / SHOW COLUMNS FROM tbl_name
如何修改表结构
-修改表名：

ALTER TABLE tbl_name RENAME [TO|AS] new_name #方法二
RENAME TABLE tbl_name TO new_name #方法一

-添加字段：

ALTER TABLE tbl_name ADD 字段名称 字段类型 [完整性约束条件]  [FIRST|AFTER 字段名称]

-删除字段：

ALTER TABLE tbl_name DROP 字段名称

-修改字段

ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [完整性约束条件]  [FIRST|AFTER 字段名称]

-修改字段名称

ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件]  [FIRST|AFTER 字段名称]

-添加默认值

ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值

-删除默认值

ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT

-添加主键

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY[index_type] (字段名称)

-删除主键

ALTER TABLE tbl_name DROP PRIMARY KEY

-添加唯一

ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [索引名称] (字段名称)

-删除唯一

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

-修改表的存储引擎

ALTER TABLE tbl_name ENGINE=存储引擎名称

-设置自增长的值

ALTER TABLE tbl_name AUTO_INCREMNET=值

-删除数据表：DROP TABLE IF EXISTS tbl_name[,tbl_name]

数据的操作DML

插入数据

不指定具体的字段名：INSERT [INTO] tbl_name VALUES|VALUE(值…)
列出指定字段：INSERT [INTO] tbl_name(字段名称1,…) VALUES|VALUE(值…)
同时插入多条记录：INSERT [INTO] tbl_name[(字段名称1,…)] VALUES(值…),(值…)…
通过SET形式插入数据：INSERT [INTO] tbl_name SET 字段名称=值,…
将查询结果插入到表中：INSERT [INTO] tbl_name [(字段名称1,…)] SELECT 字段名称 tbl_name [WHERE 条件]

更新数据

UPDATE tbl_name SET 字段名称=值,... [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条数]

删除数据

DELECT FROM tbl_name [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条数]

彻底清空数据表：TRUNCATE [TABLE] tbl_name

查询数据操作DQL

单表查询

查询记录

SELECT select_expr[,select_expr...]
[
FROM table_refernce
[WHERE 条件]
[GROUP BY {col_name|position} [ASC\DESC],...分组]
[HAVING 条件 对分组结果进行二次筛选]
[ORDER BY {col_name|position} [ASC\DESC],...排序]
[LIMIT 限制显示条数]
]

查询表达式

每一个表达式表示想要的一列，必须至少有一列，多列之间以逗号分隔
*表示所有列tbl_name.*可表示命名表的所有列
查询表达式可以使用[AS]alias_name赋予别名，不建议省略as
查询过程中忽略大小写

WHERE 条件

比较：= < > <= >= != <> !> !< <=>
指定范围： BETWEEN AND / NOT BETWEEN AND
指定集合：IN / NOT IN
匹配字符：LIKE / NOT LIKE
是否为控制： IS NULL / IS NOT NULL
多个查询条件：AND / OR

GROUP BY 查询结果分组

配合GROUP_CONCAT()得到分组详情
配合聚合函数：COUNT()、MAX()、MIN()、AVG()、SUM()
配合WITH POLLUP记录上面所有记录总和

HAVING子句

通过HAVING子句对分组结果进行二次筛选

ORDER BY 排序

通过ORDER BY 对查询结果排序，默认采用升序排列ASC，降序通过DESC

LIMIT限制查询结果显示条数

LIMIT显示条数
LIMIT偏移量，显示条数
当在更新数据时，设置更新条数时，不可以设置偏移量，仅使用一位数字表示条数

连接查询

连接查询：将两个或两个以上的表按某个条件连接起来，从中选择需要的数据。可同时查询两个或两个以上的表。当不同表中存在相同意义的字段时，可以通过该字段连接几个表

内连接查询

JOIN | CROSS JOIN | INNER JOIN
通过ON 连接条件
显示两个表中符合连接条件的记录

外连接查询

左外连接：LEFT [OUTER] JOIN，显示左表的全部记录及右表复合连接条件的记录
右外连接：RIGHT [OUTER] JOIN，显示右表的全部记录及左表符合连接条件的记录

外键

外键：表的一种特殊字段，被参照的表是主表，外键所在字段为子表。
设置原则：以来数据库中已存在的表的主键
作用：建立该表与父表的关联关系。父表中对记录进行操作时，子表中与之对应的信息应相应改变
外键的作用保持数据的一致性与完整性
可以实现一对一或一对多的关系
注意
-父表和子表必须使用相同的存储引擎，而且禁止使用临时表
-数据表的引擎必须是InnoDB
-外键列和参照列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符的长度可以不相同
-外键列与参照列必须建立索引，如果外键列不存在索引，mysql自动创建索引
外键约束的参照操作：
-CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
-SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL，如果使用该选项，必须保证子列表中没有指定的NOT NULL
-RESTRICT：拒绝对父表删除或更新操作
-NO ACTION：表中sql关键字，与RESTRICT操作相同

联合查询

UNION / UNION ALL
UNION和UNION ALL 区别是UNION去掉相同记录，UNION ALL是简单的合并到一起

子查询

子查询：将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果可作为外层查询语句提供条件。
引发子查询的情况：
1）使用[NOT]IN的子查询
2）使用比较运算符的子查询< > <= >= <> != <=>
3）使用[NOT]EXISTS的子查询
4）使用ANY|SOME或ALL的子查询

a）>=或>：ANY最小值、SOME最小值、ALL最大值
b）<=或<：ANY最大值、SOME最大值、ALL最小值
c）=：ANY任意值、SOME任意值
d）<>或!=：ALL任意值

将查询结果写入数据表中

INSERT [INTO] tbl_name[(col_name,...)] SELECT...

新建数据表同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement

正则表达式查询

REGEXP’匹配表达式
常用匹配方式
1）^：匹配字符开始的部分
2）$：匹配字符结束的部分
3）.：代表字符串中任意一个字符，包括回撤和换行
4）[字符集合]：匹配字符集合中的任何一个字符
5）[^字符集合]：匹配除了字符集合以外的任何一个字符
6）S1|S2|S3：匹配S1S2S3中任意一个字符
7）*：代表0或1个或者多个其前的字符
8）+：代表1个或多个其前的字符
9）String[N]：代表字符串出现N次
10）字符串[M,N]：字符串至少出现M次，最多N次

Mysql中运算符

算数运算符：
+：加法
-：减法
*：乘法
/：除法
D|V ：除法
%：取余
MOD：取余
比较运算符：结果只能为真或假
=：判断是否相等
<>或!=：判断是否相等
<=>：判断是否相等，可以判断NULL
>或>=：判断是否大于等于
<或<=：判断是否小于等于
IS NULL或 IS NOT NULL：判断是否为NULL
BETWEEN AND 或NOT BETWEEN：判断是否在范围内
IN或NOT IN：判断是否在某一个固定的范围内
LIKE或NOT LIKE：判断是否匹配
PEGEXP：判断是否正则匹配
逻辑运算符
&&或AND：与
||或OR：或
!或NOT：非
XOR：异或
运算符的优先级
！～ ^ *,/,DIV,MOD,% +,- >>,<< & | =,<=>,<,<=,>=,!=,IN,<>,IS NULL,LIKE,REGEXP BETWEEN AND,CASE,WHEN,THEN,ELSE NOT &&,AND ||,OR ,XOR ;=
可以通过括号()改变优先级

Mysql中函数

数学函数库

GEIL()：进一取整
FLOOR：舍一取整
MOD：取余数
POWER()：幂运算
ROUND()：四舍五入
TRUNCATE()：数字截取
ABS()：取绝对值
PI()：圆周率
RAND() RAND(X)：返回0-1之间随机数
SIGN(X)：返回x的符号，x为负数、0、正数
EXP(X)：计算e的几次方

字符串函数库

CHAR_LENGTH(S)：返回字符串的字符数
LENGTH：返回字符串的长度
CONCAT(S1,S2…)：将字符串合并为一个字符串
CONCAT_WS(X,S1,S2…)：以制定分隔符连接字符串
UPPER(S)/UCASE(S)：将字符串转换成大写
LOWER(S)/LCASS(S)：将字符串转换成小写
LEFT(S,N)/RIGHT(S,N)：返回字符串前/后n个字符
LPAD(S1,LEN,S2)/RPAD(S1,LEN,S2)：将字符串S1用S2填充到指定的LEN
LTRIM(S)\RTRIM(S)\TRIM(S)：去除字符串中的空格
TRIM((S1 FROM S)：去掉字符串s开始处和结尾处的s字符串1
REPEAT(S,N)：重复字符串指定次数
SPACE(N)：返回n个空格
REPLACE(S,S1,S2)：在字符串s中搜索上，使用s2替换掉
STRCMP(S1,S2)：比较字符串,>=<分别返回1，0，-1
SUBSTRING(S,N,LEN)：截取字符串
REVERSE(S)：反转字符串
ELT(N,S1,S2)：返回指定位置的字符串

日期时间函数库

CURDATE(),CURRENT_DATE()：返回当前日期
CURTIME,CURRENT_TIME()：返回当前时间
NOW()：返回当前日期和时间
MONTH(D)：返回日期中月份值
MONTHNAME(D)：返回日期中月份名字
DAYNAME(D)：返回星期几
DAYOFWEEK(D)：返回一周内的第几天，1代表星期日
WEEKDAY(D)：返回日期是星期几，0代表星期一
WEEK(D)：一年中的第多少个星期
YEAR(D)：返回年份值
HOUR(T)：返回小时值
MINUTE(T)：返回分钟值
SECOND(T)：返回秒数
DATEIFF(D1,D2)：计算两个日期之间相隔的天数

条件判断函数

IF(EXPR,V1,V2)：如果表达式expr成立，返回结果v1，否则返回v2
IFNULL(V1,V2)：如果V1不为空，就显示v1的值，否则v2
CASE WHEN exp1 THEN v1[WHEN exp2 THEN v2][ELSE vn] END：case表示函数开始，end表示函数结束。如果表达式exp1成立时，返回v1；如果表达式exp2成立时，返回v2，以此类推，遇到ELSE ，返回vn值

系统函数

VERSION()：返回数据库的版本号
CONNECTION_ID()：返回服务器的连接数
DATABASE(),SCHEMA()：返回当前数据库名
USER()SYSTEM_USER()：返回当前用户
CURRENT_USER(),CURRENT_USER：返回当前用户
CHARSET(STR)：返回字符串STR字符集
COLLATION(STR)：返回字符串STR的校验字符集
LAST_INSERT_ID()：返回最近生成的AUTO_INCREMENT

加密函数

MD5(str)：信息摘要算法
PASSWORD(str)：密码算法
ENCODE(str,pwd_str)：加密结果是一二进制数，必须使用BLOB类型字段保存
DECODE(crypt_str,pwd_str)：对通过ENCODE加密之后的内容解密

其他常用函数

FORMAT(x,n)：将数字x进行格式化，将x保留到小数点后n位
ASCII(S)：返回字符串s的第一个ASCII码
BIN(X)：返回x的二进制编码
HEX(X)：返回x的十六进制编码
OCT(X)：返回x的八进制编码
CONV(x,f1,f2)：将x从f1进制数变成f2进制数
INET_ATON(IP)：将IP地址转换成数字
INET_NOVA(N)：将数字转换成ip地址
GET_LOCT(name,time)：定义锁
RELEASE_LOCK(name)：解锁

索引的使用

索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度。
-优点：提高检索数据的速度
-缺点：创建和维护索引需要耗费时间
-索引可以提高查询速度，会减慢写入速度
索引的分类：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引
创建索引的方法：
1）创建表的时候创建索引：CREATE TABLE tbl_name(字段名称字段类型 [完整性的约束条件]...[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名称] [(长度)] [ASC|DESC])
2）在已经存在的表上创建索引：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 [(长度)] [ASC|DESC]
ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] 索引名称(字段名称 [(长度)] [ASC|DESC]）