SQL learning notes

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.

Format:

CREATE TABLE Table name
(
	Column name data type constraint (allow to be) NULL,Default values, etc.),
    ...
)

example:

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

7. View

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

Syntax:

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) ;

DELIMITER:

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;

Keywords: Database SQL

Added by PartyMarty on Sun, 17 Oct 2021 20:46:29 +0300

Popular Keywords

©2024 Programming VIP