1.更改使用数据库 use database
2.创建表
create table user
(
id int,
name varchar(20),
password varchar(20),
birthday date
);
drop table if exists employee;
create table employee(
id int,
name varchar(20),
gender varchar(6),
birthday date,
entry_date date,
job varchar(30),
salary float(5,1),
resume text
);
3.插入数据
insert into user(id,name,password,birthday) values(1,'zs','123','1999-01-01');
4.查询数据
select * from user;
5.查询表结构
desc user;
6.修改表 其中不输入timestamp表示默认的当前时间
drop table if exists user;
create table user
(
id int,
name varchar(20),
password varchar(20),
birthday timestamp
);
添加列和属性
alter table employee
add image blob;
修改job属性和默认值
alter table employee
modify job varchar(40) default 'java';
删除gender列
alter table employee
drop gender;
表名修改
rename table employee to myemployee;
字符集修改
alter table myemployee
character set utf8;
修改列名
alter table myemployee
change column name username varchar(20);
7.创建/查看/修改/删除表
create table 表名
show create table 表名
desc 表名
drop table 表名
8.数据类型
tinyint/smallint/mediumint/int/bigint-->1B/2B/3B/4B/8Bfloat/double-->单精度/双精度浮点型
decimal-->不会产生精度丢失的单精度/双精度浮点型
date-->日期类型
time-->时间类型
datetime-->日期时间类型
year-->年类型
char-->定长字符串类型
varchar-->可变长字符串类型
tinyblob/blob/mediumblob/longblob-->255B/64K/16M/4G大小的图片/音乐等二进行数据
tinytext/text/mediumtext/longtext-->255B/64K/16M/4G大小的文本数据
values(5,'马利','2011-10-8','2011-12-31','software',5000.1,NULL);
//修改客户端输入和输出使用的编码方式,与WindowXP平台一致
set character_set_client=gbk;
set character_set_results=gbk;
将所有员工薪水修改为6000元。
update user set salary = 6000;
将姓名为’马利’的员工薪水修改为7000元。
update user set salary = 7000 where username = '马利';
将’jack’的薪水在原有基础上增加1000元。
update user set salary = salary + 1000 where username = 'jack';
删除表中名称为’jack’的记录。
delete from user where username = 'jack';
删除表中所有记录。
delete from user;
使用truncate删除表中记录。
truncate table user;
查询表中所有学生的信息。
select * from student;
select id,name,math,chinese,english from student;
select name,id,math,chinese,english from student;
select name,math from student;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
过滤表中重复数据。
select distinct english from student;
select distinct name,english from student;
在所有学生分数上加10分特长分。
select name,math+10 from student;
select name as 姓名,math+10 as 数学 from student;
统计每个学生的总分。
select name,math+chinese+english
from student;
使用别名表示学生分数。
select name,math+chinese+english as 总分
from student;
查询姓名为’张小明’的学生成绩
select *
from student
where name = '张小明';
查询英语成绩大于90分的同学
select *
from student
where english > 90;
查询总分大于200分的所有同学
select name,chinese+math+english as 总分
from student
where chinese+math+english > 200;
查询英语分数在 80-90之间的同学。
select *
from student
where english>=80 and english<=90;
或
select *
from student
where english between 80 and 90;
查询数学分数为89,90,91的同学。
select *
from student
where math=89 or math= 90 or math=91;
或
select *
from student
where math [not] in(89,90,91);
查询所有姓’李’的学生成绩。
select *
from student
where name LIKE '李%';
select *
from student
where name LIKE '%李';
select *
from student
where name LIKE '%李%';
---------------------------------------在网站中,多条件查询中用到
select *
from student
where name LIKE '%%';
select *
from student
where name LIKE '__李';
select *
from student
where math IS [NOT] NULL;
查询数学分>80且语文分>80的同学。
select *
from student
where math >80 and chinese>80;
对数学成绩排序后输出。
升序:
select *
from student
order by math asc;
降序:
select *
from student
order by math desc;
对总分降序后输出。
select name,math+chinese+english as 总分
from student
order by math+chinese+english desc;
对姓’李’的学生总分降序输出。
select name,math+chinese+english as 总分
from student
where name LIKE '李%'
order by math+chinese+english desc;
统计一个班级共有多少学生?
select count(*) as 总人数
from student;
统计数学成绩大于80的学生有多少个?
select count(*) as 总人数
from student
where math > 80;
统计总分大于250的人数有多少?
select count(*) as 总人数
from student
where (math+chinese+english) > 250;
select count(english) as 总人数
from student;//13
select count(math) as 总人数
from student;
统计一个班级数学总成绩。
select sum(math)
from student;
select sum(name)
from student;//0
统计一个班级语文、英语、数学各科的总成绩。
select sum(math) as 数学总分,sum(chinese) as 语文总分,sum(english) as 英语总分
from student;
统计一个班级语文、英语、数学的成绩总和。
select sum(math)+sum(chinese)+sum(english) as 班级总分
from student;
统计一个班级语文成绩平均分。
select sum(math)/count(math)
from student;
select sum(math)/count(*)
from student;
avg()
max(),min(),当max()和min()函数位于日期类型时,分别取得最近日期和最早日期
求班级语文最高分和最低分。
select max(name),min(name)
from student;
drop table if exists teacher;
create table teacher(
id int,
name varchar(20),
birthday date
);
insert into teacher(id,name,birthday) values(1,'jack','2011-1-1');
insert into teacher(id,name,birthday) values(2,'marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');
select max(birthday),min(birthday)
from teacher;
对订单表中商品归类后,显示每一类商品的总价
select product as 类别名,sum(price) as 商品类别总价
from orders
group by product;
查询购买了几类商品,并且每类总价大于100的商品
select product as 类别名,sum(price) as 商品类别总价
from orders
group by product
having sum(price) > 100;
where 和having区别:
where主要用于行过滤器
having主要用于类别过滤器,通常有having就一定出现group by,但有group by的地方,不一定出现having。
drop table if exists teacher;
create table teacher(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday date
);
insert into teacher(name,birthday) values(NULL,'2011-1-1');
insert into teacher(name,birthday) values('marry','2011-2-2');
insert into teacher(id,name,birthday) values(3,'sisi','2011-3-3');
select max(birthday),min(birthday)
from teacher;
删除主键,主键在表中只有一个,要么是一列,要么是多列
alter table teacher drop primary key;
一对一关系(方案一):
drop table if exists card;
drop table if exists person;
create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values('jack');
insert into person(name) values('marry');
create table card(
id int primary key auto_increment,
location varchar(20) not null,
pid int,
constraint pid_FK foreign key(pid) references person(id)
);
insert into card(location,pid) values('BJ',1);
insert into card(location,pid) values('GZ',2);
insert into card(location,pid) values('CS',NULL);
insert into card(location,pid) values('NJ',3);//出错
//删除person表的某记录
delete from person where name = 'jack';
一对一关系(方案二):
drop table if exists card;
drop table if exists person;
create table person(
id int primary key auto_increment,
name varchar(20) not null
);
insert into person(name) values('jack');
insert into person(name) values('marry');
create table card(
id int primary key auto_increment,
location varchar(20) not null,
constraint id_FK foreign key(id) references person(id)
);
insert into card(location) values('BJ');
insert into card(location) values('GZ');
insert into card(location) values('CS');//出错
insert into card(location) values(NULL);
一对多/多对一关系:
drop table if exists employee;
drop table if exists department;
create table department(
id int primary key auto_increment,
name varchar(20) not null
);
insert into department(name) values('软件部');
insert into department(name) values('销售部');
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
did int,
constraint did_FK foreign key(did) references department(id)
);
insert into employee(name,did) values('jack',1);
insert into employee(name,did) values('marry',1);
问题?查询"软件部"的所有员工(组合式)
select d.name as 部门名,e.name as 员工名
from department as d,employee as e
where d.name = '软件部';
思考:还有没有其它方法?
分解:
(1)select id from department where name='软件部';
(2)select name from employee where did = 1;
(总)嵌入式SQL
select name as 员工
from employee
where did = (
select id
from department
where name='软件部'
);
多对多关系:
drop table if exists middle;
drop table if exists student;
drop table if exists teacher;
create table if not exists student(
id int primary key auto_increment,
name varchar(20) not null
);
insert into student(name) values('jack');
insert into student(name) values('marry');
create table if not exists teacher(
id int primary key auto_increment,
name varchar(20) not null
);
insert into teacher(name) values('赵');
insert into teacher(name) values('蔡');
create table if not exists middle(
sid int,
tid int,
constraint sid_FK foreign key(sid) references student(id),
constraint tid_FK foreign key(tid) references teacher(id),
primary key(sid,tid)
);
insert into middle(sid,tid) values(1,1);
insert into middle(sid,tid) values(1,2);
insert into middle(sid,tid) values(2,1);
insert into middle(sid,tid) values(2,2);
问题?查询"赵"所教过的所有学员
select t.name as 老师, s.name as 学员
from teacher as t,student as s,middle as m
where t.name = '赵'and m.sid=s.id and m.tid=t.id;
模式:
select 列出需要显示的字段
from 列出所涉及到的所有表,建议写别名
where 业务条件 and 表关联条件
使用MySQL特有函数:
到年底还有几少天?
select datediff('2011-12-31',now());
截取字符串
select substring('mysql',1,2); //从1开始
保留小数点后2位(四舍五入)
select format(3.1415926535657989,3);
向下取整(截取)
select floor(3.14);
select floor(-3.14);
select floor(3.54);
select floor(-3.54);
取随机值
select format(rand(),2);
取1-6之间的随机整数值
select floor(rand()*6) + 1;
MySQL扩展知识:
查MySQL文档,利用MySQL的函数:随机产生'a'-'z'之间的随机字符。
随机产生'a'-'z'之间的随机字符
(1)查询'a'-'z'对应的Unicode值
select ascii('a');//97
select ascii('z');//122
(2)产生97-122之间的随机整数
select floor(rand()*26)+97;
(3)产生97-122对应的字符
select char(floor(rand()*26)+97);
查MySQL文档,利用MySQL的函数:对密码'123456'进行MD5加密。
select md5('123456');
drop table user;
create table user(
id int primary key auto_increment,
name varchar(20),
gender varchar(6),
salary float
);
insert into user(name,gender,salary) values('jack','male',4000);
insert into user(name,gender,salary) values('marry','female',5000);
insert into user(name,gender,salary) values('jim','male',6000);
insert into user(name,gender,salary) values('tom','male',7000);
insert into user(name,gender,salary) values('soso','female',NULL);
insert into user(name,gender,salary) values('haha','female',3500);
insert into user(name,gender,salary) values('hehe','female',4500);
select * from user;
MySQL特有流程控制函数:
1) if(value,第一值,第二值);
value为真,取第一值,否则取第二值
将5000元(含)以上的员工标识为"高薪",否则标识为"起薪"
类似于Java中的三目运算符
select if(salary>=5000,'高薪','起薪')
from user;
2) ifnull(value1,value2)
value1为NULL,用value2替代
将薪水为NULL的员工标识为"无薪"
select name as 员工,ifnull(salary,'无薪') as 薪水情况
from user;
3) case when [value] then [result1] else [result2] end;
当value表达式的值为true时,取result1的值,否则取result2的值(if...else...)
将5000元(含)以上的员工标识为"高薪",否则标识为"起薪"
select
case when salary>=5000 then '高薪'
else '起薪' end
from user;
4) case [express] when [value1] then [result1] when [value2] then [result2] else [result3] end;
当express满足value1时,取result1的值,满足value2时,取result2的值,否则取result3的值(switch...case..)
将7000元的员工标识为"高薪",6000元的员工标识为"中薪",5000元则标识为"起薪",否则标识为"低薪"
select
case salary
when 7000 then '高薪'
when 6000 then '中薪'
when 5000 then '起薪'
else '低薪' end
from user;