MySQL----Strengthening knowledge points in Java programming

In the data, table-building processing is a very common and useful 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;" > Example: A husband and wife think: Keep Everyone's information in a table. There are men and women, asked to find out all the husband and wife, 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;//now 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 to find all the cars owned by someone. According to the requirements of the paradigm, two tables should be designed, respectively, to represent the information of people and cars. <strong></strong>

Step 1: Draw the E-r diagram///Step 2: Build the Entity table separately and add the foreign KEY constraint to the table of the multiparty 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//entity table 1INSERT into Person2 (id,pname,sex) VALUES (' P001 ', ' Jack ', ' 1 ') for two tables; 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 '), INSERT into 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 ', ' + ' 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 ', ten, ' P004 '); insert into 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 (use "table name. Column name" To access the column, if the column name is not duplicated, you 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 many roles, such as a person, a father, a son, and a husband. At the same time, these three characters can be given to everyone else. Ask to be the father, the son, and the husband's owner. Find out all the roles owned by everyone. <strong></strong>

Specific implementation methods are posted in the final notes.

constraints on the database primary Key primary key
When you create a table, you specify a primary key---you should use the NOT NULL keyword when declaring a primary key.
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);
Primary key auto-grow:
Auto_increment for fields of type int
Foreign key foreign key-represents a reference relationship
An association with another table-called a foreign key-when the foreign key does not have a record in the primary table, the unreferenced record cannot be written 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. If there are no records in the primary table, the child table will not be incremented.
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.
You can manipulate an out-of-context association through a visual interface. The unique unique– differs from the primary key , the primary key cannot be null, and the unique can have a column null, which is the limit for SQL Server, but MySQL can write to multiple columns of null values, 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 repeat */
Default value Defaults
When you create a table, you can specify default values such as:
Create table stud (sex char (1) Default ' 1 ',.....);

querying data by using associations When the data of a table does not meet our needs, we need to query the data from multiple tables. You must use the associated query at this time:
Inner join–, both must 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

Summary:
Stored Procedures

Three different ways:

1, the simplest and most basic 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 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 procedures with return values----parameters and variables (@ variable name  , one @ for user variable, two @ that is @@ 为 Global system variable) delimiter$ $CREATE PROCEDURE P3 (in ID INT, in nm VARCHAR (30), Out num INT) BEGIN   INSERT into Stud (id,sname) 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 case-sensitive query

<span style= "FONT-SIZE:18PX; White-space:pre; " ></span>mysql queries are case insensitive by default, 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_n Ame where a like ' A% ' <span style= "White-space:pre" ></span> effect is the same. <span style= "White-space:pre" ></span> to make MySQL query case-sensitive, you 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 WH ere binary a like ' A% ' <span style= "White-space:pre" ></span> can also be identified when the table is built   <span style= "White-space :p Re "></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 and cannot execute only part of it.
Consistency (consistency): The database is consistent (database data integrity constraints) before and after transaction execution.
Isolation (isolcation): The impact of one transaction on another transaction processing.
Persistence (Durability): The effect of transaction processing can be permanently saved.
A transaction will have only one result: success or failure.

Start transaction; Begin a transaction. Commit, submit the changes you have made. Rollback; Roll back the changes you made. If an error occurs during operation, a new transaction should be started.

Set the Thing isolation levelSet the isolation level on the MySQL command line.
Setting the isolation level on the MySQL command line is valid only for this open command-line window. The first command-line window is a standalone client.
Select @ @tx_isolation; You can view the current isolation level.
Set TRANSACTION isolation Levels < level name > You can set the isolation level.
The level name is: {READ Uncommitted | READ COMMITTED | Repeatable READ | SERIALIZABLE}
It is important to note that the isolation level must be used in a 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