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
显示全部