Sams Teach Yourself in 10 Minutes Sams Teach Yourself in 10 Minutes However, I certainly could not learn all the SQL involved in this book in 10 minutes, so I named it 30 minutes to learn SQL statements (actually half an hour did not learn…). .

The database at hand is mysql, so the following examples are demonstrated by mysql. Since most tools now support syntax highlighting, the following keywords are in lower case.

See link to the original article
Blog. Xiange. Tech/post/SQL – gu…

To prepare

tool

Mycli, a terminal tool written in Python, supports syntax highlighting, auto-completion, multi-line mode, and if you are familiar with VI, you can use vi-mode to quickly move and edit. In short, vi + MyCLI is a miracle!

Similarly, postgreSQL can use PGCLI.

PIP install -u pgcli # PIP install -u pgcli #Copy the code

The database

Before creating a new database, you need to have a database service, if not, you can choose to use docker for installation, the following is the docker installation guide tutorial

  • postgres docker
  • mysql docker

In addition, you can also search the search engine to find how to use the command line to install bare-metal machine

In summary, once you have a database service, you can use PGCLI /mycli to access the SQL interactive command environment, as shown below

For help with pgCLI and mysql, you can use pgCLI –help

$ pgcli -U postgres -h 172.0.0.1
Server: PostgreSQL 10.5
Version: 1.10.3
Chat: https://gitter.im/dbcli/pgcli
Mail: https://groups.google.com/forum/#!forum/pgcli
Home: http://pgcli.com
postgres@172:postgres>
Copy the code
172.0.0.1 $mycli -u root - h - p $password Version: 1.12.0 Chat: https://gitter.im/dbcli/mycli email: https://groups.google.com/forum/#! forum/mycli-users Home: http://mycli.net Thanks to the contributor - Chris Anderton (none)>Copy the code

Before learning basic SQL statements, create a new database as a prerequisite for learning SQL

The following is the process of creating a new database for Postgres, mysql as well

postgres@172:postgres> create database demo CREATE DATABASE Time: 0.891s postgres@172:postgres> use demo You are now connected to database "demo" as user "postgres" Time: 0.391 s postgres @ 172: demo >Copy the code

The sample table

After the completion of the new database, I prepared several sample tables for the new database demo for future study.

There are two tables in the example, the Student table and the class table. The specific class and student fields are shown below.

These two tables will be used as sample tables in the following SQL syntax study. You will use the sample table to learn the following

  • Retrieve the data
  • The sorting
  • Data filtering
  • Computed field
  • Data aggregation
  • The data packet
  • The subquery
  • join
  • Insert data
  • Modify the data
  • Create and update tables
  • view
  • Constraints and indexes
  • The trigger
  • The stored procedure

mysql

If you use mysql, execute the following SQL statement to generate the data

Create table class (id int(11) not null auto_increment COMMENT 'class id', name varchar(50) not null comment' class id', Primary key (id)) comment 'class table '; Create table student (id int(11) not null auto_increment comment 'id', name varchar(50) not null comment' iD ', Age tinyint unsigned default 20 comment 'age ', sex enum('male', 'famale') comment' gender ', score tinyint comment 'enrollment ', Class_id int(11) COMMENT 'class ', createTime TIMESTAMP default current_TIMESTAMP COMMENT' createTime ', primary key (id), class_id int(11) COMMENT 'class ', createTime TIMESTAMP default current_timestamp COMMENT' createTime ', primary key (id), Foreign key (class_id) references class (id) comment 'student '; Insert into class (name) values (' software '), (' marketing '); Insert into score (name, age, sex, score, class_id) values (' 三', 21, 'male', 100, 1); Insert into score (name, age, sex, score, class_id) values (' 三 ', 22, 'male', 98, 1); Insert into score (name, age, sex, score, class_id) values ('王五', 22, 'male', 99, 1); Insert into score (name, age, sex, score, class_id) values (' yan7 ', 21, 'famale', 34, 2); Insert into score (name, age, sex, score, class_id) values (' famale', 23, 'famale', 78, 2);Copy the code

postgres

If you are using Postgres, execute the following SQL statement to generate the data

create table class ( id serial not null, name varchar(50) not null, primary key (id) ); create type sex_type as enum('male', 'famale'); create table student ( id serial not null, name varchar(50) not null, age smallint default 20, sex sex_type, score smallint, class_id int, createTime timestamp default current_timestamp, primary key (id), foreign key (class_id) references class (id) ); Insert into class (name) values (' software '), (' marketing '); Insert into score (name, age, sex, score, class_id) values (' 三', 21, 'male', 100, 1); Insert into score (name, age, sex, score, class_id) values (' 三 ', 22, 'male', 98, 1); Insert into score (name, age, sex, score, class_id) values ('王五', 22, 'male', 99, 1); Insert into score (name, age, sex, score, class_id) values (' yan7 ', 21, 'famale', 34, 2); Insert into score (name, age, sex, score, class_id) values (' famale', 23, 'famale', 78, 2);Copy the code

SQL based

The term

  • DBMS

    A database is a collection of associated data that is operated and managed by a DBMS, including MySQL, PostgreSQL, MongoDB, Oracle, SQLite, etc. RDBMSS are relational model-based databases that use SQL to manage and manipulate data. There are also NoSQL databases, such as MongoDB. Because NoSQL is a non-relational database and generally does not support join operations, there will be some denormalization of the data and the query will be faster.

  • Database

    A database is a collection of associated data, such as the demo database created by Create DateBase Demo.

  • Table

    A structured file with specific attributes. For example, student table, student attributes have student number, age, gender and so on. Schema is used to describe this information. NoSQL does not require fixed columns, generally has no schema, and also favors vertical scaling.

  • Column

    Specific attributes in the table, such as student id, age. Each column has a data type.

  • Data Type

    Each column has a data type, such as CHAR, vARCHar, int, text, BLOb, datetime, timestamp. Choose the right data type for the column based on the granularity of the data to avoid wasteful space. Here are some type comparisons

    • Char, vARCHAR Stores char when the variance of the length of data to be stored is small. Otherwise, VARCHAR stores char. Varchar uses extra length to store string length, occupying large storage space. They have different strategies for handling Spaces at the end of strings, and different DBMSS have different strategies, which should be taken into account when designing databases.

    • Datetime, timestamp Datetime storage time range from 1001 to 9999. Timestamp saves the number of seconds since January 1, 1970. Because the storage range is relatively small, the natural storage space is also relatively small. The date type can be set to automatically update the date when the row is updated. It is recommended that the date and time types be stored as the two types based on the accuracy. Today’s DBMSS can store precision at the microsecond level. For example, mysql defaults to seconds, but can specify precision at the microsecond level, which is six decimal places to the decimal point

    • Enum You are advised to store some fixed and unchangeable status codes as enum types, which have better readability and smaller storage space, and ensure data validity.

    Insert a quick question: how to store IP addresses

  • Row

    Each row of a data table. Such as student Zhang SAN.

Retrieve the data

Select name from student; Select name, age, class from student; Select * from student; Select * from student; select * from student; Select * from student limit 1, 10; select * from student limit 1, 10; select * from student limit 10 offset 1;Copy the code

The sorting

The default sort is ASC, so you do not need to specify the ascending keyword. The descending keyword is DESC. Using b-tree indexes can improve sorting performance, but only for left-most matches. You can see the following FAQ about indexes.

Select * from student order by number desc; Adding an index (score, name) improves sorting performance -- adding an index (name, score) does nothing to improve sorting performance. Select * from student order by score desc, name;Copy the code

Data filtering

Data filtering, or data filtering, is used most frequently in SQL

Select * from student where number = 1; select * from student where number = 1; Select * from student where number between 1 and 10; select * from student where number between 1 and 10; Select * from student where email is null; Select * from student where class_id = 1 and age > 23; select * from student where class_id = 1 and age > 23; Select * from student where class_id = 1 or age > 22; select * from student where class_id = 1 or age > 22; Select * from student where class_id in (1, 2); select * from student where class_id in (1, 2); Select * from student where class_id not in (1, 2);Copy the code

Computed field

  • CONCAT

    select concat(name, '(', age, ')') as nameWithAge from student;

select concat('hello', 'world') as helloworld;
Copy the code

  • Math

    select age - 18 as relativeAge from student;

select 3 * 4 as n;
Copy the code

See the API manual for more functions, and you can also customize User Define functions.

You can call the function directly using SELECT

select now();
select concat('hello', 'world');
Copy the code

Data aggregation

Aggregate functions are functions that summarize data, including COUNT, MIN, MAX, AVG and SUM.

Select count(*) from student where class_id = 1;Copy the code

The data packet

Using group by to group data, you can use aggregation functions to aggregate the grouped data and having to filter the grouped data.

Select count(*) from student group by class_id select count(*) from student group by class_id; Select count(*) as CNT from student group by class_id having CNT > 3;Copy the code

The subquery

Select * from student where class_id in (select id from class where name = 'class_id');Copy the code

join

While it is possible to create a join if two tables have common fields, using foreign keys can better ensure data integrity. For example, when inserting a nonexistent class into a student, the insertion fails. In general, joins have better performance than subqueries.

Select * from student, class where student.class_id = class.id and class.name = 'software ';Copy the code

  • In the connection

    Inner connection is also called equivalent connection.

    Select * from student inner join class on student.class_id = class.id where class.name = 'class_id ';Copy the code

  • Since the connection

    A self-join is a join of the same table

    Select * from student s1 inner join student s2 on s1.class_id = s1.class_id where s1.name = 's1 ';Copy the code

  • Outer join

    The outer join is divided into left join and right join. Left Join means that the left side will never be null, and right join means that the right side will never be null.

    Null select name, class. Name from student left join class on student.class_id = class.id;Copy the code

Insert data

Use insert into to insert data into a table, or to insert multiple rows.

You can insert data without specifying a column name, but it depends on the order of the columns in the table. You are advised to insert data by specifying a column name, and you can insert some columns.

Insert into sc values(8, 1, 2, 3); Insert into sc (name, age, sex, class_id) values(9, 'score ', 25, 1, 3);Copy the code

Modify the data

Before modifying important data, select data and begin to rollback data in a timely manner

  • update

    Update student set class_id = 2 where name = 'class_id ';Copy the code

  • delete

    Delete from student where name = 'student '; Delete from student; Truncate table student;Copy the code

Create and update tables

Create student table; Create table student (id int(11) not null auto_increment comment 'STUDENT ID ', Name varchar(50) not NULL comment 'student ', age tinyint unsigned default 20 comment' student ', sex enum('male', 'famale') comment 'gender ', score tinyint comment' class_id ', class_id int(11) comment 'class ', CreateTime timestamp default current_timestamp comment 'createTime ', primary key (id), Foreign key (class_id) references class (id) comment 'student '; Create table student_copy as select * from student; Alter table student drop column AGE; Alter table student add column age smallint; Drop table student;Copy the code

view

A view is a virtual table that makes it easier to retrieve data from multiple tables. A view can also be written, but it is best kept read-only. Views can be used when multiple table joins are required.

create view v_student_with_classname as
select student.name name, class.name class_name
from student left join class
where student.class_id = class.id;

select * from v_student_with_classname; 
Copy the code

Constraints and indexes

  • primiry key

    No two rows have the same primary key, and neither row has two primary keys and the primary key is never empty. Using primary keys speeds up indexing.

    alter table student add constraint primary key (id);
Copy the code

  • foreign key

    Foreign keys ensure data integrity. There are two situations.

    • Insert class 5 into student table failed because class 5 does not exist.
    • Deleting class 3 from class 3 will fail because Lu Xiaofeng and Chu Liuxiang are still in Class 3.
    alter table student add constraint foreign key (class_id) references class (id);
Copy the code

  • unique key

    Unique indexes guarantee that the column value is unique, but nulls are allowed.

    alter table student add constraint unique key (name);
Copy the code

  • check

    The check constraint can make the column satisfy certain conditions, if all the people in the student table should be older than 0.

    But unfortunately mysql does not support, can use
    The triggerInstead of 

    alter table student add constraint check (age > 0);
Copy the code

  • index

    Indexes can retrieve data faster, but reduce the performance of update operations.

    create index index_on_student_name on student (name);

alter table student add constraint key(name);
Copy the code

The trigger

I have never used it in the development process, probably because OF my lack of experience

Events can be triggered when rows are inserted, updated, or deleted.

Scene:

  1. Data constraints, such as the age of the student must be greater than 0
  2. Hook, which provides database-level hooks
Create trigger -- for example, mysql does not have the check constraint, you can use create trigger, when the insert data is less than 0, set to 0. create trigger reset_age before insert on student for each row begin if NEW.age < 0 then set NEW.age = 0; end if; end; -- Print list of triggers show triggers;Copy the code

The stored procedure

I have never used it in the development process, probably because OF my lack of experience

A stored procedure can be viewed as a function that executes a series of SQL statements based on input. Stored procedures can also be regarded as the encapsulation of a series of database operations, which can improve database security to a certain extent.

Create PROCEDURE create_student(name varchar(50)) begin INSERT into students(name) values (name); create procedure create_student(name varchar(50)) begin INSERT into students(name) values (name); end; Call create_student('shanyue');Copy the code

SQL practice

See LeetCode for more exercises

1. Rank students according to their scores. If the scores are equal, it will be the same rank

select id, name, score, (
  select count(distinct score) from student s2 where s2.score >= s1.score
) as rank
from student s1 order by s1.score desc;
Copy the code

A Btree index can be added to score for frequently used fields in WHERE and sorting.

Result:

id name score rank
1 Zhang SAN 100 1
3 Cathy 99 2
2 Li si 98 3
5 Lin XianEr 78 4
4 Yan seven 34 5

reference
leetcode: rank-scores

2. Write a function to get the NTH highest score

create function getNthHighestScore(N int) return int
begin
  declare M int default N-1;
  return (
    select distinct score from student order by score desc limit M, 1;
  )
end;

select getNthHighestScore(2);
Copy the code

Result:

getNthHighestScore(2)
99

reference
leetcode: nth highset salary

3. Retrieve the top two students in each class and display the ranking

select class.id class_id, class.name class_name, s.name student_name, score, rank from ( select *, ( select count(distinct score) from student s2 where s2.score >= s1.score and s2.class_id = s1.class_id ) as rank from student s1 ) as s left join class on s.class_id = class.id where rank <= 2; If you do not want to include the select clause in the FROM clause, you can also search as follows: Select class. Id class_id, class. Name class_name, s1.name, score from student s1 left join class on s1.class_id = class.id where (select count(*) from student s2 where s2.class_id  = s1.class_id and s1.score <= s2.score) <= 2 order by s1.class_id, score desc;Copy the code

Result:

class_name student_name score rank
Software engineering Zhang SAN 100 1
Software engineering Cathy 99 2
marketing Yan seven 34 2
marketing Lin XianEr 78 1

FAQ

Most are compiled based on the most viewed questions in StackOverflow.

inner join 与 outer joinWhat is the difference between

Reference StackOverflow:
what is the difference between inner join and outer join

How do I update data from one table to another

For example, the student table above holds the scores, while the other table score_correct contains the scores of the students that need to be corrected because of errors.

In mysql, you can use the following syntax

update student, score_correct set student.score = score_correct.score where student.id = score_correct.uid;
Copy the code

How does the index work

Indexes are classified into hash and B-tree indexes. The time complexity of hash lookup is O(1). A B-tree is a B+Tree, which is a self-balancing multi-fork search number. Self-balancing indicates that the Tree height needs to be dynamically adjusted every time data is inserted or deleted to reduce the balance factor. B+Tree only leaves store information and are linked together using a linked list. Therefore, it is suitable for scope search and sorting, but only the left-most prefix can be searched, such as the index can only index the name starting with a, but not the name ending with a. Besides, Everything is trade off. The self-balancing feature of B+Tree ensures fast search and reduces update performance. You need to weigh the pros and cons.

Reference StackOverflow:
how dow database indexing work

How do I join fields of multiple rows

In mysql, you can use group_concat

select group_concat(name) from student;
Copy the code

Reference StackOverflow:
Concatenate many rows into a single text string

How do I insert multiple rows of data into a SQL statement

Values are separated by commas, allowing multiple rows of data to be inserted

insert into student(id, name) values (), (), ()
Copy the code

Reference StackOverflow:
Inserting multiple rows in a single SQL query

How to inselectUse conditional expressions in

For example, in the student table, query for all grades and display 0 if less than 60

select id, name, if(score < 60, 0, score) score from student;
Copy the code

How do I find duplicates

Name and class unique, find the name and class repeat, retrieve the number of repeat and ID

select name, class_id, group_concat(id), count(*) times from student
group by name, class_id
having times > 1;
Copy the code

What is the necessity of 1:1 Relation design

Refer to https://stackoverflow.com/questions/517417/is-there-ever-a-time-where-using-a-database-11-relationship-makes-sense

How do I delete duplicates and keep only the first item

Name and class unique, delete duplicates, only retain the first item

Delete s1 from student s1, student s2 where s1.name = s2.name and s1.sex = s2.sex and s1.id > s2.id;Copy the code

Reference StackOverflow:
how can i remove duplicate rows

What is SQL injection

For example, a query statement is

"select * from (" + table + ");"
Copy the code

When table = student); drop table student; When, the statement becomes, the table will be deleted, causing an attack.

"select * from (student); drop table student; -);"Copy the code

What’s the difference between single quotes, double quotes, and backquotes in mysql

Backquotes (‘) indicate table, column identifiers. This parameter is used when the table name or column name is reserved. In some other DBMSS, table and column names are also represented by [].

Single quotes (‘) represent strings.

Double quotes (“) stand for strings by default, but when SQL_mode is ANSI_QUOTES, double quotes stand for table or column names.

Reference StackOverflow:
when to use single quotes, double quotes and backticks in mysql