Java Face question 06

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.