MYSQL数据库操作大全(名师整理版).doc

MYSQL操作大全（名师整理版）
数据库相关操作：
create database if not exists test3 default character set utf8;

alter database test3 character set gb2312;

drop database if exits test5;

数据表相关操作：
create table users (
id int(11) not null auto_increment,
username varchar(55) default null,
sex smallint(2) default null,
password varchar(55) default null,
primary key (id)
) default charset=utf8;

修改表结构：
alter table users_copy drop column sex,add column address varchar(55) default null;

alter table users_copy change password pass int(30) not null;

alter table users_copy modify pass varchar(55) null;

alter table users_copy rename users_backup;

alter table user_copy add index(username);

desc user_copy;

drop table if exists users_backup;

rename table users to users_copy;

desc users;

表数据的处理:
insert into users_copy(username,sex,password) values('jack','1','123789');

update users_copy set username = 'rose',sex = '2' where id = 2;

delete from users_copy where id = 1;

delete from users_copy;

表数据的查询:
select id,username,sex ,password from users_copy where username like '%a%' and id = 6;

select distinct(username) name,id,sex,password from users_copy;

select id,username,sex,password from users_copy order by id,sex asc;--desc

select sex as '性别',count(distinct id) '合计',username from users_copy group by sex having id = 5;
select sex as '性别',count(distinct id) as id,username from users_copy group by sex having id = 5;

select * from users_copy limit 2,5;

set @skip = 1;set @numrows = 3;

prepare stmt from "select * from users_copy limt ?,?";

excute stmt using @skip, @numrows;

select id,sex into outfile 'c:\\xx.txt' from users;

函数：
/****************************repeat**********************************************/
create procedure add_users3(in username varchar(55),in password varchar(55),in address varchar(150),chk int)
begin
repeat
set chk = chk + 1;
insert into users(username,password,address) values(username,password,address);
until
chk < 10
end repeat;
end;

-- call add_users3('111redyin111','1116666','11999999',7);

create procedure add_users4(in username varchar(55),in password varchar(55),in address varchar(150))
begin
insert into users(username,password,address) values(username,password,address);
end;

-- alter procedure add_users4 contains SQL;-- comment 'add_users4';

-- drop {procedere | function} [if exists] sp_name;

/*******************************function***************************************/
create function sum_function(num1 int,num2 int) returns int
return num1 + num2;

-- select sum_function(1,2);

-- select sum_function(count(id),count(username)) from users;

-- alter function sum_function comment 'sum_function';

create function hello(s char(20)) returns char(50)
return concat('Hello,',s,'!');
select hello('world');

-- select case 1 when 1 then 'one' when 2 then 'two' else 'more' end;

视图：
create or replace view users_view (id,username,password) as select id,username,password from users where address = '2' with check option;

alter view users_view as select id,username from users;

drop view if exists users_view1;

select * from users_view;

show create view users_view;

存储过程：
create procedure users_proc (out paraml int)
begin
select count(id) into paraml from users;
end;

call users_proc(@id);
select @id;

drop procedure if exists add_users;

create procedure add_users(in username varchar(55),out password varchar(55),inout address varchar(150))
begin
insert into users(username,password,address) values(username,password,address);
end;

set @cc = '222';
set @dd = '333';
call add_users('111',@cc,@dd);
commit;

create procedure query_proc(in x_name varchar(55),in x_id int)
begin
declare xusername varchar(55) default 'jack';
declare xpassword varchar(55);
declare xid int;
select id,username,password into xid ,xusername,xpassword from users where username = x_name and id = x_id;
select xid,xusername,xpassword;
end;

-- call query_proc('jack',1);

/***********************************if*****************************************/
create procedure add_users1(in username varchar(55),in password varchar(55),in address varchar(150))
begin
if length(password) > 6 then
insert into users(username,password,address) values(username,password,address);
else
insert into users(username,address) values(username,address);
end if;
end;

-- call add_users1('redyin','55','8888888');

/***********************************loop*****************************************/
create procedure add_users2(in username varchar(55),in password varchar(55),in address varchar(150),chk int)
begin
label1:loop
set chk = chk + 1;
if chk < 10 then
insert into users(username,password,address) values(username,password,address);
iterate label1; -- iterate只可以出现在loop，repeat和while语句内。iterate意思为：再次循环。
end if;
end loop label1;
end;

-- call add_users2('redyin111','6666','999999',7);

索引：
create fulltext index username_index on users_copy(username);

create unique index id_index using btree on users_copy(id);

drop index username_index on users_copy;

触发器：
create trigger add_users_copy_trigger after insert on users for each row
begin
insert into users_copy set username = new.username,password = new.password,address = new.address;
end;

-- drop trigger [schema_name]trigger_name;
-- insert into users(username,password,address) values('ccf','ddf','eef');