mysql增删改查

增

#创建数据库
create database liuyang;

#创建gbk字符集的数据库
create database liuyang default character set gbk;

#创建utf8字符集的数据库
create database liuyang default character set utf8;

#创建表
CREATE TABLE ceshi (id int(4) not null primary key auto_increment,name char(20) not null);

#在创建表时添加索引
CREATE TABLE ceshi (id int(4) not null primary key auto_increment,name char(20) not null,key `index_name` (`name`));

#在创建表后添加索引
create index index_name on ceshi(name);

删

#删除数据库
drop database liuyang;

#删除表
DROP TABLE ceshi;

#删除表中的索引
alter table ceshi drop index index_name;

#删除表中的主键
alter table ceshi drop primary key;

#清空表中的所有数据
truncate table ceshi;

#删除表中指定的数据
delete from ceshi where id=5;

#清空binlog文件
reset master;

#删除mysql-bin.000010之前的binlog文件
purge master logs to 'mysql-bin.000010';

#删除2017-10-07 03:30:30之前的binlog文件
purge master logs before '2017-10-07 03:30:30';

改

#修改密码
set password for 'root'@'localhost'=password('123456');

#在创建表后添加索引
alter table ceshi add index index_name (name);

#在创建表后添加主键
alter table ceshi add primary key(id);

#在创建表后添加字段
alter table ceshi add sex char(4);
alter table ceshi add age int(4) after name;

#修改表名
rename table ceshi to test;

#修改表中的数据
update ceshi set name='luyao' where id=1;

#在表中插入数据
insert into ceshi(id,name) values(001,'liuyang');
insert into ceshi(name) values('yangzixuan');
insert into ceshi values(003,'zhangshuang');
insert into ceshi values(4,'wanglu'),(5,'zhaoqian');

#修改表的存储引擎
alter table ceshi engine = myisam;
alter table ceshi engine = innodb;

查

#查询用户
select user,host from mysql.user;

#显示所有数据库
show databases;

#显示当前进入的数据库
select database();

#显示数据库中的所有表
show tables;
show tables from mysql;

#显示创建的数据库信息
show create database liuyang;

#显示创建的表信息
show create table ceshi;
show create table ceshi\G

#查看表结构
desc ceshi;
show columns from ceshi;

#查看表索引
show index from ceshi;
show index from ceshi\G

#查询表总行数
select count(*) from ceshi;

#查询表中name字段唯一值数量
select count(distinct name) from ceshi;

#查询表内容
select * from ceshi;

#查询表内容前两行
select * from ceshi limit 2;

#查询表内容时以id字段排序
select * from ceshi order by id;

#条件查询表内容
select * from ceshi where id=4;
select * from ceshi where name='wanglu';
select * from ceshi where id=4 and name='wanglu';
select * from ceshi where id=3 or name='wanglu';
select * from ceshi where id>2 and id<5;
select name from ceshi where id>2 and id<5;

#查询表内使用索引信息
explain select * from ceshi where name='liuyang'\G

#显示正在执行的sql语句
show processlist;
show full processlist;

#查看变量
show variables;

#查看mysql状态
show global status;

#查看bin-log文件和位置
show master status;

#查看binlog文件内容
show binlog events;