MySQL Basics Summary

One, CMD-based operation

1. Database folder:

Create Database db1; #创建数据库db1

Drop database db1; #删除数据库db1

Show databases db1; #查看文件db1

Use db1; #切换到t1文件

2. Data sheet:

Build table:
CREATE TABLE T1 user table (id int auto_increment primary key,num int)

Engine=innodb default Charset=utf8;

Show  tables;                   #查看建的表

Clear the table:
        Delete from T1;          #清除内容但id从清除的数字开始
        truncate TABLE t1        #清除内容但id从零开始

Select COUNT (1) from T1;        See how much data is in the table

SELECT * from T1;               View the contents of the T1 table

Desc   T1;                      See if T1 has self-increment

Show CREATE table T1 \g;        See how the T1 table was created

3. Adding and deleting changes

Increase

INSERT INTO TB11 (name,age) VALUES (' Alex ', ' n ');
   
INSERT INTO TB11 (name,age) VALUES (' Alex ', ' n '), (' root ');
   
INSERT INTO TB12 (name,age) select Name,age from Tb11;
Delete
Delete from tb12;
Delete from tb12 where ID!=2
Delete from tb12 where ID =2
Delete from tb12 where ID > 2
Delete from tb12 where ID >=2
Delete from tb12 where ID >=2 or name= ' Alex '

Change
Update tb12 set name= ' Alex ' where id>12 and name= ' xx '
Update tb12 set name= ' Alex ', age=19 where id>12 and Name= ' xx '
Check
   
select * from TB12;
   
select Id,name from TB12;
   
Select Id,name from tb12 where ID > ten or name = ' xxx ';
   
Select Id,name as CNAME from TB12 where ID > ten or name = ' xxx ';
   
select name,age,11 from TB12;
   
Other:

      ALTER TABLE T1 auto_increment=2;   Insert content starting with ordinal two

      SELECT * FROM TB12 where id! = 1
      SELECT * from TB12 where ID in (1,5,12);   View 1,5,12
      SELECT * from TB12 where ID not in (1,5,12);   View except for 1,5,12
      SELECT * from TB12 where ID in (select ID from TB11)  to see a specific column in the table, * only one column
      SELECT * from TB12 where ID between 5 and 12; The area (closed) is fixed, taking between 5 and 12.

wildcard characters:
   
SELECT * from TB12 where name is like "a%" Example Assss afffffffff
SELECT * from TB12 where name "A_" example as AD AC


Paging:
   
select * from TB12 limit 10; View the top 10 articles
      
select * from TB12 limit 0, 10; From 0 onwards, take 10 strips .
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;
      
select * FROM TB12 limit ten offset;


# page = input (' Please enter the page number to view ')
# page = Int (page)
# (page-1) *
# Select * from TB12 limit 0,10; 1
# Select * from TB12 limit 10,10;2
   
   
Sort by:
SELECT * from TB12 ORDER by id desc; large to small
SELECT * from TB12 ORDER by ID ASC; Small to large
SELECT * from Tb12 ORDER by age Desc,id desc;
       
after taking 10 data
SELECT * from TB12 ORDER by id desc limit;

Group:
   
select count (ID), max (ID), part_id from Userinfo5 Group by part_id;
      
Count
Max
min
sum
avg
      
* * * * If the aggregate function results are filtered two times? Must use having * * * *
select count (ID), part_id from Userinfo5 Group by PART_ID have count (id) > 1;
      
select count (ID), part_id from Userinfo5 where ID > 0 GROUP by PART_ID have count (id) > 1;

      
table Operation:
   
SELECT * from Userinfo5,department5
      
SELECT * from userinfo5,department5 where userinfo5.part_id = Department5.id
      

SELECT * from Userinfo5 LEFT join department5 on userinfo5.part_id = Department5.id
SELECT * from department5 LEFT join userinfo5 on userinfo5.part_id = Department5.id
# Userinfo5 left all displayed
      
      
# Select * from Userinfo5 right join department5 on userinfo5.part_id = Department5.id
# department5 Right all show
   
   
   
SELECT * from Userinfo5 innder join department5 on userinfo5.part_id = Department5.id
a row is hidden when null appears
      
      
   
   
   
   
SELECT * from
department5
Left join userinfo5 on userinfo5.part_id = Department5.id
Left join Userinfo6 on userinfo5.part_id = Department5.id
   
   
Select
Score.sid,
Student.sid
 from
score

Left join student on score.student_id = Student.sid

Left Join course on score.course_id = Course.cid

Left Join class on student.class_id = Class.cid

Left join teacher on Course.teacher_id=teacher.tid
      



select COUNT (id) from USERINFO5;
   

MySQL Basics Summary