MySQL常用命令和语句（一）

在学习MySQL的过程中经常遇到语法错误的问题，有些是自己理解错了，有些是版本的变化，踩了好多坑。在这里记录一下目前测试正确的命令和语句，相当于学习笔记。

环境：CentOS 7 + MySQL 8.0.11

参考资料传送门：

http://www.runoob.com/mysql/mysql-tutorial.html（MySQL教程）

https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-data-definition.html（MySQL官网文档）

http://www.runoob.com/sql/sql-tutorial.html（SQL语法教程）

Tips：SQL关键词不区分大小写，数据表里的字段名不区分大小写，而数据库名和数据表名区分大小写。

本文目录：

用户设置

数据库操作

数据表操作

表内字段操作

数据的增删改查

 

---

用户设置

创建用户并赋予权限（用户名和密码都是jack，testdb是数据库）：

mysql> CREATE USER 'jack'@'localhost' IDENTIFIED BY 'jack';
Query OK, 0 rows affected (0.10 sec)
 
mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'jack'@'localhost';
Query OK, 0 rows affected (0.15 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

查看用户拥有的权限：

mysql> SHOW GRANTS FOR jack@localhost;
+----------------------------------------------------------+
| Grants for jack@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `jack`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `jack`@`localhost` |
+----------------------------------------------------------+

---

数据库操作

创建数据库：

mysql> CREATE DATABASE testdb;

查看数据库列表：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+

删除数据库：

mysql> DROP DATABASE testdb;

选择数据库：

mysql> USE testdb;
Database changed

---

数据表操作

创建新表：

mysql> CREATE TABLE IF NOT EXISTS tbl_movies(
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> movie_id INT UNSIGNED NOT NULL DEFAULT '0',
    -> movie_name VARCHAR(40) NOT NULL,
    -> movie_score FLOAT UNSIGNED
    -> )DEFAULT CHARACTER SET = utf8;

查看数据库里的表：

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tb_movie         |
| tbl_movies       |
+------------------+

查看表结构（2种方法）：

1. describe table_name; 或 desc table_name;

   mysql> desc tbl_movies;
   +-------------+------------------+------+-----+---------+----------------+
   | Field       | Type             | Null | Key | Default | Extra          |
   +-------------+------------------+------+-----+---------+----------------+
   | id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
   | movie_id    | int(10) unsigned | NO   |     | 0       |                |
   | movie_name  | varchar(40)      | NO   |     | NULL    |                |
   | movie_score | float unsigned   | YES  |     | NULL    |                |
   +-------------+------------------+------+-----+---------+----------------+
   

2. mysql> show columns from tbl_movies;

   结果和1一样。

查看建表语句：

mysql> show create table tbl_movies;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_movies | CREATE TABLE `tbl_movies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `movie_id` int(10) unsigned NOT NULL DEFAULT '0',
  `movie_name` varchar(40) NOT NULL,
  `movie_score` float unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

删除表：

mysql> drop table tb_movie;

修改表名：

mysql> ALTER TABLE tbl_movies RENAME movie_tbl;

---

表内字段操作

删除字段：

mysql> ALTER TABLE tbl_movies DROP movie_score;

增加字段：

mysql> ALTER TABLE tbl_movies ADD (
    -> movie_score FLOAT UNSIGNED DEFAULT NULL,
    -> region VARCHAR(20),
    -> year YEAR,
    -> type VARCHAR(20),
    -> director VARCHAR(40),
    -> actor VARCHAR(60) COMMENT 'Starring')；

修改字段类型（字段名不变）：

mysql> ALTER TABLE tbl_movies MODIFY region VARCHAR(16);

修改字段名称和类型：

mysql> ALTER TABLE tbl_movies CHANGE type movie_type VARCHAR(16);

（type为原字段名，movie_type为新名称，VARCHAR(16)是新类型。）

修改字段默认值：

mysql> ALTER TABLE tbl_movies ALTER movie_score SET DEFAULT 0.0;

删除默认值：

mysql> ALTER TABLE tbl_movies ALTER movie_score DROP DEFAULT;

---

数据的增删改查

插入数据（Insert）：

mysql> INSERT INTO movie_tbl 
    -> (movie_id, movie_name, movie_score, region, year, movie_type, director, actor) 
    -> VALUES 
    -> (1,'肖申克的救赎','9.6','美国','1994','犯罪 剧情','弗兰克·德拉邦特 Frank Darabont','蒂姆·罗宾斯 Tim Robbins /...');

movie_tbl中有一个 id 字段是 auto_increment 的，所以没有填id对应的值。

查询数据（Select）：

mysql> SELECT id, movie_id, movie_name, MOVIE_SCORE FROM movie_tbl;
+----+----------+--------------------+-------------+
| id | movie_id | movie_name         | MOVIE_SCORE |
+----+----------+--------------------+-------------+
|  1 |        1 | 肖申克的救赎       |         9.6 |
+----+----------+--------------------+-------------+

修改数据（Update）：

mysql> UPDATE  movie_tbl SET movie_score=9.5, region='英国' WHERE id=1;

删除数据（Delete）：

mysql> DELETE FROM movie_tbl WHERE id=1;

如果没有WHERE子句，将删除表内所有记录。