mysql快速掌握

创建一张表：

create table  IF NOT EXISTS `student`(
`id` INT UNSIGNED AUTO_INCREMENT,
`name` varchar(32),
`age` int ,
`score` double(4,1),
`birthday` date,
PRIMARY KEY (`id`)
)ENGINE=InnoDB CHARACTER SET = utf8;

添加：

 

INSERT INTO    study.student VALUES(1001,'王五',23,89,'1992-1-30');

 

修改：

UPDATE    `student` SET    `student`.`name`='飘雪' WHERE    `student`.id=1001;

删除：

DELETE    FROM    `student` WHERE    `id`=1004;

查询：

 

SELECT    *    FROM    `student`;

 

。。。。。。。。。更多例子：

 

-- DQL语句(查询)
-- 基础查询
select * from student;
-- 计算列
-- ifnull
select id,name,ifnull(score,0) from student;
-- 别名
select id as '学号',name as '姓名',ifnull(score,0) as '分数' from student;
-- 条件
-- null不会参与运算
select * from student where id = 6;
select * from student where score <> 60;
-- 分数在60以上,90以下的学生
select * from student where score between 60 and 90; -- [60,90]
-- 查询年龄是18或者22的学生
select * from student where age in(18, 22);
-- 查询姓名以J开头的学生
select * from student where name like '%j%';-- jack jackson ...
-- 查询出没有成绩的学生信息
select * from student where score is null;
select * from student where score is not null;
-- 逻辑运算
-- and or not
-- 查询出年龄大于19,成绩大于80的学生
select * from student where age > 19 and score > 80;
-- 查询出年龄大于19或者成绩大于80的学生
select * from student where age > 19 or score > 80;
-- 查询出姓名中不包含j的学生
select * from student where name not like '%j%';
-- 排序
select * from student order by score desc;
-- 查询出成绩大于等于60的学生的姓名,并按照成绩降序排序
select * from student where score >= 60 order by score desc, age desc;
-- 聚合函数(最好是对数值进行计算)
-- *代表的是任意一列
select count(*) from student;
select max(score) as '最高分' from student;
select min(score) as '最低分' from student;
select sum(score) as '总分' from student;
select avg(score) as '平均分' from student where score >= 60;
-- 分组
-- 查询的“列”一定能和分组的列产生逻辑关系
select avg(score) as '平均分', sex as '性别' from student where name not like
'%j%' group by sex having 性别 = 2;
-- 查询出大于平均成绩的学生?
select avg(score),* from student where score > avg(score);
select avg(score) as '平均分', count(*) as '人数', sex as '性别' from student
where score >= 60 group by sex having 人数 > 2;
-- 分页
-- limit 开始的索引,每页查询的条数;
-- 公式：开始的索引 = (当前的页码 - 1) * 每页显示的条数
select * from student limit 0,3; -- 第1页 (1-1)*3
select * from student limit 3,3; -- 第2页 (2-1)*3
select * from student limit 6,3; -- 第3页 (3-1)*3

 

。。。。。。。。附带：

 

-- DML语句(增删改)
-- 插入数据
insert into student(id,name,age,score,birthday)
values(1,'tom',20,98.7,'2000-12-12');
insert into student values(2,'jreey',19,100.0,'2001-07-09');
insert into student(id,name,age,birthday) values(3,'andy',19,'2001-05-09');
insert into student values(4,'jack',21,78.9,'1999-06-09'),
(5,'rose',20,78.9,'2000-05-09');
-- 删除数据(加上条件)
delete from student where id = 1;
truncate table student;
-- 修改数据(加上条件)
update student set name = 'jackson', score = 80.0 where id = 3;