Getting Started with SQL

CSDN Blog Relocation

To connect to a database:
1. Under DOS window, under the Bin directory of the installation directory of the database, start the database service using the MYSQLD command, or start the MySQL service in the service of the computer.
2. Open a DOS window and go to the bin directory of the installation directory of the database and use the command to connect to the database server: Mysql-u root-p

First, database creation, modification, backup, recovery
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, enter the database's installation directory in the bin directory, 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)

second, CREATE table to modify the table structure
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;

Iii. inserting statements
Use the INSERT statement to insert information for three employees into a table. (Inserted character and date type data should be enclosed in single quotes)
Rename table user to employee;
INSERT into employee (Id,username,birthday,entry_date,job,salary,resume) VALUES (1, ' AAA ', ' 2015-09-29 ', ' 1980-09-09 ', ' BBB ', ' aaaaa ');
SELECT * from employee;

Insert the details of the data 1 (you can not specify the field, as long as the inserted value and the field of the table exactly match the row)
INSERT into employee values (1, ' AAA ', ' 1980-09-09 ', ' 1980-09-09 ', ' BBB ', ' aaaaa ');

Insert the details of the data 2 (you can add a single quotation mark to each inserted field value, and MySQL will automatically convert to the corresponding type when it gets the data)
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 the MySQL client 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;
iv. updating of data
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.

v. Delete records from a table
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;

The difference between delete and TRUNCATE TABLE
Delete is the deletion of records in a table, truncate is to destroy the table structure, and then rebuild the table structure

Vi. Query Statements
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 counts only the rows that have values, and if the column value is empty, the column is not counted)


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;

The difference between where and having
Both can be used for filtering, but the where is not followed by an aggregate function, which can be used in conjunction with the aggregate function, general and GROUP by

vii. Defining Constraints
Define PRIMARY KEY constraints (each table must have a primary key column, not allowed to be empty, must be inserted)
CREATE TABLE Student
(
ID int PRIMARY KEY,
Name varchar (40)
);

Define the primary key auto-growth (programmers do not have to define the field of automatic growth of the main key, by the database management, note that when a record is deleted and then inserted a record automatically grow the field will skip the previous occurrence of the value, because it has been added to that value, will

Continue to add)
CREATE TABLE Student
(
ID int primary KEY auto_increment,
Name varchar (40)
);

Define a UNIQUE constraint (the value of the field cannot be duplicated, such as the name cannot be duplicated)
drop table student;
CREATE TABLE Student
(
ID int primary KEY auto_increment,
Name varchar (+) Unique
);

Define a non-null constraint (the value of the field cannot be empty)
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)
);

viii. design of database tables in actual development
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