MySQL常用指令整理
24034 ワード
--
--
create database itcast;
--
create database itcast character set utf8;
--
show ;
show databases;
--
drop database itcase;
--
create table ( 1 [( )] [ ], 2 [( )] [ ]);
create table t4 (c1 int,c2 varchar(20),c3 text,c4 int,c5 int);
--
desc ;
--
alter table rename ;
alter table xi_ID rename xi;
--
alter table character set ;
alter table users character set utf8;
--
alter table add [( )] ;
alter table student add lalala varchar(20);
--
alter table modify [( )] [ ];
alter table t4 modify c3 int not null;
--
alter table change [( )];
alter table student change gender name varchar(20);
--
alter table drop ;
alter table student drop id;
--
--
create table test06 (id int primary key,name varchar(15) not null,age int(10),aa int unique,bb text,index(id));
--
create table test07 (id int primary key,name varchar(15) not null,age int(10),aa int unique,bb text,index indexName (id));
--
explain select * from t2 where id=1;
--
create table test09 (id int,name varchar(15) not null,age int(10),aa int unique,bb text,unique index unique_id (id asc));
--
create table test10 (id int,name varchar(15) not null,age int(10),aa int unique,bb text,fulltext index fulltext_name (name));
--
create table t1 (id int,name varchar(20) not null,age int(10),aa int unique,bb text,index single_name(name(20)));
--
create table t2 (id int,name varchar(20) not null,age int(10),aa int unique,bb text,index multi(name(20),id));
--
create table t3 (id INT,name varchar(20) not null,age int(10),space geometry not null,SPATIAL index sp(space))engine=myisam;
-- -
create index index_c1 on t4(c1);
alter table t4 add index hh(c1);
-- -
create unique index unique_index_c2 on t4(c2);
alter table t4 add unique index hh2(c2);
-- -
create index usignal_index_c3 on t4(c3(10));
alter table t4 add index signal_hh2(c2);
-- -
create index mulitidx_index_c4andc5 on t4(c4(4),c5(4));
alter table t4 add index mulit_hh2(c2,c3(10));
-- -
create fulltext index fulltext_index_c2 on t4(c2);
alter table t4 add fulltext index fulltext_hh2(c2);
-- -
create spatial index spatial_index on t5(space);
alter table t4 add spatial index spatial_hh2(space);
--
alter table t4 drop index fulltext_hh2;
drop index mulit_hh2 on t4;
--
--
insert into t4 (c1,c2,c3,c4,c5) values (11,"awei",22,33,44);
insert into t4 values (22,"haokun",33,44,55);
insert into t4 (c4,c5) values (33,44);
insert into t6 values (22,"haokun",22),(33,"awei",33),(44,"hk",44);
insert into t6 (id,name) values (222,"test1"),(333,"wei");
--
update student set name=' ',grade=100.00 where name="awei";
update student set grade=66 where grade<40;
update student set grade=66;
--
delete from student where name=' ';
delete from student;
-- , 1
-- DDL, drop create , ,
truncate table student;
--
--
select id,grade,name from student;
-- grade ,
select distinct grade from student order by grade;
select * from student;
--
select * from student where id<5;
select * from liuwei.test where name in(' ',' ',' ',' ');
select * from liuwei.test where name not in(' ',' ',' ',' ');
select * from liuwei.test where id between 10 and 20;
select * from liuwei.test where id not between 10 and 20;
--
select * from liuwei.test where name is null;
select * from liuwei.test where name is not null;
--
select distinct gender from liuwei.text;
select distinct gender,name from liuwei.test;
--
--% ,_
select * from liuwei.test where xieyihao like "%2%8";
select * from liuwei.test where xieyihao like "_170___80";
--and
select * from liuwei.test where xieyihao like "%8%" and id<30;
select * from liuwei.test where xieyihao like "%8%" and id in (27,28,29) and gender=' ';
--or
select * from liuwei.test where xieyihao like "%8" or id<5;
--and/or , and
select * from liuwei.test where gender=' ' or id<15 and name in (' ',' ',' ',' ') and xuehao like "%75%";
--4.3
--4.3.1
--
select count(name) from liuwei.test;
--
select sum(xieyihao) from liuwei.test;
-- , ifnull null , aa null, 0
select sum(ifnull(aa,0)+bb+cc) from exam;
--
select avg(xieyihao) from liuwei.test;
--
select max(xieyihao) from liuwei.test;
--
select max(xieyihao) from liuwei.test;
--4.3.2
select * from student order by id;
-- asc( ) , asc
select * from student order by asc id;
-- , , 0
select * from liuwei.test order by id+0 limit 20;
-- -- , id
select * from liuwei.test order by gender,id+0 desc;
--4.3.3
--group by
select gender from liuwei.test group by gender;
--group by
select count(*),gender from liuwei.test group by gender;
--group by having
select sum(xieyihao),gender from liuwei.test group by gender having sum(xieyihao)>90000;
--4.3.4 limit
select * from liuwei.test order by id+0 limit 20;
-- 4 , 5
select * from liuwei.test order by id+0 limit 3,5;
--4.4
as as , as
select name stu_name,gender stu_gender,xieyihao xy,xuehao xh from test t where t.id>30;
--
--5.1
--
alter table add constraint FK_ID foreign key ( ) REFERENCES ( );
alter table student add constraint FK_ID foreign key (gid) REFERENCES grade (id);
--
alter table drop foreign key
alter table student drop foreign key FK_ID;
--5.2
-- ( , )
-- ( , )
--5.3
--5.3.1
select * from 1 CROSS JOIN 2;
--5.3.2
-- 1 2 ;
select from 1 [inner] join 2 on 1. = 2. ;
select employee.name,department.dname from department join employee on department.did=employee.did;
select employee.name,department.dname from department,employee where department.did=employee.did;
--
select * from employee p1 join employee p2 on p1.did=p2.did;
-
1.
foreign key(orderdis) references orders(id);
dept emp, emp id dept id
create table dept(
id int primary key auto_increment,
name varchar(40)
);
create table emp(
id int primary key auto_increment,
name varchar(40),
dept_id int,
foreign key (dept_id) references dept(id)
);
2. ,
alter table add constraint FK_ID foreign key( ) REFERENCES ( );
alter table emp add constraint FK_ID foreign key(dept_id) REFERENCES dept(id);
3.
alter table drop foreign key ;
alter table emp drop foreign key FK_ID;
4.
( ): 1 2
select * from emp,dept;
:
select * from emp,dept where emp.dept_id=dept.id;
select * from emp inner join dept on emp.dept_id=dept.id;
:
select * from dept left join emp on dept.id=emp.dept_id;
:
select * from dept right join emp on dept.id=emp.dept_id;
:
select * from dept full join emp on dept.id=emp.dept_id; # mysql !
mysql , union ,
select * from dept left join emp on dept.id=emp.dept_id
union
select * from dept right join emp on dept.id=emp.dept_id;
~ 4 :
select dept.name ,emp.name from dept inner join emp on dept.id=emp.dept_id where dept.id=4;
1. IN
IN , , .
~ 20
select dname from department where did in (select did from employee where age=20);
~ 20
select dname from department where did not in (select did from employee where age=20);
2.EXISTS , , , TRUE FALSE, TRUE ,
~ employee 21 , , department
select * from department where exists (select * from employee where age>21);
3.ANY , , ,
~ ANY ,
-- employee did, department did , employee did
select * from department where did>any(select did from employee);
4. ALL .
~ ALL ,
-- employee did, department did , employee did
-- employee did 4, department did 4
select * from department where did>all(select did from employee);
5.
~ ,
-- employee did, department
select dname from department where did = (select did from employee where name=' ');
: ,
mysql , mysql
:
start transaction; --
...
...
commit; -- , sql
rollback; -- , , .
(ACID)
(Atomicity):
, .
(Consistency):
.( : , , )
(Isolation):
, , .
(Durability):
, , .
, , (Isolation) .
mysql
read uncommitted -- , ( )
read committed -- ( )
repeatable read -- , ( )
serializable --
:
serializable > repeatable read > read committed > read uncommitted
:
read uncommitted > read committed > repeatable read > serializable
mysql repeatable read !
set [session/global] transaction isolation level ....; --
select @@tx_isolation; --
~
show variables like '%autocommit';
sql ,
1.
create procedure ([proc_parameter]) [characteristics...]routine_body
proc_parameter :
[IN|OUT|INOUT]param_name type
[ | | ] (MySQL )
characteristics
LANGUAGE SQL: , SQL , .
[NOT] DETERMINISTIC: , .[NOT] , .
[NOT] DETERMINISTIC
~ student
delimiter !! -- sql
create procedure Proc()
begin
select * from student;
end !!
delimiter ; --
2.
BEGIN END ,
DECLARE var_name[,varname]...date_type[DEFAULT value];
1:
SET var_name = expr[,var_name = expr]...;
2:
select col_name[...] into var_name[...] table_expr;
~ myvariable , INT , 100
~ , +20
delimiter //
create procedure proc2()
begin
declare myvariable int default 100;
select myvariable;
set myvariable = myvariable+20;
select myvariable;
end //
delimiter ;
~ ,
delimiter //
create procedure proc3()
begin
declare s_grade float;
declare s_gender char(2);
select grade,gender into s_grade,s_gender from student where id = 3;
select s_grade,s_gender;
end //
delimiter ;
3.
DECLARE condition_name CONDITION FOR [condition_type];
condition_type :
-- ERROR 1049 (42000): Unknown database 'liuweiee'
SQLSTATE[VALUE] sqlstate_value -- SQLSTATE 42000
mysql_error_code -- code 1049
~ "ERROR 1049 (42000)" , command_not_allowed
declare command_not_allowed condition for sqlstate 42000;
declare command_not_allowed condition for 1049
3.
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type --
CONTINUE ,
EXIT
UNDO ,MySQL
condition_value --
SQLSTATE[VALUE] sqlstate_value 5
condition_name DECLARE CONDITION
SQLWARNING 01 SQLSTATE
NOT FOUNT 02 SQLSTATE
SQLEXCEPTION SQLWARNING NOT FOUND SQLSTATE
mysql_error_code
4. ( )
DECLARE cursor_name CURSOR for select_statement
OPEN cursor_name
FETCH cursor_name INTO var_name[,var_name]... ##
...
CLOSE cursor_name
5.
IF
IF expr_condition THEN statement_list
[ELSEIF expr_condition THEN statement_list]
[ELSE statement_list]
END IF
~ V null,
DELIMITER //
create procedure proc4()
BEGIN
DECLARE v varchar(2);
set v = 'a';
IF v is null then select 'is null';
ELSE select 'not null';
END IF;
END
//
DELIMITER ;
CASE
CASE case_expr
THEN when_value THEN statement_list
[THEN when_value THEN statement_list]...
[ELSE statement_list]
END CASE
DELIMITER //
create procedure proc5()
BEGIN
DECLARE v int;
set v = 1;
case v
when 1 then select 'value is 1';
when 2 then select 'value is 2';
else select 'not 1 or 2';
end case;
END
//
DELIMITER ;
LOOP
[loop_table:]LOOP statement_list END LOOP [loop_label]
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id+1;
-- leave ,
-- iterate (continue )
if id>=10 then leave add_loop;
end if;
end loop add_loop;
~ loop if 10~20
DELIMITER //
create procedure test1()
begin
DECLARE id INT DEFAULT 0;
my_loop:LOOP
SET id = id+1;
if id<10 then iterate my_loop;
elseif id>20 then leave my_loop;
end if;
select id ' is between 10 and 20';
end loop my_loop;
end //
DELIMITER ;
REPEAT -- do..while
[repeat_lable:] REPEAT statement_list UNTLL expr_condition END REPEAT[repeat_lable]
DECLARE id INT DEFAULT 0;
REPEAT
SET id=id+1;
UNTIL id>=10;
END REPEAT;
WHILE -- while
[while_lable:] while expr_condition DO statement_list END WHILE [while_lable]
DECLARE i INT DEFAULT 0;
WHILE i<10 do
set i=i+1;
END WHILE;
6.
1.
CALL sp_name([parameter[,...]])
~ , student
DELIMITER //
-- ,IN ,OUT
CREATE PROCEDURE CountProc1(IN s_gender VARCHAR(50),OUT num INT)
BEGIN
-- student ' ' ' ' , NUM
SELECT COUNT(*) INTO num FROM student WHERE gender = s_gender;
END //
DELIMITER ;
--
call CountProc1(" ",@num );
--
select @num;
2.
1.SHOW STATUS
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']
~
--
show procedure status;
-- 2
show procedure status like '%2';
2.SHOW CREATE
show create{procedure|function} sp_name
~
show create procedure proc4;
3. information_schema.Routines
~
select * from information_schema.Routines;
3.
ALTER {PROCEDURE|FUNCTION} sp_name[characteristic...]
CONTAINS SQL SQL , ;
NO SQL SQL ;
READS SQL DATA ;
MODIFIES SQL DATA ;
SQL SECURITY{DEFINER|INVOKER} ;
DEFINER ;
INVOKER ;
COMMENT'string'
** ,MySQL , , .
4.
DROP{PROCEDURE|FUNCTION}[IF EXISTS] sp_name
~
1.
~ +
--
create view view_stu as select math,chinese,math+chinese from student;
--
select * from view_stu;
-- , or replace
create or replace view view_stu ( , , ) as select math,chinese,math+chinese from student;
--
select * from view_stu;
2.
~
create view stu_class ( , , )
as
select student.s_id,student.name,stu_info.class from stu_info,student where stu_info.s_id=student.s_id;
--
select * from stu_class;
3.
1.desc ; --
2.show table status like ' '; --
3.show create view ; --
4.
1. create or replace view
~ create or replace view view_stu
create or replace view view_stu as select * from student;
2. alter
alter [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with[cascaded | local] check option]
~ alter
alter view view_stu as select chinese from student;
5.
-- ,
-- ,
1. update
update view_stu set chinese = 100;
2. insert
insert into student values (4,'lili',20,30);
3. delete
delete from view_stu where chinese = 30;
-- :
--
-- select
-- select
-- select distinct,union,top,group by having
6.
drop view [if exists] view_name [,view_name1]...[restrict | cascade]
~ view_stu2
drop view if exists view_stu;
1.
mysqldump: -- ( ) sql
cmd ,mysqldump -u username -p password dbname [tbname1 [tbname2...]] > filename.sql
2. -- ,
1:mysql:
-- , (create database xxx)
-- sql
cmd ,mysql -u -p [ ] [dbname] < filename.sql
2:source :
mysql : source xxx.sql sql ,
grant -- ,
grant privileges on database table
to 'username'@'hostname' [identified by [password]'password']
[,'username'@'hostname' [identified by [password]'password']] ...
~
grant select on mydb3.* to 'wangxiaokun'@'localhost' identified by 'abc123';
create user
create user 'username'@'hostname'[identified by [password]'password']
[,'username'@'hostname'[identified by [password]'password']]...
~
create user 'wangxiaokun'@'localhost' identified by 'abc123';
insert
insert into mysql.user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject)
values ('hostname','username',password('password','','',''));
flush privileges;
~ insert
insert into mysql.user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject)
values ('localhost','wangxiaokun',PASSWORD('abc123'),'','','');
flush privileges;
drop user 'username'@'hostname'[,'username'@'hostname'];
~
drop user 'wangxiaokun'@'localhost';
delete from mysql.user where host='hostname' and user='username';
flush privileges;
~
delete from mysql.user where host='localhost' and user='wangxiaokun';
flush privileges;
1. root
~ : -- 'password' ,
mysqladmin -u username [-h hostname] -p password new_passwrod
C:\Documents and Settings\[ windows ]>
~ :
update mysql.user set password=password('new_passwrod')
where user='username' and host='hostname';
flush privileges;
update mysql.user set password=password('liuwei') where user = 'root' and host='localhost';
flush privileges;
~ :
root ,
set password=password('new_passwrod');
2. root :
1:
grant usafe on *.* to 'username'@'localhost' identified by [password]'new_passwrod';
2:
update mysql.user set password=password('new_passwrod')
where user='username' and host='hostname';
flush privileges;
3:
set password for'username'@'hostname'=password('new_passwrod');
3.
set password=password('new_passwrod'); xx ALTER TABLE logixxxs_xxer_sxxck ADD UNIQUE (LogisticsUserID,LogisticsProductID);