Let's talk about the basic commands of MySQL database (summary sharing)-Mysql Tutorial-php.cn

This article brings you knowledge about the basic commands of the mysql database. The mysql database has some basic functions, such as being able to create or delete tables. Let's take a look at how to use them. I hope it will be helpful to everyone.

Let's talk about the basic commands of MySQL database (summary sharing)

##Lets talk about the basic commands of MySQL database (summary sharing). Overviewtalk about the basic commands of MySQL database (summary sharing)>Database structure

数据库–>数据表–>Lets talk about the basic commands of MySQL database (summary sharing)（记录）：用来描述一个对象的信息
              Lets talk about the basic commands of MySQL database (summary sharing)（字段）：用来描述对象的一个属性

Copy after login

talk about the basic commands of MySQL database (summary sharing)>Common data typestalk about the basic commands of MySQL database (summary sharing)>TypeDescription##intfloatdoublecharvarchartextimagedecimal (5,Lets talk about the basic commands of MySQL database (summary sharing))##Supplement


Integer type
Single precision floating point-------4 bytes Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing) bits
Double precision floating point------ -8 bytes 64 bits
fixed length character type
can Variable length character type
Text
Picture
5 valid length digits, Lets talk about the basic commands of MySQL database (summary sharing) digits after the decimal point

char can store up to Lets talk about the basic commands of MySQL database (summary sharing)55 characters. If the actual length of the stored data is smaller than the specified length, char will fill in spaces to the specified length; if the actual length of the stored data is greater than the specified length, the lower version will be intercepted, and higher versions will report an error. The length of char is immutable, while the length of varchar is variable. That is to say, define a char[Lets talk about the basic commands of MySQL database (summary sharing)0] and varchar[Lets talk about the basic commands of MySQL database (summary sharing)0]. If 'csdn' is stored, then the length occupied by char The length is still Lets talk about the basic commands of MySQL database (summary sharing)0, except for the character 'csdn', followed by six spaces, and varchar immediately changes the length to 4

varchar storage rules:

Below version 4.0 , varchar(Lets talk about the basic commands of MySQL database (summary sharing)0), refers to Lets talk about the basic commands of MySQL database (summary sharing)0 bytes. If UTF8 Chinese characters are stored, only 6 can be stored (each Chinese character is Lets talk about the basic commands of MySQL database (summary sharing) bytes) Version 5.0 or above, varchar(Lets talk about the basic commands of MySQL database (summary sharing)0), refers to Lets talk about the basic commands of MySQL database (summary sharing)0 characters, Regardless of whether numbers, letters or UTF8 Chinese characters are stored (each Chinese character is Lets talk about the basic commands of MySQL database (summary sharing) bytes), Lets talk about the basic commands of MySQL database (summary sharing)0 can be stored, and the maximum size is 655Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing) bytes.
Lets talk about the basic commands of MySQL database (summary sharing). View the database structure

View the database in the current server

SHOW DATABASES;		#不区分大小写，分号“;”表示结束

Copy after login

talk about the basic commands of MySQL database (summary sharing)>talk about the basic commands of MySQL database (summary sharing)>##View the tables contained in the database

USE 数据库名;SHOW TABLES;

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

talk about the basic commands of MySQL database (summary sharing)>View the structure (fields) of the table

方法Lets talk about the basic commands of MySQL database (summary sharing)USE 数据库名;可缩写成：DESC 表名;方法Lets talk about the basic commands of MySQL database (summary sharing)DESCRIBE  数据库名.表名;

Copy after login

talk about the basic commands of MySQL database (summary sharing)>Lets talk about the basic commands of MySQL database (summary sharing). Introduction to SQL statements

Lets talk about the basic commands of MySQL database (summary sharing) SQL statements are used for maintenance Manage the database, including data query, data update, access control, object management and other functions.

talk about the basic commands of MySQL database (summary sharing)>

CategoryDescription##DDLData definition Language, used to create database objects, such as libraries, tables, indexes, etc.DMLData manipulation language, used to manage data in tablesDQLData query language, used to find qualified data records from data tablesDCLData control language, used to set or change database user or role permissions四、创建及删除数据库和表（DDL）talk about the basic commands of MySQL database (summary sharing)>创建新的数据库talk about the basic commands of MySQL database (summary sharing)>

CREATE DATABASE 数据库名;例如：create database arts;

Copy after login

创Lets talk about the basic commands of MySQL database (summary sharing)

创建新的表talk about the basic commands of MySQL database (summary sharing)>

CREATE TABLE 表名 (字段Lets talk about the basic commands of MySQL database (summary sharing) 数据类型,字段Lets talk about the basic commands of MySQL database (summary sharing) 数据类型[,...][,PRIMARY KEY (主键名)]);#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。例：create database arts;use arts;create table star (id int not null,name char(Lets talk about the basic commands of MySQL database (summary sharing)0) not null,sex char(Lets talk about the basic commands of MySQL database (summary sharing)),primary key (id));desc star;

Copy after login

创Lets talk about the basic commands of MySQL database (summary sharing)

删除指定数据表talk about the basic commands of MySQL database (summary sharing)>

如不用USE进入库中，则需加上数据库名DROP TABLE 数据库名.表名;				进入数据库，则直接加表名drop table 表名

Copy after login

创Lets talk about the basic commands of MySQL database (summary sharing)

删除指定的数据库talk about the basic commands of MySQL database (summary sharing)>

DROP DATABASE 数据库名;

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

五、管理表中数据记录（DML）talk about the basic commands of MySQL database (summary sharing)>向数据表中插入新的数据记录talk about the basic commands of MySQL database (summary sharing)>

INSERT INTO 表名(字段Lets talk about the basic commands of MySQL database (summary sharing),字段Lets talk about the basic commands of MySQL database (summary sharing)[,...]) VALUES(字段Lets talk about the basic commands of MySQL database (summary sharing)的值,字段Lets talk about the basic commands of MySQL database (summary sharing)的值,...);例：create database market;use market;create table star (id int(Lets talk about the basic commands of MySQL database (summary sharing)) not null,name char(Lets talk about the basic commands of MySQL database (summary sharing)0),sex char(Lets talk about the basic commands of MySQL database (summary sharing)),age int(Lets talk about the basic commands of MySQL database (summary sharing)),passwd varchar(50), primary key (id));insert into star (id,name,sex,age,passwd) values(Lets talk about the basic commands of MySQL database (summary sharing),Lets talk about the basic commands of MySQL database (summary sharing)9;zzLets talk about the basic commands of MySQL database (summary sharing)9;,Lets talk about the basic commands of MySQL database (summary sharing)9;男Lets talk about the basic commands of MySQL database (summary sharing)9;,Lets talk about the basic commands of MySQL database (summary sharing)8,Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)45678);select * from star;

Copy after login

数据Lets talk about the basic commands of MySQL database (summary sharing)
补充密码Lets talk about the basic commands of MySQL database (summary sharing)

Lets talk about the basic commands of MySQL database (summary sharing)

查询数据记录talk about the basic commands of MySQL database (summary sharing)>

SELECT 字段名Lets talk about the basic commands of MySQL database (summary sharing),字段名Lets talk about the basic commands of MySQL database (summary sharing)[,...] FROM 表名 [WHERE 条件表达式];例：select * from star;select name,sex from star where id=Lets talk about the basic commands of MySQL database (summary sharing);

Copy after login

数据Lets talk about the basic commands of MySQL database (summary sharing)
以Lets talk about the basic commands of MySQL database (summary sharing)表方式竖向显示

Lets talk about the basic commands of MySQL database (summary sharing)

只显示头Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)

显示第Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)后的前Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)

Lets talk about the basic commands of MySQL database (summary sharing)

修改、更新数据表中的数据记录talk about the basic commands of MySQL database (summary sharing)>

UPDATE 表名 SET 字段名Lets talk about the basic commands of MySQL database (summary sharing)=字段值Lets talk about the basic commands of MySQL database (summary sharing)[,字段名Lets talk about the basic commands of MySQL database (summary sharing)=字段值Lets talk about the basic commands of MySQL database (summary sharing)] [WHERE 条件表达式];例：update star set age=Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing) where name=Lets talk about the basic commands of MySQL database (summary sharing)9;ppLets talk about the basic commands of MySQL database (summary sharing)9;;select * from star;

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

在数据表中删除指定的数据记录talk about the basic commands of MySQL database (summary sharing)>

DELETE FROM 表名 [WHERE 条件表达式];例：delete from star where id=6;select * from star;

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)

六、修改表名和表结构talk about the basic commands of MySQL database (summary sharing)>修改表名talk about the basic commands of MySQL database (summary sharing)>

ALTER TABLE 旧表名 RENAME 新表名;例：alter table star rename art;

Copy after login

改Lets talk about the basic commands of MySQL database (summary sharing)

扩展表结构（增加字段）talk about the basic commands of MySQL database (summary sharing)>

ALTER TABLE 表名 ADD address varchar(50) default Lets talk about the basic commands of MySQL database (summary sharing)9;地址不详Lets talk about the basic commands of MySQL database (summary sharing)9;;#default ‘地址不详’：表示此字段设置默认值 地址不详；可与 NOT NULL 配合使用例：alter table star add address varchar(50) default Lets talk about the basic commands of MySQL database (summary sharing)9;地址不详Lets talk about the basic commands of MySQL database (summary sharing)9;;

Copy after login

改Lets talk about the basic commands of MySQL database (summary sharing)

修改字段（Lets talk about the basic commands of MySQL database (summary sharing)）名，添加唯一键talk about the basic commands of MySQL database (summary sharing)>

ALTER TABLE 表名 CHANGE 旧Lets talk about the basic commands of MySQL database (summary sharing)名 新Lets talk about the basic commands of MySQL database (summary sharing)名 数据类型 [unique key];例：alter table star change name art_name varchar(Lets talk about the basic commands of MySQL database (summary sharing)0) unique key;select * from star;

Copy after login

改Lets talk about the basic commands of MySQL database (summary sharing)

删除字段talk about the basic commands of MySQL database (summary sharing)>

ALTER TABLE 表名 DROP 字段名;例：alter table star drop address;

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

扩展talk about the basic commands of MySQL database (summary sharing)>

CREATE DATABASE school;use school;create table if not exists info (id int(4) zerofill primary key auto_increment,				#指定主键的第二种方式name varchar(Lets talk about the basic commands of MySQL database (summary sharing)0) not null,cardid int(Lets talk about the basic commands of MySQL database (summary sharing)8) not null unique key,hobby varchar(50));#---------------命令解释--------------------------------#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建#int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例000Lets talk about the basic commands of MySQL database (summary sharing)#auto_increment：表示此字段为自增长字段，即每条记录自动递增Lets talk about the basic commands of MySQL database (summary sharing)，默认从Lets talk about the basic commands of MySQL database (summary sharing)开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次#unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键#not null：表示此字段不允许为NULL

Copy after login

扩展Lets talk about the basic commands of MySQL database (summary sharing)

七、数据表高级操作talk about the basic commands of MySQL database (summary sharing)>克隆表，将数据表的数据记录生成到新的表中talk about the basic commands of MySQL database (summary sharing)>方法一talk about the basic commands of MySQL database (summary sharing)>

create table testLets talk about the basic commands of MySQL database (summary sharing) like info;  #通过 LIKE 方法，复制 info 表结构生成 testLets talk about the basic commands of MySQL database (summary sharing) 表insert into testLets talk about the basic commands of MySQL database (summary sharing) select * from info;

Copy after login

复制Lets talk about the basic commands of MySQL database (summary sharing)

方法二talk about the basic commands of MySQL database (summary sharing)>

CREATE TABLE testLets talk about the basic commands of MySQL database (summary sharing) (SELECT * from info);show create table testLets talk about the basic commands of MySQL database (summary sharing)\G;					#获取数据表的表结构、索引等信息SELECT * from testLets talk about the basic commands of MySQL database (summary sharing);

Copy after login

复制Lets talk about the basic commands of MySQL database (summary sharing)

清空表，删除表内的所有数据talk about the basic commands of MySQL database (summary sharing)>方法一：记录ID未删除talk about the basic commands of MySQL database (summary sharing)>

delete from testLets talk about the basic commands of MySQL database (summary sharing);#DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一Lets talk about the basic commands of MySQL database (summary sharing)一Lets talk about the basic commands of MySQL database (summary sharing)的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。

Copy after login

删除Lets talk about the basic commands of MySQL database (summary sharing)

方法二：删除记录IDtalk about the basic commands of MySQL database (summary sharing)>

truncate table testLets talk about the basic commands of MySQL database (summary sharing);#TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 Lets talk about the basic commands of MySQL database (summary sharing) 开始重新记录。

Copy after login

除Lets talk about the basic commands of MySQL database (summary sharing)

创建临时表talk about the basic commands of MySQL database (summary sharing)>

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以可执Lets talk about the basic commands of MySQL database (summary sharing)增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

CREATE TEMPORARY TABLE 表名 (字段Lets talk about the basic commands of MySQL database (summary sharing) 数据类型,字段Lets talk about the basic commands of MySQL database (summary sharing) 数据类型[,...][,PRIMARY KEY (主键名)]);例：create temporary table testLets talk about the basic commands of MySQL database (summary sharing) (id int(4) zerofill primary key auto_increment,name varchar(Lets talk about the basic commands of MySQL database (summary sharing)0) not null,sex char(Lets talk about the basic commands of MySQL database (summary sharing)) not null);insert into testLets talk about the basic commands of MySQL database (summary sharing) values(Lets talk about the basic commands of MySQL database (summary sharing),Lets talk about the basic commands of MySQL database (summary sharing)9;asLets talk about the basic commands of MySQL database (summary sharing)9;,Lets talk about the basic commands of MySQL database (summary sharing)9;男Lets talk about the basic commands of MySQL database (summary sharing)9;);select * from testLets talk about the basic commands of MySQL database (summary sharing);show tables;quit;mysql -u root -pselect * from testLets talk about the basic commands of MySQL database (summary sharing);

Copy after login

除Lets talk about the basic commands of MySQL database (summary sharing)

创建外键约束，保证数据的完整性和一致性talk about the basic commands of MySQL database (summary sharing)>

外键的定义：如果同一个属性字段X在表一中是主键，而在表二中不是主键，则字段X称为表二的外键。

主键表和外键表的理解：
以公共关键字作主键的表为主键表（父表、主表）
以公共关键字作外键的表为外键表（从表、外表）

注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

#创建主表test4create table test4 (hobid int(4),hobname varchar(50));#创建从表test5create table test5 (id int(4) primary key auto_increment,name varchar(Lets talk about the basic commands of MySQL database (summary sharing)0),age int(Lets talk about the basic commands of MySQL database (summary sharing)),hobid int(4));#为主表test4添加一个主键约束，主键名建议以“PK_”开头alter table test4 add constraint PK_hobid primary key (hobid);#为从表test5表添加外键，并将test5表的hobid字段和test4表的hobid字段建立外键关联，外键名建议以“FK_”开头alter table test5 add constraint FK_hob foreign key (hobid) references test4 (hobid);desc test5;

Copy after login

外Lets talk about the basic commands of MySQL database (summary sharing)
插入新的数据记录时，要先主表再从表

insert into test4 values (Lets talk about the basic commands of MySQL database (summary sharing),Lets talk about the basic commands of MySQL database (summary sharing)9;readingLets talk about the basic commands of MySQL database (summary sharing)9;);insert into test5 values (Lets talk about the basic commands of MySQL database (summary sharing),Lets talk about the basic commands of MySQL database (summary sharing)9;adLets talk about the basic commands of MySQL database (summary sharing)9;,Lets talk about the basic commands of MySQL database (summary sharing)8,Lets talk about the basic commands of MySQL database (summary sharing));

Copy after login

外Lets talk about the basic commands of MySQL database (summary sharing)
删数据记录时，要先从表再主表，也就是说删除主键表时必须要先删除其他与之相关联的表

drop tables test5;drop tables test4;

Copy after login

外Lets talk about the basic commands of MySQL database (summary sharing)
查看和删除外键约束

show create table test5\G;alter table test5 drop foreign key FK_hob;alter table test5 drop key FK_hob;desc test5;

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

MySQL中6种常见的约束talk about the basic commands of MySQL database (summary sharing)>

主键约束（primary key）外键约束（foreign key）非空约束（not null）唯一性约束（unique [key|index]）默认值约束（default）自增约束（auto_increment）

Copy after login

八、数据库用户授权talk about the basic commands of MySQL database (summary sharing)>新建用户talk about the basic commands of MySQL database (summary sharing)>

USER Lets talk about the basic commands of MySQL database (summary sharing)9;用户名Lets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;来源地址Lets talk about the basic commands of MySQL database (summary sharing)9; [IDENTIFIED BY [PASSWORD] Lets talk about the basic commands of MySQL database (summary sharing)9;密码Lets talk about the basic commands of MySQL database (summary sharing)9;];#----------------------解释部分-----------------------------------------Lets talk about the basic commands of MySQL database (summary sharing)9;用户名Lets talk about the basic commands of MySQL database (summary sharing)9;：指定将创建的用户名Lets talk about the basic commands of MySQL database (summary sharing)9;来源地址Lets talk about the basic commands of MySQL database (summary sharing)9;：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，
          本地用户可用localhost，允许任意主机登录可用通配符%Lets talk about the basic commands of MySQL database (summary sharing)9;密码Lets talk about the basic commands of MySQL database (summary sharing)9;：若使用明文密码，直接输入Lets talk about the basic commands of MySQL database (summary sharing)9;密码Lets talk about the basic commands of MySQL database (summary sharing)9;，插入到数据库时由Mysql自动Lets talk about the basic commands of MySQL database (summary sharing)；
       若使用Lets talk about the basic commands of MySQL database (summary sharing)密码，需要先使用SELECT PASSWORD(Lets talk about the basic commands of MySQL database (summary sharing)9;密码Lets talk about the basic commands of MySQL database (summary sharing)9;); 获取密文，再在语句中添加 PASSWORD Lets talk about the basic commands of MySQL database (summary sharing)9;密文Lets talk about the basic commands of MySQL database (summary sharing)9;;
       若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）#----------------------------------------------------------------------例如：create user Lets talk about the basic commands of MySQL database (summary sharing)9;testLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9; IDENTIFIED BY Lets talk about the basic commands of MySQL database (summary sharing)9;Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)456Lets talk about the basic commands of MySQL database (summary sharing)9;;select password(Lets talk about the basic commands of MySQL database (summary sharing)9;Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)456Lets talk about the basic commands of MySQL database (summary sharing)9;);create user Lets talk about the basic commands of MySQL database (summary sharing)9;testLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9; IDENTIFIED BY PASSWORD Lets talk about the basic commands of MySQL database (summary sharing)9;*6BB48Lets talk about the basic commands of MySQL database (summary sharing)7EB74Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)9Lets talk about the basic commands of MySQL database (summary sharing)05EE4568DDA7DC67EDLets talk about the basic commands of MySQL database (summary sharing)CALets talk about the basic commands of MySQL database (summary sharing)AD9Lets talk about the basic commands of MySQL database (summary sharing)9;;

Copy after login

用户Lets talk about the basic commands of MySQL database (summary sharing)

查看用户信息talk about the basic commands of MySQL database (summary sharing)>

#创建后的用户保存在 mysql 数据库的 user 表里use mysql;select user,authentication_string,Host from user;

Copy after login

用户Lets talk about the basic commands of MySQL database (summary sharing)

重命名用户talk about the basic commands of MySQL database (summary sharing)>

rename user Lets talk about the basic commands of MySQL database (summary sharing)9;testLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9; to Lets talk about the basic commands of MySQL database (summary sharing)9;zzLets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9;;

Copy after login

用户Lets talk about the basic commands of MySQL database (summary sharing)

删除用户talk about the basic commands of MySQL database (summary sharing)>

drop user Lets talk about the basic commands of MySQL database (summary sharing)9;testLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9;;

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

修改当前登录用户密码talk about the basic commands of MySQL database (summary sharing)>

当前密码为abcLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)

set PASSWORD = PASSWORD(Lets talk about the basic commands of MySQL database (summary sharing)9;Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)456Lets talk about the basic commands of MySQL database (summary sharing)9;);

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

修改其他用户密码talk about the basic commands of MySQL database (summary sharing)>

set PASSWORD for Lets talk about the basic commands of MySQL database (summary sharing)9;zzLets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9; = PASSWORD(Lets talk about the basic commands of MySQL database (summary sharing)9;abcLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)9;);

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

忘记 root 密码的解决办法talk about the basic commands of MySQL database (summary sharing)>方法一：修改 /etc/my.cnf 配置文件，不使用密码直接登录到 mysqltalk about the basic commands of MySQL database (summary sharing)>

vim /etc/my.cnf[mysqld]skip-grant-tables					#添加，使登录mysql不使用授权表systemctl restart mysqld.servicemysql								#直接登录

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

使用 update 修改 root 密码，刷新数据库talk about the basic commands of MySQL database (summary sharing)>

update mysql.user set AUTHENTICATION_STRING = PASSWORD(Lets talk about the basic commands of MySQL database (summary sharing)9;abcLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)9;) where user=Lets talk about the basic commands of MySQL database (summary sharing)9;rootLets talk about the basic commands of MySQL database (summary sharing)9;;FLUSH PRIVILEGES;quit;mysql -u root -pabcLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除或注释，并重启 mysql 服务。

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

九、数据库用户授权talk about the basic commands of MySQL database (summary sharing)>授予权限talk about the basic commands of MySQL database (summary sharing)>

GRANT语句：专门用来设置数据库用户的访问权限。
当指定的用户名不存在时，GRANT语句将会创建新的用户；
当指定的用户名存在时，GRANT 语句用于修改用户信息。

格式GRANT 权限Lets talk about the basic commands of MySQL database (summary sharing)表 ON 数据库名.表名 TO Lets talk about the basic commands of MySQL database (summary sharing)9;用户名Lets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;来源地址Lets talk about the basic commands of MySQL database (summary sharing)9; [IDENTIFIED BY Lets talk about the basic commands of MySQL database (summary sharing)9;密码Lets talk about the basic commands of MySQL database (summary sharing)9;];#-------------------------------参数解释---------------------------------------------------------------------------权限Lets talk about the basic commands of MySQL database (summary sharing)表:  用于Lets talk about the basic commands of MySQL database (summary sharing)出授权使用的各种数据库操作，以逗号进Lets talk about the basic commands of MySQL database (summary sharing)分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执Lets talk about the basic commands of MySQL database (summary sharing)任何操作。
          数据库名.表名:  用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“test.*”表示授权操作的对象为 test数据库中的所有表。
              Lets talk about the basic commands of MySQL database (summary sharing)9;用户名Lets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;来源地址Lets talk about the basic commands of MySQL database (summary sharing)9;:  用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP 地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.test.com”、“Lets talk about the basic commands of MySQL database (summary sharing)9Lets talk about the basic commands of MySQL database (summary sharing).Lets talk about the basic commands of MySQL database (summary sharing)68.Lets talk about the basic commands of MySQL database (summary sharing)9.%”等。IDENTIFIED BY: 用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。例如：#允许用户 lili 在本地查询 kky 数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。GRANT select ON kky.* TO Lets talk about the basic commands of MySQL database (summary sharing)9;liliLets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9; IDENTIFIED BY Lets talk about the basic commands of MySQL database (summary sharing)9;abcLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)9;;flush privileges;quit;mysql -u lili -pabcLets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)use kky;show tables;select * from info;其他授权例子：#允许用户 pp 在所有终端远程连接 mysql ，并拥有所有权限。GRANT ALL PRIVILEGES（可不写） ON *.* TO Lets talk about the basic commands of MySQL database (summary sharing)9;ppLets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;%Lets talk about the basic commands of MySQL database (summary sharing)9; IDENTIFIED BY Lets talk about the basic commands of MySQL database (summary sharing)9;Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)Lets talk about the basic commands of MySQL database (summary sharing)456Lets talk about the basic commands of MySQL database (summary sharing)9;;

Copy after login

查Lets talk about the basic commands of MySQL database (summary sharing)

查看权限talk about the basic commands of MySQL database (summary sharing)>

SHOW GRANTS FOR 用户名@来源地址;例如：SHOW GRANTS FOR Lets talk about the basic commands of MySQL database (summary sharing)9;liliLets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9;;

Copy after login

查Lets talk about the basic commands of MySQL database (summary sharing)

撤销权限talk about the basic commands of MySQL database (summary sharing)>

REVOKE 权限Lets talk about the basic commands of MySQL database (summary sharing)表 ON 数据库名.表名 FROM 用户名@来源地址;例如：REVOKE SELECT ON kky.* FROM Lets talk about the basic commands of MySQL database (summary sharing)9;liliLets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9;;SHOW GRANTS FOR Lets talk about the basic commands of MySQL database (summary sharing)9;liliLets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9;;#USAGE权限只能用于数据库登陆，不能执Lets talk about the basic commands of MySQL database (summary sharing)任何操作；USAGE权限不能被回收，即 REVOKE 不能删除用户。flush privileges;REVOKE ALL ON *.* FROM Lets talk about the basic commands of MySQL database (summary sharing)9;liliLets talk about the basic commands of MySQL database (summary sharing)9;@Lets talk about the basic commands of MySQL database (summary sharing)9;localhostLets talk about the basic commands of MySQL database (summary sharing)9;;

Copy after login

Lets talk about the basic commands of MySQL database (summary sharing)

The above is the detailed content of Let's talk about the basic commands of MySQL database (summary sharing). For more information, please follow other related articles on the PHP Chinese website!