Simple MySQL tutorial and table design principles _ MySQL

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; // describe how many teachers the student has in the course public Set
 
  
Teachers;/* Various get sets are omitted *//****