一、创建库
1、创建一个库:
create database 数据库名;
create database 库名 character set 编码;
mysql> create database mydatabase01;
Query OK, 1 row affected (0.34 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydatabase01 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.03 sec) mysql> create database mydatabase02 character set gbk;
Query OK, 1 row affected (0.22 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydatabase01 |
| mydatabase02 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
查看编码:show create database mydatabase02;
mysql> show create database mydatabase01;
+--------------+-----------------------------------------------------------------------+
| Database | Create Database |
+--------------+-----------------------------------------------------------------------+
| mydatabase01 | CREATE DATABASE `mydatabase01` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database mydatabase02;
+--------------+----------------------------------------------------------------------+
| Database | Create Database |
+--------------+----------------------------------------------------------------------+
| mydatabase02 | CREATE DATABASE `mydatabase02` /*!40100 DEFAULT CHARACTER SET gbk */ |
+--------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
2、删除一个库: drop database 库名;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydatabase01 |
| mydatabase02 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec) mysql> drop database mydatabase02;
Query OK, 0 rows affected (0.54 sec) mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydatabase01 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
3、使用库;use 库名;
mysql> use mydatabase01;
Database changed
4、查看当前正在使用的库;
mysql> select database();
+--------------+
| database() |
+--------------+
| mydatabase01 |
+--------------+
1 row in set (0.00 sec)
二、对数据库表的操作;
1、创建表
create table 表名(
字段名 类型(长度) [约束],
字段名 类型(长度) [约束]
);
字符类型:varchar(n)
单表约束:
* 主键约束:primary key,要求被修饰的字段:唯一 和 非空
* 唯一约束: unique,要求被修饰的字段:唯一
* 非空约束: not null,要求被修饰的字段:非空约束
mysql> create table user(
-> uid int(32) primary key auto_increment,
-> uname varchar(32),
-> upassword varchar(32)
-> );
Query OK, 0 rows affected (0.94 sec)
2、查看数据库表:
mysql> show tables;
+------------------------+
| Tables_in_mydatabase01 |
+------------------------+
| user |
+------------------------+
1 row in set (0.03 sec)
3、查看表的结构
mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| uid | int(32) | NO | PRI | NULL | auto_increment |
| uname | varchar(32) | YES | | NULL | |
| upassword | varchar(32) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)
4、删除表;
mysql> drop table user;
Query OK, 0 rows affected (0.43 sec) mysql> show tables;
Empty set (0.00 sec)
5、修改表:
5.1、添加一列:alter table 表名 add 字段名 类型(长度) [约束]
mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| uid | int(32) | NO | PRI | NULL | auto_increment |
| uname | varchar(32) | YES | | NULL | |
| upassword | varchar(32) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec) mysql> alter table user add uinfo varchar(32) not null;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| uid | int(32) | NO | PRI | NULL | auto_increment |
| uname | varchar(32) | YES | | NULL | |
| upassword | varchar(32) | YES | | NULL | |
| uinfo | varchar(32) | NO | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
5.2 、修改列的类型(长度\约束):alter table 表名 modify 要修改的字段名 类型(长度)[约束]
mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| uid | int(32) | NO | PRI | NULL | auto_increment |
| uname | varchar(32) | YES | | NULL | |
| upassword | varchar(32) | YES | | NULL | |
| uinfo | varchar(32) | NO | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec) mysql> alter table user modify uinfo varchar(64) null;
Query OK, 0 rows affected (1.39 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| uid | int(32) | NO | PRI | NULL | auto_increment |
| uname | varchar(32) | YES | | NULL | |
| upassword | varchar(32) | YES | | NULL | |
| uinfo | varchar(64) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
5.3 、修改列的列名:alter table 表名 change 旧列名 新列名 类型(长度)[约束]
mysql> alter table user change uinfo info varchar(32) not null;
Query OK, 0 rows affected (1.26 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| uid | int(32) | NO | PRI | NULL | auto_increment |
| uname | varchar(32) | YES | | NULL | |
| upassword | varchar(32) | YES | | NULL | |
| info | varchar(32) | NO | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
5.4、删除表的列:alter table 表名 drop 列名
mysql> alter table user drop info;
Query OK, 0 rows affected (0.95 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| uid | int(32) | NO | PRI | NULL | auto_increment |
| uname | varchar(32) | YES | | NULL | |
| upassword | varchar(32) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
5.5 、修改表名:rename table 表名 to 新表名
mysql> show tables;
+------------------------+
| Tables_in_mydatabase01 |
+------------------------+
| user |
+------------------------+
1 row in set (0.00 sec) mysql> rename table user to tbl_user;
Query OK, 0 rows affected (0.42 sec) mysql> show tables;
+------------------------+
| Tables_in_mydatabase01 |
+------------------------+
| tbl_user |
+------------------------+
1 row in set (0.00 sec)
5.6 、修改表的字符集: alter table 表名 character set 编码
查看表当前的编码:
mysql> show create table tbl_user;
+----------+------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------+------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
| tbl_user | CREATE TABLE `tbl_user` (
`uid` int(32) NOT NULL AUTO_INCREMENT,
`uname` varchar(32) DEFAULT NULL,
`upassword` varchar(32) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table tbl_user character set gbk;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table tbl_user;
+----------+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------+
| tbl_user | CREATE TABLE `tbl_user` (
`uid` int(32) NOT NULL AUTO_INCREMENT,
`uname` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
`upassword` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------+
1 row in set (0.00 sec)
待续.....