Mysql reorganizes notes -- woods memo, mysql -- woods

Mysql reorganizes notes -- woods memo, mysql -- woods
================================ SQL memo CRUD queries multi-table events and so on ==== ==================================
Example 1. database 1. create database [if not exists] db_name [character set xxx] [collate xxx] * create a database named mydb1. Create database mydb1; * create a mydb2 database using the utf8 character set. Create database mydb2 character set utf8; * create a mydb3 database that uses the utf8 character set and has verification rules. Create database mydb3 character set utf8 collate utf8_bin; 2. view database show databases; view all databases show create database db_name; view database creation method 3. alter database db_name [character set xxx] [collate xxxx] 4. delete database drop database [if exists] db_name; 5. use db_name to switch databases; view the currently used database select database (); Tables 2. Table 1. Create table tab_name (field1 type, field2 type ,... fieldn type) [character set xxx] [collate xxx]; ***** comparison of data types between java and mysql: 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, DateTime, timestamp FileInputStream FileReader ---------- -------------------- Blob Text * creates an employee table employee create table employee (id int primary key auto_increment, name varchar (20), gender bit default 1, birthday date, entry_date date, job varchar (20), salary double, resume text); constraint: primary key unique not null auto_increment the primary key field must be of the numeric type. Foreign key constraint 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 statements for table creation in the current database 3. modify table structure (1) add an alter table tab_name add [column] column name type; (2) modify the alter table tab_name modify column name type; (3) alter column name alter table tab_name change [column] new column name type; (4) delete an alter table tab_name drop [column] column name; (5) modify the rename table name to the new table name. (6) modify the character set alter table student character set utf8 used by the table; 4. delete table drop Table tab_name; partition 3. table Record 1. insert into tab_name (field1, filed2 ,.......) values (value1, value2 ,.......); * The inserted data should be of the same type as the field data. * The data size should be within the specified range of the column. For example, you cannot add a string with a length of 80 to a column with a length of 40. * The data positions listed in values must correspond to the arranged positions of the columns to be added. * Character and date data should be included in single quotes 'zhang' 2013-04-20 '* insert null: do not specify the value of a column or insert into table value (null ), the column is null. * If you want to insert all fields, you can save the write column list and directly write the Value List in the field order in the table insert into tab_name values (value1, value2 ,......); * exercise: Use the insert statement to insert information about three employees into the table. Insert into emp (name, birthday, entry_date, job, salary) values ('zhang fee', '2017-09-09 ', '2017-01-01', 'hitting hands', 1990 ); insert into emp (name, birthday, entry_date, job, salary) values ('guan Yu ', '2017-08-08', '2017-01-01 ', 'fortuna', 1989 ); insert into emp (name, birthday, entry_date, job, salary) values ('zhao yun', '2017-07-07 ', '2017-01-02', 'security', 1991 ); insert into emp values (7, 'huangzhong', 1, '2017-05-05 ', '2017-01-01', 'ares ', 1970, null );
2. Modify the Table Record update tab_name set field1 = value1, field2 = value2,... [where statement] * The UPDATE syntax can use the new value to update columns in the original table rows. * The SET clause indicates the columns to be modified and the values to be given. * The WHERE clause specifies the rows to be updated. If no WHERE clause exists, all rows are updated. * Experiment: Change the salary of all employees to 5000 yuan. Update emp set salary = 5000; change the employee's salary with the name 'zs' to 3000 yuan. Update emp set salary = 3000 where name = 'zs'; change the salary of an employee whose name is 'LS' to 4000 RMB, and change job to ccc. Update emp set salary = 4000, job = 'ccc 'where name = 'zs'; Increase wu's salary by 1000 yuan on the original basis. Update emp set salar = salary + 4000 where name = 'wu'; 3. delete table operation from tab_name [where...] * If the where statement is not followed, delete the data in the entire table * delete can only be used to delete a row of records, but cannot delete a column value in a row of records (this is an update operation ). * The delete statement can only delete the table content, but cannot delete the table itself. to delete the table, use drop *, which is the same as insert and update, deleting records from a table will cause the integrity of the reference of other tables. When modifying database data, never forget this potential problem. * Truncate table can also delete all data in the TABLE. The word sentence first destroys the TABLE and then creates a new TABLE. Data deleted in this way cannot be recovered in the transaction. * Experiment: delete records named 'zs' in the table. Delete from emp where name = 'huangzhong'; delete all records in the table. Delete from emp; Use truncate to delete records in the table. Truncate table emp;
4. select Operation (1) select [distinct] * | field1, field2 ,...... from tab_name where from specifies which table to filter, * indicates to search for all columns. You can also specify a column list to explicitly specify the columns to be searched. distinct is used to remove duplicate rows. * Experiment: query the information of all students in the table. Select * from exam; query the names and English scores of all students in the table. Select name, english from exam; filter duplicate data in the table. Select distinct english from exam; (2) select can also use expressions, and can use the as alias to add 10 points of expertise to all student scores. Select name, english + 10, chinese + 10, math + 10 from exam; calculate the total score of each student. Select name, english + chinese + math from exam; use aliases to indicate the total score of students. Select name, english + chinese + math as total score from exam; select name, english + chinese + math total score from exam; select name english from exam; (3) use where clause, filter and query. * Exercise: select * from exam where name = 'zhang fei' for a student whose name is XXX; select name for a student whose english score is greater than 90, english from exam where english> 90; select name, math + english + chinese as total score from exam where math + english + chinese> 200; * where statements can be used: * comparison operator: >>=<=<> between 10 and 20 values: in (10, 20, 3) between 10 and 20) the value is 10, 20, or 30 like 'zhang pattern' pattern, which can be % or _. If it is %, it indicates any number of characters. In this example, Zhang Sanfeng Zhang feizhang abcd, if it is _, it indicates a character sheet _. Zhang Fei matches. Zhang Is null * logical operators can directly use the logical operators and or not * in multiple conditions to query students whose English scores are between 80 and. Select name, english from exam where english between 80 and 100; query the math scores of 75, 76, and 77. Select name, math from exam where math in (, 77); query the scores of all students surnamed Zhang. Select * from exam where name like 'zhang % '; query mathematics score> 70, Chinese score> 80. Select name from exam where math> 70 and chinese> 80; find the names of students who are absent from mathematics select name from exam where math is null; (4) Order by specifies the column to be sorted, the column to be sorted can be the column name in the table or the alias specified after the select statement. Asc ascending and Desc descending, where asc is the default value. The order by clause should be at the end of the SELECT statement. * Exercise: sort the mathematical scores and output them. Select * from exam order by math; sort the total score in the order from high to low and output select name, (ifnull (math, 0) + ifnull (chinese, 0) + ifnull (english, 0) total score from exam order by total score desc; select name (ifnull (math, 0) + ifnull (chinese, 0) + ifnull (english, 0) total score from exam where name like 'zhang % 'order by total score desc; (5) aggregate function: tip. Don't worry about what the aggregate function will do first, check the required content and then package the aggregate function. Count (column name) is used to count the total number of students in a class? First, find all the students, and then use the count package to select count (*) from exam. How many students have scored more than 70? Select count (math) from exam where math> 70; how many people are there with a total score greater than 250? Select count (name) from exam where (ifnull (math, 0) + ifnull (chinese, 0) + ifnull (english, 0)> 250; sum (column name) count the total score of a class's mathematics? First, find all the mathematical scores, and then use the sum package to select sum (math) from exam; calculate the total scores of a class in Chinese, english, and mathematics subjects select sum (math), sum (english ), sum (chinese) from exam; calculates the sum of the chinese, english, and mathematics scores of A Class. select sum (ifnull (math, 0) + ifnull (chinese, 0) + ifnull (english, 0) as total score from exam; calculate the average chinese score of A Class. select sum (chinese)/count (*) from exam; Note: sum only applies to values; otherwise, an error is returned. AVG (column name): calculate the average mathematical score of a class? First find all the mathematical scores, and then use the avg package. Select avg (ifnull (math, 0) from exam; calculate the average score of a class's total score: select avg (ifnull (math, 0) + ifnull (chinese, 0) + ifnull (english, 0) from exam; Max, Min calculate the highest score and lowest score of the class (the value 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 clause, which can be followed by multiple column names or filtered by group by results with having clause. Exercise: After the items in the order table are classified, the total price of each item in the select product, sum (price) from orders group by product is displayed. Exercise: query the purchased items, and select product, sum (price) from orders group by product having sum (price)> 100 for each type of products with a total price greater than 100 ;! ~ Differences between having and where: where statements are used for filtering before grouping, while having statements are used for filtering After grouping. having can be used for Aggregate functions, and where statements won't work. Having can be used to replace where. Exercise: query the names of items except oranges in the product list with a total price of more than 500 RMB for each category of items: select product, sum (price) from orders where product <> 'orange' group by product having sum (price)> 500;

(7) key points: Select from where group by having order by Mysql execution sequence of SQL statements: from where select group by having order by * analysis: select math + english + chinese as total score from exam where total score> 250; ---- unsuccessful select math + english + chinese as total score from exam having total score> 250; --- successful select math + english + chinese as total score from exam group by total score having total score> 250; ---- successful select math + english + chinese as total score from exam order by total score; ---- select * from exam as result where score successfully. math> 85; ---- success limit 4. constraint 1. specify constraints when creating a table: create table tb (id int primary key auto_increment, name varchar (20) unique not null, ref_id int, foreign key (ref_id) references tb2 (id )); create table tb2 (id int primary key auto_increment );
2. Foreign key constraint: (1) Add foreign key: You can specify a foreign key name. If you do not specify a foreign key name, mysql will automatically create a foreign key name for you. RESTRICT: as long as there is data pointing to the primary table in this table, the relevant records cannot be deleted in the primary table. CASCADE: If a record is deleted from the table pointed to by the foreign key, all records in the table with the same 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 the foreign key alter table Name drop foreign key (case sensitive, the foreign key name can be viewed by the desc table name); 3. primary key constraint: (1) add a primary key (automatically increasing, only the 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) add auto-increment Alter table employee modify id int auto_increment; (4) delete auto-increment Alter table tb modify id int; 5. One-to-one multi-Table Design (311 classrooms and 20130405 classes, both parties are one): Save the one-to-many and many-to-one primary keys of the other on either side (class and student, where the class is 1 and the number of students is multiple): save one's primary key many to many (both teachers and students have multiple sides): use an intermediate table to save the corresponding relationship.

Create table tb (id int primary key, name varchar (20); create table ta (id int primary key, name varchar (20 ), tb_id int); insert into tb values (1, 'Finance authorization'); insert into tb values (2, 'hr authorization'); insert into tb values (3, 'Science and technology authorization '); insert into ta values (1, 'Liu bei ', 1); insert into ta values (2, 'guan Yu', 2); insert Into ta values (3, 'zhangfe', 3); mysql> select * from ta; + ---- + ------ + ------- + | id | name | tb_id | + ---- + ------ + ------- + | 1 | aaa | 1 | 2 | bbb | 2 | 3 | bbb | 4 | + ---- + ------ + ------- + mysql> select * from tb; + ---- + ------ + | id | name | + ---- + ------ + | 1 | xxx | 2 | yyy | 3 | yyy | + ---- + ------ + 1. cartesian Product query: one corresponding record, m records, and n records are queried in two tables. Finally, m * n records are obtained. Many of the error data is select * from ta, tb; mysql> select * fr Om 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 join: query the associated data in both tables., Which is equivalent to filtering the correct results from the Cartesian product results using conditions. Select * from ta, tb where ta. tb_id = tb. id; select * from ta inner join tb on ta. tb_id = tb. id; 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 | yyy | + ---- + ------ + ------- + ---- + ------ + 3. outer join (1) left Outer join: add the result that does not exist on the left side of the outer join: select * from ta left join tb on ta. tb_id = tb. id; mysql> select * from ta left join tb on ta. tb_id = tb. id; + ---- + ------ + ------- + ------ + | id | name | tb_id | id | name | + ---- + ------ + ------- + ------ + | 1 | aaa | 1 | 1 | xxx | 2 | bbb | 2 | yyy | 3 | bbb | 4 | NULL | + ---- + ------ + ------- + ------ + (2) right outer join: select * from ta right join tb on ta. tb_id = tb. id; mysql> select * from ta right join tb on ta. tb_id = tb. id; + ------ + ------- + ---- + ------ + | id | name | tb_id | id | name | + ------ + ------- + ---- + ------ + | 1 | aaa | 1 | 1 | xxx | 2 | bbb | 2 | yyy | NULL | 3 | yyy | + ------ + ------- + ---- + ------ + (3) full outer join: add the result select * from ta full join tb on ta with no result on the left and no on the right based on the internal join. tb_id = tb. id; -- mysql does not support all external connections 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; mysql> 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; -- mysql can use this method to indirectly implement full outer connections + ------ + ------- + ------ + | id | name | tb_id | id | name | + ------ + ------- + ------ + | 1 | aaa | 1 | 1 | xxx | 2 | bbb | 2 | 2 | yyy | 3 | bbb | 4 | NULL | NULL | 3 | yyy | + ------ + ------- + ------ +