2024Mysql And Redis基础与进阶操作系列（2）作者——LJS[含MySQL登录；DDL；DML；举例说明；编码等详解步骤及常见报错问题对应的解决方法]

# 启动 MySQL 服务命令：

net start MySQL服务名

# 停止 MySQL 服务命令： 

net stop MySQL服务名

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

例如：
mysql -h localhost -P 3306 -u root -pabc123  
# root用户的密码是abc123 

例如：
 mysql -hlocalhost -P3306 -uroot -pabc123

mysql -h localhost -P 3306 -u root -p Enter password

mysql -u root -p Enter password

c:\> mysql -V 
c:\> mysql --version

登录后，通过以下方式查看当前版本信息:
mysql> select version(); 

exit 或 quit 

# 创建表create table [if not exists]表名
(字段名1 类型[(宽度)] [约束条件] [comment '字段说明'], 
字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],  
字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];

创建表是构建一张空表，
指定这个表的名字，
这个表有几列，
每一列叫什么名字，
以及每一列存储的数据类型。

修改表添加列

alter table 表名 add 列名 类型(长度) [约束]

修改列名和类型

alter table 表名 change 旧列名 新列名 类型(长度) 约束

修改表删除列

alter table 表名 drop 列名;

修改表名

rename table 表名 to 新表名;

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； 

//向表中插入一些列

insert into 表 values (值1,值2,值3...)    

 //向表中插入所有列

update 表名 set 字段名=值,字段名=值...;

update 表名 set 字段名=值,字段名=值... where 条件;

delete from 表名 [where 条件];

truncate table  表名 或者 truncate 表名

show databases; 

create database 数据库名; 

//创建atguigudb数据库，该名称不能与已经存在的数据库重名。

 create database atguigudb; 

use 数据库名

#使用atguigudb数据库 

use atguigudb

//如果没有使用use语句，
后面针对数据库的操作也没有加“数据名”的限定，
那么会报“ERROR 1046 (3D000): No database selected”（没有选择数据库） 

show tables 

#要求前面有use语句 

show tables from 数据库名

create table 表名称(   字段名 数据类型,   字段名 数据类型 ); 

//如果是最后一个字段，后面就用加逗号，因为逗号的作用是分割每个字段。

create table student(   id int,   name varchar(20)  

#名字最长不超过20个字符 

select * from 数据库表名称;

#查看学生表的数据 

select * from student; 

insert into 表名称 values(值列表);

#添加两条记录到student表中

 insert into student values(1,'张三'); 

insert into student values(2,'李四'); 

/*报错：
mysql> insert into student values(1,'张三'); ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'name' at row 1 


mysql> insert into student values(2,'李四'); ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'name' at row 1 
 */

show create table 表名称\G 

//查看student表的详细创建信息

 show create table student\G 

#结果如下*************************** 1. row *************************** 

    Table: student 
Create Table: CREATE TABLE `student` (  `id` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) 

show create database 数据库名\G 

//查看atguigudb数据库的详细创建信息  

show create database atguigudb\G 

 *************************** 1. row ***************************     
    Database: atguigudb 

Create Database: CREATE DATABASE `atguigudb` /*!40100 DEFAULT CHARACTER SET latin1 */ 1 row in set (0.00 sec) 

drop table 表名称; 

//删除学生表 

drop table student; 

drop database 数据库名;

//删除atguigudb数据库 

drop database atguigudb; 

mysql> INSERT INTO t_stu VALUES(1,'张三','男'); 

ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1 

show variables like 'character_%'; 

show variables like 'collation_%'; 

大概在63行左右，在其下添加 default-character-set=utf8 默认字符集 

大概在76行左右，在其下添加 character-set-server=utf8 collation-server=utf8_general_ci 

sudo service mysql restart

show variables like 'character_%';

show variables like 'collation_%'; 

mysqld mysqld --defaults-

file="D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini" --skip-grant-tables

（1） use mysql;

（2）update user set authentication_string=password('新密 码') where user='root' and Host='localhost'; 

（3）flush privileges; 

C:\Users\Administrator>net stop mysql            

MySQL 服务正在停止..            

MySQL 服务已成功停止。   

    mysqld --console --skip-grant-tables --shared-memory                

    C:\Users\Administrator>mysqld --console --skip-grant-tables --shared-memory        
    
    2024-05-09T02:10:48.290981Z 0 [System] [MY-010116] [Server] F:\mysql\mysql-8.0.36-        winx64\bin\mysqld.exe (mysqld 8.0.36) starting as process 5344        

    2024-05-09T02:10:48.318791Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.        
    
    2024-05-09T02:10:49.700249Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.        
    
    2024-05-09T02:10:51.926542Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'        
    
    2024-05-09T02:10:52.119615Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.        
    
    2024-05-09T02:10:52.119846Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

    2024-05-09T02:10:52.215835Z 0 [System] [MY-010931] [Server] F:\mysql\mysql-8.0.36-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.36'  socket: ''  port: 0  MySQL Community Server - GPL.                
    
    注意：不要关闭中断  

PS C:\Users\Administrator> mysql -uroot -p        

Enter password: 直接敲回车键        

Welcome to the MySQL monitor.  Commands end with ; or \g.        

Your MySQL connection id is 7        

Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.


 Oracle is a registered trademark of Oracle Corporation and/or its  affiliates. Other names may be trademarks of their respective  owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                

mysql> flush privileges;        

Query OK, 0 rows affected (0.05 sec)                

mysql> alter user root@localhost identified by '123456';        

Query OK, 0 rows affected (0.02 sec)        

mysql> \q                       

PS C:\Users\Administrator> net start mysql        

MySQL 服务正在启动 ..        

MySQL 服务已经启动成功。                

PS C:\Users\Administrator> mysql -uroot -p123456        

mysql: [Warning] Using a password on the command line interface can be insecure.        

Welcome to the MySQL monitor.  Commands end with ; or \g.        

Your MySQL connection id is 8        

Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its  affiliates. 

Other names may be trademarks of their respective   owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q   

PS C:\Users\Administrator> net stop mysql            

MySQL 服务正在停止.            

MySQL 服务已成功停止。   

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

 C:\Users\Administrator>mysqld --init-file=F:\mysql\mysql-8.0.36-winx64\mysql_init.txt

PS C:\Users\Administrator> mysql -uroot -p123456           

mysql: [Warning] Using a password on the command line interface can be insecure.            

Welcome to the MySQL monitor.  Commands end with ; or \g.            

Your MySQL connection id is 9            

Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its   affiliates. 

Other names may be trademarks of their respective  owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q

 C:\Users\Administrator>mysqld --init-file=F:\mysql\mysql-8.0.36-winx64\mysql_init.txt                    

C:\Users\Administrator>net start mysql            

MySQL 服务正在启动 ..            

MySQL 服务已经启动成功。                        

C:\Users\Administrator>mysql -uroot -p123456            

mysql: [Warning] Using a password on the command line interface can be insecure.            

Welcome to the MySQL monitor.  Commands end with ; or \g.            

Your MySQL connection id is 8            

Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its  affiliates.

Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q

mysql> INSERT INTO t_stu VALUES(1,'张三','男'); 

ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1 

SHOW VARIABLES LIKE 'character_set_%';  

mysql> create database 0728db charset Latin1; 

Query OK, 1 row affected (0.00 sec)


mysql> use 0728db; Database changed

mysql> create table student (id int , name varchar(20)) charset Latin1; Query OK, 0 rows affected (0.02 sec) 

mysql> show create table student\G

*************************** 1. row *************************** 


Table: student 

Create Table: CREATE TABLE `student`

(  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`) ) 

ENGINE=InnoDB DEFAULT CHARSET=latin1 

1 row in set (0.00 sec) 

mysql> alter table student modify name varchar(20) charset utf8; 

#修改字段字符编码为UTF8 Query OK, 0 rows affected (0.05 sec) 

Records: 0 Duplicates: 0 Warnings: 0 

mysql> show create table student\G 

*************************** 1. row ***************************     

Table: student 


Create Table: 

CREATE TABLE `student` 


(  `id` int(11) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`) ) 

ENGINE=InnoDB DEFAULT CHARSET=utf8 

1 row in set (0.00 sec) 

mysql> show create database 0728db;; 


+--------+-----------------------------------------------------------------+ 

|Database| Create Database                         | 

+------+-------------------------------------------------------------------+ 

|0728db| CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET latin1 */ 

| +------+-------------------------------------------------------------------+ 

1 row in set (0.00 sec) 


mysql> alter database 0728db charset utf8; 

#修改数据库的字符编码为utf8 

Query OK, 1 row affected (0.00 sec) 

mysql> show create database 0728db;

 +--------+-----------------------------------------------------------------+ 

|Database| Create Database                         | 

+--------+-----------------------------------------------------------------+ 

| 0728db | CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET utf8 */ 

| +--------+-----------------------------------------------------------------+ 

1 row in set (0.00 sec)