MySql笔记

auto_increment //自动排序编号 必须是int类型

alter table students rename to stu;
drop 数据名称;
add 数据名称 数据类型 是否为空;

foreign key(cpno) references course(cno)

select now();显示当前时间

use 数据库
source 路径 .sql

show variables like ‘char%’ #显示字符集
set …=utf8;
set name ‘utf-8’

show create table sc查外键的名字
alter table sc drop fortign key sc_ibfk_1;根据外键
drop table students;删除表

unique(sid)设置成唯一约束

select * from readers order by birthday ASC;将生日升序排序
select * from readers order by birthday desc；将生日降序排序

select distinct publish from books; #查不重复的出版社
select count(distinct publish)from books;#查不同出版社个数
select avg(price)from books;#所有书的平均价格

select min(birthday) from readers;#求年纪大的人

select bno,count() from borrow where year(lenddate)<2009 group by bno having count()>2;

select publish ,count(*) from books group by publish;//

select *from readers where rname=‘帅气攀’;

updata readers set spet=‘信息学院’ where rno=‘111’;

select lenddate from borrow,books,readers where readers.rno=borrow.rno and books.bno=borrow.bno and rname=‘王小丫’ and bname=‘复活’;

select rname,readers.rno,avg(price) from readers,books,borrow where readers.rno=borrow.rno and books.bno=borrow.bno group by readers.rno having avg(price)>30;

select readers.rname,spet,books.bno,lenddate from readers,borrow,books where readers.rno=borrow.rno and books.bno=borrow.bno and returndate is null;

select readers.rname,lenddate,limitdate from borrow join readers on readers.rno=borrow.rno join books on books.bno=borrow.bno where bname=“数据库设计凡人入门”;

select readers.rname,lenddate,limitdate from readers,borrow,books where readers.rno=borrow.rno and books.bno=borrow.bno and bname=“数据库设计凡人入门”;

select bname from books where bno=any(select bno from borrow where rno=any(select rno from readers where rname=‘李咏’));

select rname from readers where rno in (select rno from borrow where bno in (select bno from books where price between 20 and 40));

select lenddate from borrow where rno=any(select rno from readers where rname=‘汪函’) and bno=any(select bno from books where bname=‘三重门’);

select lenddate from borrow where rno in (select rno from readers where rname=‘汪函’) and bno in (select bno from books where bname=‘三重门’);

select *from books where csl<any(select csl from books where publish=‘北京希望出版社’);

select *from books where csl<(select min(csl) from books where publish=‘北京希望出版社’) or csl is null;

select publish,avg(price) from books group by publish having avg(price)>=all(select avg(price) from books group by publish);

select rname ,count(borrow.rno) as cnt from readers left join borrow on readers.rno=borrow.rno group by readers.rno having cnt >=all(select count (borrow.rno) from readers left join borrow on raders.rno=borrow.rno group by readers.rno);

select *from readers where rno in(select rno from borrow where lenddate=(select min(lenddate) from borrow) and bno=(select bno from books where bname=‘数据库系统概论’));

select *from books where exists (select *from borrow where borrow.bno=books.bno);

select *from books where not exists (select *from borrow where borrow.bno=books.bno);

select bno from borrow where exists (select *from readers where readers.rno=borrow.rno and rname=‘王小丫’);

select rname from readers where exists (select rno from b)

create table 清华大学出版图书表(bno varchar(8) primary key,classid char(5),banme

insert into 清华大学出版图书表 select *from books where publish=‘清华大学出版社’;

视图：
创建图书价格大于30的图书视图
create view VBook1 as select bno,bname,author,publish,price from books where price>30; (with check option)加上这个 后面更新的价格必须大于30才行。

update vbook1 set price=price*0.8 where bname=“JAVA入门”;
insert into vbook1(bno,bname,price) values(“123456”,“python精通”,200);
delete from vbook1 where bname=“python精通”;

多表视图的数据不能修改

create view ComputerStu as select *from students where sdept=“计算机系” with check option;

create view ComputerSC as select students.sname,sno,sc.cno,cname,score from students sc on students.sno=sc.sno join course on sc.cno=course.cno where sdept=“计算机系”;

create view LCView as select cno,cname,score from ComputerSC where sname=“刘晨”;
create view DeptCount as select sdept,sum from studrnts group by sdept;
create view PassedSTU as select students.sno,sname,avg(score) from students join sc.sno=students.sno group by students.sno having acg(score)>60

default 设置默认值

delimiter $ 将;号换成&
create procedure sp_search(in bbno varchar(8))
-> begin
-> select *from readers where rno in (select rno from borrow where bno=bbno);
-> end
-> $
call sp_search(‘sn9001’)$

create procedure sp_amount(in bbno varchar(8),out cnt int)
-> begin
-> select count(*) into cnt from borrow where bno=bbno group by bno;
-> end
-> $
call sp_amount(‘sn9001’,@rs)$
select @rs$

create procedure jiafa(in a int,in b int)
-> begin
-> declare c int;
-> if a>0 and b>0 then
-> set c=a+b;
-> else
-> set c=0;
-> end if;
-> select c;
-> end
-> $
call jiafa(1,2)

create procedure pro_x(in bbno varchar(8))
-> begin
-> declare p decimal(8,1);
-> select price into p from books where bno=bbno;
-> if p>50 then select “太贵了”;
-> else select “价格合适”;
-> end if;
-> end$
call pro_x(‘sn9001’)$

create user cpf@localhost identified by ‘123’; 创建用户 密码加密，安全系数高
insert into 创建用户 密码没有加密 不安全

grant select(sno,sname) on jxgl.* to cpf identified by ‘123’ with grant option;创建新用户并赋予权限

grant select(sno,sname) on jxgl.* to cpf with grant option;赋予权限.
all

with grant option 这个语句是允许将权限传递给别人

revoke all on . from ‘cpf’@‘localhost’; 收回所有权限

mysqldump -hlocalhost -uroot -proot jxgl students>d:\studentsbak.sql;

musqldump -uroot -proot>d:\1.sql;

enclosed by ‘’’ 每个字段用单引号括起来
lines terminat by ‘\r\n’ 换行用回车
fields terminated by ‘,’ 每个字段之间用逗号分开

进入数据库中：
select *from students into outfile ‘d:/stu.txt’ fields terminated by ‘,’ enclosed by “’” lines terminated by ‘\r\n’; 导出

load data infile ‘d:/stu.txt’ into table students fields terminated by ‘,’ enclosed by “’” lines terminated by ‘\r\n’; 导入

select @@ tx_isolation; 查询隔离级别
set session transaction isolation level read uncommitted;将隔离级别设成read uncommitted

start transaction; 开启事务 rollback 回滚事务，回到初始值

未提交读 ，改的值还没提交，另外一边可以看到修改的值。
提交读只有提交之后，另外一边才能读到结果
可重复读相当于下载到本地，另外一边怎么改都不会影响本地的值。