==============================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 UTF8 with the mydb3 character set, 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. modifying databases ALTER DATABASE db_name [character set XXX] [collate xxxx] 4. Deleting databases DROP database [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. Modify table Structure (1) Add a column alter table Tab_name A dd [Column] column name type; (2) Modify a column type alter table tab_name Modify column name Type; (3) Modify 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) Modify table name  RENAME table name to new table name; (6) The character set used to repair the table alter table Student Character Set utf8; 4. Deleting tables DROP table tab_name;  ; -------------------------------------------------------------------------------------------- ------Third, the table records 1. Add a record insert insert into Tab_name (field1,filed2,.......) VALUES (value1,value2,.......); *The data you insert should be the same as the data type of the field. * The size of the data should be within the specified range of columns, for example: a string of length 80 cannot be added to a column of length 40. * The data location listed in values must correspond to the arranged position of the column being joined. * Character and date data should be included in single quotes ' Zhang ' 2013-04-20 ' * insert null value: Do not specify a value for a column or insert into table value (NULL), the column takes a null value. * If you want to insert all the fields, you can save the column list by inserting into tab_name values (value1,value2,......) directly into the list of field orders in the table. * Exercise: Use INSERT statements 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 ', ' Fortuna ', 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. modifying table records 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 modify 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: Revise all employees ' salary to 5000 yuan. UPDATE EMP set salary=5000; Change the salary of employees named ' Zs ' to $3000. UPDATE emp set salary=3000 where name= ' ZS '; employee's salary with name ' ls ' changed to $4000, 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_nam e [where ....] * If you do not follow the WHERE statement to delete data from the entire table *delete can only be used to delete a row of records, you cannot delete a column value in a row of records (this is an update operation). *delete statement can only delete the contents of the table, cannot delete the table itself, want to delete the table, with drop * same as insert and update, delete records from one table will cause referential integrity problems of other tables, When you modify database data, you should never forget this potential problem in your mind. *truncate table can also 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. * Experimental: Delete records with Name ' ZS ' in the table. DELETE from emp where name= ' Jack Huang '; Delete all records in the table. DELETE from emp; use truncate to remove records from a table. TRUNCATE TABLE emp;
4.select operation (1) SELECT [DISTINCT] *|field1,field2, ... from Tab_name where from specifies which table to filter from, * means to find all columns, You can also specify a column list to explicitly specify which columns to look for, distinct to reject duplicate rows. * Experiment: Query The information of all students in the table. select * from exam; Query the names of all students in the table and the corresponding English scores. select name,english from exam; filter repeating data in a table. select distinct 中文版 from exam; (2) Select can also use an expression, and you can use the AS alias Add 1 to all student scores 0 points of extra-long display. 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: Search for student results with name XXX select * from exam whereName= ' Zhang Fei '; query students with English score greater than 90 select name,english from exam where english>90; Query all students with a total score greater than 200 select Name,math+english+chinese as score from exam where Math+engli sh+chinese>200; *where words can be used: * comparison operators: > < >= <= <> between and 20 values between 10 and 20 &N Bsp The in (10,20,3) value is 10 or 20 or 30 like ' pattern ' pattern can be% or _, and if it is% it means any number of characters , in this case, Zhang Zhang Sanfeng Zhang Abcd, if _ is a character Zhang _, Zhang Fei in line. is null * logic operator &NBSP ; in multiple conditions directly you can use the logical operator and or not * experiment query English score between 80-100 students. select name, 中文版 from exam where 中文版 between and 100; query Math score is 75, 76,77 's classmates. Select name, math from exam where math in (75,76,77); Query all student grades for Zhang. SELECT * from exam where name like ' Zhang% '; query Math >70, language points >80 classmates. Select name from exam where math>70 and Chinese >80; find students with missing math names &NB Sp SELECT name from exam where math was null; (4) Order by specifies the sorted column, which can be the column name in the table, or the alias specified after the SELECT statement. &NBSP;ASC Ascending, Desc descending, where ASC is the default value order by clause should be at the end of the SELECT statement. * Practice: Sort math results after 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: Skill, do not control the aggregation function to do, first to find out the contents of the requirements and then packAggregation functions. count (column name) Statistics How many students are there in a class? Find out all the students first, 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 (Chinese), sum (Chinese) from exam; Statistics A class language, English, math score sum Select SUM (ifnull (math,0) +ifnull (chinese,0) +ifnull (english,0)) as total from exam; Statistics a class Chinese score average select sum (Chinese)/count (*) from exam; NOTE: Sum works only on numeric values, otherwise it will report an error. &NBSP;AVG (column name): Ask for a class math average score? Find out all the credits first and then use the AVG package. selECT avg (ifnull (math,0)) from exam; Ask for a class total score average select avg ((Ifnull (math,0) + Ifnull (chinese,0) +ifnull (english,0))) from exam; max, min for class highest and lowest score (numerical 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 the exam; (6) Group by sentence, which can then be followed by multiple column names, You can also filter the results of group by with the HAVING clause. Exercise: Display the total price of each category of items after sorting the items in the Orders table select product,sum (prices) from Orders GROUP by pro duct; Exercise: Query purchases of several categories of goods, and each category of goods greater than 100 Select Product,sum (price) from orders GR OUP by product has sum (price) >100; !~having and where differences: where statements are filtered before grouping, having a filter after grouping, You can use the aggregate function in the having, not in where. Where can be substituted with a having. Practice: Check the product list for items other than oranges, the names of items with a total price greater than $500 for each category Select ProducT,sum (price) from orders where product<> ' Orange ' GROUP by product have sum (price) >500;
(7) Focus: Select from where the group by has order by MySQL execution order when executing 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 Constraints: (1) Add foreign key: You can explicitly specify the name of the foreign key, and if you do not specify a foreign key name, MySQL automatically creates a foreign key name for you. RESTRICT: As long as this table contains data pointing to the main table, it is not possible to delete related records in the main table. CASCADE: If you delete a record from the table that foreign key points to, all the records in the table that are the same as 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 (case-sensitive, foreign key name can be viewed in DESC table name); 3. PRIMARY KEY constraint: (1) Increase primary KEY (auto-grow, only primary key can automatically grow) Alter table TB Add primary key (ID) [auto_increment]; (2) Delete primary key ALTER TABLE name drop primary key (3) Increase auto growth &NBSP ; Alter table employee Modify ID int auto_increment; (4) Delete auto grow Alter table TB Modify ID int; & nbsp; --------------------------------------------------------------------------------------------------Five , multi-table design One-to-two (311 classrooms and 20130405 classes, both are a): Save the other party's master on either sideKey One-to-many, multi-pair (class and student, of which class is 1, 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 corresponding 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, ' Finance 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: Two tables in a corresponding record, M Records and N Records query, finally get M*n records, many of the error data select * from Ta,tb; mysql> select * from TA,tb; +----+------+-------+----+----- -+ | 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 side on the basis of the inner connection 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 connection: 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 | &nbsP +------+------+-------+----+------+ (3) Full outer connection: Add the left side to the right without a result on the right side of the inner connection 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 * Fro M TA right join TB on ta.tb_id = tb.id; mysql> select * from TA left join TB on ta.tb_id = TB .id union SELECT * from TA right join TB on ta.tb_id = t b.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 re-organize notes--woods memo
