MySQL常用语句操作记录

show databases; #查看数据库

	create database MyDB; #创建数据库

	show databases; 

	use mydb; #连接数据库（选中数据库）

	show tables; #查看表

	create table mytable ( id int(5), name char(10), age smallint(3), birthday date, salary float(15,2)); #创建表

	show create table mytable; #查看表结构

	drop table mytable; #删除表

	show tables;

	#创建表并带注释

	create table mytable (

	id int(5) not null auto_increment primary key comment '员工号', 

	name char(10) not null comment '姓名', 

	age smallint(3) not null default 0 comment '年龄',

	birthday date null comment '出生日期',

	salary float(15,2) not null default 0.0 comment '工资'

);

	show create table mytable; 

	create table mytable_new like mytable; #复制表结构（复制表）

	show tables;

	show create table mytable_new; 

	#在mytable表的serial_no列上创建一个名为myindex的唯一索引

	create unique index myindex on mytable(serial_no);

	#在mytable表中salary列，后面增加一个名为bonus的列,同时对id列做出修改，将列名重名为serial_no

	alter table mytable add bonus float(15,2) after salary, change id serial_no int(6) not null;

	show create table mytable;

	insert into mytable value('100001','Aping',23,'1991-1-1',4500.00,3000.00); #插入数据

	select * from mytable; #查询数据

	insert into mytable value('100002','xioming',25,'1989-2-4',5000.00,1000.00);

	insert into mytable value('100003','lishi',26,'1988-4-2',9000.00,2000.00);

	insert into mytable value('100004','wanwu',30,'1984-8-25',3500.00,1000.00);

	insert into mytable value('100005','zhangsan',30,'1984-8-25',9900.00,2000.00);

	create table mytable_new2 like mytable;

	select * from mytable_new2;

	#查询的结果插入到表中

	insert into mytable_new2 select * from mytable where salary >=5000;  

	select * from mytable_new2;

	select * from mytable;

	#更新数据（更改数据）

	update mytable set name='zeping' where serial_no='100001'; 

	select * from mytable;

	select * from mytable where salary>5000;  #查询工资超过5000的员工

	select * from mytable where salary>4000 and bonus <=1000; #查询工资超过4000并且奖金小或等于1000的员工

	select age,count(*),avg(salary) from mytable group by age; #查询各个年龄与平均工资

	select * from mytable order by bonus desc,salary asc; #查询按奖金数从高到低排序，如果奖金相同则按工资从低到高排序

	select * from mytable limit 2,3; #查询出第3~5条记录

	create table tabaddres (

	serial_no int(6) not null auto_increment primary key comment '员工号', 

	address char(30) not null comment '姓名'

);

	insert into tabaddres value('100001','jxgz');

	insert into tabaddres value('100002','jxnc');

	insert into tabaddres value('100003','gdgz');

	insert into tabaddres value('100004','gdsz');

	insert into tabaddres value('100005','hk');

	select * from tabaddres;

	#两个表联合查询 (mytable表和tabaddres表联合查询出具有员工号，员工姓名和地址的结果集)

	select A.serial_no,A.name,B.address  from mytable A, tabaddres B where A.serial_no = B.serial_no;

	#两个表联合查询 

	select A.serial_no,B.serial_no,A.name,B.address from mytable A, tabaddres B;

	#更改列名  （将serial_no 列名更改为id ）

	alter table mytable  change serial_no id  int(6) not null;

	#删除

	delete from mytable where id = 100002;

(责任编辑：IT)