Выпуск 17. Что такое база данных MySQL? Достаточно просто прочитать эту сухую статью!

MySQL
Выпуск 17. Что такое база данных MySQL? Достаточно просто прочитать эту сухую статью!

предисловие

зачем учитьсяMySQLШерстяная ткань? так какMySQLявляется самым популярнымсистема управления реляционными базами данныходин вwebприменение,MySQLэто лучшее программное обеспечение.MySQLИспользовалsqlЯзык является наиболее часто используемым стандартизированным языком для доступа к базам данных.

Будьте уверены, друзья, которые читают этот выпуск, подходят для: разработки веб-сайтов, разработки программного обеспечения или любителей.

1. Начало работы с MySQL

Что такое база данных?

  1. базу данных, на основе которойструктура данныхПриходитьОрганизация, хранение и управлениеХранилище данных.
  2. Система управления базами данных относится к программной системе, которая управляет данными в системе баз данных.

Позвольте мне составить ментальную карту:

Освоение деталей:

  • Конфигурация установки, общие команды, работа с базой данных;
  • Целое и с плавающей запятой, дата-время и символ;
  • Создание и просмотр таблиц базы данных, изменение таблиц базы данных, удаление таблиц базы данных;
  • Ненулевые ограничения, ограничения первичного ключа, уникальные ограничения, ограничения по умолчанию, ограничения внешнего ключа;
  • Инструменты управления:MySQL Workbench，SQLyog;
  • Вставка и автоматическая нумерация записей данных в одной таблице, обновление записей данных в одной таблице, удаление записей данных в одной таблице, запрос записей данных в одной таблице, группировка результатов запроса, сортировка результатов запроса и ограничение записей запроса с помощью операторов limit количество;
  • операторы MySQL, числовые функции, символьные функции, функции даты и времени, функции агрегирования, информационные функции и функции шифрования;
  • Подзапросы, создаваемые с помощью операторов сравнения, подзапросы, используемые при вставке записей
  • Объединение нескольких таблиц, внутреннее соединение, внешнее соединение, самосоединение, обновление нескольких таблиц, удаление нескольких таблиц
  • Создать, использовать пользовательскую функцию
  • Создавайте хранимые процедуры, используйте хранимые процедуры

официальный сайт mysql:

Загрузка установочного пакета: (операция установки)

Нажмите, чтобы установить:

Действия по настройке продукта:

Откройте сервисную коробку сwin+r,входитьservices.msc

2. структура каталогов mysql

  1. binкаталог: используется для хранения некоторых исполняемых файлов
  2. includeкаталог: используется для хранения некоторых включенных файлов заголовков
  3. libкаталог: используется для хранения некоторых файлов библиотеки
  4. shareКаталог: используется для хранения информации об ошибках, файлов набора символов и т. д.
  5. dataКаталог: используется для размещения некоторых файлов журналов и баз данных.
  6. my.iniфайл: файл конфигурации для базы данных

Запустить и остановить:

mysqlпараметр:

параметр описывать
-u имя пользователя
-p пароль
-V вывести информацию о версии и выйти
-h адрес хоста

3. Общие команды

Команда для смены пароля пользователя:

mysqladmin 命令用于修改用户密码

mysqladmin 命令格式：

mysqladmin -u用户名 -p旧密码 password新密码

команда для отображения базы данных

show databases;

Команды для использования базы данных

use 数据库的名称

Отображение информации о текущем соединении

  1. показать текущийподключенная база данных:select database();
  2. показать текущийверсия сервера:select version();
  3. показать текущийдата и время:select now();
  4. показать текущийПользователь:select user();

4. Работа с базой данных (создание, изменение, удаление)

создать базу данныхSQL:

create database [if not exists] db_name
[default] character set [=] charset_name

create database database_name;

Измените формат синтаксиса базы данных:

alter database db_name 
[default] character set [=] charset_name

Удалить формат синтаксиса базы данных:

drop database [if exitsts] db_name;

5. База данных — типы данных

Узнайте о типах данных: (с помощью системы управления библиотекой)

Таблица категорий книг:

类别编号（category_id） 类别名称(category) 父类别(parent_id)
1 计算机 0
2 医学 0

Информационный лист книги:

图书编号(book_id) 类别编号(book_category_id) 书名（book_name） 作者(author) 价格(price) 出版社（press） 出版时间（pubdate） 库存（store）

Информационный лист о займе:

图书编号（book_id） 身份证号(card_id) 借出日期（borrow_date） 归还日期(return_date) 是否归还(status)

Информационный лист для читателя

身份证号（card_id） 姓名(name) 性别(sex) 年龄(age) 联系电话(tel) 余额(balance)

тип данных:

Целое:TINYINT-1 байтSMALLINT-2 байтаMEDIUMINT-3 байтаINT-4 байтаBIGINT-8 байт

Типы с плавающей и фиксированной точкой:

float-4个字节
double-8个字节

decimal

Тип даты и времени:

Тип персонажа:

6. Работа со структурой таблиц базы данных

  • Создание и просмотр таблиц данных
创建数据表:create table

create table <表名>
( 
 列名1 数据类型[列级别约束条件][默认值],
 列名2 数据类型[列级别约束条件][默认值],
 ...
 [表级别约束条件]
);

  • Посмотреть таблицу базы данных:
show tables [from db_name];
  • Ознакомьтесь с базовой структурой таблицы данных:
show columns from tbl_name;

describe <表名> /DESC<表名>


show create table tbl_name;

  • Изменить таблицы базы данных

Добавьте столбцы:

alter table <表名>
 add <新列名> <数据类型>
  [ 约束条件 ] [first | after 已存在列名];

Изменить имена столбцов:

alter table <表名>
 change <旧列名> <新列名> <新数据类型>;

Измените тип данных столбца:

alter table <表名> MODIFY <列名> <数据类型>

Изменить расположение столбцов

alter table<表名>
MODIFY <列1> <数据类型> FIRST|AFTER<列2>

Удалить столбцы:

alter table <表名> drop <列名>;

Измените имя таблицы:

alter table <旧表名> RENAME [TO] <新表名>;

  • удалить таблицу базы данных
drop table [if exists] 表1,表2,...表n;

Просмотр разделов таблицы

Чтобы создать разделы таблицы: используйтеpartition byтип (поле)

использоватьvalues less thanРаздел определения оператора

create table bookinfo(
 book_id int,
 book_name varchar(20)
)
partition by range(book_id)(
 partition p1 values less than(20101010),
 partition p3 values less than MAXVALUE
);

7. Подзапросы

select price from bookinfo where book_id = 20101010;

select * from readerinfo;

update readerinfo set balance = balance-(select price from bookinfo where book_id = 20101010) * 0.05 where card_id = '2323232342sxxxxx';

Что такое подзапрос?

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

select * from table1 where col1 = (select col2 from table2);

insert into bookcategory(category,parent_id)values('x',2),('y',2);


insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)
values
(45245244, 6, 'x', '1,2,3 等', 115, '出版社', '2020-06-01',10),
(45342545, 6, 'y', '1, 2',27.8,  '出版社', '2020-07-01', 5);

update readerinfo set balance = 500 where card_id = '683246';

insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)
values
(35452455,'5724154','2020-10-10','2020-11-10','否');

Запросите таблицу информации о заимствовании, отобразите запись о заимствовании книги xx

select * from borrowinfo where book_id = (select book_id from bookinfo where book_name = 'xx');

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

select * from bookinfo where price < (select round(avg(price),2) from bookinfo);

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

select * from bookinfo where book_category_id<>(select category_id from bookcategory where category = '数据库');

Запросите таблицу информации о книгах, чтобы отобразить всю информацию о книгах с категорией книг «компьютер».

select * from bookcategory;

select * from bookinfo where book_category_id = ANY(select category_id from bookcategory where parent_id = 1);


select * from bookinfo where price > ANY (select price from bookinfo where book_category_id =4);  

select * from bookinfo where price > ALL (select price from bookinfo where book_category_id =4);

Запросите таблицу информации о книгах, чтобы отобразить всю информацию о книгах с категорией книг «2».

in 后面的子查询返回一个数据列，等于数据列里的任意一个值都是满足条件的

select * from bookinfo where book_category_id in (select category_id from bookcategory where parent_id = 2);

select * from bookinfo where book_category_id = any (select category_id from bookcategory where parent_id = 2);

Проверьте, есть ли категория «y» в таблице категорий книг, если да, проверьте таблицу информации о книгах.

select * from bookinfo where exists (select category_id from bookcategory where category='y');

select * from bookinfo where exists (select category_id from bookcategory where category='x');

insert into selectОператор копирует данные из таблицы, а затем вставляет данные в существующую таблицу.

insert into table2 select * from table1;

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

Запись поступает от читателей, которые не вернули книгу по истечении времени возврата в таблице информации о заимствовании.

create table readerfee(
	book_id int,
	card_id char(18),
	return_date date,
	actual_return_date date,
	book_fee decimal(7,3),
	primary key(book_id,card_id)
);

select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';

insert into readerfee(book_id,card_id,return_date) select book_id,card_id,return_date from borrowinfo where datediff(sysdate(),return_date)>0 and status = '否';

select * from readerfee;

Читатель с идентификационным номером 5461xxxxxxx вернет сверхлимитную книгу 20201101 и выполнит следующие требования в соответствии с описанием:

  • Обновите таблицу информации о заимствовании и обновите статус заимствования (статус) на «Да».
  • Обновите таблицу информации о штрафах, обновите фактическую дату возврата и сумму штрафа, и сумма штрафа будет вычтена в размере 0,2 юаня за каждый превышенный день.
update borrowinfo set status = '是' where book_id = 20201101 and card_id = '5461xxxxxxx';

select * from borrowinfo;

update readerfee set actual_return_date=sysdate(), book_fee=datediff(sysdate(),return_date)*0.2 where book_id = 20201101 and card_id = '5461xxxxxxx';

select * from readerfee;

8. Ограничения mysql

Это своего рода ограничение, ограничивающее данные строк или столбцов таблицы для обеспечения данных таблицы.Целостность, уникальность.

Структура таблицы:

Книги (номер книгиbook_id, номер категорииbook_category_id, название книгиbook_name,авторauthor)

существуетmysqlНесколько типов ограничений обычно используются в:

Тип ограничения Ненулевое ограничение ограничение первичного ключа уникальное ограничение ограничения по умолчанию ограничения внешнего ключа
ключевые слова not null primary key unique default foreign key

Информационный лист книги:

(номер книгиbook_id, номер категорииbook_category_id, название книгиbook_name,авторauthor,ценаprice, Издательствоpress,Дата публикацииpubdate,в наличииstore)

Таблица категорий книг:

(номер категорииcategory_id- первичный ключ, название категорииcategory- Уникальная, родительская категорияparent_id-не пустой)

Информационный лист для читателей:

(идентификационный номерcard_id,Имяname,Полsex,возрастage,Контактный телефонtel, остаток средствbalance)

Информационный лист о займе:

(номер книгиbook_id,идентификационный номерcard_id, дата кредитаborrow_date, Дата возвратаreturn_date, вернуться лиstatus)

Ненулевое ограничение

nullЗначение поля может быть пустым

not nullЗначение поля не должно быть пустым

Ненулевое ограничение

Ограничение not-null означает, что значение поля не может быть нулевым. Для полей, использующих ненулевое ограничение, если пользователь не укажет значение при добавлении данных, система базы данных сообщит об ошибке.

列名 数据类型 not null

Добавить ненулевое ограничение при создании таблицы

create table bookinfo(
	book_id int,
	book_name varchar(20) not null
);

удалить ненулевое ограничение

alter table bookinfo modify book_name varchar(20);

Добавьте ненулевое ограничение, изменив таблицу

alter table bookinfo modify book_name varchar(20) not null;

ограничение первичного ключа

Ограничение первичного ключа: данные столбца первичного ключа должны быть уникальными и не должны быть пустыми.Первичный ключ может однозначно идентифицировать запись в таблице.

Тип первичного ключа:

Первичный ключ делится напервичный ключ с одним полемиПервичный ключ объединения нескольких полей

Первичный ключ с одним полем: состоит из одного поля

在定义列的同时指定主键
列名 数据类型 primary key;

在列定义的后边指定主键
[constraint<约束名>] primary key(列名);

Добавить ограничение первичного ключа при создании таблицы

create table bookinfo(
	book_id int primary key,
	book_name varchar(20) not null
);

create table bookinfo(
	book_id int,
	book_name varchar(20) not null,
	constraint pk_id primary key(book_id)
);

удалить ограничение первичного ключа

ALTER TABLE bookinfo DROP PRIMARY KEY;

Добавьте ограничения первичного ключа, изменив таблицу

ALTER TABLE bookinfo ADD PRIMARY KEY(book_id);

Первичный ключ объединения нескольких полей, составной первичный ключ

Первичный ключ состоит из комбинации нескольких полей.primary key(字段1，字段2，...字段n);

create table borrowinfo(
book_id int,
card_id char(18),
primary key(book_id,card_id)
);

Добавьте первичный ключ в столбец, изменив таблицу

create table bookinfo(
 book_id int,
 book_name varchar(20) not null
);

alter table bookinfo modify book_id int primary key;
alter table bookinfo add primary key(book_id);
alter table bookinfo add constraint pk_id primary key(book_id);

уникальное ограничение

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

Грамматические правила:

列名 数据类型 unique

[constraint <约束名>] unique(<列名>)

Добавить уникальное ограничение при создании таблицы

CREATE TABLE bookinfo(
	book_id INT PRIMARY KEY,
	book_name VARCHAR(20) NOT NULL UNIQUE 
);

或：

create table bookinfo(
book_id int primary key,
book_name varchar(20) not null,
constraint uk_bname unique(book_name)
);

Добавьте уникальное ограничение, изменив таблицу

alter table bookinfo modify book_name varchar(20) unique;

ALTER TABLE bookinfo ADD UNIQUE(book_name);

alter table bookinfo 
add constraint uk_bname unique(book_name);

удалить уникальное ограничение

ALTER TABLE book_info DROP  KEY  uk_bname;

ALTER TABLE book_info DROP  INDEX  uk_bname;

Разница между ограничением уникальности и ограничением первичного ключа

  1. Таблица может иметь несколькоuniqueутверждение, но только одноprimary keyутверждение
  2. объявлен какprimary keyстолбец не допускает нулевых значений
  3. объявлен какuniqueСтолбцы допускают нулевые значения

ограничения по умолчанию

Ограничение по умолчанию относится к значению столбца по умолчанию.

列名 数据类型 default 默认值

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

CREATE TABLE bookinfo(
	book_id INT PRIMARY KEY,
	press VARCHAR(20)  DEFAULT '出版社'
);

Добавьте ограничения по умолчанию, изменив таблицу

ALTER TABLE bookinfo 
ALTER COLUMN press SET DEFAULT '出版社';

alter table bookinfo 
modify press varchar(10) default '出版社';

удалить ограничения по умолчанию

alter table bookinfo modify press varchar(20);

ALTER TABLE bookinfo 
ALTER COLUMN press DROP DEFAULT;

ограничения внешнего ключа

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

Внешний ключ соответствует ссылочной целостности.Внешний ключ таблицы может иметь нулевое значение.Если это не нулевое значение, каждый внешний ключ должен быть равен значению первичного ключа в другой таблице.

Роль: поддерживать согласованность и целостность данных.

Добавить ограничение внешнего ключа при создании таблицы

图书类别表(父表)
CREATE TABLE bookcategory(
	category_id INT PRIMARY KEY,
	category VARCHAR(20),
	parent_id INT
);

图书信息表(子表)
CREATE TABLE bookinfo(
	book_id INT PRIMARY KEY,
	book_category_id  INT,
	CONSTRAINT fk_cid FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id)
);

Добавьте ограничения внешнего ключа, изменив таблицу

ALTER  TABLE  bookinfo  
ADD  FOREIGN KEY(book_category_id) REFERENCES  bookcategory(category_id);

Удалить ограничения внешнего ключа

ALTER TABLE bookinfo DROP FOREIGN KEY fk_cid;

Ссылочные операции для ограничений внешнего ключа

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

create table bookinfo(
 book_id int primary key,
 book_category_id int,
 constraint fk_cid foreign key (book_category_id) references bookcategory(category_id) on delete cascade);

Создайте системную таблицу управления библиотекой

Список категорий книг

create table bookcategory(
category_id int primary key,
category varchar(20) not null unique,
parent_id int not null
);

Информационный лист книги

create table bookinfo(

book_id int primary key,
book_category_id int,
book_name varchar(20) not null unique,
author varchar(20) not null,
price float(5,2) not null,
press varchar(20) default '机械工业出版社',
pubdate date not null,
store int not null,
constraint fk_bcid foreign key(book_category_id) references bookcategory(category_id)

);

Информационный лист для читателя

create table readerinfo(

card_id char(18) primary key,
name varchar(20) not null,
sex enum('男','女','保密') default '保密',
age tinyint,
tel char(11) not null,
balance decimal(7,3) default 200

);

Информационный лист о займах

create table borrowinfo(

book_id int,
card_id char(18),
borrow_date date not null,
return_date date not null,
status char(11) not null,
primary key(book_id,card_id)

);

9. Работа с записями таблицы базы данных

Вставка записей данных одной таблицы

Формат синтаксиса:

insert into table_name(column_list) values(value_list);

Вставить данные для всех столбцов таблицы

insert into bookcategory
(category_id,category,parent_id)values
(1,'x',0);

insert into bookcategory values(2,'y',0);

Вставить данные для указанного столбца таблицы

insert into readerinfo
(card_id,name,tel)values('4562135465','张飞','4651354651');

Вставка нескольких записей одновременно

insert into bookcategory(category_id,category,parent_id)values(3,'x',1),(4,'y',1),(5,'z',2);

Вставить результат запроса в таблицу

insert into bookcategory select * from test where id>5;

автоматическое приращение

Значение свойства таблицы настроек автоматически увеличивается:

列名 数据类型 auto_increment

Добавить столбец автоинкремента при создании таблицы

create table bookcategory_tmp(
	category_id int primary key auto_increment,
	category varchar(20) not null unique,
	parent_id int not null
)auto_increment=5;

тестовый автоинкремент

insert into bookcategory_tmp(category,parent_id)values('dadaqianduan',0);

удалить автоинкремент

alter table bookcategory_tmp modify category_id int;

Добавить столбец автоинкремента

alter table bookcategory_tmp modify category_id int auto_increment;

Изменить начальное значение столбца автоинкремента

alter table bookcategory_tmp auto_increment = 15;

insert into bookcategory_tmp(category,parent_id)values('文学',0);

Удалить внешний ключ таблицы информации о книге

alter table bookinfo drop foreign key fk_bcid;

Добавить возможность автоматической нумерации таблицы категорий книг

alter table bookcategory modify category_id int auto_increment;

восстановить связь

alter table bookinfo add constraint fk_bcid foreign key(book_category_id)references bookcategory(category_id);

Обновление записи данных одной таблицы

Вставьте информацию о заимствовании в таблицу информации о заимствовании

insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)values(20202010,46516874,'2020-11-29','2020-12-29','否');

Обновление баланса в таблице информации о посетителях

查看书的价格   79.80
select price from bookinfo where book_id = 20202010;

обновить баланс

update readerinfo set balance = balance - 79.80*0.05 where card_id = '46516874';
select * from readerinfo;

Обновить инвентарь таблицы информации о книгах

update bookinfo set store = store -1 where book_id = 20150201;
select * from bookinfo;

Удаление отдельных записей данных таблицы

Удалить записи с указанными условиями

delete from readerinfo where card_id = '46461265464565';

удалить все записи в таблице

delete from readerinfo;

truncate table readerinfo;快

Чтобы удалить все записи в таблице, вы можете использоватьtruncate tableутверждение,truncateИсходная таблица будет удалена напрямую, а таблица будет создана заново, ее синтаксическая структура:

truncate table table_name

Посмотрите номера категорий для педиатрии

select category_id from bookcategory where category='儿科学';

Удалить информацию о книге с номером книги 5

delete from bookinfo where book_category_id = 5;

Удалить категорию Педиатрия из таблицы категорий книг

delete from bookcategory where category = '儿科学';

Запрос записи данных одной таблицы

запросить все столбцы

select * from bookcategory;

select category_id,category,parent_id from bookcategory;

запросить указанный столбец

select category from bookcategory;

select category_id,category from bookcategory;

Запросить записи с указанными условиями

select book_id,book_name,price from bookinfo where press='出版社';

Записи с уникальными результатами запроса

select distinct press from bookinfo;

Просмотр пустых значений

select * from readerinfo where age is null;

группировка

Подсчитайте количество читателей мужского пола в таблице информации о читателях.

select count(*) from readerinfo where sex='男';

Сгруппировать записи в таблице информации о читателе по полу

select sex from readerinfo group by sex;

Сгруппируйте записи в таблице информации о читателях по полу и подсчитайте количество людей для каждого пола.

select sex,count(*) from readerinfo group by sex;

Сгруппируйте записи в таблице информации о читателе по полу, и количество людей после группировки больше, чем пол

select sex from readerinfo group by sex having count(sex)>2;

Сортировать

Сортировка результатов запроса по предложению

order by 列名 [asc|desc]

Направление сортировки:

  1. Сортировка разделена по возрастанию и убыванию, по умолчанию используется порядок возрастания
  2. по возрастаниюasc
  3. По убываниюdesc

Сортировать по одному столбцу

select * from bookinfo order by price;

Сортировать по нескольким столбцам

select * from bookinfo order by price,store;

Укажите направление сортировки

select * from bookinfo order by price,store desc;

Оператор limit ограничивает количество записей запроса

первые 3 строки записей

select * from bookinfo limit 3;

Следующие 2 записи, начиная с 3-й записи

select * from bookinfo limit 2,2;

select * from bookinfo limit 2 offset 2;

insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)
values
(454235424,4, '123', 'xxx',85.8, '出版社', '2020-04-01', 10),
(452454542,4, '456', 'xxx', 35.5, '出版社', '2020-08-01', 20),
(454578754,4, '789', 'xxx', 46.6, '出版社', '2020-05-01',8);

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

select store,count(*)from bookinfo 
group by store 
order by store desc 
limit 4;

10. Операторы и функции

MySQL в основном имеет следующие операторы:

  1. арифметические операторы
  2. оператор сравнения
  3. Логические операторы
  4. побитовые операторы

арифметические операторы

оператор сравнения

Логические операторы

побитовые операторы

приоритет оператора

Идентификационный номер читателя, имя, номер телефона, баланс.

select card_id, name, tel, balance from readerinfo where balance-200<=0;

Проверьте информацию о читателе, чей баланс больше 200, в таблице информации о читателе.

select * from readerinfo where balance>200;

Проверьте информацию о читателе, чей баланс не равен 200 в таблице информации о читателе.

select * from readerinfo where balance <> 200;

Проверьте информацию о читателях, чей возраст не пуст в таблице информации о читателях.

select * from readerinfo where age is not null;

Проверьте таблицу информации о считывателях для считывателей с балансом от 350 до 450.

select * from readerinfo where balance between 350 and 450;

select * from readerinfo where name in('dada','dada1','dada2');

select * from readerinfo where name like '张_';

select * from readerinfo where tel like '135%';

select * from bookinfo where price>50 and store<5;

select * from bookinfo where price>80 or press = '出版社';

select * from bookinfo where price not between 50 and 100;

Числовая функция

ceil возвращает наименьшее целочисленное значение больше x

select ceil(28.55); // 29

floor возвращает наибольшее целочисленное значение меньше x

select floor(28.55); // 28

округление round возвращает целое число, ближайшее к аргументу x, округляя аргумент x в большую сторону.

select round(28.55); // 29
select round(28.55,1),round(28.55,0),round(28.55,-1);
// 28.6 29 30

функция усечения

select truncate(28.55,1),truncate(28.55,0),truncate(28.55,-1);
// 28.5 28 20

По модулю возвращает остаток после деления x на y

select mod(11,2); // 1

select book_id,book_name,price, round(price) from bookinfo;

select * from bookinfo where mod(book_id,2)=0;

символьная функция

Конкатенация строк

select concat('hello','world');
select concat_ws('-','hello','world');

преобразование регистра букв

select lower('Hello World');
select upper('Hello World');

найти длину

select length(' hello ');

удалить пробелы

select ltrim(' hello '),length(ltrim(' hello '));
select rtrim(' hello '),length(rtrim(' hello '));
select trim(' hello '),length(trim(' hello '));

строка перехвата

select substring('hello world',1,5);

select substring('hello world',-5,2);

Получить строку заданной длины

select left('hello world', 5); // hello
select right('hello world', 5); // world

заменить функцию

select replace('hello world','world','mysql'); // hello mysql

функция форматирования

select format(1234.5678,2),format(1234.5,2),format(1234.5678,0);
//1234.57 1234.50 12345

select book_id,book_name,format(price,2)from bookinfo;

функции даты и времени

Просмотр текущей системной даты

select curdate();
// 2020-02-02

select curdate()+0;

select curtime()+0;

Просмотр текущей системной даты и времени

select now(); // 2020-10-10 12:12:12

select sysdate(); // 2020-10-10 12:12:12

date_add(date,interval expr type): year,month,day,week,hour

日期的加运算
select date_add('2020-01-01', interval 5 month); // 2020-06-01

计算两个日期之间间隔的天数
select datediff('2020-02-10','2020-02-01');

日期格式化
select date_format('2020-02-01', '%Y%m');

Агрегатные функции (группирующие функции)

|Имя |Описание | |avg()|Возвращает среднее значение столбца | |count()|Возвращает количество строк в столбце | |max()|Возвращает максимальное значение столбца | |min()| Возвращает минимальное значение столбца | |sum()|Возвращает сумму значений столбца |

Найдите среднюю цену всех книг в таблице информации о книгах.

select avg(price) from bookinfo;

Найдите общую стоимость всех книг в таблице информации о книгах.

select sum(price) from bookinfo;

Найдите максимальный инвентарь в таблице информации о книгах.

select max(store) from bookinfo;

Найдите минимальный запас в таблице информации о книгах.

select min(store) from bookinfo;

Найдите, сколько видов книг содержится в таблице информации о книгах.

select count(*) from bookinfo;

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

select book_category_id as '图书类别',count(book_id) as '图书种类', sum(store) as '库存总和' from bookinfo group by book_category_id;

Информационная функция и функция шифрования

информационная функция системы

查看当前MySQL服务器版本的版本号
select version();

查看MySQL服务器当前连接的次数
select connection_id();

查看当前的数据库名
select schema();

查看当前登录的用户名
select user();

функция шифрования

select md5('test');

create table myuser(
	username varchar(10),
	password varchar(35)
);

insert into myuser values('user1',md5('pwd1'));

select * from myuser;

select * from myuser where username = 'user1' and password = md5('pwd1');

select password('rootpwd');

set password = password('rootpwd');

select user,authentication_string from mysql.user;

11. Запрос соединения нескольких таблиц

Запрос объединения нескольких таблиц предназначен для извлечения данных из нескольких таблиц.

Из таблицы информации о книгах: (номер книгиbook_id, номер категорииbook_category_id, название книгиbook_name)

По таблице категорий книг: (номер категорииcategory_id, название классификацииcategory, родительская категорияparent_id)

Получить таблицу: (ID книгиbook_id, название книгиbook_name, название классификацииcategory)

Синтаксическая структура объединения нескольких таблиц:

table_reference
[INNER] JOIN | {LEFT|RIGHT} [OUTER] JOIN
table_reference
on conditional_expr

Объединение нескольких таблиц Просмотр таблицы информации о книгах и таблицы категорий книг чтобы получить номер книги, название книги, категорию книги

select book_id,book_name,category from bookinfo inner join bookcategory  on bookinfo.book_category_id = bookcategory.category_id;

внутреннее соединение

Запрашивайте и выбирайте данные из нескольких таблиц в соответствии с условиями соединения, отображайте строки данных в этих таблицах, которые соответствуют условиям соединения, и объединяйте их в новые записи. (Внутренние соединенияобщий для обоих)

Синтаксическая структура внутреннего соединения:

select column_list
from t1
[INNER] JOIN t2 ON join_condition1
[INNER] JOIN t3 ON join_condition2
...]
where where_conditions;

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

select borrowinfo.book_id,book_name,borrowinfo.card_id, name, tel, return_date, status from borrowinfo
inner join bookinfo on borrowinfo.book_id = bookinfo.book_id
inner join readerinfo on borrowinfo.card_id = readerinfo.card_id
where borrowinfo.status = '否';

select t1.book_id,book_name,t1.card_id, name, tel, return_date, status from borrowinfo t1
join bookinfo t2 on t1.book_id = t2.book_id
join readerinfo t3 on t1.card_id = t3.card_id
where t1.status = '否';

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

Внешнее соединение будет запрашивать связанные строки из нескольких таблиц.

Внешнее соединение делится на: левое внешнее соединениеleft outer join; правое внешнее соединениеright outer join

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

select book_id, book_name, category from bookcategory
left join bookinfo on bookcategory.category_id = bookinfo.book_category_id
where parent_id<>0;

select book_id, book_name, category from bookinfo a
right join bookcategory b on b.category_id = a.book_category_id;

select * from bookcategory;

Левое внешнее соединение: отображаются все записи в левой таблице и записи в правой таблице, соответствующие условиям соединения.

Правое внешнее соединение: отображаются все записи в правой таблице и записи в левой таблице, соответствующие условиям соединения.

Грамматические конструкции:

select column_list
from t1
left | right [outer] join t2 on join_condition1;

самоподключение

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

Запросите номер категории книг, имя категории и имя родительской категории всех категорий книг.

select * from bookcategory;

select s.category_id as'图书类别编号', s.category as '图书类别名称', p.category as'图书的上级分类名称' from bookcategory s
inner join bookcategory p
on s.parent_id = p.category_id;

Обновление за несколькими столами

update 
table1 {[inner] join | {left|right} [outer] join} table2
on conditional_expr
set col1 = {expr1|default}
[,col2 = {expr2|default}]...
[where where_condition]

Считыватель с идентификационным номером 432xxxxxx вернет книгу сверхурочных 86154 и выполнит следующие требования в соответствии с описанием:

  1. Обновите таблицу информации о заимствовании и обновите статус заимствования (статус) на «Да».
  2. Обновите таблицу информации о штрафах, обновите фактическую дату возврата и сумму штрафа, и сумма штрафа будет вычтена в размере 0,2 юаня за каждый превышенный день.
  3. Также обновите баланс таблицы информации о читателе. (Вычесть сумму штрафа из баланса)
update readerfee t1 join readerinfo t2 on t1.card_id = t2.card_id
set actual_return_date = sysdate(),book_fee=datediff(sysdate(),return_date)*0.2,balance = balance - book_fee
where t1.book_id = 86154 and t1.card_id = '432xxxxxx';

select * from readerinfo;

Удалить несколько таблиц

delete table1[.*], table2[.*]
from table1 {[inner]join|{left|right}[outer]join} table2
on conditional_expr
[where where_condition]

Таблица категорий книг, таблица информации о книгах:

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

select book_id,book_name,category from bookcategory_bak t1
left join bookinfo_bak t2 
on t1.category_id = t2.book_category_id
where parent_id<>0;

delete t1 from bookcategory_bak t1
left join bookinfo_bak t2 
on t1.category_id = t2.book_category_id
where parent_id<>0 and book_id is null;

select * from bookcategory_bak;

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

select book_id,book_name,category_id,category from bookcategory_bak t1
inner join bookinfo_bak t2
on t1.category_id = t2.book_category_id;

delete t1,t2 from bookcategory_bak t1
inner join bookinfo_bak t2
on t1.category_id = t2.book_category_id
where t1.category_id = 3;

Объединение нескольких таблиц

По результатам, возвращаемым запросом соединения: внутреннее соединение, внешнее соединение, перекрестное соединение.

Оператор, используемый в соответствии с условием соединения: равное соединение, неравное соединение.

12. Пользовательские функции

создать функцию

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

функция вызова

SELECT 函数名(参数列表)

Функция просмотра

SHOW FUNCTION STATUS;

удалить функцию

DROP FUNCTION IF EXISTS function_name;

Функция: необходимо иметь возвращаемое значение, вы можете указать 0 ~ n параметров

Создайте пользовательскую функцию:

create function function_name([func_parameter])
returns type
[characteristics..] routine_body

CharacteristicsУказывает характеристики хранимой функции.Примеры значений:

sql security{definer|invoker}指明谁有权限来执行。

definer表示只有定义者才能执行。

invoker表示拥有权限的调用者才可以执行，默认情况下，系统指定为definer。

comment 'string':注释信息，可以用来描述存储函数。

Тело функции состоит из кода sql, который может быть простым оператором sql. Если вам нужно использовать составную структуруbegin...endЗаявления, составные структуры могут содержать объявления, управление потоком.

select length('hello');
select date_format(pubdate,'%Y-%m') from bookinfo;

delimiter // 
create function ym_date(mydate date)
returns varchar(15)
begin
return date_format(mydate,'%Y-%m');
end//

delimiter;
select ym_date(pubdate) from bookinfo;

Создайте пользовательскую функцию:

Формат синтаксиса:

create function function_name([func_parameter])
returns type
[characteristics...] routine_body

select length('hello');

select date_format(pubdate,'%Y-%m') from bookinfo;

delimiter //
create function ym_date(mydate date)
returns varchar(15)
begin
return date_format(mydate,'%Y-%m');
end//
delimiter ;

使用（调用）自定义函数
select ym_date(pubdate) from bookinfo;

Пример функции анализа:

创建一个函数
delimiter $$ --定界符
--- 开始创建函数
create function user_main_fn(v_id int)
returns varchar(50)
begin
--定义变量
declare v_userName varchar(50);
--给定义的变量赋值
select f_userName info v_userName from t_user_main
where f_userId = v_id;
--返回函数处理结果
return v_userName;
end $$ --函数创建定界符
delimiter;

Два необходимых условия для пользовательских функций: параметры, возвращаемое значение

Создать пользовательскую функцию

create function function_name
returns
{string|integer|real|decimal}
routine_body

语法格式：

CREATE FUNCTION function_name([func_parameter])
RETURNS type
[characteristics … ] routine_body
  1. имя_функции : имя функции
  2. func_parameter : список параметров функции
  3. RETURN type : указывает тип возвращаемого значения.
  4. Характеристики: определяет характеристики хранимой функции.
  5. подпрограмма_тело : тело функции

Создайте пользовательскую функцию без аргументов:

удалить пользовательскую функцию

DROP FUNCTION [IF EXISTS] func_name;


SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点：%i分：%s秒')

CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点：%i分：%s秒');

SELECT f1();

Функции композитных конструкций

-- 将语句结束符改为$$,为了防止下面的函数将;看成是语句的结束
DELIMITER $$

CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
RETURN
BEGIN
INSERT INTO table_1(username) VALUES(username);
LAST_INSERT_ID();
END;

-- 将分隔符改回来
DELIMITER ;

Использование управления потоком

Часто используемые операторы управления потоком:

  1. ЕСЛИ заявление условного суждения -если
  2. CASE заявление об условном суждении -кейс
  3. Оператор цикла WHILE - пока
  4. Оператор цикла LOOP -loop
  5. Оператор цикла REPEAT - повторить

13. Хранимые процедуры

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

DECLARE var_name [, varname2, varname3 …] date_type [DEFAULT value];
例：DECARE num INT DEFAULE 10;

внутреннийBEGIN…ENDПеременные, определенные в блоке, действительны только внутри этого блока.

Переменные сеанса распространяются на всю программу

грамматический результат

create procedure proc_name([proc_parameter])
[characteristics...] routine_body

delimiter // 
create procedure selectproc1()
begin
select book_id, book_name, price, store from bookinfo;
end //
delimiter;

call selectproc();

Удалить хранимую процедуру:

drop procedure [if exists] proc_name;

创建一个查询图书的编号、书名、价格和库存的存储过程。
delimiter //
create procedure selectproc1()
begin
select book_id,book_name,price,store from bookinfo;
end//
delimiter ;

调用存储过程
call selectproc1();

创建查询图书编号、书名、图书类别的存储过程
delimiter //
create procedure proc1()
begin
select book_id,book_name,category from bookinfo t1
join bookcategory t2
on t1.book_category_id = t2.category_id;
end//
delimiter ;

call proc1();

设计一个存储过程，删除一个读者，并输出剩余读者的个数。
delimiter //
create procedure proc2(in cid char(18), out num int)
begin
delete from readerinfo where card_id = cid;
select count(card_id) into num from readerinfo;
end//
delimiter ;

select * from readerinfo;
call proc2('6545xx', @num);
select @num;


设计一个存储过程，实现交换两个数的处理。
delimiter //
create procedure proc3(inout num1 int, inout num2 int)
begin
declare t int default 0;
set t = num1;
set num1 = num2;
set num2 = t;
end//
delimiter ;

set @n1 = 3, @n2 = 5;
call proc3(@n1,@n2);
select @n1,@n2;

删除存储过程
drop procedure proc1;
drop procedure  if exists proc2;

разница между хранимой процедурой и функцией

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

Сохраненные функции, реализованные функции более целенаправленны.

Разница в возвращаемом значении

Хранимая процедура: она может возвращать несколько значений или не может возвращать значение только для достижения определенного эффекта или действия.

Сохраненная функция: должна иметь возвращаемое значение, и может быть только одно возвращаемое значение.

различные параметры

Хранимая процедура: существует три типа параметров хранимой процедуры: in, out, inout.

Сохраненная функция: существует только один тип параметра, аналогичный параметру in.При вызове функции необходимо указать значение в соответствии с типом параметра.

Грамматические структуры

Хранимая процедура, когда хранимая процедура объявленаТип возвращаемого значения указывать не нужно.

Сохраненная функция, необходимая при объявлении функцииуказать тип возвращаемого значенияи должен содержать допустимый оператор return в теле функции.

метод вызова

хранимая процедура, сcallзаявление о вызове

хранимая функция, встроенная вsqlиспользуется вselectвызывать

14. Транзакции

Есть четыре условия, которым должна соответствовать транзакция:

atomicity 原子性
consistency 一致性
lsolation 隔离性
durability 持久性

контрольная транзакция

rollback, откат завершает транзакцию пользователя и отменяет все незафиксированные текущие изменения.

commit, фиксирует транзакцию и делает все изменения, внесенные в базу данных, постоянными

savepoint identifier, позволяет создать точку сохранения в транзакции, а в транзакции может быть несколько точек сохранения

rollback to identifier, чтобы откатить транзакцию до точки

Существует два основных метода обработки транзакций.

использоватьbegin, rollback, commitреализовать

  1. begin,start transactionначать транзакцию
  2. rollbackоткат транзакции
  3. commitПодтверждение транзакции

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

  1. set autocommit = 0Отключить автоматическую фиксацию
  2. set autocommit = 1Начать автоматическую фиксацию

InnoDB использует транзакцию

MySQL5.5 Версия с самого начала, InnoDB - это таблицы двигателя хранения по умолчанию.

innodbЯвляется предпочтительным механизмом для транзакционных баз данных и поддерживает безопасные для транзакций таблицы.

разделитель в MySql

По умолчанию разделителем является точка с запятой.В клиенте командной строки, если строка команд заканчивается точкой с запятой, после возврата каретки mysql выполнит команду.

(Сообщите интерпретатору mysql, завершилась ли команда и можно ли выполнить mysql.)

Что такое механизм хранения: Двигатель хранения базы данных является основным программным компонентом базы данных. Система управления базами данных использует механизм данных для создания данных, запроса, обновления и удаления данных.

mysqlЯдром является механизм хранения.

innodbмеханизм хранения

  • этоmysqlПредоставляет безопасный для транзакций механизм хранения с возможностями фиксации, отката и аварийного восстановления.
  • Хорошая производительность для обработки больших объемов данных
  • innodbМеханизм хранения поддерживает ограничения целостности внешнего ключа.
  • innodbИспользуется на многих крупных сайтах баз данных, требующих высокой производительности.

Настройте механизм хранения:

  1. Установите механизм хранения сервера
  2. Установите требуемый механизм хранения под mysqld в файле конфигурации my.ini.
  3. default-storage-engine=InnoDB
  4. перезапустить сервер mysql
创建表（单个）设置存储引擎
create table mytest(
 id int primary key,
 name varchar(10)
) engine = innodb default charset = utf8;

Изменить механизм хранения таблицы

alter table tablename engine = engineName

15. Управление и обслуживание

Управление пользователями

USE mysql;

select user from user;

Таблица разрешений: таблица сведений о разрешениях учетной записи хранения:user,db,host,tables_priv,columns_privиprocs_priv

Роль каждой таблицы разрешений

tables_privТаблица используется для установки прав доступа к таблице;columns_privТаблица используется для установки разрешений для столбца таблицы;procs_privТаблицы могут устанавливать права доступа к хранимым процедурам и хранимым функциям.

Создайте нового пользователя с помощью инструкции CREATE USER.

Формат синтаксиса:

CREATE USER “user”@“host” [IDENTIFIED BY “password”];

Удаление пользователя с помощью инструкции DROP USER

Формат синтаксиса:

DROP USER user[, user];

例：使用DROP USER删除账户"rose"@"localhost":
DROP USER "rose"@"localhost";

Пример:

查看日志文件的路径
show variables like 'log_error';

创建新的日志信息表
flush logs;

创建新的日志信息表
mysqladmin -uroot -p flush-logs

Обратите внимание, не потеряйтесь

Ну все,выше все содержание этой статьи.Люди которые здесь видят таланты. Я буду продолжать обновлять статьи, связанные с технологиями, в будущем.Если вы считаете, что статья полезна для вас, пожалуйста, поставьте лайк и поделитесь, спасибо! !

Категории