练习
DROP DATABASE SHOP;
CREATE DATABASE SHOP CHARACTER SET UTF8;
CREATE TABLE ` USER ` (
USERID INT PRIMARY KEY AUTO_INCREMENT ,
USERNAME VARCHAR ( 20 ) NOT NULL ,
` PASSWORD` VARCHAR ( 18 ) NOT NULL ,
ADDRESS VARCHAR ( 100 ) ,
PHONE VARCHAR ( 11 )
) ;
CREATE TABLE CATEGORY (
CID VARCHAR ( 32 ) PRIMARY KEY ,
CNAME VARCHAR ( 100 ) NOT NULL
) ;
CREATE TABLE PRODUCTS(
PID VARCHAR ( 32 ) PRIMARY KEY ,
` NAME` VARCHAR ( 40 ) ,
PRICE DOUBLE ( 7 , 2 ) ,
CATEGORY_ID VARCHAR ( 32 ) ,
CONSTRAINT FOREIGN KEY ( CATEGORY_ID) REFERENCES CATEGORY( CID)
) ;
CREATE TABLE ORDERS(
OID VARCHAR ( 32 ) PRIMARY KEY ,
TOTALPRICE DOUBLE ( 12 , 2 ) ,
USERID INT ,
CONSTRAINT FOREIGN KEY ( USERID) REFERENCES ` USER ` ( USERID)
) ;
CREATE TABLE ORDERITEM(
OID VARCHAR ( 32 ) ,
PID VARCHAR ( 32 ) ,
NUM INT ,
PRIMARY KEY ( OID, PID) ,
CONSTRAINT FOREIGN KEY ( OID) REFERENCES ORDERS( OID) ,
CONSTRAINT FOREIGN KEY ( PID) REFERENCES PRODUCTS( PID)
) ;
INSERT INTO ` USER ` ( USERNAME, PASSWORD, ADDRESS, PHONE) VALUES ( '张三' , '123' , '北京昌平沙河' , '13812345678' ) ;
INSERT INTO ` USER ` ( USERNAME, PASSWORD, ADDRESS, PHONE) VALUES ( '王五' , '5678' , '北京海淀' , '13812345141' ) ;
INSERT INTO ` USER ` ( USERNAME, PASSWORD, ADDRESS, PHONE) VALUES ( '赵六' , '123' , '北京朝阳' , '13812340987' ) ;
INSERT INTO ` USER ` ( USERNAME, PASSWORD, ADDRESS, PHONE) VALUES ( '田七' , '123' , '北京大兴' , '13812345687' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p001' , '联想' , 5000 , 'c001' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p002' , '海尔' , 3000 , 'c001' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p003' , '雷神' , 5000 , 'c001' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p004' , 'JACK JONES' , 800 , 'c002' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p005' , '真维斯' , 200 , 'c002' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p006' , '花花公子' , 440 , 'c002' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p007' , '劲霸' , 2000 , 'c002' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p008' , '香奈儿' , 800 , 'c003' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p009' , '相宜本草' , 200 , 'c003' ) ;
INSERT INTO products( pid, NAME, price, category_id) VALUES ( 'p010' , '梅明子' , 200 , NULL ) ;
INSERT INTO category VALUES ( 'c001' , '电器' ) ;
INSERT INTO category VALUES ( 'c002' , '服饰' ) ;
INSERT INTO category VALUES ( 'c003' , '化妆品' ) ;
INSERT INTO category VALUES ( 'c004' , '书籍' ) ;
INSERT INTO orders VALUES ( 'o6100' , 18000.50 , 1 ) ;
INSERT INTO orders VALUES ( 'o6101' , 7200.35 , 1 ) ;
INSERT INTO orders VALUES ( 'o6102' , 600.00 , 2 ) ;
INSERT INTO orders VALUES ( 'o6103' , 1300.26 , 4 ) ;
INSERT INTO orderitem VALUES ( 'o6100' , 'p001' , 1 ) , ( 'o6100' , 'p002' , 1 ) , ( 'o6101' , 'p003' , 1 ) ;
SELECT *
FROM ORDERS;
SELECT B. ` USERID` , A. ` OID` , A. ` PID` , A. ` NUM`
FROM ORDERITEM AS A
INNER JOIN ORDERS AS B
ON A. ` OID` = B. ` OID`
WHERE USERID = 1 ;
SELECT *
FROM ORDERS
WHERE USERID = ( SELECT USERID FROM USER WHERE USERNAME = '张三' ) ;
SELECT *
FROM USER
WHERE USERID
IN
( SELECT USERID FROM ORDERS WHERE TOTALPRICE > 800 ) ;
SELECT *
FROM ORDERS
LIMIT 0 , 5 ;
package t1;
import com. mysql. jdbc. Driver;
import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. Statement;
public class TestJDBC {
public static void main ( String[ ] args) throws Exception {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
String url = "jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8" ;
String user = "root" ;
String password = "1234" ;
Connection connection = DriverManager. getConnection ( url, user, password) ;
Statement statement = connection. createStatement ( ) ;
String sql1 = "INSERT INTO t_countries(COUNTRY_ID,COUNTRY_NAME)VALUES('AL','阿尔巴尼亚');" ;
String sql2 = "UPDATE t_employees SET FIRST_NAME='TOM', LAST_NAME = 'Jackson' WHERE EMPLOYEE_ID = '206';" ;
String sql3 = "DELETE FROM t_employees WHERE EMPLOYEE_ID = '206'" ;
int result = statement. executeUpdate ( sql1) ;
int result2 = statement. executeUpdate ( sql2) ;
int result3 = statement. executeUpdate ( sql3) ;
if ( result > 0 && result2 > 0 && result3 > 0 ) {
System. out. println ( "新增成功!" ) ;
} else {
System. out. println ( "新增失败" ) ;
}
statement. close ( ) ;
connection. close ( ) ;
}
}