关系型数据库(RDBMS)
二维数据库
通过表结构存储数据的叫关系型数据库
create database if not exists test;
mysql> select database(); 查询当前数据库
+------------+
| database() |
+------------+
| itcast |
+------------+
1 row in set (0.00 sec)
DDL-表操作-查询
mysql> desc table tb_user;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> show create table tb_user; #查看表详细信息
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_user | CREATE TABLE `tb_user` (
`id` int DEFAULT NULL COMMENT '编号',
`name` varchar(50) DEFAULT NULL COMMENT '姓名',
`age` int DEFAULT NULL COMMENT '年龄',
`gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
DDL-表操作-创建
create table tb_user( id int comment "编号", name varchar(50) comment '姓名', age int comment '年龄', gender varchar(1) comment '性别' ) comment '用户表';
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| tb_user |
+------------------+
1 row in set (0.00 sec)
数值类型
字符串类型
varchar(10) 存储一个字符就占用一个字符的空间(变长字符串)
性能比较差
char(10) 存储一个字符也占用10个字符的空间(定长字符串)
性能好
mysql> create table emp(id int,workno varchar(10),name varchar(10),gendef char(1),age tinyint unsigned, idcard char(18),entrydate date);
Query OK, 0 rows affected (0.02 sec)
mysql> desc emp
-> ;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gendef | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
DDL-表修改&删除
添加字段
mysql> alter table emp add nickname varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gendef | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
| nickname | varchar(20) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> alter table emp change nickname usernmae varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gendef | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
| usernmae | varchar(30) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
DLL表操作修改
mysql> alter table emp drop usernmae;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gendef | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)
DDL-表操作-修改
mysql> alter table emp rename to employee;
Query OK, 0 rows affected (0.01 sec)
mysql> desc employee;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gendef | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
mysql> drop table if exists ta_user;
\Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| incast |
| information_schema |
| itcast |
| itheima |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql> select gendef from emp;
+--------+
| gendef |
+--------+
| M |
| M |
| F |
| M |
| F |
+--------+
5 rows in set (0.00 sec)
mysql> select distinct gendef from emp;
+--------+
| gendef |
+--------+
| M |
| F |
+--------+
2 rows in set (0.01 sec)