1.创建数据库:
create database dbname
例:
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
2.查看有哪些数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
+--------------------+
5 rows in set (0.01 sec)
- information_schema:主要存储了系统中的一些数据库对象信息,比如用户表信息、列信息、权限信息、字符集信息、分区信息等。
- mysql:存储了系统的用户权限信息。
- test:系统自动创建的测试数据库,任何用户不都可以使用。
3.进入数据库:
mysql> use test1;
Database changed
4.查看数据库中的表:
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
5.删除数据库:
drop database dbname;
例:
mysql> drop database test1;
Query OK, 0 rows affected (0.00 sec)
6.创建表
create table tablename (
column_name_1 column_type_1 constraints,
column_name_2 column_type_2 constraints,
.....
column_name_n column_type_n constraints)
例:
mysql> create table emp(
-> ename varchar(10),
-> hiredate date,
-> sal decimal(10,2),
-> deptno int(2));
Query OK, 0 rows affected (0.02 sec)
7.查看表的定义
desc tablename
例:
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
8.查看创建表语句
mysql> show create table empG;
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
9.删除表
drop table tablename
例:
mysql> drop table ca1;
Query OK, 0 rows affected (0.00 sec)
10.修改表
1.修改表类型:
alter table tablename modify[column] column_definition[first|after col_name]
例:
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2.增加表字段
alter table tablename add [column]column_definition[first|after col_name]
例:
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3.删除表字段
alter table tablename drop [column]col_name
例:
mysql> alter table emp drop column age;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.字段改名
alter table tablename change[column] old_col_name column_definition [first|after col_name]
例 :
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> alter table emp change deptno deptno1 int(4);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno1 | int(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
注意:
change和modify都可以修改表的定义,不同的是chenge后面需要写两次列名,不方便。但是change的优点是可以修改列名称,modify则不能。
5.修改字段排列顺序
将字段增加在某字段之后
mysql> alter table emp add birth date after ename;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno1 | int(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
将字段增加在最前面
mysql> alter table emp add age int(6) first;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age | int(6) | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno1 | int(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
6.更改表名
alter table tablename rename [to] new_tablename
例:
mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| emp1 |
+-----------------+
1 row in set (0.00 sec)