Getting Started with SQL

MySQL Administrator's user name and password: root root

Create a database called MYDB1
Create Database mydb1;
show databases;

Create a MYDB2 database that uses the Utf-8 character set.
Create database mydb2 character set UTF8;

Create a MYDB3 database that uses the Utf-8 character set and with the proofing rules.
Create database mydb3 character set UTF8 collate utf8_general_ci;

View the definition information for the MYDB2 database that you created earlier
Show CREATE Database mydb2;

Delete the previously created MYDB1 database
Drop database mydb1;

View the database in the server and modify the character set of one of the libraries to gb2312;
ALTER DATABASE MYDB2 character set gb2312;
Show CREATE Database mydb2;

Demo Recovery and backup
Create DATABASE TT;
Use TT;
CREATE Table A
(
Name varchar (20)
);
Insert into a (name) values (' aaaa ');
SELECT * from A;
-----See a table with data

For the TT backup operation, start a window command line and execute the following command
Mysqldump-uroot-p Tt>c:\tt.sql

Demo Recovery
1. Delete the library first
Drop database TT;

2. Recovery of the TT Library (1)
2.1 In order to restore the library, create database TT first;
2.2 Re-recovery TT Library
Use TT;
SOURCE C:\tt.sql (Source: Can execute an SQL script)

3. Recovery of the TT Library (2)
2.1 In order to restore the library, create database TT first;
2.2 Recovery Library Mysql-uroot-proot tt<c:\1.sql; (Window command)

Create an Employee table
Use MYDB2;
CREATE TABLE Employee
(
ID int,
Name varchar (40),
Sex varchar (4),
Birthday date,
Entry_date date,
Job varchar (40),
Salary Decimal (8,2),
Resume text
);

Show tables; View all tables of a library
Show create table employee; View the creation details of a table
DESC employee; Look at the table structure

An image column is basically added to the employee table above.
ALTER TABLE employee add image blob;

Modify the job column so that it has a length of 60.
ALTER TABLE employee Modify job varchar (60);

Delete the sex column
ALTER TABLE employee drop sex;

The table name is changed to user.
Rename table employee to user;

Modify the table's character set to Utf-8
ALTER TABLE user character set UTF8;

Column Name name modified to username
ALTER TABLE user change column name username varchar (40);

Delete a table
drop table user;

Use the INSERT statement to insert information for three employees into a table.
Rename table user to employee;
INSERT into employee (Id,username,birthday,entry_date,job,salary,resume) VALUES (1, ' AAA ', ' 1980-09-09 ', ' 1980-09-09 ', ' BBB ', ' aaaaa ');
SELECT * from employee;

Insert the details of the data 1
INSERT into employee values (1, ' AAA ', ' 1980-09-09 ', ' 1980-09-09 ', ' BBB ', ' aaaaa ');

Insert the details of the data 2
INSERT into employee values (' 1 ', ' aaa ', ' 1980-09-09 ', ' 1980-09-09 ', ' BBB ', ' n ', ' aaaaa ');
The data is inserted in single quotation marks, save the data error, if the ID is generated, MySQL will automatically convert the type.

Insert the details of the data 3 (insert Chinese)
To tell MySQL customers to use gb2312 encoding
Show variables like ' chara% ';
Set character_set_client=gb2312;
INSERT into employee (id,username) VALUES (' 3 ', ' Zhang San ');

Do not garbled if you want to view
Show variables like ' chara% ';
Set character_set_results=gb2312;
SELECT * from employee;

Revise the salary of all employees to 5000 yuan.
Update employee set salary=5000;

The salary of the employee with the name ' BBB ' was revised to 3000 yuan.
Update employee set salary=3000 where username= ' BBB ';

The employee's salary of "BBB" was changed to 4000 yuan, and the job changed to CCC.
Update employee set salary=4000,job= ' CCC ' where username= ' BBB ';

Increase the BBB salary by 1000 yuan on the original basis.
Update employee set salary=salary+1000 where username= ' BBB ';

Update the issues to be aware of
Update employee set Username= ' CCC ', salary=9000,birthday= ' 1980-09-09 ',.....................
Update where id=1;
This place forgot to write where, the consequences are very serious.

Delete records with name ' ZS ' in the table.
Delete from employee where username= ' BBB ';

Deletes all records in the table.
Delete from employee;

Use truncate to delete records in a table.
TRUNCATE TABLE employee;

Query the information for all students in the table.
SELECT * from student;

Check the names of all the students in the table and the corresponding English scores.
Select Name,english from student;

Filter the repeating English data in the table.
Select distinct 中文版 from student;

Add 10 extra-long points to all students ' total scores.
Select Name, (Chinese+english+math) +10 from student;

Count each student's total score.
Select Name, (Chinese+english+math) from student;

Use aliases to represent student scores.
Select name as name, (Chinese+english+math) +10 as total from student;
Select name Name, (Chinese+english+math) +10 out of student;

Search for student's grades with name Harry
SELECT * FROM student where name= ' Harry ';

Query students with English scores greater than 90 points
SELECT * FROM student where english> ' 90 ';

Find all students with a total score greater than 200
Select name from student where (Chinese+english+math) >200;

Check the English score between 80-90 students.
Select name from student where english>80 and english<90;
Select name from student where 中文版 between and 90; = = select name from student where english>=80 and english<=90;

Query math scores for 89,90,91 's classmates.
SELECT * FROM student where math in (89,90,91);

All students surnamed Li are queried for their grades.
SELECT * FROM student where name like ' Li% ';
SELECT * from student where name is like ' Li _ ';

Query Math >80, the language of >80 students.
SELECT * FROM student where math>80 and chinese>80;

Sort the math scores after the output.
Select Name,math from student order by math;

Sort the output after the total score, and then output in order from high to low
Select name Name, (Chinese+english+math) total score from student order by (CHINESE+ENGLISH+MATH) desc;
Select name Name, (Chinese+english+math) total score from student order by total: desc;

Sort out the grades of students surnamed Li
SELECT * FROM student where name like ' Li% ' order by (Chinese+english+math) desc;

How many students are there in a class?
Select count (name) from student;
Select COUNT (*) from student;

How many students with a statistical math score greater than 90?
Select COUNT (*) from student where math>80;

What is the number of people with total statistics greater than 250?
Select COUNT (*) from student where (Chinese+english+math) >250;

Details about the function of count (count only the rows with values)

Statistic a class math total?
Select sum (math) from student;

Statistics of a class of Chinese, English, mathematics, the total scores of various subjects
Select SUM (Chinese), sum (中文版), sum (math) from student;

Statistics a class of Chinese, English, mathematics, the sum of the results
Select SUM (chinese+english+math) from student;

Statistic the average score of a class's Chinese score
Select SUM (Chinese)/count (*) from student;

Statistic the average score of a class's Chinese score
Select AVG (Chinese) from student;

To find the average score of a class total
Select AVG (chinese+math+english) from student;

To find the highest and lowest grades of class
Select Max (chinese+math+english), Min (chinese+math+english) from student;

Displays the total price of each type of item after sorting the items in the order form
Select Product,sum (Price) from the orders group by product;

Inquire about the purchase of several kinds of goods, and each kind of total price more than 100 of goods
Select product from Orders GROUP by product have sum (price) >100;

Define PRIMARY KEY constraints (each table must have a primary key column)
CREATE TABLE Student
(
ID int PRIMARY KEY,
Name varchar (40)
);

Defining auto-growth for primary keys
CREATE TABLE Student
(
ID int primary KEY auto_increment,
Name varchar (40)
);

Defining UNIQUE constraints
drop table student;
CREATE TABLE Student
(
ID int primary KEY auto_increment,
Name varchar (+) Unique
);

Define a non-null constraint
drop table student;
CREATE TABLE Student
(
ID int primary KEY auto_increment,
Name varchar (max) unique NOT NULL
);

Defining FOREIGN KEY constraints
CREATE TABLE Husband
(
ID int PRIMARY KEY,
Name varchar (40)
);

Create TABLE wife
(
ID int PRIMARY KEY,
Name varchar (40),
husband_id int,
Constraint HUSBAND_ID_FK foreign KEY (husband_id) references husband (ID)
);

Table design when a one-to-many or many-on object is stored in the database
Departments and employees
CREATE TABLE Department
(
ID int PRIMARY KEY,
Name varchar (40)
);

CREATE TABLE Employee
(
ID int PRIMARY KEY,
Name varchar (40),
Salary Decimal (8,2),
department_id int,
Constraint DEPARTMENT_ID_FK foreign KEY (department_id) references Department (ID)
);

Design of tables with many-to-many objects (teachers and students)
CREATE TABLE Teacher
(
ID int PRIMARY KEY,
Name varchar (40),
Salary Decimal (8,2)
);

CREATE TABLE Student
(
ID int PRIMARY KEY,
Name varchar (40)
);

CREATE TABLE Teacher_student
(
teacher_id int,
student_id int,
Primary KEY (TEACHER_ID,STUDENT_ID),
Constraint TEACHER_ID_FK foreign KEY (teacher_id) references teacher (ID),
Constraint STUDENT_ID_FK foreign KEY (student_id) references student (ID)
);

Database design for one-to-one objects
CREATE TABLE Person
(
ID int PRIMARY KEY,
Name varchar (40)
);

CREATE TABLE Idcard
(
ID int PRIMARY KEY,
City varchar (40),
Constraint ID_FK foreign key (ID) references person (ID)
);

Self-connected tables
CREATE TABLE Person
(
ID int PRIMARY KEY,
Name varchar (40),
parent_id int,
Constraint PARENT_ID_FK foreign KEY (parent_id) references person (ID)
);

Select
...
From
...
where
...
GROUP BY
...
Having
...
ORDER BY
...
The order of the above keywords can not change, strictly abide by

Connection Query

1, internal connection (natural connection): A and B tables can be fully matched records query out called the internal connection

(equivalent connection in the SQL92 connection)
Mysql> Select D.name,e.name from department d,employee e where d.id=e.department_id;

(equivalent connection in SQL99) enables department and employee to be connected.
Mysql> Select D.name,e.name from Department D joins employee E on d.id=e.department_id;

2. External connection:
A and B tables can exactly match the records of the query out, the records of one of the tables unconditionally fully queried, the table does not match the record will automatically simulate null and matching, this query is called an outer join.
Including
(1) Left OUTER join (all queries without restriction on the left table) leave all the data in the table to the left of the join query
Mysql> Select D.name,e.name from Department D left joins employee E on d.id=e.department_id;

(2) Right outer join (all queries without restriction on the right side of the table)
Mysql> Select D.name,e.name from the employee e right joins department D on D.ID=E.DEPARTMENT_ID;

Note: Any left outer connection can be written as a right outer join

(3) Full-outer connection (all queries with no restriction on both the left and right tables)
3. Self-Connection (connection occurs within a base table)

Getting Started with SQL