mysql常用命令
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql常用命令,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含12016字,纯文字阅读大概需要18分钟。
内容图文
![mysql常用命令](/upload/InfoBanner/zyjiaocheng/448/42f9ae1f9bc4495b828a97839d99d2aa.jpg)
要保证数据库中存储的数据与数据库编码一致,即数据的编码与character_set_database一致。
要保证 SELECT 的返回与程序的编码一致,即 character_set_results 与程序(PHP、Java等)编码一致。
要保证程序编码与浏览器编码一致,即程序编码与 一致。
五、数据库的连接和查询
#连接到某个具体的数据库 mysql> use mysql; Database changed #查询当前正在使用的数据库 mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) #查询数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.37 | +-----------+ 1 row in set (0.01 sec) #从数据库的表user中查询数据 mysql> select user from user; +------+ | user | +------+ | root | | root | +------+ 2 rows in set (0.00 sec)
从表user中删除数据
mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+-----------+-------------------------------------------+ 2 rows in set (0.00 sec) #我们删除host=‘localhost‘的数据 mysql> drop user ‘root‘@‘localhost‘; Query OK, 0 rows affected (0.02 sec) mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+-----------+-------------------------------------------+ 1 row in set (0.00 sec)
六、grant语句
测试all privileges有哪些权限
#创建test@localhost用户,并赋予所有权限 mysql> grant all privileges on *.* to test@localhost identified by ‘123456‘; Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password from mysql.user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | test | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+-----------+-------------------------------------------+ 2 rows in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) #查看test@localhost用户具体有哪些权限,但是这里还是显示的是all privileges mysql> show grants for test@localhost; +----------------------------------------------------------------------------------------------------------------------+ | Grants for test@localhost | +----------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ | +----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) #从test@localhost删除select权限 mysql> revoke select on *.* from test@‘localhost‘; Query OK, 0 rows affected (0.00 sec) mysql> show grants for test@localhost; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for test@localhost | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO ‘test‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) #revoke用法,可以通过help查询到 mysql> help revoke; Name: ‘REVOKE‘ Description: Syntax: REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ... REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... REVOKE PROXY ON user FROM user [, user] ... The REVOKE statement enables system administrators to revoke privileges from MySQL accounts. Each account name uses the format described in http://dev.mysql.com/doc/refman/5.5/en/account-names.html. For example: REVOKE INSERT ON *.* FROM ‘jeffrey‘@‘localhost‘; If you specify only the user name part of the account name, a host name part of ‘%‘ is used. For details on the levels at which privileges exist, the permissible priv_type and priv_level values, and the syntax for specifying users and passwords, see [HELP GRANT] To use the first REVOKE syntax, you must have the GRANT OPTION privilege, and you must have the privileges that you are revoking. To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named user or users: REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... To use this REVOKE syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database. URL: http://dev.mysql.com/doc/refman/5.5/en/revoke.html
七、表操作
#查询user表结构 mysql> desc user; #查询user表的创建语句 mysql> show create table user;
修改表数据
#user表中的数据 mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | test | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+-----------+-------------------------------------------+ 2 rows in set (0.00 sec) #修改test用户的信息 mysql> update user set host=‘127.0.0.1‘ where user=‘test‘; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #查看结果已改变 mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | test | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+-----------+-------------------------------------------+ 2 rows in set (0.00 sec)
删除表中的数据
#user表中的数据 mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | test | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+-----------+-------------------------------------------+ 2 rows in set (0.00 sec) #删除test用户 mysql> delete from user where user=‘test‘; Query OK, 1 row affected (0.00 sec) mysql> select user,host,password from user; +------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | 127.0.0.1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +------+-----------+-------------------------------------------+ 1 row in set (0.00 sec)
清空表
#将表中的数据清空 mysql> truncate table test;
修改表明和表字段
#在gitlab数据库中创建表goods mysql> use gitlab; Database changed #创建表goods mysql> create table goods(name char(10)); Query OK, 0 rows affected (0.04 sec) mysql> desc goods; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 1 row in set (0.00 sec) #添加表字段price mysql> alter table goods add price char(10); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc goods; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | price | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) #在name列的后面添加一个num列 mysql> alter table goods add num int(3) after name; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc goods; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | num | int(3) | YES | | NULL | | | price | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) #删除表字段num mysql> alter table goods drop num; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc goods; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | name | char(10) | YES | | NULL | | | price | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
修改表名
mysql> show tables; +------------------+ | Tables_in_gitlab | +------------------+ | goods | +------------------+ 1 row in set (0.00 sec) #将表goods重命名为price mysql> rename table goods to price; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +------------------+ | Tables_in_gitlab | +------------------+ | price | +------------------+ 1 row in set (0.00 sec) #再将表price重命名为address mysql> alter table price rename to address; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +------------------+ | Tables_in_gitlab | +------------------+ | address | +------------------+ 1 row in set (0.00 sec)
本文出自 “ly36843运维” 博客,请务必保留此出处http://ly36843.blog.51cto.com/3120113/1651650
mysql常用命令
标签:mysql常用命令
本文系统来源:http://ly36843.blog.51cto.com/3120113/1651650
内容总结
以上是互联网集市为您收集整理的mysql常用命令全部内容,希望文章能够帮你解决mysql常用命令所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。