数据库MySQL（二）插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

目录

一、DML

1.插入数据INSERT

2.更新数据UPDATE

3.删除数据DELETE

二、DQL

1.简单查询

2.条件查询

3.查询排序

---

一、DML

1.插入数据INSERT

完整插入 语法：INSERT INTO 表名 VALUES (值1，值2，值3...);

部分插入 语法：insert into student (id,name) values (1,'lin');

2.更新数据UPDATE

语法：UPDATE 表名 SET 列名=值 WHERE CONDATION;

示例1：update t1 set name='lin' where id=1;

             select * from t1;  //查询结果

示例2：修改MySQL数据库管理员root账户的密码

              update mysql.user set authentication_string=password("Qian@1234新密码") whre user="root";

              flush privileges;        //更新数据库

3.删除数据DELETE

语法：DELETE FROM 表名 WHERE CONDITION;

示例：delete from t1 where name='lin';

二、DQL

准备环境：

• 素材1

准备一张表，包含三列信息

id int 序号

name varchar 姓名

age int 年龄

示例：create table t3 (id int,name varchar(20),age int);

再插入测试数据

insert into t3 values (1,"zhangsan",23);

insert into t3 values (2,"lisi",31);

insert into t3 values (3,"wang",22);

• 素材2

结构语句

mysql> create table t1(id int,name varchar(30),sex enum('m','f'),hire_date date,postvarchar(30),job_description varchar(30),salary double(15,2),office int,dep_id int);
mysql> desc t1;
+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| id              | int           | YES  |     | NULL    |       |
| name            | varchar(30)   | YES  |     | NULL    |       |
| sex             | enum('m','f') | YES  |     | NULL    |       |
| hire_date       | date          | YES  |     | NULL    |       |
| post            | varchar(30)   | YES  |     | NULL    |       |
| job_description | varchar(30)   | YES  |     | NULL    |       |
| salary          | double(15,2)  | YES  |     | NULL    |       |
| office          | int           | YES  |     | NULL    |       |
| dep_id          | int           | YES  |     | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

插入数据

mysql> insert into t1(name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','m','20210101','instructor','teach',5000,501,100),('tom','m','20220101','instructor','teach',5000,501,100),('robin','m','20210101','instructor','teach',5000,501,100),('alice','f','20210901','hr','hrcc',7000,501,100);

1.简单查询

mysql> select * from t1;    //查看所有列
+------+-------+------+------------+------------+-----------------+---------+--------+--------+
| id   | name  | sex  | hire_date  | post       | job_description | salary  | office | dep_id |
+------+-------+------+------------+------------+-----------------+---------+--------+--------+
| NULL | jack  | m    | 2021-01-01 | instructor | teach           | 5000.00 |    501 |    100 |
| NULL | tom   | m    | 2022-01-01 | instructor | teach           | 5000.00 |    501 |    100 |
| NULL | robin | m    | 2021-01-01 | instructor | teach           | 5000.00 |    501 |    100 |
| NULL | alice | f    | 2021-09-01 | hr         | hrcc            | 7000.00 |    501 |    100 |
+------+-------+------+------------+------------+-----------------+---------+--------+--------+
4 rows in set (0.00 sec)

mysql> select name,sex from t1;    //查部分列
+-------+------+
| name  | sex  |
+-------+------+
| jack  | m    |
| tom   | m    |
| robin | m    |
| alice | f    |
+-------+------+
4 rows in set (0.00 sec)

2.条件查询

单条件查询where

//查询hr部门的员工姓名
mysql> select name,post from t1 where post='hr';
+-------+------+
| name  | post |
+-------+------+
| alice | hr   |
+-------+------+
1 row in set (0.01 sec)

多条件查询and/or

//查询instructor,并且工资等于5000
mysql> select name,post from t1 where post='instructor' AND salary=5000;
+-------+------------+
| name  | post       |
+-------+------------+
| jack  | instructor |
| tom   | instructor |
| robin | instructor |
+-------+------------+
3 rows in set (0.00 sec)

关键字between and 在什么之间

//查询薪资在4000-6000之间的
mysql> select name,salary from t1 where salary between 4000 and 6000;
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| tom   | 5000.00 |
| robin | 5000.00 |
+-------+---------+
3 rows in set (0.00 sec)
//查询薪资不在4000-6000之间的
mysql> select name,salary from t1 where salary not between 4000 and 6000;
+-------+---------+
| name  | salary  |
+-------+---------+
| alice | 7000.00 |
+-------+---------+
1 row in set (0.00 sec)

关键字in集合查询

//工资可能是5000，也有可能是7000的
mysql> select name,salary from t1 where salary in (5000,7000);
+-------+---------+
| name  | salary  |
+-------+---------+
| jack  | 5000.00 |
| tom   | 5000.00 |
| robin | 5000.00 |
| alice | 7000.00 |
+-------+---------+
4 rows in set (0.00 sec)

关键字is null  没有岗位描述的

//岗位描述为空的
mysql> select name,job_description from t1 where job_description is null;
//岗位描述非空的
mysql> select name,job_description from t1 where job_description is not null；

关键字like 模糊查询

//好像有一个a开头的，通配符%代表多个任意字符
mysql> select * from t1 where name like 'a%';
+------+-------+------+------------+------+-----------------+---------+--------+--------+
| id   | name  | sex  | hire_date  | post | job_description | salary  | office | dep_id |
+------+-------+------+------------+------+-----------------+---------+--------+--------+
| NULL | alice | f    | 2021-09-01 | hr   | hrcc            | 7000.00 |    501 |    100 |
+------+-------+------+------------+------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)

//通配符_代表1个任意字符
select * from t1 where name like 'al_';

3.查询排序

示例1：以工资升序排序

select * from t1 order by salary ASC;

示例2：以工资降序排序

select * from t1 order by salary DESC;

示例3：工资最高的前五名

select * from t1 order by salary DESC LIMIT 5;