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)
至此,关于数据表的基本操作就介绍完毕。