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

SQL DML and DDL

SQL can be divided into two parts: data manipulation language (DML) and data definition language (DDL).

SQL (structured query language) is the syntax used to execute queries. But the SQL language also contains syntax for updating, inserting, and deleting records.

Query and update instructions make up the DML part of SQL:

SELECT-get data from database tables

UPDATE-updates data in database tables

DELETE-removes data from the database table

INSERT INTO-inserts data into a database table

The data definition language (DDL) section of SQL gives us the ability to create or delete tables. We can also define indexes (keys), specify links between tables, and impose constraints between tables.

The most important DDL statement in SQL:

CREATE DATABASE-create a new database

ALTER DATABASE-modify the database

CREATE TABLE-create a new table

ALTER TABLE-change (change) database tables

DROP TABLE-Delete a table

CREATE INDEX-create index (search key)

DROP INDEX-Delete the index

Create a data test

Use testcreate table Persons (Id int primary key,Name varchar (14) not null,Sex varchar (13) not null,Address varchar (20) not null,City varchar (20) not null) insert into Persons values (1re 'Zhang San', 'male', 'Guangdong Maoming', 'Guangdong'); insert into Persons values ('Li Si', 'male', 'Zhejiang Hangzhou', 'Zhejiang'); insert into Persons values ('Wang Wu', 'female', 'Beijing Fifth Ring Road', 'Beijing') Insert into Persons values (4Jing 'Xiao', 'female', 'Shanghai Pudong', 'Shanghai'); select * from Persons

Select statement

Syntax:

SELECT column name FROM table name

 to view a column of data

Select Name from Persons

 can query multiple columns separated by ","

Select Name,Sex from Personsselect * from sysdatabases; # View so libraries (* on behalf of all) use tongji # switch tongji libraries select * from sys.tables # to view all tables

 views stored procedures of schema,user

SELECT * FROM SYS.DATABASE_PRINCIPALSSELECT * FROM SYS.SCHEMASSELECT * FROM SYS.SERVER_PRINCIPALS

 views all the contents of the DEPT table

Select * from DEPT

 views the contents of the DEPT table with deptno 101,

Select * from DEPT where deptno='101'

 select distinct statement

The keyword DISTINCT is used to return a unique different value.

Syntax:

SELECT DISTINCT column name FROM table name

Select distinct Name,Sex from Persons

 SQL SELECT INTO statement

SQL SELECT INTO---- can be used to create backup files for tables

SQL SELECT INTO syntax

You can insert all the columns into the new table:

SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename

Or just insert the desired columns into the new table:

SELECT column_name (s) INTO new_table_name [IN externaldatabase] FROM old_tablename

 makes backup copy of "Persons" table

Select * into Persons_backup from Persons

Clause can be used to copy a table to another database:

SELECT * INTO Persons IN 'Backup.mdb'FROM Persons

If we want to copy some fields, we can list them after the SELECT statement:

SELECT LastName,FirstNameINTO Persons_backupFROM Persons  create statement  creates test library use mastercreate database test

 creates test login users with a password of 123 and a default library of tongji

Create login test with password='123',default_database=tongji

 creates a database user test1 (create user) for login users. The default library is dbo.

Create user test1 for login test with default_schema=dbo

 creates the DEPT table (to execute it in the library first)

Create table DEPT (DEPTNO int primary key,DNAME VARCHAR (14), LOC VARCHAR (13))

The data type (data_type) specifies what data type the column can hold. The following table contains the most commonly used data types in SQL:

Data type description

integer (size)

int (size)

smallint (size)

tinyint (size) holds only integers. Specify the maximum number of digits in parentheses.

decimal (size,d)

numeric (size,d) holds numbers with decimals.

"size" specifies the maximum number of digits. "d" specifies the maximum number of digits to the right of the decimal point.

Char (size) holds fixed-length strings (which can hold letters, numbers, and special characters).

Specify the length of the string in parentheses.

Varchar (size) holds strings of variable length (which can hold letters, numbers, and special characters).

Specify the maximum length of the string in parentheses.

Date (yyyymmdd) holds the date.

 create index

You can create indexes in tables to query data more quickly and efficiently.

The user cannot see the index, they can only be used to speed up the search / query.

Note: updating a table with an index takes more time than updating a table without an index, because the index itself needs to be updated. Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.

SQL CREATE INDEX syntax

Create a simple index on the table. Duplicate values are allowed:

CREATE INDEX index_name ON table_name (column_name)

Note: "column_name" specifies the columns that need to be indexed.

SQL CREATE UNIQUE INDEX syntax

Create a unique index on the table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name ON table_name (column_name)

If you want to index more than one column, you can list the names of those columns in parentheses, separated by commas:

Create index sed on Persons (City,Name);  insert statement

Grammar

INSERT INTO table name VALUES (value 1, value 2.)

We can also specify the column in which we want to insert the data:

INSERT INTO table_name (column 1, column 2.) VALUES (value 1, value 2pm.)

 insert data

Insert into DEPT VALUES (101 new YORK'); INSERT INTO DEPT VALUES (102 recordings, researchery, and Dalas'); INSERT INTO DEPT VALUES (103, Salesling, and CHICAGO'); INSERT INTO DEPT VALUES (104 parting, opening, recording, and boosting).

-- specifies that data is inserted into the column

Insert into Persons (Name,Sex) values ('big', 'female')  update statement

Syntax:

UPDATE table name SET column name = new value WHERE column name = a value

 adds Address for Name is a big person

Update Persons set Address=' Nanjing 'where Name=' Da'

 for Name is a big person to modify Address,City

Update Persons set Address=' Zhongshan', City=' Nanjing 'where Name=' Da'  delete statement

The DELETE statement is used to delete rows from a table.

Grammar

DELETE FROM table name WHERE column name = value

Delete the content with ID number 5

Delete from Persons where Id=5

You can delete all rows without deleting the table. This means that the structure, properties, and indexes of the table are complete:

DELETE FROM table_name or: DELETE * FROM table_name  drop statement

Indexes, tables, and databases can be easily deleted by using the drop statement.

SQL DROP TABLE statement

The DROP TABLE statement is used to delete the table (the structure, attributes, and indexes of the table are also deleted):

DROP TABLE name

SQL DROP DATABASE statement

The DROP DATABASE statement is used to delete the database:

DROP DATABASE database name

Syntax for Microsoft SQLJet (and Microsoft Access):

DROP INDEX index_name ON table_name  alter statement

The ALTER TABLE statement is used to add, modify, or delete columns from an existing table.

SQL ALTER TABLE syntax

To add columns to the table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype

To delete a column from a table, use the following syntax:

ALTER TABLE table_name

DROP COLUMN column_name

Note: some database systems do not allow this way of deleting columns from database tables (DROP COLUMN column_name).

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name

ALTER COLUMN column_name datatype

Add a column to the Order table

Alter table Orders add Id nchar (10)

Add a column to the Persons table to add content

Alter table Persons add Birthday datetime;update Persons set Birthday='1991-03-26' where Id=1

Delete Birthday column

Alter table Persons drop column Birthday  where statement

* * Syntax:

SELECT column name FROM table name WHERE column operator value

The following operators can be used in the WHERE clause:

Operator description

= equal to

Not equal to

Greater than

< 小于 = 大于等于 (select avg(OrderPrice) from Orders)SQL COUNT() 函数 COUNT() 函数返回匹配指定条件的行数。 SQL COUNT(column_name) 语法 COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）： SELECT COUNT(column_name) FROM table_name SQL COUNT() 语法 COUNT() 函数返回表中的记录数： SELECT COUNT(*) FROM table_name SQL COUNT(DISTINCT column_name) 语法 COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目： SELECT COUNT(DISTINCT column_name) FROM table_name 注释：COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是无法用于 Microsoft Access。 SQL COUNT(column_name) 实例 我们拥有下列 "Orders" 表： O_Id OrderDate OrderPrice Customer 1 2008/12/29 1000 Bush 2 2008/11/23 1600 Carter 3 2008/10/05 700 Bush 4 2008/09/28 300 Bush 5 2008/08/06 2000 Adams 6 2008/07/21 100 Carter 现在，我们希望计算客户 "Carter" 的订单数。 我们使用如下 SQL 语句： SELECT COUNT(Customer) AS CustomerNilsen FROM OrdersWHERE Customer='Carter' 以上 SQL 语句的结果是 2，因为客户 Carter 共有 2 个订单： CustomerNilsen 2 查看表中总行数 SELECT COUNT(*) AS NUMBER FROM ORDERS 去掉重复行 SELECT COUNT(DISTINCT Company) AS NUMBER FROM ORDERSFIRST() 函数 FIRST() 函数返回指定的字段中第一个记录的值。 提示：可使用 ORDER BY 语句对记录进行排序。 SQL FIRST() 语法 SELECT FIRST(column_name) FROM table_name sql2005不支持 MAX() 函数 MAX 函数返回一列中的最大值。NULL 值不包括在计算中。 SQL MAX() 语法 SELECT MAX(column_name) FROM table_name 注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。 查找OrderPrice列中最大值 select max(OrderPrice) as maxprice from Orders MIN() 函数 MIN 函数返回一列中的最小值。NULL 值不包括在计算中。 SQL MIN() 语法 SELECT MIN(column_name) FROM table_name 注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。 select min(OrderPrice) as maxprice from OrdersSUM() 函数 SUM 函数返回数值列的总数（总额）。 SQL SUM() 语法 SELECT SUM(column_name) FROM table_name 查看orderprice列总和 select sum(OrderPrice) as maxprice from OrdersGROUP BY 语句 GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。 SQL GROUP BY 语法 SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name 我们希望查找每个客户的总金额（总订单）。 select Company,sum(OrderPrice)as sumprice from Orders group by Company HAVING 子句 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。 SQL HAVING 语法 SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value 查找客户 "IBM" 或 "Apple" 拥有超过 1500 的订单总金额。 select Company,sum(OrderPrice)as sumprice from Orders where Company='IBM' OR Company='Apple' group by Company having sum(OrderPrice) >

1500  UPPER () function

The UPPER function converts the value of the field to uppercase.

SQL UPPER () syntax

SELECT UPPER (column_name) FROM table_name

Change it all to uppercase

Select upper (Company) as upname from Orders

 LOWER () function

Grammar is the same as above

Change it all to lowercase

Select lower (Company) as upname from Orders  LEN () function

The LEN function returns the length of the value in the text field.

SQL LEN () syntax

SELECT LEN (column_name) FROM table_name

Take the length of the value "Company"

Select lEN (Company) as LENname from Orders  ROUND () function

The ROUND function is used to round numeric fields to the specified number of decimal places.

SQL ROUND () syntax

SELECT ROUND (column_name,decimals) FROM table_name

Parameter description

Column_name is required. The field to round.

Decimals is required. Specify the number of decimal places to return

Round the name and price to the nearest whole number

Select ProductName,round (UnitPrice,0) as roundprice from products  NOW () function

The NOW function returns the current date and time.

Tip: if you are using a Sql Server database, use the getdate () function to get the current date and time.

SQL NOW () syntax

SELECT NOW () FROM table_name

Select ProductName,UnitPrice, getdate () as date from products

Modify date format

Select ProductName,UnitPrice, CONVERT (VARCHAR (10), getdate (), 23) as date from products

Reference:

Http://www.w3school.com.cn/sql/sql_view.asp

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.