Summary of Mysql table, column, database addition, deletion, modification and query problems

#Create a database CREATE DATABASE mytest CHARACTER SET gbk
# DROP DATABASE mytest
Table operations#Create table (create table table name (columns))
CREATE TABLE students(
id INT PRIMARY KEY,
NAME CHAR(10) NOT NULL,
sex CHAR(4) NOT NULL
);
#Delete table (drop table table name)
DROP TABLE students
#Insert data into the table (insert into table name (attribute) values ​​(corresponding value))
INSERT INTO students(id,NAME,sex) VALUES(1,"张三","男")
#Update table data (update table name set key = value, key = value where condition) Use "," to separate the middle, and other invalid sets only need to write one UPDATE students SET id=2 , NAME="a" WHERE id=1
#Delete data in the table (delete from table name where condition)
DELETE FROM students WHERE id=0
#View the data in the table (select the query from table name where condition)
SELECT * FROM students WHERE id=1
SELECT * FROM students ORDER BY age DESC (order by ascending, order by column name desc descending)
Column#Add column (alter table table name add column name field type)
ALTER TABLE students ADD tel CHAR(20)
ALTER TABLE students ADD address CHAR(50) AFTER sex
#Delete columns (alter table table name drop column name)
ALTER TABLE students DROP address
#Modify column properties (alter table table name change column name to be modified modified column name new column name field type)
ALTER TABLE stu CHANGE telphone tel CHAR(20) DEFAULT "-"
ALTER TABLE students CHANGE tel
ALTER TABLE students RENAME stu
Simple function SELECT SUM(age) AS "total age" FROM students
SELECT AVG(age) AS "Average age" FROM students
SELECT MAX(age) AS "Maximum age" FROM students
SELECT COUNT(id) AS "Number" FROM students (Select the primary key to count the number of people, otherwise the nullable column will affect the result)
Grouping table contents:
2005-05-09 wins 2005-05-09 wins 2005-05-09 loses 2005-05-09 loses 2005-05-10 wins 2005-05-10 loses 2005-05-10 loses If you want to generate the following results, how do you write the SQL statement?
      Win or Lose 2005-05-09 2 2
2005-05-10 1 2
SELECT DATA AS " ",SUM(result='Win') AS "Win",SUM(result='Lose') AS "Lose" FROM test1 GROUP BY DATA Case when(case attribute="" then "" end)
      Write out the SQL statement to get table3 from table1.table2: SELECT t1.department dep,SUM(CASE WHEN month mon='January' THEN performance yj ELSE NULL END) AS 'January',
SUM(CASE WHEN month mon='February' THEN performance yj ELSE NULL END) AS 'February',
SUM(CASE WHEN month mon='March' THEN performance yj ELSE NULL END) AS 'March' FROM table1 t1 LEFT JOIN table2 t2 ON t1.department dep=t2.department dep GROUP BY department dep
Left join and right join (left join table name on join statement)
The difference between left join and right join is that left join is based on the main table and displays all the contents. If the connected table does not have a corresponding value, it will not be displayed or displayed as null. The same is true for right join.
The inner join uses a single SQL statement to query the names of students whose scores in each course are greater than 80 (the table name is score)
  Select distinct name from score where name not in (
    Select name from score where fenshu<=80;
  );

Union all, union (hereinafter respectively t1.t1)
SELECT * FROM t1 UNION ALL SELECT * FROM t2 (do not remove duplicates) ===》t3
SELECT * FROM t1 UNION SELECT * FROM t2 (remove duplicates) =====> t4
(The above are t3 and t4 respectively)