MySQL development must know sql statements

Create a database

create database db1;

Delete database

drop database db1;

Create Data Table

create table tb1 User table(
                    id int not null auto_increment primary key,
                    name char(10),
                    department_id int,
                    p_id int,
                )engine=innodb default charset=utf8;

Primary keys A table can only have one primary key, which can be composed of one column or more columns

Creation of foreign keys

CREATE TABLE t5 (
                      nid int(11) NOT NULL AUTO_INCREMENT,
                      pid int(11) not NULL,
                      num int(11),
                      primary key(nid,pid) --This is where the two columns are set as primary keys
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

                    create table t6(
                        id int auto_increment primary key,
                        name char(10),
                        id1 int,
                        id2 int,
                        CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid) --Here's setting the foreign key
                    )engine=innodb default charset=utf8;

Operation of data rows

Insertion of data

insert into tb1(name,age) values('ax',8);
insert into tb12(name,age) select name,age from tb11;

Deleting data from tables

delete from t1;
truncate table t1;
drop table t1
delete from tb1 where id > 10
delete from tb12 where id >=2 or name='alex'

Update of data

update tb1 set name='root' where id > 10

Query of data

select * from tb;
select id,name from tb;

Viewing table structure

show create table t1;
desc t1;

Other

  select * from tb12 where id != 1
    select * from tb12 where id in (1,5,12);
    select * from tb12 where id not in (1,5,12);
    select * from tb12 where id in (select id from tb11)
    select * from tb12 where id between 5 and 12;

wildcard

select * from tb12 where name like "a%"
select * from tb12 where name like "a_"

paging

select * from tb12 limit 10;                    
select * from tb12 limit 0,10;
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;                 
select * from tb12 limit 10 offset 20;

# page = input('Please enter the page number to view')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1 
# select * from tb12 limit 10,10;2

sort

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;

Remove 10 Data
select * from tb12 order by id desc limit 10;

Grouping

select count(id),max(id),part_id from userinfo5 group by part_id;
//Aggregate functions have the following:                  
                    count
                    max
                    min
                    sum
                    avg

**** If the result of aggregate function is filtered twice?Must be used having ****
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;

select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;

Self-Value-Added Settings

Setup of Table Self-Value Added

alter table t1 auto_increment=20;
-- This means we start at 20 and use the show create table t1\G You can see what the current value is.

Based on session level

-- View current session values
show session variables like 'auto_incre%'
-- Set Session Step
set session auto_increment_increment=2;
-- Set Start Value
set session auto_increment_offset=10;

Based on global settings

-- View global settings
show global variables like 'auto_inc%';
-- Set global step value
set global auto_increment_increment=3;
-- Set Start Value
set global auto_increment_offset=11;

sql server is flexible and can be set up by itself when creating tables
REATE TABLE t5 (
nid int(11) NOT NULL AUTO_INCREMENT,
pid int(11) NOT NULL,
num int(11) DEFAULT NULL,
PRIMARY KEY (nid,pid)
) ENGINE=InnoDB AUTO_INCREMENT=4, step=2 DEFAULT CHARSET=utf8

          CREATE TABLE `t6` (

nid int(11) NOT NULL AUTO_INCREMENT,
pid int(11) NOT NULL,
num int(11) DEFAULT NULL,
PRIMARY KEY (nid,pid)
) ENGINE=InnoDB AUTO_INCREMENT=4, step=20 DEFAULT CHARSET=utf8

unique index

create table t1(
    id int,
    num int,
    xx int,
    unique qu1 (num ,xx) -- This means that the two columns cannot have the same data on one row, for example, they are both 1,1,Not possible
);

Unique index: Constraints cannot be repeated (can be empty)
Primary key index: Constraints cannot be repeated (cannot be null)
Their feature is to speed up queries

Foreign Key One to One

create table userinfo1(
                    id int auto_increment primary key,
                    name char(10),
                    gender char(10),
                    email varchar(64)
                )engine=innodb default charset=utf8;

                create table admin(
                    id int not null auto_increment primary key,
                    username varchar(64) not null,
                    password VARCHAR(64) not null,
                    user_id int not null,
                    unique uq_u1 (user_id),
                    CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
                )engine=innodb default charset=utf8;

Foreign key many-to-many

Example 1:
                //User table
                //Affinity table

            //Example 2:
                //User table
                //Host table
                //User Host Relationship Table
            ===>Many-to-many

                create table userinfo2(
                    id int auto_increment primary key,
                    name char(10),
                    gender char(10),
                    email varchar(64)
                )engine=innodb default charset=utf8;

                create table host(
                    id int auto_increment primary key,
                    hostname char(64)
                )engine=innodb default charset=utf8;

                create table user2host(
                    id int auto_increment primary key,
                    userid int not null,
                    hostid int not null,
                    unique uq_user_host (userid,hostid),
                    CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
                    CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
                )engine=innodb default charset=utf8;

Join 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 shown

                    # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
                    # All shown on right side of department5

                    select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
                    //Will appearnullHide every line

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.ti           

select count(id) from userinfo5;
Note: The above is uploaded to http://blog.51cto.com/sgk2011/2051641

Tags: Session Database SQL

Posted on Sat, 07 Mar 2020 09:46:26 -0800 by celeb-x2