Mysql数据库学习

1.mysql -u root -p 
  root


创建一个名称为Mercurial的数据库。
//如果这个数据库事先就存在了，你还想叫这个名称
drop database Mercurial;//删除一个存在的数据库
create database Mercurial;
创建一个使用gbk字符集的Mercurial2数据库。
create database Mercurial2 character set gbk;
创建一个使用gbk字符集，并带校对规则的Mercurial3数据库。
create database Mercurial3 character set gbk collate gbk_chinese_ci; 




查看当前数据库服务器中的所有数据库
show databases;
查看前面创建的Mercurial2数据库的定义信息
show create database Mercurial2;
删除前面创建的Mercurial3数据库
drop database Mercurial3;
drop database Mercurial2;


要使用某个数据库
use 数据库名;


2.建表
   create table 表名(
列名1 数据类型,
列名2 数据类型
......
   );


   修改表的名称：Rename table 表名 to 新表名


   修改表的字符集： alter table 表名 character set 字符集名


   create table employee(
id int,
name varchar(10),
gender char(1),
birthday date,
enterdate date,
job varchar(20),shouw
salary double,
resume text
   );


   --查看这个表
desc employee;
   --显示所有表
show tables;
   --显示表的字符编码
        show create table employee;


在上面员工表的基本上增加一个image列。
alter table employee add image blob;
修改job列，使其长度为60。
alter table employee modify job varchar(60);
删除image列。
alter table employee drop image;
表名改为user。
rename table employee to user;
修改表的字符集为gbk
alter table user character set gbk;
列名name修改为username
alter table user change name username varchar(10);
alter table user change username name varchar(20);


3.数据操作语言
INSERT UPDATE DELETE


insert
字符和日期型数据应包含在单引号中。
插入空值，不指定或insert into table value(null)


1.客户端编码问题
--查看支持的变量有哪些
show variables like 'character%';


--设置客户端编码
set character_set_client=gbk;--设置客户端用什么编码
set character_set_results=gbk;--查看数据库时，显示编码


2.添加三个员工信息
 insert into user(id,name,gender,birthday,enterdate,job,salary,resume)values(1,'张三','男','1988-10-1','2013-12-31','android开发工程师',100000,'他是一个NB的工程师');
 insert into user values(2,'王二小','男','1992-9-20','2010-11-11','java架构师',20000,'这是一个顶级架构师');
 insert into user values(2,'小丽','女','1995-8-8','2014-9-11','android高级开发工程师',30000,'这是一个顶级安卓高手');
insert into user(username,password,email,birthday，description) values('李四','321456','[e-mail]','2014-9-11','李四是一个好人');



Update:


3.将所有员工薪水修改为5000元。
update user set salary=5000;
将姓名为’张三’的员工薪水修改为3000元。
update user set salary =3000 where name=‘张三';
将姓名为’王二小’的员工薪水修改为40000元,job改为ccc。
update user set salary=40000,job='ccc' where name='王二小';
将小丽的薪水在原有基础上增加1000元。
update user set salary=salary+1000 where name='小丽';
将小丽的id改为3
update user set id=3 where name='小丽菁';



Delete:  delete from 表名 where 条件
删除表中名称为’zs’的记录。
delete from user where name='张三';
删除表中所有记录。
delete from user;  --一行一行删除,效率不高
使用truncate删除表中记录。
truncate table user;//效率高



4.SELECT
用于查询
select 字段 from 表名 where 条件 group by 分组  having 分组后条件 order by 排序字段(asc|desc)


查询表中所有学生的信息。
select * from user;
查询表中所有学生的姓名和对应的英语成绩。
select name,english from student;
过滤表中重复数据。
过滤英语成绩中重复数据
select  distinct english from student;


在所有学生数学分数上加10分特长分。
select name,math+10 from student;   (update student set math=math+10 )
select name as '姓名',math+10  成绩 from student;
统计每个学生的总分。
select name,chinese+math+english 总分 from student;
使用别名表示学生分数。


查询姓名为王五的学生成绩
select * from student where name='王五';
查询英语成绩大于90分的同学
select name from student where english>90;
查询总分大于200分的所有同学
select name,math+chinese+english from student where math+chinese+english>200;




查询英语分数在 80－90之间的同学。
select * from student where english between 80 and 90;
select * from student where english>=80 and english<=90;
查询数学分数为89,90,91的同学。
select * from student where math=89 or math=90 or math=91;
select * from student where math in(89,90,91);
查询所有姓李的学生成绩。
select * from student where name like '李%';   //%代表任意的
查询数学分>80，语文分>80的同学。
select * from student where math>80 and chinese>80;





使用order by 子句排序查询结果

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




3.数据完整性:
保证插入的数据的正确性。


实体完整性
1  李四 20

通过主键来保证实体完整性(逻辑主键，实际主键)
主键的写法:
primary key,它特点是唯一性，非空性
create table teacher(
id int primary key,
name varchar(10)
);
insert into teacher values(1,'wyj');
insert into teacher values(2,'wyj4');
alter table teacher drop primary key;

域完整性
就是保证某个字段达到要求
not null:非空
unique：唯一
create table teacher(
id int ,
name varchar(10) not null,
primary key(id)
);


insert into teacher values(1,null);


参照完整性
一个表中的某列数据，对应的只能来自于另外一个表的某个列
点名册(包含所有的学生姓名)
成绩表(成绩表中的名字必须会在点名册中出现，否则这个学生就不是这个班级的)


在数据库表面如何实现参照完整性
1.一对多  (直接通过外键)
通过外键来实现
constraint FK_score_sid foreign key (sid) references student(sid);
  关键字      外键名        外键     字段    关键字     主表(主键)


create table student1(
id int primary key,
name varchar(20)
);


1  李四 
insert into student1 values(1,'李四');
2  张三
insert into student1 values(2,'张三');


//自动增长  auto_increment 
1  1   59
2  2   80
3  1   70


create table score(
scoreid int auto_increment primary key,
sid  int ,
scores double,
constraint FK_score_sid foreign key(sid) references student1(id)
)
//自动增长，所以id没写值
insert into score(sid,scores) values(1,90);
insert into score(sid,scores) values(2,80);


一对多:在多的一方加入一的一方的主键充当它的外键
多对多关系:往往通过中间表进行关系的关联


create table teacher3(
  tid int primary key,
  tname varchar(20)
);
insert into teacher3 values(1,'wyj');
insert into teacher3 values(2,'lc');


create table student3(
  sid int primary key,
  sname varchar(20)
);


insert into student3 values(20,'陈冠希');
insert into student3 values(21,'段正淳');
//联合主键  primary key(字段1,字段2)
create table t_s_r3(
  tid int ,
  ssid int ,
  primary key(tid,ssid),
  constraint FK_t_s_r3_tid foreign key (tid) references teacher3(tid),
  constraint FK_t_s_r3_sid foreign key (ssid) references student3(sid)
)


insert into t_s_r3 values(1,20);
insert into t_s_r3 values(1,21);
insert into t_s_r3 values(2,20);


1,20
1,21
2,20






 一对一:
基于外键的一对一




4.连接查询
select 字段 from 表名1  连接类型(内连接，左外联接，右外联接) 表名2 on 连接条件 where 条件......


交叉联接
CROSS JOIN
select * from customer cross join orders;
select * from customer , orders;


内联接
显式内联接:inner (outer) join 
select * from customer inner join orders on (customer.id=orders.customer_id) 
where customer.id=1;


隐式内联接
select * from customer ,orders where customer.id=orders.customer_id


左外联接:left (outer) join,就是左表中联不上的记录也会显示出来
显示出所有的客户，有订单的显示订单，没有订单的也要显示客户信息
select * from customer left join orders on (customer.id=orders.customer_id) ;


右外联接  right join(就会导致右表的记录都会显示出来)
显示所有的订单，没有指定客户的这个订单也会显示出来
select * from customer right join orders on (customer.id=orders.customer_id) ;




子查询
 查询“陈冠希”的所有订单信息
 select id from customer where name='陈冠希';
 select * from orders where customer_id=1;


 -- select * from orders where customer_id=(select id from customer where name='陈冠希');


       联合查询：union
将两个select 查出来的结果进行合并，就是重复记录只显示1遍.
查询订单金额>=200的，和陈冠希的订单
select * from orders where price>=200;//4
select * from orders where customer_id=(select id from customer where name='陈冠希');//3


 select * from orders where price>=200 
 union
 select * from orders where customer_id=(select id from customer where name='陈冠希');


5.报表查询
1.聚合函数:  max()最大值,  min()最小值, sum()求和,count()统计行数, avg()平均数


2.分组group by 
 分组后的条件having




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




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




求一个班级数学平均分？
select avg(math) from student;
求一个班级总分平均分
select (sum(chinese)+sum(english)+sum(math))/count(*) from student;
select avg(chinese+english+math) from student;




求班级总分，最高分和最低分
select max(chinese+english+math),min(chinese+english+math) from student;






create table ordersw(
id int,
product varchar(20),
price float
);


insert into ordersw(id,product,price) values(1,'电视',900);
insert into ordersw(id,product,price) values(2,'洗衣机',100);
insert into ordersw(id,product,price) values(3,'洗衣粉',90);
insert into ordersw(id,product,price) values(4,'桔子',9);
insert into ordersw(id,product,price) values(5,'洗衣粉',90);
对订单表中商品归类后，显示每一类商品的总价
select product,sum(price) from ordersw group by product ;
查询购买了几类商品，并且每类总价大于100的商品
select product from ordersw group by product having sum(price)>100; 




6.数据库备份：
mysqldump -u root -p test>test.sql