这是学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 student\G *************************** 1. row *************************** Table: student Create 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=utf8 1 row in set (0.00 sec) | #查看创建表语句 | ||
mysql> show index from student\G *************************** 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: | #查看表的索引 |
命令 | 例子 | 中文说明 | 英文描述 |
select | |||
select 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<5; +----+------+ | id | name | +----+------+ | 3 | kk | | 4 | zuma | +----+------+ 2 rows in set (0.00 sec) | 条件范围查询表数据 | ||
mysql> select id,name from test where id>1 and id<5 order by id desc into outfile '/tmp/a.txt'; Query OK, 3 rows affected (0.00 sec) | #导出表数据 |
命令 | 例子 | 中文说明 | 英文描述 |
explain | |||
mysql> explain select * from test where name='oldboy'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ref possible_keys: index_name #已使用,没使用为NULL key: index_name #已使用 key_len: 60 ref: const rows: 1 Extra: Using where; Using index 1 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 Info 7 root localhost NULL Query 0 NULL show full process | 抓取慢查询语句 |
命令 | 例子 | 中文说明 | 英文描述 |
desc | mysql> 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) | 查看表结构 |
命令 | 例子 | 中文说明 | 英文描述 |
update | UPDATE语法可以用新值更新原有表行中的各列.SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句,则更新所有的行。如果指定了ORDER BY子句,则按照被指定的顺序对行进行更新。 LIMIT子句用于给定一个限值,限制可以被更新的行的数目。 | For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value. The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated | |
mysql> 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 | 修改表的普通索引 |
命令 | 例子 | 中文说明 | 英文描述 |
drop | |||
mysql> 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 from tbl_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. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges 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 ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. INSERT ... SELECT is discussed further 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 privileges from MySQL accounts. Each account name uses the format described in | |
mysql> REVOKE INSERT ON oldboy.* FROM 'oldboy'@'localhost'; | 撤销插入权限 |
命令 | 例子 | 中文说明 | 英文描述 |
truncate | mysql> truncate table test; | #清空表数据 |
命令 | 例子 | 中文说明 | 英文描述 |
flush | 刷新语句 | The FLUSH statement has several variant forms that clear or reload various internal caches, flush tables, or acquire locks. To execute FLUSH, you must have the RELOAD privilege. Specific flush options might require additional privileges, as described later. | |
mysql> flush privileges; |
命令 | 例子 | 中文说明 | 英文描述 |
use | 切换数据库相等于CD | ||
mysql> use oldboy Database changed | 切换到oldboy数据库 |
转载于:https://blog.51cto.com/ygtq666/2108124