Mysql insert into select
Содержание:
- SQL References
- Добавление данных. Команда Insert
- INSERT INTO TABLE statement
- Example — Using VALUES keyword
- Множественный UPDATE
- Пример — использование оператора INSERT для вставки нескольких записей
- Get Inserted ID
- Практическое упражнение № 2:
- SQL References
- Table of contents
- INSERT INTO Example
- INSERT INTO TABLE example
SQL References
SQL Keywords
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
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
Добавление данных. Команда Insert
Последнее обновление: 13.07.2017
Для добавления данных применяется команда INSERT, которая имеет следующий формальный синтаксис:
INSERT имя_таблицы VALUES (значение1, значение2, ... значениеN)
Вначале идет выражение INSERT INTO, затем в скобках можно указать список столбцов через запятую, в которые надо добавлять данные, и
в конце после слова VALUES скобках перечисляют добавляемые для столбцов значения.
Например, пусть ранее была создана следующая база данных:
CREATE DATABASE productsdb; GO USE productsdb; CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL )
Добавим в нее одну строку с помощью команды INSERT:
INSERT Products VALUES ('iPhone 7', 'Apple', 5, 52000)
После удачного выполнения в SQL Server Management Studio в поле сообщений должно появиться сообщение «1 row(s) affected»:
Стоит учитывать, что значения для столбцов в скобках после ключевого слова VALUES передаются по порядку их объявления. Например, в выражении
CREATE TABLE выше можно увидеть, что первым столбцом идет Id. Но так как для него задан атрибут IDENTITY, то значение этого столбца автоматически генерируется, и его можно не указывать.
Второй столбец представляет ProductName, поэтому первое значение — строка «iPhone 7» будет передано именно этому столбцу.
Второе значение — строка «Apple» будет передана третьему столбцу Manufacturer и так далее. То есть значения передаются столбцам
следующим образом:
-
ProductName: ‘iPhone 7’
-
Manufacturer: ‘Apple’
-
ProductCount: 5
-
Price: 52000
Также при вводе значений можно указать непосредственные столбцы, в которые будут добавляться значения:
INSERT INTO Products (ProductName, Price, Manufacturer)
VALUES ('iPhone 6S', 41000, 'Apple')
Здесь значение указывается только для трех столбцов. Причем теперь значения передаются в порядке следования столбцов:
-
ProductName: ‘iPhone 6S’
-
Manufacturer: ‘Apple’
-
Price: 41000
Для неуказанных столбцов (в данном случае ProductCount) будет добавляться значение по умолчанию, если задан атрибут DEFAULT, или
значение NULL. При этом неуказанные столбцы должны допускать значение NULL или иметь атрибут DEFAULT.
Также мы можем добавить сразу несколько строк:
INSERT INTO Products
VALUES
('iPhone 6', 'Apple', 3, 36000),
('Galaxy S8', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000)
В данном случае в таблицу будут добавлены три строки.
Также при добавлении мы можем указать, чтобы для столбца использовалось значение по умолчанию с помощью ключевого слова DEFAULT или значение NULL:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES ('Mi6', 'Xiaomi', DEFAULT, 28000)
В данном случае для столбца ProductCount будет использовано значение по умолчанию (если оно установлено, если его нет — то NULL).
Если все столбцы имеют атрибут DEFAULT, определяющий значение по умолчанию, или допускают значение NULL, то можно для всех столбцов вставить
значения по умолчанию:
INSERT INTO Products DEFAULT VALUES
Но если брать таблицу Products, то подобная команда завершится с ошибкой, так как несколько полей не имеют атрибута DEFAULT и при этом не допускают значение NULL.
НазадВперед
INSERT INTO TABLE statement
There is no point in creating database structures and not having any data in the database. The INSERT INTO TABLE statement is the one we’ll use to solve this problem. It is one of the four important SQL DML (Data Manipulation Language) statements SELECT … FROM, INSERT INTO TABLE, UPDATE … SET, and DELETE FROM… The complete syntax is pretty complex since the INSERT INTO could also be a very complex statement. Please take a look at the T-SQL INSERT INTO TABLE complete syntax here.
In this article we’ll use simplified, but also most common syntax:
INSERT INTO table_name (column_list) VALUES (column_values);
In such INSERT INTO statement, you’ll need to define the table_name where you’re inserting data into, list all columns (maybe you’ll use all of them, but maybe only a few of them), and then list all values. Notice that column values should match column types definition (e.g., you can’t insert a textual value into the numerical column/attribute).
In case you’re inserting all values in the table, you don’t need to list all columns after the table_name and you could use even more simplified syntax:
INSERT INTO table_name VALUES (column_values);
I personally prefer listing all column names, because this approach would work even if we add new columns to the existing table.
Note: The INSERT INTO TABLE statement could be written in such manner we insert multiple rows with 1 statement or even combined with the SELECT statement.
The simplified syntax for one such case where INSERT and SELECT statements are combined is given below:
INSERT INTO destination_table (column_list, …)
SELECT column_list
FROM source_table
WHERE condition;
Example — Using VALUES keyword
The simplest way to create a SQL Server INSERT query to list the values using the VALUES keyword.
For example:
INSERT INTO employees (employee_id, last_name, first_name) VALUES (10, 'Anderson', 'Sarah');
This SQL Server INSERT statement would result in one record being inserted into the employees table. This new record would have an employee_id of 10, a last_name of ‘Anderson’, and a first_name of ‘Sarah’.
You can also use this syntax to insert more than one record at a time. For example:
INSERT INTO employees (employee_id, last_name, first_name) VALUES (10, 'Anderson', 'Sarah'), (11, 'Johnson', 'Dale');
This INSERT example shows how to insert more than one record using the VALUES keyword. In this example, two records are inserted into the employees table. The first record has an employee_id of 10, a last_name of ‘Anderson’, and a first_name of ‘Sarah’. The second record has an employee_id of 11, a last_name of ‘Johnson’, and a first_name of ‘Dale’.
This would be equivalent to the following two INSERT statements:
Множественный UPDATE
Учебный тренинг. SQL от новичка до профессионала. Бесплатные авторские видеокурсы SQL, PLSQL, JAVAЧалышев Максим Михайловичизучаем SQL группа в контакте — присоединяйтесьИтак, небольшая заметка по операторы UPDATEпредположим что нам необходимо заменить значения в таблице 1 , данными из таблицы 2можно конечно написать что то вроде
update t1 set t1.f1=select t2.f1 from t2 where t2.key1 = t1.key
но у нас есть более легкий способ произвести подобную операцию
UPDATE
(
SELECT
t.c1,
t.c2,
s.c1 AS c1_new,
s.c2 AS c2_new
FROM table1 t
INNER JOIN table2 s ON s.key=t.key
)
tt
SET tt.c1=tt.c1_new, tt.c2=tt.c2_new
Где table1, table2 этоТо есть на основании данных из одной таблицы мы обновляем данные в другой таблице, важно наличие первичного ключа в каждой из представленных таблицприведем пример демонстрирующий работу UPDATE с несколькими таблицами
create table PHONES1 ( PHONENUM VARCHAR2(48) not null primary key, NAME VARCHAR2(48) ); create table PHONES ( PHONENUM VARCHAR2(48) not null primary key, NAME VARCHAR2(48) );
заполним данными и выполним UPDATE
INSERT INTO PHONES1(PHONENUM, name) values ('495 1211133','Алексеев1');
INSERT INTO PHONES1(PHONENUM, name) values ('499 3311133','Михайлов1');
INSERT INTO PHONES1(PHONENUM, name) values ('917 12122sd3','Коробочкин1');
INSERT INTO PHONES1(PHONENUM, name) values ('499 33111133','Костин1');
INSERT INTO PHONES(PHONENUM, name) values ('495 1211133','Алексеев');
INSERT INTO PHONES(PHONENUM, name) values ('499 3311133','Михайлов');
INSERT INTO PHONES(PHONENUM, name) values ('917 12122sd3','Коробочкин');
INSERT INTO PHONES(PHONENUM, name) values ('499 33111133','Костин');
INSERT INTO PHONES(phonenum, name) values ('214 331das1133','Докучаев');
UPDATE (SELECT t.phonenum AS phonenum1, t.phonenum phonenum2, s.name AS name1, t.name AS name_new FROM phones1 t
INNER JOIN phones s ON s.phonenum=t.phonenum ) Tt SET tt.name1=tt.name_new
результат — обновление данных в phones из таблицы phones1
Пример — использование оператора INSERT для вставки нескольких записей
Поместив оператор SELECT в оператор INSERT, вы можете быстро выполнить несколько операций вставки. Давайте рассмотрим пример того, как это сделать.
В этом примере у нас есть таблица employees со следующими данными:
| employee_number | first_name | last_name | salary | dept_id |
|---|---|---|---|---|
| 1001 | Justin | Bieber | 62000 | 500 |
| 1002 | Selena | Gomez | 57500 | 500 |
| 1003 | Mila | Kunis | 71000 | 501 |
| 1004 | Tom | Cruise | 42000 | 501 |
И таблица customers со следующими данными:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Теперь давайте вставим некоторую информацию из таблицы employees в таблицу customers:
PgSQL
INSERT INTO customers
(customer_id, last_name, first_name)
SELECT employee_number AS customer_id,
last_name,
first_name
FROM employees
WHERE employee_number < 1003;
|
1 |
INSERTINTOcustomers (customer_id,last_name,first_name) SELECTemployee_numberAScustomer_id, last_name, first_name FROMemployees WHEREemployee_number<1003; |
СОВЕТ: С этим типом INSERT некоторые базы данных требуют от вас псевдонимов имен столбцов в SELECT, чтобы они соответствовали именам столбцов таблицы, в которую вы вставляете. Как вы можете видеть в приведенном выше примере, мы связали первый столбец в операторе SELECT с customer_id.
Будет вставлено 2 записи. Выберите данные из таблицы customers еще раз:
PgSQL
SELECT *
FROM customers;
|
1 |
SELECT* FROMcustomers; |
Вот результаты, которые вы должны получить:
| customer_id | first_name | last_name | favorite_website | |
|---|---|---|---|---|
| 4000 | Justin | Bieber | google.com | |
| 5000 | Selena | Gomez | bing.com | |
| 6000 | Mila | Kunis | yahoo.com | |
| 7000 | Tom | Cruise | oracle.com | |
| 8000 | Johnny | Depp | NULL | |
| 9000 | Russell | Crowe | google.com | |
| 1001 | Justin | Bieber | 62000 | NULL |
| 1002 | Selena | Gomez | 57500 | NULL |
В этом примере последние 2 записи в таблице customers были вставлены с использованием данных из таблицы employees.
Get Inserted ID
For tables with an auto increment id field, you can get the id of the row you
just inserted by asking the result object.
Note: To be able to get the inserted id, only one row can be inserted.
Example
Insert a record in the «customers» table, and return the ID:
var mysql = require(‘mysql’);var con = mysql.createConnection({
host: «localhost», user: «yourusername», password: «yourpassword»,
database: «mydb»
});con.connect(function(err) { if (err) throw err; var sql = «INSERT INTO customers (name, address)
VALUES (‘Michelle’, ‘Blue Village 1’)»;
con.query(sql, function (err, result) { if (err) throw err; console.log(«1 record inserted,
ID: » + result.insertId); });});
Save the code above in a file called «demo_db_insert_id.js», and run the file:
Run «demo_db_insert_id.js»
C:\Users\Your Name>node demo_db_insert_id.js
Which will give you something like this in return:
1 record inserted, ID: 15
❮ Previous
Next ❯
Практическое упражнение № 2:
На основании таблицы contacts и customers, вставить в таблицу contacts всех customers, которые проживают в штате Nevada.
Oracle PL/SQL
—создаем contacts
CREATE TABLE contacts
( contact_id number(10) not null,
last_name varchar2(50) not null,
first_name varchar2(50) not null,
address varchar2(50),
city varchar2(50),
state varchar2(20),
zip_code varchar2(10),
CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);
—создаем customers
CREATE TABLE customers
( customer_id number(10) not null,
last_name varchar2(50) not null,
first_name varchar2(50) not null,
address varchar2(50),
city varchar2(50),
state varchar2(20),
zip_code varchar2(10),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);
|
1 |
—создаем contacts CREATETABLEcontacts last_namevarchar2(50)notnull, first_namevarchar2(50)notnull, addressvarchar2(50), cityvarchar2(50), statevarchar2(20), zip_codevarchar2(10), CONSTRAINTcontacts_pkPRIMARYKEY(contact_id) CREATETABLEcustomers last_namevarchar2(50)notnull, first_namevarchar2(50)notnull, addressvarchar2(50), cityvarchar2(50), statevarchar2(20), zip_codevarchar2(10), CONSTRAINTcustomers_pkPRIMARYKEY(customer_id) |
Решение для практического упражнения № 2:
Следующий оператор Oracle INSERT вставит запись в таблицу customers:
Oracle PL/SQL
INSERT INTO contacts
(contact_id, last_name, first_name, address, city, state, zip_code)
SELECT customer_id,
last_name,
first_name,
address,
city,
state,
zip_code
FROM customers
WHERE state = ‘Nevada’;
|
1 |
INSERTINTOcontacts SELECTcustomer_id, last_name, first_name, address, city, state, zip_code FROMcustomers WHEREstate=’Nevada’; |
Так как количество полей в таблицах contacts и customers одинаковы и поля перечислены в том же порядке, то вы могли бы записать решение следующим образом (хотя это, как правило, лучшая практика, перечислить весь список имен столбцов в случае изменения определения таблицы):
Oracle PL/SQL
INSERT INTO contacts
SELECT *
FROM customers
WHERE state = ‘Nevada’;
|
1 |
INSERTINTOcontacts SELECT* FROMcustomers WHEREstate=’Nevada’; |
SQL References
SQL Keywords
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
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
Table of contents
| Learn SQL: CREATE DATABASE & CREATE TABLE Operations | |
| Learn SQL: INSERT INTO TABLE | |
| Learn SQL: Primary Key | |
| Learn SQL: Foreign Key | |
| Learn SQL: SELECT statement | |
| Learn SQL: INNER JOIN vs LEFT JOIN | |
| Learn SQL: SQL Scripts | |
| Learn SQL: Types of relations | |
| Learn SQL: Join multiple tables | |
| Learn SQL: Aggregate Functions | |
| Learn SQL: How to Write a Complex SELECT Query | |
| Learn SQL: The INFORMATION_SCHEMA Database | |
| Learn SQL: SQL Data Types | |
| Learn SQL: Set Theory | |
| Learn SQL: User-Defined Functions | |
| Learn SQL: User-Defined Stored Procedures | |
| Learn SQL: SQL Views | |
| Learn SQL: SQL Triggers | |
| Learn SQL: Practice SQL Queries | |
| Learn SQL: SQL Query examples | |
| Learn SQL: Create a report manually using SQL queries | |
| Learn SQL: SQL Server date and time functions | |
| Learn SQL: Create SQL Server reports using date and time functions | |
| Learn SQL: SQL Server Pivot Tables | |
| Learn SQL: SQL Server export to Excel | |
| Learn SQL: Intro to SQL Server loops | |
| Learn SQL: SQL Server Cursors | |
| Learn SQL: SQL Best Practices for Deleting and Updating data | |
| Learn SQL: Naming Conventions | |
| Learn SQL: SQL-Related Jobs | |
| Learn SQL: Non-Equi Joins in SQL Server | |
| Learn SQL: SQL Injection | |
| Learn SQL: Dynamic SQL |
INSERT INTO Example
The following SQL statement inserts a new record in the «Customers» table:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES (‘Cardinal’, ‘Tom B. Erichsen’, ‘Skagen 21’, ‘Stavanger’, ‘4006’, ‘Norway’);
The selection from the «Customers» table will now look like this:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 89 | White Clover Markets | Karl Jablonski | 305 — 14th Ave. S. Suite 3B | Seattle | 98128 | USA |
| 90 | Wilman Kala | Matti Karttunen | Keskuskatu 45 | Helsinki | 21240 | Finland |
| 91 | Wolski | Zbyszek | ul. Filtrowa 68 | Walla | 01-012 | Poland |
| 92 | Cardinal | Tom B. Erichsen | Skagen 21 | Stavanger | 4006 | Norway |
Did you notice that we did not insert any number into the CustomerID
field?The CustomerID column is
an auto-increment field and will be
generated automatically when a new record is inserted into the table.
INSERT INTO TABLE example
Before doing anything, let’s check what is stored in our tables. This is the model we have created in the previous article. You can see that we have one table where we’ll store data related to countries and another one for data related to cities. They are also related to each other, but we’ll talk about that in the following article:
In order to check the contents of these two tables, we’ll use two simple SELECT statements:
|
1 |
SELECT*FROMcountry; SELECT*FROMcity; |
While SELECT is not the topic of this article, it should be mentioned that its’ basic syntax is:
SELECT 1 or more attributes FROM table;
The star (*) after SELECT represents that we want to show the values of all attributes/columns from that table in the query result.
As expected, there is nothing in these two tables, and SQL Server returns the result, as shown in the picture below. Statements return names of the columns from the tables we used in the SELECT query, but there is nothing under these column names. You can look at this as an empty Excel sheet with defined column names (headers). You know what type of data should be there, but there is nothing:
Now, we’ll need to change that.
First, we’ll populate the country table using the following INSERT INTO TABLE statements:
|
1 |
INSERTINTOcountry (country_name,country_name_eng,country_code)VALUES(‘Deutschland’,’Germany’,’DEU’); INSERTINTOcountry (country_name,country_name_eng,country_code)VALUES(‘Srbija’,’Serbia’,’SRB’); INSERTINTOcountry (country_name,country_name_eng,country_code)VALUES(‘Hrvatska’,’Croatia’,’HRV’); INSERTINTOcountry (country_name,country_name_eng,country_code)VALUES(‘United Stated of America’,’United Stated of America’,’USA’); INSERTINTOcountry (country_name,country_name_eng,country_code)VALUES(‘Polska’,’Poland’,’POL’); |
Data for five countries were successfully inserted. The result is shown in the picture below. Since we had 5 INSERT INTO TABLE statements we have 1 “(1 row affected)” message for each of these five commands in the “Messages” section:
Please note that all values (after VALUES) were ordered in the same manner in which we listed columns (after INSERT INTO country). All three values are texts. The query would work even if we haven’t ordered them in the right manner because all of them have the same data type (text), but the data would be stored in the wrong columns. In that case, we would have a semantic error.
The next thing we need to do is to populate the city table. We’ll do that using the following statements:
|
1 |
INSERTINTOcity (city_name,lat,long,country_id)VALUES(‘Berlin’,52.520008,13.404954,1); INSERTINTOcity (city_name,lat,long,country_id)VALUES(‘Belgrade’,44.787197,20.457273,2); INSERTINTOcity (city_name,lat,long,country_id)VALUES(‘Zagreb’,45.815399,15.966568,3); INSERTINTOcity (city_name,lat,long,country_id)VALUES(‘New York’,40.73061,-73.935242,4); INSERTINTOcity (city_name,lat,long,country_id)VALUES(‘Los Angeles’,34.052235,-118.243683,4); INSERTINTOcity (city_name,lat,long,country_id)VALUES(‘Warsaw’,52.237049,21.017532,5); |
After executing these statements, this was the result. As expected, 6 rows were added. And once more we have 1 message for each insert in the Messages section:
In this case, we would have a problem if we haven’t listed values in the same manner, we listed columns because their data types are not the same (they are – in order: text, decimal number, decimal number, integer). This type of error is called syntax error and the DBMS itself would prevent the query from running at all.
