Введение в администрирование MySQL
С развитием систем баз данных процедуры инсталляции и использования MySQL становятся все проще. Судя по всему, именно простота работы с MySQL стала основной причиной широкой ее популярности среди пользователей. Особенно это относится к тем из них, которые не являются, да и не желают быть программистами. Безусловно, знания компьютерного профессионала могут оказаться весьма полезными, но для успешного использования MySQL быть опытным программистом вовсе не обязательно.
Однако и полностью без управления MySQL работать также не может. Администратор должен хотя бы иногда проверять согласованность и эффективность ее работы и знать, что делать при возникновении проблем. Вся эта информация представлена далее
Этот курс посвящен рассмотрению различных аспектов администрирования MySQL . В этой лекции представлено описание всех основных вопросов, которыми необходимо владеть для успешного выполнения задач по администрированию инсталляцией MySQL. Она же включает краткий обзор всех обязанностей администратора. Инструкции по их выполнению рассматриваются в следующих лекциях.
Представленный далее в этой лекции длинный список обязанностей может не на шутку напугать начинающего или неопытного администратора. Каждая указанная в списке задача действительно очень важна, однако не стоит пробовать освоить их все сразу. Гораздо лучше использовать лекции этого курса в качестве справочного руководства, которое всегда под рукой и в которое при необходимости можно заглянуть.
Уже имеющие опыт работы администраторы могут заметить, что администрирование MySQL подобно администрированию других систем управления базами данных. Опыт администрирования других систем просто неоценим. В то же время, администрирование MySQL имеет свои уникальные особенности, которые и описываются в этом курсе.
Обзор задач администрирования
СУБД MySQL состоит из нескольких основных компонентов. Знание их сути и предназначения поможет лучше понять природу управляемой системы и принципы работы различных ее средств. Настоятельно рекомендуется потратить немного времени, чтобы хорошенько разобраться в представленном далее материале. Это значительно упростит дальнейшую работу. В частности, необходимо вникнуть в следующие аспекты работы MySQL.
What Does the DROP TABLE Statement Look Like?
The syntax for the DROP TABLE command is pretty simple:
First, you write DROP TABLE, and then the name of the table you want to drop.
Each database has its own additions to the SQL DROP TABLE statement.
Oracle
The CASCADE CONSTRAINTS keyword means that all referential integrity constraints with the table will be dropped as well. If there are constraints on the table and you don’t specify this keyword, then an error will be displayed and the table will not be dropped.
If you specify PURGE, it means that the table and its dependent objects will be purged from the recycle bin, and you will no longer be able to recover the table. By default, this is not specified, and the table and objects are moved to the recycle bin and can be recovered if you need to.
SQL Server
The IF EXISTS keyword lets you only run the command if the table exists.
SQL Server lets you drop multiple tables in one command. You can also specify the database name and schema name, but they are not required.
There is no CASCADE keyword for SQL Server. You’ll need to drop the constraints first, then drop the table.
MySQL
MySQL lets you use the IF EXISTS keyword to run the command only if the table exists.
You can drop a temporary table by using the TEMPORARY keyword. You can also drop multiple tables with a single statement in MySQL.
There are two additional options at the end of the statement: RESTRICT and CASCADE. They don’t actually do anything, and just exist to make porting to other databases easier.
If you want to remove the constraints that impact this table, you’ll need to drop the constraints first, then drop the table.
If you want to drop all tables in a database, I’ve written about several methods to do that here: MySQL Drop All Tables: How-To With Examples
PostgreSQL
PostgreSQL also includes an IF EXISTS keyword to run the command only if the table exists. You can also drop multiple tables with a single statement in PostgreSQL.
There are two parameters at the end of the statement:
- CASCADE: Automatically drop objects that depend on the table (such as views) and then all objects that depend on those objects.
- RESTRICT: Refuse to drop the table if any objects depend on it. This is the default.
SQL References
SQL Keywords
ADD
ADD CONSTRAINT
ALL
ALTER
ALTER COLUMN
ALTER TABLE
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE
MySQL Functions
String Functions:
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Numeric Functions:
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Date Functions:
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Advanced Functions:
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION
SQL Server Functions
String Functions:
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Numeric Functions:
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Date Functions:
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access Functions
String Functions:
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Numeric Functions:
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Date Functions:
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Other Functions:
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL Quick Ref
SQL Server drop table if exists create table
In real-life scenarios, you would have faced a situation, where you wanted to recreate a table instead of modifying its structure. In such situations, you have to drop the table first and then create it again.
Also, sometimes you want to create a table but do not know if you have already created a table with the same name. You will end up with an error saying that there is already an object with the same name in the database.
Therefore, it is always a good practice to wite the DROP TABLE statement before creating the table.
In this section, you will learn how you can verify the existence of the table in the database and create a table with the same name after dropping the already existing table.
You will see an example where we will be implementing the above method to create a new table.
Suppose you have to create a table SampleTable and insert values into it. But you mistakenly forgot to enclose the date values inside the quotes, resulting in an error.
This time you corrected your mistake and enclosed dates inside the quotes.
Resulted in Error
- The code resulted in an error because you have already created the table.
- Therefore, it is always a good practice to first drop the table if it exists.
- You can use the following code snippet before the CREATE TABLE statement to drop the table.
This time you will not get any error.
Table Created
Hence, you might have learned why we need to drop the table before creating it and how to do it.
Read: SQL Server Convert Datetime to date
DROP IF EXISTS
У инструкции DROP в Microsoft SQL Server 2016 появился дополнительный параметр IF EXISTS, который позволяет предварительно проверить существование объекта, перед его непосредственным удалением.
DROP с этим параметром значительно упрощает написание кода, так как в случае если Вам необходимо пересоздать таблицу, или просто удалить таблицу, предварительно не нужно писать различные дополнительные условные конструкции IF, чтобы проверить, существует ли эта таблица или нет. Инструкция DROP IF EXISTS сделает эту проверку сама.
Кстати, в других популярных СУБД, например, даже в бесплатной PostgresSQL, уже давно существует такая возможность, но компания Microsoft в свою СУБД добавила ее совсем недавно.
Инструкцию DROP IF EXISTS поддерживают следующие виды объектов Microsoft SQL Server (т.е. при их удалении можно использовать параметр IF EXISTS):
- TABLE – таблица;
- VIEW – представление;
- PROCEDURE – хранимая процедура;
- FUNCTION – функция;
- SEQUENCE – последовательность;
- TRIGGER – триггер;
- INDEX – индекс;
- DATABASE – база данных;
- SCHEMA – схема;
- ROLE — роль базы данных;
- RULE – правила;
- TYPE – псевдоним типа данных или определяемый пользователем тип данных;
- AGGREGATE – определяемые пользователем агрегатные функции;
- ASSEMBLY – сборка CLR;
- USER – пользователь базы данных;
- DEFAULT — объект «Значение по умолчанию»;
- SECURITY POLICY — политика безопасности;
- SYNONYM – синоним.
Пример DROP IF EXISTS – удаление таблицы или представления
Раньше, если перед удалением таблицы (или представления) необходимо было проверить, существует ли эта таблица, мы перед инструкцией по удалению таблицы (DROP) писали условную конструкцию IF в сочетании с функцией OBJECT_ID, код выглядел примерно следующим образом
IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
Теперь условную конструкцию можно опустить, добавив вместо нее параметр IF EXISTS.
DROP TABLE IF EXISTS dbo.TestTable;
Пример DROP IF EXISTS – удаление функции
Для того чтобы проверить существует ли функция перед удалением, можно перед инструкцией удаления писать следующую условную конструкцию (часто можно встретить именно такой подход).
IF EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.TestFunction')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION dbo.TestFunction;
Теперь все гораздо проще.
DROP FUNCTION IF EXISTS dbo.TestFunction;
У меня все, удачи!
Нравится1Не нравится
STRING_ESCAPE
One of the primary functions of a database system such as SQL Server is to store and retrieve data in its original form. Some data’s original form may consist of special characters as shown in Text 1 which makes use of several special characters such as single quotes, double quotes, forward slashes and a horizontal tab.
‘Jane”s birthday is “11/05/1975”
Text 1
To illustrate the usage of this new function, we have stored the aforementioned text into a @test1 variable as shown in Script 7.
|
1 |
DECLARE@test1 VARCHAR(800)=’Jane»s birthday is «11/05/1975″‘
|
Script 7
Then, we defined an @json variable to store a concatenated JSON string.
|
1 |
DECLARE@json_val VARCHAR(800)=»
|
Script 8
Figure 2 shows the results of a select statement against the @json variable.
|
1 |
SELECT@json_val |
Figure 2
However, when we validate the content returned in Figure 2 using the ISJSON function, we can see that zero (0) is returned which indicates the content is not of JSON type.
|
1 |
SELECT ISJSON(@json_val)isjson
|
Figure 3
The new STRING_ESCAPE allows us to handle the format of Text 1 better. As shown in Figure 4, we have revised our script to use the STRING_ESCAPE function and this results into a valid JSON data.
|
1 |
DECLARE@json_val VARCHAR(800)=»
SELECT@json_val |
|
1 |
SELECT ISJSON(@json_val)isjson
|
Figure 4
Команда ALTER TABLE
Иногда (довольно редко) возникает задача изменить таблицу. В SQLite для этого используется команда ALTER TABLE. В отличие от других СУБД, здесь она имеет не очень большие возможности.
Переименовать таблицу можно с помощью RENAME TO:
ALTER TABLE t1 RENAME TO t2;
Если нужно переименовать какой-то столбец в таблице, то используется RENAME COLUMN:
ALTER TABLE t1 RENAME COLUMN name TO title;
Для добавления нового столбца используется ADD COLUMN:
ALTER TABLE t1 ADD COLUMN slug TEXT DEFAULT '';
В данном примере мы добавили новое поле типом TEXT и значением по умолчанию DEFAULT.
Ну и с помощью DROP COLUMN можно удалить столбец.
ALTER TABLE t1 DROP COLUMN slug;
В SQLite нет команды MODIFY, с помощью которой можно было бы изменить тип столбца. В других базах, например MySQL есть возможность модифицировать поле, а в SQLite эту же операцию можно сделать, только созданием новое таблицы и копированием в неё данных из старой. Не очень удобно, но в случае необходимости, вопрос решается.
![Delete table in sql / drop table in sql [practical examples] | golinuxcloud](https://wudgleyd.ru/wp-content/uploads/1/1/2/1126c1c559d2620410f5ef4c41a51799.webp)
SQL Server Data Types Recommendation
More details about SQL Server data types and mapping it with another databases and program languages you can find here
| General Type | Type | ANSI | Recommended | What use instead | Why use or not |
|---|---|---|---|---|---|
| Exact Numerics | bit | No | Maybe | tinyint | convert any number (except 0) to 1, 0 converted to 0 |
| Exact Numerics | tinyint | No | Maybe | int | for saving 3 bytes compare to data type or for replacing data type |
| Exact Numerics | smallint | Yes | Maybe | int | for saving 2 bytes compare to data type |
| Exact Numerics | int | Yes | Yes | — | |
| Exact Numerics | bigint | No | Yes | int | if you work more than 2^31 numbers. |
| Exact Numerics | decimal | Yes | Yes | — | |
| Exact Numerics | smallmoney | No | Maybe | decimal | possibility to loss precision due to rounding errors |
| Exact Numerics | money | No | Maybe | decimal | possibility to loss precision due to rounding errors |
| Approximate Numerics | real | Yes | Yes | — | |
| Approximate Numerics | float(1-24) | Yes | No | real | SQL Server automatically converts to data type |
| Approximate Numerics | float(24-53) | Yes | Yes | — | |
| Date and Time | date | Yes | Yes | — | |
| Date and Time | smalldatetime | No | Maybe | date | |
| Date and Time | time | Yes | Yes | — | |
| Date and Time | datetime2 | No | Yes | — | |
| Date and Time | datetime | Yes | Maybe | datetime2 | On the Advantages of DateTime2(n) over DateTime |
| Date and time | datetimeoffset | No | Yes | — | |
| Character Strings | char | Yes | Maybe | varchar | Save 1 byte from , but be ready for trailing spaces |
| Character Strings | varchar | Yes | Yes | — | |
| Character Strings | varchar(max) | Yes | Yes | — | |
| Character Strings | nchar | Yes | Maybe | nvarchar | |
| Character Strings | nvarchar | Yes | Yes | — | |
| Character Strings | nvarchar(max) | Yes | Yes | — | |
| Character Strings | ntext | No | Deprecated | nvarchar(max) | NVARCHAR(MAX) VS NTEXT in SQL Server |
| Character Strings | text | No | Deprecated | varchar(max) | Differences Between Sql Server TEXT and VARCHAR(MAX) Data Type |
| Binary Strings | image | No | Deprecated | varbinary(max) | VARBINARY(MAX) Tames the BLOB |
| Binary Strings | binary | Yes | Deprecated | varbinary | Conversions between any data type and the binary data types are not guaranteed |
| Binary Strings | varbinary | Yes | Yes | — | |
| Binary Strings | varbinary(max) | Yes | Yes | — | |
| Other Data Types | cursor | No | Yes | — | |
| Other Data Types | sql_variant | No | Yes | — | |
| Other Data Types | hierarchyid | No | Yes | — | |
| Other Data Types | rowversion | No | Maybe | — | |
| Other Data Types | No | Deprecated | rowversion | it is just synonym to rowversion data type and must be removed | |
| Other Data Types | uniqueidentifier | No | Yes | — | |
| Other Data Types | xml | Yes | Yes | — | |
| Other Data Types | table | No | Maybe | — | |
| Spatial Data Types | geometry | No | Yes | — | |
| Spatial Data Types | geography | No | Yes | — |
DATEDIFF_BIG
Many of the KPIs (Key Performance Indicators) in Business Intelligence solutions involves determining how long it takes to complete tasks i.e. how long it takes to process an insurance claim based on the date the claim was logged versus the date it was completed. Since, its release in SQL Server 2008, the DATEDIFF function has been quite useful towards calculating such KPIs as shown in Script 9.
|
1 |
SELECT DATEDIFF(MONTH,GETDATE()-100,GETDATE())
|
Script 9
However, the limitation with DATEDIFF is that it returns an INT data type which is only limited to a range of 2,147,483,647. This range is not an issue when your DATEDIFF looks at differences in Days, Months, and Years. However, when trying to get differences in MILLISECOND you are likely to run into a SQL Server 535 error code as the returned value is too large to be held into an integer data type.
|
1 |
SELECT DATEDIFF(MILLISECOND,GETDATE()-100,GETDATE())
|
Msg 535, Level 16, State 0, Line 2
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Script 10
Luckily, in SQL Server 2016 we have a new function called DATEDIFF_BIG which can be used to return big integer values. Now, if we alter Script 9 to use DATEDIFF_BIG as shown in below, the statement successfully returns 8640000000.
|
1 |
SELECT DATEDIFF_BIG(MILLISECOND,GETDATE()-100,GETDATE())
|
Script 11
Example — DELETE Statement with One Condition
If you run a DELETE statement with no conditions in the WHERE clause, all of the records from the table will be deleted. As a result, you will most often include a WHERE clause with at least one condition in your DELETE statement.
Let’s start with a simple example of a DELETE query that has one condition in the WHERE clause.
In this example, we have a table called suppliers with the following data:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 100 | Microsoft | Redmond | Washington |
| 200 | Mountain View | California | |
| 300 | Oracle | Redwood City | California |
| 400 | Kimberly-Clark | Irving | Texas |
| 500 | Tyson Foods | Springdale | Arkansas |
| 600 | SC Johnson | Racine | Wisconsin |
| 700 | Dole Food Company | Westlake Village | California |
| 800 | Flowers Foods | Thomasville | Georgia |
| 900 | Electronic Arts | Redwood City | California |
Enter the following DELETE statement:
Try It
DELETE FROM suppliers WHERE supplier_name = 'Microsoft';
There will be 1 record deleted. Select the data from the suppliers table again:
SELECT * FROM suppliers;
These are the results that you should see:
| supplier_id | supplier_name | city | state |
|---|---|---|---|
| 200 | Mountain View | California | |
| 300 | Oracle | Redwood City | California |
| 400 | Kimberly-Clark | Irving | Texas |
| 500 | Tyson Foods | Springdale | Arkansas |
| 600 | SC Johnson | Racine | Wisconsin |
| 700 | Dole Food Company | Westlake Village | California |
| 800 | Flowers Foods | Thomasville | Georgia |
| 900 | Electronic Arts | Redwood City | California |
This example would delete all records from the suppliers table where the supplier_name is ‘Microsoft’.
You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SELECT statement before performing the delete:
Try It
SELECT COUNT(*) FROM suppliers WHERE supplier_name = 'Microsoft';
This quey will return number of records that will be deleted when you execute the DELETE statement.
The Purge Clause
Oracle has an optional clause, which you can use if you want to drop the table and release the space associated with it in a single step. If you specify , then the database does not place the table and its dependent objects into the recycle bin.
This is equivalent to first dropping the table and then purging it from the recycle bin, but it lets you save one step in the process.
Note that if you specify , you won’t be able to recover the table.
If you don’t specify , the statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user’s space quota.
Изменение и удаление представлений
Для изменения определения представления в языке Transact-SQL применяется инструкция ALTER VIEW. Синтаксис этой инструкции аналогичен синтаксису инструкции CREATE VIEW, применяющейся для создания представления.
Использование инструкции ALTER VIEW позволяет избежать переназначения существующих разрешений для представления. Кроме этого, изменение представления посредством этой инструкции не влияет на объекты базы данных, зависящие от этого представления. Если же модифицировать представление, сначала удалив его (инструкция DROP VIEW), а затем создав новое представление с требуемыми свойствами (инструкция CREATE VIEW), то все объекты базы данных, которые ссылаются на это представление, не будут работать должным образом, по крайней мере, в период времени между удалением представления и его воссоздания.
Использование инструкции ALTER VIEW показано в примере ниже:
В этом примере инструкция ALTER VIEW расширяет инструкцию SELECT в представлении view_WithoutBudget новым условием в предложении WHERE.
Инструкция DROP VIEW удаляет из системных таблиц определение указанного в ней представления. Применение этой инструкции показано в примере ниже:
При удалении представления инструкцией DROP VIEW все другие представления, основанные на удаленном, также удаляются, как показано в примере ниже:
Здесь инструкция DROP VIEW явно удаляет представление view_Consultant, при этом неявно удаляя представление view_project_p2, основанное на представлении view_Consultant. Теперь попытка выполнить запрос по представлению view_project_p2 возвратит сообщение об ошибке.
При удалении базовой таблицы представления, основанные на ней другие представления, не удаляются автоматически. Это означает, что все представления для удаленной таблицы нужно удалять явно, используя инструкцию DROP VIEW. С другой стороны, представления удаленной таблицы можно снова использовать на новой таблице, имеющей такую же логическую структуру, как и удаленная.
Разбиение строки в массив в SQL Server
Подход на основе таблицы чисел означает, что вы должны вручную создать таблицу, содержащую достаточно строк, чтобы самая длинная строка, которую вы разбиваете, не превысила их число.
IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.NumbersTest') ) BEGIN DROP TABLE NumbersTest; END; CREATE TABLE NumbersTest (Number INT NOT NULL); DECLARE @RunDate datetime = GETDATE() DECLARE @i INT = 1; WHILE @i BEGIN INSERT INTO dbo.NumbersTest(Number) VALUES (@i); SELECT @i = @i + 1; END; CREATE UNIQUE CLUSTERED INDEX n ON dbo.NumbersTest(Number) WITH (DATA_COMPRESSION = PAGE); GO
сжатие индекса может использоваться только в Enterprise версии SQL Server.
CREATE FUNCTION dbo.Split_Numbers ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) )
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT Item = SUBSTRING(@List, Number,
CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
FROM dbo.NumbersTest
WHERE Number
AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter);
С другой стороны, если мы используем метод CTE, он не потребует таблицы чисел. Вместо нее будет использоваться рекурсивное CTE для извлечения каждой части строки из «остатка» после предыдущей части.
CREATE FUNCTION dbo.Split_CTE ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS @Items TABLE (Item NVARCHAR(4000)) WITH SCHEMABINDING AS BEGIN DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter); WITH a AS ( SELECT = 1, = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @ll), = SUBSTRING(@List, 1, COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @ll) - 1) UNION ALL SELECT = CONVERT(INT, ) + @ld, = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, + @ld), 0), @ll), = SUBSTRING(@List, + @ld, COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, + @ld), 0), @ll)--@ld) FROM a WHERE ) INSERT @Items SELECT FROM a WHERE LEN() > 0 OPTION (MAXRECURSION 0); RETURN; END
DECLARE @Values NVARCHAR(MAX) =N'Value 1,Value 2,Value 3,Value 4,Value 5'; SELECT Item AS NumSplit FROM dbo.Split_Numbers (@Values, N','); SELECT Item AS CTESplit FROM dbo.Split_CTE (@Values, N',');
CREATE TABLE dbo.TestData ( string_typ INT, string_val NVARCHAR(MAX) ); CREATE CLUSTERED INDEX st ON dbo.TestData(string_len); CREATE TABLE #Temp(st NVARCHAR(MAX)); INSERT #Temp SELECT N'a,va,val,value,value1,valu,va,value12,,valu,value123,value1234'; GO INSERT dbo.TestData SELECT 1, st FROM #Temp; GO 10000 INSERT dbo.TestData SELECT 2, REPLICATE(st,10) FROM #Temp; GO 1000 INSERT dbo.TestData SELECT 3, REPLICATE(st,100) FROM #Temp; GO 100 INSERT dbo.TestData SELECT 4, REPLICATE(st,1000) FROM #Temp; GO 10 INSERT dbo.TestData SELECT 5, REPLICATE(st,10000) FROM #Temp; GO DROP TABLE #Temp; GO -- убираем концевую запятую UPDATE dbo.TestData SET string_val = SUBSTRING(string_val, 1, LEN(string_val)-1) + 'x';
SELECT func.Item FROM dbo.TestData AS tst CROSS APPLY dbo.Split_CTE(tst.string_val, ',') AS func WHERE tst.string_typ = 1; -- Значения string_typ от 1-5 SELECT func.Item FROM dbo.TestData AS tst CROSS APPLY dbo.Split_Numbers(tst.string_val, ',') AS func WHERE tst.string_typ = 1; -- Значения string_typ от 1-5
Знакомимся с MS SQL 2016
Новая версия разрабатывалась воистину ударными темпами. Не успели познакомиться с версией 2014 (вышла 1 апреля 2014 года), как буквально через год, в мае 2015-го, на конференции Ignite был представлен очередной релиз, а для загрузки выложили сразу Community Technology Preview 2. Финальная версия вышла 1 июня 2016 года. В новом релизе MS, очевидно, сделала шаг в направлении общей кодовой базы SQL Server и Azure SQL Database, что довольно логично. Функции, ранее появившиеся в Azure SQL DB (вроде Row-Level Security и Dynamic Data Masking), теперь доступны и для локального сервера.
Вместе с релизом SQL Server 2016 MS представила агрессивную программу перехода с СУБД лидера рынка Oracle (занимает примерно 40% против 21% у MS), включающую «бесплатную» лицензию (заплатить нужно будет за подписку на Software Assurance), инструменты для миграции и обучение персонала. При построении БД с нуля СУБД от Miсrosoft обойдется на порядок дешевле. Предлагаются две лицензии: одна основана на вычислительных мощностях (Core-based), вторая — на количестве пользователей или устройств (Server + Client Access License). Oracle работает под Linux, и здесь у MS тоже есть сюрприз: анонсировано, что новая версия также будет работать под управлением этой ОС. Хотя сам релиз под Linux выйдет примерно в середине 2017 года.
Реализовано пять версий: Enterprise, Standard, Express, Developer и Web. Версия Developer Edition доступна бесплатно и обладает всеми возможностями Enterprise, но предназначена исключительно для разработки и тестирования, ее нельзя использовать в рабочих средах. Числовые показатели по сравнению с 2014 практически не изменились. Максимальный размер баз данных 524 Пбайт, у Express — 10 Гбайт. Максимальный объем используемой памяти на экземпляр: Express — до 1 Гбайт, Standard — 128 Гбайт, остальные ограничены возможностями ОС. Максимальное количество ядер: Express — до четырех, Standard — 24 (в 2014 — 16).
Как принято, новый релиз поддерживает минус одно поколение ОС. Из списка выпали Win7 и Win2k8. Возможна установка SQL Server на все x64-редакции Windows от 8 и Win2012, в том числе и урезанные Core и Nano. Особо отмечается, что процессоры x86 больше не поддерживаются.
С нового релиза SQL Server Management Studio (SSMS) поставляется отдельно, а сам он теперь управляет всеми редакциями SQL Server от 2008 (включая будущую SQL 2016 под Linux). То есть теперь не придется держать несколько SSMS для работы с разными релизами СУБД. Установка SSMS возможна на Win7SP1+/Win2k8+.
Выбор компонентов при установке MS SQL 2016
Другие статьи в выпуске:
Xakep #210. Краткий экскурс в Ethereum
- Содержание выпуска
- Подписка на «Хакер»-60%
В SSMS появилось несколько полезных возможностей. Например, Live Query Statistics позволяет наблюдать за ходом выполнения запроса. Полученные сведения (время, количество данных, процент выполнения) можно использовать для оптимизации. Активируется функция при помощи кнопки Include Live Query Statistics, в сессии должен быть включен сбор статистики (, ). Модуль PowerShell для работы с MS SQL sqlps, идущий в комплекте SQL 2016, поддерживает управление всеми версиями от SQL2k (конечно, в более ранних версиях сервера будут недоступны некоторые функции). Также изменения коснулись набора дополнений к Visual Studio — SQL Server Data Tools для Visual Studio. Раньше были доступны две версии: собственно SSDT и SSDT-BI (Business Intelligence), теперь они объединены в один пакет.
В документе SQL Server 2016 and Windows Server 2016 Better Together разработчики приводят примеры, почему только сочетание MS Server 2016 и SQL Server 2016 обеспечит максимальную безопасность и производительность. Например, Win2016 поддерживает технологию энергонезависимой памяти Storage Class Memory (SCM), в том числе и NVDIMM — они заменяют обычные DIMM-модули памяти, но умеют хранить информацию при потере питания. Применяя их, можно добиться существенного прироста производительности. С помощью технологии Storage Spaces Direct на основе стандартных серверов с локальным хранилищем можно создать высокодоступное и масштабируемое хранилище данных.
Еще две технологии — Just-In-Time (JIT) и Just Enough Administration (JEA) — позволяют ограничить администратора в правах по времени или при помощи PowerShell делегировать пользователю ровно те права, которые ему нужны, чтобы выполнять работу.
В SSMS теперь можно следить за ходом запроса
Official Reference and useful links
- Transact-SQL Formatting Standards (by Robert Sheldon)
- Subjectivity: Naming Standards (by Aaron Bertrand)
- General Database Conventions (by Thomas Kejser)
- Writing Readable SQL (by Red Gate)
- SQL Style Guide (by Simon Holywell)
- SQL Code Layout and Beautification (by William Brewer)
- TSQL Coding Style (by Gregory Larsen)
- User-Defined Functions
- Synonyms (Database Engine)
- Primary and Foreign Key Constraints
- sys.objects
- SQL Server Constraints
- CHECK Constraint TECHNET
- SQL Server CLR Integration
- Deploying CLR Database Objects
- CLR Stored Procedures
- User-defined Functions
- SET NOCOUNT ON (Transact-SQL)
- T-SQL Coding Guidelines Presentation (by Chris Adkin)
- Sql Coding Style
- SQL Server Code Review Checklist for Developers (by Samir Behara)
- SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis (by ApexSQL)
- In The Cloud: The Importance of Being Organized
- Naming Conventions in Azure
- The Basics of Good T-SQL Coding Style – Part 3: Querying and Manipulating Data
- SQL naming conventions (by Phi Factor)
- SQL Server Compact Object Limitations
- Dos and Don’ts of Dynamic SQL (by Thom Andrews)
Внешние ключи FOREIGN KEY
Когда мы связывали несколько таблиц, то использовали какое-то поле, которое ссылается на другую таблицу. Фактически таблицы полностью самостоятельны, а связь мы организуем через sql-запрос. Но SQLite (и другие СУБД) позволяют организовать такую связь прямо при создании таблиц.
Когда создаётся FOREIGN KEY для таблиц, то возникают дополнительные условия хранения и изменения данных в связанных полях. Например, если мы попытаемся удалить запись из родительской таблицы, но при этом останется ссылка в дочерней, то это приведёт к ошибке — вначале нужно будет удалить данные из дочерней таблицы. Так проверяется целостность данных.
Таких ситуаций может быть много — они связаны и с добавлением данных, и удалением, и обновлением.
Для чего же тогда были придуманы FOREIGN KEY? В первую очередь для того, чтобы избавиться от «проблемных» данных и проверка на уровне БД позволяет гарантировать целостность и корректность данных. Если мы вручную вносим данные в базу, то логично было бы получить и проверку.
На практике же такие проверки выполняю готовые программы. Скажем при добавлении автора, php-скрипт сделает запрос, чтобы убедиться, что указанный автор действительно существует. Или при удалении пользователя, также вначале нужно убедится, что он есть, а потом скрипт удалит все данные пользователя во всех таблицах базы. Такой алгоритм работы объясняется безопасностью, а также тем правилом, что нельзя доверять любым входящим данным. Поэтому многочисленные проверки стали нормой, что делают не нужными проверки уровня FOREIGN KEY.
База SQLite и основы SQL. Часть 11
База SQLite и основы SQL — 9. Встроенные функции
