1. 数据库管理
1.1 查询所有数据库
show databases;
1.2 创建数据库
create database demo; //demo为创建的表名
1.3 查看数据库的默认字符集
show create database demo;
![](https://img.haomeiwen.com/i5207977/39da936b3c5218ee.png)
image.png
1.4 删除数据库
drop database demo;
1.5 修改数据库
alter database demo default character set gbk;
alter database demo default character set utf8;
![](https://img.haomeiwen.com/i5207977/fd1514dcd63fa340.png)
image.png
2. 表管理
2.1 查看所有表
use demo; //进入一个数据库
show tables; //查看该数据库的所有表
2.2 创建数据表
create table tb_demo( //tb_demo表名
id int, //字段名id,字段名类型int
name varchar(40) //字段名类型的长度 varchar(40)
);
![](https://img.haomeiwen.com/i5207977/7eca1221b4ffb63d.png)
image.png
2.3 查看表结构
desc tb_demo;
![](https://img.haomeiwen.com/i5207977/d1127e96bacadfd7.png)
image.png
2.4 删除表
drop table tb_demo; //删除数据库drop后面就加database,数据表就为table.
2.5 修改表
2.5.1 添加字段
alter table tb_demo add column password varchar(50);
![](https://img.haomeiwen.com/i5207977/7619fd98f0c808ef.png)
image.png
2.5.2 删除字段
alter table tb_demo drop column password; //删除字段可以不用添加字段类型
2.5.3 修改字段类型
alter table tb_demo modify column password varchar(40);
![](https://img.haomeiwen.com/i5207977/ced2e14c05e8af37.png)
image.png
2.5.4 修改字段名称
alter table tb_demo change column name username varchar(40);
![](https://img.haomeiwen.com/i5207977/d4af12a77417a095.png)
image.png
2.5.5 修改表名称
alter table tb_demo rename tb_test;
![](https://img.haomeiwen.com/i5207977/a0b1713abf34c023.png)
image.png
3.数据操作
3.1 增加数据
insert into tb_test values(1,'zhangsan','aaaa'); //按顺序添加,不能少字段多字段
insert into tb_test(username) values(2); //插入部分字段的写法
3.2 修改数据
update tb_test set username='lisi' where id=1; //id为1的学生,修改username为lisi.
![](https://img.haomeiwen.com/i5207977/bd6d0884314357fc.png)
image.png
3.3 删除数据
delete from tb_test;
delete from tb_test where id=1;
3.4 查询数据(重要!!!)
select * from tb_test;
![](https://img.haomeiwen.com/i5207977/aae45f35a039f5ef.png)
image.png
3.4.1 查询指定列
select id,username from tb_test;
![](https://img.haomeiwen.com/i5207977/cf5a1885980ffd40.png)
image.png
3.6 条件查询(where)
select * from tb_test where id=5 and password='cccc';
![](https://img.haomeiwen.com/i5207977/a1df9c1cdd0b0128.png)
image.png
select * from tb_test where id=5 or password='bbbb';
![](https://img.haomeiwen.com/i5207977/1192962862a19713.png)
image.png
查询年龄大于18小于20的数据
select * from tb_test where age>18 and age<20;
![](https://img.haomeiwen.com/i5207977/1bed62241e94509b.png)
image.png
查询年龄为空的数据
select * from tb_test where age is null;
![](https://img.haomeiwen.com/i5207977/21b8546ea5c63e89.png)
image.png
查询username中首字母是a的数据
select * from tb_test where username like 'a%';
![](https://img.haomeiwen.com/i5207977/6e03b2f38fabe7fe.png)
image.png
- 聚合查询 常用的聚合函数sum() avg() max() min() count()
查询平均年龄
select avg(age) as 'age' from tb_test;
![](https://img.haomeiwen.com/i5207977/1162ba82411dc5da.png)
image.png
查询最大年龄
select max(age) from tb_test: //另一种写法
![](https://img.haomeiwen.com/i5207977/3b07581eeac6b4f1.png)
image.png
统计个数
selecte count(id) from tb_test;
![](https://img.haomeiwen.com/i5207977/19074edbba17a5af.png)
image.png
- 查询排序 order by 字段 :asc: 顺序,desc: 倒序.
倒序排列
select * from tb_test order by id desc;-- 反序
![](https://img.haomeiwen.com/i5207977/557892a1f892e545.png)
image.png
网友评论