MySQL(三)

时间:2022-12-15 16:43:44

一、创建库

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)

待续.....