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 数据控制语句)语句:
主键可以定义为自动增长
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
主键可以定义为自动增长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)
);
转载于:https://www.cnblogs.com/YingYue/p/3742063.html
