MySQL简单操作随笔记录

create database golden;

create table golden.users(
usersId int unsigned not null auto_increment primary key,
userName char(20) not null,
passWord int unsigned not null
);

create table golden.u like golden.users ; -- 复制表结构
insert into golden.u select * from golden.users; -- 复制表数据
create table golden.uu select * from golden.u; -- 复制表结构和数据

select * from golden.uu;
select * from golden.u;

alter table golden.users change password pwd varchar(20); -- 修改列名
alter table golden.users change userName usersName varchar(20); -- 修改列名
alter table golden.users modify column passWord char(20); -- 修改列类型
alter table golden.users  AUTO_INCREMENT = 2; -- 修改自增起始值
alter table golden.users add column age int; -- 添加一列

insert into golden.users(usersid,usersName,pwd) value(5,‘wangwu‘,‘123‘); -- 添加数据
insert into golden.users(usersName,pwd) value(‘lb‘,123);

delete from golden.users where usersId=5 and usersName = ‘ww‘; -- 删除数据

update golden.users set age=10 where usersId=4; -- 修改数据

select * from golden.users;  -- 查询

create index Index_usersId on golden.users(usersId);-- 创建索引

drop index userId on golden.users; -- 删除索引

show index from golden.users; -- 显示索引
show keys from golden.users;

create view golden.v_view as select * from golden.users; -- 创建视图
create view golden.v_view(usersId,usersName) as select usersId,usersName from golden.users;
drop view golden.v_view ; -- 删除视图

describe golden.v_view; -- 查询视图
desc golden.v_view;

select count(age)  from golden.users ; -- 查询总数

select sum(age) from golden.users; -- 求和
select avg(usersId) from golden.users; -- 平均数
select max(usersid) from golden.users; -- 最大
select min(usersid) from golden.users; -- 最小

-- 分页查询limit（m，n）(m是指记录开始的index，从0开始，表示第一条记录，n代表第m+1开始，取n条)
SELECT * FROM golden.users ORDER BY usersId LIMIT 3, 2;

SELECT * FROM golden.users WHERE usersId BETWEEN 2 AND 6;

SELECT * FROM golden.users WHERE usersId in (1,4, 6);

MySQL简单操作随笔记录,古老的榕树,5-wow.com