MySQL数据表的CURD

一、数据表的CURD

1.create数据

创建一个员工表，新建employee表并向表中添加一些记录：

创建数据表：

?

1	create table employee(id int, name varchar(20), sex int, birthday date, salary double, entry_date date, resume text);

向数据表中插入数据：

?

1 2 3

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元：

?

1	update employee set salary=salary+500;

将王五的员工薪水修改为10000元，resume改为也是一个中牛：

?

1	update employee set salary=10000, resume='也是一个中牛' where name='王五';

结果如下：

3.Retrieve数据

查询员工的年收入：

?

1	select id, name as "名字", salary "月薪", salary*12 年薪  from employee where id <=1;

4.delete数据

删除表中姓名为王五的记录：

?

1	delete from employee where name='王五';   //注意from不能省略

删除表中所有记录：

?

1 2	delete from employee; truncate table employee; //无条件 效率高

二、综合示例

创建一个学生表：

?

1	create table student(id int, name varchar(20), chinese int, english int, math int);

 向数据表中插入数据：

?

1 2 3 4 5 6 7 8 9 10

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.查询表中所有学生的信息：

?

1	select * from student;

　　

2.查询表中所有学生的姓名和对应的英语成绩：

?

1	select name,english from student;

3.过滤表中重复数据：

?

1 2 3 4 5 6

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分特长分：

?

1	select name,english+10 from student;

5.统计每个学生的总分：

?

1	select name,english+chinese+math from student;

6.使用别名表示学生分数：

?

1 2	select name,english+chinese+math as 总分 from student; select name,english+chinese+math 总分 from student;

7.查询姓名为范冰的学生成绩：

?

1	select * from student where name='范冰';

8.查询英语成绩大于90分的同学：

?

1	select * from student where english>90;

9.查询总分大于250分的所有同学：

?

1	select * from student where english+chinese+math>250;

10.查询英语分数在 85－95之间的同学：

?

1 2	select * from student where english>=85 and english<=95; select * from student where english between 85 and 95;

11.查询数学分数为84,90,91的同学：

?

1 2	select * from student where math=84 or math=90 or math=91; select * from student where math in(84,90,91);

12.查询所有姓范的学生成绩：

?

1	select * from student where name like '范%';

13.查询数学分>85，语文分>90的同学：

?

1	select * from student where math>85 and chinese>90;

14.对数学成绩排序后输出：

?

1	select * from student order by math;

15.对总分排序后输出，然后再按从高到低的顺序输出：

?

1	select * from student order by math+chinese+english desc;

16.对姓范的学生成绩排序输出：

?

1 2	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.统计一个班级共有多少学生：

?

1	select count(*) from student;

18.统计数学成绩大于90的学生有多少个：

?

1	select count(*) from student where math>90;

19.统计总分大于250的人数有多少：

?

1	select count(*) from student where math+chinese+english>250;

20.统计一个班级数学总成绩：

?

1	select sum(math) from student;

21.统计一个班级语文、英语、数学各科的总成绩：

?

1	select sum(math), sum(chinese), sum(english) from student;

22.统计一个班级语文、英语、数学的成绩总和：

?

1 2	select sum(math+chinese+english)from student; select sum(math)+sum(chinese)+sum(english) from student;

23.求一个班级数学平均分：

?

1	select avg(math) from student;

24.求一个班级总分平均分：

?

1 2	select avg(math+chinese+english)from student; select avg(math)+avg(chinese)+avg(english) from student;

25.求班级最高分和最低分：

?

1	select max(math+chinese+english),min(math+chinese+english) from student;

　

四、分组数据

为学生表，增加一个班级列，练习分组查询。

?

1	alter table student add column class_id int;

更新表：

?

1 2 3

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;

求各个班级 英语的平均分：

?

1	select class_id, avg(english) from student group by class_id;

查出各个班的总分，最高分：

?

1	select class_id, sum(math+chinese+english),max(math+chinese+english) from student group by class_id;

查询出班级总分大于1300分的班级ID：

?

1	select class_id from student group by class_id having sum(math+chinese+english)>1300;

五、日期时间函数

MySQL里面时间分为三类：时间、日期、时间戳(含有时分秒的sysdate)，如执行：

?

1	select now(), year(now()) 年, month(now()) 月, day(now()) 日, date(now());

?

1	select CURRENT_DATE() , CURRENT_TIME(),  CURRENT_TIMESTAMP() from dual;

六、字符串相关函数

字符串拼接：

?

1	select concat('hello ', 'mysql ', 'haha ', 'hehe ') from dual;

日期转字符串：

在MySQL中进行日期转换需使用date_format()来代替。

?

1 2	select date_format(now(), '%Y-%m-%d') from dual; select date_format(now(), '%Y-%c-%d %h:%i:%s') from dual;

字符串转日期：

?

1	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中约束举例：

?

1	create table myclass (id INT(11) primary key auto_increment,name varchar(20) unique);