MySQL database crud statement Quick Start

Directory:

SQL statement... 1

1. Database and table operations... 1

1. Common Database and table operations... 1

2. Operate the database... 2

3. operation table... 3

4. add, delete, modify, and query data in the table... 4

5. Table constraints... 9

6 Table relationships... 11

Day11

SQL statement

SQL statement, a language used to operate databases

Database, which can be easily understood as files on hard disks and can store data

SQL statements can be roughly classified into two categories

How to access the database:

1. Command LineCode:

Enter database c: \ mysql-hlocalhost-p3306-uroot-p123456

Start Database Net start MySQL

Stop Database Net stop MySQL

I. Operations on database and table 1. Common Operations on database and table

Create

View show

Alter

Delete drop

// Create a database

Create Database mydb;

// View all database Databases

Show databases;

// Delete mydb

Drop database mydb;

// Delete the user table

Drop table user;

Operations on records in a table

Add insert

Delete Delete

Update

Query select

2. operate databases

Create a database named mydb1

Create Database mydb1;

Create a mydb2 database using the UTF-8 character set.

Create Database mydb2 Character Set utf8;

Create a mydb3 database that uses the UTF-8 character set and has verification rules.

Create Database mydb3 Character Set utf8 collate utf8_general_ci;

View all databases on the Current Database Server

Show databases;

View the definition and character information of the mydb2 database created earlier.

Show create database mydb2;

Delete the mydb3 database created earlier

Drop database mydb3;

Modify the character set of mydb2 to GBK

Alter database mydb2 Character Set GBK;

// Back up the database

Use mydb2;

Create Table

(

Name varchar (20)

);

// Insert data

Insert into a (name) values ("zhangsan ");

Insert into a (name) values ("wangwu ");

// Back up the database

Mysqldump-uroot-proot mydb1> D: \ A. SQL

This command is similar to MySQL, so it cannot be used in MySQL.

// Delete mydb2

Drop database mydb2;

// Restore the database

Create Database mydb22;

Source D: \ A. SQL

// Source command

Specify an SQL script

Disable Error Message

Mysql-uroot-p123456-B

3. operation table

1. Create a table

Create employee table

Id Shaping

Name authentication type

Sex plugin or bit

Brithday

Entry_date date type

Job validation type

Salary decimal type

Resume large text type

Create a table:

Create Table employee

(

Id int,

Name varchar (20 ),

Gender varchar (6 ),

Brithday date,

Entry_date date,

Job varchar (20 ),

Salary double,

Reshme text

);

2. modify a table

Add an image Column Based on the employee table above

Alter table employee add image blob;

Modify the job column to 60

Alter table employee modify job varchar (60 );

Delete the sex column.

Alter table employee drop sex;

Change the table name to users.

Rename table employee to user;

Modify the table character set to UTF-8

Alter table user Character Set utf8;

Change column name to Username

Alter table user change column name username varchar (20 );

View All Tables

Show tables;

View the table creation statement

Show create table user;

View table structure

Desc user;

Delete user table

Drop table user;

4. add, delete, modify, and query data in the table

There are almost four data operations for the table: add, delete, modify, query, crud create

1. Insert statement

Employee. SQL

Create Table employee

(

Id int,

Name varchar (20 ),

Sex varchar (10 ),

Birthday date,

Salary float,

Entry_date date,

Resume text

);

// Insert three records into the employee table

Insert into employee (ID, name, sex, birthday, salary, entry_date, resume) values (1, 'hangsan', 'male', '2017-11-23 ', 1987, '2014-2-18 ', 'good boy ');

Insert into employee (ID, name, sex, birthday, salary, entry_date, resume) values (2, 'wangwu', 'male', '2017-11-23 ', 1988, '2014-2-18 ', 'good boy ');

Insert into employee (ID, name, sex, birthday, salary, entry_date, resume) values (3, 'xiaohong ', 'female', '2017-11-23 ', 1983, '2014-2-18 ', 'Good girl ');

Insert into employee (ID, name, sex, birthday, salary, entry_date, resume) values (4, 'nan Zhao ', 'male', '2017-11-23', 1986, '2017-2-18 ', 'Super good guy ');

// View all records of the table

Select * from employee;

// View all codes used by the data

Show variables like 'character % ';

// Modify the client encoding to GBK

Set character_set_client = GBK;

// Modify the result set encoding to GBK.

Set character_set_results = GBK;

// Other insert statements

Create Table

(

Username varchar (20 ),

Password varchar (20)

);

Insert into a values ('zs', '123 ');

Insert into a values ('wangw', '000000'), ('lisi', '000000 ');

Insert a values ('aaa', 'bbb ');

Insert a (username) values ('aaa ');

2. Update statement

Change the salary of all employees to 5000 yuan

Update maid set salary = 5000;

Change the salary of an employee named 'zhangsan' to 3000 yuan.

Update employee set salary = 3000 where name = 'hangsan ';

Change the salary of an employee whose name is 'lisi' to 4000 yuan, and change sex to female.

Update employee set salary = 4000, sex = 'female 'Where name = 'wangwu ';

Increase Xiaohong's salary by RMB 1000 based on the original salary.

Update employee set salary = salary + 1000 where name = 'xiaohong ';

3. Delete statement

Delete the record whose name is "Zhao Nan" in the table.

Delete from employee where name = 'zhaonan ';

Delete all records in the table. Delete is the deletion of multiple entries. If there are many entries, the efficiency will be very low.

Delete from employee;

Use truncate to delete records in a table. Destroy the table and create a table. Try to use this

Truncate employee;

4. SELECT statement

Student. SQL

Create Table student (

Id int,

Name varchar (20 ),

Chinese Float,

English float,

Math float

);

Insert into student (ID, name, Chinese, English, math) values (1, 'zhang Xiaoming ', 89,78, 90 );

Insert into student (ID, name, Chinese, English, math) values (2, 'Li Jin ', 67,53, 95 );

Insert into student (ID, name, Chinese, English, math) values (3, 'wang wu', 87,78, 77 );

Insert into student (ID, name, Chinese, English, math) values (4, 'Li yi', 88,98, 92 );

Insert into student (ID, name, Chinese, English, math) values (5, 'lilaicai ', 67 );

Insert into student (ID, name, Chinese, English, math) values (6, 'zhangjinbao', 45 );

Insert into student (ID, name, Chinese, English, math) values (7, 'huangrong ', 30 );

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.

Select distinct English from student;

Add 10 points of expertise to all student scores.

Select name, English + 10, Chinese + 10, math + 10 from student;

Calculate the total score of each student.

Select name, English + Chinese + math as sum from student;

Use aliases to indicate student scores.

Where clause

Query the score of a student named Li Yi

Select * from student where name = 'Li yi ';

Query students whose English score is greater than 90

Select * from student where English> 90;

Query all students whose total score is greater than 200

Select name, English + Chinese + math sum from student where English + Chinese + math> 200;

You do not need to use

Operator

Query the students whose English scores are between 80 and 90.

Select * from student where English between 65 and 85;

Students whose mathematical scores are, 90, and 91 are queried.

Select name, math from student where math in (89,90, 91 );

Query the scores of all students surnamed Li.

Select * from student where name like 'Li % ';

// Query the two students surnamed Li

Select * from student where name like 'Lee _';

The number of students with a query score greater than 80 in mathematics and a Chinese score greater than 80.

Select * from student where math> 80 and Chinese> 80;

Query English> 80 or total score> 200

Select *, Chinese + math + English from student where English> 80 or Chinese + English + math> 200;

Order by clause

Sorts mathematical scores and outputs them.

Select * from student order by math;

Output after sorting the total score, and then output in the order from high to low

Select *, Chinese + math + English from student order by Chinese + math + English DESC;

The Order clause must be placed behind the WHERE clause for sorting students' scores surnamed Li.

Select *, Chinese + math + English from student where name like 'Li % 'order by Chinese + math + English;

Aggregate functions

Count

How many students are counted in a class?

Select count (*) from student;

How many students have scored more than 90 in mathematics?

Select count (*) from student where math> 90;

How many people are there with a total statistical score greater than 230?

Select count (*) from student where Chinese + math + English> 230;

Sum

Count the total score of a class's mathematics?

Select sum (math) from student;

Measure the total scores of each class in Chinese, English, and mathematics.

Select sum (math), sum (Chinese), sum (English) from student;

Total scores of Chinese, English, and mathematics in a class

Select sum (Math + Chinese + English) from student;

Average score of each class

Select sum (Chinese)/count (*) from student;

Not involved in Calculation

Select sum (Chinese)/count (Chinese) from student;

AVG

Average Chinese score

Select AVG (Chinese) from student;

Max/min

Highest Chinese score

Select max (Chinese) from student;

Select Statement (6)

Group

Order table

Create Table orders (

Id int,

Product varchar (20 ),

Price float

);

Insert into orders (ID, product, price) values (1, 'TV', 900 );

Insert into orders (ID, product, price) values (2, 'washer ', 100 );

Insert into orders (ID, product, price) values (3, 'washing powder ', 90 );

Insert into orders (ID, product, price) values (4, 'Orange ', 9 );

Insert into orders (ID, product, price) values (5, 'washing powder ', 90 );

Classify items

Select * from orders group by product;

Display single product Summary

Select *, sum (price) from orders group by product;

Product categories show that the total price of a single product is greater than 100

Select *, sum (price) from orders group by product having sum (price)> 100;

// Sort products with a unit price greater than 20 by price

Select * from orders where price> 20 group by product order by price;

5. Table Constraints

When defining a table, we can add constraints to the field while creating the table and limit the data to be inserted in the future.

I. Table Constraints

When defining a table, you must add some constraints to the fields in the table to limit the data to be inserted in the future.

1. unique constraint unique

Create Table

(

Id int,

Username varchar (20) unique,

Password varchar (20)

);

Insert into a (ID, username, password) values (1, 'hangsan', '123 ');

Insert into a (ID, username, password) values (2, 'hangzhou', '123 ');

2. Non-null constraint not null

Create Table B

(

Id int not null,

Name varchar (20)

);

Insert into B (ID, name) values (1, 'aaa ');

Insert into B (ID, name) values (1, 'bbbbb ');

3. Primary Key constraints are equivalent to unique and non-empty constraints.

Each table in the database should have at least one primary key, usually ID

Create Table C

(

Id int primary key,

Name varchar (20)

);

Insert into C (ID, name) values (1, 'aaa ');

Insert into C (ID, name) values (2, 'bbbbb ');

Create Table D

(

Firstname varchar (20 ),

Lastname varchar (20 ),

Primary Key (firstname, lastname)

);

Insert into D (firstname, lastname) values ('Tom ', 'cat ');

Insert into D (firstname, lastname) values ('Tom ', 'HK ');

Create Table E

(

Id int,

Name varchar (20)

);

Insert into e (ID, name) values (1, 'aaa ');

Add primary key constraints to the table

Alter table e add primary key (ID );

Command modified here:

Modify the job column to 60 in length.

Alter table employee modify job varchar (60 );

Delete primary key constraints

Alter table e drop primary key;

4. Define automatic growth of primary keys

This function enables the ID key to automatically increase as the number of entries increases.

Note that the primary key type must be int, and only int type can be auto-incrementing.

Create Table F

(

Id int primary key auto_increment,

Name varchar (20)

);

Insert into F (name) values ('aaa ');

Insert into F (ID, name) values (11, 'bbb ');

Mysql> select * from F;

+ ---- + ------ +

| ID | Name |

+ ---- + ------ +

| 1 | AAA |

| 2 | AAA |

| 3 | AAA |

| 4 | AAA |

| 11 | BBB |

| 12 | AAA |

| 13 | AAA |

| 14 | AAA |

| 15 | AAA |

| 16 | AAA |

+ ---- + ------ +

Foreign key constraint

Binding Force: The inserted foreign key value must be the value in the referenced column.

The referenced data in the referenced table cannot be deleted.

Note: The foreign key constraint is not null or unique binding.

Create Husband and Wife tables

Create Table husband

(

Id int primary key auto_increment,

Name varchar (20)

);

Create Table wife

(

Id int primary key auto_increment,

Name varchar (20 ),

Husbandid int,

Constraint husbandid_fk foreign key (husbandid) References husband (ID)

);

External constraints: constraint defines foreign key constraints. Foreign key is the foreign key name. Refer to the primary key of a table.

Add two records respectively

Insert into husband (name) values ('laobi ');

Insert into husband (name) values ('laoyu ');

Insert into wife (name, husbandid) values ('fengjie', 2 );

Insert into wife (name, husbandid) values ('rongjie', 1 );

Fengjie

Select * from husband where id = (select husbandid from wife where name = 'fengjie ');

Delete from husband where name = 'laoyu ';

6 Table relationships

1-to-1

Create a department table

Create Table Department

(

Id int primary key auto_increment,

Name varchar (20)

);

Add three departments

Insert into Department (name) values ('dashboard ');

Insert into Department (name) values ('salesman ');

Insert into Department (name) values ('personnel authorization ');

Create employee table

Create Table employee

(

Id int primary key auto_increment,

Name varchar (20 ),

Departmentid int,

Constraint specified mentid_fk foreign key (specified mentid) References Department (ID)

);

Add six employees

Insert into employee (name, inclumentid) values ('zhang san', 1 );

Insert into employee (name, inclumentid) values ('Li si', 1 );

Insert into employee (name, inclumentid) values ('wang wu', 2 );

Insert into employee (name, inclumentid) values ('zhao liu', 3 );

Insert into employee (name, inclumentid) values ('tag7', 3 );

Insert into employee (name, partition mentid) values ('Week 8 ', null );

Multi-Table query

Find all employees in department 1

Select * from employee where partition mentid = 1;

Find all employees in the Development Department

Select * from employee where partition mentid = (select ID from department where name = 'dged ');

Find out where Zhao Liu is in.

Select * from department where id = (select employee mentid from employee where name = 'zhao liu ');

--------------------------------------

Joint Query

Find all employees in the Development Department

Select * from department, employee;

+ ---- + -------- + ---- + ------ + -------------- +

| ID | Name | specified mentid |

+ ---- + -------- + ---- + ------ + -------------- +

| 1 | Development Department | 1 | Zhang San | 1 |

| 2 | sales department | 1 | Zhang San | 1 |

| 3 | Personnel Department | 1 | Zhang San | 1 |

| 1 | Development Department | 2 | Li Si | 1 |

| 2 | sales department | 2 | Li Si | 1 |

| 3 | Personnel Department | 2 | Li Si | 1 |

| 1 | Development Department | 3 | Wang Wu | 2 |

| 2 | sales department | 3 | Wang Wu | 2 |

| 3 | Personnel Department | 3 | Wang Wu | 2 |

| 1 | Development Department | 4 | Zhao Liu | 3 |

| 2 | sales department | 4 | Zhao Liu | 3 |

| 3 | Personnel Department | 4 | Zhao Liu | 3 |

| 1 | Development Department | 5 | Tian Qi | 3 |

| 2 | sales department | 5 | Tian Qi | 3 |

| 3 | Personnel Department | 5 | Tian Qi | 3 |

| 1 | Development Department | 6 | week 8 | null |

| 2 | sales department | 6 | week 8 | null |

| 3 | Personnel Department | 6 | week 8 | null |

+ ---- + -------- + ---- + ------ + -------------- +

Arrangement and combination of all records of multiple tables in a Cartesian set

The Cartesian set contains a lot of matched data (the values of the foreign key column of the reference table are inconsistent with those of the primary key of the referenced table ).

Add conditions to remove waste data

Select * from department, employee where employee. Partition mentid = Department. ID;

Add conditions for query

Select * from department, employee where employee. Partition mentid = Department. ID and department. Name = 'dnt ';

Optimize adding aliases to tables to retain only the required data

Select E. * from department d, employee e where E. Required mentid = D. id and D. Name = 'dnt ';

Find out where Zhao Liu is in.

Select D. * from department d, employee e where E. Worker mentid = D. id and E. Name = 'zhao liu ';

2. many-to-many

Create instructor table

Create Table teacher

(

Id int primary key auto_increment,

Name varchar (20)

);

Add three Instructors

Insert into teacher (name) values ('olde'), ('olde'), ('olde ');

Create Table student

(

Id int primary key auto_increment,

Name varchar (20)

);

Add three students

Insert into student (name) values ('taobao'), ('twomao'), ('twomomao'), ('twomao '), ('six hairs '), ('seven hairs'), ('Eight hairs '), ('small hairs ');

Create an intermediate table description

Create Table tea_stu

(

Teaid int,

Stuid int,

Primary Key (teaid, stuid ),

Constraint teaid_fk foreign key (teaid) References teacher (ID ),

Constraint stuid_fk foreign key (stuid) References student (ID)

);

Add data

Insert into tea_stu (teaid, stuid) values ), (2, 3), (2, 5), (2, 7), (2, 9), (3, 2), (3, 3), (3, 4 ),

(3, 5), (3, 6), (3, 7), (3, 8 );

Query the Students taught by instructor 2

Select S. * from student s, tea_stu ts where ts. stuid = S. ID and Ts. teaid = 2;

Query all the students taught by Lao Yu

Select S. * from student s, tea_stu ts, teacher t where ts. stuid = S. ID and Ts. teaid = T. ID and T. Name = 'old ';

Multi-table joint Query

The query result is a Cartesian set.

N tables need n-1 conditions for joint query to remove waste data.

Condition for removing waste data: foreign key column of the reference table = primary key column of the referenced table

Add the query conditions to obtain the results.

3. One-to-one

Create Table person
(
Id int primary key auto_increment,
Name varchar (20)
);

Create Table idcard
(
Id int primary key,
Location varchar (20 ),
Constraint personid_fk foreign key (ID) References person (ID)
);

Insert into person (name) values ('hangsan ');
Insert into person (name) values ('lisi ');

Insert into idcard (ID, location) values (2, 'tianjin ');

Insert into idcard (ID, location) values (1, 'shanghai ');

Check Li Si's ID card
Select idcard. * From person, idcard where idcard. ID = person. ID and person. Name = 'lisi ';

Attachment: MySQL installation Diagram