Mysql数据库基本操作(二)

最新推荐文章于 2024-10-29 18:55:33 发布

原创最新推荐文章于 2024-10-29 18:55:33 发布 · 1.1k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Database

本文介绍了如何在MySQL中进行数据库和表的基本操作，包括创建数据库、创建及修改表结构，如添加、删除字段，以及字段名称和类型的变更。此外，还涉及了插入记录、查询、更新和删除记录的语法。

-创建数据库

语法: create database databases_name;

下面我们创建一个新的数据库,名字为player,如下

mysql> create database player;

创建成功,结果显示如下:

Query OK, 1 row affected (0.00 sec)

查看数据库;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| player             |
| student            |
+--------------------+
4 rows in set (0.00 sec)

使用数据库:

mysql> use player;
Database changed

现在由于新创建的数据库,所以里面不会有任何表,那么我们现在就来完善表的配置;

-创建表

语法: create table table_name(Field Type Null Key Default Extra );

如下创建一个player_info的表,具体括号内数据一系列属性可以根据实际情况定义如下:

mysql> create table player_info(id int(12) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY);

结果如下:

Query OK, 0 rows affected (0.04 sec)

使用show tables;语句查看是否存在;

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

除了上节可以使用show create table table_name \G 用来查询表结构,这里介绍一下查看表的结构的另外一种方法

-查询表结构方法(二)

语法: describe table_name;

mysql> describe player_info;

结果显示为:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(12) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.01 sec)

-修改之增加表字段

语法: alter table table_name add Field Type Null Key Default Extra;

现在我添加球员的姓名,性别,生日等信息, 注意这些属性的类型

mysql> alter table player_info add name varchar(10);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table player_info add sex enum('male','female');
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table player_info add birth date NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-修改之删除表字段

语法: alter table table_name drop Field_name;

比如说我将"birth"这一栏删除,为了做一下对比,我们使用describe table_name;来显示一下刚才添加的字段

mysql> describe player_info;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(12) unsigned      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
| birth | date                  | NO   |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

现在删除该字段,删除的命令和结果如下:

mysql> alter table player_info drop birth;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用上面的命令在查看是否存在;

mysql> describe player_info;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(12) unsigned      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

再次添加birth字段,为后续演示做准备,

mysql> alter table player_info add birth date NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在已经添加birth成功,即现在表内已经还原为四项信息.

mysql> describe player_info;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(12) unsigned      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | NULL    |                |
| birth | date                  | NO   |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

现在我想将birth字段这个名称改为birthday

-修改原字段名称和类型(再根据需要修改其他属性)

语法: alter table table_name change old_Field_name new_Field_name Type;

下面我在修改Field_name基础上,设定一个Default的日期.

mysql> alter table player_info change birth birthday date default '1988-2-1';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在的结果为,试着对比一下:

mysql> describe player_info;
+----------+-----------------------+------+-----+------------+----------------+
| Field    | Type                  | Null | Key | Default    | Extra          |
+----------+-----------------------+------+-----+------------+----------------+
| id       | int(12) unsigned      | NO   | PRI | NULL       | auto_increment |
| name     | varchar(10)           | YES  |     | NULL       |                |
| sex      | enum('male','female') | YES  |     | NULL       |                |
| birthday | date                  | YES  |     | 1988-02-01 |                |
+----------+-----------------------+------+-----+------------+----------------+
4 rows in set (0.00 sec)

-记录操作之插入表记录

语法: insert (into) table_name values(1,2,3,4 or more...)

注意: 1,2,3,4分别对应原字段1,字段2,字段3,字段4.对应规定好的属性.

mysql> insert player_info values('24','kobe','male','1978-08-23');
Query OK, 1 row affected (0.00 sec)

mysql> insert player_info values('23','james','male','1984-12-30');
Query OK, 1 row affected (0.02 sec)

mysql> insert player_info values('25','FW','male','2014-04-14');
Query OK, 1 row affected (0.02 sec)

数据插入成功,如何查询呢?

-记录操作之查询表记录

语法: select * from table_name where condition;

mysql> select * from player_info where sex='male';

查询性别为'male',搜索结果如下:

+----+-------+------+------------+
| id | name  | sex  | birthday   |
+----+-------+------+------------+
| 23 | james | male | 1984-12-30 |
| 24 | kobe  | male | 1978-08-23 |
| 25 | FW    | male | 2014-04-14 |
+----+-------+------+------------+
3 rows in set (0.00 sec)

-记录操作之更改表记录

语法: update table_name set Field_name='New_Name' where condition;

注意:这个Field_name可以是上面4个字段中的任意一个;

mysql> update player_info set id=33 where name='FW';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在查询一次表记录(可以不用精确定位),显示更改成功

mysql> select * from player_info where id;
+----+-------+------+------------+
| id | name  | sex  | birthday   |
+----+-------+------+------------+
| 23 | james | male | 1984-12-30 |
| 24 | kobe  | male | 1978-08-23 |
| 33 | FW    | male | 2014-04-14 |
+----+-------+------+------------+
3 rows in set (0.00 sec)

-记录操作之删除表记录

语法: delete from table_name where condition;

mysql> delete from player_info where id=33;
Query OK, 1 row affected (0.00 sec)

再查询表记录,显示已经成功删除;

mysql> select * from player_info where sex='male';
+----+-------+------+------------+
| id | name  | sex  | birthday   |
+----+-------+------+------------+
| 23 | james | male | 1984-12-30 |
| 24 | kobe  | male | 1978-08-23 |
+----+-------+------+------------+
2 rows in set (0.00 sec)

标签

#linux #数据库 #mysql