Head First SQL note

Look at the time summed up a bit, as follows:

Chapter 1:

CREATE database database_name; using database use database_name; creating Tables Crate Table table_name (col2 var_type, col1 var_type not NUL L DEFAULT xxx, delete tables drop table table_name; SHOW Table DESC table_name;describe table_name; inserting data INSERT INTO table_name (col1, col2) VALUES (Col1_val, Col2_val) and other forms of inserting data are not listed. Chapter 2:InquireSELECT * FROM table_name [WHERE col1 =Xxxxand col2 =yyyy] SELECT col1, col2 col3 from table_name [WHERE col1 =Xxxx]; SELECT col1, col2 col3 from table_name [WHERE col1 is NULL]; SELECT col1, col2 col3 from table_name [WHERE col1 was not NULL]; 

SELECT col1, col2 col3 from table_name [WHERE col1 like xxxx];

SELECT col1, col2 col3 from table_name [WHERE isn't col1 likeXxxx]; SELECT col1, col2 col3 from table_name [WHERE col1 betweenXxx andyyy];SELECT col1, col2 col3 from table_name [WHERE not col1 between XXX andyyy]; SELECT col1, col2 col3 from table_name [WHERE col1 in (Xxx, yyy, ZZZ)];SELECT col1, col2 col3 from table_name [WHERE col1 not in (XXX, yyy, ZZZ)]; SELECT col1, col2 col3 from table_name [WHERE isn't col1 likeXxxxAnd not col2 =yyyy];The comparison operator:=>>=<<=<> is not equal to a wildcard character:% Match any number of characters_ Match any character string escape character: \ ' ' above two denotes single quotation marks for strings, do not use double quotes, double quotes are used in PHP to represent SQL statements using Chapter 3:Delete delete operation delete from Table_name;delete from table_name WHERE col1 = xxxx; The use of the where statement is the same as in the SELECT statement Update table_name SET col1 = xxxx, col2 = yyyy [WHERE col1 = zzzz] The use of the WHERE statement with the SELECT statement In the same Chapter 4:1NF: Each column must be atomic, and the first row must have a primary key. There is no duplicate type value in the table [cannot have an array]. Show command, usage please oneself realize: show CREATE TABLE table_name; SHOW CREATE DATABASE db_name; SHOW COLUMNS from table_name; SHOW INDEX from table_name; SHOW WARNINGS; Specifies the primary key (not NULL is not required, but the primary key plus this restriction is expected): CREATE TABLE table_name (col1 col1_type NOT NULL, col2 Col2_type, PRIMARY K EY (col1)); Specify auto increment: CREATE TABLE table_name (col1 col1_type not NULL auto_increment, col2 col2_type, PRIMARY KEY (col1)); Only one column per table can be defined as Auto_increment Chapter 5:Alter ALTER TABLE name change table: Alter TABLE_NAME1 RENAME to Table_name2; Add a column: ALTER TABLE table_name Add column col_name col_type first; specified location can also be used: SECOND, after Col_name, before col_name add primary key: ALTE R TABLE table_name ADD PRIMARY KEY (col_name); Modify the column: ALTER TABLE table_name Change column orig_name, new_name not NULL auto_increment; (changes used to redefine a column, So the latter part is the same as redefining a column, changing only the data type, without changing the column name: ALTER TABLE table_name MODIFY col_name new_type; Delete Column: ALTER TABLE table_name DROP column col_name; Some string-handling functions: Left (Col_name, Count)-Right (Col_name, Count) SUBSTRING (Col_name, FS, count) returns all characters before the count of FS Upper (Col_ Name) LOWER (col_name) REVERSE (col_name) LTRIM (col_name) Delete leftmost blank RTrim (col_name) LENGTH (col_name) where Col_ Name can be changed directly to the string alter cannot directly change the order of the columns, it is necessary to delete the previous column before inserting the column into the desired position. Chapter 6:Case:update table_name SET col_name=when Case col1 = Val1 then val11 case col2 = Val2 then val22 case col3 = Val3 then Val Val_defaultend ELSE; Order By:select col1, col2 WHERE col3 = Xxxxorder by col1 DESC, col2 ASC, sort by col1, then col2, or just one column, you can specify ascending order (ASC, Ascendi NG) or Descending (DESC, descending) numeric processing function: SUM (col_name) AVG (col_name) MIN (col_name) MAX (col_name) COUNT (col_name) These functions ignore the presence of NULL in the grouping: Group by col_name: DISTINCT col_name limits: limit N; LIMIT m, n; n from the beginning of the first m Chapter 7:FOREIGN key: A table with a foreign key that references a primary key that is called a parent key foreign key is a value that is called a foreign key of the parent table can be null to create a table with a foreign key: CREATE TABLE table_name (col1 type,col2 type,constraint parent_table Parent_col_nameforeign KEY (col2) REFERENCE parent_table (parent_col_name)); Other constraints: Uniquecheck (MySQL does not support) handling cases that violate the first paradigm by using a table to handle many-to-many situations by constructing an intermediate table key combination: A primary key consisting of more than two columns.2NF: On the basis of 1NF, there is no partial function dependency. Already conform to the 1NF table, if there is only one primary key, it certainly conforms to 2NF.3NF: On a 2NF basis, there is no transfer function dependency. (transitive function dependency refers to a dependency between non-key columns.) ) Chapter 8:As specified alias: SELECT col_name as new_name; Specify column alias Select Col_name from table_name as new_name; Specifies the table alias where the As can also be omitted, note that the alias and the original name cannot be separated by commas innter join: There are four kinds of inner joins, cross join, Qeuijoin, Non-qeuijoin, Natural-joinselect Table1.col1, table2.col2 from table1 cross JOIN table2; Where cross joins can replace select Table1.col1 with commas, table2.col2 from table1 INNER joins table2 on some_condition;equijoin equal connections: Select Ta Ble1.col1, table2.col2 from table1 INNER joins table2 on table1.coln = Table2.colm;non-equijoin unequal connections: SELECT table1.col1, tab Le2.col2 from table1 INNER joins table2 on Table1.coln <> Table2.colm;natural-join natural connections (available when two tables have the same column): SELECT table1 . col1, table2.col2 from table1 NATURAL JOIN table2; Equal connections and on in unequal connections can also work as in where Chapter 9:Select Col_name from table_name whre col_name in (SELECT col_name2 from table_name2); Other subquery keywords also have not in, EXISTS, not EXISTS , =, <> subqueries can also be used for selecting columns in select Col1, col2, (sub_query) ...; A non-correlated subquery refers to a subquery that can run independently of the outer query, with the opposite meaning of the associated subquery. Using non-associative subqueries as much as possible can speed up the query. In general, subqueries should return only a single column of results, which can be returned when used in, not, and EXISTS, not EXISTS. Chapter:Left OUTER join: SELECT table1.col1, table2.col2 from table1 left OUTER JOIN table2, and each row in the right table is compared to each row in the list in the form, and when matched, a row is added to the result. If a row in the left table does not match all the rows in the right table, a row is also output, but there is null in the result. The first row in the left table will appear at least once in the output result. The right outer join is just the direction of the join changing from right to left, which is essentially the same as the left outer join. You can use only one outer join mode in your work. Self-referencing foreign key: a column that references the primary key in the same sheet. Join yourself (in the same way as other inner joins, just write yourself on the right-hand side, but typically have two different aliases for the same key): SELECT T1.col_name, t2.col_name from table1 as T1 INTER JOIN Table2 as T2 on condition; UNION (combining different search results): SELECT col_name from table1 unionselect col_name from table2 unionselect col_name from Table3order by Col This command is automatically _name, and if you want to keep duplicate results, you can use the union ALL command. The Union command requires that the number of columns to be searched must be the same, with the same column type.  The order between the SEL does not affect the result, even if no ORDER by command is added. Intersect and Except:intersect are used to take two times the intersection of Select, that is, two of the parts. The except is used to take two times the difference set of a select, that is, in one result but not in the other part of the result. These two commands are not supported by MySQL, so they are not written in any specific form. In the Select command, basically a subquery can use joins to code. However, the subquery can also be used in the update INSERT Delete command. CREATE table with the result of select: "CREATE TABLE as SELECT ...; Chapter:Check constraint: Used to ensure that the columns inserted into the database meet certain conditions (not supported by MySQL, so do not write in concrete form);  CREATE view: Creating views view_name as select ...;   The command to create a new table with the result of select is similar, in fact, the created view can also be used as a new table, you can select any column from it using the SELECT statement, and the syntax is exactly the same as the normal select: select * from View_name; The view_name section is replaced with the Select command when creating the view as a subquery in this SELECT statement. The   view can also be used in update, INSERT, delete commands, but it is generally not recommended. But a hack feature is worth a try, which is to use the view's check option to mimic the check constraints of other databases.  create View view_name as SELECT col1, col2 from table_name WHERE condition with check OPTION, and subsequent insert operations on this view will check Whether the condition condition is satisfied.   Delete a view: Drop view view_name;  uses show TABLES; command to view all tables and views in the current database.   When a table appears in the Create command for a view, the table cannot be deleted if the view is not deleted.  transcation: A transaction is a set of SQL commands that are either executed or not executed, that is, atomic. And transactions can be rolled back before committing, as if none of these statements were executed.  start transaction;sql command;sql command; Rollback; start transaction;sql command;sql command; commit;  using rollback will revoke the start transcation, and all subsequent statements. Using the commit command commits the statement atomicity that follows the start transcation command to the database.  mysql, only the BDB and InnoDB engines support transactions. Transactions are implemented using logs, and transactions are present in the log until rollback or commit is executed.   Chapter:Set root password: Set PASSWORD for ' root ' @ ' hostname ' = PASSWORD (' New_password '); Create user user_name identified by ' password '; users created in this way do not have any permissions, that is to say, nothing can be done. Grand:grand XXX on table_name to USER_NAME1, username2; GRAND XXX on table_name to user_name with GRANT OPTION; The user can also grant this permission to other people xxx can be update, INSERT, DELETE, SELECT; For SELECT permissions, it can also be written as select (Col_name), which means that only col_name columns can be viewed. Revoke:revoke XXX on table_name from username [CASCADE]; if username has Grant OPTION and has granted permission to someone else, this command will also revoke this permission granted to others. If you use the Restrict option instead of the CASCADE option, you will get an error in the above scenario. You can also revoke permissions only if you grant the permission: REVOKE Grant OPTION on the table_name from username; the side effect is that if the user has granted this permission to someone else, it will take back the permission that he granted to others. You can use the wildcard character: Grant Select on database_name.* to user_name; All tables in a database grant SELECT On *. * to user_name; All tables in all databases can create roles, grant permissions to roles, and Give the role to the user, MySQL does not support the role, so here is not specifically written.

Head First SQL note