Note:/*
SELECT - extract data from the database
UPDATE - UPDATE the data in the database
DELETE - DELETE data from the database
INSERT INTO - new data INTO the database
The CREATE DATABASE - CREATE a new DATABASE
The ALTER DATABASE - change the DATABASE
The CREATE TABLE - creating a new TABLE
ALTER TABLE - change (change) database TABLE
DROP TABLE - delete TABLE
The CREATE INDEX - CREATE indexes (search)
DROP INDEX - removed INDEX
*/
/* a. Create a database
The CREATE TABLE SQL statement
The CREATE TABLE statement is used to CREATE tables in the database,
Table consists of rows and columns, each table must have a table name,
The CREATE TABLE SQL syntax
The CREATE TABLE table_name
(
Column_name1 data_type (size),
Column_name2 data_type (size),
Column_name3 data_type (size),
.
);
Column_name specifies the name of the column in the table,
Data_type parameters specified column data types (such as varchar, integer, decimal, date, etc.),
The maximum length column in the table size parameter regulation,
Tip: if you want to know, MS Access, MySQL and SQL Server data types available, please visit our complete data types reference manual,
*/
-
The create database of lw
The use of lw
- 2. Create a table
The create table lwweb
(id varchar (100) not null,
Namecode varchar (100),
The name varchar (100),
VIP varchar (100),
Price varchar (100),
PRIMARY KEY (ID) - constraint PRIMARY keys
)
- change the database
3./*
The ALTER TABLE statement
The ALTER TABLE statement is used to in an existing TABLE to add, remove, or modify column,
The ALTER TABLE SQL syntax
If you want to add a column in the table, please use the following syntax:
ALTER TABLE table_name
The ADD column_name datatype
If you need to delete the column in the table, please use the following syntax (please note that some database systems do not allow this to delete in the database table column) :
ALTER TABLE table_name
DROP the COLUMN column_name
To change the data type column in the table, please use the following syntax:
SQL Server/MS Access:
ALTER TABLE table_name
The ALTER COLUMN column_name datatype
My SQL/Oracle:
ALTER TABLE table_name
MODIFY the COLUMN column_name datatype
After Oracle 10 g version:
ALTER TABLE table_name
The MODIFY column_name datatype.
*/
The alter table lwweb add vipname varchar (100)
The alter table lwweb alter the column id int
/* 4. Insert the data
1. The INSERT INTO statement can have two written forms,
The first form doesn't need to insert the data specified column name, only need to provide is inserted value:
INSERT INTO table_name
VALUES (value1, value2, value3,... );
The second form you need to specify the column name and inserted value:
INSERT INTO table_name (column1, column2, column3,... )
VALUES (value1, value2, value3,... );
2. Insert into the select and select into the from the difference between
Insert into scorebak select * from socre where neza='neza' - inserting a row, request table scorebak must exist
Select * into scorebak from score where neza='neza' -- and inserting a row, scorebak requirements table does not exist
*/
Insert into lwweb values (1101, 'black rice, 100, 10,' wang ')
Insert into lwweb values (2102, 'white', 100, 10, 'black')
Insert into lwweb values (3103, 'yellow rice, 100, 10,' white ')
Insert into lwweb values (4104, 'm', 100, 10, 'komori)
Insert into lwweb values (5103, 'beans', 100, 10,' white ')
Insert into lwweb values (6104, 'm', 100, 10, 'big du)
Select distinct VIP from lwweb - distinct to heavy
Select * from lwweb where the VIP is null; - a null value judgment: is null
Select * from lwweb where VIP between 1 and 100. - between the and (in value) between
/*
1. Like fuzzy query
Select * from emp where ename like 'M %';
Query EMP Ename column in the table has the value of M, M to query the contents of the fuzzy information,
More than % said word values, _ _ said one character at a time;
M % : to match, regular expressions, said mean for fuzzy query information for M at the beginning of,
% M % : query contains all contents of M,
% M_ : query to all content of M in the second place,
2. Where
A WHERE clause without comparison operators:
The WHERE clause does not necessarily take comparison operators, when without operator performs an implicit conversion, when 0 into false, 1 into true, for example:
The SELECT studentNO FROM student WHERE 0
Will return an empty set, because each row WHERE returns false,
The SELECT studentNO FROM student WHERE 1
Returning student studentNO column values in the table all the lines, because each row WHERE returns true
3. The ORDER BY
The ORDER BY multiple columns, sorted BY the first column name first, in accordance with the second column name sort; If above tutorial last an example:
1), the country first value this column sorting, with front row for the CN, belong to USA row behind;
2), and then in the multi-line data belong to the CN, and then based on the size of alexa value,
3), the ORDER BY arrangement, not set forth the ASC DESC, default is ASC,
ALLE
The ORDER BY multiple columns, eg:
At this time of the order by A and B are in ascending order by default
At this time of the order by A desc, B A descending order, ascending order B
At this time of the order by A, B desc A ascending, descending order B
The desc or asc applies only to it followed by the first column, the other is not affected, is still the default ascending,
*/
Select * from lwweb order by id desc - reverse order
Select * from lwweb order by id asc - positive sequence sorting
The update lwweb set VIP=200, price=20 where id=2
The update lwweb set VIP=300, price=30 where id=3
The update lwweb set VIP=400, price=40 where id=4
/*
SELECT the TOP clause is used to specify the number of records to return,
SELECT the TOP clause for large tables, with thousands of records to be very useful,
Note: not all database system to support the SELECT TOP statements, MySQL support LIMIT statement to article SELECT a specified number of data, Oracle can use ROWNUM, to SELECT
*/
SELECT the TOP 50 PERCENT * FROM lwweb; - percent said percentage (%)
- LIKE SQL syntax
- SELECT column_name (s) FROM table_name WHERE column_name LIKE pattern;
/*
The wildcard description
1. % instead of zero or more characters
(2) _ instead of a single character
3. [charlist] character column in any single character
4. [^ charlist] or [! Charlist] is not among characters in any single character
MySQL using REGEXP or NOT REGEXP operator (or RLIKE and NOT RLIKE) to operate the regular expression,
For example; The following SQL statement to select the name with A to H letter website:
Instance
SELECT * FROM Websites WHERE name REGEXP '^ [A - H]'.
*/
/*
The IN operator
IN operator allows you to multiple values IN the WHERE clause regulation,
SQL syntax IN
The SELECT column_name (s)
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
