Fadak - Programming - SQL (Structured Query Language) & Tutorial Mysql Server (Relational Database)
: 2020-10-16Visitor Count : 563

SQL (Structured Query Language) & Tutorial Mysql Server (Relational Database)


  1. (Structured Query Language — «язык структурированных запросов»)
    1. Traditional JOIN
    2. Modern JOIN
  2. Simple Replace
  3. Selected a string between two strings in a column in MYSQL
  4. Replaced a string between two strings in a column in MYSQL
  5. Copy Columns from One Table to Another
  6. Find duplicate records in MySQL
  7. Copy values from one column to another in the same table
  8. Backup and Restore MySQL Database
  9. Where to change the value of lower_case
  10. SQLite
  11. Join
  12. Convert sqlite to mysql
  13. Data Type in MySQL
  • sqlite

  • (Structured Query Language — «язык структурированных запросов»)

    Traditional JOIN

    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;

    Modern JOIN

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

    Simple Replace

    UPDATE Table SET `Column` = REPLACE(Column,'Find_String_Start', 'Replace_With')

    Selected a string between two strings in a column in MYSQL

    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%'

    Replaced a string between two strings in a column in MYSQL

    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

    Copy Columns from One Table to Another

    UPDATE `Table1`,`Table2`
    SET  `Table1`.`column1` = `Table2`.`column1`
    WHERE `Table1`.`column2` = `Table2`.`column3`
    COLLATE utf8_unicode_ci

    Find duplicate records in MySQL

     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(*)

    Copy values from one column to another in the same table

    INSERT INTO table2 (st_id,uid,changed,status,assign_status)
    SELECT st_id,from_uid,now(),'Pending','Assigned'
    FROM table1

    Backup and Restore MySQL Database

    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/

    Where to change the value of lower_case

    در زمانی که عدم امکان حروف بزرگ

    lower_case_table_names=2

    mysql - Where to change the value of lower_case_table_names=2 on windows xampp

     

    آموزش SQL

    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
     گذاشتن شرط
         
         

     

    SQLite

    10 SQLite Database Browser Alternatives – Top Best Alternatives

     

    Join

    JOINs and VIEWs

    MySQL Tutorial 2: Views and Joins

     

    Convert sqlite to mysql

    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/

    Data Type in MySQL

    هدف این مقاله بررسی انواع داده‌ها در پایگاه داده 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

     

    توابع convert و cast در SQL

     

     

     

    sqlite

    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

     

     


       

    2000-2016 CMS Fadak. ||| Version : 4.2-b1 ||| This page was produced in : 0.029 Seconds |||