Location>code7788 >text

Introduction to Sql and Sql Basic Queries

Popularity：870 ℃/2024-09-26 22:44:30

Introduction to Sql and Sql Basic Queries

SQL

SQL, also known as Structured Query Language (Structure Query Language), is a language that is used forManage and operate relational databasesThe standardized computer language, SQL language is widely used in a variety of relational database systems (RDBMS) such as Mysql, Oracle, Microsoft SQL Server and so on.

SQL operations on databases

  1. Data query.pass (a bill or inspection etc)SELECTstatement to retrieve data from the database
  2. Data Definition (DDL).These includeCREATE,ALTER,DROPPerform a databaseDefining and modifying database structures
  3. Data Manipulation (DML).including throughINSERT,UPDATE,DELETEStatements for inserting, updating, deleting data
  4. Data control.pass (a bill or inspection etc)GRANT,REVOKEStatements manage user rights and scope control

Features of SQL

  • Standardization.SQL is a standardized language, all major database vendors support SQL.
  • Highly deprocessed.Unlike processualization and language, theSQL does not need to specify characteristic algorithms or steps to complete tasks.SQL statements are more focused on describing the desired result, not how to get the desired result
  • Set Operation.SQL supports aggregate operations, i.e. multiple rows of data can be processed at once. ASELECTstatement can return multiple rows, theINSERT,UPDATEIt is also possible to affect multiple rows of data at once
  • Transaction support.SQL supports transactions, allowing a set of related data to be executed as a complete unit. Transactions have: atomicity (Atomicity), consistency (Consistency), isolation (Isolation), persistence (Durability) (about these properties in the subsequent chapter on transactions will be explained in detail), ACID, these four properties to ensure the consistency and reliability of data
  • Integration Capabilities.SQL can be integrated with other programming languages, enabling developers to embed SQL code in the application to achieve efficient operation, which typically belongs to the Java developers can use JDBC to execute SQL queries.

SQL Basic Queries

grammatical structure

  • SELECT Query List FORM Table Name where the lookup list can be the tableFields, constant values, expressions, functions, etc.

as ifSELECT * FORM emplyees; included among these* Indicates that all data in the table is queried

Querying a single field in a table

  • SELECT Field Name FORM Table Name

    eg:query all names of employees

    SELECT e_name FORM emplyees;

Querying multiple fields in a table

Separate each field with a comma

  • SELECT field1,field2,field3 FORM table name.

    eg: Example: query all the employees name, e-mail, phone number

    SELECT e_name,e_email,e_phone_num FORM emplyees;

Alias fields

In the actual database table structure, some field names are too long, or not easy to understand, we can have a field name to indicate that in the subsequent call directly use its alias can be,.in a multi-table query, some field names will be duplicated, then we can alias the corresponding field, should not be confused

  • Using the AS keyword
    • After the field to be aliased, use theASKeywords.
      • SELECT field name AS alias FORM table name.
  • Directly separated by spaces
    • I.e., just separate the field and the alias with a space.
      • SELECT field name alias FORM table name.

de-emphasize

query out of the statement may come out of the first field name duplicate results, then, we can use the de-emphasis function

  • utilizationDISTINCTKeywords.

    • SELECT DISTINCT Fields to be de-duplicated ,Other fields... FORM Table name;

    eg: query department table in the department ID, for each department ID can appear once, so we can use the de-emphasis of the

    SELECT DISTINCT d_id FORM department;

The + operation

In Mysql+The symbol has only one function: as an operator, not as a concatenator.

  • as+ Both sides of the number are numeric and are treated as normal addition.
  • as+Characters appear on both sides of the number, this attempts to convert the characters to numeric types, if the conversion succeeds, then continue to perform the addition operation, if it fails, then converted to the numeric value 0.The transformation rules are.Converts all numeric characters from the beginning until it encounters a character character, e.g. '11'⇒11; '1a'⇒1; 'abc'⇒0. '11a1'⇒11
  • null value with any numeric type for+The operations are all null
SELECT 1 + 1; //2
SELECT '1' + '1';//2
SELECT 'a' + 1;//1
SELECT 'a' + 'b';//0
SELECT '1a' + '1b';//2
SELECT '11a11' + '11b11'//22;
SELECT 1 + null;//null

CONCAT() operation

this being the case+The symbol can only be used as an operator, so how do you concatenate characters? →CONCAT()function (math.)

  • CONCAT() :: Call CONCAT() using SELECT.

    • SELECT CONCAT(str1,str2,…);

    eg:query employee name and e-mail, according to the fixed format (name [xxx@]) requires output, such as: Zhang San [123@]

    SELECT CONCAT(e_name,’[’,e_email,’]’)FORM employees;

IFNULL() function

  • IFNULL():If a NULL value exists in the query, replace it with an alternative value.

    • SELECT IFNULL(field name, instead of value) FROM table name.

    eg: query the employee's name and department ID, if the employee does not have departmental information, display Unknown

    SELECT e_name,IFNULL(d_id,'unknown') FORM employees.

conditional query

Conditional query, allowing the query data, specify certain conditions, so as to filter out records that meet the conditions

WHERE keyword

The WHERE keyword is at the heart of conditional queries; the

  • Grammatical structure:

    SELECT Query List //Execution Order 3
FORM table name //execution order 1
WHERE conditional expression;//execution order 2

    eg:Query all employees whose d_id is 10.

    SELECT d_id FORM employees WHERE d_id=10;

Expression filtering

SQL contains the conditional operators:>, <,>=,<=, =, ! =, <>;<> and ! = have the same effect of not being equal, conditional operators are used in conditional expressions.

  • eg.1:query salary greater than 8000 employees information

    SELECT ＊FROM employees WHERE salary>8000;

  • eg.2 Querying Employee Information for Department Number Not Equal to 3

    SELECT *FORM employees WHERE d_id ! =3;

logical operation

Logical operators are also commonly used in WHERE expressions to help specify multiple conditions in a query or to change conditional logical relationships

  • Logical operators include.&&,||,!, respectivelyand,or,not

  • eg1: query salary in the 8000 to 10000 between the employee information → use AND operator connection

    SELECT * FORM employees WHERE salary>8000 AND salary<100000;

  • eg2: query salary greater than 8000 or department 3 of the employee information → use the OR operator connection

    SELECT * FORM employees WHERE salary >8000 OR d_id=3;

fuzzy query

LIKE keyword

Commonly used in SQL areLIKEkeywords, with wildcards to achieve fuzzy queries

  • Common Wildcards

    • % : represents multiple characters that can be matched, including 0 and 1.

    • _: means only one character can be matched

      SELECT Query List
FROM table name
WHERE column name LIKE 'schema';

  • eg1: query name contains the word [micro] employee information

    SELECT * FORM employees WHERE e_name LIKE '% micro%%';

  • eg2: query name in the second word is [can] word employee information

    SELECT * FORM employees WHERE e_name LIKE '_micros%';

BETEWEEN…AND

Query the data within a range of values, including critical values, i.e. BETEWEEN 8000 AND 100000⇒[8000,10000].

  • eg1:query salary between 8000 and 10000 employees information

    SELECT * FORM employees WHERE salary BETWEEN 8000 AND 10000;

  • eg2:Querying information about employees who started between 2018-01-01 and 2022-01-01

    SELECT * FORM employees WHERE hiredate BETWEEN ‘2018-01-01’ AND ’2022-01-01’;

IN Keywords

The IN operator is used to check whether a field value is in a specified list of values. Using the IN operator simplifies the query statement, especially when dealing with multiple values.

  • eg:Query employee information in department 2 or department 3.

    SELECT *FORM employees WHERE d_id IN(2,3); equivalenceSELECT * FORM employees WHERE d_id=2 OR d_id=3;

IS NULL/IS not NULL

Operators for detecting whether a value is NULL or not

  • eg1:Querying Employee Information Without a Department

    SELECT * FORM employees WHRER d_id is NULL;

  • eg2:Querying Employee Information with Departments

    SELECT * FROM emplyees WHERE d_id is not NULL;

Sorting Queries

The data queried by default in accordance with the order in which the table is saved query, we need to modify the order of the query results display, you can use the sorting query.

ORDER BY keyword

ORDER BY in SQL, ASC stands for Ascending, DESC stands for Descending.

  • grammatical structure

    SELECT Query List
FROM Table
[WHERE Filter List]
ORDER BY Sort List [ASC ascending|DESC descending] -- ASC|DESC defaults to ASC if left unspecified

Implementing single-field sorting

  • ORDER BY sort field [ASC ascending|DESD descending].

  • eg:Query employee information sorted by high and low wages

    - Ascending order by salary
SELECT * FROM emplyees ORDER BY salary ASC -- ASC can be left out
- Descending order by salary
SELECT * FROM emplyees ORDER BY salary DESC -- DESC cannot be omitted.

Sorting with multiple fields

  • eg:query employee information, first by department ID descending order, and then by salary ascending order

    SELECT * FROM emplyees ORDER BY department_id DESC,salary ASC;

<Previous Next>