SQL technology Daquan (1)
[B] Introduction SQL SQL (Structure Query Language, Structure Query Language) is a powerful database language. SQL is usually communications in the database. ANSI (National Standard Society) claims that SQL is a standard language for relational database management systems. SQL statements are often used to complete some database operation tasks, such as updating data in the database, or retrieving data from the database. Common relational database management systems using SQL include: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, they also have their systems for their systems for their own existing proprietary features. However, standard SQL commands, such as "SELECT", "INSERT", "Update", "Delete", "Create", and "DROP" are often used to complete the operation of most databases. However, unlike other languages, such as C, Pascal, etc., SQL has no looping structure (such as if-dam, do-whiled, and function definition, etc.). And SQL only has a fixed setting of a data type. In other words, you cannot create your own data type when using other programming languages. SQL function is powerful, but summarizes, it can be divided into the following groups: DML (Data Manipulation Language, Data Operation Language): Used to retrieve or modify data; DDL (Data Definition Language, Data Definition Language): Structure for Defining Data For example, create, modify, or delete database objects; DCL (Data Control Language, Data Control Language): The permissions used to define database users. DML groups can be subdivided into the following statements: SELECT: INSERT: INSERT: Used to add data to the database; Update: Use to modify existing data delete from the database: to delete data from the database. DDL statements can be used to create users and rebuild database objects. Below is the DDL command: The CREATE TABLALTER TABLEDROP TableCREATE INDEXDROP INDEXDCL command is used to create a relational user access and an authorized object. Below is a few DCL commands: Alter PasswordGrantrevokecreate Synonym In order for you have an intuitive understanding of SQL, the following is given an example of a simple SQL statement: We use the SQL statement to retrieve the department ID from Employees: SELECT Employees .NameFrom Employeeswhere Employees.deptid = "CS" May I now don't understand these statements at the beginning, maybe you will fog, don't matter, after learning this tutorial, you will find how ordinary this statement is. In order not to let you confuse, I will explain it below: First for the from clause, the from Employees in the statement means retrieving data from the EMPLOYEES table.
And statement where employees.deptid = "cs" means retrieving Employees's DEPTID listed as "CS", so the result of SQL statement retrieval will be all data of the column of deptid as CS, such as: Empid Name dePT123 Purple CS124 ZSC CS Finally, let's explain a SELECT clause, which specifies all the data from the Name column, such as NamePurplezsc, start our next tutorial ----- The basic knowledge of the table. SQL step gradual (2) ------ The basic knowledge of the basic knowledge form of the table typically contains multiple tables. The database is actually a collection of tables, and the database's data or information is stored in the table. The table is a logical structure for storing and operation of data, each representing an object of the user meaning. For example, in a company database, there will be employee table, department table, inventory table, sales table, wage form, etc. The transcript we often see is a table, which is composed of rows and columns, and we can identify data through the name. Columns include the names, data types, and other properties of the column; the line contains the records or data of the column. The name, language, mathematics, and English are columns, and the line contains the data of this table, that is, each person's score: Name language mathematics English king children 78,87 Zhang Liu Feng 85 92 95 purple Yunfei 65 89 86 Huang Tianlong 98 67 75SQL step gradual (3) ------ Data Retrieval Data Retrieval in SQL SELECT statement is usually used to retrieve databases, or retrieve data to meet your settings, the following is simple SELECT statement format: Select "Column1" [, "Column2", etc] from "Tablename" [where "condition"]; [] = optional, the name of the column follows the Select keyword, it determines which column will be as a result return. You can specify multiple columns arbitrarily, or you can use "*" to select all columns. The name of the table is followed by the from keyword, it pointed out which form will be queried as the last result. The WHERE clause (optional) indicates which data or row will be returned or displayed, which is based on the conditions described later in the keyword WHERE. The following conditions can be selected in the WHERE clause: = equal to> greater than
Select * from Empinfowhere First = 'Eric'; the above SQL statement only selects the first name of the first name 'Eric'. SQL step gradual (4) ------- Create a table Create a table This CREATE TABLE statement is used to create a new form. The following is a simple creation of a format of a table statement: Create Table "Tablename" ("Column1" "Data Type", "Column2" "Data Type", "Column3" "Data Type"; if you want to use optional constraints, The format of the creation table is: create table "table" [constraint], "column2" "data type" [constraint], "column3" "data type" [constraint]); [] = Optional Note here: You can create multiple columns of forms, this condition is optional. For better understanding, the following example: Create Table Employee (First Varchar (15), Last Varchar (20), Age Number (3), Address Varchar (30), City VARCHAR (20), State Varchar (20) To create a new form, you can follow the name of the table after the keyword table table, then a circular left bracket "(", then the first column name, and then the data type of this column, then it is arbitrary Optional constraints, eventually is round, right brackets "). Be sure to use circular left brackets before starting the table content and use round parentheses after the last column definition of the table is quite important. You have to ensure a comma separation between each column definition. Finally, in the end of the SQL statement, add a semicolon ";". The table and column name must begin with letters, the second character can be a letter, a number, or an underline, but to ensure that the total length of the name should not exceed 30 characters. Do not use keywords or column names that do not use SQL (such as "SELECT", "CREATE", "Insert", etc.) to avoid errors. SQL step gradual (5) ------- Insert data to the table inserted data to the table INSERT statement to insert or add a line of data into the table, its format is: Insert Into "Tablename" (first_column, ... Last_Column Values (first_value, ... last_value); [] = Optional simple example: Insert Into Employee (First, Last, Age, Address, City Values ('Luke', 'Duke', 45, '2130 Boars Nest ',' Hazard co '); here you should pay attention: Every character must be enclosed in single quotes. In order to insert the data in the table, you must follow the table name after the keyword INSERT INTO, then the left brackets, then the column name separately is a series of column names, and then a right round bracket, then in keyword values Then followed by a series of values enclosed with parentheses. These values are the data you want to fill in the table, which must match the specified column name.
String translation is enclosed in single quotes, while numbers are not available. In the above example, 'Luke' must match the column first, and 45 must match the column AGE. If you want to insert the following data into the Employee table; ZHANG weiguo, 28, Beijing 601 mailbox, Beijing then you want to use the following SQL statement: Insert Into Employee (First, Last, Age, Address, City Values ('zhang', 'Weiguo', 28, 'Beijing 601 Mailbox', 'Beijing'); SQL step gradual (6) ------ Remove Table Delete Table DROP TABLE command to delete all lines in a table or table. Its grammatical format is: Drop Table "TableName" will be taken here: Drop Table Employee; To delete the entire table (including all rows), you can use the Drop table command to add Tablename. The drop table command is not the same as the slap in the table: Remove all records in the table to leave the table (just it is empty) and constraint information; and the Drop table is all the information of the delete table, including all rows, forms And constraint information, etc. SQL step gradual (7) ------- Update record update record UPDATE statement is used to update or change the record of matching the specified condition, which is implemented by constructing a WHERE statement. The format of its statement is as follows: Update "tablename" set "colorue" = "newValue" [, "nextColumn" = "newValue2" ...] where "columnname" Operator "value" [and | or "column" operator "value"] [] = Optional Next example: Update phone_bookset area_code = 623where prefix = 979; The above statement is in the phone_book table, and the area_code is set to 623 in the line of Prefix = 979. update phone_bookset last_name = 'Smith', prefix = 555, suffix = 9292where last_name = 'Jones'; and the statements in the above phone_book, the last_name = 'Jones' line in the set last_name 'Smith', prefix It is 555, and Suffix is 9292. Update Employeeset Age = age 1where first_name = 'mary' and last_name = 'Williams'; this statement is in the Employee table, plus AGE 1 in first_name = 'mary' and last_name = 'Williams' rows. As each lesson, you should make the following exercises after ending this tutorial: 1 Because Jonie Weber has married Bob Williams, it needs to update its Last name to Weber-Williams. 2 Dirk Smith's birthday is today, so his age should add 1.
3 All secretaries are called "Administrative Assistant". Therefore, all header titles should be modified accordingly. Just do these exercises, don't want to know. SQL step gradual (8) ------- Delete Record Delete Record DELETE statement is used to delete records or lines from the table, whose statement format is: delete from "tablename" where "columnname" Operator "value" [and | Or "column" Operator "Value"]; [] = Optional Below or take an example: delete from Employee; this statement does not have a WHERE statement, so it will delete all records, so if you don't use WHERE, you have to do 10 million Be careful. If you only remove one of the rows or a few lines, you can refer to the following statement: delete from employewhere lastname = 'may'; this statement is to remove LastName 'May' from the EMPLYEE table. Delete from Employewhere FirstName = 'Mike' or FirstName = 'Eric'; this statement is to remove firstname to 'Mike' or 'ERIC' from the Emplyee table. In order to delete a complete record or line from the table, add the name of the table directly after "Delete from", and use where defimizes what is eliminated. If you don't use the WHERE clause, all records or rows in the table will be deleted. SQL step gradual (9) ------- SELECT statement select statement is available in the SELECT statement in the tutorial above. It will be explained in detail in this tutorial. The SELECT statement is the core of SQL, and the most used in your SQL statement is the SELECT statement. Since a large number of options can be used for SELECT statements, the entire tutorial is like the SELECT statement. When we construct SQL query statements (using SELECT statements), we know all possible options and best or most efficient methods are useful. This tutorial will provide you with these skills. The SELECT statement is used to query the database and retrieve the selection data that matches you specified. SELECT statement has five main clauses you can choose, and from WOM is the only clause. Each clause has a large number of selection items, parameters, and more. These clauses will be listed below, and each of them will be described in more detail in the later tutorial. The following is the format of the SELECT statement: select [all | distinct] column1 [, column2] from table1 [, table2] [where "conditions"] [group by "column-list"] [Having "conditions] [Order By" column- List "[ASC | DESC]] The following example: SELECT NAME, AGE, SALARYFROM EMPLOYEWHERE AGE> 50; The above statement will select all Name, Age and Salary columns of the AGE greater than 50 from the EMPLOYEE table. Note : Be sure to add a semicolon at the end of the SQL statement. This semicolon prompts the SQL statement that has ended and ready to be interpreted.
The following table gives a variety of comparison operation symbols: = equal to> greater than
