2.数据库和表结构的操作

作者: 王洛书 | 来源:发表于2018-01-27 10:23 被阅读0次

2.数据库和表结构的操作
django
SQL之DDL
DDL基本操作
Mysql数据库性能影响因素
PostgreSQL 常用操作
binlog——逻辑复制的基础
2021-01-29 DDL_操作数据库_创建&查询
MySQL的基本操作
基础篇 - 数据库及表达修改和删除

1.MySQL分类

* 数据库语言：DDL（Data Defintion Language）,用于定义数据库对象：库database，表table，列column。
* 数据库操作语言：DML（Data Manipulation Language），用来对数据库中表的记录用来更新。
* 数库查询语言：DQL（Data Query Language），用来查询数据库中表的记录。
* 数据控制语言：DCL（Data Control Language），用来定义数据库的访问权限和安全级别，及创建用户。

2.数据库操作

①创建一个数据库

mysql> create database tiger;
Query OK, 1 row affected (0.00 sec)

②创建一个数据库，并指定编码标准

mysql> create database tiger_red character set gbk;
Query OK, 1 row affected (0.27 sec)

③查看已有的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tiger              |
| tiger_red              |
+--------------------+
5 rows in set (0.00 sec)

④使用tiger数据库

mysql> use tiger;
Database changed

⑤查看库的结构

mysql> create database tiger ;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| tiger    | CREATE DATABASE `tiger` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> show create database tiger_red;
+-----------+-------------------------------------------------------------------+
| Database  | Create Database                                                   |
+-----------+-------------------------------------------------------------------+
| tiger_red | CREATE DATABASE `tiger_red` /*!40100 DEFAULT CHARACTER SET gbk */ |
+-----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

⑥删除数据库

mysql> drop database tiger_red;
Query OK, 0 rows affected (0.27 sec)

⑦查看正在使用的数据库

mysql> SELECT database();
+------------+
| database() |
+------------+
| tiger      |
+------------+
1 row in set (0.00 sec)

3.表操作

①创建表

格式
create table tableName(
      title type(length) [constraint],
      字段名 类型(长度) [约束]
);
example:
mysql> create table user(
mysql> uid int(32) primary key auto_increment,
mysql> uname varchar(32),
mysql> upassword varchar(32)
mysql> );
Query OK, 0 rows affected (0.44 sec)

②查看表

mysql> show tables;
+-----------------+
| Tables_in_tiger |
+-----------------+
| user            |
+-----------------+
1 row in set (0.00 sec)

③查看表的结构

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> drop table user;
Query OK, 0 rows affected (0.36 sec)

mysql> show tables;
Empty set (0.00 sec)

⑤修改表

5.1添加一列
ALTER table tableName add title type(length) constraint;
example:
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修改列的类型（length，constraint）
ALTER table tableName modify title 被修改的type(length) [constraint]
example:
mysql> ALTER table user modify uinfo varchar(120) null;
Query OK, 0 rows affected (0.52 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(100) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

5.3修改列的列名
ALTER table user change oldColumnName newColumnName type(length) [constraint]
example:
mysql> ALTER table user change uinfo udesc varchar(32) not null;
Query OK, 0 rows affected (0.60 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    |                |
| udesc     | varchar(32) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

5.4删除表的一列
ALTER table tableName drop columnName
example:
mysql> ALTER table user drop udesc;
Query OK, 0 rows affected (0.28 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 tableName to newTableName
example:
mysql> RENAME table user to newUser;
Query OK, 0 rows affected (0.09 sec)

mysql> desc user;
ERROR 1146 (42S02): Table 'tiger.user' doesn't exist
mysql> desc newUser;
+-----------+-------------+------+-----+---------+----------------+
| 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.6修改表的字符集
ALTER table tableName character set coding
example:
mysql> show create table newUser;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                               |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| newUser | CREATE TABLE `newuser` (
  `uid` int(32) NOT NULL AUTO_INCREMENT,
  `uname` varchar(32) DEFAULT NULL,
  `upassword` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER table newUser character set gbk;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table newUser;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                      |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| newUser | CREATE TABLE `newuser` (
  `uid` int(32) NOT NULL AUTO_INCREMENT,
  `uname` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
  `upassword` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)