Mysql数据库基础概念及用法（增删改查）

1.前言

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

2.数据库的基本概念

• 数据：描述事务的符号记录，其中包括了数字、文字、图形、声音、档案记录等、以“记录”形式按统一的格式进行存储
• 表：将不同的记录组织到一起，用来存储具体数据
• 数据库：表的集合，是存储数据的仓库，并以一定的组织方式存储的相关有关的数据集合

2.1数据库系统

• 数据库管理系统是实现对数据资源有效组织、管理和存取的系统文件
• 数据库系统是一个人机系统、由硬件、OS、数据库、DBMS、应用软件、和数据库用户组成，用户通过DBMS或应用程序操作数据库

3.数据库类型

当今主流的数据库SQL Server（微软公司）、Oracle（甲骨文公司）、DB2（IBM公司）、MySQL（由甲骨文公司收购），这些都是关系型数据库，但是也有非关系型数据库。

3.1关系数据库

关系型数据库系统是基于关系模型的数据库系统，关系模型的数据结构使用简单的二维数据表，包含了实体（数据对象）、关系、和属性三个要素

• 实体：也称为实例，对应现实世界中可区别于其他对象的“事件”或这“事物”，如银行、银行账户等
• 属性：实体所具有的某一特性、一个实体可以有多个属性，如“客户”实体集中的每个实体均具有、姓名、住址、电话等属性
• 联系：实体集之间的对应关系称为联系，也称为关系，如客户和账户之间存在“储蓄”关系
  所有实体及实体之间联系的集合构成一个关系数据库
  关系型数据库的存储结构是二维表格，在每个二维表中，每一行被称为一条记录，每一列称为一个字段。

3.2非关系数据库

非关系数据库也被称为NoSQL（Not Only SQL），它的存储数据不以关系模型为依据，并且不需要固定的表格式这意味着非关系型数据库：可高并发读写、对海量数据高效率的存储于访问、具有高扩展性于可用性。常用的非关系数据库有Redis、mongoDB等。

4.常用的数据类型

• int:整型，整数
• float:单精度浮点4字节32位（准确表示到小数点后六位）
• double:双精度浮点8字节64位
• char:固定长度的字符类型
• varchar:可变长度的字符类型
• text:文本
• image:图片
• decimal(5，2):5个有效长度数字，小数点后面有两位
  char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会截取，高版本会报错。

5.SQL语言概述

SQL语言
关系型数据库的标准语言，它用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能
SQL分类

• DDL：数据定义语言，用于创建数据库对象，如库、表、索引等。
• DML：数据操纵语言，用于对表中的数据进行管理。
• DQL：数据查询语言，用于从数据表中查找符合条件的数据记录。
• DCL：数据控制语言，用于设置或者更改数据库用户或角色权限。

6.MySQL数据库的基础操作（增删改查）

6.1DDL

• 免交互进入mysql数据库（不建议，因为不安全）

[root@localhost ~]# mysql -uroot -pqwer      
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 

• 交互（需要手动输入密码进入mysql）进入mysql

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

• 查看数据库信息

mysql> show databases;  
+--------------------+
| Database           |
+--------------------+
| information_schema |         //信息表
| mysql              |
| performance_schema |         //对接表
| sys                |
+--------------------+
4 rows in set (0.00 sec)

• 进入库，查看mysql库下所有的表

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

• 显示表结构（字段）

mysql> describe usr;
ERROR 1146 (42S02): Table 'mysql.usr' doesn't exist
mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
field：字段名称
Type：数据类型
Null：是否允许为空
Key：主键
Dcfault：默认值
Extra：扩展属性

• 创建库

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

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

• 创建表
  id为整数不可为空，name（设置最大字符值，这里我设为20，不可为空），score（可以附带小数点，所以设置decimal（5，2）不可为空），age（年龄我随便设置了最大值5位数），address（地址最大字符数位50），default（默认地址为home），primary key（表示主键为id）

mysql> use xy;
Database changed
mysql> create table bj (id int not null,name varchar(20) not null,score decimal(5,2) not null,age int(5),address varchar(50) default 'hoome',primary key(id));
Query OK, 0 rows affected (0.00 sec)

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

mysql> desc bj;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   |     | NULL    |       |
| score   | decimal(5,2) | NO   |     | NULL    |       |
| age     | int(5)       | YES  |     | NULL    |       |
| address | varchar(50)  | YES  |     | home    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

删除数据库

mysql> drop table bj;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> drop table xy.bj;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

6.2DML

• insert：插入新数据
• update：更新原有数据
• delete：删除不需要的数据
  格式

INSERT INTO 表明(字段一,字段二[,....] VALUES (字段一的值,字段二的值,...);

UPDATE 表名 SET 字段1=字段值1[字段名2=字段值2] [WHERE 条件表达式];

DELETE FROM 表名 [WHERE 条件表达式];

• 插入数据在表中，并查询详细内容

mysql> insert into bj (id,name,score,age,address) values(1,'zhangsan',89.155,20,'南京');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from bj;
+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | zhangsan | 89.16 |   20 | 南京    |
+----+----------+-------+------+---------+
1 row in set (0.00 sec)

主键属性不能冲突（也就是id，在创建时已经标明id为主键），会报错，也就是说执行不了

• 更新原有数据（通过where判断主键精确匹配）

mysql> select * from bj;
+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | zhangsan | 89.16 |   20 | 南京    |
|  2 | lisi     | 80.00 |   20 | 杭州    |
|  3 | wangwu   | 80.00 |   20 | 成都    |
+----+----------+-------+------+---------+

mysql> update bj set name='zhaoliu' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from bj;
+----+---------+-------+------+---------+
| id | name    | score | age  | address |
+----+---------+-------+------+---------+
|  1 | zhaoliu | 89.16 |   20 | 南京    |
|  2 | lisi    | 80.00 |   20 | 杭州    |
|  3 | wangwu  | 80.00 |   20 | 成都    |
+----+---------+-------+------+---------+
3 rows in set (0.00 sec)

也可以通过，连接更新多条数据

mysql> update bj set name='zhangsan',age='30' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from bj;
+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | zhangsan | 89.16 |   30 | 南京    |
|  2 | lisi     | 80.00 |   20 | 杭州    |
|  3 | wangwu   | 80.00 |   20 | 成都    |
+----+----------+-------+------+---------+
3 rows in set (0.00 sec)

• 删除不需要的数据

mysql> delete from bj where id='2';
Query OK, 1 row affected (0.00 sec)

mysql> select * from bj;
+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | zhangsan | 89.16 |   30 | 南京    |
|  3 | wangwu   | 80.00 |   20 | 成都    |
+----+----------+-------+------+---------+
2 rows in set (0.00 sec)

尽量选择分辨性唯一性较强的

6.3DQL

格式

SELECT 字段1,字段2[,..] FROM 表名 [WHERE 条件表达式]

• 查看所有数据

mysql> select * from bj;
+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | zhangsan | 89.16 |   30 | 南京    |
|  3 | wangwu   | 80.00 |   20 | 成都    |
+----+----------+-------+------+---------+
2 rows in set (0.00 sec)

• 查询id，name字段

mysql> select id,name from bj;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  3 | wangwu   |
+----+----------+
2 rows in set (0.00 sec)

• 精准查询第一行的id，name

mysql> select id,name from bj where id=2;
Empty set (0.00 sec)

mysql> select id,name from bj where id=1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

• 显示大于小于该数据的字段

mysql> select * from bj where score>85;
+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | zhangsan | 89.16 |   30 | 南京    |
+----+----------+-------+------+---------+
1 row in set (0.01 sec)

mysql> select * from bj where score<85;
+----+--------+-------+------+---------+
| id | name   | score | age  | address |
+----+--------+-------+------+---------+
|  3 | wangwu | 80.00 |   20 | 成都    |
+----+--------+-------+------+---------+
1 row in set (0.00 sec)

• 只显示以第一行为开始的后面两行

mysql> select * from bj limit 1,2;
+----+--------+-------+------+---------+
| id | name   | score | age  | address |
+----+--------+-------+------+---------+
|  2 | lisi   | 80.00 |   20 | 杭州    |
|  3 | wangwu | 80.00 |   20 | 成都    |
+----+--------+-------+------+---------+
2 rows in set (0.00 sec)

• 显示三行数据

mysql> select * from bj limit 3;
+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | zhangsan | 89.16 |   30 | 南京    |
|  2 | lisi     | 80.00 |   20 | 杭州    |
|  3 | wangwu   | 80.00 |   20 | 成都    |
+----+----------+-------+------+---------+
3 rows in set (0.00 sec)

6.4DCL

格式

ALTER TABLE 旧表名 RENAME 新表名;

• 修改表名

mysql> alter table rqrw rename new;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from new;
+----+----------+-------+------+---------+
| id | name     | score | age  | address |
+----+----------+-------+------+---------+
|  1 | zhangsan | 89.16 |   30 | 南京    |
|  2 | lisi     | 80.00 |   20 | 杭州    |
|  3 | wangwu   | 80.00 |   20 | 成都    |
+----+----------+-------+------+---------+
3 rows in set (0.00 sec)

• 添加hope字段

mysql> alter table new add hope varchar(11);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc new;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   |     | NULL    |       |
| score   | decimal(5,2) | NO   |     | NULL    |       |
| age     | int(5)       | YES  |     | NULL    |       |
| address | varchar(50)  | YES  |     | home    |       |
| hope    | varchar(11)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

• 修改address为_new，字符长度为50，并且设置唯一键（但可以为空，切空值只允许出现一一次），默认地址为不详

mysql> alter table new change address address_new varchar(50) unique key default '地址不详';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc new;
+-------------+--------------+------+-----+--------------+-------+
| Field       | Type         | Null | Key | Default      | Extra |
+-------------+--------------+------+-----+--------------+-------+
| id          | int(11)      | NO   | PRI | NULL         |       |
| name        | varchar(20)  | NO   |     | NULL         |       |
| score       | decimal(5,2) | NO   |     | NULL         |       |
| age         | int(5)       | YES  |     | NULL         |       |
| address_new | varchar(50)  | YES  | UNI | 地址不详     |       |
| hope        | varchar(11)  | YES  |     | NULL         |       |
+-------------+--------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)

mysql> select * from new;
+----+----------+-------+------+-------------+------+
| id | name     | score | age  | address_new | hope |
+----+----------+-------+------+-------------+------+
|  1 | zhangsan | 89.16 |   30 | 南京        | NULL |
|  2 | lisi     | 80.00 |   20 | 杭州        | NULL |
|  3 | wangwu   | 80.00 |   20 | 成都        | NULL |
+----+----------+-------+------+-------------+------+
3 rows in set (0.00 sec)

7.用户管理

• 新建用户并设置加密密码

mysql> create user 'lisi'@'localhost' identified by 'abc123';		
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql.user\G;			
mysql> select password('abc123');			
+-------------------------------------------+
| password('abc123')                        |
+-------------------------------------------+
| *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
create user 'wangwu'@'localhost' identified by '*6691484EA6B50DDDE1926A220DA01FA9E575C18A';

• 查询用户信息

mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| lisi          | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)

• 删除用户

mysql> create user 'xy'@'localhost' identified by 'abc222';		
Query OK, 0 rows affected (0.00 sec)

mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| xy            | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost |
+---------------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)

mysql> drop user 'xy'@'localhost';			
Query OK, 0 rows affected (0.00 sec)

mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| xy            | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)

8.示例

mysql> create table if not exists info(id int(4) zerofill primary key auto_increment not null,name varchar(20),cardid int(18) not null unique key,hobby varchar(50) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> desc info;
+--------+--------------------------+------+-----+---------+----------------+
| Field  | Type                     | Null | Key | Default | Extra          |
+--------+--------------------------+------+-----+---------+----------------+
| id     | int(4) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)              | YES  |     | NULL    |                |
| cardid | int(18)                  | NO   | UNI | NULL    |                |
| hobby  | varchar(50)              | NO   |     | NULL    |                |
+--------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

• if not exists：表示要创建的表是否已经存在，如果不存在就继续创建，
• int（4）zerofill：表示若数值不满4位数，则前面用“0”填充
• auto_increment：表四次字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段不可重复，自增字段必须是主键，如过添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
• unique key：表示此字段唯一键约束，此字段数据不可以重复，一张表中只能有一个主键，但是一张表中可以有多个唯一键。

create table 复制的新表名 like 原表名；		#这里只是单纯的复制表的格式，
insert into 源表名 select * from 复制的新表名；		#将表中的内容导入新表中，也可以用来备份
create table test01 （select * from test）；		#克隆表 ，不带特殊格式，如主键

mysql> create table new like info;				
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_xy  |
+---------------+
| new           |
| info          |
+---------------+
3 rows in set (0.00 sec)
mysql> insert into new select * from info;			
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from new;
+----+---------+-----------+---------+
| id | name    | score_new | address |
+----+---------+-----------+---------+
|  1 | lisi    | 99        | 上海    |
|  2 | zhansan | 50        | 杭州    |
|  3 | wangwu  | 20        | 杭州    |
|  4 | xuyi    | 40        | 南京    |
|  5 | whd     | 50        | 湖北    |
+----+---------+-----------+---------+
5 rows in set (0.00 sec)
mysql> create table xyy (select * from new);		
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show tables;
+---------------+
| Tables_in_xy  |
+---------------+
| new           |
| info          |
| xyy           |
+---------------+
4 rows in set (0.00 sec)

mysql> alter table xyy change id id int(11) primary key;			
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe xyy;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(10) | NO   |     | NULL    |       |
| score_new | varchar(3)  | YES  |     | NULL    |       |
| address   | varchar(40) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

总结

数据以一定的形式记录写入，以“记录”形式存储，表将不同的记录组织在了一起（表有n个），n多个表的集合，组成了库也就是数据库

关系数据库存储结构是二维表格，每一行称为一条记录，用来描述一个对象的信息，每一列称为一个字段，用来描述对象的一个属性，类似于Excel表格

unique key：唯一键，唯一，但是可以为空，但是空值只允许出现一次
promary key：唯一键，唯一且必须不能为空