添加 删除 小操作

create table Student(
StudentNo int(4) not null comment '学号',
LoginPwd varchar(20) null comment '',
StudentName varchar(20) null comment '学生姓名',
Ses tinyint(1) null comment '性别，取值0或1',
GradeId int(11) null comment '年级编号',
Phone varchar(50) not null comment '联系电话，允许为空，即可选输入',
Address varchar(255) not null comment '地址，允许为空，即可选为空',
BornDate varchar(50) null comment '出生时间',
Email varchar(50) not null comment '邮箱账号，允许为空，即可选输入',
IdentityCard varchar(18) null comment '身份证号'
);

create table result(
StudentNo int(4) not null comment'学号',
SubjectNo int(4) not null comment '课程编号',
ExamDate datetime not null comment '考试日期',
StudentResult int(4) not null comment '考试成绩'
);

alter table Student rename as students;
alter table students add Address VARCHAR(256) not null;
alter table students MODIFY Address VARCHAR(256) null;
alter table students CHANGE Address area VARCHAR(256) null;
alter table students drop area;

drop table students;
create table students(
id int(4) primary key auto_increment,
name char(20),
age int(4),
salary double,
denger char(4)
);
insert into students (name) values('张三');
insert into students values (3,'zhangsan',22,800,'男');
insert into students (name,age) values('王三',20);
INSERT into students (name,age) value('lisi',29),('zhaoliu',50);

update students set salary=0 where name='张三';
update students set salary=200 where name='张三';
update students set salary=salary+500;
update students set salary=0 where name='王三';
update students set salary=0 where name='lisi';
update students set salary=0 where name='zhaoliu';

update students set salary =salary+100 where name='lisi' and name='zhaoliu';
update students set salary=salary+500,age=age+10;
update students set age=age+2 where age>=30 and age<50;
DELETE from students where age=34 or age=32;
delete from students;##
TRUNCATE table students;## 删除数据 不能删除结构 不记录日志 速度快 无法恢复 会影响自增主键的值，从1重新开始，delete 不会从1开始 默认从上次最大值＋1开始

alter table students modify age not null;

update students set age=30 where name='lisi';
desc students;

select * from students;

 

 

 

##dcl命令 grant revoke commit ROLLBACK
##赋权
create user 'admin' IDENTIFIED by 'admin';
show grants for admin;## 查询用户拥有哪些权限
grant select,insert,update on 库名.* to admin;##指定数据库下所有表的查询，新增，修改权限

grant all PRIVILEGES on *.* to admin; ##给admin所有库 所有表 的所有权限；

flush PRIVILEGES; ##刷新权限

revoke SELECT,drop on 库名.* from admin; 回收权限;

##事务操作
## 1 关闭自动提交
set autocommit=0; ##FALSE
## 2开始一个事务，设置一个事务的起点
start TRANSACTION;
## 3xie SQL
## 4 提交或者回滚事务
commit ROLLBACK
## 5设置事务自动提交，恢复默认状态
set autocommit=1## TRUE  注意 rollback 只能回滚  DML语言 即 insert  update delete

##备份
##mysqldump -u 用户名 －p -c 数据库名称 表名1 表名2> 备份文件路径及文件名
##mysqldumo -u 用户名 －p -c 数据库名称 > 备份文件路径及文件名
##恢复
## mysql命令行下
source 备份文件路径及文件名
## cmd 终端模式下
mysql -u root -p java1708 < 文件路径及文件名

 

 

 

select * from students;
select students.* from students;

select * from students where salary is not null and id between 6 and 8;
select * from students where salary is not null and id in (6,7,8); in ##gu ding zhi

select * from students where salary is not null and id not between 6 and 8;
select * from students where salary is not null and id not in (6,7,8);

select * from students order by age asc; ## asc 升序排序 默认升序
select * from students order by age desc ## jiang xu pai xu
select * from students order by salary desc ,age desc; ##  先排序前面的 后排序后面的

insert into students (name) values ('aaa'),('ccc');

create table score(
student_id int not null;
subject_name varchar(10) not null;
score double,
foreign key(student_id) references students(id)
);

insert into score values (8,'java',90);
insert into score values(9,'java',80);
insert into score values (5,'java',80);
insert into score values (6,'java',80);
insert into score values (10,'java',80);
insert into score values (11,'java',80);

select students.id,students.name,score.subject_name,score.score
from students ,score
where students.id = score.student_id;

select s.id,s.name,c.subject_name,c.score ## as 给表 字段 起别名 as 可以省略
from s ,c
where s.id = c.student_id;

select students.id,students.name as 姓名,score.subject_name as 科目名称,score.score as 成绩
from students ,score
where students.id = score.student_id;

select * from students;
desc students;

create table information(
id int primary key auto_increment;
message varcharI(64)
):

use java1708;
insert into information(message) values('zhangsan'),('zhangsan2abcdefff'),('zhangsan3afff');
insert into information(message) values('wangsan1abcde'),('lisanabcdefff'),('zhangzhang');

select * from information;

select * from information where message like '%zhangsan%'; 查询含有zhangsan
select * form information where message like '%f%'; 含有f
select * from information where message like 'wang%'; 以wang 开头
select * from information where message like '__s%'; 第三个字母是s

select count(*) from information;
select * from information limit 5; 显示前5条数据
SELECT * FROM information LIMIT 5,5; 显示第六条到第十条
SELECT * FROM information LIMIT 10,5 显示第11条 到第 十五条

 

 

select student.name,student.age,score.subject_name,score.score
from student,score
where student.id =score.student_id
and student.age between 12 and 45
order bu student.id;

 

 

 

 

select student.name,student.age,score.subject_name,score.score
from student inner join score
on student.id = score.student_id
and student.age between 12 and 45
order by student.id ;

select student.name,student.id,score.score
from student left join score ##左连接 把把左边的数据全部显示出来
on student.id =score.student_id
order by student.id;

select student.name,student.id,score.score
from student right join score ##右连接 把把右边的数据全部显示出来
on student.id =score.student_id
order by student.id;

##创建一个科目表 科目编号 科目名称 课时
create table subject (
id int primary key auto_increment,
name varchar(16) not null,
classTime int
);

## 修改成绩表结构 学生学号 科目编号 成绩
alter table score change subject_name subject_id int;##假如之前的 subject_name有值 需要全部删除 否则修改不了

select student.name,subject.id,subject.name,score.score
from students join score on student.id=score.student_id
join subject on score.subject_id=subject.id
where score>=60
order by score desc;

select student.name,subject.id,subject.name,score.score
from students left outer join score on student.id=score.student_id
left outer join subject on score.subject_id=subject.id
where score>=60 or score is null
order by score desc;


use MySchool;
##员工表 员工编号 员工姓名 工资 上级领导编号
create table employee(
id int primary key auto_increment,
name varchar(36) not null,
salary double,
pid int
);

insert into employee(name,salary,pid )values('平董事长',10000,0),('张钦佩',9000,1),('胡俊浩',8000,2),('歪哥',8000,2);

## 查询 员工姓名 员工工资 上级领导名字
select a.name,a.salary,b.name
from employee a,employee b
where a.pid=b.id ;

##查询歪哥姓名，工资 上级领导名字
select a.name,a.salary,b.name
from employee a,employee b
where a.pid=b.id and a.name='歪哥';

 

 

## union  合并数据
select name,salary from emplyoee union select name,salary from students;
## group by
## 根据科目名称，分别统计每个科目的最高分 最低分 平均分 max(),min(),avg(),sum(),count()

insert into score select * from score;

select subject.name,max(score),min(score),avg(score ）平均分
from subject left outer join score
on subject.id=score.subject_id
where subject.name <> 'php'
group by subject.name ##group by 后跟分组条件
having 平均分>80 ##当 查询时候有函数的时候 这里不能用where having 通常用于对统计函数执行结果进行删选 where后面不能写统计函数
order by 平均分 desc ; ## order by 后面可以写 别名
limit 1; ## 显示第一条数据

##mysql select a,b,count(*) sum(*) group by
select subject.id '科目编号',subject.name '科目名称',max(score),min(score),avg(score ）平均分
from subject left outer join score
on subject.id=score.subject_id
group by subject.id,subject.name; 除了统计函数之外，最好跟上 查询上所有的要查询的字段

##子查询 统计平均分大于80分的学生信息（姓名 学号 年龄） students subject score
select * from students where

##查询 java 课程并且分数大于 80 的学生学号
select score.student_id
from score join subject
on score.subject_id=subject.id
where subject.name='java ' and score>80

##子查询
select score.student_id
from score
where score>80 and score.subject_id in (select id from subject where name=='java')

select * from students where id in (
select score.student_id
from score join subject
on score.subject_id=subject.id
where subject.name='java ' and score>80
);

select * from students
where id in(
select score.student_id
from score
where score>80 and score.subject_id
in(select id from subject where name='java')
);

select count(*) from students; ##count(*) 一般用于统计表中记录的行数
select count(salary) from students;## count(字段名) 统计该字段非空的记录总数
select * from students;

##通过创建视图 展示员工编号 姓名 上级领导编号 视图中只定义了结构，并不存储数据，主要用来查询，数据在表中
create view vm_score as select id,name,pid from employee;
select * from vm_score;

## 通过视图存储复杂sql
create view myView01 as select * from students
where id in(
select score.student_id
from score
where score>80 and score.subject_id
in(select id from subject where name='java')
);

 

select stu_id from (select * from score where c_name ='计算机' and grade>80) a where a.stu_id in(select stu_id from score where grade>80 and c_name='英语')