|
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;
#删除
(责任编辑：IT)delete from mytable where id = 100002; |