30-минутное руководство по SQL для начинающих

задняя часть база данных MySQL SQL

Эта статьяSQL должен знатьзаметки по чтению, английское название SQL должно быть известно, называетсяSams Teach Yourself in 10 Minutes. Тем не менее, я определенно не могу выучить весь SQL, связанный с этой книгой, за 10 минут, поэтому я назвал ее «Изучение операторов SQL за 30 минут» (на самом деле, я не изучал ее за полчаса...).

Текущая база данных — это mysql, поэтому все следующие примеры демонстрируются с помощью mysql. Поскольку большинство инструментов теперь поддерживают подсветку синтаксиса, все следующие ключевые слова написаны строчными буквами.

Смотрите оригинальную ссылкуblog.Hunan-Hubei.Specialities/post/SQL-So…

Подготовить

инструмент

mycli , терминальный инструмент, написанный на python, поддерживает подсветку синтаксиса, автодополнение, многострочный режим, и если вы знакомы с vi, вы можете использовать vi-режим для быстрого перемещения и редактирования. Короче говоря, vi+mycli — это артефакт!

такой же,postgreSQLможно использоватьpgcli .

pip install -U mycli    # 默认你已经安装了pip
pip install -U pgcli    # 默认你已经安装了pip

база данных

Перед созданием новой базы данных вам необходимо иметь службу базы данных. Если нет, вы можете использовать Docker для ее установки. Ниже приведено руководство по установке Docker.

Кроме того, вы также можете использовать поисковые системы для установки на «голое железо» с помощью командной строки.

Короче говоря, когда у вас есть сервер базы данных, вы можете использовать pgcli/mycli для входа в интерактивную командную среду SQL следующим образом.

Что касается использования pgcli и mysql, вы можете передать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>

$ mycli -u root -h 172.0.0.1 -p $password
Version: 1.12.0
Chat: https://gitter.im/dbcli/mycli
Mail: https://groups.google.com/forum/#!forum/mycli-users
Home: http://mycli.net
Thanks to the contributor - Chris Anderton
(none)>

Прежде чем изучать основные операторы SQL, создайте новую базу данных в качестве необходимого условия для изучения SQL.

Ниже приведеныpostgresПроцесс создания новой базы данных,mysqlтакой же

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.391s

postgres@172:demo>

образец таблицы

После того, как новая база данных завершена, я создаю новую базу данныхdemoБыло подготовлено несколько образцов листов для дальнейшего изучения.

В примере есть две таблицы, которые разделены на таблицу «Студент-студент» и «Таблица класса-класса».

При последующем изучении синтаксиса SQL эти две таблицы будут использоваться в качестве примеров таблиц., вы будете использовать образец листа, чтобы узнать следующее

  • получить данные
  • Сортировать
  • Фильтрация данных
  • Вычисляемое поле
  • агрегация данных
  • пакет данных
  • подзапрос
  • присоединиться
  • вставить данные
  • изменить данные
  • создать таблицу и обновить таблицу
  • Посмотреть
  • Ограничения и индексы
  • курок
  • хранимая процедура

mysql

если вы используетеmysql, выполните следующую инструкцию sql для генерации данных

create table class (
  id int(11) not null auto_increment comment '班级id',
  name varchar(50) not null comment '班级名',
  primary key (id)
) comment '班级表';

create table student (
  id int(11) not null auto_increment comment '学生id',
  name varchar(50) not null comment '学生姓名',
  age tinyint unsigned default 20 comment '学生年龄',
  sex enum('male', 'famale') comment '性别',
  score tinyint comment '入学成绩',
  class_id int(11) comment '班级',
  createTime timestamp default current_timestamp comment '创建时间',
  primary key (id),
  foreign key (class_id) references class (id)
) comment '学生表';

insert into class (name) values ('软件工程'), ('市场营销');

insert into student (name, age, sex, score, class_id) values ('张三', 21, 'male', 100, 1);
insert into student (name, age, sex, score, class_id) values ('李四', 22, 'male', 98, 1);
insert into student (name, age, sex, score, class_id) values ('王五', 22, 'male', 99, 1);
insert into student (name, age, sex, score, class_id) values ('燕七', 21, 'famale', 34, 2);
insert into student (name, age, sex, score, class_id) values ('林仙儿', 23, 'famale', 78, 2);

postgres

если вы используетеpostgres, выполните следующую инструкцию sql для генерации данных

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 ('软件工程'), ('市场营销');

insert into student (name, age, sex, score, class_id) values ('张三', 21, 'male', 100, 1);
insert into student (name, age, sex, score, class_id) values ('李四', 22, 'male', 98, 1);
insert into student (name, age, sex, score, class_id) values ('王五', 22, 'male', 99, 1);
insert into student (name, age, sex, score, class_id) values ('燕七', 21, 'famale', 34, 2);
insert into student (name, age, sex, score, class_id) values ('林仙儿', 23, 'famale', 78, 2);

Основы SQL

срок

  • DBMS

    База данных относится к набору связанных данных, и это СУБД, которая оперирует и управляет этими данными, включая MySQL, PostgreSQL, MongoDB, Oracle, SQLite и другие. РСУБД — это база данных на основе реляционной модели, использующаяSQLУправляйте и манипулируйте данными. Есть также некоторыеNoSQLБаза данных, например MongoDB. так какNoSQLЭто нереляционная база данных и, как правило, не поддерживает операции соединения, поэтому будут некоторые денормализованные данные, и запрос будет выполняться быстрее.

  • Database

    База данных относится к набору связанных данных, как описано выше.create datebase demoновыйdemoбаза данных.

  • Table

    Структурированный документ с определенными свойствами. Например, в таблице учеников атрибуты учеников включают номер ученика, возраст, пол и т. д. Schema (схема) используется для описания этой информации.NoSQLНет необходимости в фиксированных столбцах, обычно нет схемы, и это также способствует вертикальному расширению.

  • Column

    Конкретные атрибуты в таблице, такие как студенческий билет, возраст. Каждый столбец имеет тип данных.

  • Data Type

    Каждый столбец имеет тип данных, например char, varchar, int, text, blob, datetime, timestamp. Выберите подходящий тип данных для столбца в соответствии с детализацией данных, чтобы избежать бессмысленной траты пространства. Вот некоторые виды сравнения

    • char, varchar подходят для хранения, когда дисперсия длины сохраняемых данных невеликаchar,в противном случаеvarchar.varcharДополнительная длина будет использоваться для хранения длины строки, которая занимает много места для хранения. У них разные стратегии обработки пробелов в конце строк, и у разных СУБД разные стратегии, и вам следует обратить внимание на эту разницу при проектировании базы данных.

    • datetime, timestamp datetimeДиапазон времени хранения от 1001 до 9999.timestampСекунды с 1 января 1970 года сохраняются, потому что диапазон хранения относительно невелик, и естественное занятие места для хранения также относительно невелико. Тип даты может быть установлен для автоматического обновления даты при обновлении строки.Рекомендуется, чтобы тип даты и времени сохранялся как эти два типа в соответствии с точностью. Современные СУБД могут хранить данные с микросекундной точностью, напримерmysqlТочность хранения по умолчанию — секунды, но ее можно указать с точностью до микросекунд, то есть с шестью десятичными знаками после запятой.

    • enum Для некоторых фиксированных неизменяемых кодов состояния рекомендуется хранить их какenumтип, имеет лучшую читаемость, меньше места для хранения и может гарантировать достоверность данных.

    Вставьте небольшой вопрос: Как хранить IP-адреса

  • Row

    Каждая строка таблицы данных записывает. Например, студент Чжан Сан.

получить данные

-- 检索单列
select name from student;

-- 检索多列
select name, age, class from student;

-- 检索所有列
select * from student;

-- 对某列去重
select distinct class from student;

-- 检索列-选择区间
-- offset 基数为0，所以 `offset 1` 代表从第2行开始
select * from student limit 1, 10;
select * from student limit 10 offset 1;

Сортировать

Сортировка по умолчаниюASC, поэтому нет необходимости указывать в общем порядке возрастания, ключевое слово для убыванияDESC. использоватьB-TreeИндексы могут повысить производительность сортировки, но только для самых левых совпадений. Что касается индекса, вы можете увидеть следующееFAQ.

-- 根据学号降序排列
select * from student order by number desc;

-- 添加索引 (score, name) 可以提高排序性能
-- 但是索引 (name, score) 对性能毫无帮助，此谓最左匹配，可以根据 B+Tree 进行理解
select * from student order by score desc, name;

Фильтрация данных

Фильтрация данных или фильтрация данных чаще всего используется в sql

-- 找到学号为1的学生
select * from student where number = 1;

-- 找到学号为在 [1, 10] 的学生(闭区间)
select * from student where number between 1 and 10;

-- 找到未设置电子邮箱的学生
-- 注意不能使用 =
select * from student where email is null;

-- 找到一班中大于23岁的学生
select * from student where class_id = 1 and age > 23;

-- 找到一班或者大于23岁的学生
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 not in (1, 2);

Вычисляемое поле

  • CONCAT

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

select concat('hello', 'world') as helloworld;

  • Math

    select age - 18 as relativeAge from student;

select 3 * 4 as n;

Дополнительные функции см. в руководстве по API. Вы также можете настроить функцию (определяемая пользователем функция).

можно использовать напрямуюselectфункция вызова

select now();
select concat('hello', 'world');

агрегация данных

Агрегатные функции, некоторые функции, обобщающие данные, обычно имеютCOUNT,MIN,MAX,AVG,SUMпять.

-- 统计1班人数
select count(*) from student where class_id = 1;

пакет данных

использоватьgroup byЧтобы сгруппировать данные, вы можете использовать агрегатные функции для суммирования сгруппированных данных, используйтеhavingФильтровать сгруппированные данные.

-- 按照班级进行分组并统计各班人数
select class_id, count(*) from student group by class_id;

-- 列出大于三个学生的班级
select class_id, count(*) as cnt from student group by class_id having cnt > 3;

подзапрос

-- 列出软件工程班级中的学生
select * from student where class_id in (
  select id from class where name = '软件工程'
);

присоединиться

Хотя две таблицы имеют общие поля для создания соединения, использование внешних ключей обеспечивает лучшую целостность данных. Например, при вставке несуществующего класса для учащегося вставка завершится ошибкой. Как правило, соединения имеют лучшую производительность, чем подзапросы.

-- 列出软件工程班级中的学生
select * from student, class
where student.class_id = class.id and class.name = '软件工程';

  • Внутреннее соединение

    Внутренние соединения также называются соединениями по эквивалентности.

    -- 列出软件工程班级中的学生
select * from student
inner join class on student.class_id = class.id
where class.name = '软件工程';

  • самостоятельно присоединиться

    Самосоединение — это та же таблица, к которой нужно присоединиться

    -- 列出与张三同一班级的学生
select * from student s1
inner join student s2 on s1.class_id = s2.class_id
where s1.name = '张三';

  • внешнее соединение

    Внешнее соединение делится наleft joinиright join,left joinозначает, что левая сторона никогда не равна нулю,right joinозначает, что правая сторона никогда не будет нулевой.

    -- 列出每个学生的班级，若没有班级则为null
select name, class.name from student
left join class on student.class_id = class.id;

вставить данные

использоватьinsert intoВставьте данные в таблицу, вы также можете вставить несколько строк.

Вы не можете указать имя столбца при вставке, но это сильно зависит от порядка столбцов в таблице.Рекомендуется указывать имя столбца для вставки данных, и вы можете вставить некоторые столбцы.

-- 插入一条数据
insert into student values(8, '陆小凤', 24, 1, 3);

insert into student(name, age, sex, class_id) values(9, '花无缺', 25, 1, 3);

изменить данные

При изменении важных данных обязательно выберите сначала, чтобы подтвердить, нужно ли манипулировать данными, а затемbeginудобно и своевременноrollback

  • возобновить

    -- 修改张三的班级
update student set class_id = 2 where name = '张三';

  • Удалить

    -- 删除张三的数据
delete from student where name = '张三';

-- 删除表中所有数据
delete from student;

-- 更快地删除表中所有数据
truncate table student;

создать таблицу и обновить таблицу

-- 创建学生表，注意添加必要的注释
create table student (
  id int(11) not null auto_increment comment '学生id',
  name varchar(50) not null comment '学生姓名',
  age tinyint unsigned default 20 comment '学生年龄',
  sex enum('male', 'famale') comment '性别',
  score tinyint comment '入学成绩',
  class_id int(11) comment '班级',
  createTime timestamp default current_timestamp comment '创建时间',
  primary key (id),
  foreign key (class_id) references class (id)
) comment '学生表';

-- 根据旧表创建新表
create table student_copy as select * from student;

-- 删除 age 列
alter table student drop column age;

-- 添加 age 列
alter table student add column age smallint;

-- 删除学生表
drop table student;

Посмотреть

Представление – это виртуальная таблица, упрощающая извлечение данных из нескольких таблиц. Представления также можно записывать, но желательно, чтобы они были доступны только для чтения. Представления можно использовать, когда вам нужно присоединиться к нескольким таблицам.

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;

Ограничения и индексы

  • primiry key

    Никакие две строки никогда не будут иметь один и тот же первичный ключ, и ни одна строка никогда не будет иметь два первичных ключа, и первичный ключ никогда не будет пустым. Использование первичного ключа может ускорить индексацию.

    alter table student add constraint primary key (id);

  • foreign key

    Внешние ключи могут гарантировать целостность данных. Возможны следующие два случая.

    • Вставка Zhang Sanfeng Class 5 в таблицу учеников не удастся, потому что Class 5 не существует в таблице классов.
    • Удаление класса 3 из таблицы классов не удастся, потому что Лу Сяофэн и Чу Люсян все еще находятся в классе 3.
    alter table student add constraint foreign key (class_id) references class (id);

  • unique key

    Уникальный индекс гарантирует уникальность значения столбца, но может допускать пустые значения.

    alter table student add constraint unique key (name);

  • check

    Ограничения проверки могут заставить столбец соответствовать определенному условию, если все люди в таблице учеников должны иметь возраст больше 0.

    Но, к сожалению, mysql не поддерживает его, вы можете использоватькурокзаменять
    alter table student add constraint check (age > 0);

  • index

    Индексы могут извлекать данные быстрее, но снижают производительность операций обновления.

    create index index_on_student_name on student (name);

alter table student add constraint key(name);

курок

Я никогда не использовал его в процессе разработки, возможно, у меня мало опыта

События могут инициироваться при вставке, обновлении или удалении строк.

Сцены:

  1. Ограничения данных, такие как возраст учащегося, должны быть больше 0
  2. ловушка, которая предоставляет ловушки на уровне базы данных
-- 创建触发器
-- 比如mysql中没有check约束，可以使用创建触发器，当插入数据小于0时，置为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;

-- 打印触发器列表
show triggers;

хранимая процедура

Я никогда не использовал его в процессе разработки, возможно, у меня мало опыта

Хранимая процедура может рассматриваться как функция, которая выполняет ряд операторов SQL на основе входных данных. Хранимые процедуры также можно рассматривать как инкапсуляцию ряда операций с базой данных, которые могут в определенной степени повысить безопасность базы данных.

-- 创建存储过程
create procedure create_student(name varchar(50))
begin
  insert into students(name) values (name);
end;

-- 调用存储过程
call create_student('shanyue');

практика SQL

Посмотреть больше упражненийleetcode

1. Учащиеся ранжируются в соответствии с баллами учеников в классе, если баллы равны, они будут занимать одно и то же место.

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;

Поля, часто используемые в where и сортировке, должны быть добавлены с индексами Btree, поэтому индексы могут быть добавлены для оценки.

Result:

id name score rank
1 Чжан Сан 100 1
3 Ван Ву 99 2
2 Ли Си 98 3
5 Линь Сяньэр 78 4
4 Ян Ци 34 5
Ссылаться наleetcode: rank-scores

2. Напишите функцию для получения N-го наивысшего балла.

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);

Result:

getNthHighestScore(2)
99
Ссылаться наleetcode: nth highset salary

3. Найдите двух лучших учеников в каждом классе и отобразите рейтинг.

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;

-- 如果不想在from中包含select子句，也可以像如下检索，不过不显示排名
select class.id class_id, class.name class_name, s1.name 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;

Result:

class_name student_name score rank
разработка программного обеспечения Чжан Сан 100 1
разработка программного обеспечения Ван Ву 99 2
маркетинг Ян Ци 34 2
маркетинг Линь Сяньэр 78 1

FAQ

в основном на основеstackoverflow Самые просматриваемые вопросы в .

inner joinиouter joinв чем разница

Ссылаясь на StackOverflow:what is the difference between inner join and outer join

Как обновить одну таблицу на основе данных из другой таблицы

такие как вышеstudentТаблица содержит оценки, и есть еще одна таблицаscore_correctПамять оценок учащихся, которые необходимо пересмотреть из-за ошибок.

В mysql вы можете использовать следующий синтаксис

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

Как работают индексы

Проще говоря, индекс делится наhashи B-Treeдва вида.hashВременная сложность поиска составляет O (1).B-TreeФактическиB+Tree, самобалансирующийся поисковый номер с несколькими ответвлениями, самобалансировка означает, что каждый раз, когда данные вставляются и удаляются, необходимо динамически регулировать высоту дерева, чтобы уменьшить коэффициент балансировки.B+TreeТолько конечные узлы будут хранить информацию и будут связаны с помощью связанных списков. Поэтому он подходит для поиска по диапазону и сортировки, но можно искать только самый левый префикс.a, но не может индексировать имена, начинающиеся сaконечное имя. Кроме того,Everything is trade off.B+TreeФункция самобалансировки алгоритма обеспечивает быстрый поиск, но также снижает производительность обновления, необходимо взвесить все за и против.

Ссылаясь на StackOverflow:how dow database indexing work

Как соединить поля из нескольких строк

В mysql вы можете использоватьgroup_concat

select group_concat(name) from student;
Ссылаясь на StackOverflow:Concatenate many rows into a single text string

Как вставить несколько строк данных в один оператор sql

значения разделяются запятыми и могут вставлять несколько строк данных

insert into student(id, name) values (), (), ()
Ссылаясь на StackOverflow:Inserting multiple rows in a single SQL query

какselectиспользуя условные выражения в

Например, в таблице учеников запросите оценки всех, если меньше 60, будет отображаться 0.

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

Как найти дубликаты

Имя и класс уникальны, найдите дубликаты имени и класса, получите количество повторений и идентификатор

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

В чем необходимость дизайна отношений 1:1

Ссылка https://stackoverflow.com/questions/517417/is-there-ever-a-time-where-using-a-database-11-relationship-makes-sense

Как удалить дубликаты и оставить только первый элемент

Имя и класс уникальны, удалите дубликаты, оставьте только первый элемент

# mysql 就简单很多
delete s1 from student s1, student s2
where s1.name = s2.name and s1.sex = s2.sex and s1.id > s2.id;
Ссылаясь на StackOverflow:how can i remove duplicate rows

Что такое SQL-инъекция

Если оператор запроса

"select * from (" + table + ");"

Когда значение таблицыstudent); drop table student; --Когда оператор будет изменен, таблица будет удалена, что вызовет атаку.

"select * from (student); drop table student; --);"

В чем разница между одинарными кавычками, двойными кавычками и обратными кавычками в mysql

Обратные кавычки (`) обозначают идентификаторы таблицы и столбца. В основном используется, когда имя таблицы или имя столбца является зарезервированным словом. В некоторых других СУБД также используйте[]Представляет имена таблиц и столбцов.

Одинарные кавычки (') обозначают строки.

Двойные кавычки ("") представляют строки по умолчанию, но когда sql_modeANSI_QUOTES, двойные кавычки указывают на имя таблицы или имя столбца.

Ссылаясь на StackOverflow:when to use single quotes, double quotes and backticks in mysql
Категории