1. Insert data INSERT INTO
1.1 insert complete line
INSERT INTO customers VALUES(100, 'Tony', 'USA', NULL, NULL);
This example inserts a new customer into the customers table. Each column of data stored in the table is given by VALUES, and each column must be provided with a value. If a column has no value, NULL is used (assuming that the column allows NULL VALUES). The columns must be populated in the order they are defined in the table.
The above method has a disadvantage: it depends too much on the order of columns, so the following form can be used
INSERT INTO customers(cust_id,cust_name,cust_city,cust_qq,cust_email) VALUES(100, 'Tony', 'USA', NULL, NULL);
The DBMS populates the corresponding column after the table name with the corresponding value in VALUES. In this way, the insertion result will not change with the change of table structure.
1.2 insert partial lines
When an explicit column name is used to insert a row (as in the above example), some information can also be inserted by omitting some columns. This means that you can only provide values for some columns and not others
INSERT INTO customers(cust_id,cust_name,cust_city) VALUES(100, 'Tony', 'USA');
This example is not given to cust_qq and cust_email provides a value, that is, part of the line is inserted.
You can omit some rows in the INSERT operation, but the omitted rows must meet one of the following conditions
- The column is defined to allow NULL
- The default value is given in the table definition, which means that if no value is given, the default value is used.
1.3 insert the retrieved value INSERT SELECT
INSERT can be used in conjunction with the SELECT statement to INSERT the selected results into the table
INSERT INTO customers(cust_id,cust_name,cust_city) SELECT cust_id,cust_name,cust_city FROM custnew;
The above example inserts the results retrieved from the custnew table into the customers table.
Table name in INSERT SELECT:
In this example, the column name of the selected column is consistent with the column name of the insert, but it is not necessary to match the column name. In fact, the DBMS does not care about the column name returned by the select because it uses the position of the column, so the first column of the select will be used to populate the first column of the insert...
1.4 create and copy from one table to another
Using the CREATE SELECT statement
CREATE TABLE custcopy AS SELECT * FROM customer;
The above example copies the SELECT search results to a new table.
2. UPDATE data
Do not omit the WHERE clause, because omitting the WHERE clause updates all rows; If there is a WHERE clause, it updates the filtered specified row
UPDATE user SET username = 'wu',password='di' WHERE username = 'wudi';
Filter out the rows with username 'wudi' and update the column values of these rows
If you want to delete the value of a column, you can SET the column value to null in the SET clause (if NULL is allowed)
UPDATE user SET username = NULL -- take username Set column to NULL WHERE username = 'wu';
Note that NULL is different from empty string ':
Empty string is represented by '', which is a value; NULL means no value
3. Delete data
Do not omit the WHERE clause, because if you omit the WHERE clause, you delete all rows; If there is a WHERE clause, the filtered specified row will be deleted
DELETE FROM user WHERE username = 'wu'; -- delete user In the table username by'wu'Line of
Note that DELETE can DELETE rows in the table, even all rows in the table, but cannot DELETE the table itself.
4. Create and operate tables
4.1 CREATE TABLE
When creating a table, you must ensure that the table name does not exist. In order to prevent accidental overwriting of an old table (for example, the name of the create table is consistent with an existing table name), it is required to manually delete the table and then rebuild it, rather than simply overwriting it by the CREATE TABLE statement.
CREATE TABLE Table name ( Column name data type constraint (allow to be) NULL,Default values, etc.), ... )
CREATE TABLE products ( id CHAR(10) NOT NULL, -- Cannot be NULL value name CHAR(25) NOT NULL, -- Cannot be NULL value price DECIMAL(8,2) NULL -- Can be NULL value )
Each column is either NULL (NULL values are allowed) or NOT NULL (NULL values are not allowed). NULL is the default setting. If NULL or NOT NULL is not written, the DBMS will handle it as NULL.
Note that since the primary key is the column whose value uniquely identifies each row in the table, the primary key cannot be NULL
Create table with PRIMARY KEY: PRIMARY KEY
CREATE TABLE products ( id CHAR(10) NOT NULL, -- Cannot be NULL value name CHAR(25) NOT NULL, -- Cannot be NULL value PRIMARY KEY(id) -- set up id Column as primary key,among id Column cannot be NULL value )
Set the DEFAULT value of the column: DEFAULT
CREATE TABLE products ( id CHAR(10) NOT NULL, -- Cannot be NULL value name CHAR(25) NOT NULL DEFAULT 'WUDI' -- Set the default value to'WUDI' )
5. Update columns (instead of updating column data, add or delete columns) ALTER TABLE
**ADD column to table * * ADD
ALTER TABLE user ADD email CHAR(20) NULL DEFAULT '123'; -- Give table user Add a column email，The data type is char(20),allow NULL,The default value is'123'
Delete column DROP COLUMN for table
ALTER TABLE user DROP COLUMN email; -- delete email column
6. Delete and rename the whole table
Delete the entire table DROP TABLE
DROP TABLE user; -- Delete table user
RENAME entire table RENAME
RENAME TABLE user TO user1; -- Will table user Rename to user1
A view is a virtual table that contains only the query results of dynamically retrieved data.
It can be simply understood that a VIEW is a VIEW saved by a select retrieval statement. When subsequent statements use this VIEW, they operate on the retrieval results of the select statement. Thus, SQL reuse is realized and complexity is reduced.
For example, we need to select username from user; If the retrieval result is operated multiple times, you need to call the select statement first each time. Here, you can use view to save the select statement as a view, and then call the view directly.
Note that the view does not save the result of the search statement, but the search statement itself. Therefore, each subsequent reference to the view will immediately call the retrieval statement referenced by the view once.
- The view name must be unique (cannot duplicate other table or view names)
- Views can be nested, but seriously affect performance
- Views cannot be indexed or have associated triggers or defaults
7.1 create viewcreate view
CREATE VIEW v1 AS SELECT username FROM user WHERE username > 'aaa'; -- With the following select Statement to create a view and name it v1
Reference this view
SELECT * FROM v1;
7.2 delete view DROP VIEW
DROP VIEW v1; -- Delete view v1
8. Stored procedure
A stored procedure is one or more SQL statements saved for future use. It can be regarded as a batch file, but its function is not limited to batch processing
You can think of a stored procedure analogously as a function. When using, you can pass parameters to the stored procedure or get the return value of the stored procedure.
Advantages of stored procedures: simple, safe and high performance
8.1 execute stored procedure CALL
CALL accepts the stored procedure name and any parameters that need to be passed to it
CALL AddNewUser('wudi','1111'); -- Call stored procedure AddNewUser，And pass parameters'wudi','1111'
Suppose AddNewUser is a stored procedure that adds a new user to the table, and the parameters specify the username column and password column of the row respectively. However, no parameter is specified for the primary key id. this is because the primary key is unique. Therefore, it is not safe to set the primary key value through external parameter transfer (an error will be reported if a duplicate id primary key is set). Instead, a unique primary key value should be automatically set inside the stored procedure!
8.2 create stored procedure
CREATE PROCEDURE procedure name ([[IN|OUT|INOUT] parameter name data type [, [IN|OUT|INOUT] parameter name data type...]] [attribute...] procedure body
CREATE PROCEDURE myproc(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM students; -- The stored procedure body can be understood as the function body END
The stored procedure may have input, output and input-output parameters as needed. If there are multiple parameters, separate them with ",". The parameters of MySQL stored procedure are used in the definition of stored procedure. There are three parameter types: in, out and inout:
The value of the IN parameter must be specified when calling the stored procedure. The value of the parameter modified IN the stored procedure cannot be returned and is the default value
OUT: the value can be changed inside the stored procedure and can be returned
INOUT: specified when called, and can be changed and returned
The beginning and END of the process body are identified by BEGIN and END.
Example 1: use IN parameter
DELIMITER // CREATE PROCEDURE in_param(IN p_in int) BEGIN SELECT p_in; -- retrieval p_in Parameter value SET p_in=2; -- Enter parameters p_in A value of 2 does not affect the external (analogous to C++(value transfer) END; // DELIMITER ; #call SET @p_in=1; CALL in_param(@p_in);
Example 2: using the OUT parameter
DELIMITER // CREATE PROCEDURE out_param(OUT p_out int) BEGIN SELECT p_out; -- Because this is OUT Parameter, so the retrieval returns NULL SET p_out=2; -- Will parameter p_out Setting the value to 2 affects the external (analogous to C++(reference passing) END; // DELIMITER ; #call SET @p_out=1; CALL out_param(@p_out);
Example 3: use INOUT parameter
DELIMITER // --Set the statement end separator to '/ /' CREATE PROCEDURE inout_param(INOUT p_inout int) BEGIN SELECT p_out; -- Because this is INOUT Parameter, so the retrieval returns p_inout Value of SET p_inout=2; -- Enter parameters p_inout Setting the value to 2 affects the external (analogous to C++(reference passing) END; // --The statement ends (note that it is no longer a semicolon;) DELIMITER ; -- The recovery statement end delimiter is';' #call SET @p_inout=1; CALL inout_param(@p_inout) ;
In fact, it tells the MySQL interpreter whether the command has ended and whether MySQL can be executed. By default, delimiter is a semicolon;. In the command-line client, if a command ends with a semicolon, MySQL will execute the command after entering.
But sometimes, I don't want Mysql to do that. For example, you may enter more statements, and the statements contain semicolons. For example, a stored procedure has a semicolon in the middle process body; However, you do not want to execute it immediately. In this case, you need to replace the delimiter with other symbols, such as / / or $$. In this way, the MySQL interpreter will execute this statement only after / / or $$. For example, in the above example, the entire create procedure statement ends with / / and is executed as a complete statement (instead of ending with a semicolon in the procedure body)
Note: after changing the DELIMITER, restore the DELIMITER to;