SQL Simple use-Advanced article

With the previous article, "Simple use of SQL-Basic article," A continuous chapter, "SQL Simple use-Basic article" hereinafter referred to as the basic article. In the basic article, the main simple to take a look at the most important SQL commands to add and remove the use of the command, insert INTO, delete detele/drop/truncate, change update, check selecte. Because additions and deletions are the core of the SQL command is also the most basic part, so this article is still around the use of additions and deletions to the introduction and use of advanced.

Start with the wildcard character in the WHERE clause mentioned in the basic article.

1.like used to search for a specified pattern in a column in a WHERE clause
Example:
select * from websites where name like ‘%oo%‘;
Note: (% semicolon denotes arbitrary data, _ represents any data, hands-on both sides can be familiar with)
' g% ' search for data starting with G
'%G ' searches for data ending in G
'%g% ' search for data containing g
' G ' search for two-bit data starting with G
' G ' search for two-bit data ending in G
' G ' search for three-bit data containing g

1.1 Wildcard characters also have one (%, _ and [charlist])
Example: [charlist] Using
select * from websites where name REGEXP ‘^[A-H]‘;

2.between to select a value in the range of data between two values
Example:
select * from websites where alexa between 1 and 20;
Example: adding not using
select * from websites where alexa not between 1 and 20;
Example: Combine in with
select * from websites where ( alexa BETWEEN 1 and 20) and country in (‘USA‘,‘CN‘);
Example: Text
select * from websites where name between ‘A‘ and ‘H‘; 不包含H

3.top is used to specify the data returned from the record, practical
Example: SQL Server (SELECT TOP number|percent column_name (s) from table_name;)
select top 50 percent * from websites;
Example: Oracle (SELECT column_name (s) from table_name WHERE ROWNUM <= number;)
select * from websites where ROWNUM <5;
Example: MYSQL (SELECT column_name (s) from table_name LIMIT number;)
select * from websites limit 3;

The 4.IN operator allows multiple values to be specified in the WHERE clause
Example: View multiple data for Name column in table websites
select * from websites where name in(‘baidu‘,‘Google‘)；

5. Aliases can specify aliases for table names or column names.
Syntax: Column name syntax
SELECT column_name as alias_name from table_name;
Example:
select name AS n,country AS c from websites;
Syntax: Table name syntax
SELECT column_name (s) from table_name as alias_name;
Example:
select w.name,w.url,a.count,a.date from websites AS w ,access_log AS a where w.id=a.site_id and w.name=‘菜鸟教程‘;
Note:
1. More than one table is involved in the query
2. Functions are used in queries
3. A long column name or poor readability requires a combination of two columns or multiple columns.

The 6.join clause is used to combine rows from two tables or multiple tables based on common fields between the tables
There are several join types:
INNER JOIN: Returns a row if there is at least one match in the table
Left JOIN: Returns all rows from the table, even if there is no match in the right table
Right JOIN: Returns all rows from the correct table even if there is no match in the left table
Full JOIN: Returns a row if there is a match in one of the tables (MySQL is not supported )

First, the result of the connection can be logically considered a new table consisting of the columns specified by the SELECT statement.
The left and right side of the left join is the reference to which of the two tables, which are outer joins.
An outer join is like adding a full row of null values for a non-datum table to match a row in the base table that cannot be matched. Suppose two tables with no null values are left connected, the left table is the base table, all the rows from the left table appear in the results, and the right table may appear to be a null field because it cannot match the base table.
Source: Principles of database System tutorial, Wang Shan, Chen Hong, P86

Example: INNER JOIN

SELECT    websites.id,    websites.NAME,    access_log.count,    access_log.dateFROM    websitesINNER JOIN access_log ON websites.id = access_log.site_id;

7.union result set for merging two or more SELECT statements
Grammar:
SELECT column_name (s) from table1
UNION
SELECT column_name (s) from table2;
Example: Union de-weight

Example: Union All shows all included repeating parts

select country from websitesunion allselect country from apps;

Example: union all with a where

select country,name from websites where country=‘CN‘union allselect country,app_name from apps where country=‘CN‘ order by country;

8. into copy data from one table and insert data into another new table
   Note: MySQL database does not support SELECT ... into statement, but supports INSERT into ... SELECT.
   Syntax: Copy all columns into a new table
   SELECT * INTO newtable [IN externaldb] FROM table1;
   Syntax: Copy only the columns you want into a new table
   SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;

8.1 INSERT INTO Select
Example: Copying data from "apps" into "Websites":
INSERT INTO websites (name,country) select app_name,country from apps;

9. Create for creating a database or data table
   Syntax: Creating a Database
   create database db_name;
   Syntax: Creating a data table

   create table table_name(column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),... ...);    date_type 数据类型，size参数规定表中列的最大长度

Example: Creating a database named Runoob
create database runoob;
Example: Create a Student_informaton table with five columns: Student_id,student_name,student_class,student_tele,student_add

create table student_infomation (student_id int(10),student_name char(4),student_class char(10),student_tele int(11),student_add varchar(255));

Front is the column name followed by the data type for the column name

10. Constraints are used to specify the data rules in the table
Constraints can be specified by the CREATE TABLE statement when creating tables, or by the ALTER TABLE statement after the table is created
Syntax: Crate table + Constraint

create table table_name(column_name1 type_data(size) constraint,column_name2 type_data(size) constraint,column_name3 type_data(size) constraint,... ...);

In SQL, we have the following constraints:
Not null indicates that a column cannot store a NULL value, forcing the field to always contain a value, or you cannot insert a new record or update a record.
unique guarantees that each row of a column must have a unique value.
PRIMARY key-not NULL and a UNIQUE combination. Ensuring that a column (or a combination of more than two columns) is uniquely identified helps make it easier and faster to find a particular record in a table.
FOREIGN KEY ensures that the data in one table matches the referential integrity of values in another table.
Check guarantees that the values in the column meet the specified criteria.
The default rule does not assign a value to a column when it is assigned.

10.1 NOT NULL constraint constraint forced not to accept any null value
Example: Student_tele cannot be empty

create table student_information (    student_id INT (10) ,    student_name CHAR (4),    student_class CHAR (10),    student_tele INT (11) NOT NULL,    student_add VARCHAR (255));

The 10.2 Unique constraint uniquely identifies each record in a database table.
Both the unique and PRIMARY KEY constraints provide a unique guarantee for a column or column collection.
The PRIMARY KEY constraint has a UNIQUE constraint that is automatically defined.
Note that each table can have multiple UNIQUE constraints, but there can be only one PRIMARY KEY constraint per table.
Example: MYSQL

create table student_information (    student_id INT (10),    student_name CHAR (4),    student_class CHAR (10),    student_tele INT (11),    student_add VARCHAR (255),    unique (student_id)    );

Example: SQL server/oracle

create table student_information (    student_id int (10) NOT NULL UNIQUE,    student_name CHAR (4),    student_class CHAR (10),    student_tele INT (11),    student_add VARCHAR (255),    );

Example: SQL mysql/server/oracle defines a unique constraint over a column.

create table student_information (    student_id int (10) NOT NULL UNIQUE,    student_name CHAR (4),    student_class CHAR (10),    student_tele INT (11),    CONSTRAINT  stu_inf UNIQUE (student_id,student_name)    );  这里的 stu_inf 为约束名称constraint_name，自定义。

UNIQUE constraint on ALTER TABLE
Example:

alter table student_informationadd unique (student_id);

Example: Add multiple unique,

alter table student_informationadd constraint stu_inf unique (student_id,student_add);

Revoke a UNIQUE constraint
Example: MySQL

alter table student_informationdrop index stu_inf;

Example: SQL

alter table student_informationdrop constraint stu_inf;

10.3 Primary Key The primary key must contain a unique value, the primary key cannot be null, and each table should have a primary key and be unique.
Example: Refer to unique and replace the unique one with primary key. There are several unique examples added above, and if you change to primary it means that the primary key consists of several columns added.

10.4 FOREIGN KEY constraint
A. Behavior that can be used to prevent the connection between a broken table
B. Prevent illegal data from inserting a foreign key column, because it must be one of the values in the table that points to

Example: MYSQL

create table websites(  id int(11) NOT NULL,  name char(20) NOT NULL,  url varchar(255) NOT NULL,  alexa int(11) NOT NULL,  country char(10) NOT NULL,  primary key (id),  foreign KEY (id) references apps(id));

Example: SQL server/oracle

CREATE TABLE websites ( Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, Id int FOREIGN KEY REFERENCES apps(Id) );

Example: Mysql/sql server/oracle

CREATE TABLE websites ( Id int NOT NULL, OrderNo int NOT NULL, Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (Id) REFERENCES apps(Id) );

ALTER TABLE uses FOREIGN KEY constraint
Example:

ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

Example: to name a FOREIGN key constraint and define a FOREIGN key constraint for multiple columns

ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

Revoke FOREIGN KEY constraint
Example: MySQL

alter table Orders drop index fk_PerOrders;

Example: SQL

alter table Orders drop constraint fk_PerOrders;

10.5 CHECK Constraint
To limit the range of values in a column
Example: MYSQL

create table websites(  id int(11) NOT NULL,  name char(20) NOT NULL,  url varchar(255) NOT NULL,  alexa int(11) NOT NULL,  country char(10) NOT NULL,  check (id>0));

ALTER TABLE uses a CHECK constraint
alter table websites add check (id>0);
Revoke a CHECK constraint (refer to ALTER TABLE in a UNIQUE constraint)
alter table websites drop check constraint_name;

10.6 DEFAULT Constraint
1. Used to insert a default value into a column
2. If no other value is specified, add the default value to all records
Example: MYSQL

create table student_information (    student_id INT (10) NOT NULL,    student_name CHAR (4),    student_class CHAR (10) DEFAULT ‘‘ comment ‘班级‘,    student_tele INT (11),    student_add VARCHAR (255));  comment 是为 字段或列的属性添加注释用的

ALTER TABLE uses the default
Example: MYSQL

alter table websitesalter country set default ‘CN‘;

Example: SQL Server
alter table websites add constraint ad_c default ‘CN‘ for country;
Example: Oracle
alter table websites modify country default ‘CN‘;
Undo Default Constraint
Example: MYSQL

alter table websitesalter country drop default;

Example: SQL server/oracle

alter tables websitesalter column country drop default;

11. Create index for creating indexes in tables
    Creating an index in a table can query data more efficiently, and users cannot see the index, they can only be used to speed up search/query.
    Note: Updating a table that contains an index takes longer than no index table, because the index itself needs to be updated. Therefore, it is ideal to simply try to create an index on all columns (and tables).
    Syntax: Create a simple index that allows the use of duplicate values
    create index index_name ON table_name (column_name);
    Syntax: Create a unique index in a table that does not allow duplicate values (create Unique Table): A unique index means that two rows cannot have the same index value.
    create UNIQUE index index_name ON table_name (column_name);

Example: Create an index named Web_index in the Name column in the websites table.
create index web_index ON websites (name);

12.drop can delete tables, indexes, and databases
The DROP INDEX statement is used to delete indexes in the table.
DROP INDEX syntax for SQL Server:
DROP INDEX table_name.index_name
The DROP INDEX syntax for db2/oracle:
DROP INDEX index_name
DROP INDEX syntax for MySQL:
ALTER TABLE table_name DROP INDEX index_name
DROP TABLE 语句用于删除表。
DROP DATABASE 语句用于删除数据库。

You just need to delete the data in the table, but not the table itself.
TRUNCATE TABLE table_name

13.ALTER table is used to add, delete, or modify columns in an existing table.
Syntax for adding columns:
ALTER TABLE table_name ADD column_name datatype;
To delete a column syntax from a table:
ALTER TABLE table_name DROP COLUMN column_name datatype;
To change the data type syntax in a table:
ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Example: Add a column named Column_date to the website table, and then modify the column's data type to remove the added column

alter table websites add column_date date；  添加alter table websites modify column column_date year；修改alter table websites drop column column_date;   删除

Refer to the Novice tutorial please add a link description organized notes

SQL Simple use-Advanced article