MySql

Mysql常用命令

use [table_schema]
set names utf8 # 设置编码
show databases # 展示所有数据库
show tables # 展示制定数据库的所有表
show columns(index) from [table_name] # 展示制定数据表的列信息(以及主键信息)
show table status [from db_name] [like 'pattern'] [\G] # 输出性能及统计信息，查看表类型（引擎），from 选取库来源，可用正则表达式选取表来源，\G 将按列打印信息
## show table status from mysql like 'mysql%' \G;
exit # 退出
create table(database) [name] [name type]# 创建表或数据库，前者需要制定字段与类型
drop table(database) [name] # 删除数据库或表
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); # 插入新的行，分别指定除了id之外的特定字段与值，如果数据是字符型则必须有引号
INSERT INTO table_name VALUES ( value1, value2,...valueN ); # 插入新的行，默认所有列都添加数据，第一列为主键，若主键自增(PRIMARY KEY AUTO_INCREMENT)则添加0或者null即可
UPDATE [table_name] SET [name]='' [WHERE] # 更新表数据
## UPDATE runoob_tbl SET runoob_title = REPLACE(runoob_title, 'C++', 'Python') where runoob_id = 3; # 批量将C++替换为Python
DELETE FROM [table_name] [WHERE] # 删除表记录，不指定WHERE则删除所有记录
ALTER TABLE [table_name] DROP [name] # 删除表的某个字段
						 Engine = [engine] # 修改数据表类型
						 ADD name type [FIRST[AFTER NAME]] # 给表添加字段,FIRST表名字段位置位于首位，AFTER表示在某字段之后
						 INDEX [index_name] (column_name) # 添加索引，若是添加唯一索引则将INDEX改为UNIQUE
						 PRIMARY KEY ([field_name]) # 添加主键
						 RENAME TO [name] # 重命名
						 ALTER [name] DROP DEFAULT # 删除默认数值
						 			  SET DEFAULT [number] # 更改默认数值

  • Mysql函数查询

  • WHERE BINARY 后面的字符串区分大小写

  • WHERE field LIKE condition 实现模糊匹配，"condition"中"%"表示0个或多个的任意字符，"_"表示单个任意字符，"[]"表示所列字符中的一个，"[^]"表示不在所列字符中的字符。"[]"括起特殊符号可以避免通配符的二义性

  • UNION ALL 会输出重复内容

  • ORDER BY field1 [ASC[DESC]], field2 [ASC] "ASC"升序，"DESC"降序，默认ASC。按拼音排序时，若使用GBK编码，则直接使用字段排序；若使用UTF-8编码，则需要先转码成GBK再排序

    SELECT * FROM [table_name] ORDER BY CONVERT(field using gbk)

  • INNER JOIN

    mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)

    LEFT JOIN 会读取左边数据表的全部数据，即使右边无对应数据

  • NULL值处理运算符

    IS NULL #是NULL则返回true
IS NOT NULL
<=> # 两个值相等或都为NULL时返回true

    使用其他比较运算符永远返回NULL

    ifnull(columnName2,0) 把 columnName2 中 null 值转为 0

  • ALTER 修改中，MODIFY 只修改字段类型：MODIFY j BIGINT NOT NULL DEFAULT 100; 。CHANGE 同时修改字段名称和类型：CHANGE j j INT; 。

  • 字段类型"DECIMAL"：DECIMAL(m,n) 表示浮点数，最大位数为m，小数点右侧的位数最多为n。

  • 完整拷贝表：
    方法一：先查看被复制表的结构信息：

    SHOW CREATE TABLE test1 \G;

    会得到创建该结构的语句，复制粘贴后创建相同结构的新表(注意改名)。随后将原表内容拷贝至新的表：

    INSERT INTO test1 (id,title,content) SELECT id,title,content from test;

    方法二：

    CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

    方法三：

    CREATE TABLE targetTable SELECT * FROM sourceTable

  • 拷贝表的部分字段：

    CREATE TABLE test3 AS(
	SELECT title,content FROM test2
)

  • 服务器元信息：

    SELECT VERSION() # 服务器版本
SELECT DATABASE() # 当前数据库名称
SELECT USER() # 当前用户名
SHOW STATUS	# 服务器状态
SHOW VARIABLES # 服务器配置信息

  • 查询最后的插入表中的自增列的值：

    SELECT LAST_INSERT_ID();

  • 对自增id重新排列(删除记录会导致不连续)：

    可以删除字段后重新添加

    ALTER TABLE [table_name] DROP id;
ALTER TABLE [table_name] ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
ALTER TABLE [table_name] ADD PRIMARY KEY (id);

    新建表时在末尾添加 AUTO_INCREMENT=100 可将初始值定为100，或者使用一下语句：

    ALTER TABLE [table_name] AUTO_INCREMENT=100;

  • SELECT DISTINCT 自动省略重复数据

    查询重复数据：

    SELECT column_name1,column_name2,count(1) AS COUNT FROM [table_name] GROUP BY column_name1,column_name2 HAVING count>1;
# GROUP BY后加括号会有别的意义

  • DESC tableName | Describe tableName 展示表结构

  • select distinct column from tableName 显示表中column字段唯一的值（过滤重复）

  • select colA "A", colB "B", colC "c" from tableName 利用 "sql*plus" 语句定制列名

  • select concat(colA, ’, ‘, colB) as alias from tableName 拼接得到 colA, colB 作为列的内容，以"alias"为列名展示。要求内部的字符串必须使用单引号 ', '

  • 设置密码：
    ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY "123456";
    SET PASSWORD FOR 'root'@'localhost' = '123456';

    mysqladmin -u root -p password 123456

  • 修改表字段顺序：

    ALTER TABLE lytable ADD date datetime AFTER userId;

ALTER TABLE lytable CHANGER date datetime AFTER userId;

Mysql在PHP中的应用模板

<?php
$dbhost = 'localhost';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
 
if( isset($runoob_count ))
{
   $sql = "SELECT runoob_author, runoob_count
           FROM  runoob_test_tbl
           WHERE runoob_count = $runoob_count";
}
else
{
   $sql = "SELECT runoob_author, runoob_count
           FROM  runoob_test_tbl
           WHERE runoob_count IS NULL";
}
mysqli_select_db( $conn, 'RUNOOB' );

mysqli_begin_transaction($conn);            // 开始事务定义
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)"))
{
    mysqli_query($conn, "ROLLBACK");     // 判断当执行失败时回滚
}
$seq = mysql_insert_id ($conn_id);	// 获取最后插入的自增列的值
$count = mysqli_affected_rows ($conn_id);	// 读取受影响的记录数量
print ("$count 条数据被影响\n");
mysqli_query($conn, "COMMIT")；	//执行成功后提交

$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>菜鸟教程 IS NULL 测试<h2>';
echo '<table border="1"><tr><td>作者</td><td>登陆次数</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
    echo "<tr>".
         "<td>{$row['runoob_author']} </td> ".
         "<td>{$row['runoob_count']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

参考：[

Runoob-Mysql教程

]

2022/8/21：今天在安装mysql时出现了问题，记录一下解决过程

问题汇总

问题一：无法启动mysql服务

下载并解压mysql社区版后cd到根目录创建"my.ini"文件

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

注意里面是有安装目录路径需要设置的，一开始没看到路径出错导致数据库初始化一直不成功，--console 回显中出现许多error。设置好my.ini后安装mysql

.\mysqld --install
.\mysqld install
# 安装mysql，有无--都可
.\mysqld --remove
.\mysqld remove
# 卸载mysql

不知道为什么网上的教程很多命令行都是没有 .\ 的，除此之外命令功能基本一致，安装mysql后初始化

.\mysqld --initialize --console
# --console 会输出初始化信息，其中包括数据库的初始密码
.\mysqld --initialize-insecure
# 初始化的同时数据库初始无密码

初始化数据库就是在创建data文件夹，若是步骤出错需要重新初始化，可以手动删除data文件夹再重新初始化（不可以直接初始化，会出现报错），重新初始化可用于重置密码

初始化完毕后启动mysql程序即可，这里本地出现了报错

PS F:\Mysql\mysql-8.0.28-winx64\bin> net start mysql
MySQL 服务正在启动 .
MySQL 服务无法启动。

服务没有报告任何错误。

请键入 NET HELPMSG 3534 以获得更多的帮助。

原因是端口3306被占用了，参考了博客：解决方案

netstat -ano
# 查看进程，根据PID从任务管理器的详情中管理进程

查看本地进程时发现3306端口运行的就是mysql程序，但是那是phpstudy集成的mysql，我忘记关闭了导致端口被其占用，关闭后再次启动mysql任务，成功启动

net start mysql
# 启动mysql
net stop mysql
# 关闭mysql

问题二：同时存在phpstudy的mysql与自己装的mysql，兼容问题

自己装的mysql若是安装了，则phpstudy中的mysql就无法一键启动，前者一旦卸载则后者恢复正常

phpstudy中的mysql分为两种情况，一种是在extension/下手动安装mysql，一种是不做任何手动处理直接一键启动，两种情况互不兼容

进入phpstudy的extensions文件夹中的mysql，使用命令行将mysql安装后，phpstudy将无法一键启动该数据库，但可以使用 net start mysql 启动mysql服务；卸载mysql后phpstudy可正常一键启动，无法使用命令行启动服务。

phpstudy一键启动mysql后，可以在bin目录下使用命令 mysql -u root -p 正常登录并使用mysql

命令行安装mysql只能选择一个版本，且一旦安装则phpstudy无法一键启动mysql服务。

phpstudy 的 mysql 命令行使用需要配置 bin 目录的环境变量（直接在 bin 目录下输入 mysql 可能会提示找不到命令）

问题三：mysql创建表有语法错误

mysql> CREATE TABLE testing(
    -> id int not null auto_increment,
    -> title varchar(100) not null,
    -> primary key ( id ),
    -> )ENGINE=InnoDB DEFAULT CHARSET=uft8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')engine=innodb default charset=utf8' at line 7

选取主键的语句后多了一个逗号，不同于一些高级语言对象中的多个键值对可以挂载逗号，这里多了一个逗号会导致语法错误，删除后即可正常创建数据表

问题四：执行事务回滚无效

方法一：数据库引擎需要更换成InnoDB，在"my.ini"中更改 default-storage-engine=INNODB ,默认是MyISAM，只有InnoDB可以执行事务。修改后重启mysql，然后新建一张表（原来的表是在MyISAM引擎下创建的，仍然不能使用事务），即可执行事务。

方法二：新建表时带上引擎参数：CREATE TABLE test (id int(5)) engine=innodb; ，仅限该表格可执行事务。

参考：[

驰愿-mysql事务回滚无效

]

问题五：Install/Remove of the Service Denied!

权限不够进行操作，使用管理员权限打开shell即可

问题六：mysql8安装

mysql8默认开启ssl密码验证，缺少文件时登录会一直失败，需要在ini文件中配置：

[mysqld]
skip-ssl

来跳过ssl认证，或者使用：

mysql -h localhost -u root --ssl-mode=DISABLED

需要指定登录用户为root，否则会有：

ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

初始化时带上 --initialize-insecure 即可不带密码初始化，从而直接登录。

进去后 select user,host,plugin from mysql.user; 查看密码策略

若要使用密码登录，上述步骤完成后仍会出现：

ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

网上提示修改默认插件：

[mysqld]
# 更改默认密码验证方式为原始方式
default_authentication_plugin=mysql_native_password

实测下来没用。后来发现他可能是在初始化时生效的，只是重启服务是不够的。

问题解释：MySQL:caching_sha2_password快速问答

远程连接MySQL错误“plugin caching_sha2_password could not be loaded”的解决办法 不适用于我的情况，一是不用软件连接，而是此时登录不进去无法修改。目前不知道如何解决上述问题。

Tips

posted @   Festu  阅读(30)  评论(0)  编辑  收藏  举报
相关博文：
· [Sqli-Labs-Master] - Page_1
· BUUOJ——WEB（一）
· Mysql常用命令 - 正删改查
· MySQL 安装、密码设置、"库 、表、记录"的增删改查、字符编码设置
· MySQL命令总结
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· AI技术革命，工作效率10个最佳AI工具
点击右上角即可分享
微信分享提示
AI IDE Trae