mysql 个人笔记

数据库应用：

查看系统日期时间：select now();
查看计算结果：select2*3,11%3,9/2;
查看系统常用命令：\h
查看系统参数设置：\s
清除命令：\c

20180907

1.启动、关闭MYSQL服务
（1)services.msc
(2)DOS命令
net start mysql
nest stop mysql

2.启动MYSQL
（1）双击MYSQL.exe
（2）打开命令提示符窗口
cd\
cd\program files\mysql\mysql server 5.5\bin
mysql -u root P
（3） 打开运行窗口
c:\program files\mysql\mysql server 5.5\bin\mysql -u root -p

3. 配置MYSQL
(1) set
9 set character_set_client=gbk;
set character_set_server=gbk:
(2)编辑 my.ini
default-character-set=gbk

4.select

20180911

创建数据库
create.database.xxxcharacter set =gbl
查看数据库
show databaseaj查看所有
show cerate database xxx,查看某一个
修改
alter database 数据库名称 default character set 编码方式 collate 编码方式_bin.1
删除数据库
drop database 数据库名称

20180918

复习
1.create database itcast;
2.use itcast;
3.create table tb_grade(id int(11),name varchar(20),grade float);
4.修改tb_grade:
alter table tb_grade rename to grade;//重命名
alter table grade change name username varchar(20);
alter table grade modify id int(20); //调整
alter table grade add age int(10);  //增加字段
alter table grade add stuid int(11) first;
alter table grade drop age
create table example01(id int primary key,nane varchar(20),grade float);
create table example02(stu_id int, course_id int,grade float,primary key(stu_id course_id));
grade float not null;
create table example04(id int primary key, name varchar(20)not null,grade float);

20180925

create database itcaat;
use itcast;
create table tl(id int,name varchar(20),grade flont,index(id));
create table t2(id int not null,name varchar(20) not null,score flpat,unique index unique_id(id asc));
create table t3(id int not null,name varchar(20) not mull,score float,fulltext index fulltext_name(id asc));
engine=MyISAM; //全文 
create table t5(id int,nane varchar(20),grade float,index multi(id.name(20))); //多列
create table t6(id int,space geometry not null,spatial index sp(space)engine=MyISAID);//空间
在表中创建索引
create index index id on book(bookid);
create unique index uniqueidx on book(bookid asc);
create index singledx on book(comment);
create fulltext index on book(info);
createspatial index on book(g）;

    create table book
	( g GEOMETRY not null,
    bookid int not NULL,
    bookname varchar(255)not NULL,
	authors varchar（255)not NULL,
	info varchar(255)NULL,
	comment varchar（255) NULL,
	publicyear year not NULL
	)enginc=MyISAM;

20180928

create database itcast;
use itcast;
create table book
	(bookid int not NULL,
	bookname varchar(255） not NULL,
	authors varchar(255) not NULL,
	info varchar(255) NULL,
	comment varchar(255)NULL
	publicyear year not NULL,
	space GEOMETRY not NULL)engine=MYISAM;
	
create index index_id on book(bookid);
create unique index uniqueidx on book(bookid);
create index singleidx on book(comment);
create index mulitidx on book(authors(20),info(20));
create fulltext index fulltextidx on book(info);
create spatial index spatidx on book(space);
alter table book add index index_id1(bookid);
alter table book add unique uniqueidx1(bookid);
alter table bookt add index singleidx1(comment(50));
alter table book add index multidx(authors(20),info（50));
alter table book drop index bookid;
drop index multidx on book;
create table student(id int(4),name varchar(20)not null,grade float);
insert into student(id, name, grade);
values(1,'zhangsan',98.5);
select*from student;

20181012

create database chap04;
use chap04;

create table student(
	id int(3) primary key auto_increment,
	name varchar(20) not NULL,
	grade float,
	gender char(2),
	class varchar(10)) character set =utf8;

insert into student(name,grade,gender,class)
	values( 'songjiang'，40，'男'，'应用1'），
	('何晓鹏'，88，' 男','应用5');
select*from student;
select name,grader from student;
select gender, name from student;
select id, name from student where id=4;
select name,gender from student where name='songjiang';
select id,grade, name,gender from student where  id in(1,2,3)

20181016

//mysql %和_
//与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少
 select name,grade ,class from student where name like "w__g";
 select id,grade ,gender from student where name like "%建%";
 select id,grade ,gender , name from student where name like "%建";
 
10. (1) insert into student(name,grade,gender,class)
values('sunx%bing_bing',89,'男',null);
	(2) update student set name="李_林" where id=9;
		update student set name="李%永" where id=13;
11. select name,grade ,class from student where name like "%\%%";
\%, \_
like --- % %建%，%建，_建，建% 建_

20181019

插入、修改
update student set name="李_林" where id=9;
update student set name="李%林" where id=13;
insert into student (name,grade,gender,class)
values('sunx%bing_bing',89,'男',NULL)；
slect*<字段名称>from<表名>where <>;
一、条件查询 "%建%" "建％" "%建" "_建" / "%\%%" "%_%"
1. select name,grade,class from student where name like"%\%%" or name
like"%_%";
2. select * from student where class="应用3" and gender="" and grade>85;

20181026

create database chap04;
use chap04;
select gender,count(*) from student group by gender;
select gender,avg(gradc) from student group by gender;
select class,avg(grade) from student group by class;

select class,grade,avg(grade) from student group by class having class is not null and avg(grade)>85;

select * from student order by grade desc limit 5;
select id,name,year(now())-year(hirthday)as 年龄 from student;//查询student表各记录的id,nane和年龄。
select gender,avg(year(now())-year(birthday)) from student group by gender;
select id.name.grade.clase from student where grade>(select avg(grade) from student);//查询student表中高于平均成绩的记录的idnamegrade和班级。

select gender,if(gender="男",1,0)from student;
select gender,if(gender="男",1,if（gender="女",2,0))from student ;
select idname,reverse(name),length(name),grade,class from student;

20181030

select class,max(score from score group by class;
select class,min(score from score group by class;
select class,avg(score) from score group by class;
select avg(score) from ScOre //77.0625
select class,avg(score) from score group by class having avg(score)<77.0625

20181102

多表操作
create database chap05;
use chap05;
create table grade(id int prinary key,name varchar(20));
create table student(sid int primary key, snane varchar(20),gid int not mull);
alter table student add constraint PK_ID foreign key(gid) references grade(id);
show create table student\G;
insert into student values(1701,'zhangyong' ,1);
alter table student drop foreign key PK_ID;
drop table grade;
insert into grade values(1,'应用一班'，（2,'应用二班'）;
insert into student values(1001,'王紅',1),(1002,'昊永',1）.（1003，'钱进'，2），（1004,'黄佳妮'，2）;
insert into grade values(3，'应用二班');
insert into student values(1005,'张红云',3);
delete from student where gid=1; //(1）先删除studert表中的“应用一班”的全部学生
delete from grade where name='应用一班';//（2）再删际grade表中班级名称为“应用一班”的记录
alter table student drop foreign key PK_ID;//删除原外键约束
alter table student add constraint abc foreign key(gid) references grade(id) on delete cascade on update cascade;//重新建立外键，带on delete /on update 参数
delete from grade where nane='应用二班'；//删除crade表中班级名称为“应用二班”的记录
update grade set id:4 where id:3;//更新erade表，将‘应用三班’的编号更改为4

20181113

create database chap0s
use chap05
1:
select e.id,e.nane,salary,d.name from department d join employee e on d.id=e.did;
select e.id,e.nane,salary,d.name from departnent d , employee e where d.id=e.did;
2:
select e.id,name,e.name,salary,d.name from department d , employee  e where d.id=e.did order by salary desc limit 3,//查询工资最高的前三名员工， 按工资阵序显示员工号，部门名，员工姓名和工资。
3:
select e.id,e.name, avg(salary),d.name from department d , employee e where d.id=e.did and salary>5000 order bysalary desc ;
//查询所有工资高于5000的的员工信息，包括员工编号，姓名，工资及部门名，并按工资降序显示；
4:
select d.name,avg(salary)from department d , employee e where d.id=e.did group by d.name;
//按部门(或性别)统计员工的平均工资，显示部门名，平均工资：
select gender,avg(salary) from employee group by gender;
5:
select d.name,avg(salary) from department d , employee e where d.id=e.did 
group by d.name having avg(salary)>(select avg(salary) from employee);
//5.查询部门平均工资高于总平均工资的部门名及部门平均工资
6：
select e.id,e.name,salary,d.name from department d , employee e where d.id=e.did 
and salary=(select max(salary) from employee);
//查询工资最高的员工的id,姓名，工竟及所在的部门名

20181115

1.查询存在年龄为22岁的员工的部门。(in)
select* from department where id in(select did from employee where age=22);
2. 查询不存在年龄为23岁的员工的部门。(not in)
select* from department where id not in(select did from employee where age=23);
3. 查询在employee表中是否存在年龄大于21岁的员工，如果存在. 则查询department 表中的所有记录。(exista)
select* from department where id exista(select did from employee where age>21)
4. 使用带ANY关键字的子查询，查询满足条件的部门。(any)
select* from department where id>any(select did from employee);
5. 使用带ALL关键字的子查询， 查询满足条件部门。(al1）
select* from department where id>all(select did from employee);
6. 使用带比较运算符的子查询，查询孙晓是哪个部门的员工
select* from department where id=(select did from employee where name “孙晓”);
7. 查询部门平均工资高于总平均工资的部门名及部门平均工资;
select d.name,avg(salary) from department d, employee e where d.id=e.did
group by e.did having avg(salary)>(select avg(salary) from emeloyee);
8. 使用子查询，查询工资最高的员工的id,姓名,工资及所在的部门名。
select e.id,e.name,galary,d.name from department d, employee where d.id=e.did
and salary(select max(salary) from employee);

20181116

1. 删除student表中的phone字段;
alter table student drop phone;
2. 在student表添加字段status（政治面貌）
alter table student add status varchar(20);
3.为学号19开头的所有记录的etatus 字段填上内容‘团员’ (查看更新的表中信息是否正确)
ubdate student set status='团员' where studentno like '19%';
4. 查询course表中的所有记录；
select * from course;
5. 查询student表中名字含有“山”的学生信息；
select * from student where snane like “%山%”
6. 查询student表中女生的人数，并起别名：性别、人数；
select sex ,count(sex) from student where sex="女”;
7. 删除student表中名字叫崔依歌的同学的记录;
delete from student where sname= 崔依歌”;
8. 按性别分组，求出student表中每组学生的平均年龄，并起别名：性别、平均年龄；
select sex, avg(year(now()-year(birthday)) from student group by sex;
9. 查询选修课程号为'c05109'的学生的学号、姓名和期末成绩；
select student.gtudentnosnane,courseno,final from student,score
where student.studentno=score.studentno and courseno='c05109';
10. 计算每个学生的总成绩，总成绩=平时成绩*40%+期末成绩+60%：
显示：学号，姓名，课程号、总成绩；
select student.studentno.sname.courseno,daily*0.4+final+0.6 from student,score
where student.studentno=gcore.studentno;

20181123

5. 创建 一个 过程proc5， 求长方形的周长和面积。
delimiter //
create Procecure proc05(a int, b int)
begin
declare c int;
declare s int;
set a=3,b=5;
set c=(a+b)*2;
set s=a*b;
select s,c;
end//
delimiter ;
call proc05(3,4)
6.定义 个过程proc6, 求两个数中的较大
delimiter //
create procedure proc6(a int,b int)
begin
if a>b then
select a;
else seleet b;
end if;
end//
delimiter ;
coll proc6(5,4）