一.数据库命令:
1.查询当前服务器的所有命令
show databases;
2.创建一个新的数据库
create databases 数据库名称;
3.切换数据库
use databaseName;
4.删除数据库
drop database databaseName;
二.数据库中表的命令
1.创建一个表
比如创建一个老师表:
create table teacher (
id int primary key AUTO_INCREMENT comment '序号',
name varchar(10) not null comment '姓名',
cource varchar(10) comment '课程编号'
)
创建课程表
create table cources (
id int primary key AUTO_INCREMENT comment '序号',
name varchar(10) not null comment '课程姓名',
cource varchar(10) not null comment '课程编号'
)
2.插入数据:
insert into teacher values (1,"小陈","001");
insert into teacher values (2,"小王","002");
insert into cources values (1,"测试","001");
3.查询
单表查询
select * from teacher;这是查询teacher表中的所有字段和值
selid from teacher;这是查询teacher表中的id字段和值
select id,name from teacher;这是查询teacher表中的id,name字段和值
select name from teacher where id=1;这就是条件查询,需要查询id为1的老师姓名
select name from teacher where cource in ('001','002');查询课程编号为001,002的人的姓名
同理,cources表也是同样操作
组合查询
(1)内连接
select * from teacher a inner join cources b on a.cource =b.cource ;查询课程编号相同的teacher表的所有字段和数据
select a.name,b.name from teacher a inner join cources b on a.cource =b.cource ;查询teacher和cources表中课程编号相同的姓名
select a.name,b.name from teacher a inner join cources b on a.cource =b.cource where b.name='小陈' ;
(2)左连接和右连接
select a.name,b.name from teacher a left join cources b on a.cource =b.cource; select a.name,b.name from teacher a right join cources b on a.cource =b.cource;
查询时降序排序和升序排序,不设定默认升序排序
select * from teacher order by id desc;降序排序
select * from teacher order by id asc;升序排序
4.修改表中某个字段的值
update 表名 set 列名=xxx where 列名=zzz;
5.删除表
drop table teacher;
清空表的数据
truncate table teacher;