| SQL Databases | SQL Databases | |
|---|---|---|
| Data Storage Model | Tables with fixed rows and columns | Document: JSON documents, Key-value: key-value pairs, Wide-column: tables with rows and dynamic columns, Graph: nodes and edges |
| Development History | Developed in the 1970s with a focus on reducing data duplication | Developed in the late 2000s with a focus on scaling and allowing for rapid application change driven by agile and DevOps practices. |
| Examples | Oracle, MySQL, Microsoft SQL Server, and PostgreSQL | Document: MongoDB and CouchDB, Key-value: Redis and DynamoDB, Wide-column: Cassandra and HBase, Graph: Neo4j and Amazon Neptune |
| Primary Purpose | General purpose | Document: general purpose, Key-value: large amounts of data with simple lookup queries, Wide-column: large amounts of data with predictable query patterns, Graph: analyzing and traversing relationships between connected data |
| Schemas | Rigid | Flexible |
| Multi-Record ACID Transactions | Supported | Most do not support multi-record ACID transactions. However, some—like MongoDB—do. |
To put the join condition NAME = OWNER into a query using the traditional approach is simply to list it in the WHERE clause as a rule. So...
SELECT * FROM car,driver WHERE owner = name;
To put the join condition NAME = OWNER into a query using the modern approach, you rewrite the FROM line to say:
FROM table1 JOIN table2 ON (rules)
So in our example:
SELECT * FROM car JOIN driver ON (owner = name);
UPDATE Table SET `Column` = REPLACE(Column,'Find_String_Start', 'Replace_With')
SELECT ID, Column, substr(Column, instr(Column,'Find_String_Start'), LOCATE('Find_String_End',Column,instr(Column,'Find_String_Start')+2)-instr(Column,'Find_String_Start') +2) as temp from Table where `Column` LIKE '%Find_String_Start%'
UPDATE Table SET `Column` = REPLACE(Column,substr(Column, instr(Column,'Find_String_Start'), LOCATE('Find_String_End',Column,instr(Column,'Find_String_Start')+2)-instr(Column,'Find_String_Start') +2), 'Replace_With') where `Column` LIKE
UPDATE `Table1`,`Table2`
SET `Table1`.`column1` = `Table2`.`column1`
WHERE `Table1`.`column2` = `Table2`.`column3`
COLLATE utf8_unicode_ci
SELECT count(*), column
FROM Table
GROUP BY column
HAVING COUNT(*) > 1
SELECT column, COUNT(*) NewColumn FROM Table GROUP BY column DESC HAVING NewColumn > 1 ORDER BY DESC COUNT(*)
INSERT INTO table2 (st_id,uid,changed,status,assign_status)
SELECT st_id,from_uid,now(),'Pending','Assigned'
FROM table1
Backup: # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
Backup: # mysqldump -u root --all-databases > dump.sql
Backup: # mysqldump -u root --databases db1 db2 db3 > dump.sql
Restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/
در زمانی که عدم امکان حروف بزرگ
lower_case_table_names=2
mysql - Where to change the value of lower_case_table_names=2 on windows xampp
foreach ($_POST as $key => $value) {
$_POST[$key] = htmlspecialchars($value, ENT_QUOTES,"UTF-8");
}
$newpost = array_map ( 'htmlspecialchars' , $_POST );
SELECT (9) DISTINCT (11) TOP (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) WITH {CUBE | ROLLUP} (7) HAVING (10) ORDER BY
The first noticeable aspect of SQL that is different than other programming languages is the order in which the code is processed. In most programming languages, the code is processed in the order in which it is written. In SQL, the first clause that is processed is the FROM clause, while the SELECT clause, which appears first, is processed almost last.
Each step generates a virtual table that is used as the input to the following step. These virtual tables are not available to the caller (client application or outer query). Only the table generated by the final step is returned to the caller. If a certain clause is not specified in a query, the corresponding step is simply skipped. Brief Description of Logical Query Processing Phases
Don't worry too much if the description of the steps doesn't seem to make much sense for now. These are provided as a reference. Sections that come after the scenario example will cover the steps in much more detail.
01 FROM: A Cartesian product (cross join) is performed between the first two tables in the FROM clause, and as a result, virtual table VT1 is generated.
02 ON: The ON filter is applied to VT1. Only rows for which the is TRUE are inserted to VT2.
03 OUTER (join): If an OUTER JOIN is specified (as opposed to a CROSS JOIN or an INNER JOIN), rows from the preserved table or tables for which a match was not found are added to the rows from VT2 as outer rows, generating VT3. If more than two tables appear in the FROM clause, steps 1 through 3 are applied repeatedly between the result of the last join and the next table in the FROM clause until all tables are processed.
04 WHERE: The WHERE filter is applied to VT3. Only rows for which the is TRUE are inserted to VT4.
05 GROUP BY: The rows from VT4 are arranged in groups based on the column list specified in the GROUP BY clause. VT5 is generated.
06 CUBE | ROLLUP: Supergroups (groups of groups) are added to the rows from VT5, generating VT6.
07 HAVING: The HAVING filter is applied to VT6. Only groups for which the is TRUE are inserted to VT7.
08 SELECT: The SELECT list is processed, generating VT8.
09 DISTINCT: Duplicate rows are removed from VT8. VT9 is generated.
10 ORDER BY: The rows from VT9 are sorted according to the column list specified in the ORDER BY clause. A cursor is generated (VC10).
11 TOP: The specified number or percentage of rows is selected from the beginning of VC10. Table VT11 is generated and returned to the caller.
سوالات SQL در مصاحبهها هستند، میپردازیم:
1. دستور SQL برای یافتن دومین حقوق بیشتر کارمندان را بنویسید
به روشهای مختلفی میتوانید به این سوال پاسخ دهید. میتوانید از SQL Joint یا Subquery برای حل این مسأله استفاده کنید. این سوال را میتوان با استفاده از دستور زیر حل کرد:
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee);.
2. دستور SQL برای یافتن حقوق ماکسیمم در هر بخش را بنویسید
می توانید حقوق ماکسیمم در هر بخش را با گروه بندی کردن همه پروندهها انجام دهید. با استفاده از DepID و سپس با کمک دستور Max، حقوق ماکسیمم در هر گروه یا بخش را محاسبه کنید:
SELECT DepID,Max(Salary) FROM Empoloyee GROUP BY DepID
.
3. دستور SQL برای نشان دادن تاریخ را بنویسید
SQLدستوری دارد که میتوانید با آن تاریخ را بیابید. اگر از این دستور استفاده کنید، نمایش تاریخ برمی گردد. این دستور در سرورSQL مایکروسافت و دیگر نرم افزارها، مثل Oracle و MySQL کار میکند.
SELECT GetDate();
4. دستور SQLرا بنویسید تا بررسی کند که آیا تاریخی که در دستور عنوان شده با تاریخ فرمت یکی است یا خیر
SQL فرمانی در خودش دارد که فرمت خاص تاریخ داده شده را بررسی کند. میتوانید از فرمان IaDate() برای این هم استفاده کنید. فرمان MSSQL هم هست که شاید در Oracle یا MySQL یا هر پایگاه دادهای دیگری پشتیبانی نشود.
SELECT ISDate)’1/08/13’) AS “MM/DD/YY”);
5. دستور SQL برای پرینت نام کارمندی مشخص که DOB وی بین 01/01/1960 تا 31/12/1975 باشد
این دستوری زیرکانه است که میتوانید با استفاده از آن در عبارت، تمام پروندههایی که به شامل این تاریخ هستند را به دست آورید. با ترکیب زیر پاسخ را مییابید.
SELECT DISTINCT EmpName FROM Employees WHERE DOB BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;
6. دستور SQL بنویسید که شماره کارمندی را با توجه به جنسیتش بیابد که DOB وی بین 01/01/1960 تا 31/12/1975 باشد
پاسخ به این سوال بسیار آسان است. از عبارتی ساده برای کسب پاسخ استفاده کنید:
SELECT COUNT(*), sex from Employees WHERE DOB BETWEEN '01/01/1960' AND '31/12/1975' GROUP BY sex;
7. دستور SQL بنویسید که کارمندی را بیابد که حقوقش برابر یا بیشتر از 10000باشد
SELECT EmpName FROM Employees WHERE Salary>=10000;
8. دستور SQL برای یافتن سال از تاریخ را بنویسید
می توانید با دستور زیر، در سرور SQL 2008، سال را از تاریخ به دست آورید
SELECT YEAR(GETDATE()) as "Year";
9. دستور SQL برای یافتن سطرهای تکراری در پایگاه دادهها را بنویسید. سپس دستور SQL برای حذف آنها را قید کنید.
می توانید از دستور سادهای مثل زیر برای انتخاب دادهای مشخص در پروندهای خاص استفاده کنید
SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno)
و برای حذف:
DELETE FROM emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);
10. جدولی با دو ردیف دانش آموز و نمرات وجود دارد، باید دانش آموزانی را بیابید که نمراتشان از متوسط نمرهها بیشتر باشد، مثلاً فهرست متوسط نمرات دانش آموزان قید شده.
این دستور را میتوان با کمک دستور تابعی مثل زیر نوشت:
SELECT student, marks from table where marks > SELECT AVG(marks) from table)
| SQL Statement | Syntax | |
| AND / OR | SELECT column_name(s) FROM table_name WHERE condition AND|OR condition |
اضافه کردن شرط |
| ALTER TABLE | ALTER TABLE table_name ADD column_name datatype or ALTER TABLE table_name DROP COLUMN column_name |
|
| AS (alias) | SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name AS table_alias |
|
| BETWEEN | SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 |
|
| CREATE DATABASE | CREATE DATABASE database_name | ساخت دیتابیس |
| CREATE TABLE | CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, ... ) |
ساخت جدول |
| CREATE INDEX | CREATE INDEX index_name ON table_name (column_name) or CREATE UNIQUE INDEX index_name ON table_name (column_name) |
یکتا کردن ستون |
| CREATE VIEW | CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition |
|
| DELETE | DELETE FROM table_name WHERE some_column=some_value or DELETE FROM table_name (Note: Deletes the entire table!!) DELETE * FROM table_name (Note: Deletes the entire table!!) |
|
| DROP DATABASE | DROP DATABASE database_name | حذف یک دیتابیس |
| DROP INDEX | DROP INDEX table_name.index_name (SQL Server) DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL) |
|
| DROP TABLE | DROP TABLE table_name | حذف جدول |
| EXISTS | IF EXISTS (SELECT * FROM table_name WHERE id = ?) BEGIN --do what needs to be done if exists END ELSE BEGIN --do what needs to be done if not END |
|
| GROUP BY | SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name |
|
| HAVING | SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value |
|
| IN | SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..) |
|
| INSERT INTO | INSERT INTO table_name VALUES (value1, value2, value3,....) or INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....) |
درج یک خط |
| INNER JOIN | SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
|
| LEFT JOIN | SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
|
| RIGHT JOIN | SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
|
| FULL JOIN | SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
|
| LIKE | SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern |
شرط جهت جستجو |
| ORDER BY | SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC] |
|
| SELECT | SELECT column_name(s) FROM table_name |
انتخاب به همراه ستونها |
| SELECT * | SELECT * FROM table_name |
انتخاب به همرااه همه ستونها |
| SELECT DISTINCT | SELECT DISTINCT column_name(s) FROM table_name |
|
| SELECT INTO | SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name or SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name |
|
| SELECT TOP | SELECT TOP number|percent column_name(s) FROM table_name |
|
| TRUNCATE TABLE | TRUNCATE TABLE table_name | |
| UNION | SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 |
|
| UNION ALL | SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2 |
|
| UPDATE | UPDATE table_name SET column1=value, column2=value,... WHERE some_column=some_value |
به روز رسانی جدول |
| WHERE | SELECT column_name(s) FROM table_name WHERE column_name operator value |
گذاشتن شرط |
10 SQLite Database Browser Alternatives – Top Best Alternatives
MySQL Tutorial 2: Views and Joins
apt-get install sqlite3
sqlite3 sqlite.db .dump > mysql.sql
sqlite in php:
http://zetcode.com/db/sqlitephp/
Convert json
http://jeroenjanssens.com/2013/09/19/seven-command-line-tools-for-data-science.html
https://json-csv.com/
هدف این مقاله بررسی انواع دادهها در پایگاه داده MySQL (مایاسکیوال) میباشد. MySQL انواع مختلف داده را در دسته بندیهای زیر پشتیبانی میکند:
اعداد (Numeric)
زمان (Date and Time)
رشتهها (String)
دادههای فضایی (Spatial)
در این مقاله دادههای عددی، زمان و رشتهها را بررسی میکنیم. با توجه به تخصصی بودن دادههای فضایی، این نوع دادهها را در مقالهای جداگانه در آینده بررسی خواهیم کردم.
اعداد (Numeric)
MySQL تمامی انواع دادههای عددی استاندارد SQL شامل اعداد صحیح و اعداد تقریبی را پشتبانی میکند. هر یک از نوع دادههای عددی حافظه مشخصی برای ذخیره سازی نیاز دارند. همچنین در نظر داشته باشید که هر کدام از آنها محدوده مشخصی را پشتیبانی میکنند. هر نوع از این دادههای عددی را میتوان به صورت SIGNED یا UNSIGNED تعریف کرد. در حالت SIGNED این ستونها از مقادیر منفی پشتبیبانی میکنند ولی در حالت UNSIGNED اعداد ذخیره شده باید بزرگتر ویا مساوی صفر باشند. در نظر داشته باشید که نوع داده BOOLEAN برای ذخیره مقادیر TRUE و FALSE همان TINYINT(1) میباشد.
جدول زیر اطلاعات کلی نوع دادههای عددی را در حالت SIGNED و UNSIGNED نشان میدهد:
اطلاعات کلی نوع دادههای عددی Integer نوع حافظه (بایت) حداقل مقدار حداکثر مقدار توضیحات
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INTEGER 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615
اطلاعات کلی نوع دادههای عددی شناور نوع حافظه (بایت) مقدار نحوه تعریف توضیحات
FLOAT 4 از ۰ تا ۲۳ رقم FLOAT(M,D) مقدار تقریبی (Approximate Value)
DOUBLE 8 از ۲۴ تا ۵۳ رقم DOUBLE(M,D) مقدار تقریبی (Approximate Value)
DECIMAL هر ۹ رقم ۴ بایت به صورت باینری تا ۶۵ رقم DECIMAL(M,D) مقدار دقیق (Exact Value)
در جدول بالا حرف M نشاندهنده تعداد کل رقمهای داده و حرف D نشاندهنده تعداد رقمهای بعد از ممیز (.) میباشد. به عنوان مثال در ستونی که به صورت FLOAT(7,4) تعریف شده است میتوان حداکثر مقدار 999.9999 را ذخیره نمود. در صورتی که شما عدد 999.00009 را به این جدول وارد کنید، عدد واردشده به صورت 999.0001 ذخیره خواهد شد.
سوال: منظور از مقدار تقریبی و دقیق در جدول دادههای عددی شناور چیست؟
پاسخ: گاهی اوقات در نوع دادههای تقریبی اعداد گرد میشوند. در زیر با اجرا کردن چند دستور در خط فرمان MySQL پایگاه دادهای با دو ستون Decimal و Float تعریف کردیم. در آخرین دستور نتیجه گردشدن را در ستون Float مشاهده میفرمایید:
mysql> create table numbers (a decimal(10,2), b float);
mysql> insert into numbers values (100, 100);
mysql> select @a := (a/3), @b := (b/3), @a * 3, @b * 3 from numbers \G
*************************** 1. row ***************************
@a := (a/3): 33.333333333
@b := (b/3): 33.3333333333333
@a * 3: 99.999999999
@b * 3: 100
برای اطلاعات بیشتر به صفحه Problems with Floating-Point Values در سایت MySQL مراجعه نمایید.
در نسخههای قبل از 5.0.3 نوع داده DECIMAL به صورت String ذخیره میشود و مقدار رقمهایی که پشتیبانی میکرد مانند نوع داده DOUBLE بود.
نوع داده BIT
این نوع داده برای ذخیره بیتهای ۰ و ۱ استفاده میشود. برای تعریف ستونی از این نوع از عبارت BIT(M) استفاده میکنیم که در اینجا M فضایی به طول تقریبا (M+7)/8 بایت را برای نگهداری دادهها ایجاد میکند. M میتواند عددی از ۱ تا ۶۴ باشد. برای نشاندادن ۰ و ۱ به صورت بیت از حرف b استفاده میکنیم. به عنوان مثال b'111' و b'10000000' که به ترتیب برابر اعداد ۷ و ۱۲۸ میباشند. برای درک بیشتر این موضوع به دستورات و نتایج زیر در خط فرمان MySQL توجه فرمایید:
mysql> INSERT INTO t SET b = b'11111111';
mysql> INSERT INTO t SET b = b'1010';
mysql> INSERT INTO t SET b = b'0101';
mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;
+------+----------+----------+----------+
| b+0 | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
| 255 | 11111111 | 377 | FF |
| 10 | 1010 | 12 | A |
| 5 | 101 | 5 | 5 |
+------+----------+----------+----------+
3 rows in set (0.03 sec)
آخرین Query در بالا توابع مختلف برای تبدیل این اعداد را نشان میدهد. عبارت +0 مقدار دودویی ذخیره شده را به عدد دهدهی متناظر تبدیل میکند.
زمان (Date and Time)
در کل ۵ نوع داده برای ذخیره زمان (ساعت و تاریخ) داریم. در جدول زیر اطلاعات این نوع دادهها را مشاهده میفرمایید:
اطلاعات کلی نوع دادههای زمانی (Date and Time) نوع حافظه (بایت) مقدار صفر (“Zero” Value)
DATE 3 '0000-00-00'
TIME 3 '00:00:00'
DATETIME 8 '0000-00-00 00:00:00'
TIMESTAMP 4 '0000-00-00 00:00:00'
YEAR 1 0000
در ادامه به بررسی نوع دادههای ذکر شده در بالا خواهیم پرداخت.
نوع داده DATE
این نوع داده فقط برای ذخیره سازی تاریخ استفاده میشود. فرمت پیش فرض این نوع داده 'YYYY-MM-DD' است. بازهای که توسط DATE پشتیبانی میشود از '1000-01-01' تا '9999-12-31' میباشد.
نوع داده TIME
این نوع داده برای ذخیره سازی زمان (ساعت، دقیقه، ثانیه) استفاده میشود. فرمت پیشفرض این نوع داده 'HH:MM:SS' و یا 'HHH:MM:SS' است. بازهای که در نوع TIME پشتیبانی میشود از '-838:59:59' تا '838:59:59' میباشد. نوع داده TIME فقط برای نمایش زمان روز استفاده نمیشود. برای نمایش تفاوت زمانی دو بازه و یا زمان باقیمانده نیز میتواند از TIME استفاده نمود. به همین دلیل بازه پشتیبانی شده بیشتر از ۲۴ ساعت و یا کمتر از صفر (منفی) میباشد.
برای استفاده از عبارتهای مخفف در نوع داده TIME باید دقت نمایید. به عنوان مثال MySQL عبارت '11:12' را به صورت '11:12:00' تفسیر میکند. در صورتی که داده بدون ':' (دو نقطه) بیان شود، MySQL آن را از سمت راست تفسیر میکند. به عنوان مثال عبارت '1112' و یا 1112 به معنی '00:11:12' است.
به صورت پیش فرض MySQL دادههای خارج از محدوده TIME را در صورتی که با فرمت مناسب وارد شده باشند به نزدیکترین بازه تبدیل میکند. به عنوان مثال عبارتهای '-850:00:00' و '850:00:00' به '-838:59:59' و '838:59:59' تبدیل خواهند شد. دادههایی که فرمت مناسب نداشته و یا قابل تفسیر برای داده TIME نباشند به عبارت '00:00:00' تبدیل خواهند شد.
نوع داده DATETIME
این نوع داده برای ذخیره سازی زمان به صورت کامل شامل تاریخ و ساعت استفاده میشود. فرمت پیش فرض این نوع داده 'YYYY-MM-DD HH:MM:SS' میباشد. بازهی پشتیبانی DATETIME از '1000-01-01 00:00:00' تا '9999-12-31 23:59:59' است.
نوع داده TIMESTAMP
این نوع داده همانند DATETIME میباشد. بازهای که توسط TIMESTAMP پشتیبانی میشود از '1970-01-01 00:00:01' UTC تا '2038-01-19 03:14:07' UTC است.
دادههای TIMESTAMP هنگام ذخیره شدن از زمان منطقه زمانی (Time Zone) کنونی سیستم به UTC تبدیل و ذخیره میشوند. متناظر با همین امر، هنگام نشان دادن دادههای TIMESTAMP، ابتدا دادهها از UTC به منطقه زمانی کنونی سیستم تبدیل شده و بعد نمایش داده میشوند. به همین علت در صورتی که منطقی زمانی سیستم عوض شود، ممکن است دادهها به صورت غیرعادی و یا غلط نمایش داده شوند.
نوع داده TIMESTAMP این قابلیت را دارد که مقداردهی اولیه و یا مقداردهی خودکار داشته باشید. برای مقداردهی اولیه کافیست مقدار پیش فرض فیلد مورد نظر را CURRENT_TIMESTAMP قرار دهید. مانند:
1
mysql> CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
در حالت مقداردهی خودکار، مقدار ذخیره شده در فیلد TIMESTAMP هنگام بروزرسانی (UPDATE) یک ردیف به زمان بروزرسانی تغییر پیدا میکند. مانند:
1
23
mysql> CREATE TABLE t (name VARCHAR(100), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
mysql> INSERT INTO t(name) VALUES('Saeid');
mysql> SELECT * FROM t;
+-------+---------------------+
| name | ts |
+-------+---------------------+
| Saeid | 2011-12-22 12:00:26 |
+-------+---------------------+
1 row in set (0.00 sec)
mysql> UPDATE t SET name = 'Alireza';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM t;
+---------+---------------------+
| name | ts |
+---------+---------------------+
| Alireza | 2011-12-22 12:01:56 |
+---------+---------------------+
1 row in set (0.00 sec)
برای کسب اطلاعات بیشتر به صفحه TIMESTAMP Properties در سایت MySQL مراجعه نمایید.
نوع داده YEAR
این نوع داده برای ذخیره سازی سال با فرمت YYYY به کار میرود. شما میتواند سال مورد نظر خود را به صورت دو رقمی YEAR(2) و یا ۴ رقمی YEAR(4) تعریف کنید. نوع YEAR از سال 1901 تا 2155 و 0000 را پشتیبانی میکند. در صورتی که سال را به صورت ۲ رقمی تعریف کرده باشید، اعداد 00 تا 69 در قرن ۲۱ و اعداد 70 تا 99 در قرن ۲۰ در نظر گرفته میشوند. مثلا 45 به عنوان 2045 و 80 به صورت 1980 در نظر گرفته میشود. تمامی مقادیر غیر منطقی برای YEAR به صورت 0000 ذخیره میشوند.
رشتهها (String)
نوع داده String برای نگه داری رشتهها (متون) استفاده میشود. در زیر اطلاعات کلی دادههایی از نوع String را مشاهده میفرمایید. در جدول زیر M نشاندهنده طول تعریف شده برای داده و L نشاندهنده طول واقعی رشته داده شده میباشد.
اطلاعات کلی نوع داده رشتهها (String) نوع مقدار حافظه (بایت)
CHAR(M) 0 <= M <= 255 M × w بایت. w تعداد بایت مورد نیاز برای حداکثر طول کاراکترهای تعریف شده در character set جدول میباشد.
BINARY(M) 0 <= M <= 255 M بایت
VARCHAR(M) 0 <= M <= 65532 L + 1 بایت اگر داده مورد نظر کمتر از ۲۵۵ بایت نیاز داشته باشد.
L + 2 بایت اگر داده مورد نظر بیشتر از ۲۵۵ بایت نیاز داشته باشد.
VARBINARY(M) 0 <= M <= 65532 M + 1 بایت اگر داده مورد نظر کمتر از ۲۵۵ بایت نیاز داشته باشد.
M + 2 بایت اگر داده مورد نظر بیشتر از ۲۵۵ بایت نیاز داشته باشد.
TINYBLOB L < 28 L + 1 بایت
TINYTEXT L < 28 L + 1 بایت
BLOB L < 216 L + 2 بایت
TEXT L < 216 L + 2 بایت
MEDIUMBLOB L < 224 L + 3 بایت
MEDIUMTEXT L < 224 L + 3 بایت
LONGBLOB L < 232 L + 4 بایت
LONGTEXT L < 232 L + 4 بایت
ENUM('v1','v2',...) حداکثر ۶۵,۵۳۵ عضو ۱ یا ۲ بایت.
وابسته به مقدار داده.
SET('v1','v2',...) حداکثر ۶۴ عضو ۱، ۲، ۳، ۴ یا ۸ بایت
وابسته به مجموعه داده شده.
نوع داده CHAR و VARCHAR
CHAR و VARCHAR بسیار شبیه به هم هستند. مهمترین تفاوتی که بین CHAR و VARCHAR وجود دارد نحوه ذخیره سازی و بازیابی اطلاعات است. هنگام تعریف این نوع دادهها شما حداکثر تعداد کاراکتری را که میخواهید ذخیره شود را مشخص مینمایید. به عنوان مثال CHAR(30) برای ذخیره حداکثر ۳۰ کاراکتر تعریف میشود.
طول ستونی که از نوع CHAR باشد ثابت است. در صورتی که طول داده کمتر از طول تعریف شده باشد، فضای باقیمانده با فاصله (space) از راست پر میشود. هنگام بازیابی اطلاعات این فضای خالی قبل از نمایش حذف میشود مگر اینکه حالت PAD_CHAR_TO_FULL_LENGTH فعال شده باشد.
در نوع داده VARCHAR طول داده ذخیره شده به اندازه مقدار داده وارد شده میباشد. به عنوان مثال اگر ستونی به صورت VARCHAR(100) تعریف شده باشد ولی طول رشته داده شده 50 کاراکتر باشد، فقط به مقدار بایت زیر فضا لازم است.
(50 * فضای لازم برای ذخیره هر کاراکتر) + 2
در صورتی که در تعریف VARCHAR(M) مقدار M بیشتر از ۶۵,۵۳۵ باشد، MySQL به طور خودکار نوع ستون را از VARCHAR به نوع مناسبی مانند MEDIUMTEXT تغییر میدهد.
جدول زیر تفاوتهای فضای مورد نیاز برای ذخیره اطلاعات را در CHAR و VARCHAR نمایش میدهد. دقت کنید که در جدول زیر Character Set داده مورد نظر تک بایتی مانند latin1 است.
بررسی تفاوت فضای مورد نیاز برای CHAR(4) و VARCHAR(4) داده CHAR(4) فضای مورد نیاز VARCHAR(4) فضای مورد نیاز
'' ' ' ۴ بایت '' ۱ بایت
'ab' 'ab ' ۴ بایت 'ab' ۳ بایت
'abcd' 'abcd' ۴ بایت 'abcd' ۵ بایت
'abcdefgh' 'abcd' ۴ بایت 'abcd' ۵ بایت
نوع داده BINARY و VARBINARY
این نوع دادهها مانند CHAR و VARCHAR میباشند فقط با این تفاوت که در این نوع دادهها رشتههای باینری (صفر و یک) ذخیره میشود. در نتیجه Character set برای این نوع دادهها معنی ندارد. از طرفی مرتبسازی و مقایسه بر اساس مقدار عددی رشته باینری ذخیره شده میباشد.
نوع داده BLOB و TEXT
این نوع دادهها مانند BINARY و VARBINARY میباشند. هر کدام از این دادهها دارای ۴ نوع میباشند که تنها تفاوت آنها در حداکثر طول داده قابل ذخیره شدن است.
نوع داده ENUM
این نوع داده فقط امکان ذخیره سازی رشتهای را میدهد که از مجموعه تعریف شده باشد. این مجموعه هنگام ساخت جدول تعریف میشود. مانند جدول زیر:
1
2
3
CREATE TABLE sizes (
name ENUM('small', 'medium', 'large')
);
در ستون name در جدول بالا فقط میتوانید یکی از مقادیر small، medium یا large را ذخیره نمایید. در صورتی که مقداری خارج از مجموعه وارد مجموعه شود، فضای خالی در name ذخیره میشود، نه مقدار وارد شده.
نوع داده SET
این نوع داده مانند ENUM میباشد، با این تفاوت که شما میتوانید چند عضو از مجموعه تعریف شده را انتخاب نمایید. توجه نمایید که یا دادههای تکراری قبل از ذخیره سازی حذف میشوند. به مثال زیر توجه فرمایید:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
5 rows in set (0.00 sec)
از قراردادن فاصله قبل و یا بعد ',' جدا خوددداری نمایید.
چطور نوع داده مناسب را انتخاب نماییم؟
قبل از هر چیز بازهی مورد نیاز برای داده خود را معین نمایید. سپس مختصرترین نوع داده را برای ستون خود انتخاب نمایید. به عنوان مثال برای بازه اعداد ۰ تا ۹۹۹۹۹ نوع MEDIUMINT UNSIGNED بهترین گزینه است.
مورد دیگر انتخاب نوع ستون مناسب با داده شما است. به عنوان مثال هرگز تاریخ شمسی را در پایگاه داده در ستونی از نوع رشته (مانند CHAR) ذخیره ننمایید. بهترین گزینه تبدیل تاریخ به تاریخ میلای و ذخیره آن در دادهای از نوع DATE است.
در هنگام تعریف ستونی برای دادههای عددی در صورتی که فقط اعداد مثبت را لازم دارید، ستون خود را از نوع UNSIGNED انتخاب نموده و حداقل بازه را انتخاب نمایید. به عنوان مثال اگر ستون عددی شما کلید اصلی و افزایشی خودکار است (PRIMAY KEY and AUTO_INCREMENT) حتما نوع داده خود را UNSIGNED انتخاب نمایید تا حداکثر مقدار کلید اصلی دو برابر شود. نوع INTEGER SIGNED حداکثر عدد 2147483648 (حدود ۲ میلیارد) و INTEGER UNSIGNED حداکثر عدد 4294967295 (حدود ۴ میلیارد) را پشتیبانی میکند.
روش دیگر استفاده از PROCEDURE ANALYSE به منظور دریافت پیشنهاد خود پایگاه داده است. PROCEDURE ANALYSE با توجه به دادههای فعلی، پیشنهاد خود را برای بهینه کردن نوع داده ارائه میدهد. در نظر داشته باشید که هر چه تعداد داده فعلی شما بیشتر باشد، PROCEDURE ANALYSE پیشنهاد بهتر و دقیقتری میدهد. نحوه استفاده از PROCEDURE ANALYSE بسیار ساده است. نحوه نگارش آن به صورت زیر است:
1
mysql> SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]]);
مانند:
1
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);
یا
1
SELECT * FROM table1 PROCEDURE ANALYSE() \G
اسامی مشابه
در پایگاه داده MySQL برخی از انواع دادهها با نامهای مشابه دیگری نیز قابل دسترسی میباشند. یکی از دلایل این کار پیاده سازی راحتتر SQLهای پایگاههای دادهی دیگر است. در زیر این اسامی را مشاهده مینمایید.
اسامی مشابه برای نوع دادهها در MySQL نوع داده اسم مشابه
INTEGER INT
DECIMAL DEC
DECIMAL FIXED
DECIMAL NUMERIC
DOUBLE REAL
TINYINT BOOLEAN
TINYINT BOOL
VARCHAR(M) CHARACTER VARYING(M)
FLOAT FLOAT4
DOUBLE FLOAT8
TINYINT INT1
SMALLINT INT2
MEDIUMINT INT3
INT INT4
BIGINT INT8
MEDIUMBLOB LONG VARBINARY
MEDIUMTEXT LONG VARCHAR
MEDIUMTEXT LONG
MEDIUMINT MIDDLEINT
SELECT writefile('object0.gz', Content) FROM Document WHERE DocumentID = 1
sqlite3 my.db "SELECT writefile('object0.gz', MyBlob) FROM MyTable WHERE id = 1"
How to dump a file stored in a sqlite database as a blob? - Stack Overflow
Retrieve large blob from Android sqlite database - Stack Overflow
Retrieve large blob from Android sqlite database - Stack Overflow
java - Decoding blob data from a sqlite database into a human readable text - Stack Overflow
//UPDATE `sqlite_sequence` SET `seq` = 144 WHERE `name` = 'Pre';
