代码:
Use data1;
create table t_1(
id int not null auto_increment,
name varchar(20) not null,
sex varchar(2),
age tinyint,
grade varchar(5),address varchar(50),
chinese float,math float,
primary key (id)
);
使用查询编辑器往表data1.t_1中一次性插入多条记录:(字段id为主键、标识符列,会自动增长,不用写)
Use data1;
INSERT INTO t_1 (name,sex,age,grade,address,chinese,math) VALUES ('张三','男',13,'三年一班','广州市',108,77);
INSERT INTO t_1 (name,sex,age,grade,address,chinese,math) VALUES ('李小芳','女',14,'三年二班','佛山市',-60,48.5);
INSERT INTO t_1 (name,sex,age,grade,address,chinese,math) VALUES ('张大白','男',13,'三年一班','广州市',120,99.5);
在查询编辑器里输出表data1.t_1中的所有记录:
SELECT * FROM data1.t_1;
############
############
在库data1中创建表t_2:
Use data1;
create table t_2(
id int not null auto_increment,
name varchar(20) not null,
phone varchar(30),
english float,
primary key (id)
);
使用查询编辑器往表data1.t_2中一次性插入多条记录:(字段id为主键、标识符列,会自动增长,不用写)
Use data1;
INSERT INTO t_2 (name,phone,english) VALUES ('张三','11234',60);
INSERT INTO t_2 (name,phone,english) VALUES ('张大白','22234',70.5);
在查询编辑器里输出表data1.t_2中的所有记录:
Use data1;
SELECT * FROM t_2;
############
############
将表data1.t_1、data1.t_2的一些字段、记录放到一个新表中:
先复制表结构:
Use data1;
CREATE TABLE new_t_1 SELECT t_1.name,t_1.grade, t_1.chinese,t_2.english FROM t_1,t_2 WHERE 1=2;
再复制表数据:
INSERT INTO new_t_1 (name,grade,chinese,english) SELECT t_1.name,t_1.grade, t_1.chinese,t_2.english FROM t_1,t_2 where t_1.name = t_2.name and t_1.name = '张大白';
上面的代码执行成功后,再查看新表data1.new_t_1的表数据:
Use data1;
SELECT * FROM new_t_1;
##################
##################
在库data1中再创建表table1、table2、table3:
Use data1;
create table table1(
id int not null auto_increment,
number varchar(50) not null,
name varchar(50),
address varchar(50),
primary key (id)
);
create table table2(
id int not null auto_increment,
number varchar(50) not null,
name varchar(50),
class varchar(20),
primary key (id)
);
create table table3(
id int not null auto_increment,
class varchar(20),
teacher varchar(20),
primary key (id)
);
插入记录:(字段id为主键、标识符列,会自动增长,不用写)
Use data1;
insert into table1 (number,name,address) values ('A001','小明','佛山');
insert into table1 (number,name,address) values ('A002','小李','广州');
insert into table1 (number,name,address) values ('A003','小海','肇庆');
insert into table2 (number,name,class) values ('A001','小明','跆拳道');
insert into table2 (number,name,class) values ('A002','小李','空手道');
insert into table2 (number,name,class) values ('A003','小海','跆拳道');
insert into table3 (class,teacher) values ('跆拳道','李大杰');
insert into table3 (class,teacher) values ('空手道','小龙');
在查询编辑器里一次性输出表data1.table1、data1.table2、data1.table3中的所有记录:
USE data1;
SELECT * FROM table1;
SELECT * FROM table2;
SELECT * FROM table3;
############
############
多表交叉查询:(没有直接相通的字段时)
查询选择了课程“空手道”的学生信息,并显示对应的课程老师:
Use data1;
select table1.number,table1.name, table1.address,table2.class,table3.teacher from table1,table2,table3 where table1.number = table2.number and table2.class = table3.class and table3.class = '空手道';
笺注:要有间接相通的字段,才能进行查询。
查询选择了课程“跆拳道”的学生信息,并显示对应的课程老师:
Use data1;
select table1.number,table1.name, table1.address,table2.class,table3.teacher from table1,table2,table3 where table1.number = table2.number and table2.class = table3.class and table3.class = '跆拳道';
############
############
将表data1.table1、data1.table2、data1.table3的一些字段、记录放到一个新表中:
先复制表结构:
Use data1;
CREATE TABLE new_t_2 SELECT table1.number,table1.name, table1.address,table2.class,table3.teacher FROM table1,table2,table3 WHERE 1=2;
再复制表数据:
INSERT INTO new_t_2 (number,name,address,class,teacher) SELECT table1.number,table1.name, table1.address,table2.class,table3.teacher from table1,table2,table3 where table1.number = table2.number and table2.class = table3.class and table3.class = '跆拳道';
上面的代码执行成功后,再查看新表data1.new_t_2的表数据:
Use data1;
SELECT * FROM new_t_2;