MySQL（三）
一、创建库
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)
待续.....
