<深入浅出MySQL>读书笔记之常用命令

帮助相关的

? 要查看帮助的命令. 如

mysql> ? create database;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...

create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.7/en/create-database.html


mysql>

查看支持的数据类型: ? data types

查看数据类型的信息及范围

mysql> ? int
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

查看与show命令相关的命令: ? show
查看与函数相关的帮助: ? functions

与DB相关的

* 创建数据库: `create dbname`
* 删除数据库: `drop database dbname`
* 选择数据库: `use dbname`
*

与表相关

维护表元信息的操作

显示当前DB的所有表: show tables;
查看表的定义: desc tbname;
查看表的建表语句: show create table tbname\G
查看表的状态: show table status like 'tbname'
删除表: drop table tbname;
修改字段名: ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]

注: change 和 modify 都可以修改表的定义. 不同的是change后面需要写两次列名. 但是change可以修改列名, 而modify不能.

添加表字段: ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]
修改表结构: alter table tbname modify column col_definition [first | after other_col_name]
删除表字段: alter table tbname drop column col_name

注: 关于表的字段顺序问题: 默认情况下, change和modify都不会改变原表定义的字段顺序, 新添加的列默认是加在表的最后位置.如果想修改列的顺序, 可以在修改字段的最后加上[first | after 其他字段名] first表示在其他字段名的前面, after表示其他字段名的后面.

修改表名: ALTER TABLE tablename RENAME [TO] new_tablename

与表索引相关

创建索引: alter table tbname add [unique] index index_name (col1 [asc|desc], col2...)
创建索引: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,. .) index_col_name: col_name [(length)] [ASC | DESC]:
删除索引: drop index index_name on tbname;

表的DML语句

插入一行: insert into tbname (col1, col2) values (value1, value2);
插入多行: insert into tbname (col1, col2) values (value1, value2), (value3, value4), (value5, value6);
更新一张表的记录: update tbname set field1=value1, field2=value2 [where condition...]
更新多张表的记录: update t1, t2 set t1.field=value1, t2.field=value2 [where condition]

注意：多表更新的语法更多地用在了根据一个表的字段来动态地更新另外一个表的字段。

删除一张表的记录: delete from tbname [where condition]
删除多张表的记录: DELETE t1,t2,…,tn FROM t1,t2,…,tn [WHERE CONDITION]

注意:

如果from后面的表名用别名，则delete后面也要用相应的别名，否则会提语法错误.
不管是单表还是多表，不加where条件将会把表的所有记录删除，所以操作时一定要小心。

查询: select * from tbname [where condition]
查询不重复记录select distinct * from tbname

with rollup: 用例子说明:

mysql> select hello, sum(id) from hello group by hello ;
+--------+---------+
| hello  | sum(id) |
+--------+---------+
| hello  |      18 |
| hello1 |       8 |
| hello2 |      10 |
+--------+---------+
3 rows in set (0.00 sec)

mysql> select hello, sum(id) from hello group by hello with rollup;
+--------+---------+
| hello  | sum(id) |
+--------+---------+
| hello  |      18 |
| hello1 |       8 |
| hello2 |      10 |
| NULL   |      36 |
+--------+---------+
4 rows in set (0.00 sec)

DCL 语句

MySQL里的用户, 是由用户名@主机名来唯一匹配的. 即相同用户名,但主机名不同, 是被认为是不同的用户.

mysql> create user 'yang'@'localhost' identified by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'yang'@'192.168.1.2' identified by '123';
Query OK, 0 rows affected (0.00 sec)

用本地用户yang@localhost登录时, 可以看到:

mysql> select current_user;
+----------------+
| current_user   |
+----------------+
| yang@localhost |
+----------------+
1 row in set (0.01 sec)

创建一个用户: CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
授权给一个用户: GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

查看元信息

以下的都是在information_schema数据库里的操作

查看拥有的数据库

schemata

mysql> select * from schemata;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | utf8                       | utf8_general_ci        | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.00 sec)

show databases; 命令,就是从这里取结果的.

查看所有的表

select * from tables;

show tables 命令就是从这里取出信息的.

查看所有列

select * from columns

show columns from tbname 命令就是从这里取出信息的.

表索引信息

select * from STATISTICS;

show index from tbname 命令就是从这里取出信息的.

Contents