mysql命令巧记_mysql常用命令杂记

查看版本

mysqladmin -uRootmaster -pRootmaster@777 version

select version()

查看Log_bin是否开启

show variables like 'log_bin';

创建表

create table students ( student_id int unsigned, name varchar(39), sex char(1), birth date, primary key (student_id));

create table tb_emp1(id int(11), name varchar(25),deptId int(11), salary float);

create table member(id bigint auto_increment primary key,

name varchar(20),sex tinyint not null default '0')engine=myisam default charset=utf8 auto_increment=1;

create table tb_member1(

id bigint primary key auto_increment ,

name varchar(20),

sex tinyint not null default '0'

)ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

create table tb_member2 like tb_member1;

DROP table IF EXISTS tb_member;

create table tb_member(

id bigint primary key auto_increment ,

name varchar(20),

sex tinyint not null default '0'

)ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;

设置主键：

create table tb_emp2 (id int(11) primary key, name varchar(25), depId int(11), salary float);

create table tb_emp3 ( id int(11),name varchar(25),depId int(11), salary float, primary key(id));

create table tb_emp4 ( name varchar(25), deptId int(11), salary FLOAT, primary key(name,deptId));

外键约束

定义表tb_emp5,让他的建deptId 作为外键关联到tb_dept1的主键id，

create table tb_dept1( id int(11) primary key, name varchar(22) not null, location varchar(50) );

create table tb_emp5 ( id int(11) primary key, name varchar(25), deptId int(11), salary float, constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id));

唯一行约束

create table tb_dept2 ( id int(11) primary key, name varchar(22) unique, location varchar(50));

create table tb_dept3 ( id int(11) primary key, name varchar(22), location varchar(50), constraint sth unique(name));

默认值约束

create table tb_emp7 ( id int(11) primary key, name varchar(25) not null, deptId int(11) default 1111, salary float);

设置表的属性值自动增加

一个表只能有一个字段设置

AUTO_INCREMENT,可以是任意整数类型(TINYINT,SMALLIN,INT,BIGINT)

create table tb_emp8( id int(11) primary key auto_increment, name varchar(25) not null, deptId int(11), salary float);

insert into tb_emp8 (name,salary) values('lucy',1000),('lura',1200),('kevin',1500);

修改表数据

1.修改表名

alter table tb_dept3 rename tb_deptment3;

2.修改表字段

alter table tb_dept1 modify name varchar(33);

3.修改字段名

alter table tb_dept1 change location loc varchar(50);

4.修改字段名为location，并将数据类型改变为varchar(60);

alter table tb_dept1 change loc location varchar(60);

添加字段

alter table tb_dept1 add managerId int(10);

alter table tb_dept1 add column1 varchar(12) not null;

在表的第一类添加一个字段

alter table tb_dept1 add column2 int(11) first;

在表的指定列之后添加一个字段

alter table tb_dept1 add column3 int(11) after name;

删除字段

删除字段

alter table tb_dept1 drop column2;

修改字段位置

alter table tb_dept1 modify column1 varchar(12) first;

将字段放到指定列之后

alter table tb_dept1 modify column1 varchar(12) after location;

删除表

drop table if exists tb_dept2;

alter table tb_emp drop foreign key fk_emp_dept; # 如有外键

插入数据

insert into students(student_id,name,sex,birth) value (1,'steven','1','1991-01-01');

insert into member(name,sex) select name,sex from member;

insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;

首次安装 更改root用户密码

/data/app/mysql-3307/bin/mysqladmin -uroot password '123456'

查看默认引擎

show variables like '%storage_engine%';

show create table tb_dept\G

查询:

select f_id,f_name from fruits where f_name='apple';

小于

select f_name,f_price from fruits where f_price < 10;

带in

select s_id, f_name,f_price from fruits where s_id in (101,2) order by f_name;

select s_id, f_name,f_price from fruits where s_id in (101,102) order by f_name;

between and

select f_name,f_price from fruits where f_price between 2.00 and 10.20;

not between and

select f_name,f_price from fruits where f_price not between 2.00 and 10.20;

like查询

select * from fruits where f_name like 'b%';

select * from fruits where f_name like 'b%y';

_ 下划线 一次匹配一个

select * from fruits where f_name like '____y';

查询空值：

select c_id,c_name,c_email from customers where c_email is null;

select * from customers where c_email is not null;

and查询：

select f_id,f_price,f_name from fruits where s_id='101' and f_price>8;

select f_id, f_price, f_name from fruits where s_id in('101','102') and f_price >= 5 and f_name ='apple';

or查询：

select s_id,f_name,f_price from fruits where s_id=101 or s_id=102;

查询结果不重复：

select distinct s_id from fruits ;

查询结果排序：

查询结果排序

select f_name from fruits order by f_name;

多列排序

select f_name, f_price from fruits order by f_name ,f_price;

指定排序方向

select f_name,f_price from fruits order by f_price desc;

先将价格降序 在按名字升序

select f_price,f_name from fruits order by f_price desc, f_name;

lower_case_table_names=1 使 Linux 环境下 MySQL 忽略表名大小写，否则使用 MyCAT 的时候会提示找不到表的错误