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

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

The following is some basic sql knowledge I have summarized, mainly for better reference and to help other beginners in the future, while recording my own growth, and also writing some slightly

This is a question at the level of a SQL interview question. Okay, without further ado, let’s see the real question. . .

#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)

The above is a summary of the Mysql table, column, and library addition, deletion, modification, and query problems introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!

You may also be interested in:
  • mysql add, delete, modify and query basic statements
  • Simple implementation of Mysql add, delete, modify and query statements
  • MySQL trigger trigger add, delete, modify and query operation example
  • MySQL detailed single table add, delete, modify and query CRUD statements
  • Detailed explanation of MySQL view management view example [add, delete, modify and query operations]
  • MySQL table addition, deletion, modification and query basic tutorial

<<:  Detailed explanation of the basic knowledge of front-end componentization

>>:  An example of how to use nginx to configure multiple laravel projects with one domain name

Recommend

A collection of information about forms and form submission operations in HTML

Here we introduce the knowledge about form elemen...

How to use JS code compiler Monaco

Preface My needs are syntax highlighting, functio...

Detailed explanation of sql_mode mode example in MySQL

This article describes the sql_mode mode in MySQL...

How to deploy hbase using docker

Standalone hbase, let’s talk about it first. Inst...

Basic ideas and codes for implementing video players in browsers

Table of contents Preface Summary of audio and vi...

Steps to build MHA architecture deployment in MySQL

Table of contents MAH 1. Introduction to MAH Arch...

A brief discussion on how to customize the host file in Docker

Table of contents 1. Command 2. docker-compose.ym...

The most complete 50 Mysql database query exercises

This database query statement is one of 50 databa...

Detailed installation and use tutorial of mysql 8.0.15 under windows

This article shares with you the detailed install...

Three methods of inheritance in JavaScript

inherit 1. What is inheritance Inheritance: First...

vue+element custom query component

This article mainly introduces the Vue project. O...

Best Practices Guide for Storing Dates in MySQL

Table of contents Preface Do not use strings to s...