Study database Notes 2. Database notes

Study database Notes 2. Database notes

I

Table and database and MYSQL database Server (table & database & mysqlserver )?

A table consists of multiple rows and multiple columns of data (a table is created with a table header, and even if the table structure is set up, only data is left in the table)

A database consists of multiple tables. tables --> database

The database server has multiple databases --> mysql server

II

What is an SQL language?

Structured Query Language)

What type of language is used. We usually use the who type language. For example, if JAVA wants to implement some functions, you must tell it what you should do in this step, what should I do if any problem occurs. What type of SQL language is,Tell it what you wantIt will return the value to you.

What are the three types of SQL languages?

The DML database operation language, which accounts for about 80% of normal operations. It queries, modifies, and deletes existing data (where queries account for 80% of existing data) and stands for users.

DDL database definition language, which accounts for 15% of the total number. Table creation, database creation, and view creation are required (the structure must be defined during creation, so it is also called definition ), from the builder's point of view (project manager is the project manager's point of view)

DCL Database Control Language, accounting for 5%. It controls database permissions and monitors whether you have the permission to operate the database. The current user has the right to view which databases and which databases are not the same as the administrator.

3.

Insert operation

A row is the most atomic unit in the database. Any operation is performed on a row. Even a column or multiple columns are operated on which column of the row.

You can insert a specified column.

You can also insert all columns.

However, columns and values must correspond. values that do not want to be added can be left empty and will be filled with NULL.

After a table is created, the table structure is fixed. You can insert all or some columns.

Syntax: Which table is inserted, which columns are to be inserted, and what content is inserted in these columns (where id is not used here. If the inserted id is not specified at the time of insertion, it will automatically increase. If the specified id already exists, an error will be reported)

Insert into user (id, name, age) values (3, 'lilei', 18 );

If you want to add values to all columns, You can omit the column names in the brackets.

Insert into user values (5, 'Lucy ', 16 );

View the table structure statement: desc user;

Update operation

Syntax: The table to be updated, the table to be updated, and the data to be changed.

Example 1 update user set age = 16 whereName = 'Lucy'The updated and modified condition does not have to be id, but can be name or other

Example 2: update user set name = 'lisi', age = '28' where schoolcode = '15 ';

A very serious incorrect writing method. I did not write which one to change:

Update user set age = 18; in this way, the age for writing the entire table is changed to 18, so you need to write it like this at work, so don't say anything. Hurry up.

* You can set the new mode for Oracle, MySQL, and other databases. If you do not write the where condition, submission is not allowed to prevent data loss.

Delete operation

In a relational database, one row is the most atomic unit. to delete a row, only one whole row can be deleted. If only one field is deleted, it is modified and must be updated, set the row you want to delete to Null.

Therefore, you do not need to write the Deleted field in the delete syntax. Because one row is deleted, the delete statement is relatively simple.

Syntax: Delete the table and the row of data.

Delete from user where id = 9;

A serious error occurred. The write condition is missing !!

Delete from user; the data in the entire user table will be deleted, and then run ..

Select Operation (80% of DML database operation languages, which is also the most difficult part, depends on the Data Query Model)

The simplest query operation is select * from user, but it cannot be used in real work *. Several statements are fine. Currently, the project contains millions of data records, and * is used for query, in an instant, the memory burst. It was just a mess. Which data records should be used to check which one would only select * from people, and the salary would be 3 K.

Syntax: What to query (which columns), which table, and which rows of records

Select * from user where id = 9;

When querying data, you can query its multiple rows

Select * from user where id = 3;

Select * from user where id> = 3;

Which columns can you check?

Select name, age from user where id> = 5;

Check a column

Select name from user where id = 5;

Database operations are performed on the column content, and then specify which row of the column is operated!