1、数据库操作
创建:creat database xxx;
使用:use xxx;
删除:drop database xxx;
显示:show databases;
2、数据类型
数值型: 1、整型 bit :设置boolean时,设置bit,默认长度1,值为0和1
int:对应java的Integer
2、浮点型 float double
decimal(M,D) M是总长度,D是小数点位数
字符串类型
1、varchar(size):普通字符类型
2、text:大文本
日期类型:
datetime
timestamp:java.util.Date
3、表的操作:
创建 : create table xxx(
cloumn1 type,
cloumn2 type;
.....
)
删除: drop table xxx;
显示:show table xxx;
4、使用数据库:use xxx;
===================================
create table student(
sn int,
name varchar(20),
account decimal(11,2),
sgroup int
);
1、新增:(插入了一行数据) (insert into...values..)
insert into student(sn,name,account,sgroup) values
(1,'孙悟空',100.50,3);
(插入多行数据)
insert into student(sn,name,account,sgroup) values
(2,'猪八戒',52.1,3),
(3,'沙和尚',99,3),
(4,'唐僧',21.01,2);
2、查询 (select ...from...)
select * from student;
(*代表所有的列)
select sn,name,account,sgroup from student;
(和带*的方法等价,都是查出所有列)
如果只想查姓名和年级则:
select name,sgroup from student;
select account+200 from student;
(则表头的名字会变成account+200,且下面的值都会+200)
别名:
select account+200 as acc from student;
select account+200 acc from student;
(则表头的名字会变成acc,下面的值依然会都+200)
3、去除重复
select distinct sgroup from student;
(本来是3,3,2,去重后显示3,2);
4、排序
asc 升序
desc 降序
例如把语文成绩按升序排列:
select * from exam_result order by chinese desc;
(如果末尾不写acs或者desc,则默认为acs升序)
5、条件查询
where 条件查询
例如查询语文成绩在八十分以上的:
select * from exam_result where chinese>80;
查总分:
select id,name,chinese+math+english from exam_result;
查总分在200分以上的:
select id,name,chinese+math+english from exam_result where chinese+math+english>200;
查询语文成绩在70到80之间的
select * from exam_result where chinese between 70 and 80;
查询学号是2和4 的学生:
select * from student where sn in (2,4);
查询id是空的:
select * from exam_result where id is null;
查询id不是空的:
select * from exam_result where id is not null;
查询叫孙悟空的同学:
select * from exam_result where name='孙悟空';
查询姓孙的同学(模糊查询):
select * from exam_result where name like '孙%';
查询语文成绩大于70,数学成绩小于80分的同学:(多条件查询):
select * from exam_result where chinese>70 and math<80;
where 条件1 and 条件2
where 条件1 or 条件2
where 条件1 and (条件2 or 条件3)
查询满足语文成绩大于70并且(数学成绩小于85分或者英语成绩大于60)的人:
select * from exam_result where chinese>70 and (math<85 or english>60);
6、分页查询:limit
limit n 从0开始
select * from exam_result limit 3;
(从0开始筛选3条结果)
select * from exam_result limit 2,3; == select * from exam_result limit 2 offset 3;
(从2开始筛选3条件结果) (2实际上是从第三行开始,和数组一样)
查询满足(**德或是孙**) 并且(数学成绩大于75分或者英语小于70)的:
select * from exam_result where (name like '%德' or name like '孙%') and (math>75 or english<70);
select * from exam_result where name like '孙_';
(只能查出孙权,查不出孙悟空,因为_代表一个字符)
7、修改数据
(唐三藏的id为null,把它的id修改为1):
update exam_result set id=1 where name='唐三藏';
(唐三藏的id为null,把它的id修改为1,语文成绩改成70):
update exam_result set id=1,chinese=70 where name='唐三藏';
(唐三藏的id为null,把它的id修改为1,语文成绩改成数学成绩减去20):
update exam_result set id=1,chinese=math-20 where name='唐三藏';
8、删除数据:
(删除id为3的):
delete from exam_result where id=3;
(删除全部):
delete from exam_result;