Beginners do not point in too much loss, MySQL database super complete knowledge point summary!

Time:2020-11-28

This article first appeared in the CSDN blog City Angel of the same name article: beginners do not point in too much loss, MySQL database super complete knowledge point summary! .

1 database overview

1.1 what is a database

  • A database is a file system, but we need to operate the file system through the command (SQL).
  • Database is a “warehouse to organize, store and manage data according to data structure”. A large amount of data can be stored and managed in a unified and shared computer for a long time.
  • A database is a data collection that can be stored together in a certain way, can be shared with multiple users, has as little redundancy as possible, and is independent of the application program. It can be regarded as an electronic file cabinet — the place where electronic documents are stored. Users can add, query, update and delete the data in the file.

1.2 role of database

  • Storage data, data warehouse, with access rights, restrict different people have different operations.

1.3 why study database

  • All operations are background data, which are encapsulated in the background data and then presented to the foreground.

1.4 common databases

  1. MySQL: open source free database for small and medium-sized enterprises. Sun company acquired mysql, Sun company was acquired by Oracle, and Oracle began to charge after acquisition.
  2. MariaDB: created by the founder of MySQL, it is a branch of the open source version of MySQL. Basically, all commands are the same.
  3. Oracle: Oracle, commercial charge, suitable for large e-commerce websites, acquisition of sun and Java.
  4. DB2: IBM, mainly providing solutions, software and hardware, server architecture. Most banking systems use DB2.
  5. Sqlserver: government website in Windows asp.net SQL server is usually used in most university teaching. Graphical tools are good.
  6. Sybase: it’s gone.
  • NoSQL non relational database: key:value Mongodb, redis, etc.

1.5 relational database

  1. It is mainly used to describe the relationship between entities.
  2. E-R diagram shows: entity box, attribute ellipse, relation diamond.

1.6 MySQL database server

  • Mysql database: database management software.
  • Server: similar to a computer, this computer is installed with relevant server software. These software will listen to different port numbers and provide different services according to the port number accessed by users.

2 SQL statement of MySQL

  1. SQL: structure query language.
  2. DDL: data definition language. Define database, database table and structure. Create, drop and alter.
  3. DML: Data Manipulation Language: mainly used to manipulate data. Insert, update, delete.
  4. DCL: data control language: define access rights, cancel access rights, security settings grant.
  5. DQL: Data Query Language: select, from clause, where clause.

2.1 crud operation of database

  • First log in to the database server:MySQL - u [user name] - P [password]

2.1.1 create database

  1. Create database database name;
    create database sql_1;
  2. Create database database name character set character set;
    create database sql_2 character set utf8;
  3. Create database database name character set character set collate proofreading rules;
    create database sql_3 character set utf8 collate utf8_bin;

2.1.2 view database

  1. --View all databases
    show databases;
  2. --View database defined statements
    Show create database database name
    show create database sql_1;
    show create database sql_2;

2.1.3 modify database

  1. --Modify the character set of the data
    The name character set character set of the alter database database;
    alter database sql_1 character set gbk;

2.1.4 delete database

  1. Drop database database name;
    drop database sql_2;
    drop database sql_3;

2.1.5 other database operation commands

  1. --Switch database (select database)
    The name of the use database;
    use sql_1;
  2. --View databases currently in use
    select database();

2.2 crud operation of table

2.2.1 create table

  1. Create table name(
        Type (length) constraint of column name 1,
        Type (length) constraint of column name 2,
        . . . . . .
    );
    
    create table student(
        sid int primary key,
        sname varchar(15),
        sex int,
        age int
    );
  2. The type of column

    -- int
    --Char / varchar [char: fixed length, varchar: variable type]
    -- double, float, boolean
    -- date[YYYY-MM-DD]
    -- time[hh:mm:ss]
    --DataTime [yyyy-mm-dd HH: mm: SS] is null by default
    --Timestamp [yyyy-mm-dd HH: mm: SS] uses the current time by default
    --Text [mainly used to store text]
    --Blob [stores binary]
  3. Column constraints

    --Primary key constraint: primary key
    --Unique constraint: unique
    --Non NULL constraint: not null

2.2.2 view table

  1. --View all tables
    show tables;
  2. --View the definition of the table
    Name of the show create table table table;
    show create table student;
  3. --View the structure of the table
    Name of desc table;
    desc student;

2.2.3 modification table

  • Add, modify, change, drop, and rename.
  1. --Add column
    The constraint of type column of add column column in alter table name
    alter table student add grade int not null;
  2. --Modify column type
    Alter table name modify the type of column column column;
    alter table student modify sex varchar(2);
  3. --Modify column name
    Alter table name change the original column name new column name new column type;
    alter table student change sex tel varchar(20);
  4. --Delete column
    Alter table name DROP column name;
    alter table student drop tel;
  5. --Modify table name
    Rename table the original table name to the new table name;
    rename table student to stus;
  6. --Modify the character set of a table
    Alter table name character set character set;
    alter table stus character set gbk;

2.2.4 delete table

  1. Drop table name;
    drop table stus;

2.3 crud operation of data in table

2.3.1 insert data

  1. Insert into table name (column name 1, column name 2, column name 3) values (value 1, value 2, value 3);
    insert into student(sid, sname, sex, age) values (1, 'liuyi', 1, 23);
  2. --If you insert data with full column names, the column names after the table names can be omitted
    Insert into table name values (value 1, value 2, value 3);
    insert into student values(2, 'chener', 1, 23);
  3. --Note that if a partial column is inserted, the column name cannot be omitted
    insert into student(sid, sname) values(3, 'zhangsan');
    Insert into student values (3, 'Zhangsan'); -- error
  4. --Batch insert
    insert into student values
    (4, 'lisi', 1, 23),
    (5, 'wangwu', 1, 23),
    (6, 'zhaoliu', 1, 23),
    (7, 'sunqi', 1, 23),
    (8, 'zhouba', 1, 23);

2.3.2 deleting records

  1. Delete from table name [where condition];
    delete from student where sid=10;
  2. --If no condition is specified, the data in the table will be deleted one by one
    delete from student;
  3. The difference between delete data and truncate delete data

    --Delete: DML deletes the data in the table one by one
    --Truncate: DDL deletes the table before rebuilding it
    --The data in the table should be used to determine which item is more efficient
    --If there is less data, delete is more efficient
    --If there is more data, truncate is more efficient

2.3.3 update table records

  1. Update table name set, column name 1 = value of column 1, column name 2 = value of column 2 [where condition];
    --If the parameter is a string or date, use single quotation marks
    Update student set sname ='lisi'where sid = 4;
    --If there is no condition, then every record will be executed
    Update student set sname ='li Si ', sex = 20;

2.3.4 query records

  1. Select [distinct] [*] [column name 1, column name 2] from table name [where condition]; -- distinct to remove duplicate data
  2. First create two tables for operation:

    --Commodity classification
    --1. Classification ID
    --2. Classification name
    --3. Classification and description
    create table category(
        cid int primary key auto_increment,
        cname varchar(10),
        cdesc varchar(31)
    );
    
    --All merchandise
    --1. Commodity ID
    --2. Trade name
    --3. Commodity price
    --4. Production date
    --5. Commodity classification ID
    --Commodity and commodity classification: ownership
    create table product(
        pid int primary key auto_increment,
        pname varchar(10),
        price double,
        pdate timestamp,
        cno int
    );
    --As for data, there is no more operation here. Just a little data is OK
  3. Simple query

    --Query all products
    select * from product;
    --Query product name and price
    select pname, price from product;
  4. Alias query, as keyword, as can be omitted

    --Table alias (mainly used for multi table queries)
    select p.pname, p.price from product as p;
    --Column alias
    Select pname as commodity name, price as commodity price from product;
    --Omit as keyword
    Select pname, price, commodity price from product;
  5. Remove duplicate values

    --Query all prices of goods
    select price from product;
    select distinct price from product;
    --Select operation query: only the query result is operated, and the data in the table is not changed
    select *, price*1.5 from product;
    select *, price*0.9 from product;
    Select *, price * 0.5 as discounted price from product;
  6. Query [where keyword] by condition, specify the condition and determine the record to be operated

    --Query all commodity information with commodity price > 60
    select * from product where price > 60;
    --Conditional writing after where
    --Relational operators: > > = < < = =! = < >
    --< > not equal to, standard SQL syntax
    --! = not equal to, non-standard SQL syntax
    --Query all products whose price is not equal to 88
    select * from product where price <> 88;
    --Query the commodity price between 10 and 100
    select * from product where price > 10 and price < 100;
    -- between...and...
    select * from product where price between 10 and 100;
  7. Logical operation: and, or, not

    --Find out the goods whose price is less than 100 or whose price is greater than 900
    select * from product where price < 100 or price > 900;
  8. Like fuzzy query

    -- _ : represents a character
    --%: represents multiple characters
    --Find all the products with 'pie' in the name '% cake%'
    Select * from product where pname like '% pie%';
    --Query all products whose second place is' Cigarette '_ Smoke% '
    select * from product where pname like '_ Smoke% ';
  9. In gets the value in a range

    --Query all the commodities with commodity classification ID in 1, 4, 5
    select * from product where cno in (1, 4, 5);
  10. Sort query: order by keyword

    --ASC: ascend, the default sort method
    --Desc: descending
    --Query all products and sort them by price
    select * from product order by price;
    --Query all products and sort them in descending order by price
    select * from product order by price desc;
    --Query items with 'small' names in ascending order of price
    Select * from product where pname like '% order by price ASC;
  11. Aggregate function

    Sum(): Sum
    AVG (): Average
    Count(): Statistics quantity
    Max (): maximum value
    Min (): minimum
    --Get the sum of all commodity prices
    select sum(price) from product;
    --Get the average price of all items
    select avg(price) from product;
    --Get the number of all products
    select count(*) from product;
  • Note that the where condition cannot be followed by an aggregate function
  1. Subquery

    --Query the goods whose price is greater than the average price
    select * from product where price > (select avg(price) from product);
  2. Group by

    --According to the CNO field grouping, after grouping, count the number of goods
    select cno, count(*) from product group by cno;
    --According to the CNO group, the average price of each group of goods is counted, and the average price of goods is > 60
    select cno, avg(price) from product group by cno having avg(price) > 60;
    --Having keyword, which can be followed by aggregate function, appears after grouping
    --Where keyword, can not be linked to aggregate function, appears before grouping
  3. Writing order

    -- S..F..W..G..H..O
    -- select .. from .. where .. group by .. having .. order by
  4. Execution order

    -- F..W..G..H..S..O
    -- from .. where .. group by .. having .. select .. order by 

2.3 paging query

  1. Limit [index], [number of displayed]
    Select * from product limit 0,3; -- three data items from items 1 to 3 are shown
    Select * from product limit 3,3; -- three pieces of data from items 4 to 6 are shown

2.4 relationship maintenance between multiple tables

  1. Foreign key constraint: foreign key

    --Add a foreign key constraint to CNO in product (two tables: product, category)
    alter table product add foreign key(cno) references category(cid);
  • When deleting, delete all the data associated with the foreign key before deleting the classified data.
  1. Primary key constraint: by default, it cannot be empty and unique.

    • Foreign keys are primary keys that point to another table
    • A table can only have one primary key
  2. Unique constraint: the content in the column must be unique, can not be repeated, can be empty.

    • Unique constraints cannot be used as foreign keys for other tables
    • You can have multiple unique constraints

2.4.1 principle of database establishment

  • Usually, a project / application builds a database.

2.4.2 table building principles among multiple tables

  1. One to many: commodity and classification

    • Principle of creating a table: add a foreign key in one party and point to the primary key of one party.
  2. Many to many: teachers and students, students and Curriculum

    • Table creation principle: create an intermediate table, split the many to many relationship into one to many relationship. The intermediate table must have at least two foreign keys, pointing to the original two tables.
  3. one-on-one: class and monitor, citizen and ID card, country and national flag

    • Principle of table building:

      • Treat the one-to-one situation as a one to many situation. Add a foreign key to any table, and the foreign key must be unique and point to another table
      • Merge two tables directly into one
      • Establish a connection between the primary keys of the two tables, so that the primary keys in the two tables are equal
    • Practical use: it is not used much

      • Split table operation: separate personal common information and infrequently used information to reduce the overstaffing of the table.

2.5 multi table query

2.5.1 Cartesian product (cross join query)

  1. --What we find out is the product of two tables, and the result is meaningless.
    select * from product, category;
  2. --Filter out meaningful data
    select * from product, category where cno = cid;
    select * from product as p, category as c where p.cno = c.cid;
    select * from product p, category c where p.cno = c.cid;

2.5.2 inner join query

  1. --Implicit inner join
    select * from product p, category c where p.cno = c.cid;
  2. --Explicit inner join
    select * from product p inner join category c on p.cno = c.cid;
  3. --Differences
    --Implicit inner join: filter the where condition based on the query results
    --Explicit inner join: query results with conditions, and the execution efficiency is high

2.5.3 left external connection

  1. If there is no corresponding data in the right table, null is used instead.
  2. select * from product p left outer join category c on p.cno = c.cid;

2.5.4 right outer connection

  1. If there is no corresponding data in the left table, null is used instead.
  2. select * from product right outer join category c on p.cno = c.cid;

3 END

Thank you for reading here. If you feel that this article is well written, please click on it and collect it. You can also pay attention to it~~

Recommended Today

It’s easy to output complete SQL statements in laravel

Query construction method in laraveltoSqlThe resulting SQL statement does not bind condition parameters, similar to thisselect * from users where id = ?So I wrote an extension package laravel dump SQL to get the complete SQL statement. Source code laravel-dump-sql – https://github.com/guanguans/… install $ composer require guanguans/laravel-dump-sql -v Publishing services $ php artisan vendor:publish –provider=”Guanguans\\LaravelDumpSql\\ServiceProvider” […]