MySQL----Strengthening knowledge points in Java programming

In the data, the table-building process is a very useful and often seen method.

the relationship between tables and tables is 1:1

? 1:n

? ? ? ? N.

Three different ways.

1-to-1 way

<span style= "FONT-SIZE:18PX;" > Demo Example: A husband and wife think: Keep all the people's information in a table. There are men and women. Asked to find out all the couples. That is, a man and a woman. Note the following: Harry is a bachelor should not be able to query out. </span>

<span style= "FONT-SIZE:18PX;" >create TABLE person (?? ID int,?? NAME VARCHAR (Ten),?? Sex CHAR (1),?? wife int,?? husband int); INSERT into person VALUES (1, ' floret ', ' 0 ', 0,3); insert into the person values (2, ' Yufen ', ' 0 ', 0,4); insert into the person values (3, ' Zhang San ', ' 1 ', 1,0); INSERT INTO The person values (4, ' John Doe ', ' 1 ', 2,0); Inserts into the person values (5, ' Harry ', ' 1 ', 0,0);//A pair of relations operation: Find out the name of each couple create VIEW W as SELECT * From the person WHERE sex= ' 0 '; CREATE VIEW m as SELECT * from person WHERE sex= ' 1 ';//do not take advantage of table-to-table relationships SELECT w.name as wife, M.name as husband from W,m WHERE W.husban D=m.id and m.wife=w.id;//today's more advanced way: Using relationships between Tables select W.name as wife, M.name as husband from W INNER JOIN m on W.husband=m.id and m.wif E=w.id;</span>

1 The way to the many

1 to many, many to 1. A person can have more than one car, asking for a search of all the cars owned by someone. According to the requirements of the paradigm, two tables should be designed. The information of the person and the car are represented separately. <strong></strong>

Step 1: Draw e-r Diagram///Step 2: Build the Entity table separately and add foreign key constraints to the table of the multi-party CREATE TABLE Person2 (?? ID VARCHAR () PRIMARY KEY,?? PName VARCHAR (),?? Sex CHAR ( 1)); CREATE TABLE car (?? ID VARCHAR () PRIMARY KEY,?? CNAME varchar ($),?? Price NUMERIC (10,2),?? pid VARCHAR (+),?? CONSTRAINT CAR_FK FOREIGN KEY (PID) REFERENCES Person2 (ID));D ROP Table car;//Step 3: Add test data to two tables//entity table 1INSERT into Person2 (ID, Pname,sex) VALUES (' P001 ', ' Jack ', ' 1 '), insert into Person2 (id,pname,sex) VALUES (' P002 ', ' Tom ', ' 1 '); INSERT into Person2 ( Id,pname,sex) VALUES (' P003 ', ' Rose ', ' 0 '); insert into Person2 (id,pname,sex) VALUES (' P004 ', ' Mary ', ' 0 '); Person2 (id,pname,sex) VALUES (' P005 ', ' Mike ', ' 1 '); SELECT * FROM person2;////entity table 2INSERT into car (id,cname,price,pid) VALUES (' C001 ', ' BMW ', 123.5, ' P001 '); INSERT into car (id , cname,price,pid) VALUES (' C002 ', ' Benz ', 123.5, ' P001 '); INSERT into car (id,cname,price,pid) VALUES (' C003 ', ' BMW ', 223.5 , ' P001 '); insert into car (id,cname,price,pid) VALUES (' C011 ', ' BMW ', 83.5, ' P003 '); insert into car (id,cname,price,pid) VALUES (' C012 ', ' Benz ', 1, ' P003 '), insert into car (id,cname,price,pid) VALUES (' C013 ', ' Audi ', 223.5, ' P003 '); insert into car (Id,cname,price, PID) VALUES (' C021 ', ' BMW ', 88.5, ' P004 '); insert into car (id,cname,price,pid) VALUES (' C022 ', ' QQ ', ' P004 '); Car (id,cname,price,pid) VALUES (' C023 ', ' Audi ', ' P005 '), INSERT into car (id,cname,price) VALUES (' C033 ', ' Audi ', 1000) ;

/query: Who has what kind of car (with "table name. Column Name" In the form of access to the column, assuming that the column name is not repeated, can save token name)//Use the primary key of one party and the foreign key of "multiparty" to correlate select Person2.pname,car.cname from Person2,car Where person2.id=car.pid;//query jack has what car select Person2.pname,car.cname from Person2,car WHERE person2.id=car.pid and Person2.pname= ' Jack ';//query who has more than two cars select Person2.pname,count (pname) as the number of vehicles from Person2,car WHERE person2.id=car.pid GROUP by PName have count (pname) >=2 ORDER by car quantity; SELECT * from Person2 where ID in (select PID from car GROUP by PID has COUNT (PID) >=2);//query who does not have a car SELECT * from per Son2 WHERE ID not in (SELECT pid from car);

Many-to-many

Many-to-many: a person can have a variety of roles. If a person is a father, a son, and a husband. At the same time, these three characters can be given to all other people.
Ask to be a father, son, and husband.
Find out all the roles that all people have. <strong></strong>

Detailed implementation methods are posted in the final notes.

constraints on the database primary Key primary key
Specifies the primary key---when the table is created when the primary key is declared. The not Nullkeyword should be used at the same time.
Create table stud (ID int primary key,.....)
Specify the primary key after the creation is complete:
Alter table stud add constraint STUD_PK primary key (ID);
The primary key itself grows voluntarily:
Auto_increment for fields of type int
Foreign key foreign key-represents a reference relationship
An association relationship with a table-called a foreign key. When a foreign key is in effect when there is no record in the primary table. Cannot write unreferenced records to the child table:
Alter table stud? Add constraint STUD_FK foreign key (STUD_CLSID) references CLS (ID);
The primary foreign key relationship is a 1-to-many relationship. Assuming that there are no records in the primary table, the child table will not be added.
To create a primary foreign key association, the data type and size of the two tables must be consistent otherwise the creation will not succeed.
The ability to manipulate an out-of-context association through a visual interface. unique unique– and primary key differ, the primary key cannot be null, and unique can have a column null, which is the limit for SQL Server, but MySQL can write null values for multiple columns, so the use of unique on MySQL is generally limited by not NULL.

ALTER TABLE CLS? Add constraint Cls_uk unique (name); /* Specify name cannot be repeated */
Default value Defaults
When you create a table, you can specify default values such as:
Create table stud (sex char (1) Default ' 1 ',.....);

Use associations to query data when a table's data does not meet our needs. We are going to query the data from multiple tables.

You must use the associated query at this time:
Inner join– within the association. Both sides have to exist.?
Left join-right associative.

The data on the left will prevail.
Right join. The relationship between the table and the table above is used

Summarize:
Stored Procedures

Three different ways:

1. The simplest. The most important way.

The phrase "delimiter$$" is intended to allow the parser to take "$$" as the end sign (otherwise the default is ";") As the closing tag), so that the statement terminator in the stored procedure ";" Will not be used as the closing tag for the process.

delimiter$ $CREATE PROCEDURE p1 () BEGIN   SELECT * from stud;   INSERT into Stud (id,sname,age,score,dept) VALUES (1014, ' Liu Sanfong ', 33, 55, ' Communication Academy '); end$ $DELIMITER;   Restore the end tag back to call P1 ();  Call the stored procedure P1

2. The way with the number of parameters

Stored procedure with parameters delimiter$ $CREATE PROCEDURE p2 (in ID int., in NM VARCHAR) BEGIN   INSERT into Stud (id,sname) VALUES (ID,NM) ; end$ $DELIMITER; DROP PROCEDURE p2; Call P2 (1015, ' hanging silk ');

3, with the return value of the

Stored procedure with a return value----parameters and variables (@ variable name)  . An @ is a user variable. Two @ that is, @@ 为 the Global system variable) delimiter$ $CREATE PROCEDURE P3 (in ID int., in NM VARCHAR (+), out num int) BEGIN   INSERT into stud (id,s Name) VALUES (ID,NM);   SELECT COUNT (*) into num from stud; end$ $DELIMITER; Call P3 (1016, ' nameless ', @aa); SELECT @aa; The value of the output variable AA

MySQL differential uppercase and lowercase queries

<span style= "FONT-SIZE:18PX; White-space:pre; " ></span>mysql query By default is not distinction between uppercase and lowercase, such as: <span style= "White-space:pre" ></span>select? * from? table_name where? A like? ' A% '??? <span style= "White-space:pre" ></span>select? * from? table_name where? A like? ' A% '??? <span style= "White-space:pre" ></span>select * FROM table_name where a like ' a% ' <span style= ' white-space: Pre "></span>select * from table_name where a like ' A% ' <span style=" White-space:pre "></span> effect is the same. <span style= "White-space:pre" ></span> to make MySQL queries differentiate between uppercase and lowercase. Can: <span style= "White-space:pre" ></span>select? * from? table_name where? Binary? A like? ' a% '?? <span style= "White-space:pre" ></span>select? * from? table_name where? Binary? A like? ' A% '??? <span style= "White-space:pre" ></span>select * FROM table_name where binary a like ' a% ' <span style= ' White-space:pre "></span>select * FROM table_name where binary a like 'A% ' <span style= ' White-space:pre ' ></span> also be able to be identified when the table is under construction? <span style= "white-space:pre" ></span>create table table_name (???? <span style= "White-space:pre" ></span> a varchar (binary<span style= "White-space:pre" ></span >) <span style= "FONT-SIZE:18PX;" ></span>

transaction transaction:?Atomicity (atomicity): The statement that makes up the transaction forms a logical unit that cannot be run only as part of it.
Consistency (consistency): The database is consistent (database data integrity constraints) before and after the transaction is run.
Isolation (isolcation): The effect of one transaction on another transaction.

?
Persistence (Durability): The effect of transaction processing can be permanently saved.
A transaction has only one result: success or failure.

Start transaction; start a transaction.
Commit, submit the changes you have made. Rollback; Roll back the changes you made. Assume an error occurred while operating. You should start a new transaction.

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center "/>

Set the Thing isolation level to set the isolation level at the MySQL command line.
Setting the isolation level on the MySQL command line is only valid for this open command-line form. The first command-line form is a separate client.
Select @ @tx_isolation; Be able to see the isolation level at the moment.

Set TRANSACTION isolation Levels < level name > ability to set isolation levels.
The level name is: {READ Uncommitted | READ COMMITTED | Repeatable READ | SERIALIZABLE}?
It is important to note that the isolation level must be used within the transaction, there is no transaction, and the isolation level is meaningless.

※ Transaction Start TRANSACTION  DELETE from stud WHERE id=1015;  DELETE from stud WHERE id=1014;  SELECT * from stud; ROLLBACK/  COMMIT;

MySQL----Strengthening knowledge points in Java programming