MySQL once again to organize notes--woods memo

==============================SQL Memo CRUD queries multi-table events, etc.===============================
--------------------------------------------------------------------------------------------------A. Database   1. Creating database   Create databases [if not EXISTS] db_name [character set XXX] [collate xxx]  * Create a database called MYDB1.    create Database mydb1;  * Create a MYDB2 db using the UTF8 character set.    create Database MYDB2 Character Set utf8;  * Creates a use UTF8 character set. and a MYDB3 database with proofing rules.    create database mydb3 character set UTF8 collate utf8_bin;  2. View databases   show databases; View all databases   s How to create Database db_name; View how the database was created  3. ALTER DATABASE   ALTER DB_NAME [character set XXX] [collate xxxx] 4. Delete database   Drop db [If exists] db_name; 5. Using the database   switch database use db_name;  view the database currently in use select Databases ();------------------------ --------------------------------------------------------------------------ii. table  1. Creating Tables    create Table Tab_name (    field1 type,    field2 type,    ...    FIELDN type    )[Character Set XXX] [Collate xxx];      ****java and MySQL data type comparison     String  ----------------------  char (n) varchar (n) 255  65535    byte short int long float double--------------tinyint  smallint int bigint float double    boolean------------------bit 0/1    Date------------------date, time, Dateti Me, timestamp    fileinputstream filereader  ------------------------------Blob text        Creating an employee table employee    CREATE TABLE employee (     id int primary key auto_increment, & nbsp    name varchar (,     gender bit default 1,     birthday date,    &NB Sp;entry_date date,     job varchar,     salary double,     resume text    );       constraints:     primary key     unique    &NBSP ; not null    auto_increment The primary key field must be a numeric type.       FOREIGN KEY constraints   2. View table information    DESC tab_name view table structure    show tables View all tables in the current database    show CREATE TABLE Tab_name view current database table Build table statements   3. ALTER TABLE structure     (1) Add a column      alter table Tab_name A dd [Column] column name type;    (2) Change column type      alter table tab_name Modify column name Type;    (3) Change column name      alter table Tab_name change [column] columns name new column name Type;    (4) Delete a column      alter table Tab_name Drop [column] column name;    (5) Change table name      RENAME table name to new table name;    (6) The character set used to modify the table      alter table Student Character Set utf8;        4. Deleting tables     DROP table tab_name;&nbsp ;     -------------------------------------------------------------------------------------------- ------Third, the table records  1. Add a record insert  insert into Tab_name (field1,filed2,.......) VALUES (value1,value2,.......);   *The data inserted should be the same as the data type of the field.   * The size of the data should be within the specified range of columns, such as: You cannot add a string of length 80 to a column of length 40.

* The data location listed in values must correspond to the arranged position of the added column.

* Character and date data should be included in the single quote ' Zhang ' 2013-04-20 ' * insert null: Do not specify a value for a column or insert into table value (NULL). Then the column takes a null value.

* Suppose you want to insert all the fields to save the column list, insert into tab_name values (value1,value2,......) directly by the list of fields in the table.  * Exercise: Use the INSERT statement to insert information for three employees into a table.  INSERT into EMP (name,birthday,entry_date,job,salary) VALUES (' Zhang Fei ', ' 1990-09-09 ', ' 2000-01-01 ', ' thugs ', 999);  INSERT into EMP (name,birthday,entry_date,job,salary) VALUES (' Guan Yu ', ' 1989-08-08 ', ' 2000-01-01 ', ' Fortune ', 9999);  INSERT into EMP (name,birthday,entry_date,job,salary) VALUES (' Zhao Yun ', ' 1991-07-07 ', ' 2000-01-02 ', ' security ', 888); INSERT into EMP values (7, ' Jack Huang ', 1, ' 1970-05-05 ', ' 2001-01-01 ', ' Ares ', 1000,null);
2. Change table record update tab_name set field1=value1,field2=value2,......   The [WHERE statement] *update syntax allows you to update the columns in the original table row with the new values.   The *set clause indicates which columns to change and which values to give. The *where clause specifies which rows should be updated.

If there is no WHERE clause, all rows are updated.

* Experiment: Change the salary of all employees to 5000 yuan.    Update EMP set salary=5000; The salary of the employee named "Zs" was changed to $3000.

Update emp Set salary=3000 where name= ' ZS ';    Change the employee's salary of the name ' ls ' to 4000 yuan, the job changed to CCC.    Update emp Set salary=4000,job= ' CCC ' where name= ' ZS '; Add Wu's salary to 1000 yuan on the original basis.

Update emp Set salar=salary+4000 where Name= ' Wu ';   3. Delete Table operation delete from Tab_name [where ...]   * Assuming that the data in the entire table is deleted without a where statement *delete can only be used to delete a row of records, and the value of a column in a row of records cannot be deleted (this is an update operation). The *delete statement can only delete the contents of a table. You cannot delete the table itself, you want to delete the table, with drop * Same as INSERT and update, deleting records from one table will cause the other table to be subject to integrity issues when changing database data.   Never forget this potential problem in your mind. *truncate table is also able to delete all the data in the table, the word sentence first destroys the table, and then creates a new table.   Data that is deleted in this manner cannot be recovered in a transaction.    * Experiment: Delete records with Name ' ZS ' in the table.    Delete from emp where name= ' Jack Huang ';    Deletes all records in the table.    Delete from EMP;    Use truncate to delete records in a table. TRUNCATE TABLE EMP;
4.select operation (1) SELECT [DISTINCT] *|field1,field2, .... From the tab_name from which table is filtered, * means to find all columns, or to specify a column column indicating the column to be found by white.    Distinct is used to reject repeated lines.     * Experiment: Check the information of all the students in the table.     SELECT * from exam;     The names of all the students in the enquiry form and the corresponding English scores.     Select Name,english from exam;     Filter the repeating data in the table.  SELECT distinct 中文版 from exam;     (2) Select is also able to use expressions, and can use an as alias to add 10 extra-long points to all student scores.     Select name,english+10,chinese+10,math+10 from exam;     Count each student's total score.     Select Name,english+chinese+math from exam; Use aliases to represent student totals.

Select Name,english+chinese+math as total from exam;     Select Name,english+chinese+math total from exam;  Select name 中文版 from exam; (3) Use the WHERE clause to filter the query.

      * Practice:      Query student results      select * from exam where name= ' Zhang Fei ';  &n Bsp   Query students who score more than 90 in English      select name,english from exam where english>90;      Query all students with total score greater than 200 points      select Name,math+english+chinese as total score from exam where math+english+chinese>200;      *where words can use:       * comparison operator:          > < >= &lt = <>            between and 20 values from 10 to 20            The In (10,20,3) value is either 10 or 20 or 30          like ' pattern ' pattern can be either% or _, assuming that% is a random multi-character, in this case the Zhang Sanfeng Zhang abcd. The assumption is that _ is a character-Zhang _. Zhang Fei is in accord.           is null                * logic operator   &NBSP ;     The ability to use logical operators and or not     * experiments       query English scores between 80-100 students in multiple conditions.        selectName, 中文版 from exam where 中文版 between and 100;      query math score for 75,76,77 classmates.

Select name, math from exam where math in (75,76,77);      Check the student scores for all surnamed Zhang.      SELECT * from exam where name is like ' Zhang% '; Query Math >70, the language of >80 students.

Select name from exam where math>70 and Chinese >80;  Find the name of the student missing math select name from exam where math is null;     (4) Order by specifies the sorted column, which is the column name in the table, and can be the alias specified after the SELECT statement.    ASC Ascending, desc Descending, where ASC is the default value the ORDER BY clause should be at the end of the SELECT statement. * Exercise: Sort the math scores after the output.

     select * from exam ORDER by math;      sorting totals by high to low sequential output      select name, ( Ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) Total score from exam order by total desc;      Sort output of students with Zhang surname nbsp    select name, (Ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) total in exam where name like ' sheet% ' order by Total desc;  (5) aggregate function: technique, do not tube the aggregation function to do first, first the content of the requirements to find out and then wrap the aggregation function.     count (column name)       Statistics How many students does a class share? Find out all the students first, and then use the Count package on       SELECT COUNT (*) from exam;      statistical math scores more than 70 of the number of students?       SELECT count (math) from exam where math>70;      statistics total score greater than 250 how many people?       SELECT COUNT (name) from exam where (Ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) >250;& nbsp   SUM (column name)       Statistics A class math total? Find out all the math results first, then use the sum package       Select sum (math) from exam;      Statistics A class of Chinese, English, mathematics, the total score       Select SUM (mATH), sum (中文版), sum (Chinese) from exam;      Statistics A class of Chinese, English, and math scores total       Select sum (ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) as total score from exam;      Statistics one class Chinese score average       & Nbsp;select sum (Chinese)/count (*) from exam;      NOTE: Sum works only on numeric values, otherwise it will be an error.

AVG (column name): Ask for a class math average score? Find out the full number of credits first and then use AVG package.

Select AVG (ifnull (math,0)) from exam;   Ask for a class score average of select AVG ((Ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) from exam;      Max, Min asks for class highest and lowest (numeric range is particularly useful in statistics) Select Max ((Ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) from exam;   Select Min ((Ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0))) from exam; (6) Group by sentence. It can then be followed by multiple column names, and the result of group by can be filtered with the HAVING clause.

Exercise: After classifying items in the order form, display the total price of each type of item select Product,sum from the orders group by product; Exercise: Search for items that have purchased several categories of goods, and select product for each category with a total price greater than 100.    SUM (price) from the orders group by product have sum (price) >100; The difference between!~having and where: the where statement is used before grouping the filter. Having the filter after grouping, having the ability to use the aggregate function, where is not.       Use where where can be substituted. Exercise: The name of the item in the list of items other than oranges, the total price of each type of commodity is greater than 500 yuan. Select product,sum from orders where product<> ' Orange ' GROUP by P Roduct has sum (price) >500;

  (7) Focus: Select from where the group by has order by      MySQL run order when running SQL statements: from where Select GROUP by Having order by    * Analysis:     select Math+english+chinese as total scores from exam where total >250; ----unsuccessful      select Math+english+chinese as total scores from exam have total >250; ---success      select Math+english+chinese as total scores from exam group by total total having total scores >250; ----Success      select  math+english+chinese as total from exam order by total,----success      select * from exam as score where score .math>85; ----Success       -------------------------------------------------------------------------------- ------------------constraints   1. Specifying constraints when creating tables:   create Table TB (    ID int primary KEY auto_ increment,    name varchar (a) unique not null,    ref_id int,     foreign key (ref_id) References TB2 (ID)    );     &Nbsp;create table TB2 (    ID int primary key auto_increment   );   
2. FOREIGN KEY constraint: (1) Add foreign key: be able to understand the name of the specified foreign key, assuming that the name of the foreign key is not specified, MySQL will voluntarily create a foreign key name for you.

    RESTRICT: Only the data in this table that points to the main table cannot be deleted from the main table.     CASCADE: Suppose you delete a record from the table that foreign key points to. Then all the records in this table, like that key, will be deleted together.     ALTER TABLE book add [constraint Fk_book] foreign key (pubid) references pub_com (ID) [on delete restrict] [on Update restrict];       (2) Delete foreign key     ALTER TABLE name drop FOREIGN key foreign key (distinguishes between uppercase and lowercase.) Foreign key name can be viewed in DESC table name);       3. PRIMARY KEY constraint:    (1) Add primary key (self-growth, only primary key can grow voluntarily)     Alter Table TB Add primary key (ID) [auto_increment];    (2) Remove primary key     ALTER TABLE name drop primary key  &nbs p; (3) Add yourself active growth     ALTER TABLE employee modify ID int auto_increment;    (4) Delete yourself active growth     Alter t Able TB Modify ID int;   -------------------------------------------------------------------------- ------------------------Five, multi-table design   One to two (311 classrooms and 20130405 classes, both of them are a): In the casual side of the save there is also a party's primary key   one-to-many, many-to-a (class and student. The class is 1, the student is more): in many of the parties to save one side of the primary key   Many-to-many (teachers and students, both sides are more): Using the intermediate table,Save the appropriate relationship

--------------------------------------------------------------------------------------------------six, multi-table query   CREATE table TB (ID int primary key,name varchar);  CREATE TABLE TA (    ID int primary key,  &NB Sp Name varchar (,    tb_id int         );  INSERT into TB values (1, ' financial Department ');  INSERT into TB values (2, ' hr ');  INSERT into TB values (3, ' Science Department ');   insert INTO TA values (1, ' Liu Bei ', 1);  INSERT INTO TA values (2, ' Guan Yu ', 2);  insert INTO TA values (3, ' Zhang Fei ', 3);    mysql> select * from TA;&NB Sp  +----+------+-------+   | ID | name | tb_id |   +----+------+-------+   |  1 | AAA  |     1 |   |  2 | BBB  |     2 |   |  3 | BBB  |     4 |   +----+------+-------+  mysql> select * from tb;   +----+------+  &NB sp;| ID | Name |   +----+------+   |  1 | XXX  |   |  2 | YYY  |   |  3 | YYY  |   +----+------+   1. Cartesian product query: a corresponding record in two tables. M Records and N Records query, finally get m*n record, among very many error data    select * from TA,tb;    mysql> select * from TA, tb;&nbsp ;   +----+------+-------+----+------+    | ID | name | tb_id | ID | Name |    +----+------+-------+----+------+    |  1 | AAA  |     1 |  1 | XXX  |    |  2 | BBB  |     2 |  1 | XXX  |    |  3 | BBB  |     4 |  1 | XXX  |    |  1 | AAA  |     1 |  2 | yyy  |    |  2 | BBB  |     2 |  2 | yyy  |    |  3 | BBB  |     4 |  2 | yyy  |    |  1 | AAA  |     1 |  3 | yyy  |    |  2| BBB  |     2 |  3 | yyy  |    |  3 | BBB  |     4 |  3 | YYY  |    +----+------+-------+----+------+  2. Internal connection: Query the correlation data in both tables, which is equivalent to using conditions to filter out the correct results from the Cartesian product results.

   select * from TA, tb where ta.tb_id = tb.id;   select * from TA inner join TB on ta.tb_id = tb.id;& nbsp;   mysql> select * from TA inner join TB on ta.tb_id = tb.id;    +----+------+-------+----+--- ---+    | ID | name | tb_id | ID | Name |    +----+------+-------+----+------+    |  1 | AAA  |     1 |  1 | XXX  |    |  2 | BBB  |     2 |  2 | YYY  |    +----+------+-------+----+------+  3. Outer connection     (1) Left outer connection: Add a result on the left with no right on the base of the inner join    select * from-ta left-join TB on ta.tb_id = tb.id;      mysql> select * from-Ta left J Oin tb on ta.tb_id = tb.id;    +----+------+-------+------+------+    | ID | name | tb_id | ID   | Name |    +----+------+-------+------+------+    |  1 | AAA  |     1 |    1 | XXX  |    |  2 | BBB  |     2 |   &NBSP;2 | yyy  |    |  3 | BBB  |     4 | NULL | NULL |    +----+------+-------+------+------+    (2) Right outer join: Add a result on the right side of the inner join     SELECT * FROM TA Right join TB on ta.tb_id = tb.id;       mysql> select * from TA right join TB on ta.tb_i D = tb.id;    +------+------+-------+----+------+    | ID   | name | tb_id | ID | Name |    +------+------+-------+----+------+    |    1 | AAA  |     1 |  1 | XXX  |    |   &NBSP;2 | BBB  |     2 |  2 | yyy  |    | NULL | NULL |  null |  3 | YYY  |    +------+------+-------+----+------+       (3) Full outer connection: Add on the basis of the inner connection to the left there is no right and there is no left side of the result     SELECT * from TA full join TB on ta.tb_id = tb.id; --mysql does not support full outer connection     SELECT * from TA left join TB on ta.tb_id = tb.id    union    SELECT * from TA right join TB on ta.tb_id = tb.id;     &nb Sp Mysql> select * from TA left join TB on ta.tb_id = tb.id        union      &NB Sp -select * from TA right join TB on ta.tb_id = tb.id; --mysql can use this method to indirectly implement full-outer connection     +------+------+-------+------+------+    | ID   | name | tb_id | ID   | Name |    +------+------+-------+------+------+    |    1 | AAA  |     1 |    1 | XXX  |    |   &NBSP;2 | BBB  |     2 |   &NBSP;2 | yyy  |    |    3 | BBB  |     4 | NULL | NULL |    | NULL | NULL |  null |    3 | YYY  |    +------+------+-------+------+------+

MySQL once again to organize notes--woods memo