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 the tables in the library (view the tables in the library to open the library first)
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 ');

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;

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 the name Wu
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)
);

Getting Started with SQL