11:33 2013/6/26 SQL语句: DML(Data Manipulation Language 数据操作语言)语句：insert update delete select

DDL(Data Difinition Language 数据定义语言)语句：create table/ create database/ drop database/ alter table      DCL(Data Control Language 数据控制语句)语句:

主键可以定义为自动增长， 注意主键类型必须是int create table d (  id int primary key auto_increment,  name varchar(20) );

insert into d(name) values(‘ccc‘);

insert into d(name) values(‘aaaa‘);

delete from d where id=4;

create table e (  id int,  name varchar(20) ); // 增加主键约束 alter table e modify id int primary key auto_increment;

// 创建联合主键 create table f (  firstname varchar(20),  lastname varchar(20),  primary key(firstname, lastname) );

insert into f values(‘zhao‘,‘nan‘); insert into f values(‘li‘, ‘nan‘);

最重要的约束  外键约束 create table husband (  id int primary key,  name varchar(20) );

create table wife (  id int primary key auto_increment,  name varchar(20),  husbandid int,  constraint husbandid_FK foreign key(husbandid) references husband(id) );

insert into husband (id,name) values(3,‘liuxiaoshuai‘);

insert into wife (name, husbandid) values(‘fengjie‘, 3);

delete from husband where id=3;

create table aaa (  id int

);

// 为aaa 加外键约束 alter table aaa add constraint husid_FK foreign key(husid) references husband(id);

// 删除外键约束 alter table aaa drop foreign key husid_FK;

2. 修改表 在上面员工表的基础上增加一个image列 alter table employee add image varchar(30);

修改job列，使其长度为60 alter table employee modify job varchar(60);

删除sex列。 alter table employee drop sex;

表名改为users。 rename table employee to users;

修改表的字符集为utf-8 alter table user character set utf8;

列名name修改为username alter table user change column name username varchar(20);

查看所有表 show tables;

查看表的创建语句 show create table user;

查看表结构 desc user;

删除 user 表 drop table user;

三、数据的增删改查 1. insert语句 employee.sql create table employee (  id int,  name varchar(20),  sex varchar(10),  birthday date,  salary float,  entry_date date,  resume text ); create table employee (  id int,  name varchar(20),  sex varchar(10),  birthday date,  salary float,  entry_date date,  resume text ); // 向员工表插入三条记录 insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(1,‘zjx‘,‘male‘,‘1991-10-08‘,9500,‘2010-2-18‘,‘good boy‘); insert into employee(id,name,sex,birthday,salary,entry_date,resume) values(4,‘赵楠‘,‘男‘,‘1986-11-23‘,3800,‘2011-2-18‘,‘绝种好男人‘);

// 查看表的所有记录 select * from employee;

// 查看数据使用的所有编码 show variables like ‘character%‘;

// 修改客户端的编码 为 gbk set character_set_client=gbk;

// 修改结果集的编码为gbk set character_set_results=gbk;

// insert 语句的其他写法 create table a (  username varchar(20),  password varchar(20) );

insert into a values(‘zs‘,‘111‘);

insert into a values(‘wangwu‘,‘2222‘),(‘lisi‘,‘3333‘);

insert a values(‘aaa‘,‘bbb‘);

insert a (username) values(‘aaa‘);

2. update语句

将所有员工薪水修改为5000元 update employee set salary=5000;

将姓名为’zhangsan’的员工薪水修改为3000元。 updata employee set salary=2000 where name=‘zhangsan‘;

将姓名为’lisi’的员工薪水修改为4000元,sex改为female。 update employee set salary=4000,sex=‘female‘ where name=‘wangwu‘;

将xiaohong的薪水在原有基础上增加1000元 update employee set salary=salary+1000 where name=‘xiaohong‘;

3. delete语句

删除表中name为’赵楠’的记录。 delete from employee where name=‘赵楠‘;

删除表中所有记录。 delete from employee;

使用truncate删除表中记录。  摧毁表再创建表 truncate employee;

4. select语句 student.sql create table student(  id int,  name varchar(20),  chinese float,  english float,  math float );

insert into student(id,name,chinese,english,math) values(1,‘a‘,89,78,90); insert into student(id,name,chinese,english,math) values(2,‘b‘,67,53,95); insert into student(id,name,chinese,english,math)values(2,‘c‘,67,53,95); insert into student(id,name,chinese,english,math) values(3,‘e‘,87,78,77); insert into student(id,name,chinese,english,math) values(4,‘f‘,88,98,92); insert into student(id,name,chinese,english,math) values(5,‘d‘,82,84,67); insert into student(id,name,chinese,english,math) values(6,‘g‘,55,85,45); insert into student(id,name,chinese,english,math) values(7,‘h‘,75,65,30);

查询表中所有学生的信息。 select * from student; 查询表中所有学生的姓名和对应的英语成绩。 select name,english from student;

过滤表中重复数据。 select distinct english from student;

在所有学生分数上加10分特长分。 select name,english+10,chinese+10,math+10 from student;

统计每个学生的总分。 select name,english+chinese+math as sum from student;

使用别名表示学生分数。

where 子句 查询姓名为李一的学生成绩 select * from student where name=‘李一‘;

查询英语成绩大于90分的同学 select * from student where english>90;

查询总分大于200分的所有同学 select name,english+chinese+math from student where english+chinese+math>200;

运算符 查询英语分数在 80－90之间的同学。 select * from student where english between 65 and 85; 查询数学分数为89,90,91的同学。 select name,math from student where math in(89,90,91); 查询所有姓李的学生成绩。 select * from student where name like ‘李%‘; // 查询姓李的两个字的学生 select * from student where name like ‘李_‘; 查询数学分>80，语文分>80的同学。 select * from student where math>80 and chinese>80; 查询英语>80或者总分>200的同学 select *,chinese+math+english from student where english>80 or chinese+english+math>200;

order by 子句 对数学成绩排序后输出。 select * from student order by math;

对总分排序后输出，然后再按从高到低的顺序输出 select *,chinese+math+english from student order by chinese+math+english desc; 对姓李的学生成绩排序输出 select * from student where name like ‘李%‘ order by chinese+math+english;

合计函数 count 统计一个班级共有多少学生？ select count(*) from student; 统计数学成绩大于90的学生有多少个？ select count(*) from student where math>90; 统计总分大于230的人数有多少？ select count(*) from student where chinese+math+english>230;

sum 统计一个班级数学总成绩？ select sum(math) from student; select sum(math,chinese,english)from student; select sum(math) from student; 统计一个班级语文、英语、数学各科的总成绩 select sum(math),sum(chinese),sum(english) from student; 统计一个班级语文、英语、数学的成绩总和 select sum(math+chinese+english) from student; 统计一个班级语文成绩平均分 select sum(chinese)/count(*) from student; 缺考的不参与计算 select sum(chinese)/count(*) from student; select sum(chinese)/count(chinese) from student;

avg 语文平均分 select avg(chinese) from student; select avg(chinese) from student; max/min 语文最高分 select max(chinese) from student; select max(chinese) from student; group by create table orders(  id int,  product varchar(20),  price float );

insert into orders(id,product,price) values(1,‘电视‘,900); insert into orders(id,product,price) values(2,‘洗衣机‘,100); insert into orders(id,product,price) values(3,‘洗衣粉‘,90); insert into orders(id,product,price) values(4,‘桔子‘,9); insert into orders(id,product,price) values(5,‘洗衣粉‘,90);

将商品归类 select * from orders group by product; select * from orders group by product; select * from orders group by product; select * from orders group by oId; 显示单类商品总结 select *,sum(price) from orders group by product; i 商品分类 显示单类商品总价大于100的 select *,sum(price) from orders group by product having sum(price)>100; select *, // 将单价大于20 的商品进行归类显示  按照价格排序 select * from orders where price>20 group by product order by price;

四、表的约束 我们可以在创建表的同时为字段增加约束，对将来插入的数据做一些限定

唯一约束  unique create table a (  name varchar(20) unique );

insert into a values(‘aaa‘); insert into a values(‘aaa‘); 错 name有唯一约束

非空约束 not null create table b (  id int,  name varchar(20) not null );

insert into b values(1,‘aaa‘);

insert into b (id) values(2); 错，name有非空约束

主键  每张表都应该有个主键 方便我们找到记录 主键必须有唯一约束、非空约束

主键约束 primary key create table c (  id int primary key,  name varchar(20) not null );

insert into c (id,name) values(1,‘aaaa‘);

insert into c(id,name) values(1,‘bbbb‘); 错，主键重复 insert into c(name) values(‘ccc‘); 错，主键不能为null

主键可以定义为自动增长， 注意主键类型必须是int create table d (  id int primary key auto_increment,  name varchar(20) );

insert into d(name) values(‘ccc‘);

insert into d(name) values(‘aaaa‘);

delete from d where id=4;

create table e (  id int,  name varchar(20) );