MySQL命令讲析

本文主要给大家介绍MySQL命令讲析，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续在开发云行业资讯里面关注我的更新文章的。命令例子说明描述show显示数据库、表、列的信息和云服务器状态SHOW has many forms that provide information about databases, tables,columns, or status information about the server.mysql> show databases;+——————–+| Database |+——————–+| information_schema || mysql || performance_schema || test |+——————–+4 rows in set (0.00 sec)显示所有数据库mysql> show grants for oldboy@’localhost’; +—————————————————————————————————————+| Grants for oldboy@localhost |+—————————————————————————————————————+| GRANT USAGE ON *.* TO ‘oldboy’@’localhost’ IDENTIFIED BY PASSWORD ‘*FE28814B4+—————————————————————————————————————+1 row in set (0.00 sec) 显示用户权限USAGE表示没有权限mysql> show databases like ‘%my%’;+—————–+| Database (%my%) |+—————–+| mysql |+—————–+1 row in set (0.00 sec)模糊匹配mysql> show create database oldboy;显示创建数据库的语句show CHARACTER SET;显示gbk的校对规则mysql> show tables; +——————+| Tables_in_oldboy |+——————+| student |+——————+1 row in set (0.00 sec)#查看表mysql> show create table studentG *************************** 1. row *************************** Table: studentCreate Table: CREATE TABLE `student` ( `id` int(4) NOT NULL, `name` char(20) NOT NULL, `are` tinyint(2) NOT NULL DEFAULT ‘0’, `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) #查看创建表语句mysql> show index from studentG *************************** 1. row *************************** Table: student Non_unique: 0 Key_name: PRIMARY #索引类型 Seq_in_index: 1 Column_name: id #索引列名称 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: #查看表的索引命令例子中文说明英文描述selectselect user,host from mysql.user;查询mysql库的user表的用户mysql> select database(); +————+| database() |+————+| NULL |+————+1 row in set (0.00 sec) 查看当前数据库mysql> select user(); +—————-+| user() |+—————-+| root@localhost |+—————-+1 row in set (0.01 sec) 查看当前用户mysql> select version(); +————+| version() |+————+| 5.5.32-log |+————+1 row in set (0.00 sec)查看数据库版本mysql> select now(); +———————+| now() |+———————+| 2018-04-10 22:25:11 |+———————+1 row in set (0.00 sec)查看当前时间mysql> select count(distinct user) from mysql.user; +———————-+| count(distinct user) |+———————-+| 7 |+———————-+1 row in set (0.01 sec) 统计表记录的唯一值的命令mysql> select * from test; +—-+——–+| id | name |+—-+——–+| 1 | oldboy |+—-+——–+1 row in set (0.00 sec)查看表所有数据mysql> select * from test limit 2; +—-+——–+| id | name |+—-+——–+| 1 | oldboy || 2 | old111 |+—-+——–+2 rows in set (0.00 sec)查询表的前2行mysql> select * from test limit 2,3; +—-+——+| id | name |+—-+——+| 3 | kk || 4 | zuma |+—-+——+2 rows in set (0.00 sec) 查询表的2到3行mysql> select * from test where id=1; +—-+——–+| id | name |+—-+——–+| 1 | oldboy |+—-+——–+1 row in set (0.00 sec) 按条件查询表的数据mysql> select * from test where id>2; +—-+——+| id | name |+—-+——+| 3 | kk || 4 | zuma |+—-+——+2 rows in set (0.00 sec) 条件范围查询表数据mysql> select * from test where id>2 and id +—-+——+| id | name |+—-+——+| 3 | kk || 4 | zuma |+—-+——+2 rows in set (0.00 sec) 条件范围查询表数据mysql> select id,name from test where id>1 and idQuery OK, 3 rows affected (0.00 sec) #导出表数据命令例子中文说明英文描述explainmysql> explain select * from test where name=’oldboy’G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: refpossible_keys: index_name #已使用，没使用为NULL key: index_name #已使用 key_len: 60 ref: const rows: 1 Extra: Using where; Using index1 row in set (0.00 sec) #查看表字段是否使用索引[root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e “show full processlist;”|egrep -vi “sleep”Id User Host db Command Time State Info7 root localhost NULL Query 0 NULL show full process抓取慢查询语句命令例子中文说明英文描述descmysql> desc student; +——-+————-+——+—–+———+——-+| Field | Type | Null | Key | Default | Extra |+——-+————-+——+—–+———+——-+| id | int(4) | NO | | NULL | || name | char(20) | NO | | NULL | || are | tinyint(2) | NO | | 0 | || dept | varchar(16) | YES | | NULL | |+——-+————-+——+—–+———+——-+4 rows in set (0.00 sec)查看表结构命令例子中文说明英文描述updateUPDATE语法可以用新值更新原有表行中的各列.SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。For the single-table syntax, the UPDATE statement updates columns ofexisting rows in the named table with ne开发云主机域名w values. The SET clauseindicates which columns to modify and the values they should be given.Each value can be given as an expression, or the keyword DEFAULT to seta column explicitly to its default value. The WHERE clause, if given,specifies the conditions that identify which rows to update. With noWHERE clause, all rows are updated. If the ORDER BY clause isspecified, the rows are updated in the order that is specified. TheLIMIT clause places a limit on the number of rows that can be updatedmysql> update mysql.user SET password=PASSWORD(“oldboy123”) WHERE user=’root’ and host=’localhost’; update(修改) mysql.user(mysql数据库的user表) SET(指定哪列？改password列，要给予哪些值？PASSWORD值) password=PASSWORD(“oldboy123”) （改成oldboy123,PASSWORD必须加因为这个是加密的）WHERE(对谁进行更改？) user=’root’（对root用户） and host=’localhost’;(和主机localhost修改)mysql> update test set name=’inct’ where id=3; Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0 #修改id为3的name，修改表一定要加条件mysql> update test set name=’inct’; Query OK, 4 rows affected (0.00 sec)Rows matched: 5 Changed: 4 Warnings: 0 #如果不加条件是全部修改，这种问题是致命的。命令例子中文说明英文描述alter修改语句mysql> alter table student change id id int primary key auto_increment; #修改主键索引mysql> alter table student drop index index_name; Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0#删除普通索引mysql> alter table student add index index_name(name);Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0修改表的普通索引命令例子中文说明英文描述dropmysql> drop user “”@localhost;Query OK, 0 rows affected (0.00 sec)删除用户mysql> drop database oldboy_gbk; Query OK, 0 rows affected (0.01 sec)删除数据库drop table student; 删除之前建的表命令例子中文说明英文描述delete删除语句For the single-table syntax, the DELETE statement deletes rows fromtbl_name and returns a count of the number of deleted rows.mysql> delete from test where id=1; Query OK, 1 row affected (0.00 sec)#删除表指定的ID命令例子中文说明英文描述create创建语句mysql> create database oldboy; 创建一个oldboy的数据库mysql> create database oldboy_gbk CHARACTER SET gbk COLLATE gbk_chinese_ci;创建gbk数据库mysql> create user ‘oldboy’@’localhost’ identified by ‘oldboy123’; Query OK, 0 rows affected (0.01 sec) #创建用户create table student( id int(4) not null,name char(20) not null,are tinyint(2) NOT NULL default ‘0’,dept varchar(16) default NULL);创建表mysql> create index index_dept on student(dept(8));Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0创建表某列的前8个字节的普通索引mysql> create index index_name_dept on student(name,dept); Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0创建表的联合索引mysql> create index index_name_dept on student(name(8),dept(10));Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0创建表的联合索引前N个字符mysql> create unique index uni_ind_name on student(name); Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0 #创建表唯一索引命令例子中文说明英文描述grant授权MYSQL用户权限和指定特殊权限The GRANT statement grants privileges to MySQL user accounts. GRANTalso serves to specify other account characteristics such as use ofsecure connections and limits on access to server resources. To useGRANT, you must have the GRANT OPTION privilege, and you must have theprivileges that you are granting.mysql> grant all on oldboy.* to oldboy@’localhost’; Query OK, 0 rows affected (0.01 sec) #授权给oldboy用户mysql> grant all on oldboy.* to ‘oldboy11’@’localhost’ identified by ‘oldboy123’;Query OK, 0 rows affected (0.00 sec)创建用户和密码并且授权命令例子中文说明英文描述insert插入语句INSERT inserts new rows into an existing table. The INSERT … VALUESand INSERT … SET forms of the statement insert rows based onexplicitly specified values. The INSERT … SELECT form inserts rowsselected from another table or tables. INSERT … SELECT is discussedfurther in [HELP INSERT SELECT].mysql> insert into test (id,name) values(1,’oldboy’);Query OK, 1 row affected (0.00 sec)插入id=1 name=oldboy的内容到test表中命令例子中文说明英文描述REVOKE撤销权限The REVOKE statement enables system administrators to revoke privilegesfrom MySQL accounts. Each account name uses the format described inmysql> REVOKE INSERT ON oldboy.* FROM ‘oldboy’@’localhost’; 撤销插入权限命令例子中文说明英文描述truncatemysql> truncate table test; #清空表数据命令例子中文说明英文描述 flush刷新语句The FLUSH statement has several variant forms that clear or reloadvarious internal caches, flush tables, or acquire locks. To executeFLUSH, you must have the RELOAD privilege. Specific flush options mightrequire additional privileges, as described later.mysql> flush privileges;命令例子中文说明英文描述use 切换数据库相等于CDmysql> use oldboy Database changed切换到oldboy数据库看了以上关于MySQL命令讲析，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。

相关推荐: MySQL query_cache_type的DEMAND参数介绍和使用举例

Query Cache存储SELECT语句及其产生的数据结果，特别适用于表数据变化不是很频繁的场景，例如一些静态页面， 或者页面中的某块不经常发生 变化的信息。如果此表上有任何写表操作发生，那么和这个表相关的所有缓存都将失效。 由于Query Cache需要缓…