MySQL数据库基础操作命令有哪些

2025-03-05 技术教程

这篇文章给大家分享的是有关MySQL数据库基础操作命令有哪些的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

1、使用帮助信息

登陆数据库就不介绍了哦，比如说想做创建的数据库的操作，不知道命令，就可以查看帮助信息

mysql>helpcreate;Manyhelpitemsforyourrequestexist.Tomakeamorespecificrequest,pleasetype'help<item>',where<item>isoneofthefollowingtopics:CREATEDATABASE#最简单的创建命令CREATEEVENTCREATEFUNCTIONCREATEFUNCTIONUDFCREATEINDEXCREATELOGFILEGROUPCREATEPROCEDURECREATESERVERCREATETABLECREATETABLESPACECREATETRIGGERCREATEUSERCREATEVIEWSHOWSHOWCREATEDATABASESHOWCREATEEVENTSHOWCREATEFUNCTIONSHOWCREATEPROCEDURESHOWCREATETABLESPATIAL

2、创建、删除、查看数据库

mysql>createdatabasetest_data;#创建默认字符集的数据库（默认是拉丁字符集）QueryOK,1rowaffected(0.02sec)mysql>showdatabaseslike"test%";+------------------+|Database(test%)|+------------------+|test_data|+------------------+1rowsinset(0.00sec)

创建gbk字符集的数据库

mysql>createdatabasetest_gbkDEFAULTCHARACTERSETgbkCOLLATEgbk_chinese_ci;QueryOK,1rowaffected(0.04sec)mysql>showcreatedatabasetest_gbk;#查看创建数据库的语句+----------+----------------------------------------------+|Database|CreateDatabase|+----------+------------------------------------------------------------------+|test_gbk|CREATEDATABASE`test_gbk`/*!40100DEFAULTCHARACTERSETgbk*/|+----------+----------------------------------------------+1rowinset(0.00sec)

删除数据库

mysql>dropdatabasetest_data;QueryOK,0rowsaffected(0.07sec)mysql>showdatabases;+----------------------------+|Database|+----------------------------+|information_schema||test_gbk|+----------------------------+

3、连接数据库

4、创建用户、授权、收回权限

当数据库创建完成后，就需要创建用户，以供需要连接数据库的人员使用与操作数据库，不可能人人使用root登陆，所以权限设置也是很重要的

mysql>grantallontest_gbk.*to'testuser'@'localhost'identifiedby'123456';#创建用户并all权限给在test_gbk库所有表，密码‘123456’QueryOK,0rowsaffected(0.00sec)mysql>flushprivileges;#刷新权限，使权限生效QueryOK,0rowsaffected(0.00sec)mysql>showgrantsfor'testuser'@'localhost';#查看用户有哪些权限+-----------------------------------------------------------------------------------------------------------------+|Grantsfortestuser@localhost|+-----------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'testuser'@'localhost'IDENTIFIEDBYPASSWORD'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'||GRANTALLPRIVILEGESON`test_gbk`.*TO'testuser'@'localhost'|+-----------------------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)

收回权限

mysql>revokeinsert,update,select,deleteontest_gbk.*from'testuser'@'localhost';#将以上权限收回QueryOK,0rowsaffected(0.00sec)mysql>showgrantsfor'testuser'@'localhost';+----------------------------------------------------------------------------------------------------+|Grantsfortestuser@localhost|+----------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'testuser'@'localhost'IDENTIFIEDBYPASSWORD'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'||GRANTCREATE,DROP,REFERENCES,INDEX,ALTER,CREATETEMPORARYTABLES,LOCKTABLES,EXECUTE,CREATEVIEW,SHOWVIEW,CREATEROUTINE,ALTERROUTINE,EVENT,TRIGGERON`test_gbk`.*TO'testuser'@'localhost'|+------------------------------------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)

注：原来不知道all权限到底是哪些权限，采用这种方法之后，应该就清楚了

SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

5、创建、删除表

mysql>createtabletest(idint(4)notnull,namechar(20)notnull);#建表，并且建立两个字段QueryOK,0rowsaffected(0.06sec)mysql>showtables;#查看表+--------------------+|Tables_in_test_gbk|+--------------------+|test|+--------------------+1rowinset(0.00sec)mysql>desctest;#查看表结构+-------+----------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+----------+------+-----+---------+-------+|id|int(4)|NO||NULL|||name|char(20)|NO||NULL||+-------+----------+------+-----+---------+-------+2rowsinset(0.01sec)mysql>createtabletest1(idint(4)notnull,namechar(20)notnull);QueryOK,0rowsaffected(0.01sec)mysql>showtables;+------------------------+|Tables_in_test_gbk|+------------------------+|test||test1|+--------------------+2rowsinset(0.00sec)

删除表

mysql>droptablestest;QueryOK,0rowsaffected(0.00sec)mysql>showtables;+--------------------+|Tables_in_test_gbk|+--------------------+|test1|+--------------------+1rowinset(0.00sec)

查看建表

mysql>showcreatetabletest1\G***************************1.row***************************Table:test1CreateTable:CREATETABLE`test1`(`id`int(4)NOTNULL,`name`char(20)NOTNULL)ENGINE=MyISAMDEFAULTCHARSET=gbk1rowinset(0.00sec)

感谢各位的阅读！关于“MySQL数据库基础操作命令有哪些”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！