MySQL�R�}���h�E�֐��ꗗ

  • �f�[�^�x�[�XMySQL�ŃT�|�[�g����Ă���SQL�R�}���h�̈ꗗ���f�ڂ��Ă��܂��B SQL�ꗗ�\�́A�e���ڂł̕��בւ����”\�ł��B �f�[�^���얽�߁iDML�j�A�f�[�^��`���߁iDDL�j�A�g�����U�N�V�����A���Z�q�A�֐��ɕ��ނ��Ă��܂��B �\�́utable_name�v�̓e�[�u�����A�uitem�v�͍��ڂ��Ӗ����܂��B �Ȃ��A���̃f�[�^�x�[�X�iOracle�ADB2���j�ł́ASQL�̏������قȂ邱�Ƃ�����܂��̂ŁA�����ӂ��������B

    MySQL�́A���E�ōł��L���ȃI�[�v���\�[�X�E�f�[�^�x�[�X�Ƃ��Ēm���Ă���A���E�̃f�[�^�x�[�X�s��ł͑���PostgreSQL�Ȃǂ̃��C�o�������|���Ă��܂��B �I�[�v���\�[�X�E�f�[�^�x�[�X�ł��邽�߁A�قƂ�ǂ������^���T�[�o�[�ŗ��p�”\�ɂȂ��Ă��܂��B





    SQL ���� �Ӗ� �g�p�� ����
    SELECTDML�f�[�^�s���擾SELECT * FROM table_nametable_name�̑S��A�S�s���擾
    DISTINCTDML�d������s������SELECT DISTINCT item FROM table_nametable_name��item�񂾂����d���l����菜���Ď擾
    ASDML�ʖ��̎w��SELECT item1 AS item2 FROM table_nameitem1���item2��Ƃ����ʖ���t���Č��ʂ��擾
    FROMDML�Ώۃe�[�u���w��SELECT * FROM table_nametable_name�̑S��A�S�s���擾
    WHEREDML���o�����̎w��SELECT * FROM table_name WHERE item = 100item��100�Ɠ������s���擾
    GROUP BYDML�O���[�v��SELECT COUNT(*) FROM table_name GROUP BY itemtable_name��item��̒l�ɂ���ăO���[�v�����A���̍s�����W�v
    HAVINGDML�W�v�֐��̌��ʂ𒊏o�����Ɏw��SELECT x,SUM(i) FROM table_name GROUP BY x HAVING SUM(i) > 100�O���[�v�̍��v�l��100�ȏ�ł���O���[�v�݂̂��擾
    ORDER BYDML�擾�����E�\�[�gSELECT * FROM table_name ORDER BY item ASCtable_name��item��̏����iDESC�͍~���j�ɕ��ѕς���
    LIMITDML���ʂ̐���SELECT * FROM table_name LIMIT 5,3table_name�S�̂�5�s�ڂ���3�s�݂̂��擾
    INSERTDML�f�[�^�s�̒lj�INSERT INTO table_name VALUES(1,2,'test')table_name�ɗ�̍�����1,2,'test'��ێ�����s��lj��iVALUES�ɑ����l�ƑΉ��j
    UPDATEDML�f�[�^�s�̍X�VUPDATE table_name SET item1 = 1 WHERE item2 = 2table_name�ɂ����āAitem2��2�̃f�[�^�s��item1���1�ɍX�V
    DELETEDML�f�[�^�s�̍폜DELETE FROM table_name WHERE item = 1table_name�ɂ����āAitem��1�̍s�݂̂��폜
    BEGIN�g�����U�N�V�����g�����U�N�V�����J�nBIGIN WORKCOMITT�����Ċm�肷��܂ŁAINSERT�ADELETE�AUPDATE�����f����Ȃ�
    COMMIT�g�����U�N�V�����g�����U�N�V�������R�~�b�gCOMMITINSERT�ADELETE�AUPDATE�𔽉f����
    ROLLBACK�g�����U�N�V�����g�����U�N�V������߂�ROLLBACKINSERT�ADELETE�AUPDATE���j������ABIGIN�����s����O�̏�Ԃɖ߂�
    LOCK TABLES�g�����U�N�V�����e�[�u���̃��b�NLOCK TABLES table_name READ�e�[�u����READ���b�N�iREAD/READ LOCAL/WRITE�j
    UNLOCK TABLES�g�����U�N�V�����e�[�u���̃��b�N����UNLOCK TABLES�S�e�[�u���̃��b�N������
    CREATE TABLEDDL�e�[�u���̍쐬CREATE TABLE table_name(item1 INTEGER, item2 VARCHAR(20))item1��item2�̗�������A���ꂼ��̌^�������A������ł���table_name���쐬
    NOT NULLDDLNOT NULL����CREATE TABLE table_name(item1 INTEGER NOT NULL, item2 VARCHAR(20) NULL)item1���NOT NULL�Ɏw��Aitem2���NULL�w��
    UNIQUEDDLUNIQUE����CREATE TABLE table_name(item INTEGER UNIQUE)item1���UNIQUE�����ݒ�
    PRIMARY KEYDDLPRIMARY KEY����CREATE TABLE table_name(item INTEGER NOT NULL PRIMARY KEY)item1���NOT NULL����� PRIMARY KEY�����ݒ�
    DROP TABLEDDL�e�[�u���̍폜DOROP TABLE table_nametable_name���폜
    ALTER TABLEDDL�e�[�u�������ύXALTER TABLE table_name ADD COLUMN item INTEGERitem��𐔒l�^��table_name�ɒlj��iDROP�͍폜�j
    TRUNCATE TABLEDDL�e�[�u���S�s�폜TRUNCATE TABLE table_nametable_name�S�s�������ō폜�iDELETE��葬���j
    CREATE INDEXDDL�C���f�b�N�X�쐬CREATE INDEX idx ON table_nametable_name�ɑ΂��ăC���f�b�N�Xidx���쐬
    DROP INDEXDDL�C���f�b�N�X�폜DROP INDEX idx ON table_nametable_name�ɑ΂��ăC���f�b�N�Xidx���폜
    GRANTDDL�����̕t�^GRANT ALL PRIVILEGES ON *.* TO name@localhost FLUSH PRIVILEGES���ׂĂ̌�����localhost�̃��[�U�[name�ɃO���[�o�������Ƃ��ĕt�^
    REVOKEDDL�����̔��DREVOKE ALL PRIVILEGES ON table_name FROM name FLUSHI PRIVILEGEStable_name�ɑ΂��āA�S�Ă̌�����name�����D
    RENAMEDDL�I�u�W�F�N�g���̕ύXRENAME table_nameA TO table_nameB�f�[�^�x�[�Xtable_nameA��table_nameB�Ƀ��l�[��
    AND���Z�q����SELECT * FROM table_name WHERE item1 = 100 AND item2 = 200item1��100�Ɠ������A����item2��200�Ɠ������s��S�Ď擾
    BETWEEN���Z�q�͈͎w��SELECT * FROM table_name WHERE �N�� BETWEEN 30 AND 40�N���20�ȏ�A30�ȉ��̍s��S�Ď擾
    IN���Z�q�܂�SELECT * FROM table_name WHERE item IN (100,200)item��100��200�̍s��S�Ď擾
    LIKE���Z�q�p�^�[���}�b�`SELECT ���� FROM table_name WHERE ���� LIKE '%�Y'�����̍Ōオ�u�Y�v�ł���s��S�Ď擾
    NOT���Z�q�ے�SELECT * FROM table_name WHERE NOT item = 1table_name��item��1�ł͂Ȃ��s��S�Ď擾
    OR���Z�q�܂���SELECT * FROM table_name WHERE item1 = 100 OR item2 = 200item1��100�A�܂���tem2��200�Ɠ������s��S�Ď擾
    IS NULL���Z�qNULL�l�̔���SELECT * FROM table_name WHERE item IS NULLitem��NULL�̍s��S�Ď擾
    IS NOT NULL���Z�qNULL�l�̔���SELECT * FROM table_name WHERE item IS NOT NULLitem��NULL�ł͂Ȃ��s��S�Ď擾
    AVG�֐��i�W�v�j����SELECT AVG(DISTINCT item) FROM teble_nameitem��̏d���������ĕ��ϒl���v�Z
    COUNT�֐��i�W�v�j�s�����W�vSELECT COUNT(DISTINCT item)�@FROM table_nameitem��̏d���������čs�����v�Z
    MAX�֐��i�W�v�j�ő�lSELECT MAX(item) FROM table_nameitem��̍ő�l���v�Z
    MIN�֐��i�W�v�j�ŏ��lSELECT MIN(item) FROM table_nameitem��̍ŏ��l���v�Z
    STDDEV�֐��i�W�v�j�W���΍�SELECT STDDEV(item) FROM�@table_nameitem��̕W���΍����v�Z
    SUM�֐��i�W�v�j���v�lSELECT SUM(item) FROM table_nameitem��̍��v�l���v�Z
    ASCLL�֐��i�����j�����R�[�h�ϊ�SELECT ASCALL(item) FROM teble_nameitem���ASCALL�ɂ�蕶���R�[�h�ɕϊ�
    CHAR�֐��i�����j�����ϊ�SELECT CHAR(item) FROM teble_nameitem���CHAR�ɂ�蕶���ɕϊ�
    CONCAT�֐��i�����j�����񌋍�SELECT CONCAT(item1,item2,item3) FROM table_nameCONCAT�ɂ��A��item1,item2,item3�𕶎��񌋍�
    INSERT�֐��i�����j������̑}��SELECT INSERT(item,5,1,'xyz') FROM table_nameitem���5�����ڂ���1������xyz�ɒu��
    INSTER�֐��i�����j�����񌟍�SELECT INSTER(item1,item2) FROM table_nameitem1��̕����񒆂��當����item2������
    LENGTH�֐��i�����j�����񒷂��擾SELECT LENGTH(item) FROM table_nameitem��̕����񒷂��v�Z
    CHARACTER
    _LENGTH
    �֐��i�����j�����񒷎擾SELECT CHAR_LENGTH(item1) FROM table_name WHERE item2 = 100table_name��item2��100�Ɠ������s����Aitem1��̕�����̒������擾
    OCTET
    _LENGTH
    �֐��i�����j�����񒷂��擾SELECT OCTET_LENGTH(item) FROM table_nameitem��̃o�C�g�����v�Z
    POSITION�֐��i�����j�����񌟍�SELECT POSITION(item1 IN item2) FROM table_nameitem2��̕����񒆂���item1�̕�������������A�ŏ���item1�������ʒu���擾
    REPEAT�֐��i�����j�J��Ԃ�SELECT REPEAT(item1,item2) FROM table_nameitem1��item2��J��Ԃ�����������擾
    REPLACE�֐��i�����j�u��SELECT REPLACE(item1,item2,item3) FROM table_nameitem1��̕����񂩂�item2��̕�����Ɠ������ӏ���S�ĕ�����item3�ɒu��
    REVERSE�֐��i�����j���]SELECT REVERSE(item) FROM table_nameitem��̕�����𔽓]������������擾�i1�o�C�g�����̂ݑΉ��j
    SUBSTRING�֐��i�����j�������oSELECT SUBSTRING(item1,item2,item3) FROM table_nameitem1��̕������item2�Ԗڂ���Aitem3�Ԗڂ܂ł𒊏o���Ď擾
    RIGHT�֐��i�����j�E�������擾SELECT RIGHT(item1,item2) FROM table_nameitem1��̕������item2��̒l�������A�E�����璊�o������������擾
    LEFT�֐��i�����j���������擾SELECT LEFT(item1,item2) FROM table_nameitem1��̕������item2��̒l�������A�������璊�o������������擾
    RTRIM�֐��i�����j�E�̋󔒍폜SELECT RTRIM(item) FROM table_nameitem�񂩂�E���̋󔒂���菜��
    LTRIM�֐��i�����j���̋󔒍폜SELECT LTRIM(item) FROM table_nameitem�񂩂獶���̋󔒂���菜��
    TRIM�֐��i�����j�w�蕶���̍폜SELECT TRIM(item1 FROM item2) FROM table_nameitem2�̗��[����item1����菜�������ʂ��擾
    INITCAP�֐��i�����j�擪��啶���ϊ�SELECT INITCAP(item) FROM teble_nameitem��̕������擪�����A�啶���ɕϊ�
    LOWER�֐��i�����j�������ϊ�SELECT LOWER(item) FROM table_nameitem��̕������S�ď������ɕϊ�������������擾
    UPPER�֐��i�����j�啶���ϊ�SELECT UPPER(item) FROM table_nameitem��̕������S�đ啶���ɕϊ�������������擾
    SYSDATE�֐��i���t�j���ݓ��tSELECT STSDATE FROM DUAL���݂̓������擾
    CURRENT
    _DATE
    �֐��i���t�j���ݓ��tSELECT CURRENT_DATE���݂̓������擾
    CURRENT
    _TIME
    �֐��i���t�j���ݎ���SELECT CURRENT_TIME���݂̎������擾
    CURRENT
    _TIMESTAMP
    �֐��i���t�j���ݓ���SELECT CURRENT_TIMESTAMP���݂̓������擾
    NOW�֐��i���t�j���ݓ���INSERT INTO table_name VALUES(NOW())���݂̓����ōs��lj�
    DATE
    _FORMAT
    �֐��i���t�j���t�̐��`SELECT DATE_FORMAT(item,'%Y/%m/%d') FROM table_nameitem��iYYYY-MM-DD�`���j��YYYY/MM/DD�`���ɐ��`
    TIME
    _FORMAT
    �֐��i���t�j�����̐��`SELECT TIME_FORMAT(item,'%H:/%i:/%s') FROM table_nameitem��ihhmmss�`���j��hh:mm:ss�`���ɐ��`
    DATE
    _ADD
    �֐��i���t�j���t�̉��ZDATE_ADD(item, INTERVAL 10 DAY) FROM table_name���t�^��item���10������v�Z���Ď擾
    DATE
    _SUB
    �֐��i���t�j���t�̌��ZDATE_SUB(item, INTERVAL 10 DAY) FROM table_name���t�^��item���10���O���v�Z���Ď擾
    YEAR�֐��i���t�j�N���擾SELECT YEAR(item) FROM table_nameitem��iYYYY-MM-DD�`���j����N(YYYY)�݂̂��擾
    MONTH�֐��i���t�j�����擾SELECT MONTH(item) FROM table_nameitem��iYYYY-MM-DD�`���j���猎(MM)�݂̂��擾
    DAYOFMONTH�֐��i���t�j���t���擾SELECT DAYOFMONTH(item) FROM table_nameitem��iYYYY-MM-DD�`���j������t�iDD�j�݂̂��擾
    HOUR�֐��i���t�j�����擾SELECT HOUR(item) FROM table_nameitem��ihh:mm:ss�`���j���玞(hh)�݂̂��擾
    MINUTE�֐��i���t�j�����擾SELECT MINUTE(item) FROM table_nameitem��ihh:mm:ss�`���j���番(mm)�݂̂��擾
    SECOND�֐��i���t�j�b���擾SELECT SECOND(item) FROM table_nameitem��ihh:mm:ss�`���j����b(ss)�݂̂��擾
    DAYOFWEEK�֐��i���t�j�j�����擾SELECT DAYOFWEEK(item) FROM table_nameitem��iYYYY-MM-DD�`���j����j���݂̂��v�Z���Ď擾
    DAYNAME�֐��i���t�j�j�������擾SELECT DAYNAME(item) FROM table_nameitem��iYYYY-MM-DD�`���j����j�������v�Z���Ď擾
    MONTHNAME�֐��i���t�j�������擾SELECT MONTHNAME(item) FROM table_nameitem��iYYYY-MM-DD�j���猎���݂̂��擾
    DAYOFYEAR�֐��i���t�j�N�ԒʎZ�����擾SELECT DAYOFYEAR(item) FROM table_nameYYYY/01/01����item��iYYYY-MM-DD�`���j�܂ł̌o�ߓ������v�Z���Ď擾
    WEEK�֐��i���t�j�N�ԒʎZ�T���擾SELECT WEEK(item) FROM table_nameYYYY/01/01����item��iYYYY-MM-DD�`���j�܂ł̌o�ߏT�����v�Z���Ď擾
    NULLIF�֐��i�ϊ��jNULL�ϊ�SELECT NULLIF(item1,item2) FROM table_nameitem1���item2�񂪓������ꍇ�ANULL�ɕϊ�
    ABS�֐��i�ϊ��j��ΒlSELECT ABS(item) FROM table_nameitem��̐�Βl���v�Z
    SIN�֐��i�ϊ��j�T�C��SELECT SIN(item) FROM table_nameitem��̃T�C�����v�Z�i�����̒P�ʂ̓��W�A���j
    COS�֐��i�ϊ��j�R�T�C��SELECT COS(item) FROM table_nameitem��̃R�T�C�����v�Z�i�����̒P�ʂ̓��W�A���j
    TAN�֐��i�ϊ��j�^���W�F���gSELECT TAN(item) FROM table_nameitem��̃^���W�F���g���v�Z�i�����̒P�ʂ̓��W�A���j
    COT�֐��i�ϊ��j�R�^���W�F���gSELECT COT(item) FROM table_nameitem��̃R�^���W�F���g���v�Z�i�P�ʂ̓��W�A���j
    ASIN�֐��i�ϊ��j�t�T�C��SELECT ASIN(item) FROM table_nameitem��̋t�T�C�����v�Z�i�P�ʂ̓��W�A���j
    ACOS�֐��i�ϊ��j�t�R�T�C��SELECT ACOS(item) FROM table_nameitem��̋t�R�T�C�����v�Z�i�P�ʂ̓��W�A���j
    ATAN�֐��i�ϊ��j�t�^���W�F���gSELECT ATAN(item) FROM table_nameitem��̋t�^���W�F���g���v�Z�i�P�ʂ̓��W�A���j
    ATAN2�֐��i�ϊ��j�t�^���W�F���gSELECT ATAN2(item1,item2) FROM table_nameitem1���item2��ɂ��t�^���W�F���g���v�Z�i�P�ʂ̓��W�A���j
    CEILING�֐��i�ϊ��j�ŏ������lSELECT CEILING(item) FROM table_nameitem��ɂ����āA�ł������������l���v�Z�i�����_�ȉ��؂�̂āj
    FLOOR�֐��i�ϊ��j�ő吮���lSELECT FLOOR(item) FROM table_nameitem��ɂ����āA�ł��傫�������l���v�Z�i�����_�ȉ��؂�̂āj
    DEGREES�֐��i�ϊ��j���W�A����x�ɕϊ�SELECT DEGREES(item) FROM table_nameitem��i���W�A���j��x�ɕϊ�
    RADIANS�֐��i�ϊ��j�x�����W�A���ɕϊ�SELECT RADIANS(item) FROM table_nameitem��i�x�j�����W�A���ɕϊ�
    EXP�֐��i�ϊ��j�w���lSELECT EXP(item) FROM table_nameitem��̎w���l���v�Z
    GREATEST�֐��i�ϊ��j�ő�lSELECT GREATEST(item1,item2,item3) FROM table_name��item1,item2,item3�̒��ōł��傫���l���v�Z�i�����̌��͉•ϒ��j
    LEAST�֐��i�ϊ��j�ŏ��lSELECT LEAST(item1,item2,item3) FROM table_name��item1,item2,item3�̒��ōł��������l���v�Z�i�����̌��͉•ϒ��j
    MOD�֐��i�ϊ��j�]����v�ZSELECT MOD(item1,item2) FROM table_nameitem1��item2��̗]����v�Z
    POW�֐��i�ϊ��j�ׂ���SELECT POW(item1,item2) FROM table_nameitem1���item2�����v�Z
    SQRT�֐��i�ϊ��j������SELECT SQRT(item) FROM table_nameitem1��̕��������v�Z
    ROUND�֐��i�ϊ��j�l�̌ܓ�SELECT ROUND(item1,2) FROM table_nameitem1��̒l�������_�ȉ�2�����c��悤�Ɏl�̌ܓ�
    SIGN�֐��i�ϊ��j����SELECT SIGN(item) FROM table_nameitem��̕������擾�i���F0/���F-1/0�F0�j
    RAND�֐��i�ϊ��j����SELECT RAND()����(�����_���Ȑ��l)���擾
    PI�֐��i�ϊ��j�~����SELECT PI()�~�������擾

Ђ̏Љ