MySQL常用命令集合
基本命令
一、数据库基本操作
- 查看所有数据库
show databases ;
- 创建数据库-Book
create database Book ;
- 删除数据库-Book
drop database Book ;
- 查看默认存储引擎
show variables like 'storage_engine' ;
二、数据表基本操作
use test_db ;
MariaDB [test_db]> create table tb_emp1
-> (
-> id int(11),
-> name varchar(25),
-> deptId int(11),
-> salary float
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [test_db]> show tables ;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1 |
+-------------------+
1 row in set (0.00 sec)
-### 创建数据表的同时定义主键
- 语法:字段名 数据类型 primary key [默认值]
MariaDB [test_db]> create table tb_emp2
-> (
-> id int(11) primary key,
-> name varchar(25),
-> deptId int(11),
-> salary float
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [test_db]> create table tb_emp3
-> (
-> id int(11),
-> name varchar(25),
-> depId int(11),
-> salary float,
-> primary key(id)
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [test_db]> create table tb_emp4
-> (
-> name varchar(25),
-> deptId int(11),
-> salary float,
-> primary key(name,deptId)
-> );
Query OK, 0 rows affected (0.00 sec)
-
外键约束
- 语法:[constraint <外键名> ]foreign key 字段名1 [,字段名2] peferences <主表名> 主键列1 [,主键列2]
- 外键名:为定义的外键约束的名称,一个表中不能有相同名称的外键
- 字段名:表示子表需要添加外键约束的 字段列
- 主表名:即被子表外键所依赖的表的名称
- 主键列:表示主要中定义的主键列,或都列组合
- eg:定义数据表tb_emp5,在tb_emp5创建外键约束
create table tb_dept1
-> (
-> id int(11) primary key,
-> name varchar(22) not null,
-> location varchar(50)
-> );
Query OK, 0 rows affected (0.01 sec)
create table tb_emp5
-> (
-> id int(11) primary key ,
-> name varchar(25) ,
-> deptId int(11) ,
-> salary float ,
-> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
-> );
Query OK, 0 rows affected (0.01 sec)
create table tab_emp6
-> (
-> id int(11) primary key ,
-> name varchar(25) not null ,
-> deptId int(11),
-> salary float
-> );
Query OK, 0 rows affected (0.00 sec)
- 使用唯一性约束
- 语法:字段名 数据类型 unique
create table tb_dept2
-> (
-> id int(11) primary key ,
-> name varchar(22) unique ,
-> location varchar(50)
-> );
Query OK, 0 rows affected (0.01 sec)
- 使用默认约束
语法:字段名 数据类型 default 默认值
create table tb_emp7
-> (
-> id int(11) primary key ,
-> name varchar(25) not null ,
-> deptId int(11) default 111 ,
-> salary float
-> );
Query OK, 0 rows affected (0.00 sec)
- 设置表的属性值自动增加
语法:字段名 数据类型 auto_increment
create table tb_emp8
-> (
-> id int(11) primary key auto_increment ,
-> name varchar(25) not null ,
-> deptId int(11) ,
-> salary float
-> );
Query OK, 0 rows affected (0.01 sec)
insert into tb_emp8 (name,salary) values('lucy',1000),('lura',1200),('kevin',1500);
select * from tb_emp8;
+----+-------+--------+--------+
| id | name | deptId | salary |
+----+-------+--------+--------+
| 1 | lucy | NULL | 1000 |
| 2 | lura | NULL | 1200 |
| 3 | kevin | NULL | 1500 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)
- 查看表的基本结构-desc或desccribe
desc tb_emp8;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | NO | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
注:NULL:可以存储空值
key:表示是否已编制索引。
PRI:表示主键
UNI:表示唯一值
MUL:表示可重复出现
Default:是否有默认值及值是多少
Extra:可获取的与给定列有关的附加信息
查看表的详细信息
show create table tb_emp8 ;
三、修改数据表
- 修改表名
语法:alter table <旧表名> rename [to] <新表名>
alter table tab_emp6 rename tb_emp6 ;
- 修改字段数据类型
- 语法:alter table <表名> modify <字段名> <数据类型>
alter table tb_dept1 modify name varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 修改字段名
- 语法:alter tbale <表名> change <旧字段名> <新字段名> <新数据类型>
alter table tb_dept1 change location loc varchar(50);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 添加字段
- 语法:alter table <表名> add <新字段名> <数据类型> [约束条件] [first | after 已存在的字段名] ;
alter table tb_dept1 add managerId int(10) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test_db]> desc tb_dept1 ;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
- 添加有完整性约束的字段
MariaDB [test_db]>alter table tb_dept1 add column1 varchar(12) not null ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test_db]> desc tb_dept1 ;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 在表的第一列添加一个字段
alter table tb_dept1 add column2 int(11) first ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 在name列后添加一个int类型字段
alter table tb_dept1 add column3 int(11) after name ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 删除字段
- alter table <表名> drop <字段名> ;
alter table tb_dept1 drop column2 ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 修改字段排列位置
MariaDB [test_db]> desc tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
MariaDB [test_db]> alter table tb_dept1 modify column1 varchar(12) first ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test_db]> desc tb_dept1 ;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column1 | varchar(12) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
- 修改字段到表的指定列之后
MariaDB [test_db]> alter table tb_dept1 modify column1 varchar(12) after loc ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test_db]> desc tb_dept1 ;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| column1 | varchar(12) | YES | | NULL | |
| managerId | int(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
- 更换表的存储引擎
- 语法:alter table <表名> engine=<更改后的存储引擎名>
alter table tb_detpment3 engine=myisam;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test_db]> show create table tb_detpment3 \G
*************************** 1. row ***************************
Table: tb_detpment3
Create Table: CREATE TABLE `tb_detpment3` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
- 删除表的外键约束
- 语法:alter table <表名> drop foreign key <外键约束名>
MariaDB [test_db]> show create table tb_emp9 \G
*************************** 1. row ***************************
Table: tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept` (`deptId`),
CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MariaDB [test_db]> alter table tb_emp9 drop foreign key fk_emp_dept;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test_db]> show create table tb_emp9 \G
*************************** 1. row ***************************
Table: tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
- 删除没有关联的表
- drop table [if exists] 表1 ,表2,…,表n;
drop table if exists tb_dept2 ;
Query OK, 0 rows affected (0.00 sec)
- 删除有关联的表
MariaDB [test_db]> drop table tb_dept2;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
MariaDB [test_db]> alter table tb_emp drop foreign key fk_emp_dept;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test_db]> drop table tb_dept2 ;
Query OK, 0 rows affected (0.00 sec)