Day 6 summary & database operation

1. Classification of database

    Relational type ：mysql,oracle,sqllite( The mobile terminal comes with ,django Default ),sql server
     Non relational :redis,mongoDB

2.mysql install

    Guarantee ： Check whether the computer has been installed before installation , uninstall   mysql5.5 edition ,django Use 5.5 above
A database is used to store data
     library / surface    Library similar directory    The table is similar to the table of contents excle

3. Basic knowledge

sql sentence
     Dedicated to manipulating libraries and tables        a key
sql The classification of sentences
     Database query language （DQL）    select
     Database operation language （DML）    delete update insert
     Database definition language （DDL）     establish 、 Delete 、 Modify the structure of the table   create、drop、alter

Field type
     Integers ：int/bigint
     decimal ：float/double    
     character string ：char/varchar
     Time ：date/time/datetime
Field constraints
    null    Can be null
    not null  Can't be empty
    default   The default value is
    primary key  Primary key    
    auto_increment  Self increasing
    charset  Set character set    

4.DDL Database definition language

    Create a library
        create database Library name CHARSET=utf8;
     Delete Library
        DROP DATABASE Library name ;
     The statement to query the created library
        show create DATABASE Library name ;
     Use the library
        use Library name
     Create table
        create table Table name ( Field type length , Field type length )
     Delete table
        drop table Table name
     Query the statement that creates the table
        show create table Table name ;

     The structure of the query table
        desc Table name
     Modify table structure
         newly added ：alter table Table name add Field name type   length ;
         modify ：alter table Table name MODIFY COLUMN Field name type   length ;
         Delete : alter table Table name drop Field name ;

5.DML Database operation language

  newly added
        insert  into Table name value ( value , value , value , value ), ( value , value , value , value )
     modify
        UPDATE Table name set Field = The new value where Conditions                         Modify the data of a field
        UPDATE Table name set Field = The new value , Field = The new value where Conditions         Modify the data of two fields
     Delete
        delete  from Table name   where Conditions      Delete specified data
        delete  from Table name                         Delete all data

6.DQL Database query language

Query all the data ：select * from Table name ;
         Query the data of the specified field ：select Field name , Field name from Table name ;
         Comparison operator : >  >=   <    <=
            select * from Table name where Field > value ;
         Logical operators ：and    or   not
            SELECT * from student where sex=' Woman ' and score>92
            SELECT * from student where sex=' Woman ' or score>92
            SELECT * from student where sex=' male '
            SELECT * from student where not sex=' Woman '
         keyword -- Fuzzy query
            select * from Table name where Field like '% Fuzzy data %'
            eg:%： Match all data    _: To match a character
         Range queries      80~95
             stay ... Between       stay .... in
            SELECT * from student where score BETWEEN 80 and 95;
            SELECT * from student where id in(1,3,5,7)
         Sort
            select * from Table name order by Field    desc( In reverse order ), Field   desc( In reverse order )
         grouping
             function ：count() Query quantity   sum() and   max() The lion    min（） Take the minimum   avg() The average     
            select count(*) from Table name group by Field having Conditions
            eg：having similar where But in general where It cannot be used with functions later ,having Can often and group by Use it together
         Pagination
            limit Start index position , The number of bars displayed
            SELECT * from student LIMIT 0,3  

Homework 1

Homework 2

Homework 6

Homework 7

Additions and deletions