Mariadb表操作的简单总结，mariadb表总结

Mariadb表操作的简单总结

上一篇文章我们介绍了MariaDB的二进制安装方法，本文将对MariaDB数据库的表的相关操作进行简单介绍。
表的操作主要包括以下几种：

1，表的创建与删除
2，字段的添加与删除
3，字段的属性修改
4，记录的增删改查

1 表的创建与删除

创建一个新的数据表的语句格式如下：

CREATE TABLE [IF NOT EXISTS] “table_name” (col1 type1 修饰符, col2 type2 修饰符, ...);

其中col1、col2分别为创建数据表时同时创建的数组字段，type、type2则为各个字段的数值类型。 示例如下：

MariaDB [student]> CREATE TABLE student
    -> (id tinyint PRIMARY KEY,
    ->  name varchar(25) NOT NULL,
    -> gender ENUM ('M','F') DEFAULT "M");
Query OK, 0 rows affected (0.01 sec)

MariaDB [student]> DESC student;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | tinyint(4)    | NO   | PRI | NULL    |       |
| name   | varchar(25)   | NO   |     | NULL    |       |
| gender | enum('M','F') | YES  |     | M       |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除一个数据表的语句格式如下：

DROP TABLE "table_name";

示例如下：

MariaDB [student]> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

MariaDB [student]> DROP TABLE student;
Query OK, 0 rows affected (0.00 sec)

MariaDB [student]> show tables;       
Empty set (0.00 sec)

2 字段的添加与删除

数据字段除了可以在创建数据表的时候生成，也可以后来建立新的字段加入数据表。
添加数据字段的语句格式如下：

ALTEL TABLE "table_name" ADD colum type;

其中colum为要添加的字段，type为添加字段的数值类型。
示例如下：

MariaDB [student]> DESC student;                       
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | tinyint(4)    | NO   | PRI | NULL    |       |
| name   | varchar(25)   | NO   |     | NULL    |       |
| gender | enum('M','F') | YES  |     | M       |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [student]> ALTER TABLE student ADD age tinyint;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [student]> DESC student;                       
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | tinyint(4)    | NO   | PRI | NULL    |       |
| name   | varchar(25)   | NO   |     | NULL    |       |
| gender | enum('M','F') | YES  |     | M       |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

删除数据字段的语句格式如下：

ALTEL TABLE "table_name" DROP colum;

示例如下：

MariaDB [student]> DESC student;                       
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | tinyint(4)    | NO   | PRI | NULL    |       |
| name   | varchar(25)   | NO   |     | NULL    |       |
| gender | enum('M','F') | YES  |     | M       |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

MariaDB [student]> ALTER TABLE student DROP age;       
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

MariaDB [student]> DESC student;                
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | tinyint(4)    | NO   | PRI | NULL    |       |
| name   | varchar(25)   | NO   |     | NULL    |       |
| gender | enum('M','F') | YES  |     | M       |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3 字段的属性修改

前面我们在创建数据表或者添加新的数据字段，对字段的基本属性做了设定。但有时也可能需要对字段属性做出修改。修改字段属性的语句格式如下：

ALTER TABLE table_name MODIFY colum new_type new_other_attribute;

示例如下：

MariaDB [student]> DESC student;                
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | tinyint(4)    | NO   | PRI | NULL    |       |
| name   | varchar(25)   | NO   |     | NULL    |       |
| gender | enum('M','F') | YES  |     | M       |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [student]> ALTER TABLE student MODIFY name char(25);
Query OK, 2 rows affected, 1 warning (0.02 sec)    
Records: 2  Duplicates: 0  Warnings: 1

MariaDB [student]> DESC student;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | tinyint(4)    | NO   | PRI | NULL    |       |
| name   | char(25)      | YES  |     | NULL    |       |
| gender | enum('M','F') | YES  |     | M       |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4 记录行的增删改查

针对记录行的增删改查操作应该是我们针对数据表最频繁的操作了。
增加记录行的语句格式如下：

INSERT INTO "table_name" (col1,col2,……) VALUE (val1,val2,……);

其中，val1、val2分别是对应字段col1、col2的值。如果值的数据类型为字符型，还需要使用引号标注。示例如下：

MariaDB [student]> INSERT INTO student (id,name,gender) value (101,"zhang","M");
Query OK, 1 row affected (0.01 sec)

MariaDB [student]> select * from student;                                       
+-----+-------+--------+
| id  | name  | gender |
+-----+-------+--------+
| 101 | zhang | M      |
+-----+-------+--------+
1 row in set (0.00 sec)

MariaDB [student]> INSERT INTO student (id,name) value (102,"wang");            
Query OK, 1 row affected (0.01 sec)

MariaDB [student]> select * from student;                           
+-----+-------+--------+
| id  | name  | gender |
+-----+-------+--------+
| 101 | zhang | M      |
| 102 | wang  | M      |
+-----+-------+--------+
2 rows in set (0.00 sec)
MariaDB [student]> INSERT INTO student (id,name,gender) VALUE (103,"zeng","F");
Query OK, 1 row affected (0.01 sec)

MariaDB [student]> select * from student;                                      
+-----+-------+--------+
| id  | name  | gender |
+-----+-------+--------+
| 101 | zhang | M      |
| 102 | wang  | M      |
| 103 | zeng  | F      |
+-----+-------+--------+
3 rows in set (0.00 sec)

删除记录行的语句格式如下：

DELETE FROM "table_name" WHERE 子句;  

其中WHERE子句是为了过滤出需要删除的记录行。若没有WHERE子句，将清空整个数据表。示例如下：

MariaDB [student]> select * from student;                                      
+-----+-------+--------+
| id  | name  | gender |
+-----+-------+--------+
| 101 | zhang | M      |
| 102 | wang  | M      |
| 103 | zeng  | F      |
+-----+-------+--------+
3 rows in set (0.00 sec)

MariaDB [student]> DELETE FROM student WHERE name="wang";
Query OK, 1 row affected (0.01 sec)

MariaDB [student]> select * from student;                
+-----+-------+--------+
| id  | name  | gender |
+-----+-------+--------+
| 101 | zhang | M      |
| 103 | zeng  | F      |
+-----+-------+--------+
2 rows in set (0.00 sec)

修改记录行的语句格式如下：

UPDATE "table_name" SET COLUMN="NEW_VALUE" WHERE子句;

示例如下：

MariaDB [student]> select * from student;                
+-----+-------+--------+
| id  | name  | gender |
+-----+-------+--------+
| 101 | zhang | M      |
| 103 | zeng  | F      |
+-----+-------+--------+
2 rows in set (0.00 sec)

MariaDB [student]> UPDATE student SET gender="F" WHERE name="zhang";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [student]> select * from student;                           
+-----+-------+--------+
| id  | name  | gender |
+-----+-------+--------+
| 101 | zhang | F      |
| 103 | zeng  | F      |
+-----+-------+--------+
2 rows in set (0.00 sec)

查询数据记录前面我们用到了很多次select * from student,可以将我们需要的满足指定条件的数据行的指定字段显示出来，具体语句格式如下：

SELECT clo1,clo2,…… FROM "table_name" WHERE子句 ORDER BY子句

其中clo1,clo2表示需要显示的字段，WHERE子句过滤出满足条件的记录行，ORDER BY子句则将查询结果安装指定列进行排序。示例如下：

MariaDB [student]> SELECT * FROM student;      
+-----+-------+--------+------+
| id  | name  | gender | age  |
+-----+-------+--------+------+
| 101 | zhang | M      |   20 |
| 102 | wang  | M      |   21 |
| 103 | zeng  | F      |   18 |
| 104 | li    | F      |   17 |
| 105 | xu    | F      |   20 |
+-----+-------+--------+------+
5 rows in set (0.00 sec)

MariaDB [student]> SELECT id,name,age FROM student WHERE gender="F";
+-----+------+------+
| id  | name | age  |
+-----+------+------+
| 103 | zeng |   18 |
| 104 | li   |   17 |
| 105 | xu   |   20 |
+-----+------+------+
3 rows in set (0.01 sec)

MariaDB [student]> SELECT id,name,age FROM student WHERE gender="F" ORDER BY age;
+-----+------+------+
| id  | name | age  |
+-----+------+------+
| 104 | li   |   17 |
| 103 | zeng |   18 |
| 105 | xu   |   20 |
+-----+------+------+
3 rows in set (0.00 sec)
MariaDB [student]> SELECT id,name,gender,age FROM student WHERE age<20 ORDER BY age;           
+-----+------+--------+------+
| id  | name | gender | age  |
+-----+------+--------+------+
| 104 | li   | F      |   17 |
| 103 | zeng | F      |   18 |
+-----+------+--------+------+
2 rows in set (0.00 sec)

至此，关于数据表的基本操作就介绍完毕。