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
转载请注明出处
