Java Face question 06

Title:

Database

1. Table Name: g_cardapply

Field (field name/type/length):

G_applyno varchar 8;//application number (keywords)

G_applydate bigint 8;//Application Date

G_state varchar 2;//Application Status

2. Table Name: G_cardapplydetail

Field (field name/type/length):

G_applyno varchar 8;//application number (keywords)

G_name varchar 30;//Applicant's name

G_idcard varchar 18;//Applicant ID Number

G_state varchar 2;//Application Status

The associated fields of two tables are the application number.

01. Check the date of application for 440401430103082 of the identity card number

02, check the same ID card number has more than two records of the ID number and record number

03, the status of the record of 440401430103082 of the ID card number in two tables is changed to 07

04. Delete the record of the surname Li from the table G_cardapplydetail

creating databases and data tables

CREATE DATABASE IF not EXISTS exam

Use exam

CREATE TABLE IF not EXISTS g_cardapply (
G_applyno INT (8) PRIMARY KEY not NULL auto_increment COMMENT ' Application number ',
G_applydate Date COMMENT ' application dates ',
G_state VARCHAR (2) COMMENT ' Application status '
) charset= ' UTF8 ' engine=innodb;

CREATE TABLE IF not EXISTS g_cardapplydetail (
G_applyno INT (8) Not NULL COMMENT ' application number ',
G_name VARCHAR COMMENT ' applicant's name ',
G_idcard VARCHAR (COMMENT) ' Applicant's ID number ',
G_state VARCHAR (COMMENT ' Application Status ')
) charset= ' UTF8 ' engine=innodb;

ALTER TABLE g_cardapplydetail ADD CONSTRAINT foreign_cardapplydetail_cardapply foreign KEY (g_applyno) REFERENCES g_ Cardapply (G_applyno)

INSERT into G_cardapply VALUES (1, ' 2017-10-22 ', 0);
INSERT into G_cardapply VALUES (2, ' 2017-10-23 ', 1);
INSERT into G_cardapply VALUES (3, ' 2017-10-25 ', 1);
INSERT into G_cardapply VALUES (4, ' 2017-10-27 ', 0);
INSERT into G_cardapply VALUES (5, ' 2017-10-28 ', 1);
INSERT into G_cardapply VALUES (6, ' 2017-10-21 ', 1);

INSERT into G_cardapplydetail VALUES (1, ' xiaohei1 ', ' 11111111 ', 0);
INSERT into G_cardapplydetail VALUES (2, ' xiaohei2 ', ' 22222222 ', 1);
INSERT into G_cardapplydetail VALUES (3, ' xiaohei3 ', ' 33333333 ', 1);
INSERT into G_cardapplydetail VALUES (4, ' xiaohei4 ', ' 44444444 ', 0);
INSERT into G_cardapplydetail VALUES (5, ' xiaohei2 ', ' 22222222 ', 1);
INSERT into G_cardapplydetail VALUES (6, ' xiaohei3 ', ' 33333333 ', 1);

on. SELECT g_applydate from G_cardapply A, g_cardapplydetail d WHERE g_idcard= ' 22222222 ' and A.g_applyno=d.g_applyno

Geneva. SELECT G_idcard,count (G_idcard) from G_cardapplydetailgroup by G_idcard have COUNT (g_idcard) >=2

Geneva. #关闭事务自动提交

SET autocommit=0;

BEGIN;
UPDATE g_cardapply SET g_state= ' WHERE g_applyno in
(
SELECT G_applyno from G_cardapplydetail WHERE g_idcard= ' 22222222 '
);
UPDATE g_cardapplydetail SET g_state= ' WHERE g_idcard= ' 22222222 ';
#rollback;
COMMIT;

#再设置成自动提交
SET autocommit=1;

Geneva . . DELETE from G_cardapplydetail WHERE g_name like ' li% '

Java Face question 06