Database Learning notes 3--Basic SQL statements

"declaration"

Welcome reprint, but please keep the original source of the article →_→

Life One number: http://www.cnblogs.com/smyhvae/

Article Source: http://www.cnblogs.com/smyhvae/p/4028178.html

Body

Main content:

• One, the cmd command line Common command
• Ii. Data Definition language (DDL)
• Third, data manipulation language (DML)
• Iv. data Query Language (DRL)
• V. Transaction control Language (TCL)

One, the cmd command line common command:

When we log in using MySQL 5.5 Command line client, only the root user can log in. If we create other users in the future, it will be very troublesome, so we do not need MySQL 5.5 Command line client, but direct use of CMD.

Log in to the MySQL database:

Because you have configured the environment variable before installing MySQL, you can log in by typing the following command directly into CMD:

Just enter the password immediately.

In the above code,-U represents username, which is the user name. -P indicates password, or password.

To View the database:

Use database : (This command can be followed without a semicolon)

Use database name;

To view the current database table:

Show tables;

Note: The semicolon at the back of the command indicates the end.

Note: Enter show databases first, show four libraries, top three libraries for the system, and the fourth Test is the system's own testing library. Then we use test, which shows: Database changed, which indicates that the current database is active. It can be followed by other related commands. Here we enter show tables, and we can see that the test database table is empty.

So we're going to start building the table, so it's about the SQL statement.

The SQL full name is: Structured Query Language (structured query Language). Since it is a language, it involves grammar. Here's a look at the common syntax.

Ii. Data Definition language (DDL)

Data definition language: Language. such as Create, drop,alter and other statements. It is important to note that the commands in the database are case insensitive.

To Create a database statement:

"Example" creates a new database named MyDB:

CREATE DATABASE MyDB;

Note: If you want to delete a database, change "create" to "drop".

Create a table ( CREATE Table Statement ):

"For example," Create a table named Teacher:

CREATE TABLE Teacher (id int primary KEY auto_increment,name varchar), gender char (1), age int (2), Birth date,
Description varchar (100));

All the code above is actually the same line.

Define the format of a field: the name of the field + the type of field + properties

Note: After each field is defined, it is separated by commas, and the last field does not have a comma.

In general, each table must have a primary key.

The command runs with the following effects:

To view the table structure:

DESC teacher;

The effect is as follows:

It is important to note that a primary key is a field that uniquely represents a record (the primary key value must be unique)

Delete Table (drop TABLE statement):

DROP TABLE teacher;

Note: The DROP TABLE statement deletes all records and table structures

Modify Table Structure (ALTER table statement): (assuming the table name is test)

• ALTER TABLE test Add column job varchar (10); --Add Table columns
• ALTER TABLE test rename test1; --Modify Table name
• ALTER TABLE test drop column name; --Delete Table columns
• ALTER TABLE test Modify address char (10)--Modify the Form column type (change type)
• ALTER TABLE test change address Address1 char (40)--modifies the column type (name and type, as in the following line)
• ALTER TABLE test Change column address Address1 varchar (30)--Modify table column name (change name and type)

Example: Modify the primary key to autogrow: ALTER TABLE tab_teacher change ID ID int auto_increment;

Third, data manipulation language (DML):

Data manipulation language: manipulation Language. Example: INSERT (Increment), UPDATE (change), delete (delete) statement

Add Data (INSERT into ... Statements): (That is, records that add tables)

INSERT into table name (Field 1, Field 2, Field 3) VALUES (value, value, value);

Example:

Insert into Tab_teacher (name,gender,age,createdate) VALUES (' Smyh ', ' B ', 22,now ());

Note: the now () function above can bring up the current system time.

all records of the table are then queried by the following command:

SELECT * from Tab_teacher;

where the wildcard "*" denotes all fields, that is, the records of all fields are checked from the Tab_teacher table.

The display results are as follows:

Modify Data (UPDATE ... Set statement):

UPDATE Table name SET Field 1 name = value, field 2 name = value, field 3 name = value where field name = value;

Note: The section later in the where represents the modified condition. When modified, it is modified in the Order of field 1, Field 2, and Field 3.

Delete data : (delete from ... Statement

Delete all records:

DELETE from table name;

To delete a record with ID 1:

DELETE from table name where id=1;

Iv. data Query Language (DRL):

Data Retrieval Language--DRL: SELECT statement.

In the actual development, the data query language uses the most. Let's take the following table as an example:

Note: In practice, you should avoid using the wildcard "*" (wildcards represent all fields), because the system first resolves all field names, restores them to their real names, and then takes action. If you record a database with a particularly great number of content, it can affect efficiency.

Check all teachers ' information:

SELECT * from Tab_teacher;

Or: (recommended)

Select Id,name,gender,age,job,createdate from Tab_teacher;

Allows you to specify dual as a false table name in cases where no table is referenced:

Dual is a virtual table. That is, the table does not have this data, but to insist on using SQL statements, the system will use this virtual table to meet you. For example, the effect is as follows:

Query for teacher information with ID 2:

SELECT * from Tab_teacher where id=2;

Query the teacher information of occupation empty:

SELECT * from Tab_teacher the where job is null;

Note that in the above code, the word blank is represented by "job is null" instead of "Job=null".

Query the name and gender of the teacher with ID 2:

Select Name,gender from Tab_teacher where id=2;

Query gender for female and specified date teacher information:

Select $ from Tab_teacher where gender= ' W ' and ceatedate= ' 2014-10-14 ';

Note: also use "and", or "or".

To query a teacher with a gender or ID less than 2:

SELECT * from Tab_teacher where gender= ' m ' or id<2;

The teacher who queried the last character of the name "E": (Keyword: like '%+ specified character ')

SELECT * from Tab_teacher where name is like '%e ';

Query the teacher whose name begins with the specified character:

SELECT * from Tab_teacher where name is like ' s% ';

Query the teacher whose name contains "M":

SELECT * from Tab_teacher where name is like '%m% ';

query all teacher information and arrange by date in descending or ascending order: ( Order BY field, + default to ascending: asc/descending: DESC)

• Ascending:

SELECT * from Tab_teacher order by CreateDate;

Note: The last word ASC can be written without writing because the default is ascending.

• Descending:

SELECT * from Tab_teacher ORDER by createdate Desc;

Multiple sort criteria: (when the first condition is the same, sort by the second condition)

For example: The above image is sorted in descending order of date, but the ID is 2 and the date with ID 3 is the same, and these two records are sorted by default.

Example: the first condition for sorting now is: Date descending, second condition: Age descending. Multiple sorting criteria are separated by commas. The command is:

SELECT * from Tab_teacher ORDER by createdate Desc,age desc;

Number of male and female teachers by gender (group BY):

Select Gender,count (gender) from Tab_teacher group by gender;

The above command indicates that the gender field is searched, and the number of gender (count (gender)) is calculated, and then the number is grouped according to the contents of the gender (group by gender).

Question: I think it should be the first group, then query gender, and then the number of gender.

Note: The built-in function of count () is used in this area. The aggregate function for count () is described in the next article.

The correct effect and error effects are as follows:

By gender, the total number of female teachers was queried:

Select Gender,count (gender) from Tab_teacher Group by gender have gender= ' m ';

Here, on the basis of the previous command, added a keyword: having. The HAVING keyword is specifically used to add conditions after grouping . This means: First the total, then group, and then from the group to find the number of fields equal to "M". Note: You cannot use where as a keyword, because where is the condition before grouping.

The display results are as follows:

Note: This type of operation is cumbersome, inefficient, having a statement as little as possible, can be replaced by other statements, such as:

Select Gender,count (gender) from Tab_teacher where gender= ' m ';

The effect is the same.

Total number of records for the query table:

• Mode one: (Not recommended)

Select COUNT (*) from Tab_teacher;

• Mode two: (Query using the primary key method)

Select COUNT (id) from Tab_teacher;

The way one would have empty records also contained in. Therefore, mode two is used because the primary key is unique and cannot be null, and the total number of IDs represents the total number of records.

Usage of aliases:

• Total number of records using the alias query table:

Select COUNT (ID) as counts from Tab_teacher;

The effect of using aliases and aliases is as follows: ("as" in the command can be omitted)

• Use aliases to replace the name of the table:

Example: The name of the table is T.

Select T.name,t.age from Tab_teacher t;

Aliases are handy when the table name is longer, or if multiple tables exist at the same time.

Check the top three of the teacher's records (starting from 0 positions to find 3): (Very useful)

SELECT * from Tab_teacher limit 0, 3;

Command explanation: There are two parameters behind limit: The first parameter is the starting position, and the second parameter is the offset. Declaration: The ID of the first record is 0 regardless of the number of the index.

Note: This paging query method is very useful, for example, when using Baidu Search, tell you the total number of records, but will be paginated display.

V. Transaction control Language (TCL):

Transaction Control Language: Transaction control LANGUAGE--TCL. such as the Commit,rollback statement.

1. Business:

Transaction (Transaction) concept: a transaction (Transaction) is a program execution unit (unit) that accesses and possibly updates various data items in a database. In a relational database, a transaction can be an SQL statement, a set of SQL statements, or an entire program.

properties of the transaction : atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability). These four properties are often called acid properties.

Transactional features : A transaction is a multi-step operation on a database that either succeeds together or fails together.

Summary : A transaction can be a multi-step operation as a whole, which either succeeds together or fails together.

2. Commit and Rollback commands for a transaction:

Set Default transaction submission method:(default = True)

• To set the transaction submission method to " manual commit ":

Set autocommit = false;

• To set a transaction to submit as Auto commit :

Set autocommit = true;

To commit a transaction manually:

Commit

To roll back a transaction:

Rollback

Note: As long as the commit has not been manually committed, once the rollback, the previous modification operation will be zeroed.

Note: The commit command and the rollback command can only be used if the Commit method is "manual commit".

3, save the Restore point command:

When the data is not commit, adding and deleting the operation is temporarily stored in memory, when we modify the operation to a certain step, you can set a restore point to this step, to facilitate later rollback to this restore point (similar to the snapshot in PS).

To save a restore point:

SavePoint Name_point;

Roll back to the specified restore point:

Rollback to Name_point;

Database Learning notes 3--Basic SQL statements