“This is the 25th day of my participation in the First Challenge 2022. For details: First Challenge 2022”

MySQL > create table with SQL

We created the database my_learn.

Then the three tables were created: Student, Course, and SC.

Today we are going to insert and query data into the database.

Insert data

INSERT data using SQL’s INSERT INTO syntax

INSERT INTO statements can be written in two ways.

The first form does not specify the name of the column into which the data is to be inserted, but simply provides the inserted value:

INSERT INTO table_name
VALUES(value1,value2,value3,...) ;Copy the code

The second form requires specifying the column name and the value to be inserted:

INSERT INTO table_name (column1,column2,column3,...)
VALUES(value1,value2,value3,...) ;Copy the code

Next, we insert some data into the Student table:

INSERT INTO `Student` (Sno, Sname, Ssex, Sage, Sdept) VALUES ('9512101'.'li yong'.'male'.'the'.'Computer Department');
INSERT INTO `Student` (Sno, Sname, Ssex, Sage, Sdept) VALUES ('9512102'.'Mr Liu'.'male'.'20'.'Computer Department');
INSERT INTO `Student` (Sno, Sname, Ssex, Sage, Sdept) VALUES ('9512103'.'min'.'woman'.'20'.'Computer Department');
INSERT INTO `Student` (Sno, Sname, Ssex, Sage, Sdept) VALUES ('9521101'.'Zhang Li'.'male'.'22'.'Information system');
INSERT INTO `Student` (Sno, Sname, Ssex, Sage, Sdept) VALUES ('9521102'.'Mr. Wu'.'woman'.'21'.'Information system');
INSERT INTO `Student` (Sno, Sname, Ssex, Sage, Sdept) VALUES ('9521103'.'poster'.'male'.'20'.'Information system');
INSERT INTO `Student` (Sno, Sname, Ssex, Sage, Sdept) VALUES ('9531101'.'Qian Xiaoping'.'woman'.'18'.'Department of Mathematics');
INSERT INTO `Student` (Sno, Sname, Ssex, Sage, Sdept) VALUES ('9531102'.'Wang Dali'.'male'.'the'.'Department of Mathematics');
Copy the code

Let’s insert some more data into the Course table:

INSERT INTO `Course` (Cno, Cname, Credit, Semester) VALUES ('c01'.'Computer Culture'.'3'.'1');
INSERT INTO `Course` (Cno, Cname, Credit, Semester) VALUES ('c02'.'VB'.'2'.'3');
INSERT INTO `Course` (Cno, Cname, Credit, Semester) VALUES ('c03'.'Computer Network'.'4'.'7');
INSERT INTO `Course` (Cno, Cname, Credit, Semester) VALUES ('c04'.'Database Basics'.'5'.'6');
INSERT INTO `Course` (Cno, Cname, Credit, Semester) VALUES ('c05'.'Advanced Mathematics'.'8'.'2');
INSERT INTO `Course` (Cno, Cname, Credit, Semester) VALUES ('c06'.'Data structure'.'5'.'4');
Copy the code

Insert some more data into the SC table:

INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9512101'.'c01'.'90');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9512101'.'c02'.'86');
INSERT INTO `SC` (Sno, Cno) VALUES ('9512101'.'c06');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9512102'.'c02'.'78');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9512101'.'c04'.'66');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9521102'.'c01'.'82');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9521102'.'c02'.'75');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9521102'.'c04'.'92');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9521102'.'c05'.'50');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9521103'.'c02'.'68');
INSERT INTO `SC` (Sno, Cno) VALUES ('9521103'.'c06');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9531101'.'c01'.'80');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9531101'.'c05'.'95');
INSERT INTO `SC` (Sno, Cno, Grade) VALUES ('9531102'.'c05'.'85');
Copy the code

Note that the Run All Queries button is selected. Selecting Run Previous Query will execute only the last SQL statement.

Query data

Query statement is one of the most important statements in database operation.

The basic structure of a query statement:

SELECT <The target column name, separated by commas>
  FROM <Data sources, which tables are coming from>
  [WHERE <Retrieval condition expression>]
  [GROUP BY <Grouping by column>]
  [HAVING <Group extraction conditions>]
  [ORDER BY <Sorting conditions>];Copy the code

Let’s use SELECT statements with some field queries.

Single table query

Query all student id and name.

SELECT Sno, Sname FROM Student;
Copy the code

Query all student id, name, department.

SELECT Sno, Sname, Sdept FROM Student;
Copy the code

Third, query all the information of all students.

SELECT Sno, Sname, Ssex, Sage, Sdept FROM Student;
Copy the code

We can also use * instead.

SELECT * FROM Student;
Copy the code

Select * from student where name and year of birth;

In the Student table, only the age of the Student is recorded, but the year of birth of the Student is not recorded. However, we can calculate the year of birth based on the current year and the age of the Student, and subtract the age from the current year to get the year of birth. The following statement:

SELECT Sname, 2022 - Sage FROM Student;
Copy the code
Copy the code

Select name, year of birth, and add a column in front of the “year of birth” column as “year of birth”.

SELECT Sname, 'age ', 2022-sage FROM Student;Copy the code

Specifies the alias for the column

We can use AS to specify column aliases to make the data more intuitive.

SELECT * FROM Student where Sname = 'Sage' and Sname = 'Sage';Copy the code

At the end

In this article we learned how to insert data, and can easily query data.

In the next article, we’ll continue to learn about other queries for the SELECT statement.