Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the commonly used sql statements in the database", the content is simple and clear, and I hope it can help you solve your doubts. Here, let the editor lead you to study and learn about "what are the commonly used sql statements in the database?"

SQL is one of the most widely used database languages at present. Here, I summarize the sorting, filtering, and grouping of data in SQL on the database, as well as tables, views, joins, subqueries, cursors, stored procedures, and triggers.

Database correlation

Check all databases show databases

Create database create database database name

Check the database show create database database name; / / show what kind of sql statement was used when the library was created

Create database specified character set create database database name character set utf8/gbk

Delete database drop database database name

Use the database use database name

Table correlation

Create a table create table table name (id int,name varchar (10)); / / the table name is case sensitive

View all tables show tables

Look at a single table attribute show create table table name; / / what creation statement to use, you can add\ G after it to make the description clearer

View table fields desc table name

Create table specify engine and character set create table table name (id int,name varchar (10)) engine=myisam/innodb charset=utf8/gbk

Delete table drop table [if exists] table name; delete table (delete if you can choose whether to add or not)

DROP TABLE IF EXISTS `abc` CREATE TABLE `abc` (`id` mediumint (8) unsigned NOT NULL AUTO_INCREMENT comment' Commodity name', `name` char (80) NOT NULL DEFAULT''comment' Commodity name', `title`char (20) NOT NULL DEFAULT''comment' Commodity name', `type`tinyint (1) NOT NULL DEFAULT'1' comment' Commodity name', `condition` char (100) NOT NULL DEFAULT''comment' Commodity name', `show`bit DEFAULT 1 comment' visible', `price` decimal 'not null comment' Price' `status` enum ('0th,' 1th,'2') NOT NULL DEFAULT'0' comment 'status', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ENGINE=INNODB DEFAULT CHARSET=utf8

Set up the database:

CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci

Constraint

Not null is not empty

Default default constraint statement, which is used to constrain the default value of the value in the corresponding column. Null value cannot be inserted unless the default value is null.

Unique unique constraint statement, which is used to constrain that the values in the corresponding column cannot be duplicated. There can be null values, but only one null value can appear.

Primary primary key = unique + non-empty

Auto_increment automatic growth, which is used by the system to automatically generate the primary key value of the field.

Foreign key (slave table id) reference master table name (id); establish a relationship between tables

Modify the table

Modify table name rename table old table name to new table name

Modify table name alter table old table name rename new table name

Modify field data type alter table table name modify field name data type

Modify table properties alter table table name engine=myisam/innodb charset=utf8/gbk

Add table field alter table table name add new field name new data type [constraint] [first/after existing field name]

Delete table field alter table table name drop field name

Modify table field name and type alter table table name change old field name new field name type

Modify the type and location of the table alter table table name modify field name type first/after existing field name

Delete table drop table table name

Change the storage engine of the table alter table table name engine = new storage engine

Delete table foreign key constraint alter table table name drop foreign key foreign key name; / / delete all foreign keys before you can delete the table where the corresponding primary key is located

Data correlation

Insert data:

The name of the insert into table is values.

Insert into table name (field name 1, field name 2 …) Values (2)

The table name of insert into is values (5), (5) (5) (5)

Insert into table name (field name 1, field name 2) values (2), (2) values (2), (2)), (2)

Query

Select * from table name

Select name from table name

Select * from table name where id=10

Modify

Update table name set field name to modify = 100where based on field name = 10

Delete

Delete from table name where field name = 10

The following is a supplement

1. Retrieve data

SELECTprod_ nameFROM Products;# Retrieval single column SELECTprod_ id, prod_name, prod_priceFROMProducts;# Retrieval Multi-column SELECT * FROMProducts;# Retrieval all columns SELECT DISTINCTvend_id FROMProducts;# retrieves different values SELECTprod_name FROMProducts LIMIT 5 OFFSET # returns no more than 5 rows of data SELECTprod_name FROMProducts LIMIT 5 OFFSET 5 th # returns 5 rows of data from row 5. LIMIT specifies the number of rows returned, and the OFFSET with LIMIT specifies where to start. / * SELECTprod_name, vend_idFROMProducts; * / SELECTprod_nameFROMProducts;# multiline comments

two。 Sort and retrieve data

SELECTprod_nameFROMProductsORDER BYprod_name;# sorted data SELECTprod_ id, prod_price, prod_nameFROMProductsORDER BYprod_ price, prod_name;# sorted by multiple columns SELECTprod_ id, prod_price, prod_nameFROMProductsORDER BY 2,3 prod_name # sorted by column position, the third row first sorted by prod_price, and then sorted by prod_name SELECTprod_ id, prod_price, prod_nameFROMProductsORDER BYprod_ priceDESC, prod_name # prod_price columns are sorted in descending order, while prod_ name columns (within each price) are still sorted in standard ascending order

3. Filter data

SELECT prod_name, prod_priceFROMProductsWHERE prod_price

< 10;#检查单个值 SELECT prod_name, prod_priceFROMProductsWHERE vend_id 'DLL01';#不匹配检查 SELECT prod_name, prod_priceFROMProductsWHERE prod_priceBETWEEN 5 AND 10;#范围值检查 SELECT cust_nameFROMCUSTOMERSWHERE cust_emailIS NULL;#空值检查 4.高级数据过滤 SELECTprod_id, prod_price, prod_nameFROMProductsWHERE vend_id = 'DLL01'ANDprod_price = 10；#求值顺序 AND的优先级高于OR SELECTprod_name, prod_priceFROMProductsWHERE vend_idIN ('DLL01','BRS01')ORDER BY prod_name;#IN操作符 SELECT prod_nameFROMProductsWHERE NOTvend_id = 'DLL01'ORDER BY prod_name;#NOT 操作符 SELECT prod_nameFROMProductsWHEREvend_id 'DLL01'ORDER BY prod_name;#NOT 操作符 5.通配符进行过滤 SELECT prod_id, prod_nameFROMProductsWHERE prod_nameLIKE 'Fish%';#%表示任何字符出现任意次数，找出所有以词Fish起头的产品 SELECT prod_id, prod_nameFROMProductsWHERE prod_nameLIKE '%bean bag%';#'%bean bag%'表示匹配任何位置上包含文本bean bag的值，不论它在之前或之后出现什么字符 SELECT prod_nameFROMProductsWHERE prod_nameLIKE 'F%y';#找出以F起头，以y结尾的所有产品 根据邮件地址的一部分来查找电子邮件，例如WHERE email LIKE 'b%@forta.com' WHERE prod_nameLIKE '%'; #不会匹配产品名称为NULL的行，其它均可 %代表搜索模式中给定位置的0个、1个或多个字符 下划线的用途与%一样，但它只匹配单个字符，而不是多个字符 SELECT prod_id, prod_nameFROMProductsWHERE prod_nameLIKE '__inchteddy bear';#搜索模式要求匹配两个通配符而不是一个 方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符 SELECT cust_contactFROMCustomersWHERE cust_contactLIKE '[JM]%'ORDER BY cust_contact; #[JM]匹配方括号中任意一个字符，它也只能匹配单个字符，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。 SELECT cust_contactFROMCustomersWHERE cust_contactLIKE '[^JM]%'ORDER BY cust_contact;#以J和M之外的任意字符起头的任意联系人名 6.创建计算字段 SELECT Concat(vend_name, ' (', vend_country, ')')FROMVendorsORDER BY vend_name; 输出Bear Emporium(USA)Bears R Us (USA)Doll House Inc.(USA)Fun and Games(England) SELECT Concat(vend_name, ' (', vend_country, ')') ASvend_titleFROMVendorsORDER BY vend_name; #给拼接而成新字段起了一个名称 SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_priceFROMOrderItemsWHERE order_num = 20008;#汇总物品的价格 7.使用函数处理数据 SELECT vend_name, UPPER(vend_name)AS vend_name_upcaseFROMVendorsORDER BY vend_name;#文本处理函数 SELECT cust_name, cust_contactFROMCustomersWHERE SOUNDEX(cust_contact) =SOUNDEX('MichaelGreen');# SOUNDEX()函数搜索，匹配所有发音类似于Michael Green 的联系名 SELECT order_numFROMOrdersWHERE YEAR(order_date) = 2012;#从日期中提取年份 8.数据汇总 SELECT AVG(prod_price)ASavg_priceFROMProducts;WHERE vend_id = 'DLL01'; SELECT COUNT(*)ASnum_custFROMCustomers;#COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值 SELECT COUNT(cust_email)ASnum_custFROMCustomers;#只对具有电子邮件地址的客户计数 SELECT MAX(prod_price)ASmax_priceFROMProducts;#返回Products表中最贵物品的价格 SELECT MIN(prod_price)ASmin_priceFROMProducts;#返回Products表中最便宜物品的价格 SELECT SUM(quantity)ASitems_orderedFROMOrderItemsWHERE order_num = 20005;#SUM(quantity)返回订单中所有物品数量之和，WHERE 子句保证只统计某个物品订单中的物品 SELECT SUM(item_price*quantity)AS total_priceFROMOrderItemsWHERE order_num = 20005;#SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句保证只统计某个物品订单中的物品 SELECT AVG(DISTINCTprod_price)AS avg_priceFROMProductsWHERE vend_id = 'DLL01';#使用DISTINCT参数，平均值只考虑各个不同的价格 SELECT COUNT(*) AS num_items, MIN(prod_price)AS price_min, MAX(prod_price)AS price_max, AVG(prod_price)AS price_avgFROMProducts;#组合聚集函数 9.分组数据 SELECT vend_id,COUNT(*) AS num_prodsFROMProductsGROUP BY vend_id;#创建分组 SELECT vend_id,COUNT(*) AS num_prodsFROMProductsWHERE prod_price >

The = 4GROUP BY vend_idHAVING COUNT (*) > = 2th destroy where clause filters all rows with a prod_price of at least 4, then groups the data by vend_id, and the Having clause filters packets with a count of 2 or more. SELECT order_num,COUNT (*) AS itemsFROMOrderItemsGROUP BY order_numHAVING COUNT (*) > = 3ORDER BY items, order_num;# output sorted by the number of items ordered

10. Use subquery

SELECT cust_idFROMOrdersWHERE order_numIN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'); SELECT cust_name, cust_contactFROMCustomersWHERE cust_idIN (' 10000000004mm, '10000000005')

11. Join table

SELECT vend_name, prod_name, prod_priceFROMVendors, ProductsWHERE Vendors vend_id = Products.vend_id;# create joins SELECT vend_name, prod_name, prod_priceFROMVendorsINNER JOIN ProductsONVendors.vend_id = Products.vend_id;# join SELECT prod_name, vend_name, prod_price, quantityFROMOrderItems, Products, VendorsWHERE Products.vend_id = Vendors.vend_idANDOrderItems.prod_id = Products.prod_idANDorder_num = 20007 th # join multiple tables

twelve。 Create an advanced connection

SELECT c1.cust_id, c1.cust_name, c1.cust_contactFROMCustomersAS C1, CustomersAS c2WHERE c1.cust_name = c2.cust_nameANDc2.cust_contact = 'Jim Jones' # since join, the two tables needed in this query are actually the same tables SELECT C. *, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_priceFROMCustomersAS C, Orders AS O, OrderItems AS OIWHERE C.cust_id = O.cust_idANDOI.order_num = O.order_numANDprod_id = 'RGAN01' # Natural joins exclude multiple occurrences so that each column returns SELECT Customers.cust_id only once, Orders.order_numFROMCustomersLEFT OUTER JOIN OrdersONCustomers.cust_id = Orders.cust_id;# selects all rows from the Customers table on the left side of the FROM clause, and Orders.order_numFROMCustomersRIGHT OUTER JOIN OrdersONOrders.cust_id = Customers.cust_id;# selects all rows from the table on the right. SELECT Customers.cust_id, Orders.order_numFROMOrdersFULL OUTER JOIN CustomersONOrders.cust_id = Customers.cust_id;# retrieves all rows in both tables and associates those rows that can be associated

13. Combined query

SELECT cust_name, cust_contact, cust_emailFROMCustomersWHERE cust_state IN ('IL',' IN', 'MI') UNIONSELECT cust_name, cust_contact, cust_emailFROMCustomersWHERE cust_name =' Fun4ALL'ORDER BY cust_name, cust_contact;#SQL allows you to execute multiple queries and return the results as a query result set

14. Insert data

INSERT INTO Customers (cust_id, Cust_name, Cust_address, Cust_city, Cust_state, Cust_zip, Cust_country, Cust_contact, Cust_email) VALUES ('100000000006,' Toy Land', '123Any Street',' New York', 'NY' '111111,' USA', NULL, NULL) # insert complete rows INSERT INTO Customers (cust_id, Cust_contact, Cust_email, Cust_name, Cust_address, Cust_city, Cust_state, Cust_zip, Cust_country) SELECT cust_id, Cust_contact, Cust_email, Cust_name, Cust_address, Cust_city, Cust_state Cust_zip, Cust_countryFROMCustNew # merge the customer column from another table into the Customers table. SELECT * INTOCustCopyFROMCustomers;# copy from one table to another

15. Update and delete data

UPDATE CustomersSETcust_contact = 'Sam Roberts',Cust_email =' sam@toyland.com'WHERE cust_id = '1000000000006delete # Update multiple columns UPDATE CustomersSETcust_email = NULLWHERE cust_id =' 1000000005delete # Delete a column DELETE FROM CustomersWHERE cust_id = '1000000006delete data

16. Create and manipulate tables

CREATE TABLE OrderItems (Order_num INTEGER NOT NULL,Order_item INTEGER NOT NULL,Prod_id CHAR (10) NOT NULL,Quantity INTEGER NOT NULL DEFAULT 1 Magi Itemized price DECIMAL (8,2) NOT NULL); ALTER TABLE VendorsADDvend_phone CHAR (20); # add a column named vend_phone to the table, whose data type is CHAR ALTER TABLE VendorsDROP COLUMN vend_phone;#. A column in this table DROP TABLE CustCopy;# deletes the table

17. Advanced SQL features

Primary key: the value of a column (or columns) in a table uniquely identifies each row in the table. A primary key is a special constraint that ensures that the values of a column or set of columns uniquely identify each row in the table. This facilitates direct or interactive processing of rows in the table. Without a primary key, it is very difficult to safely UPDATE or DELETE specific lines without affecting other rows.

The primary key values of any two rows of ① are different

② has a primary key value per row (that is, null values are not allowed in the column)

③ columns that contain primary key values are never modified or updated.

④ primary key values cannot be reused

CREATE TABLE Vendors (Vend_id CHAR (10) NOT NULL PRIMARYKEY,Vend_name CHAR (50) NOT NULL,Vend_address CHAR (50) NULL,Vend_city CHAR (5) NULL,Vend_state CHAR (10) NULL,Vend_zip CHAR (10) NULL,Vend_country CHAR (50) NULL) ALTER TABLE VendorsADD CONSTRAINT PRIMARYKEY (vend_id)

# add the keyword PRIMARYKEY to the table vend_id column definition to make it the primary key

The above is all the contents of the article "what are the sql statements commonly used in the database?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.