Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ SQL Beginner Guide     - PostgreSQL log classification and management (Database)

- Linux system performance analysis and top command ps.pstree Comments (Linux)

- How Linux system password security guarantee (Linux)

- Try the command ip, ifconfig is obsolete under Linux (Linux)

- Xshell upload files to achieve Windows host Linux (Linux)

- Spring declarative transaction management (Programming)

- Install Git on CentOS (Linux)

- C + + secondary pointer memory model (pointer array) (Programming)

- ISO image to use offline upgrade Ubuntu (Linux)

- X security settings in Ubuntu (Linux)

- Download Google Android source code under Ubuntu 4.4 (Linux)

- C # and JavaScript arrays to re-summary (Programming)

- HBase Application Development Review and Summary of Series (Database)

- Java developers question (Programming)

- Smooth upgrade to OpenSSH 6.1 Procedure (Linux)

- MySQL Slave synchronization problem solving (Database)

- Linux / Centos anti CC attack script (Linux)

- Linux, Chrome browser font settings beautification (Linux)

- Use Pylint regulate your Python code (Programming)

- Mac OS X system setup Google Go language development environment configuration tool Sublime Text 2 (Linux)

 
         
  SQL Beginner Guide
     
  Add Date : 2017-01-08      
         
       
         
  Introduction

SQL has been applied to every corner around us, whether you believe it or not. Manipulate any kind of data, each application data needs to be stored somewhere. Whether it is big data, or just simply the number of rows of data packets; whether government, or start-up companies; whether it is across multiple servers, large databases, or run their own small database of phone, SQL everywhere.

But what SQL is it? Representative SQL Structured Query Language, in general, pronounced "ess-que-el". SQL is a database language, and specifically for database communication established. SQL is a simple language, and English language similar to English sentences because the command and has a similar structure. Those sentences organizations declarative statements, so that SQL also called declarative languages.

The Beginner's Guide SQL

In the visual aspects of writing SQL queries, have a lot of tools available, why learn a new language? When you use some SQL tools, it is important to understand SQL language, visual tools to understand what is being done, and why not do that. Sometimes, you need to manually write SQL statements, not only because this is the fastest way, but it is more powerful, often the only way to accomplish the intended target.

 

Introduction Database

We have just mentioned, SQL database language. So, what database? The database is a storage mechanism is designed to be used to access information stored in its operation. Database information is stored in an object called a table (table) of Lane. Table name is its unique identity, constituted by the column and row. Column contains the column names, data types and other properties of the column column. Row contains the column or data record. There is the relationship between the majority of database tables (relationship) or connect (link), one, or many relationship. This is why such a database is called a relational model database.

For a description of the database structure, the easiest way is to put it, and Excel spreadsheets to compare, they have many similarities. A database is a separate document. The sheet is a spreadsheet table (table), each sheet has a name. Columns and rows, and databases are the same. SQL language is used to create new tables, change an existing table, used to obtain data, update data, or delete data.

For example, we have a large collection of famous movie lines, stored in any separate text file. Even if we carefully organized, with Excel spreadsheets storage, the problems we face is still present. In this way storage lines, we are unable to quickly get all the lines from a movie, or can not get all the lines of a role. If we put a text file or spreadsheet into a database, and create a table with a relationship, all the problems solved. What is the real meaning of the relationship type is? The relational model is a method to describe the relationship data, and these data entities between. In our example, the relationship is to contact each of the lines and between the tables, the name of the movie is stored in the table, or all roles are also stored in the table.

Here is an example of the process of simplification, only one table do sample table called "Movie_quotes." It has four, lines of text represents a column, a column represents the actors say the lines role, showing a movie, as well as the year. We included eight movie lines, our sample table looks like this:

Movie_quotes
Q_TEXT Q_CHARACTER Q_MOVIE Q_YEAR
I'll be back The Terminator The Terminator 1984
I find your lack of faith disturbing. Darth Vader Star Wars 1977
It's a trap! Admiral Ackbar Star Wars 1983
Never tell me the odds. Han Solo Star Wars 1980
Do. Or do not. There is no try. Yoda Star Wars 1980
Stupid is as stupid does. Forrest Gump Forrest Gump 1994
My mama always said: Life was like a box of chocolates.
You never know what you're gonna get.

Forrest Gump Forrest Gump 1994
Run, Forrest! Run! Jenny Curran Forrest Gump 1994
When discussing a database, it is worth mentioning that there is a new database, people in the middle need to store data, resulting in a sport, it is NoSQL. They are document-based system, although they are becoming very popular, even today there are still a large number of relational database in use. Even if there is some NoSQL database query language, a large part of them (because they are almost always only after the invention of SQL) and SQL still has some similarity.

 

Four basic SQL operations (CRUD)

There are many SQL commands, but there are four usual SQL operations, tables and their data can do something:

- Create the fill data to the table.
Read - query data from a table.
Update - Modify existing data in the table.
Delete - to remove data from the table.
These initials constitute the basic SQL operations acronym "CRUD", they must be considered for each database, the four basic functions or characteristics of the base set.

By introducing basic characteristics, we will introduce the basic and most important SQL command: `CREATE`,` INSERT`, `SELECT`,` UPDATE`, `DELETE`, and` DROP`.

Create data

First, we need to create a table in the database. Create a new table, you use the `CREATE TABLE`. Simple syntax `CREATE TABLE` statement is as follows:

CREATE TABLE table_name
(Column_1 data_type,
column_2 data_type,
column_3 data_type);
First, `CREATE TABLE` keyword followed by the table name. This is an excellent example of how SQL simplicity, as well as similarities and English. Key words followed by a left parenthesis, here defines additional parameters: the column name and data type of columns, then keep right parenthesis. We must mention that all the SQL statements should be `;` the end.

Need to comply with the rules is not much. Table and column names must begin with a letter, you can keep up with the back of letters, numbers, or underscores. They can not be more than 30 characters in length. Use SQL reserved words as table or column names (such as `select`,` create`, `insert`, etc.) is prohibited.

In the example, the most simple column name might be `TEXT`,` CHARACTER`, `MOVIE`, and` YEAR`. However, the problem is that these column names are reserved words. In order to avoid any possible conflict, we will create a `Q_` as a prefix to the column name.

Because of different data types with different databases, but here is the most common type used:

`Char (size)` - fixed-length string, the parameters indicated in parentheses.
`Varchar (size)` - variable-length strings, the parameters indicated in parentheses.
`Number (size)` - numeric values in parentheses indicate the total length of the parameter.
`Date` - date value.
`Number (size, d)` - numeric value, the total length of `size`, decimals represented by` d`.
Data type specifies what type of data can be stored in the designated column in. If `Q_CHARACTER` column name used to store movies, then the specified column should have a` varchar` (variable length character) data types. The type of stored movies of the year column is `number`, our example, the corresponding column is` Q_YEAR`.

For the desired table structure, create a table of final SQL command as follows:

CREATE TABLE Movie_quotes
( 'Q_TEXT' varchar (200),
'Q_CHARACTER' varchar (20),
'Q_MOVIE' varchar (20),
'Q_YEAR' number (4));
The results of this SQL command will create an empty table, the columns are as follows:

`Q_TEXT` string of 200 characters in length is acceptable.
`Q_CHARACTER` string of 20 characters in length can be accepted.
`Q_MOVIE` string of 20 characters in length can be accepted.
`Q_YEAR` can accept a four-digit year.

Next, our movie lines populate this table data. There are many available GUI tools to manage database tables and data. However, often faster to write a SQL script, the script is basically a set of SQL commands will be executed sequentially. When you need to use a lot of data to populate tables in this way is especially convenient.

Inserted or added to the table row of data in SQL commands are `INSERT`. Format is as follows:

INSERT INTO table_name
(Column_1, column_2, ... column_n)
VALUES (value_1, value_2, ... value_n);
To insert a row into the table, `INSERT` keyword is followed by` INTO` keyword and table names. Then the column name, in parentheses, separated by commas, which is optional, however, specify the column to be inserted to ensure that the correct data into the appropriate column, this is a good practice. The last part, with `VALUES` keyword defines the data that you want to insert a list of data to the end of parentheses. Note that the string must be enclosed in single quotes, numbers should not be so.

SQL scripts to populate example `Movie_quotes` table, as follows:

INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ( 'I'll be back', 'The Terminator', 'The Terminator', 1984);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ( 'I find your lack of faith disturbing.', 'Darth Vader', 'Star Wars', 1977);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ( 'It's a trap!', 'Admiral Ackbar', 'Star Wars', 1983);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ( '. Never tell me the odds', 'Han Solo', 'Star Wars', 1980);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ( 'Do. Or do not There is no try..', 'Yoda', 'Star Wars', 1980);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ( '. Stupid is as stupid does', 'Forrest Gump', 'Forrest Gump', 1994);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ( '.. My mama always said: Life was like a box of chocolates You never know what you're gonna get', 'Forrest Gump', 'Forrest Gump', 1994);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ( '!! Run, Forrest Run', 'Jenny Curran', 'Forrest Gump', 1994);
Read data

With good data stored in the database, and now we can query the data to see what is stored in our table, we can filter and sort data in different ways.

`SELECT` statement is used to query, or select from the database we want to return data. We start from a very simple query, but `SELECT` there are many different options and extensions, which is our ultimate need to provide a lot of flexibility. `SELECT` basic syntax of the statement is as follows:

SELECT column_1, column_1, ... column_n
FROM table_name;
He pointed out that the column name, determines which column will be returned to result in, and in what order. If we want to select all the columns in the table or we do not know the exact name of the column, we can use a wildcard `` * will select all columns from the database:

SELECT * FROM table_name;
For this example, the query displays all the data, as follows:

SELECT * FROM Movie_quotes;

Only display movie lines, the year of inquiry, as follows:

SELECT Q_TEXT, Q_YEAR FROM Movie_quotes;
Sometimes we do not want to return all the data from the table. When the table has a lot of data, or we search for matching certain criteria specific data, as is the case. In this regard, we can use `WHERE` statement. `WHERE` statement will filter the records, which restrict access to records from the database to meet the specific defined criteria:

SELECT column_1, column_1, ... column_n
FROM table_name
WHERE column_name operator value;
Note that, `WHERE` statement is optional, but if we decide to use it, the following operators are available:

`` = - Equal.
`>` - More than.
`< ` - Less than.
`> =` - Greater than or equal to.
`< =` - Less than or equal to.
`< >` - Not equal.
`BETWEEN` - between two values.
`LIKE` - search for a pattern.
`IN` - a column for a variety of possible values.
Mathematical operators do not need to explain. `BETWEEN` operator to search for two declared values, intermediate values, including the case of equal on both sides. `LIKE` pattern matching operators are very powerful operators, support options and our regulations similar lines. `` Percent% is used as a wildcard to match any possible character that can occur before or after a specific string.

For example, to get from the film "Stars Wars" in the lines, we can write:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = 'StarWars';

Please note, `WHERE` statements are case-sensitive, the following SQL statement will not return results:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = 'STAR WARS';
In addition to `WHERE` clause, but also a combination of logical operators` AND` and `OR`. If we use multiple `AND` logical operators to the same column, then we should consider using` IN` clause instead.

As an example, we return from the movie "Star Wars" and "The Terminator" movie lines of all:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = 'StarWars' AND Q_MOVIE = 'TheTerminator';

With the above example, the wording is better to use `IN` statement instead:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE IN ( 'StarWars', 'TheTerminator');
So far, we have been discussing how to filter data from a database. Returned rows into the order in which they (submit to) the database sort. To control the display order of the data, we can contain `ORDER BY` clause to filter the output data. `ORDER BY` clause contains a specified sort order, and one or more column names:

SELECT column_1, column_1, ... column_n
FROM table_name
WHERE column_name operator value
ORDER BY column_name;
In order to extend our earlier example of "Star Wars" movie lines now sorted by year:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = 'StarWars'
ORDER BY Q_YEAR;

Sort of a column, the default is in accordance with the order from lowest to highest values in ascending order. In order to sort the column to descending, we can add `DESC` keywords behind the column name:

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = 'StarWars'
ORDER BY Q_YEAR DESC;

`ORDER BY` statement is limited to a single column. You can include a comma-separated list of columns to sort. Returned rows specified according to the first column, and then the next column specified in the order sorted. Remember, do not have to sort out is included in the list in the selected column. We can write the query like this:

SELECT Q_TEXT, Q_CHARACTER, Q_MOVIE FROM Movie_quotes
WHERE Q_MOVIE = 'StarWars'
ORDER BY Q_YEAR DESC;
update data

After we began to insert the data, and not be limited to read-only data. We can in any row and any column of data under any modification. `UPDATE` statement to update or modify records.

`UPDATE` the following syntax:

UPDATE table_name
SET column_name = new_value
WHERE column_name operator value;
When we use the `UPDATE`, carefully construct a` WHERE` clause is very important. `WHERE` clause specifies which record or records which should be updated. If we are in the implementation of `UPDATE` statement, but did not use` WHERE` clause, we will update all the data specified column.

Let's take a look at `Movie_quotes` list movie lines. We let all the lines end with punctuation, "The Terminator" exception. How to use `UPDATE` statement, this is an excellent example:

UPDATE Movie_quotes
SET Q_TEXT = 'I'll be back!'
WHERE Q_MOVIE = 'TheTerminator';
Before we explain, if we are not inadvertently omitted `WHERE` clause, or we deliberately put all the lines rows updated to" I'll be back!. " By simply select the movie "The terminator" the line, we can update a specified row of data.

 

delete data

When the database is widely used to remove old data from the database, sooner or later it will become necessary. We are able to remove only some rows in a table, or delete the entire table.

`DELETE` statement to delete rows in a table. The command syntax is as follows:

DELETE FROM table_name
WHERE column_name operator value;
He reiterates, and `UPDATE` same sentence,` WHERE` clause specifies which record or records which should be removed. If you do not specify `WHERE` clause, all the rows and columns will be deleted:

DELETE FROM Movie_quotes;
Suppose we do not like the movie "Forrest Gump", and want to remove the lines from the movie. In order to remove all the lines from the film, we can write the following SQL command:

DELETE FROM Movie_quotes
WHERE Q_MOVIE = 'ForrestGump';
In the end, we have more than enough movies. We are no longer interested in a movie lines, we want to move on interest music. We started collecting the lyrics. According to our current knowledge of SQL learned, modify the database is very simple.

First, we need to clear the database, the data is no longer of interest. In order to delete all the rows that contains the table, we can use `DROP TABLE` statement. Remember `DROP TABLE` statement differs from the use of` DELETE` statements and delete all records of the table are also different. Delete all records of the table, the table structure we will leave all the table itself and its definition; includes a column data type definitions and other relevant information about the database table. `DROP TABLE` removed the table, remove the definition of the table, and all the rows.

Syntax `DROP TABLE` statement is as follows:

DROP TABLE table_name;
In order to remove `Movie_quotes` from the database, we can write:

DROP TABLE Movie_quotes;
Now our database is empty, ready to accept new data. Let's start with all the CRUD process, create a new table named `Song_Lyrics` of our new collection of songs, lyrics to create a database.

 

in conclusion

In this paper, we visit covering four basic database CRUD functions: how to create new data, read data, update data we want to modify, and finally how to delete unwanted data. This includes basic, but most important of SQL commands, such as: `CREATE TABLE`,` INSERT INTO`, `SELECT`,` UPDATE`, `DELETE` and` DROP`.

These basic SQL commands support large amounts of data management, but each presentation command to have a lot of options and extra features, some of which are not described herein, to pay attention to this point. In short, when the SQL database developer novice start work and when to use a new language SQL, basic knowledge in this article should be able to open for them a good start.
     
         
       
         
  More:      
 
- Spring-depth understanding of the various annotations (Programming)
- The source code compiler installation Nginx 1.8.0 under Ubuntu 14.10 (Server)
- Java study notes: String (Programming)
- When Linux Detailed time zone and common function of time (Linux)
- Sudo and Root Account in Ubuntu related (Linux)
- SSH port forwarding application (Server)
- It is time to upgrade your gulp 4.0 (Programming)
- Ubuntu 15.10 under Python + Apache + CGI fully configured (Server)
- MySQL service failed to start thinking of settlement under CentOS7 (Database)
- PostgreSQL-- run Supervisord on Docker in Ubuntu (Database)
- How to Install Xombrero 1.6.4 (minimalist Web browser) on Ubuntu and Archlinux (Linux)
- Management Linux Container with Docker in Ubuntu (Linux)
- Java semaphores (Programming)
- Linux system security knowledge (Linux)
- How to enhance the security of Linux systems (Linux)
- Use Makeself Create installation file (Linux)
- The top command under Linux (Linux)
- Spring Boot + Nginx + Tomcat + SSL configuration notes (Server)
- C ++ pointer of the (error-prone model) (Programming)
- On the Web application attack techniques Common (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.