MySQL的基本操作

MySQL的基本知识笔记

创建数据库 create databases;

查看已存在的数据库 show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

查看已经创建好的数据库的信息 show create database 数据库名称；
mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)


删除数据库 drop database 数据库名称；

选择使用某个数据库 use 数据库名称；
mysql> use test;
Database changed

查看数据库中的表 show tables;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| linlin         |
| tbk_book       |
| tbk_bookcolumn |
| tbk_borlist    |
| tbk_group      |
| tbk_member     |
| tbk_readcolumn |
| tbk_user       |
| test           |
| tspdvideodata  |
| vvideodata     |
+----------------+
11 rows in set (0.00 sec)

创建数据表 create table 表名
           (
               字段名1 数据类型[完整约束条件],
               字段名2 数据类型[完整约束条件],
               。。。。。。
               
               字段名n 数据类型[完整约束条件]
           );
mysql>  create table linlin
    -> (
    ->     id int(11),
    ->     name varchar(20),
    ->     grade float
    -> );
Query OK, 0 rows affected (0.02 sec)

删除数据表 drop table 数据表名称；
mysql> drop table linlin;
Query OK, 0 rows affected (0.01 sec)

查看数据表 show create table 数据表名称；
mysql> show create table linlin;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                              |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| linlin | CREATE TABLE `linlin` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `grade` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看数据表的字段信息 describe 表名；
mysql> describe linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| grade | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

也可以简写为 desc 表名；
mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| grade | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改数据表名 alter table 旧表名 rename 新表名；
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| linlin         |
| tbk_book       |
| tbk_bookcolumn |
| tbk_borlist    |
| tbk_group      |
| tbk_member     |
| tbk_readcolumn |
| tbk_user       |
| test           |
| tspdvideodata  |
| vvideodata     |
+----------------+
11 rows in set (0.00 sec)

mysql> alter table test rename apple;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| apple          |
| linlin         |
| tbk_book       |
| tbk_bookcolumn |
| tbk_borlist    |
| tbk_group      |
| tbk_member     |
| tbk_readcolumn |
| tbk_user       |
| tspdvideodata  |
| vvideodata     |
+----------------+
11 rows in set (0.00 sec)

修改字段名 alter table 表名 change 旧字段名 新字段名 新数据类型；
mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| grade | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table linlin change grade score float;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改字段数据类型 alter table 表名 modify 字段名 数据类型；
mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table linlin modify id int(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

添加字段 alter table 表名 add 新字段名 数据类型；
mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table linlin add class int(4);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
| class | int(4)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

删除字段 alter table 表名 drop 字段名；
mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
| class | int(4)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table linlin drop class;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改字段的排列位置 alter table 表名 modify 字段名1 数据类型 first；/after 字段名2；
first 表示将此字段放在第一位，after表示将字段1放在字段2后面
mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table linlin modify score float first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| score | float       | YES  |     | NULL    |       |
| id    | int(20)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table linlin modify name varchar(20) after score;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc linlin;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| score | float       | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| id    | int(20)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)