Simple MySQL tutorial and Table Design Principles

Install and configure MySQL.

 

MySQL CRUD (add, delete, modify, and query)

 

List all databases

show databases;                                   

Create a database

-- Create database mydb1 using MySQL Default Configuration; -- create database mydb2 Character Set utf8 using utf8 encoding; -- create database mydb3 Character Set utf8 collate utf8_general_ci Based on utf8 encoding and utf8 verification rules; -- create database mydb2 based on the actual database creation Information show create database mydb2;

Delete Database

drop database mydb1;

 

Modify Database

-- Modify the database and change the database encoding to gb2312alter database mydb2 Character Set gb2312;

 

 

Create a table 

-- Use mydb3 database use mydb3; -- create a table named employee (ID int, -- variable character, up to 20 name varchar (20), Gender char (1 ), birthday date, entry_date date, job varchar (40), -- currency format, 8-digit number, 2-digit decimal salary decimal (8, 2), resume text -- use UTF-8 encoding) Character Set utf8;

Modify Table

-- Display the table structure DESC employee; -- display the table creation statement show create table employee; -- change the table name to user. Rename table employee to user; -- modify the character set of the table to utf-8alter table user Character Set utf8; -- add an alter table employee add image blob binary data column named image on the original table structure; -- change the column name to usernamealter table user change column name username varchar (40); -- modify the job column to 60 in length. Alter table employee modify job varchar (60); -- delete the sex column. Alter table employee drop gender;

 

 

Insert data 

insert into employee(id,name) values(1,'aaa');

 

Modify data

-- Change the salary of all employees to 5000 yuan. Update employee set salary = 5000; -- change the employee's salary with the name 'zs' to 3000 yuan. Update employee set salary = 3000 where name = 'aaa'; -- change the salary of an employee whose name is 'aaa' to 4000 RMB, and change job to CCC. Update employee set salary = 4000, entry_date = '1970-08-08 'Where name = 'aaa'; -- increase the salary of Wu by 1980 RMB. Update employee set salary = salary + 1000 where name = 'aaa ';

 

Delete data

-- Delete the record named 'zs' in the table. Delete from employee where name = 'aaa'; -- delete all records in the table. Delete from employee; -- delete truncate table employee row by row; -- Overall destruction

 

Query data

-- Query all data in the table select * from employee; -- query the information of all students in the table. Select * from student; -- query the names and English scores of all students in the table. Select name, English from student; -- filter duplicate data in the table based on English. Select distinct English from student; -- add 10 points to the mathematical scores of all students. Select name, math + 10 from student; -- calculate the total score of each student. Select name, (Chinese + English + math) from student; -- use aliases to indicate student scores. Select name as name, (Chinese + English + math) as total score from student; Select name, (Chinese + English + math) total score from student; -- select * from student where name = 'wang wu'; -- select * from student where English score> 90; -- select * from student where (Chinese + math + English)> 200 for all students whose total score is greater than 200; -- query the students whose English score is between 80 and 90. Select * from student where English> 80 and English <90; select * from student where English between 80 and 90; -- Query students whose mathematical scores are, 90, and 91. Select * from student where math in (, 90, 91); -- query the scores of all students surnamed Li. Select * from student where name like 'li _ '; -- Query math score> 80, Chinese score> 80. Select * from student where math> 80 and Chinese> 80; -- sorts mathematical scores and outputs them. Select name, math from student order by math; -- Sort the total score and output the Select name in the order from high to low (Chinese + math + English) total score from student order by total score DESC; -- output select name (Chinese + math + English) for the student scores whose surname is Li) total score from student where name like 'Li % 'order by total score DESC; -- count the total number of students in a class? Select count (*) from student; select count (name) from student; -- how many students have scored more than 90 in mathematics? Select count (math) from student where math> 90; -- how many people have a total score greater than 250? Select count (*) from student where (Chinese + math + English)> 250; -- calculate the total mathematics score of a class? Select sum (math) from student; -- calculate the total scores of the Chinese, English, and mathematics subjects in a class. Select sum (Chinese), sum (math), sum (English) from student; -- calculate the sum of the Chinese, English, and mathematics scores of A Class. Select sum (Chinese + math + English) from student; -- calculate the average Chinese score of A Class. Select sum (Chinese) /count (Chinese) from student; -- calculate the average Chinese score of a class? Select AVG (Chinese) from student; -- calculate the average score of A Class. Select AVG (Chinese + math + English) from student; -- calculate the highest score and the lowest score of the class. Select max (Chinese + math + English), min (Chinese + math + English) from student; -- After classifying the goods in the order table, show the total price of each type of item select product, sum (price) from orders group by product; -- query the purchased items, select Product from orders group by product having sum (price)> 100 for items with a total price of more than 100;

 

 

General principles of Database Table Design

In the general Java-web design, to reduce coupling, JavaBean is used as the medium for data transmission, java-bean is finally passed to the display layer or data operation layer for User display or server storage.

In Java-bean, various attributes of the message object are stored. in the database, the corresponding object is used as a separate table, and columns (fields) in the table are also stored) it is the main attribute in Java-bean.

Example 1:

Java-bean

// A student class public student {public int ID; // This is equivalent to the primary key public string name; Public String address; Public string class; // describes the number of instructors required for this student. The public set <teacher> teachers;/* Various get sets are omitted *//****/}

 

In this bean, all attributes describe the basic information of student. Currently, the student table in data is designed based on the Web service objects. The fields in the student table should be consistent with the attributes in the bean, because they represent the same thing.

Table student

+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id         | int(11)     | YES  |     | NULL    |       || address    | varchar(50) | YES  |     | NULL    |       || class      | varchar(20) | YES  |     | NULL    |       || teacher_id | int(11)     | YES  |     | NULL    |       |+------------+-------------+------+-----+---------+-------+

 

It looks like a rough one, but it is independent, Because Java-bean directly contains a list of instructors, which points to a set of instructors. The field here is only a value, and the primary key and foreign key need to be used here.

Now let's assume there is a teacher table (Java-bean is not described in detail, similar to the bean of student). The structure is as follows:

+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id         | int(11)     | YES  |     | NULL    |       || address    | varchar(50) | YES  |     | NULL    |       || student_id | int(11)     | YES  |     | NULL    |       |+------------+-------------+------+-----+---------+-------+

 

The two tables have no relevance. However, their IDs are unique and can be set as primary keys in the database.

-- Modify the ID field of student as the primary key (primary key) alter table student modify ID int primary key; -- modify the ID field of teacher as the primary key) alter table teacher modify ID int primary key;

 

Then define the foreign key

-- Modify the table student and add a constraint named teacher_id_fk to associate teacher_id with the ID field alter table student add constraint teacher_id_fk foreign key (teacher_id) of the teacher table) references teacher (ID );

 

The relationship between the two tables is as follows:

   +------------+-------------+------+-----+---------+-------+   | Field      | Type        | Null | Key | Default | Extra |   +------------+-------------+------+-----+---------+-------+   | id         | int(11)     | NO   | PRI | NULL    |       |   | address    | varchar(50) | YES  |     | NULL    |       |   | class      | varchar(20) | YES  |     | NULL    |       ||--| teacher_id | int(11)     | YES  | MUL | NULL    |       ||  +------------+-------------+------+-----+---------+-------+||||  +------------+-------------+------+-----+---------+-------+|  | Field      | Type        | Null | Key | Default | Extra ||  +------------+-------------+------+-----+---------+-------+|->| id         | int(11)     | NO   | PRI | NULL    |       |   | address    | varchar(50) | YES  |     | NULL    |       |   | student_id | int(11)     | YES  |     | NULL    |       |   +------------+-------------+------+-----+---------+-------+

 

But this is not what we want. The attributes of students or teachers described in bean are a set, but here we can only be a single one!

The above requirement is a multi-to-many relationship, which can only implement one-to-many relationships. For multi-to-many relationships, there is a common principle. Create an intermediate table as follows:

                         student   +------------+-------------+------+-----+---------+-------+   | Field      | Type        | Null | Key | Default | Extra |   +------------+-------------+------+-----+---------+-------+*->| id         | int(11)     | NO   | PRI | NULL    |       ||  | address    | varchar(50) | YES  |     | NULL    |       ||  | class      | varchar(20) | YES  |     | NULL    |       ||  | teacher_id | int(11)     | YES  |     | NULL    |       ||  +------------+-------------+------+-----+---------+-------+||                       student_teacher|  +------------+-------------+------+-----+---------+-------+|  | Field      | Type        | Null | Key | Default | Extra ||  +------------+-------------+------+-----+---------+-------+└--| teacher    | int(11)     | NO   | MUL | NULL    |       |┌--| student    | varchar(50) | NO   | MUL | NULL    |       ||  +------------+-------------+------+-----+---------+-------+||                      teacher|  +------------+-------------+------+-----+---------+-------+|  | Field      | Type        | Null | Key | Default | Extra ||  +------------+-------------+------+-----+---------+-------+*->| id         | int(11)     | NO   | PRI | NULL    |       |   | address    | varchar(50) | YES  |     | NULL    |       |   | student_id | int(11)     | YES  |     | NULL    |       |   +------------+-------------+------+-----+---------+-------+

In this way, the problem is solved. The advantage of doing so is that the logic is clearer, but the disadvantage is that the CRUD operation is relatively complicated. Another way is to combine the three tables into one table, which is the easiest, and the CRUD operation is easier, however, the data redundancy is too large. Each has its own advantages and disadvantages.

 

The Design of tables in multiple modes is like this. The other one-to-one and many-to-many tables can be better understood.