Shulou(Shulou.com)06/01 Report--

The continuous chapter with the previous article "simple use of SQL-basic articles", "simple use of SQL-basic articles" is hereinafter referred to as "basic articles". In the "basic article", it is mainly simple to take you to understand the use of the most important add, delete, change and search commands in the SQL command, add INSERT INTO, delete DETELE/DROP/TRUNCATE, change UPDATE, and check SELECTE. Because the addition, deletion, modification and search is the core and the most basic part of the SQL command, this article focuses on the advanced introduction and use of the use of addition, deletion, modification and search.

Let's start with the wildcards in the where clause mentioned in the fundamentals.

1.like is used to search for a specified pattern in a column in the where clause

Example:

Select * from websites where name like'% oo%'

Note: (% semicolon represents any data, _ represents any data, you can be familiar with both sides)

'G% 'searches for data that begins with G.

'% G' searches for data ending in G

'% g%' searches for data containing g

'G' searches for two-digit data that begins with G.

'G' searches for two-digit data ending in G

'G' search contains three-digit data of G

1.1 there is also a wildcard (%, _, and [charlist])

Example: [charlist] use

Select * from websites where name REGEXP'^ [Amurh]'

2.between is used to select values within the data range between two values

Example:

Select * from websites where alexa between 1 and 20

Example: add not usage

Select * from websites where alexa not between 1 and 20

Example: use in conjunction with IN

Select * from websites where (alexa BETWEEN 1 and 20) and country in ('USA','CN')

Example: text

Select * from websites where name between'A 'and' hacks; does not include H.

3.top is used to specify the data to be returned to the record, which is 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 0)

Undo the check constraint (refer to alter table in the unique constraint)

Alter table websites drop check constraint_name

10.6 DEFAULT constraint

1. Used to insert default values into a column

two。 If no other values are specified, add default values 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' Class', student_tele INT (11), student_add VARCHAR (255); comment is used to add comments to the properties of a field or column

Alter table uses 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;create index is used to create an index in a table

Creating an index in a table can query data more efficiently, users cannot view the index, and they can only be used to speed up the search / query.

Note: it takes longer to update a table with an index than it does without an index table, because the index itself needs to be updated. Therefore, it is ideal to create an index only on all the columns (and tables) that have been tasted.

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, and duplicate values (create unique table) are not allowed: 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 of 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 an index from a table.

DROP INDEX syntax for SQL Server:

DROP INDEX table_name.index_name

DROP INDEX syntax for DB2/Oracle:

DROP INDEX index_name

DROP INDEX syntax for MySQL:

ALTER TABLE table_name DROP INDEX index_name

The DROP TABLE statement is used to delete the table.

The DROP DATABASE statement is used to delete the database.

You only 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 existing tables.

Syntax for adding columns:

ALTER TABLE table_name ADD column_name datatype

Delete column syntax from the table:

ALTER TABLE table_name DROP COLUMN column_name datatype

Change the syntax of the data type in the table:

ALTER TABLE table_name MODIFY COLUMN column_name datatype

Example: add a column named column_date to the website table, then modify the data type of the column and delete the added column

Alter table websites add column_date date; add alter table websites modify column column_date year; modify alter table websites drop column column_date; delete

For reference to the rookie tutorial, please add a link description of the organized notes

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.