Simple MySQL tutorial and table design principles _ MySQL

Source: Internet
Author: User
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 *//****
 

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.