Learn MySQL database in one hour (Zhang Guo)

mysql> use mysql; 
mysql> GRANT ALL ON *.* TO admin@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION;

UPDATE user SET password=PASSWORD('123456') WHERE user='root'; FLUSH PRIVILEGES;

#Query select id,name from yuangong

select * from yuangong

select * from yuangong where salary>5000

#Add INSERT into yuangong(name,salary,bumenId,mobile) value('张为剑',2190.6,2,19889007867);

INSERT into yuangong(name,salary,bumenId,mobile) value('张娜拉',9871.6,1,19889007777);


#Modify update yuangong set salary=salary+1 where id=7

#deleteINSERT into yuangong(name,salary,bumenId,mobile) value('张拉拉',9871.6,1,19889007777);

delete from yuangong where id=8

#1、Add data insert into stu(name,sex,age) values('Jacky Cheung','Male',18);
insert into stu(name,sex,age) values('张娜拉','女',73);
insert into stu(name,sex,age) values('张家辉','男',23);
insert into stu(name,sex,age) values('张慧美','女',85);
insert into stu(name,sex,age) values('张铁林','男',35);

#1、Add data insert into stu(name,sex,age) values('Jacky Cheung','Male',18);
insert into stu(name,sex,age) values('张娜拉','女',73);
insert into stu(name,sex,age) values('张家辉','男',23);
insert into stu(name,sex,age) values('张慧美','女',85);
insert into stu(name,sex,age) values('张铁林','男',35);

insert into stu(name,sex,age) values('张国立','男',99);

#2. Query data #2.1. Query all students select id,name,sex,age from stu;

#2.2、Query female students older than 80 years old select id,name,sex,age from stu where age>80 and sex='female';

#1. Add data-----

insert into stu(name,sex,age) values('Jacky Cheung','male',18);
insert into stu(name,sex,age) values('张娜拉','女',73);
insert into stu(name,sex,age) values('张家辉','男',23);
insert into stu(name,sex,age) values('张慧美','女',85);
insert into stu(name,sex,age) values('张铁林','男',35);
insert into stu(name,sex,age) values('张国立','男',99);

#2. Query data-----

#2.1、Query all students select id,name,sex,age from stu;

#2.2、Query female students older than 80 years old select id,name,sex,age from stu where age>80 and sex='female';

#2.3、Query the average age select AVG(age) from stu where sex='女';

#3. Modify data-----
#3.1、Increase the age of student id 1 by 1 update stu set age=age+1 where id=1;

#3.2. Change the age of female students over 80 to 90 and add "old man" after their names
#CONCAT(str1,str2,...) concatenate string update stu set age=90,name=CONCAT(name,'(old man)') where age>=80 and sex='female';

#3.3、Change the name of student No. 4 to Zhang Huimei update stu set name='Zhang Huimei' where id=4;

#4. Delete data-----
#4.1、Delete students older than 70 years old delete from stu where age>70;

#4.2, delete all students delete from stu;

/* Start MySQL */
net start mysql

/* Connect and disconnect from server*/
mysql -h address-P port-u username-p password/* Skip permission verification and log in to MySQL */
mysqld --skip-grant-tables
-- Modify the root password password encryption function password()
update mysql.user set password=password('root');

SHOW PROCESSLIST -- Shows which threads are currently running SHOW VARIABLES -- 

/* Database operations */ ------------------
-- View the current database select database();
-- Display the current time, user name, and database version select now(), user(), version();
-- Create database create database [if not exists] database name database options database options:
 CHARACTER SET charset_name
 COLLATE collation_name
-- View existing libraries show databases[ like 'pattern']
-- View the current database information show create database database name -- Modify the option information of the database alter database library name option information -- Delete the database drop database [if exists] database name and delete the directory and its directory content related to the database at the same time /* Table operation */ ------------------
-- Create table create [temporary] table [if not exists] [database name.] table name (table structure definition) [table options]
 Each field must have a data type. There cannot be a comma after the last field. Temporary table. The table disappears automatically when the session ends. Definition of the field:
 Field Name Data Type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
-- Table options -- Character set CHARSET = charset_name
 If the table is not set, the database character set is used -- Storage engine ENGINE = engine_name 
 Tables use different data structures when managing data. Different structures lead to different processing methods, feature operations, etc. Common engines: InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
 Different engines use different methods to save table structures and data. MyISAM table file meaning: .frm table definition, .MYD table data, .MYI table index. InnoDB table file meaning: .frm table definition, tablespace data and log file. SHOW ENGINES -- Displays storage engine status information. SHOW ENGINE engine name {LOGS|STATUS} -- Displays storage engine logs or status information. -- Data file directory DATA DIRECTORY = 'directory'
 -- Index file directory INDEX DIRECTORY = 'directory'
 -- Table comment COMMENT = 'string'
 -- Partition option PARTITION BY ... (see manual for details)
-- View all tables SHOW TABLES[ LIKE 'pattern']
 SHOW TABLES FROM table name -- View the table structure SHOW CREATE TABLE table name (more detailed information)
 DESC table name/ DESCRIBE table name/ EXPLAIN table name/ SHOW COLUMNS FROM table name [LIKE 'PATTERN']
 SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
-- Modify the table -- Modify the options of the table itself ALTER TABLE table name table options EG: ALTER TABLE table name ENGINE=MYISAM;
 -- Rename the table RENAME TABLE original table name TO new table name RENAME TABLE original table name TO database name.table name (the table can be moved to another database)
 -- RENAME can exchange two table names -- Modify the field structure of the table ALTER TABLE table name operation name -- Operation name ADD [COLUMN] field name -- Add a field AFTER field name -- Indicates adding after the field name FIRST -- Indicates adding first ADD PRIMARY KEY (field name) -- Create a primary key ADD UNIQUE [index name] (field name) -- Create a unique index ADD INDEX [index name] (field name) -- Create a common index ADD 
 DROP [COLUMN] field name -- delete the field MODIFY [COLUMN] field name field attributes -- support the modification of field attributes, but not the field name (all original attributes must also be written)
 CHANGE [COLUMN] original field name new field name field attributes -- support for modifying field names DROP PRIMARY KEY -- delete the primary key (before deleting the primary key, you need to delete its AUTO_INCREMENT attribute)
 DROP INDEX index name -- delete index DROP FOREIGN KEY foreign key -- delete foreign key -- delete table DROP TABLE [IF EXISTS] table name...
-- Clear table data TRUNCATE [TABLE] table name -- Copy table structure CREATE TABLE table name LIKE table name to be copied -- Copy table structure and data CREATE TABLE table name [AS] SELECT * FROM table name to be copied -- Check table for errors CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- Optimize table OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- Repair table REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...



/* Data operation */ ------------------
-- Add INSERT [INTO] table name [(field list)] VALUES (value list) [, (value list), ...]
 -- If the list of values ​​to be inserted contains all fields and is in the same order, the field list can be omitted.
 -- Multiple data records can be inserted at the same time!
 REPLACE is exactly the same as INSERT and is interchangeable.
 INSERT [INTO] table name SET field name=value [, field name=value, ...]
-- Check the SELECT field list FROM table name [other clauses]
 -- Multiple fields from multiple tables -- Other clauses can be omitted -- The field list can be replaced by * to indicate all fields -- DELETE FROM table name [delete condition clause]
 If there is no conditional clause, all will be deleted--Change UPDATE table name SET field name = new value [, field name = new value] [update condition]

/* Character set encoding */ ------------------
-- MySQL, database, table, and field can all set encodings -- Data encoding does not need to be consistent with client encoding SHOW VARIABLES LIKE 'character_set_%' -- View all character set encoding items character_set_client The encoding used when the client sends data to the server character_set_results The encoding used by the server to return results to the client character_set_connection Connection layer encoding SET variable name = variable value set character_set_client = gbk;
 set character_set_results = gbk;
 set character_set_connection = gbk;
SET NAMES GBK; -- equivalent to completing the above three settings -- Collation set Collation set is used for sorting SHOW CHARACTER SET [LIKE 'pattern']/SHOW CHARSET [LIKE 'pattern'] View all character sets SHOW COLLATION [LIKE 'pattern'] View all collation sets charset character set encoding setting character set encoding collate collation set encoding setting collation set encoding/* Data type (column type) */ ------------------
1. Numeric type -- a. Integer----------
 Type byte range (sign bit)
 tinyint 1 byte -128 ~ 127 unsigned bits: 0 ~ 255
 smallint 2 bytes -32768 ~ 32767
 mediumint 3 bytes -8388608 ~ 8388607
 int 4 bytes bigint 8 bytes int(M) M indicates the total number of bits - By default, there is a sign bit, unsigned attribute modification - Display width, if a number is not enough for the number of bits set when defining the field, it is padded with 0 in front, zerofill attribute modification example: int(5) inserts a number '123', and after padded, it is '00123'
 - The smaller the better, provided the requirements are met.
 - 1 represents a bool value of true, 0 represents a bool value of false. MySQL does not have a Boolean type and is represented by integers 0 and 1. Tinyint(1) is often used to represent Boolean types.

-- b. Floating point type----------
 Type Byte Range float (single precision) 4 bytes double (double precision) 8 bytes Floating point type supports both the unsigned attribute of the sign bit and the zerofill attribute of the display width.
 Unlike integers, 0s are filled in front and behind.
 When defining a floating point type, you need to specify the total number of digits and the number of decimal places.
 float(M, D) double(M, D)
 M represents the total number of digits, and D represents the number of decimal places.
 The size of M and D determines the range of floating point numbers. Different from the fixed range of integer types.
 M represents both the total number of digits (excluding the decimal point and positive and negative signs) and the display width (including all displayed symbols).
 Supports scientific notation.
 Floating point numbers represent approximate values.

-- c. Fixed-point number----------
 decimal -- variable length decimal(M, D) M also represents the total number of digits and D represents the number of decimal places.
 Saves an exact value without changing the data, unlike the rounding of floating point numbers.
 Convert the floating point number to a string to store it, with each 9 digits stored as 4 bytes.

2. String type -- a. char, varchar ----------
 char is a fixed-length string, which is fast but wastes space. varchar is a variable-length string, which is slow but saves space. M represents the maximum length that can be stored. This length is the number of characters, not the number of bytes.
 Different codes occupy different spaces.
 char, up to 255 characters, regardless of encoding.
 varchar, up to 65535 characters, depending on the encoding.
 The maximum length of a valid record cannot exceed 65535 bytes.
 The maximum length of utf8 is 21844 characters, the maximum length of gbk is 32766 characters, and the maximum length of latin1 is 65532 characters. Varchar is variable length, and storage space is required to save the length of varchar. If the data is less than 255 bytes, one byte is used to save the length, otherwise two bytes are required to save it.
 The maximum effective length of varchar is determined by the maximum row size and the character set used.
 The maximum valid length is 65532 bytes, because when a varchar stores a string, the first byte is empty and does not contain any data, and then two bytes are required to store the length of the string, so the effective length is 64432-1-2=65532 bytes.
 For example: If a table is defined as CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; what is the maximum value of N? Answer: (65535-1-2-4-30*3)/3

-- b. blob, text ----------
 blob binary string (byte string)
 tinyblob, blob, mediumblob, longblob
 text Non-binary string (character string)
 tinytext, text, mediumtext, longtext
 When defining text, there is no need to define the length, and the total length will not be calculated.
 When defining a text type, you cannot give it a default value -- c. binary, varbinary ----------
 Similar to char and varchar, it is used to store binary strings, that is, to store byte strings instead of character strings.
 char, varchar, text correspond to binary, varbinary, blob.

3. The date and time type generally uses integer to save timestamps, because PHP can easily format timestamps.
 datetime 8-byte date and time 1000-01-01 00:00:00 to 9999-12-31 23:59:59
 date 3-byte date 1000-01-01 to 9999-12-31
 timestamp 4 bytes timestamp 19700101000000 to 2038-01-19 03:14:07
 time 3 bytes time -838:59:59 to 838:59:59
 year 1 byte Year 1901 - 2155
 
datetime "YYYY-MM-DD hh:mm:ss"
timestamp "YY-MM-DD hh:mm:ss"
 "YYYYMMDDhhmmss"
 "YYMMDDhhmmss"
 YYYYMMDDhhmmss
 YYMMDDhhmmss
date "YYYY-MM-DD"
 "YY-MM-DD"
 "YYYYMMDD"
 "YYMMDD"
 YYYYMMDD
 YYMMDD
time "hh:mm:ss"
 "hhmmss"
 hhmmss
year "YYYY"
 "YY"
 YYYY
 YY

4. Enumeration and Collection -- Enumeration (enum) ----------
enum(val1, val2, val3...)
 Select a single value from the known values. The maximum number is 65535.
 When the enumeration value is saved, it is saved as a 2-byte integer (smallint). Each enumeration value is incremented one by one starting from 1 in the order of the saved position.
 It is represented as a string type, but is stored as an integer type.
 The index of a NULL value is NULL.
 The index value of the empty string error value is 0.

-- Set ----------
set(val1, val2, val3...)
 create table tab ( gender set('男', '女', 'None') );
 insert into tab values ​​('男, 女');
 There can be up to 64 different members. Stored as bigint, 8 bytes in total. Takes the form of bitwise operations.
 When creating a table, trailing spaces in SET member values ​​are automatically removed.

/*Select type*/
-- PHP perspective 1. Functionality is met 2. Storage space is as small as possible, processing efficiency is higher 3. Consider compatibility issues -- IP storage -----------
1. If you only need to store data, you can use a string. 2. If you need to calculate, search, etc., you can store it as a 4-byte unsigned int, i.e. unsigned
 1) The PHP function ip2long can be converted to an integer, but there will be a sign-carrying problem. Need to be formatted as an unsigned integer.
 Use the sprintf function to format the string sprintf("%u", ip2long('192.168.3.134'));
 Then use long2ip to convert the integer back to an IP string 2) MySQL function conversion (unsigned integer, UNSIGNED)
 INET_ATON('127.0.0.1') Convert IP to integer INET_NTOA(2130706433) Convert integer to IP
 



/* Column attributes (column constraints) */ ------------------
1. Primary Key - A field that can uniquely identify a record can be used as a primary key.
 - A table can have only one primary key.
 - The primary key is unique.
 - When declaring a field, use the primary key to identify it.
 It can also be declared after the field list, for example: create table tab (id int, stu varchar(10), primary key (id));
 - The value of the primary key field cannot be null.
 - A primary key can be composed of multiple fields. In this case, the method needs to be declared after the field list.
 Example: create table tab ( id int, stu varchar(10), age int, primary key (stu, age));

2. unique index (unique constraint)
 This prevents the value of a field from being repeated.
 
3. Null constraint null is not a data type, but an attribute of a column.
 Indicates whether the current column can be null, indicating nothing.
 null, empty is allowed. default.
 not null, not allowed to be empty.
 insert into tab values ​​(null, 'val');
 -- This means setting the value of the first field to null, depending on whether the field is allowed to be null
 
4. default The default value attribute is the default value of the current field.
 insert into tab values ​​(default, 'val'); -- This means that the default value is forced to be used.
 create table tab ( add_time timestamp default current_timestamp );
 -- means setting the current timestamp as the default value.
 current_date, current_time

5. auto_increment automatic growth constraint automatic growth must be an index (primary key or unique)
 Only one field can be auto-incremented.
 The default value is 1 to start automatic growth. You can set it by using the table attribute auto_increment = x, or by using alter table tbl auto_increment = x;

6. comment Example: create table tab (id int) comment 'comment content';

7. Foreign key constraints are used to limit the data integrity of the primary table and the secondary table.
 alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
 -- Associate the t1_id foreign key of table t1 to the id field of table t2.
 -- Each foreign key has a name. You can use a constraint to specify the table where the foreign key exists, called the slave table (child table), and the table to which the foreign key points, called the master table (parent table).

 Function: Maintain data consistency and integrity. The main purpose is to control the data stored in the foreign key table (from table).

 In MySQL, you can use foreign key constraints with the InnoDB engine:
 grammar:
 foreign key (foreign key field) references main table name (related field) [action when main table record is deleted] [action when main table record is updated]
 At this time, it is necessary to detect that a foreign key of a secondary table needs to be constrained to an existing value of the primary table. A foreign key can be set to null when there is no association, provided that the foreign key column does not have not null.

 You do not need to specify the action when the master table record is changed or updated, in which case the operation on the master table is rejected.
 If on update or on delete is specified: When deleting or updating, you can choose from the following operations:
 1. cascade, cascade operation. The master table data is updated (primary key value is updated), and the slave table is also updated (foreign key value is updated). The master table record is deleted, and the related records in the slave table are also deleted.
 2. set null, set to null. The primary table data is updated (the primary key value is updated), and the foreign key of the secondary table is set to null. The master table record is deleted and the foreign key of the related record in the slave table is set to null. But note that the foreign key column is required to have no not null attribute constraint.
 3. restrict, reject parent table deletion and update.

 Note that foreign keys are only supported by the InnoDB storage engine. Other engines are not supported.


/* Table creation specifications*/ ------------------
 -- Normal Format, NF
 - Each table stores information about an entity - Each table has an ID field as the primary key - ID primary key + atomic table - 1NF, the first normal form field cannot be divided further, it satisfies the first normal form.
 -- 2NF, the second normal form satisfies the first normal form, and no partial dependencies can occur.
 Partial dependencies can be avoided by eliminating the primary key. Add single column keywords.
 -- 3NF, the third normal form satisfies the second normal form, and no transitive dependencies can occur.
 A field depends on the primary key, and other fields depend on it. This is a transitive dependency.
 The data of an entity information is placed in a table.


/* select */ ------------------

select [all|distinct] select_expr from -> where -> group by [aggregate function] -> having -> order by -> limit

a. select_expr
 -- You can use * to indicate all fields.
 select * from tb;
 -- Expressions can be used (calculation formulas, function calls, and fields are also expressions)
 select stu, 29+25, now() from tb;
 -- You can use aliases for each column. Useful for simplifying column identification and avoiding duplication of multiple column identifiers.
 - Use the as keyword, or omit as.
 select stu+10 as add10 from tb;

b. The from clause is used to identify the source of the query.
 -- You can give a table an alias. Use the as keyword.
 select * from tb1 as tt, tb2 as bb;
 -- Multiple tables can appear after the from clause.
 -- Multiple tables are stacked together horizontally, and the data forms a Cartesian product.
 select * from tb1, tb2;

c. where clause - filter from the data source obtained from.
 -- Integer 1 represents true, 0 represents false.
 -- An expression consists of operators and operands.
 -- Operands: variables (fields), values, function return values ​​-- Operators:
 =, <=>, <>, !=, <=, <, >=, >, !, &&, ||, 
 in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
 is/is not plus true/false/unknown to check the truth of a value <=> has the same function as <>, and <=> can be used for null comparison d. group by clause, grouping clause group by field/alias [sort method]
 After grouping, they will be sorted. Ascending: ASC, Descending: DESC
 
 The following aggregate functions must be used with group by:
 count Returns the number of different non-NULL values ​​count(*), count(field)
 sum finds the sum max finds the maximum value min finds the minimum value avg finds the average value group_concat returns a string result with the concatenated non-NULL values ​​from a group. Intra-group string concatenation.

e. Having clause, the conditional clause has the same function and usage as the where clause, but the execution time is different.
 where executes the detection data at the beginning and filters the original data.
 Having filters the filtered results again.
 The having field must be queried, and the where field must exist in the data table.
 Where cannot use field aliases, but having can. Because when the WHERE code is executed, the column value may not have been determined yet.
 Where aggregate functions cannot be used. Generally, having is used only when an aggregate function is needed.
 The SQL standard requires that HAVING must reference columns in the GROUP BY clause or columns used in an aggregate function.

f. order by clause, sorting clause order by sort field/alias sorting method [, sort field/alias sorting method]...
 Ascending: ASC, Descending: DESC
 Supports sorting on multiple fields.

g. limit clause, the clause to limit the number of results only limits the number of processed results. Treat the processed results as a set, in the order in which the records appear, with the index starting from 0.
 limit starting position, omitting the first parameter when getting the number of entries means starting from index 0. limit Get the number of records h. distinct, all Option distinct Remove duplicate records Default is all, all records /* UNION */ ------------------
 Combines the results of multiple select queries into a single result set.
 SELECT ... UNION [ALL|DISTINCT] SELECT ...
 The default DISTINCT mode, that is, all returned rows are unique, is recommended to wrap each SELECT query in parentheses.
 When ORDER BY is used for sorting, LIMIT must be added for combination.
 The number of fields in each select query must be the same.
 The field list (number, type) of each select query should be consistent, because the field names in the result are based on the first select statement.


/* Subquery */ ------------------
 - Subqueries must be enclosed in parentheses.
-- From type requires a table after from, and the subquery result must be given an alias.
 - Simplify the conditions within each query.
 - The from type needs to generate a temporary table with the result, which can be used to release the lock of the original table.
 - The subquery returns a table, table type subquery.
 select * from (select * from tb where id>0) as subfrom where id>1;
-- where type - The subquery returns a value, a scalar subquery.
 - No need to give subqueries aliases.
 - The table in the where subquery cannot be directly updated.
 select * from tb where money = (select max(money) from tb);
 -- Column subquery if the subquery result returns a column.
 Use in or not in to complete the query exists and not exists conditions. If the subquery returns data, it returns 1 or 0. Often used to judge conditions.
 select column1 from t1 where exists (select * from t2);
 -- The row subquery query condition is a row.
 select * from t1 where (id, gender) in (select id, gender from t2);
 Row constructor: (col1, col2, ...) or ROW(col1, col2, ...)
 Row constructors are often used to compare subqueries that return two or more columns.

 -- Special operator != all() is equivalent to not in
 = some() is equivalent to in. any is an alias for some != some() is not equivalent to not in, and is not equal to any of them.
 all, some can be used with other operators.


/* Join query (join) */ ------------------
 You can connect fields from multiple tables and specify connection conditions.
-- Inner join
 - The default is inner join, inner can be omitted.
 - A connection can only be sent if data exists. That is, there cannot be blank lines in the connection result.
 on indicates the connection condition. Its conditional expression is similar to where. You can also omit the condition (which means it is always true)
 You can also use where to express the join condition.
 There is also using, but the field names must be the same. using(field name)

 -- Cross join
 That is, there is no conditional inner join.
 select * from tb1 cross join tb2;
-- Outer join
 - If the data does not exist, it will also appear in the connection results.
 --Left outer join
 If the data does not exist, the left table record will appear, and the right table will be filled with null - right outer join
 If the data does not exist, the right table record will appear, and the left table will be filled with null - natural join
 Automatically determine the connection conditions and complete the connection.
 This is equivalent to omitting using, and the same field name will be automatically searched.
 natural join
 natural left join
 natural right join

select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;

/* Import and export */ ------------------
select * into outfile file address [control format] from table name; -- Export table data load data [local] infile file address [replace|ignore] into table table name [control format]; -- Import data The default delimiter of the generated data is tabulation. If local is not specified, the data file must be on the server. The replace and ignore keywords control the handling of duplicates of existing unique key records. -- Control format fields Control field format default: fields terminated by '\t' enclosed by '' escaped by '\\'
 terminated by 'string' -- terminated enclosed by 'char' -- wrapped escaped by 'char' -- escaped -- Example:
 SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 FROM test_table;
lines control line format default: lines terminated by '\n'
 terminated by 'string' -- terminated /* insert */ ------------------
The data obtained by the select statement can be inserted using insert.

The column specification can be omitted, and the required values ​​() brackets provide the values ​​of all fields in column order.
 Or use the set syntax.
 insert into tbl_name set field=value,...;

Multiple values ​​can be used at once in the form of (), (), ();.
 insert into tbl_name values ​​(), (), ();

You can use expressions when specifying column values.
 insert into tbl_name values ​​(field_value, 10+10, now());
A special value, default, can be used to indicate that the column uses the default value.
 insert into tbl_name values ​​(field_value, default);

You can use the result of a query as the value to be inserted.
 insert into tbl_name select ...;

You can specify that when an inserted value conflicts with the primary key (or unique index), the information of other non-primary key columns is updated.
 insert into tbl_name values/set/select on duplicate key update field=value, …;

/* delete */ ------------------
DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

Delete the maximum number of records to be deleted according to the specified conditions. Limit

Can be deleted by sorting conditions. order by + limit

Supports multi-table deletion, using similar join syntax.
delete from needs to delete data from multiple tables 1 and 2 using table join operation conditions.

/* truncate */ ------------------
TRUNCATE [TABLE] tbl_name
The difference between clearing data, deleting and rebuilding the table:
1. truncate is to delete the table and then create it, delete is to delete one by one. 2. truncate resets the value of auto_increment. But delete will not know 3, truncate does not know how many records were deleted, but delete knows.
4. When used for partitioned tables, truncate will preserve the partitions /* Backup and restore */ ------------------
Backup, save the data structure and data in the table.
This is done using the mysqldump command.

-- Export 1. Export a table mysqldump -u username-p password database name table name> file name (D:/a.sql)
2. Export multiple tables mysqldump -u username -p password database name table 1 table 2 table 3 > file name (D:/a.sql)
3. Export all tables mysqldump -u username -p password database name > file name (D:/a.sql)
4. Export a library mysqldump -u username -p password -B library name > file name (D:/a.sql)

You can use -w to carry backup conditions -- import 1. When logging in to mysql:
  source backup file 2. Without logging in, mysql -u username -p password database name < backup file /* view */ ------------------
What is a View:
 A view is a virtual table whose contents are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values ​​in the database. The row and column data comes from the tables referenced by the query that defines the view and is generated dynamically when the view is referenced.
 A view has a table structure file but no data file.
 A view acts like a filter on the underlying tables referenced in it. The filters that define a view can come from one or more tables in the current or other databases, or from other views. There are no restrictions on querying through views, and there are few restrictions on modifying data through them.
 A view is a SQL statement of a query stored in a database. It is used for two main reasons: security reasons. Views can hide some data, such as the social insurance fund table, which can display only the name and address without displaying the social insurance number and salary, etc. Another reason is that it can make complex queries easy to understand and use.

-- Create a view CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
 - The view name must be unique and cannot have the same name as a table.
 - The view can use the column names queried by the select statement, or specify the corresponding column names yourself.
 - You can specify the algorithm to be executed by the view by specifying ALGORITHM.
 - column_list, if present, must be equal to the number of columns retrieved by the SELECT statement -- View the structure SHOW CREATE VIEW view_name 

-- Drop a view - After dropping a view, the data still exists.
 - Multiple views can be deleted at the same time.
 DROP VIEW [IF EXISTS] view_name ...

-- Modify view structure - Generally do not modify the view, because not all updated views will be mapped to the table.
 ALTER VIEW view_name [(column_list)] AS select_statement

-- View function 1. Simplify business logic 2. Hide the actual table structure from the client -- View algorithm (ALGORITHM)
 MERGE merges the view's query statement with the external query before executing!
 After the TEMPTABLE temporary table executes the view, it forms a temporary table and then performs an outer query!
 UNDEFINED Undefined (default), which means that MySQL chooses the corresponding algorithm independently.



/* transaction */ ------------------
A transaction refers to a logical group of operations. Each unit that makes up this group of operations must either succeed or fail. 
 - Supports collective success or collective cancellation of consecutive SQL statements.
 - Transaction is a function of database in data management.
 - Need to use InnoDB or BDB storage engine to support auto-commit feature.
 - InnoDB is known as a transaction-safe engine.

-- Transaction starts START TRANSACTION; or BEGIN;
 After a transaction is opened, all executed SQL statements are considered SQL statements within the current transaction.
--Transaction commit COMMIT;
-- Transaction rollback ROLLBACK;
 If a problem occurs in some operations, it is mapped to before the transaction is started.

-- Transaction characteristics 1. Atomicity
 A transaction is an indivisible unit of work. Either all operations in a transaction occur or none of them occur.
 2. Consistency
 The integrity of the data before and after the transaction must remain consistent.
 - When a transaction starts and ends, external data is consistent - During the entire transaction, operations are continuous 3. Isolation
 When multiple users access the database concurrently, the transaction of one user cannot be interfered with by other users, and the data between multiple concurrent transactions must be isolated from each other.
 4. Durability
 Once a transaction is committed, its changes to the data in the database are permanent.

-- Transaction implementation 1. The table type supported by the transaction is required 2. Start the transaction before executing a set of related operations 3. After the entire set of operations is completed, if they are all successful, commit; if there is a failure, choose to roll back, which will return to the backup point at the beginning of the transaction.

-- The principle of the transaction is completed using the autocommit feature of InnoDB.
 After a normal MySQL statement is executed, the current data submission operation can be seen by other clients.
 Transactions temporarily turn off the "auto commit" mechanism and require commit to persist data operations.

-- Note 1. Data Definition Language (DDL) statements cannot be rolled back, such as statements that create or drop a database, and statements that create, drop, or change a table or stored subprogram.
 2. Transactions cannot be nested -- Savepoint SAVEPOINT savepoint name -- Set a transaction savepoint ROLLBACK TO SAVEPOINT savepoint name -- Roll back to the savepoint RELEASE SAVEPOINT savepoint name -- Delete the savepoint -- InnoDB autocommit feature setting SET autocommit = 0|1; 0 means turning off autocommit, 1 means turning on autocommit.
 - If it is closed, the results of normal operations will not be visible to other clients, and data operations can only be persisted after commit.
 - You can also turn off autocommit to start a transaction. But unlike START TRANSACTION,
 SET autocommit permanently changes the server's settings until the settings are modified again next time. (for current connection)
 START TRANSACTION records the status before it is started, and once the transaction is committed or rolled back, the transaction needs to be started again. (For current transaction)


/* Lock table */
Table locks are only used to prevent other clients from improperly reading and writing. MyISAM supports table locks, and InnoDB supports row locks -- LOCK TABLES tbl_name [AS alias]
-- UNLOCK TABLES


/* Trigger */ ------------------
 A trigger is a named database object associated with a table that is activated when a specific event occurs on the table: the addition, modification, or deletion of a record.

-- Create a trigger CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
 parameter:
 trigger_time is the action time of the trigger program. It can be before or after to indicate that the trigger is fired before or after the statement that activates it.
 trigger_event specifies the type of statement that activates the trigger. INSERT: activates the trigger when a new row is inserted into the table. UPDATE: activates the trigger when a row is changed. DELETE: activates the trigger when a row is deleted from the table. tbl_name: The monitored table must be a permanent table. The trigger cannot be associated with a TEMPORARY table or view.
 trigger_stmt: The statement that is executed when the trigger is activated. To execute multiple statements, use the BEGIN...END compound statement structure -- DROP TRIGGER [schema_name.]trigger_name

You can use old and new to replace the old and new data update operations. Before the update, it is old, and after the update, it is new.
 Delete operation, only old.
 To add an operation, only new.

-- Note 1. For a given table with the same trigger action time and event, there cannot be two triggers.


-- Character concatenation function concat(str1[, str2,...])

-- Branch statement if condition then
 Execute the statement elseif condition then
 Execute the else statement
 Execute the statement end if;

-- Modify the outermost statement terminator delimiter Customize the SQL statement terminator delimiter; -- Change back to the original semicolon -- Statement block wrapping begin
 Statement block end

-- Special execution 1. Whenever a record is added, the program will be triggered.
2. Insert into on duplicate key update syntax will trigger:
 If there are no duplicate records, before insert and after insert will be triggered;
 If there are duplicate records and they are updated, before insert, before update, and after update will be triggered;
 If there are duplicate records but no update occurs, before insert and before update are triggered.
3. Replace syntax If there are records, execute before insert, before delete, after delete, after insert


/* SQL Programming */ ------------------

--// Local variables----------
-- Variable declaration declare var_name[,...] type [default value] 
 This statement is used to declare local variables. To provide a default value for a variable, include a default clause. The value can be specified as an expression and does not need to be a constant. If there is no default clause, the initial value is null. 

-- Assignment Use the set and select into statements to assign values ​​to variables.

 - Note: Global variables (user-defined variables) can be used within a function


--// Global variables----------
--Definition and assignment The set statement can define and assign values ​​to variables.
set @var = value;
You can also use the select into statement to initialize and assign values ​​to variables. This requires that the select statement can only return one row, but it can be multiple fields, which means assigning values ​​to multiple variables at the same time, and the number of variables needs to be consistent with the number of columns in the query.
You can also think of the assignment statement as an expression and execute it through select. In order to avoid = being treated as a relational operator, use := instead. (The set statement can use = and :=).
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;

select into can assign the data obtained from the table query to a variable.
 -| select max(height) into @max_height from tb;

-- Custom variable names To avoid conflicts between user-defined variables and system identifiers (usually field names) in select statements, user-defined variables use @ as the starting symbol before the variable name.
@var=10;

 - Once a variable is defined, it is valid throughout the entire session (login to logout)


--// Control structure----------
-- if statement if search_condition then 
 statement_list 
[elseif search_condition then
 statement_list]
...
[else
 statement_list]
end if;

-- case statement CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END


-- while loop [begin_label:] while search_condition do
 statement_list
end while [end_label];

- If you need to terminate the while loop early within the loop, you need to use a label; labels need to appear in pairs.

 -- Exit the loop Exit the entire loop leave
 Exit the current loop iterate
 Determine which loop to exit by the exit label --//Built-in function----------
-- Numerical function abs(x) -- Absolute value abs(-10.9) = 10
format(x, d) -- formats the thousandths value format(1234567.456, 2) = 1,234,567.46
ceil(x) -- round up ceil(10.1) = 11
floor(x) -- round down floor (10.1) = 10
round(x) -- round to the nearest integer mod(m, n) -- m%nm mod n remainder 10%3=1
pi() -- Get the value of pi pow(m, n) -- m^n
: : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :
instr(string, substring) -- Returns the position where substring first appears in stringconcat(string [,...]) -- Concatenates substringscharset(str) -- Returns the character set of a substringlcase(string) -- Converts to lowercaseleft(string, length) -- Takes length characters from the left of string2load_file(file_name) -- Reads content from a filelocate(substring, string [,start_position]) -- Same as instr, but can specify the starting positionlpad(string, length, pad) -- Repeatedly add pad to the beginning of string until the length of the string is length
ltrim(string) -- remove leading spaces repeat(string, count) -- repeat count times rpad(string, length, pad) -- add pad after str until the length is length
rtrim(string) -- remove trailing spaces strcmp(string1 ,string2) -- compare the size of two strings character by character -- Process function case when [condition] then result [when [condition] then result ...] [else result] end Multi-branch if(expr1,expr2,expr3) Double branch.

-- Aggregate function count()
sum();
max();
min();
avg();
group_concat()

-- Other commonly used functions md5();
default();


--//Stored function, custom function----------
-- Create a new CREATE FUNCTION function_name (parameter list) RETURNS return value type function body - function name, which should be a legal identifier and should not conflict with existing keywords.
 - A function should belong to a certain database. You can use the form of db_name.funciton_name to execute the database to which the current function belongs, otherwise it is the current database.
 - The parameter part consists of "parameter name" and "parameter type". Multiple parameters are separated by commas.
 - The function body consists of multiple available MySQL statements, flow control, variable declaration and other statements.
 - Multiple statements should be enclosed in a begin...end statement block.
 - There must be a return statement.

-- DROP FUNCTION [IF EXISTS] function_name;

-- View SHOW FUNCTION STATUS LIKE 'partten'
 SHOW CREATE FUNCTION function_name;

-- Modify ALTER FUNCTION function_name function options -- // stored procedure, custom function ----------
--Define a stored procedure: A stored procedure is a piece of code (procedure) that is stored in the database in SQL.
A stored procedure is usually used to complete a piece of business logic, such as registration, class fee payment, order entry, etc.
A function usually focuses on a certain function and is regarded as a service for other programs. It needs to be called in other statements, while a stored procedure cannot be called by others and is executed by itself through call.

-- Create CREATE PROCEDURE sp_name (parameter list)
 Procedure body parameter list: Different from the function parameter list, it is necessary to specify the parameter type IN, which indicates input type, OUT, which indicates output type, INOUT, which indicates mixed type. Note that there is no return value.


/* Stored Procedure */ ------------------
A stored procedure is a collection of executable code. Compared with functions, it is more inclined to business logic.
Call: CALL procedure name -- Note - there is no return value.
- Can only be called alone, not mixed with other statements -- Parameter IN|OUT|INOUT Parameter name Data type IN Input: During the call, input data into the parameter inside the procedure body OUT Output: During the call, return the result of the procedure body to the client INOUT Input and output: Both input and output are possible -- Syntax CREATE PROCEDURE Procedure name (parameter list)
BEGIN
 Procedure body END


/* User and permission management */ ------------------
User information table: mysql.user
-- FLUSH PRIVILEGES
-- Add user CREATE USER username IDENTIFIED BY [PASSWORD] password (string)
 - You must have the global CREATE USER privilege for the mysql database, or have the INSERT privilege.
 - Can only create users, cannot grant permissions.
 - Username, note the quotes: e.g. 'user_name'@'192.168.1.1'
 - The password also needs to be quoted, and pure numeric passwords also need to be quoted - To specify the password in plain text, omit the PASSWORD keyword. To specify the password as a hashed value returned by the PASSWORD() function, include the keyword PASSWORD.
-- Rename user RENAME USER old_user TO new_user
-- Set password SET PASSWORD = PASSWORD('password') -- Set password for current user SET PASSWORD FOR username = PASSWORD('password') -- Set password for specified user -- Delete user DROP USER username -- Assign permissions/add user GRANT permission list ON table name TO username [IDENTIFIED BY [PASSWORD] 'password']
 - all privileges means all privileges - *.* means all tables in all libraries - library name.table name means a table under a certain library - view privileges SHOW GRANTS FOR user name - view current user privileges SHOW GRANTS; or SHOW GRANTS FOR CURRENT_USER; or SHOW GRANTS FOR CURRENT_USER();
-- Revoke privileges REVOKE privilege list ON table name FROM username REVOKE ALL PRIVILEGES, GRANT OPTION FROM username -- Revoke all privileges -- Privilege level -- To use GRANT or REVOKE, you must have the GRANT OPTION privilege, and you must use it for the privilege you are granting or revoking.
Global level: Global permissions apply to all databases in a given server.
 GRANT ALL ON *.* and REVOKE ALL ON *.* grant and revoke only global privileges.
Database level: Database permissions apply to all objects in a given database, mysql.db, mysql.host
 GRANT ALL ON db_name.* and REVOKE ALL ON db_name.* grant and revoke database privileges only.
Table level: Table privileges apply to all columns in a given table. mysql.table_priv
 GRANT ALL ON db_name.tbl_name and REVOKE ALL ON db_name.tbl_name grant and revoke table privileges only.
Column-level: Column privileges apply to a single column in a given table. mysql.columns_priv
 When using REVOKE, you must specify the same columns as those being granted.
-- Privilege list ALL [PRIVILEGES] -- Set all simple privileges except GRANT OPTION ALTER -- Allow use of ALTER TABLE
ALTER ROUTINE -- change or delete a stored procedure CREATE -- enable use of CREATE TABLE
CREATE ROUTINE -- create a stored routine CREATE TEMPORARY TABLES -- allow use of CREATE TEMPORARY TABLE
CREATE USER -- Enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW -- Allows the use of CREATE VIEW
DELETE -- Allows the use of DELETE
DROP -- Allows the use of DROP TABLE
EXECUTE -- Allows the user to run a stored procedure FILE -- Allows the use of SELECT ... INTO OUTFILE and LOAD DATA INFILE
INDEX -- Allows use of CREATE INDEX and DROP INDEX
INSERT -- Allows the use of INSERT
LOCK TABLES -- Allows you to use LOCK TABLES on tables for which you have SELECT privileges.
PROCESS -- Enable use of SHOW FULL PROCESSLIST
REFERENCES -- not implemented RELOAD -- FLUSH is allowed
REPLICATION CLIENT -- allows the user to ask for the address of a slave or master server REPLICATION SLAVE -- for replication slave servers (reading binary log events from the master server)
SELECT -- Allows the use of SELECT
SHOW DATABASES -- Displays all databases SHOW VIEW -- Enables the use of SHOW CREATE VIEW
SHUTDOWN -- allow use of mysqladmin shutdown
SUPER -- Enables use of CHANGE MASTER , KILL , PURGE MASTER LOGS , and SET GLOBAL statements, and the mysqladmin debug command; allows you to connect (once) even if max_connections has been reached.
UPDATE -- Allows the use of UPDATE
USAGE -- Synonym for "no privileges" GRANT OPTION -- Allow granting of privileges /* Table maintenance */
--Analyze and store the keyword distribution of the table ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table name...
-- Check one or more tables for errors CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- Defragment the data file OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...


/* Miscellaneous*/ ------------------
1. You can use backticks (`) to wrap identifiers (library names, table names, field names, indexes, aliases) to avoid duplication with keywords! Chinese can also be used as an identifier!
2. Each library directory has an option file db.opt that saves the current database.
3. Notes:
 Single-line comment# Comment contentMulti-line comment/* Comment content*/
 Single-line comment -- comment content (standard SQL comment style, requiring a space character (space, TAB, line break, etc.) after a double dash)
4. Pattern wildcards:
 _ Any single character % Any number of characters, including zero characters Single quotes need to be escaped \'
5. The statement terminator in the CMD command line can be ";", "\G", "\g", which only affects the display result. Elsewhere, use a semicolon to end the statement. delimiter can modify the statement terminator of the current dialog.
6. SQL is not case sensitive 7. Clear existing statements: \c

/*======================================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2017/3/5 10:29:05 */
/*======================================================================*/


drop table if exists Address;

drop table if exists ArticleComment;

drop table if exists ArticleType;

drop table if exists Articles;

drop table if exists DictSub;

drop table if exists DictTop;

drop table if exists OrderPdt;

drop table if exists Orders;

drop table if exists ProductComment;

drop table if exists Products;

drop table if exists Users;

/*======================================================================*/
/* Table: Address */
/*======================================================================*/
create table Address
(
 `AddressId` int not null auto_increment comment 'Delivery address number',
 `UserId` int not null comment 'User ID',
 `Province` varchar(50) not null comment '省',
 `City` varchar(50) not null comment '市',
 `County` varchar(50) not null comment 'County/District',
 `Street` varchar(300) not null comment 'Detailed address',
 `RevName` varchar(30) not null comment 'Consignee's name',
 `PostCode` varchar(20) comment 'Postal code',
 `Mobile` varchar(50) not null comment 'Mobile phone',
 `Phone` varchar(50) comment 'Phone',
 `IsDefault` bool comment 'Is it the default address',
 primary key (AddressId)
);

alter table Address comment 'delivery address';

/*======================================================================*/
/* Table: ArticleComment */
/*======================================================================*/
create table ArticleComment
(
 `ArticleCommentId` int not null auto_increment comment 'Article comment number',
 `ArticleId` int not null comment 'Article number',
 `UserId` int not null comment 'User ID',
 `ArticleCommentContent` varchar(4000) not null comment 'Article Comment Content',
 `ArticleCommentDate` timestamp default CURRENT_TIMESTAMP comment 'Article comment time',
 `ArticleCommentState` int default 1 comment 'status',
 `ArticleRemark` int comment 'Rating',
 `ArticleCommentReserver1` varchar(4000) comment 'Reserve 1',
 `ArticleCommentReserver2` varchar(4000) comment 'Reserve 2',
 primary key (ArticleCommentId)
);

alter table ArticleComment comment 'Article Comments';

/*======================================================================*/
/* Table: ArticleType */
/*======================================================================*/
create table ArticleType
(
 `ArticleTypeId` int not null auto_increment comment 'Article column number',
 `ArticleTypeName` varchar(200) comment 'Article column name',
 `ArticleTypeState` int default 1 comment 'status',
 `ArticleTypeDesc` varchar(4000) comment 'Article column description',
 `ArticleTypePicture` varchar(400) comment 'Article column picture',
 `ArticleTypeReserve1` varchar(4000) comment 'Reserve 1',
 `ArticleTypeReserve2` varchar(4000) comment 'Reserve 2',
 primary key (ArticleTypeId)
);

alter table ArticleType comment 'Article column';

/*======================================================================*/
/* Table: Articles */
/*======================================================================*/
create table Articles
(
 `ArticleId` int not null auto_increment comment 'Article number',
 `ArticleTypeId` int not null comment 'Article column number',
 `ArticleTitle` varchar(400) not null comment 'Article Title',
 `ArticleContent` text comment 'Article content',
 `ArticleDate` timestamp default CURRENT_TIMESTAMP comment 'Article release time',
 `ArticleAuthor` varchar(200) comment 'Article publisher',
 `ArticleFileName` varchar(100) comment 'Static file name',
 `ArticleThumbNail` varchar(200) comment 'Thumbnail image',
 `ArticleAddition` varchar(200) comment 'Attachment name',
 `ArticleLevel` int comment 'display priority',
 `ArticleIsAllowComment` integer default 1 comment 'Is comment allowed',
 `ArticleState` int default 1 comment 'status',
 `ArticleHotCount` int comment 'Number of clicks',
 `ArticleReserve1` varchar(4000) comment 'Reserve 1',
 `ArticleReserve2` varchar(4000) comment 'Reserve 2',
 `ArticleReserve3` numeric(8,0) comment 'Reserve 3',
 primary key (ArticleId)
);

alter table Articles comment 'Articles';

/*======================================================================*/
/* Table: DictSub */
/*======================================================================*/
create table DictSub
(
 `SubId` int not null auto_increment comment 'Sub-item number',
 `DictId` int not null comment 'Dictionary number',
 `SubName` varchar(200) not null comment 'Sub-item name',
 `SubDesc` varchar(4000) comment 'Subitem description',
 `SubReserve1` varchar(4000) comment 'Reserve 1',
 primary key (SubId)
);

alter table DictSub comment 'Dictionary sub-item';

/*======================================================================*/
/* Table: DictTop */
/*======================================================================*/
create table DictTop
(
 `DictId` int not null auto_increment comment 'Dictionary number',
 `DictName` varchar(100) not null comment 'Dictionary name',
 `DictDesc` varchar(4000) comment 'Dictionary description',
 `DictReserve1` varchar(4000) comment 'Reserve for backup',
 primary key (DictId)
);

alter table DictTop comment 'Dictionary';

/*======================================================================*/
/* Table: OrderPdt */
/*======================================================================*/
create table OrderPdt
(
 `OrderPdtId` int not null auto_increment comment 'Order item number',
 `Id` int not null comment 'Number',
 `UserId` int not null comment 'User ID',
 `OrderId` int comment 'Order number',
 `PdtAmount` int comment 'Order quantity',
 `PdtPrice` decimal comment 'unit price',
 `PdtReserve1` varchar(2000) comment 'Reserve 1',
 `PdtReserve2` varchar(4000) comment 'Reserve 2',
 primary key (OrderPdtId)
);

alter table OrderPdt comment 'Order Item';

/*======================================================================*/
/* Table: Orders */
/*======================================================================*/
create table Orders
(
 `OrderId` int not null auto_increment comment 'Order number',
 `AddressId` int not null comment 'Delivery address number',
 `OrderState` int default 1 comment 'Order status',
 `ExpressNO` varchar(50) comment 'Express number',
 `ExpressName` varchar(50) comment 'Express name',
 `PayMoney` decimal comment 'payable',
 `PayedMoney` decimal comment 'paid',
 `SendInfo` varchar(300) comment 'Shipper information',
 `BuyDate` timestamp default CURRENT_TIMESTAMP comment 'Order time',
 `PayDate` datetime comment 'payment time',
 `SendDate` datetime comment 'Shipping time',
 `ReceivDate` datetime comment 'receipt time',
 `OrderMessage` varchar(4000) comment 'PS',
 `UserId` integer comment 'User ID',
 `OrderReserve1` varchar(4000) comment 'Reserve 1',
 `OrderReserve2` varchar(4000) comment 'Reserve 2',
 `OrderReserve3` decimal comment 'Reserve 3',
 primary key (OrderId)
);

alter table Orders comment 'Orders';

/*======================================================================*/
/* Table: ProductComment */
/*======================================================================*/
create table ProductComment
(
 `ProductCommentId` int not null auto_increment comment 'Product Comment Number',
 `ProductId` int not null comment 'Product number',
 `UserId` int not null comment 'User ID',
 `ProductCommentContent` varchar(4000) comment 'Product Comment Content',
 `ProductCommentDate` timestamp default CURRENT_TIMESTAMP comment 'Product Comment Date',
 `ProductCommentState` int comment 'status',
 `ProductCommentRemark` int comment 'Rating',
 `ProductCommentReserve1` varchar(4000) comment 'Reserve 1',
 `ProductCommentReserve2` varchar(4000) comment 'Reserve 2',
 primary key (ProductCommentId)
);

alter table ProductComment comment 'Product Comments';

/*======================================================================*/
/* Table: Products */
/*======================================================================*/
create table Products
(
 `Id` int not null auto_increment comment 'Number',
 `Name` varchar(200) not null comment 'Name',
 `SubIdColor` int not null comment 'color',
 `SubIdBrand` int not null comment 'Brand',
 `SubIdInlay` int not null comment 'belonging mosaic',
 `SubIdMoral` int not null comment 'Meaning',
 `SubIdMaterial` int not null comment 'Type of water',
 `SubIdTopLevel` int not null comment 'First level classification number',
 `MarketPrice` decimal comment 'Market reference price',
 `MyPrice` decimal not null comment 'Yuyuan direct sales price',
 `Discount` decimal default 1 comment 'Discount',
 `Picture` varchar(200) comment 'Picture',
 `Amount` int comment 'Inventory quantity',
 `Description` text comment 'Detailed description',
 `State` int default 1 comment 'State',
 `AddDate` timestamp default CURRENT_TIMESTAMP comment 'Delivery date',
 `Hang` int comment 'Pendant',
 `RawStone` int comment 'Gambling Stone',
 `Size` varchar(200) comment 'Size',
 `ExpressageName` varchar(100) comment 'Expressage name',
 `Expressage` decimal comment 'Expressage fee',
 `AllowComment` int default 1 comment 'Whether to allow comments',
 `Reserve1` varchar(4000) comment 'Reserve 1',
 `Reserve2` varchar(4000) comment 'Reserve 2',
 `Reserve3` decimal(0) comment 'Reserve 3',
 primary key (Id)
);

alter table Products comment 'Products';

/*======================================================================*/
/* Table: Users */
/*======================================================================*/
create table Users
(
 `UserId` int not null auto_increment comment 'User ID',
 `UserName` varchar(200) not null comment 'Username',
 `Password` varchar(512) not null comment 'Password',
 `Email` varchar(100) not null comment 'Email',
 `Sex` varchar(10) comment 'Gender',
 `State` int default 1 comment 'State',
 `RightCode` int comment 'Permission status',
 `RegDate` timestamp default CURRENT_TIMESTAMP comment 'Registration time',
 `RegIP` varchar(200) comment 'Registration IP',
 `LastLoginDate` datetime comment 'Last login time',
 `UserReserve1` varchar(4000) comment 'Reserve 1',
 `UserReserve2` varchar(4000) comment 'Reserve 2',
 `UserReserve3` varchar(4000) comment 'Reserve 3',
 primary key (UserId)
);

alter table Users comment 'Users';

alter table Address add constraint FK_AddressBelongUser foreign key (UserId)
 references Users (UserId) on delete restrict on update restrict;

alter table ArticleComment add constraint FK_ArticleCommentForArticle foreign key (ArticleId)
 references Articles (ArticleId) on delete restrict on update restrict;

alter table ArticleComment add constraint FK_ArticleCommentForUser foreign key (UserId)
 references Users (UserId) on delete restrict on update restrict;

alter table Articles add constraint FK_ArticleBelongType foreign key (ArticleTypeId)
 references ArticleType (ArticleTypeId) on delete restrict on update restrict;

alter table DictSub add constraint FK_BelongDict foreign key (DictId)
 references DictTop (DictId) on delete cascade on update cascade;

alter table OrderPdt add constraint FK_BelongOrder foreign key (OrderId)
 references Orders (OrderId) on delete cascade on update cascade;

alter table OrderPdt add constraint FK_CartForUser foreign key (UserId)
 references Users (UserId) on delete restrict on update restrict;

alter table OrderPdt add constraint FK_OrderDepProduct foreign key (Id)
 references Products (Id) on delete restrict on update restrict;

alter table Orders add constraint FK_OrderBelongAddress foreign key (AddressId)
 references Address (AddressId) on delete restrict on update restrict;

alter table ProductComment add constraint FK_ProductCommentBelongUsers foreign key (UserId)
 references Users (UserId) on delete restrict on update restrict;

alter table ProductComment add constraint FK_ProductCommentForProduct foreign key (ProductId)
 references Products (Id) on delete restrict on update restrict;

alter table Products add constraint FK_BelongBrand foreign key (SubIdMaterial)
 references DictSub (SubId) on delete restrict on update restrict;

alter table Products add constraint FK_BelongColor foreign key (SubIdBrand)
 references DictSub (SubId) on delete restrict on update restrict;

alter table Products add constraint FK_BelongInlay foreign key (SubIdInlay)
 references DictSub (SubId) on delete restrict on update restrict;

alter table Products add constraint FK_BelongMaterial foreign key (SubIdColor)
 references DictSub (SubId) on delete restrict on update restrict;

alter table Products add constraint FK_BelongMoral foreign key (SubIdTopLevel)
 references DictSub (SubId) on delete restrict on update restrict;

alter table Products add constraint FK_BelongTopLevel foreign key (SubIdMoral)
 references DictSub (SubId) on delete restrict on update restrict;