mysqlデータベース常用sqlの分類整理
17999 ワード
*******************************************************************************************************************************
*MySQL
1. (RDBMS,Relational Database Management System)
2. SQL ( ,Structured Query Language)
*SQL ：
1.DML(Data Manipulation Language, )--------
2.DDL(Data Definition Language, )--------- , 、
3.DCL(Data Control Language, )---------
* CMD MySQL
1.mysql -h Mysql -u -p ======= ( MySQL )
2.show databases; ======= ( )
3.use ; ======= ( )
4.create database default character set 'utf8'; ======= ( (SQL))
5.show tables; ======= ( )
6.show create database ; ======= ( )
7.desc ; ( describe ); ======= ( )
8.show create table ; ======= ( )
*******************************************************************************************************************************
* SQL
1.
create table (
1 [primary key auto_increment],
2 [not null],
...
n [ ]
);
：
：
integer (int)
float( , )
double( , )
decimal( , )
：
varchar(n)
char(n)
2.
drop table ;
3. ( )
insert into ( 1, 2,...)values( 1, 2,...);
insert into values( 1, 2,...);
SQL ：
insert into ( 1, 2,...)values( 1, 2,...),( 1, 2,...)...;
insert into values( 1, 2,...),( 1, 2,...)...;
4. ( )
update set 1= 1 [where ] [order by [desc]] [limit ];
5. ( )
delete from [where ] [order by [desc]] [limit ];
6. ， delete from ，
truncate table ; ======》 ， 。
7.
select 1, 2... from [where ];
:
select 1 as 1, 2 as 2... from [where ];
*******************************************************************************************************************************
* SQL
1. ( )
alter table add column [ ];
2. ( )
alter table modify column [ ];
3. ( )
alter table change column [ ];
4. ( )
alter table drop column ;
*******************************************************************************************************************************
*******************************************************************************************************************************
*******************************************************************************************************************************
*
1.not、or、and
and or where ， and 。
eg: remark null
select * from student where remark is not null;
2. (like)
：%
eg: ' '
select * from student where name like '% %';
_
eg: ' '
select * from student where name like '_ %';
3.
order by 1[desc], 2[desc]...;
4.
limit ;
limit ( 0 ), ;
eg:
student
select * from student limit 3;
2 4
select * from student limit 1,3;
;
select * from student order by score desc limit 3;
*********************************************************************************************************************************
*
1.
max( ) eg: select max(price) from product;
min( ) eg: select min(price) from product;
sum( )
avg( )
count(*)
count( ) null
2.
group by 1, 2 [having ]
： ，select group by ， ，
select ( )。
， 。
eg:
， ：
select kind,avg(price) from product group by kind;
。
select kind,avg(price) from product group by kind having kind=' ';
*********************************************************************************************************************************
* ( )
1.any(some)
any ， 。
2.all
all ， 。
3.exists
， true.
not exists ， true.
4.in
, 。
not in
*********************************************************************************************************************************
*
1.
select 1, 2... from 1, 2 where ;
2.
(left join): ， 。
select 1, 2... from left join on ;
eg:
select student.name,school.school_name from student left join school
on student.school_id=school.id;
(right join): ， 。
select 1, 2... from right join on ;
3.
select 1, 2... from 1 inner join 2 on ;
*********************************************************************************************************************************
* (Foreign Key)
“ ” 。
， 。
create table emp(
id int primary key auto_increment,
emp_name varchar(10) not null,
dept_id int not null,
constraint fk_emp foreign key(dept_id) references dept(id)
);
alter table emp drop foreign key fk_emp; //
*********************************************************************************************************************************
/*
eg:
create table school(
sid int primary key auto_increment,
sname varchar(20) not null
);
insert into school(sname) values(' '),(' ');
create table teacher(
tid int primary key auto_increment,
tname varchar(20) not null,
school_id int not null,
constraint fk_teacher foreign key(school_id) references school(sid)
);
insert into teacher(tname,school_id) values(' ',1);
insert into teacher(tname,school_id) values(' ',2);
create table subject(
sid int primary key auto_increment,
sname varchar(20) not null,
teacher_id int not null,
constraint fk_subject foreign key(teacher_id) references teacher(tid)
);
insert into subject(sname,teacher_id) values(' ',2);
insert into subject(sname,teacher_id) values(' ',1);
*/
*********************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
* (transaction)
， ， ， 。
(ACID)：
1. (Atomic)
， ，
， 。
2. (Consistent)
， 。
3. (Isolation)
，
。
4. (Duration)
， 。
*********************************************************************************************************************************
*
：
* (dirty read)： 。
* : 。
* ： ( ) 。
：
1. (read uncommitted)：
。
、 、 。
2. (read committed):
。
， 。
3. (repeatable read,MySQL )
。
( ：Innodb )。
4. (serializable)
( ) 。
* CMD ：
start transaction; //
select @@tx_isolation; //
//
set session transaction isolation level read uncommitted || read committed || repeatable read || serializable;
rollback; // ( )
savepoint ; //
rollback to ; //
commit; //
*********************************************************************************************************************************
*JDBC 、
JDBC
： PreparedStatement
void addBatch() // PreparedStatement
int[] executeBatch() //
： Statement
Statement ？
Statement stmt=connection.createStatement();
int count=stmt.executeUpdate(String sql); // SQL
ResultSet rs=stmt.executeQuery(String sql); // SQL
Statement ：
addBatch(String sql) // SQL Statement
int[] executeBatch() //
*********************************************************************************************************************************
*JDBC ：
Connection ：
1. setAutoCommit(boolean autoCommit) //
conn.setAutoCommit(false); //
2. void commit() //
3. void rollback() //
4. Savepoint setSavepoint() //
5. void rollback(Savepoint savepoint) //
***************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
*
select 1, 2... from [where ]
union [all]
select 1, 2... from [where ];
:union all ， ， 。
union ， 。
： SQL ， SQL
( ， )。
SQL 。
*********************************************************************************************************************************
*SQL
SQL SQL ， ， SQL 。
SQL ？
、 SQL 、 。
*********************************************************************************************************************************
*MySQL
1.
mysqldump --no-defaults -u -p --databases >
2.
mysql -u -p <
3.
mysqldump --no-defaults -u -p >
4.
mysql -u -p <
*********************************************************************************************************************************
* (DCL)
1.
create user ' '@' IP' identified by ' ';
： ， IP '%'。
2.
grant update|insert|alter... on . to ' '@' IP';
eg: 。
grant all on *.* to ' '@' IP';
3.
revoke on . from ' '@' IP';
4.
delete from user where user=' ';
*********************************************************************************************************************************
* (index)
：explain select * from [where ...];
(select) 。
：
create index on ( );
： ， 。
：
show index from \G;
：
drop index on ;
：
1. ，% 。
2. ， ，
。
： （ ）unique
create table stu(
id int primary key auto_increment,
name varchar(10) unique,
score double unique
);
：
： 。
： null, 。
null, ( )。
*********************************************************************************************************************************
* (view)
。 。 。
create view
as
(select );
：
create view emp_view
as
(
select emp.e_name, emp.e_job,dept.d_name from employee as emp
inner join dept on emp.dept_no=dept.d_no
);
*********************************************************************************************************************************
* (Storage Procedure)
delimiter //
create procedure ( )
begin
end //
：
in( ， )
out( )
inout( )
eg: in( ， )
delimiter //
create procedure proc_select(stuid int)
begin
select id as ,name as ,age as ,score as ,remark as
from student where id=stuid;
end //
eg: out( )
delimiter //
create procedure proc_out(out count int)
begin
select count(*) into count from student;
end //
call proc_out(@x) //
select @x //
eg:inout( )
delimiter //
create procedure proc_inout(inout info varchar(20))
begin
select remark into info from student where name=info;
end //
set @y=' ' //
call proc_inout(@y) //
select @y //
eg: if...then...end if
delimiter //
create procedure proc_update(in a int)
begin
if (a is not null) then
update student set remark=' ！' where score>=95;
end if;
end //
delimiter //
create procedure proc_case(in stuid int)
begin
declare vscore double;
declare vremark varchar(20);
declare info varchar(20);
select score,remark into vscore,vremark from student where id=stuid;
case vscore
when 60 then
set info=' ';
when 95 then
set info=' 95 ！';
else
set info=' ， ';
end case;
select info;
end //
*********************************************************************************************************************************
*MySQL
： date (2017-02-23)
time (16:40:35)
datetime (2017-02-23 16:40:35)
timestamp
：
1. now() sysdate()
2. ：
adddate('date datetime ',interval year|month|day|hour|minute|second);
eg:
select adddate(now(),interval 6 day);
3. ：
subdate('date datetime ',interval year|month|day|hour|minute|second);
eg:
select subdate(now(),interval 6 day);
4. ：
datediff(' date datetime ',' date datetime ');
： 。
*******************************************************************************************************************************
：
select md5(' MD5 ');
select database();
select session_user();
***************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
*MySQL (trigger)
delimiter //
create trigger before|after (insert|delete|update)
on for each row
begin
end
delimiter ;
：
delimiter //
create trigger stu_trigger after insert
on student for each row
begin
insert into log(logname,logtime)values(new.name,now());
end //
delimiter ;
*********************************************************************************************************************************
*MySQL
：
1. SQL 。 ， 。
， ， ， 。
2. (index)。
3. 。 ， ，
。
4. 。 ， 。eg: ，
emp ， emp dept
。
5. 。 ， 。
***************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************