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; en