数据库基本操作

数据库操作

1. DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

1.1.1INSERT语句

DML操作之增操作insert

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
+--------------------+
4 rows in set (0.003 sec)

MariaDB [(none)]> use changed;
ERROR 1049 (42000): Unknown database 'changed'
MariaDB [(none)]> use runtime;
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
MariaDB [runtime]> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| biao              |
| cys               |
+-------------------+
2 rows in set (0.000 sec)

MariaDB [runtime]> 

1.1.2 SELECT语句

字段column表示法

表示符	代表什么？
*	所有字段
as	字段别名，如col1 AS alias1,当表名很长时用别名代替

MariaDB [runtime]> select name as 姓名,age as 年龄 from biao; 
+----------+--------+
| 姓名     | 年龄   |
+----------+--------+
| yuli     |   NULL |
| jerry    |     23 |
| wangwu   |     25 |
| zhangsan |     28 |
| lisi     |     26 |
| laoliu   |     20 |
| xiaoqi   |     30 |
| laoba    |     20 |
+----------+--------+
8 rows in set (0.000 sec)

MariaDB [runtime]> 

条件判断语句WHERE
操作类型 常用操作符
|操作符| >，<，>=，<=，=，!=
BETWEEN column# AND column#
LIKE：模糊匹配
RLIKE：基于正则表达式进行模式匹配
IS NOT NULL：非空
IS NULL：空
条件逻辑操作 AND
OR
NOT
ORDER BY：排序，默认为升序（ASC）

MariaDB [runtime]> select * from biao where age = 20;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  6 | laoliu |   20 |
|  8 | laoba  |   20 |
+----+--------+------+
2 rows in set (0.000 sec)

MariaDB [runtime]> 

MariaDB [runtime]> select * from biao where age = 20 and id = 6;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  6 | laoliu |   20 |
+----+--------+------+
1 row in set (0.000 sec)

MariaDB [runtime]> 

MariaDB [runtime]> select * from biao where id > 4;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  5 | lisi   |   26 |
|  6 | laoliu |   20 |
|  7 | xiaoqi |   30 |
|  8 | laoba  |   20 |
+----+--------+------+
4 rows in set (0.000 sec)

MariaDB [runtime]> select * from biao where id < 4;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | yuli   | NULL |
|  2 | jerry  |   23 |
|  3 | wangwu |   25 |
+----+--------+------+
3 rows in set (0.000 sec)

MariaDB [runtime]> select * from biao where age between 21 and 28;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | wangwu   |   25 |
|  4 | zhangsan |   28 |
|  5 | lisi     |   26 |
+----+----------+------+
4 rows in set (0.000 sec)

MariaDB [runtime]> 

MariaDB [runtime]> select * from biao where name like '%g%';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | wangwu   |   25 |
|  4 | zhangsan |   28 |
+----+----------+------+
2 rows in set (0.000 sec)

ORDER BY语句	意义
ORDER BY ‘column_name’	根据column_name进行升序排序
ORDER BY ‘column_name’ DESC	根据column_name进行降序排序
ORDER BY ’column_name’ LIMIT 2	根据column_name进行升序排序,并只取前2个结果
ORDER BY ‘column_name’ LIMIT 1,2	根据column_name进行升序排序,并且略过第1个结果取后面的2个结果

DML操作之查操作select

MariaDB [runtime]> select * from biao;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | yuli     |    8 |
|  2 | jerry    |   23 |
|  3 | wangwu   |   25 |
|  4 | zhangsan |   28 |
|  5 | lisi     |   26 |
|  6 | laoliu   |   20 |
|  7 | xiaoqi   |   30 |
|  8 | laoba    |   20 |
+----+----------+------+
8 rows in set (0.000 sec)

MariaDB [runtime]> 

MariaDB [runtime]> select name from biao;
+----------+
| name     |
+----------+
| yuli     |
| jerry    |
| wangwu   |
| zhangsan |
| lisi     |
| laoliu   |
| xiaoqi   |
| laoba    |
+----------+
8 rows in set (0.001 sec)

MariaDB [runtime]> 
MariaDB [runtime]> select * from biao order by age;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | yuli     |    8 |
|  6 | laoliu   |   20 |
|  8 | laoba    |   20 |
|  2 | jerry    |   23 |
|  3 | wangwu   |   25 |
|  5 | lisi     |   26 |
|  4 | zhangsan |   28 |
|  7 | xiaoqi   |   30 |
+----+----------+------+
8 rows in set (0.000 sec)

MariaDB [runtime]> 
MariaDB [runtime]> select * from biao order by age desc;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  7 | xiaoqi   |   30 |
|  4 | zhangsan |   28 |
|  5 | lisi     |   26 |
|  3 | wangwu   |   25 |
|  2 | jerry    |   23 |
|  6 | laoliu   |   20 |
|  8 | laoba    |   20 |
|  1 | yuli     |    8 |
+----+----------+------+
8 rows in set (0.000 sec)

MariaDB [runtime]> 
MariaDB [runtime]> select * from biao order by age limit 1,2;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  8 | laoba  |   20 |
|  6 | laoliu |   20 |
+----+--------+------+
2 rows in set (0.000 sec)
MariaDB [runtime]> select * from biao order by age limit 2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | yuli  |    8 |
|  8 | laoba |   20 |
+----+-------+------+
2 rows in set (0.000 sec)

MariaDB [runtime]> 
MariaDB [runtime]> select * from biao where age >= 25;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | wangwu   |   25 |
|  4 | zhangsan |   28 |
|  5 | lisi     |   26 |
|  7 | xiaoqi   |   30 |
+----+----------+------+
4 rows in set (0.000 sec)

MariaDB [runtime]> 
MariaDB [runtime]> select * from biao where age >= 25 and name = 'wangwu';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  3 | wangwu |   25 |
+----+--------+------+
1 row in set (0.001 sec)

MariaDB [runtime]> 
MariaDB [runtime]> select * from biao where age between 25 and 28;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | wangwu   |   25 |
|  4 | zhangsan |   28 |
|  5 | lisi     |   26 |
+----+----------+------+
3 rows in set (0.001 sec)

MariaDB [runtime]> 
MariaDB [runtime]> select * from biao where age is not null;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | yuli     |