mysql表的操作
DML
在mysql管理软件中,DDL已经定义了数据库的结构,那么如何对其中的数据进行管理呢,可以通过SQL语句中的DML语言来实现对数据的操作,接下来要详细介绍DML语言。
语句 | 作用 |
---|---|
insert | 实现数据的插入 |
update | 实现数据的更新 |
delete | 实现数据的删除 |
插入数据insert
# insert into 表名 values (值1,值2.....);
insert into t2 values (1,"张三",19);
当不在库中的表时,一定要将库和表一起写出:
insert into sqlproject01.t2 values (2,"李四",24);
当插入的时候要插入很多的内容:
insert into t2 values (3,"aaa"),(4,"bbb"),(5,"eee");
逗号代表的是并列关系,小数点代表的是上下级关系。比如:
库名.表名 -->上下级关系
值1,值2---->并列关系
如何在表中插入某几个列的内容:
insert into school.student1 (id,name) values (1,"lisi"),(2,"zhangsan");
只在部分列中插入内容
更新数据update
更新表中的数据:
# 格式:update 表名 set 列名=值 条件 值=值1;
update school.student1 set name="张三" where id=1;
- 示例
如何更改数据库的管理员密码?
密码在mysql库的user表中,其中有root的帐号和密码。
select * from mysql.user;
update mysql.user set authentication_string=password("Mysql@Password123456") where user="root";
删除数据
# 格式:delete from 表名 where condition
delete from school.student1 where name="张三";
DQL
在mysql管理软件中,可以通过SQL语句中的DQL语言来实现数据的查询操作select.
- 准备环境
准备一张表,包含三列信息(id int 序号)(name varchar 姓名)(age int 年龄)
create table t1 (id int,name varchar(20),age int);
插入数据:
insert into t1 values (1,"张三",18);
insert into t1 values (2,"李四",23);
insert into t1 values (2,"王五",20);
- 简单查询
可以使用select来进行查询
# 格式:select 列名1,列名2..... from t1;
select * from t1;# *代表所有
select host,id,name,sex,age from t1;
- 单条件查询
如何查询某几行的信息?
select * from t1 where name="张三";
select name,post from employee5 where post="sale";
- 四则运算查询
select name,salary,salary* 14 from employee5;
- 条件查询
and并列条件,多个条件组织到一起
查询hr组中工资大于1000的人
select name,salary from employee where post="hr" and salary > 1000;
or满足一个就可以查询出来
select name,salary from employee where salary=6000 or salary=8000;
BETWEEN AND查询一个区间
select name,salary from employee where salary BETWEEN 5000 AND 15000;
select name,salary,office from employee where BETWEEN 5000 AND 15000;
# not BETWEEN AND 在范围之外
select name,salary,office from employee where not BETWEEN 5000 AND 15000;
# in 是什么什么
select name,salary from employee where salary in (4000,5000,6000);
# not in不在范围之内
select name,salary from employee where salary not in (4000,5000,6000);
IS NULL查看没有描述的
select name,job_description from employee where job_description is null;
IS NOT NULL查看非空的
select name,job_description from employee where job_description is not null;
LIKE模糊查询
# %代表的是任意长度的任意字符
select * from employee where name LIKE "a%";
# _代表一个长度的任意字符
select * from employee where name LIKE "a____";
查询排序
以年龄升序作为排列
order by 默认的是升序
select * from employee order by age asc;
以年龄降序作为排列
select * from employee order by age desc;
限制查询数量
select * from employee order by salary desc LIMIT 5;