mysql事务拼写_MySQL语句的拼写

/*************user会员表*****************//**/CREATETABLEUSER(u_idINTPRIMARYKEYAUTO_INCREMENTNOTNULL,u_pwdINTNOTNULL,u_phoneVARCHAR(20)NOTNULL,u_addressVARCHAR(200)NOTNUL...

/*************user会员表*****************/

/**/

CREATE TABLE USER(

u_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,

u_pwd INT NOT NULL,

u_phone VARCHAR(20) NOT NULL,

u_address VARCHAR(200) NOT NULL,

u_name VARCHAR(20) NOT NULL,

u_email VARCHAR(20)

)

/*o_count 人数

o_phone预约电话

o_state预约状态

o_onsubmit提交时间

o_type预约类型

o_order 预约时间

*/

CREATE TABLE appointment(

o_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,

u_id INT,

o_count INT NOT NULL,

o_phone INT NOT NULL,

o_state VARCHAR(10),

o_onsubmit DATE,

o_order DATE,

o_type VARCHAR(10),

CONSTRAINT FOREIGN KEY(u_id)REFERENCES USER(u_id)

);

/*orderDetail订单详情表

od_count 产品数量

od_price产品价格

p_id 产品id

u_id 用户名

*/

CREATE TABLE orderDetail(

od_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,

od_count INT NOT NULL,

p_price FLOAT,

o_addresss VARCHAR(200),

p_id INT ,

u_id INT,

CONSTRAINT FOREIGN KEY(p_id)REFERENCES product(p_id),

CONSTRAINT FOREIGN KEY(u_id)REFERENCES USER(u_id)

);

/*orderDetail订单总表

od_id 订单详情id

od_price产品价格

p_id 产品id

u_id 用户名

ot_state 订单状态

ot_onsubmit 下单时间,

ot_order执行时间

*/

CREATE TABLE orderTotal(

ot_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,

od_id INT,

u_id INT,

p_name VARCHAR(30),

ot_phone INT NOT NULL,

ot_state VARCHAR(10),

ot_onsubmit DATE,

ot_order DATE,

CONSTRAINT FOREIGN KEY(od_id)REFERENCES orderDetail(od_id),

CONSTRAINT FOREIGN KEY(u_id)REFERENCES USER(u_id)

)

/*orderDetail产品总表

od_id 订单详情id

p_price产品价格

p_name 产品名

p_size 大小

p_type 类型

*/

CREATE TABLE product(

p_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,

p_price INT NOT NULL,

p_name VARCHAR(30),

p_size VARCHAR(60),

p_type VARCHAR(20)

)

SELECT * FROM appointment

INSERT INTO appointment(o_count,o_phone,o_state,o_onsubmit,o_order,o_type,u_id) VALUES

(2,187801999916,1,"2016-2-14","2016-2-14","自作",1),

(1,18=80199916,1,"2016-5-14","2016-2-14","到店",2),

(2,1878=019516,1,"2016-4-14","2016-4-14","自作",4),

(2,18=01999916,1,"2z016-2-14","2016-2-14","到店",2),

(4,18=01999916,1,"2016-2-14","2016-4-14","自作)

",

查询出用户的姓名，产品名称，产品的数量，价格， 总金额等信息，怎么拼写SQL语句，已知条件只有ot_state=2

展开