mysql常用命令

1.登录  


MySQL -h 127.0.0.1 -u root -p


2.数据库相关 


create database db_name;


show databases;


use db_name;


drop database db_name;


3.引擎相关


show engines \g


show variables like 'have%';


show variables like 'storage_engine%';


4.数据类型相关


help contents;


help data types;


help int;


5.表相关


create table t_dept {


deptno int  not null auto_increment,


dname varchar(20) default  默认值,


loc varchar(40),


constraint  uk_name unique(depno),


constraint pk_dname primary key(depno),


constraint fk_name foreign key(属性名) references 表名(属性名2)


(unique,fulltext) index index_xxx(属性名)


};


describe table_name;


show create table table_name;


drop table table_name;


alter table old_table_name rename [to] new_table_name;


alter table table_name add  属性名  属性类型  first(after 属性名);


alter table table_name drop 属性名;


alter table table_name modify 属性名  属性类型;


alter table table_name change 旧属性名 新属性名  旧数据类型;


6.索引相关


create (unique,fulltext) index index_xxx on 表名(属性名)


alter table t_xxx add index index_xxx(属性名)


7.视图相关


create view view_name as 查询语句;


show table status from view \g


show create view view_name;


alter view  view_name as 查询语句;


8.触发器相关


delimiter $$


create trigger trigger_name


before|after trigger_event 


on table_name for each row 


begin


trigger_stmt


end


$$


delimiter ;




create trigger tri_diarytime


before insert


on t_dept for each row


insert into t_diary value(xxx);


drop trigger trigger_name;


9.数据相关


insert into table_name (xxx)values(xxx),(xxx),(xxx) 


update tabale_name set xxxx=xxx,xxx=xxx where condition;


delete from table_name where xxx=xxx;


select distinct xx from table_name;


select xx as yyy,mmm from table_name where xxx>xxx and xxx>xxx or xxx is not  null and xxx bewteen jjj and kkk;


select * from table_name where field in(value1, value2,xxx) or filed like '%yyy%';


select * from table_name order by field asc, filed2 desc;


select * from table_name where condition limit  (offset_start,)row_count;


select AVG(comm) average from table_name where not xxx=0;


select deptno,GROUP_CONCAT(ename) enames, COUNT(ename) number from table_name group by deptno;


10.多表连接


笛卡尔积，外连接，内连接


select xxx,yyy from table1 inner join table2 on condition;


select t.empno,e.ename,e.job,d.dname,d.loc from t_emplyee e, t_dept d where e.deptno=d.deptno;


select ename,sal from t_employee where sal>=all(select  sal from t_employee where job='manager');


11.常用函数和运算符


select now(), week(now()),dayname(now()),weekday(now());


12.存储过程和函数


create procedure procedure_name(xxx)  [characteristic] routine_body


delimiter $$


create procedure proce_employee_sal()


comment  'xxx'


begin


declare xxx int defalut 1000;


set xxx=350;


select ***;


end$$


delimiter;


show procedure status like 'xxx'\g


use information_schema;select * from routines;


show create procedure proce_name \G


drop procedure xxxx;


13事务


acid 未提交读，提交读，可重复读，可串行化


共享锁，排他锁，意向锁


脏读，不可重复读，幻读