MySQL basic Operation record statement

The following is an example of the database "Ceshi"

1, connect the database

Mysql-u username-p Password

2. Create/Delete Database

Created: Create database Ceshi;

Delete: Drop database Ceshi;

3. Create/delete datasheet

Created: CREATE TABLE students (SID Int (a) auto_increment primary key,name varchar (255), course varchar (255), score int (10));

Delete: drop table students;

Set the data table encoding: ALTER TABLE ' students ' default character set UTF8 collate utf8_general_ci;

4. Inserting data

Single insert: INSERT INTO students (Name,course,score) values (VALUE1,VALUE2,VALUE3);
Multiple inserts: INSERT into students (Name,course,score) SELECT value1[0],value1[1],value1[2] Union select Value2[0], value2[1], VALUE2[2] Union ...
Read multiple data from another table to add to the new table: INSERT into students (COL1,COL2,COL3) select A,b,c from TableA;
To add data from more than one table to a new table: Insert Ioto tablename (col1,col2,col3) Select A,b,c from TableA where a=1 union ALL select A,b,c from TableB where a=2 the union all in the top code if you change to union, the same record is inserted only once and not inserted repeatedly.
into all the code above can be omitted!

5. ORDER BY statement

SELECT * from students order by score (ASC); From low to High row, the default, ASC can be omitted

SELECT * from students order BY score Desc; From height to low row

6. GROUP BY statement

SELECT * FROM students group by course; The query data is grouped by course, showing only the first one that is queried

SELECT * FROM Students group BY course order by Socre; The order by must be executed after group by, which will not be ordered by the group by, or by the ordering by, if there is only one record after group by. To find out which one of the largest or smallest fields in group by IS using the Max or Min function.

--==================== simple check and delete to change ===========

--View all the data for the student table

SELECT * FROM Studio

--Insert a new student information

INSERT into Studio (St_name,st_sex,st_age,st_add,st_tel) VALUES ("Huanglan", 0, 36, ' Nanchong ', ' 13943943334 ')

--View all class data

SELECT * FROM class

--Add two piece of data to the class table

INSERT into Class (Cl_class,cl_coding,cl_o_time,cl_remark) VALUES (' New electric Training class ', ' gxa-ncs-001 ', ' 2008-03-11 ', ' All very good friends ')

INSERT into Class (Cl_class,cl_coding,cl_o_time) values (' Aba teachers ' training class, ' gxa-absz-001 ', ' 2008-03-11 ')

--The importance of updating a data condition

Update class set cl_remark= ' really nice ' where cl_id=5

--The importance of deleting a single data condition

Delete from class where cl_id=7

--Modifying column headings

Select cl_id as ' class primary key ', Cl_class as ' class name ' from class

Select name =st_name from Studio

--Using text strings

Select ' Name is: ', st_name from studio

--============= condition slightly complex point of check and delete change ==============

--mainly related to or not between in like > < =!>!<!= <> () <= >= is null.

--Query for all information cl_id greater than 1

SELECT * FROM class where cl_id>1

--Use or

SELECT * from class where cl_id<>10 or cl_class= ' Hundred Jie class '

--Use and

SELECT * from class where cl_id<>10 and Cl_class= ' Hundred Jie class '

--use like and%

SELECT * FROM class where cl_class like ' percent Jay '

SELECT * FROM class where cl_remark like '% a.m. '

--Using between

SELECT * from class where cl_id between 3 and 5

--using between with not

SELECT * from class where cl_id not between 3 and 5

--use is not NULL

SELECT * from class where Cl_remark was NOT null

--Use in

SELECT * from class where cl_class in (' Thousand Star class ', ' Hundred Second class ')

--================= using mathematical operators ======================

--mainly related to + = *

--Query Java related courses How many weeks to go according to 5 days a week, 6 classes a day to calculate

Select ' Results ' =CO_NUM/5/6 from course where co_name in (' Java Basics ', ' Java Project Primer ')

--================== Using summary functions ========================

--involving Count SUM AVG MAX MIN

--How many doors are there in a course with less than 50 hours of inquiry?

Select COUNT (*) from course where co_num<50

--How many hours are there for all courses?

Select SUM (co_num) from course

--Calculate the whole class fee, suppose 50 dollars per lesson

Select SUM (co_num) *50 from course

--The minimum course of inquiry

Select min (co_num) from course

--most of the courses in the inquiry class

Select Max (co_num) from course

--average number of hours per course

Select AVG (co_num) from course

Aggregate functions:
Max: Max () min. () average () AVG ()
Sum: Sum () Total: count ()
For example: The average wage for each department
Select Department, AVG (Basic salary) as department base salary from employee table Group by department
Department showing average base salary greater than 3000
Select Department, AVG (base salary) from Employee table Group Department where AVG (base salary) >3000
This is a wrong sentence. SQL rules the use of conditions in a group cannot be used where instead of having
Select Department, AVG (base salary) from Employee table GROUP by department having AVG (base salary) >3000

Eight, multiple table query:
A database of multiple tables, there is a certain link, how normal display such a table information?
There are now three tables:
Yg
Name Sex Age
Samnang Male 20
Ma Dongxu Female 40
Gs
Name Title Date Unit
Samnang AD detailed 2006-11-10 Tsinghua University
Ma Dongxu Linux 2005-01-01 Renmin University
Dz
Company Address
Five crossing of Tsinghua University
Renmin University Huang Zhuang

The first method is called a cross join, also known as the Cartesian product in SQL Server
But note that the total number of default generated records is the product of two-table records
SELECT * from Yg,gs;
SELECT * from Yg,gs where yg.name=gs.name;
This is the record we want.
The second method is to connect by join:
INNER JOIN
SELECT * FROM YG join GS on Yg.name=gs.name
Left OUTER JOIN
Right outer join
But no full outer joins

Ix. Joint:
In addition to the connection, Mysql4. More than 0 versions also support the Union operator, which is used to connect the output of multiple select query numbers into a single result set. In most cases, this operator is used to add the result set produced by the query to a different table, while creating a separate table that includes all the results. For example, interview when asked you, there are two tables, field information, so that you use a statement of two tables of information into a separate table!
In order to illustrate the use of the Union operator, we give an example: now there are two tables, are stored in the information of male students and female students, if a statement will be all the students to display the information!
Mysql> select * from Nan;
+--------+-------+
| name | Score |
+--------+-------+
|    Peng Cong Stay | 80 |
|    Cost Excellent | 81 |
|    Chiquan | 82 |
+--------+-------+
3 Rows in Set (0.00 sec) mysql> SELECT * from NV;
+------+-------+
| name | Score |
+------+-------+
|    Peng Hong | 80 |
|    Fehon | 81 |
|    Chougon | 82 |
+------+-------+
3 Rows in Set (0.00 sec)
Mysql> select * FROM nan Union SELECT * from NV;
+--------+-------+
| name | Score |
+--------+-------+
|    Peng Cong Stay | 80 |
|    Cost Excellent | 81 |
|    Chiquan | 82 |
|    Peng Hong | 80 |
|    Fehon | 81 |
|    Chougon | 82 |
+--------+-------+
6 rows in Set (0.00 sec)
What if there are three watches? is the same operation!
Note, however, that if a record of two tables is exactly the same, only one is displayed; If you want to show all the records, add all after the Union
Mysql> select * FROM nan UNION ALL SELECT * from NV;
If the interviewer asks you again, what if you want to save the displayed information to a table?
Mysql> CREATE TABLE table name SELECT statement;