MySQL数据表的CURD
一、数据表的CURD
1.create数据
创建一个员工表，新建employee表并向表中添加一些记录：
创建数据表：
create table employee(id int, name varchar(20), sex int, birthday date, salary double, entry_date date, resume text);
向数据表中插入数据：
insert into employee values(1,'张三',1,'1983-04-27',15000,'2012-06-24','一个大牛'); insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-02-22',10000,'2012-07-24','一个中牛'); insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-08-28',7000,'2012-08-24','一个小虾');
结果如下：
2.update数据
将所有员工薪水都增加500元：
update employee set salary=salary+500;
将王五的员工薪水修改为10000元，resume改为也是一个中牛：
update employee set salary=10000, resume='也是一个中牛' where name='王五';
结果如下：
3.Retrieve数据
查询员工的年收入：
select id, name as "名字", salary "月薪", salary*12 年薪 from employee where id <=1;
4.delete数据
删除表中姓名为王五的记录：
delete from employee where name='王五'; //注意from不能省略
删除表中所有记录：
delete from employee; truncate table employee; //无条件 效率高
二、综合示例
创建一个学生表：
create table student(id int, name varchar(20), chinese int, english int, math int);
向数据表中插入数据：
insert into student(id,name,chinese,english,math) values(1, '范建',80,85,90); insert into student(id,name,chinese,english,math) values(2,'罗况',90,95,95); insert into student(id,name,chinese,english,math) values(3,'杜子腾',80,96,96); insert into student(id,name,chinese,english,math) values(4,'范冰',81,97,85); insert into student(id,name,chinese,english,math) values(5,'申晶冰',85,84,90); insert into student(id,name,chinese,english,math) values(6,'郝丽海',92,85,87); insert into student(id,name,chinese,english,math) values(7,'郭迪辉',75,81,80); insert into student(id,name,chinese,english,math) values(8,'拎壶冲',77,80,79); insert into student(id,name,chinese,english,math) values(9,'任我行',95,85,85); insert into student(id,name,chinese,english,math) values(10,'史泰香',94,85,84);
执行结果如下：
三、基础的SQL
1.查询表中所有学生的信息：
select * from student;
2.查询表中所有学生的姓名和对应的英语成绩：
select name,english from student;
3.过滤表中重复数据：
select english from student; select DISTINCT english from student; select DISTINCT english,name from student; select english+chinese+math from student; select english+chinese+math as 总分 from student; select name,english+chinese+math as 总分 from student;
4.在所有学生英语分数上加10分特长分：
select name,english+10 from student;
5.统计每个学生的总分：
select name,english+chinese+math from student;
6.使用别名表示学生分数：
select name,english+chinese+math as 总分 from student; select name,english+chinese+math 总分 from student;
7.查询姓名为范冰的学生成绩：
select * from student where name='范冰';
8.查询英语成绩大于90分的同学：
select * from student where english>90;
9.查询总分大于250分的所有同学：
select * from student where english+chinese+math>250;
10.查询英语分数在 85－95之间的同学：
select * from student where english>=85 and english<=95; select * from student where english between 85 and 95;
11.查询数学分数为84,90,91的同学：
select * from student where math=84 or math=90 or math=91; select * from student where math in(84,90,91);
12.查询所有姓范的学生成绩：
select * from student where name like '范%';
13.查询数学分>85，语文分>90的同学：
select * from student where math>85 and chinese>90;
14.对数学成绩排序后输出：
select * from student order by math;
15.对总分排序后输出，然后再按从高到低的顺序输出：
select * from student order by math+chinese+english desc;
16.对姓范的学生成绩排序输出：
select * from student where name like '范%' order by math+chinese+english desc; select name, math+chinese+english from student where name like '范%' order by math+chinese+english desc;
17.统计一个班级共有多少学生：
select count(*) from student;
18.统计数学成绩大于90的学生有多少个：
select count(*) from student where math>90;
19.统计总分大于250的人数有多少：
select count(*) from student where math+chinese+english>250;
20.统计一个班级数学总成绩：
select sum(math) from student;
21.统计一个班级语文、英语、数学各科的总成绩：
select sum(math), sum(chinese), sum(english) from student;
22.统计一个班级语文、英语、数学的成绩总和：
select sum(math+chinese+english)from student; select sum(math)+sum(chinese)+sum(english) from student;
23.求一个班级数学平均分：
select avg(math) from student;
24.求一个班级总分平均分：
select avg(math+chinese+english)from student; select avg(math)+avg(chinese)+avg(english) from student;
25.求班级最高分和最低分：
select max(math+chinese+english),min(math+chinese+english) from student;
四、分组数据
为学生表，增加一个班级列，练习分组查询。
alter table student add column class_id int;
更新表：
update student set class_id=1 where id<=5; update student set class_id=2 where id>5; update student set class_id=2 where id between 6 and 10;
求各个班级 英语的平均分：
select class_id, avg(english) from student group by class_id;
查出各个班的总分，最高分：
select class_id, sum(math+chinese+english),max(math+chinese+english) from student group by class_id;
查询出班级总分大于1300分的班级ID：
select class_id from student group by class_id having sum(math+chinese+english)>1300;
五、日期时间函数
MySQL里面时间分为三类：时间、日期、时间戳(含有时分秒的sysdate)，如执行：
select now(), year(now()) 年, month(now()) 月, day(now()) 日, date(now());
select CURRENT_DATE() , CURRENT_TIME(), CURRENT_TIMESTAMP() from dual;
六、字符串相关函数
字符串拼接：
select concat('hello ', 'mysql ', 'haha ', 'hehe ') from dual;
日期转字符串：
在MySQL中进行日期转换需使用date_format()来代替。
select date_format(now(), '%Y-%m-%d') from dual; select date_format(now(), '%Y-%c-%d %h:%i:%s') from dual;
字符串转日期：
select str_to_date('2013-6-04 05:14:15' , '%Y-%c-%d %h:%i:%s') from dual;
七、数据表的约束
- 定义主键约束 primary key: 不允许为空，不允许重复
- 定义主键自动增长 auto_increment
- 定义唯一约束 unique
- 定义非空约束 not null
- 定义外键约束 constraint ordersid_FK foreign key(ordersid) references orders(id)
- 删除主键：alter table tablename drop primary key ;
MySQL中约束举例：
create table myclass (id INT(11) primary key auto_increment,name varchar(20) unique);
