Systemy Operacyjne
Usługi systemowe
Bazy Danych
Programowanie
CONTENT
  • CHANGES
Szukaj
counter

#top Zapytania SQL



#top users


#top USER CREATE


Zobacz także USER CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/create-user.html

Składnia:
CREATE USER user_specification [, user_specification] ...

user_specification:
    user [ identified_option ]

identified_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
}

Example: utworzenie użytkownika wraz hasłem:
CREATE USER temp@127.0.0.1 IDENTIFIED BY 'temp';
CREATE USER temp@localhost IDENTIFIED BY 'temp';

Example: utworzenie użytkownika wraz z hasłem poprzez nadanie uprawnień:
GRANT SELECT,INSERT,UPDATE,DELETE ON temp.* TO temp@127.0.0.1 IDENTIFIED BY 'temp';
GRANT SELECT,INSERT,UPDATE,DELETE ON temp.* TO temp@localhost IDENTIFIED BY 'temp';
FLUSH PRIVILEGES;


#top USER ALTER


Zobacz także USER ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/rename-user.html

Składnia:
RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

Example:
-- comment


#top USER SET PASSWORD


Zobacz także USER SET PASSWORD dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/set-password.html

Składnia:
SET PASSWORD [FOR user] = password_option

password_option: {
    PASSWORD('auth_string')
  | OLD_PASSWORD('auth_string')
  | 'hash_string'
}

Example:
SET PASSWORD FOR root@localhost = PASSWORD('newpass');
FLUSH PRIVILEGES;

Example: That is equivalent to the following statements:
UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

Example: Another way to set the password is to use GRANT:
GRANT USAGE ON *.* TO root@localhost IDENTIFIED BY 'newpass';
FLUSH PRIVILEGES;


#top USER DROP


Zobacz także USER DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/drop-user.html

Składnia:
DROP USER user [, user] ...

Example: odebranie nadanych / wszystkich uprawnień użytkownikowi, a następnie skasowanie użytkownika
REVOKE ALL PRIVILEGES ON temp.* FROM temp@127.0.0.1;
REVOKE ALL PRIVILEGES ON temp.* FROM temp@localhost;
DROP USER temp@127.0.0.1;
DROP USER temp@localhost;
FLUSH PRIVILEGES;



#top permissions


#top GRANT


Zobacz także GRANT dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/grant.html

Składnia:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user_specification:
    user [ auth_option ]

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY PASSWORD 'hash_string'
}

ssl_option: {
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'
}

resource_option: {
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

There are several aspects to the GRANT statement, described under the following topics in this section:
Global Privileges
CREATE USER - Privilege enables use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
FILE - Privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function.
PROCESS - Privilege pertains to display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads. The PROCESS privilege also enables use of SHOW ENGINE.
RELOAD - Privilege enables use of the FLUSH statement. It also enables mysqladmin commands that are equivalent to FLUSH operations: flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload.
REPLICATION CLIENT - Privilege enables the use of SHOW MASTER STATUS and SHOW SLAVE STATUS.
REPLICATION SLAVE - Privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.
SHOW DATABASES - Privilege enables the account to see database names by issuing the SHOW DATABASE statement.
SHUTDOWN - Privilege enables use of the mysqladmin shutdown command. There is no corresponding SQL statement.
SUPER - Privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables.
Database Privileges
CREATE - Privilege enables creation of new databases and tables.
DROP - Privilege enables you to drop (remove) existing databases, tables, and views.
EVENT - Privilege is required to create, alter, drop, or see events for the Event Scheduler.
GRANT OPTION - Privilege enables you to give to other users or remove from other users those privileges that you yourself possess.
LOCK TABLES - Privilege enables the use of explicit LOCK TABLES statements to lock tables for which you have the SELECT privilege.
Table Privileges
ALTER - Privilege enables use of ALTER TABLE to change the structure of tables. ALTER TABLE also requires the CREATE and INSERT privileges. Renaming a table requires ALTER and DROP on the old table, ALTER, CREATE, and INSERT on the new table.
CREATE VIEW - Privilege enables use of CREATE VIEW.
CREATE - Privilege enables creation of new databases and tables.
DELETE - Privilege enables rows to be deleted from tables in a database.
DROP - Privilege enables you to drop (remove) existing databases, tables, and views.
GRANT OPTION - Privilege enables you to give to other users or remove from other users those privileges that you yourself possess.
INDEX - Privilege enables you to create or drop (remove) indexes. INDEX applies to existing tables.
INSERT - Privilege enables rows to be inserted into tables in a database.
SELECT - Privilege enables you to select rows from tables in a database.
SHOW VIEW - Privilege enables use of SHOW CREATE VIEW.
TRIGGER - Privilege enables trigger operations.
UPDATE - Privilege enables rows to be updated in tables in a database.
Column Privileges
INSERT - Privilege enables rows to be inserted into tables in a database.
SELECT - Privilege enables you to select rows from tables in a database.
UPDATE - Privilege enables rows to be updated in tables in a database.
Stored Routine Privileges
ALTER ROUTINE - Privilege is needed to alter or drop stored routines (procedures and functions).
CREATE ROUTINE - Privilege is needed to create stored routines (procedures and functions).
EXECUTE - Privilege is required to execute stored routines (procedures and functions).
GRANT OPTION - Privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

Example: globalne uprawnienia
global grant apply to *.*, NOT TO database.*:
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON *.* TO temp@localhost;
FLUSH PRIVILEGES;

Example: nadanie wszytkich uprawnień: WARNING: STANOWCZO ODRADZANE, NIE NALEŻY NADAWAĆ UPRAWNIEŃ WIĘKSZYCH NIŻ SĄ WYMAGANE DO POPRAWNEGO DZIAŁANIA APLIKACJI (WZGLĘDY BEZPIECZEŃSTWA)
GRANT ALL ON *.* TO temp@localhost;
FLUSH PRIVILEGES;

Example: uprawnienia związane z zarządzaniem kontami użytkowników
Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/adding-users.html
utworzenie konta użytkownika temp2@localhost z uprawnieniami do tworzenia kont (oraz przeładowania konfiguracji uprawnień)
mysql -u root -p mysql
CREATE USER temp2@localhost IDENTIFIED BY 'temp2';
GRANT SUPER,RELOAD ON *.* TO temp2@localhost WITH GRANT OPTION;
FLUSH PRIVILEGES;
utworzenie konta użytkownika temp3@localhost z uprawnieniami do tworzenia kont (oraz przeładowania konfiguracji uprawnień)
mysql -u temp2 --password=temp2 mysql
CREATE USER temp3@localhost IDENTIFIED BY 'temp3';
GRANT SUPER,RELOAD ON *.* TO temp3@localhost WITH GRANT OPTION;
FLUSH PRIVILEGES;
utworzenie konta użytkownika temp4@localhost z uprawnieniami do tworzenia kont (oraz przeładowania konfiguracji uprawnień)
mysql -u temp3 --password=temp3 mysql
CREATE USER temp4@localhost IDENTIFIED BY 'temp4';
GRANT SUPER,RELOAD ON *.* TO temp4@localhost WITH GRANT OPTION;
FLUSH PRIVILEGES;

Example: uprawnienia związane wymuszaniem zapisu i zamknięciem otwartych tabel
FLUSH TABLES;
ERROR 1227 (42000): Access denied; you need the RELOAD privilege for this operation
GRANT RELOAD ON *.* TO temp@localhost;
FLUSH PRIVILEGES;

Example: uprawnienia związane z replikacją
Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO replicant@replication_host;
FLUSH PRIVILEGES;

Example: uprawnienia związane z blokowaniem tabel (for mysqldump)
GRANT LOCK TABLES ON temp.* TO temp@localhost;
FLUSH PRIVILEGES;

Example: uprawnienia związane bazami danych
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, ALTER, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON temp.* TO temp@localhost;
FLUSH PRIVILEGES;

Example: uprawnienia związane z procedurami / funkcjami
GRANT CREATE ROUTINE ON temp.* TO temp@localhost;
GRANT ALTER ROUTINE ON temp.* TO temp@localhost;
GRANT EXECUTE ON temp.* TO temp@localhost;
GRANT FILE ON temp.* TO temp@localhost;
FLUSH PRIVILEGES;

Example: uprawnienia związane z tabelami
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER ON *.* TO temp@localhost;
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO temp@localhost;
FLUSH PRIVILEGES;


#top REVOKE


Zobacz także REVOKE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/revoke.html

Składnia:
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

Example: Odebranie nadanych powyżej uprawnień oraz skasowanie założonych kont:
mysql -u root -p mysql
REVOKE ALL PRIVILEGES ON *.* FROM temp4@localhost;
REVOKE ALL PRIVILEGES ON *.* FROM temp3@localhost;
REVOKE ALL PRIVILEGES ON *.* FROM temp2@localhost;
FLUSH PRIVILEGES;
DROP USER temp4@localhost;
DROP USER temp3@localhost;
DROP USER temp2@localhost;

Example: Odebranie wszytkich uprawnień: OK: STANOWCZO WSKAZANE, NIE NALEŻY NADAWAĆ UPRAWNIEŃ WIĘKSZYCH NIŻ SĄ WYMAGANE DO POPRAWNEGO DZIAŁANIA APLIKACJI (WZGLĘDY BEZPIECZEŃSTWA)
REVOKE RELOAD ON *.* FROM temp@localhost;
REVOKE ALL ON *.* FROM temp@localhost;
FLUSH PRIVILEGES;


#top SHOW GRANTS


Zobacz także SHOW GRANTS dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/show-grants.html

Składnia:
SHOW GRANTS [FOR user]

Example:
-- comment



#top databases


#top DATABASE CREATE


Zobacz także DATABASE CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/create-database.html

Składnia:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

Example: utworzenie bazy danych oraz użytkownika wraz z hasłem oraz uprawnieniami do utworzonej bazy danych
CREATE DATABASE temp DEFAULT charset=utf8;
GRANT SELECT,INSERT,UPDATE,DELETE ON temp.* TO temp@localhost IDENTIFIED BY 'temp';
GRANT SELECT,INSERT,UPDATE,DELETE ON temp.* TO temp@127.0.0.1 IDENTIFIED BY 'temp';
FLUSH PRIVILEGES;

Example: utworzenie bazy danych (jeśli nie istnieje) z określonym kodowaniem znaków oraz kodowaniem znaków stosowanych podczas porównywania (klauzule WHERE)
CREATE DATABASE IF NOT EXISTS temp DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_unicode_ci;


#top DATABASE ALTER


Zobacz także DATABASE ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/alter-database.html

Składnia:
ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

Example:
ALTER DATABASE temp DEFAULT charset=utf8;


#top RENAME DATABASE - trick


Przydatne przy małych bazach danych.
Zmiana nazwy bazy danych bez modyfikacji struktury katalogowej (trick)
1. Zrzut polecenia tworzącego bazę danych, zmiana nazwy poleceniem sed i wykonanie polecenia tworzącego bazę danych.
2. Zrzut polecenia tworzącego tabelę, zmiana nazwy bazy danych (jeśli tabela jest z prefixem w postaci nazwy bazy danych z kropką baza.) i wykonanie polecenia tworzącego tabelę
3. Insert danych do tabeli w nowej bazie danych poprzez pobranie wszystkich danych ze starej bazy danych.
username="root"; password="password"; dbname1="database1"; dbname2="database2";
#echo "SHOW CREATE DATABASE $dbname1" | mysql -N -u $username --password=$password mysql | sed -e "s,^.*CREATE DATABASE,CREATE DATABASE,g" -e
#echo "CREATE DATABASE $dbname2 ..." | mysql -u $username --password=$password mysql
echo "SHOW CREATE DATABASE $dbname1" | mysql -N -u $username --password=$password mysql | sed -e "s,^.*CREATE DATABASE,CREATE DATABASE,g" -e 's,\\n,\n,g' -e "s,$dbname1,$dbname2,g" | mysql -u $username --password=$password mysql
for table in `echo "show tables from $dbname1" | mysql  -N -u $username --password=$password mysql`;do echo "table=$table"; echo "SHOW CREATE TABLE $dbname2.$table" | mysql -N -u $username --password=$password mysql | sed -e 's,^.*CREATE TABLE,CREATE TABLE,g' -e 's,\\n,\n,g' | mysql -u $username --password=$password mysql; echo "INSERT INTO $dbname2.$table SELECT * FROM $dbname1.$table" | mysql -u $username --password=$password mysql; done

Uwaga!!! Metoda zadziała tylko dla baz danych z tabelami Engine=MyISAM, których dane przechowywane są w plikach *.MYD a indeksy w plikach *.MYI, których zamknięcie (plików z danymi i indeksami) poprzez wywołanie zapytania FLUSH TABLES;.
W przypadku większych baz danych można zaryzykować modyfikację w systemie plików.
1. Zrzut polecenia tworzącego bazę danych, zmiana nazwy poleceniem sed i wykonanie polecenia tworzącego bazę danych.
2. Zrzut polecenia tworzącego tabelę, zmiana nazwy bazy danych (jeśli tabela jest z prefixem w postaci nazwy bazy danych z kropką baza.) i wykonanie polecenia tworzącego tabelę
3. Skopiowanie/Przeniesieniu plików (a nawet całego katalogu po uprzednim usunięciu istniejącego) do nowej lokalizacji.
username="root"; password="password"; dbname1="database1"; dbname2="database2";
#echo "SHOW CREATE DATABASE $dbname1" | mysql -N -u $username --password=$password mysql | sed -e "s,^.*CREATE DATABASE,CREATE DATABASE,g" -e
#echo "CREATE DATABASE $dbname2 ..." | mysql -u $username --password=$password mysql
echo "SHOW CREATE DATABASE $dbname1" | mysql -N -u $username --password=$password mysql | sed -e "s,^.*CREATE DATABASE,CREATE DATABASE,g" -e 's,\\n,\n,g' -e "s,$dbname1,$dbname2,g" | mysql -u $username --password=$password mysql
for table in `echo "show tables from $dbname1" | mysql  -N -u $username --password=$password mysql`;do echo "table=$table"; echo "SHOW CREATE TABLE $dbname2.$table" | mysql -N -u $username --password=$password mysql | sed -e 's,^.*CREATE TABLE,CREATE TABLE,g' -e 's,\\n,\n,g' | mysql -u $username --password=$password mysql; done

Należy wykonać tylko jeden!!! z poniższym kroków, w zależności od preferencji.
3. (wersja 1) Skopiowanie plików do nowej lokalizacji.
username="root"; password="password"; dbname1="database1"; dbname2="database2";
cp -av /srv/mysql/$dbname1/* /srv/mysql/$dbname2/
3. (wersja 2) Przeniesienie plików do nowej lokalizacji.
username="root"; password="password"; dbname1="database1"; dbname2="database2";
mv -av /srv/mysql/$dbname1/* /srv/mysql/$dbname2/
3. (wersja 3) Przeniesienie całego katalogu (po uprzednim usunięciu istniejącego) do nowej lokalizacji.
username="root"; password="password"; dbname1="database1"; dbname2="database2";
# skasowanie struktury danych utworzonej w krokach 1 i 2
rm -rf /srv/mysql/$dbname2
mv -av /srv/mysql/$dbname1 /srv/mysql/$dbname2


#top DATABASE DROP


Zobacz także DATABASE DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/drop-database.html

Składnia:
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Example: usunięcie bazy danych wraz z odebraniem nadanych uprawnień do bazy danych nadanych użytkownikowi temp@localhost
DROP DATABASE temp;
REVOKE SELECT,INSERT,UPDATE,DELETE ON temp.* FROM temp@localhost;
-- DROP USER temp@localhost;
FLUSH PRIVILEGES;



#top schemas


#top SCHEMA CREATE


Zobacz także SCHEMA CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

CREATE SCHEMA is a synonym for CREATE DATABASE.


#top SCHEMA ALTER


Zobacz także SCHEMA ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

ALTER SCHEMA is a synonym for ALTER DATABASE.


#top SCHEMA DROP


Zobacz także SCHEMA DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

DROP SCHEMA is a synonym for DROP DATABASE.



#top tablespaces


#top TABLESPACE CREATE


Zobacz także TABLESPACE CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html

Składnia:
CREATE TABLESPACE tablespace_name
    ADD DATAFILE 'file_name'
    USE LOGFILE GROUP logfile_group
    [EXTENT_SIZE [=] extent_size]
    [INITIAL_SIZE [=] initial_size]
    [AUTOEXTEND_SIZE [=] autoextend_size]
    [MAX_SIZE [=] max_size]
    [NODEGROUP [=] nodegroup_id]
    [WAIT]
    [COMMENT [=] comment_text]
    ENGINE [=] engine_name

Example:
-- comment


#top TABLESPACE ALTER


Zobacz także TABLESPACE ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/alter-tablespace.html

Składnia:
ALTER TABLESPACE tablespace_name
    {ADD|DROP} DATAFILE 'file_name'
    [INITIAL_SIZE [=] size]
    [WAIT]
    ENGINE [=] engine_name

Example:
-- comment


#top TABLESPACE DROP


Zobacz także TABLESPACE DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/drop-tablespace.html

Składnia:
DROP TABLESPACE tablespace_name
    ENGINE [=] engine_name

Example:
-- comment



#top tables


#top TABLE CREATE


Zobacz także TABLE CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Składnia:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)] [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length) [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:
    table_option [[,] table_option] ...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE(expr)
        | LIST(expr) }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES 
            {LESS THAN {(expr) | MAXVALUE} 
            | 
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some valid select statement)


#top TABLE CREATE LIKE


Zobacz także TABLE CREATE LIKE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example:
CREATE TABLE temp_table_like LIKE temp_table;


#top TABLE CREATE AS


Zobacz także TABLE CREATE AS dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example:
Nie jest to wierna kopia struktury originalnej tabeli !!!
CREATE TABLE temp_table_like AS SELECT colname1, colname1, colname2, colname3 FROM table_like;


#top TABLE CREATE with PRIMARY KEY


Zobacz także TABLE CREATE with PRIMARY KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with PRIMARY KEY
CREATE TABLE temp_prikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


#top TABLE CREATE with auto_increment


Zobacz także TABLE CREATE with auto_increment dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with auto_increment
CREATE TABLE temp_autoinc (
idtemp int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


#top TABLE CREATE with UNIQUE KEY (singlecolumn)


Zobacz także TABLE CREATE with UNIQUE KEY (singlecolumn) dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with UNIQUE KEY
CREATE TABLE temp_prikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
UNIQUE KEY name (name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


#top TABLE CREATE with UNIQUE KEY (multicolumn)


Zobacz także TABLE CREATE UNIQUE KEY (multicolumn) dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with UNIQUE KEY (multicolumn)
Podczas próby utworzenia tabeli z kolumnami name varchar(255), value varchar(255) jak w powyższym przykładzie tabeli może wystąpić błąd:
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

1. Rozwiązaniem problemu jest utworzenie klucza z użyciem części pól:
CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
UNIQUE KEY name_value (name(160),value(160))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. Rozwiązaniem problemu jest utworzenie tabeli z mniejszymi polami:
CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(160) NOT NULL,
value varchar(160) NOT NULL,
UNIQUE KEY name_value (name,value)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


#top TABLE CREATE with FULLTEXT KEY


Zobacz także TABLE CREATE with FULLTEXT KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with FULLTEXT KEY
CREATE TABLE words_fulltext (
idword int(9) NOT NULL auto_increment,
title varchar(255) NOT NULL,
body text NOT NULL,
PRIMARY KEY  (idword),
FULLTEXT (body)
) DEFAULT CHARSET=utf8;


INSERT INTO words_fulltext (title,body) VALUES ('friend metting','my friend come to me');
INSERT INTO words_fulltext (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...');
INSERT INTO words_fulltext (title,body) VALUES ('How To Use MySQL Well','After you went through a ...');
INSERT INTO words_fulltext (title,body) VALUES ('Optimizing MySQL','In this tutorial we will show ...');
INSERT INTO words_fulltext (title,body) VALUES ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');
INSERT INTO words_fulltext (title,body) VALUES ('MySQL vs. YourSQL','In the following database comparison ...');
INSERT INTO words_fulltext (title,body) VALUES ('MySQL Security','When configd properly, MySQL ...');


SELECT * FROM words_fulltext WHERE MATCH (body) AGAINST ('friend');
+--------+----------------+----------------------+
| idword | title          | body                 |
+--------+----------------+----------------------+
|      1 | friend metting | my friend come to me |
+--------+----------------+----------------------+

SELECT * FROM words_fulltext WHERE MATCH (body) AGAINST ('database');
+--------+-------------------+------------------------------------------+
| idword | title             | body                                     |
+--------+-------------------+------------------------------------------+
|      6 | MySQL vs. YourSQL | In the following database comparison ... |
|      2 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+--------+-------------------+------------------------------------------+

SELECT * FROM words_fulltext WHERE MATCH (body) AGAINST ('come');
Empty set (0.00 sec)


#top TABLE CREATE with FOREIGN KEY


Zobacz także TABLE CREATE with FOREIGN KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with FOREIGN KEY (Engine=InnoDB)
CREATE TABLE users (
id int(11) NOT NULL,
login varchar(30) NOT NULL DEFAULT '',
hashed_password varchar(40) NOT NULL DEFAULT '',
firstname varchar(30) NOT NULL DEFAULT '',
lastname varchar(30) NOT NULL DEFAULT '',
mail varchar(60) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE user_preferences (
id int(11) NOT NULL,
user_id int(11) NOT NULL DEFAULT '0',
others text,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;


#top TABLE CREATE with INDEX


Zobacz także TABLE CREATE with INDEX dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with INDEX
CREATE TABLE temp_index (
idtemp int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY  (idtemp),
INDEX nameidx USING HASH(name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


#top TABLE CREATE with partitioned


Zobacz także TABLE CREATE with partitioned dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with: DATA DIRECTORY + INDEX DIRECTORY
echo "SHOW VARIABLES LIKE 'have_symlink'" | mysql -u root -p mysql

result:
Variable_name   Value
have_symlink    DISABLED

enable symbolic links by server command line /etc/sysconfig/mysqld
MYSQLD_OPTIONS="--symbolic-links"

enable symbolic links by server configuration /etc/mysqld/my.cnf
#symbolic-links=0


echo "SHOW VARIABLES LIKE 'have_symlink'" | mysql -u root -p mysql


Variable_name   Value
have_symlink    YES

create storage directory:
mkdir -p /srv/mydata/edata /srv/mydata/eindex
chown -R mysql:mysql /srv/mydata

now create table with DATA DIRECTORY + INDEX DIRECTORY
mysql -u root -p mysql
DROP DATABASE IF EXISTS partitioneddb;
CREATE DATABASE partitioneddb DEFAULT charset=utf8;

mysql -u root -p partitioneddb
CREATE TABLE partitionedtablemyisam (
iduser    int(9) NOT NULL,
startdate date NOT NULL,
enddate   date NOT NULL,
INDEX iduser_2 USING HASH (iduser),
INDEX startdate_2 USING BTREE (startdate),
INDEX enddate_2 USING BTREE (enddate)
) ENGINE=MyISAM DATA DIRECTORY='/srv/mydata/edata' INDEX DIRECTORY='/srv/mydata/eindex' DEFAULT CHARSET=utf8;

CREATE TABLE partitionedtableinnodb (
iduser    int(9) NOT NULL,
startdate date NOT NULL,
enddate   date NOT NULL,
INDEX iduser_2 USING HASH (iduser),
INDEX startdate_2 USING BTREE (startdate),
INDEX enddate_2 USING BTREE (enddate)
) ENGINE=InnoDB DATA DIRECTORY='/srv/mydata/edata' INDEX DIRECTORY='/srv/mydata/eindex' DEFAULT CHARSET=utf8;


INSERT INTO partitionedtablemyisam (iduser, startdate, enddate) VALUES(1, '2011/07/04 00:00:00', '2011/07/04 01:00:00');
INSERT INTO partitionedtablemyisam (iduser, startdate, enddate) VALUES(1, '2011/07/04 02:00:00', '2011/07/04 03:00:00');
INSERT INTO partitionedtablemyisam (iduser, startdate, enddate) VALUES(1, '2011/07/04 04:00:00', '2011/07/04 05:00:00');
INSERT INTO partitionedtablemyisam (iduser, startdate, enddate) VALUES(1, '2011/07/04 05:00:00', '2011/07/04 07:00:00');

INSERT INTO partitionedtableinnodb (iduser, startdate, enddate) VALUES(1, '2011/07/04 00:00:00', '2011/07/04 01:00:00');
INSERT INTO partitionedtableinnodb (iduser, startdate, enddate) VALUES(1, '2011/07/04 02:00:00', '2011/07/04 03:00:00');
INSERT INTO partitionedtableinnodb (iduser, startdate, enddate) VALUES(1, '2011/07/04 04:00:00', '2011/07/04 05:00:00');
INSERT INTO partitionedtableinnodb (iduser, startdate, enddate) VALUES(1, '2011/07/04 05:00:00', '2011/07/04 07:00:00');

check results
ll /srv/mysql/partitioneddb/ /srv/mydata/edata/ /srv/mydata/eindex/


/srv/mydata/edata/:
total 8
-rw-rw---- 1 mysql mysql 44 2013-05-19 23:20 partitionedtablemyisam.MYD

/srv/mydata/eindex/:
total 8
-rw-rw---- 1 mysql mysql 4096 2013-05-19 23:20 partitionedtablemyisam.MYI

/srv/mysql/partitioneddb/:
total 200
-rw-rw---- 1 mysql mysql     61 2013-05-19 23:19 db.opt
-rw-rw---- 1 mysql mysql   8640 2013-05-19 23:19 partitionedtableinnodb.frm
-rw-rw---- 1 mysql mysql 147456 2013-05-19 23:20 partitionedtableinnodb.ibd
-rw-rw---- 1 mysql mysql   8640 2013-05-19 23:19 partitionedtablemyisam.frm
lrwxrwxrwx 1 mysql mysql     44 2013-05-19 23:19 partitionedtablemyisam.MYD -> /srv/mydata/edata/partitionedtablemyisam.MYD
lrwxrwxrwx 1 mysql mysql     45 2013-05-19 23:19 partitionedtablemyisam.MYI -> /srv/mydata/eindex/partitionedtablemyisam.MYI


#top TABLE CREATE with COLLATE without UNIQUE


Zobacz także TABLE CREATE with COLLATE without UNIQUE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with COLLATE - without UNIQUE
SHOW COLLATION LIKE '%_cs';
SHOW COLLATION LIKE '%_ci';
SHOW COLLATION LIKE 'latin2%';
SHOW COLLATION LIKE 'utf8%';

SET NAMES 'utf8' COLLATE 'utf8_bin';
SET CHARACTER SET 'utf8';


CREATE TABLE words_cs (
idword int(9) NOT NULL auto_increment,
wordname varchar(255) character set utf8 collate utf8_bin NOT NULL,
PRIMARY KEY  (idword)
) DEFAULT CHARSET=utf8;

CREATE TABLE words_ci (
idword int(9) NOT NULL auto_increment,
wordname varchar(255) character set utf8 collate utf8_general_ci NOT NULL,
PRIMARY KEY  (idword)
) DEFAULT CHARSET=utf8;


INSERT INTO words_cs (wordname) VALUES ('ZAJĄC');
INSERT INTO words_cs (wordname) VALUES ('zając');
INSERT INTO words_cs (wordname) VALUES ('ZaJąC');
INSERT INTO words_cs (wordname) VALUES ('zAjĄc');

INSERT INTO words_ci (wordname) VALUES ('ZAJĄC');
INSERT INTO words_ci (wordname) VALUES ('zając');
INSERT INTO words_ci (wordname) VALUES ('ZaJąC');
INSERT INTO words_ci (wordname) VALUES ('zAjĄc');


SELECT * FROM words_cs;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+

SELECT * FROM words_ci;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+

SELECT * FROM words_cs WHERE wordname LIKE 'ZAJĄC';
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+

SELECT * FROM words_ci WHERE wordname LIKE 'ZAJĄC';
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+

SELECT * FROM words_cs WHERE wordname LIKE 'ZAJĄC' COLLATE utf8_polish_ci;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+

SELECT * FROM words_ci WHERE wordname LIKE 'ZAJĄC' COLLATE utf8_polish_ci;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+

SELECT * FROM words_cs WHERE wordname LIKE 'ZAJĄC' COLLATE utf8_bin;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+

SELECT * FROM words_ci WHERE wordname LIKE 'ZAJĄC' COLLATE utf8_bin;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+

SELECT * FROM words_cs WHERE LOWER(wordname)=LOWER('ZAJĄC');
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+

SELECT * FROM words_ci WHERE LOWER(wordname)=LOWER('ZAJĄC');
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+


#top TABLE CREATE with COLLATE with UNIQUE


Zobacz także TABLE CREATE with COLLATE with UNIQUE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: create table with COLLATE - with UNIQUE
SHOW COLLATION LIKE '%_cs';
SHOW COLLATION LIKE '%_ci';
SHOW COLLATION LIKE 'latin2%';
SHOW COLLATION LIKE 'utf8%';

SET NAMES 'utf8' COLLATE 'utf8_bin';
SET CHARACTER SET 'utf8';


CREATE TABLE words_cs (
idword int(9) NOT NULL auto_increment,
wordname varchar(255) character set utf8 collate utf8_bin NOT NULL,
PRIMARY KEY  (idword),
UNIQUE KEY wordname (wordname)
) DEFAULT CHARSET=utf8;

CREATE TABLE words_ci (
idword int(9) NOT NULL auto_increment,
wordname varchar(255) character set utf8 collate utf8_general_ci NOT NULL,
PRIMARY KEY  (idword),
UNIQUE KEY wordname (wordname)
) DEFAULT CHARSET=utf8;


INSERT INTO words_cs (wordname) VALUES ('ZAJĄC');
INSERT INTO words_cs (wordname) VALUES ('zając');
INSERT INTO words_cs (wordname) VALUES ('ZaJąC');
INSERT INTO words_cs (wordname) VALUES ('zAjĄc');
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

INSERT INTO words_ci (wordname) VALUES ('ZAJĄC');
INSERT INTO words_ci (wordname) VALUES ('zając');
INSERT INTO words_ci (wordname) VALUES ('ZaJąC');
INSERT INTO words_ci (wordname) VALUES ('zAjĄc');
Query OK, 1 row affected (0.00 sec)
ERROR 1062 (23000): Duplicate entry 'zając' for key 2
ERROR 1062 (23000): Duplicate entry 'zając' for key 2
ERROR 1062 (23000): Duplicate entry 'zając' for key 2


SELECT * FROM words_cs;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+

SELECT * FROM words_ci;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+

SELECT * FROM words_cs WHERE wordname LIKE 'ZAJĄC';
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+

SELECT * FROM words_ci WHERE wordname LIKE 'ZAJĄC';
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+

SELECT * FROM words_cs WHERE wordname LIKE 'ZAJĄC' COLLATE utf8_polish_ci;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+

SELECT * FROM words_ci WHERE wordname LIKE 'ZAJĄC' COLLATE utf8_polish_ci;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+

SELECT * FROM words_cs WHERE wordname LIKE 'ZAJĄC' COLLATE utf8_bin;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+

SELECT * FROM words_ci WHERE wordname LIKE 'ZAJĄC' COLLATE utf8_bin;
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+

SELECT * FROM words_cs WHERE LOWER(wordname)=LOWER('ZAJĄC');
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
|      2 | zając    |
|      3 | ZaJąC    |
|      4 | zAjĄc    |
+--------+----------+

SELECT * FROM words_ci WHERE LOWER(wordname)=LOWER('ZAJĄC');
+--------+----------+
| idword | wordname |
+--------+----------+
|      1 | ZAJĄC    |
+--------+----------+


#top TABLE ALTER


Zobacz także TABLE ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Składnia:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO|AS] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | PARTITION BY partitioning_expression
  | REMOVE PARTITIONING

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name

table_options:
    table_option [[,] table_option] ...  (see CREATE TABLE options)

partition_options:
    (see CREATE TABLE options)


#top TABLE ALTER with PRIMARY KEY


Zobacz także TABLE ALTER with PRIMARY KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: alter table with PRIMARY KEY
CREATE TABLE temp_prikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE temp_prikey ADD PRIMARY KEY (idtemp);


#top TABLE ALTER with auto increment


Zobacz także TABLE ALTER with auto_increment dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: alter table with auto increment
CREATE TABLE temp_prikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE temp_prikey ADD PRIMARY KEY (idtemp);
ALTER TABLE temp_prikey CHANGE idtemp idtemp int(11) NOT NULL auto_increment;


#top TABLE ALTER with UNIQUE KEY (singlecolumn)


Zobacz także TABLE ALTER with UNIQUE KEY (singlecolumn) dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: alter table with UNIQUE KEY
CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE temp_unikey ADD UNIQUE KEY name (name);


#top TABLE ALTER with UNIQUE KEY (multicolumn)


Zobacz także TABLE ALTER UNIQUE KEY (multicolumn) dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: alter table with UNIQUE KEY (multicolumn)
Podczas próby utworzenia unikatowego klucza na tabeli z kolumnami name varchar(255), value varchar(255) jak w powyższym przykładzie tabeli może wystąpić błąd:
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

1. Rozwiązaniem problemu jest utworzenie klucza z użyciem części pól:
CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE temp_unikey ADD UNIQUE KEY name_value (name(160),value(160));

2. Rozwiązaniem problemu jest utworzenie tabeli z mniejszymi polami:
CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(160) NOT NULL,
value varchar(160) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE temp_unikey ADD UNIQUE KEY name_value (name,value);


#top TABLE ALTER with FULLTEXT KEY


Zobacz także TABLE ALTER with FULLTEXT KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: alter table with FULLTEXT KEY
CREATE TABLE words_fulltext (
idword int(9) NOT NULL auto_increment,
title varchar(255) character set utf8 collate utf8_general_ci NOT NULL,
body text character set utf8 collate utf8_general_ci NOT NULL,
PRIMARY KEY  (idword)
) DEFAULT CHARSET=utf8;

ALTER TABLE words_fulltext ADD FULLTEXT KEY words_fulltext_body_idx (body);


#top TABLE ALTER with FOREIGN KEY


Zobacz także TABLE ALTER with FOREIGN KEY dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: alter table add FOREIGN KEY (Engine=InnoDB)
CREATE TABLE users (
id int(11) NOT NULL,
login varchar(30) NOT NULL DEFAULT '',
hashed_password varchar(40) NOT NULL DEFAULT '',
firstname varchar(30) NOT NULL DEFAULT '',
lastname varchar(30) NOT NULL DEFAULT '',
mail varchar(60) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE user_preferences (
id int(11) NOT NULL,
user_id int(11) NOT NULL DEFAULT '0',
others text,
PRIMARY KEY (id)
) Engine=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE user_preferences ADD FOREIGN KEY (user_id) REFERENCES users (id);


#top TABLE ALTER with INDEX


Zobacz także TABLE ALTER with INDEX dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Example: alter table add INDEX
CREATE TABLE temp_index (
idtemp int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY  (idtemp)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE INDEX temp_index_nameidx USING HASH ON temp_index (name);


#top TABLE ALTER with partitioned


Zobacz także TABLE ALTER with partitioned dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase


#top TABLE ALTER with COLLATE without UNIQUE


Zobacz także TABLE ALTER with COLLATE without UNIQUE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase


#top TABLE ALTER with COLLATE with UNIQUE


Zobacz także TABLE ALTER with COLLATE with UNIQUE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase



#top TABLE RENAME


Zobacz także TABLE RENAME dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/rename-table.html

Składnia:
RENAME TABLE tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

Example: rename table
ALTER TABLE temp_table RENAME TO temp_table2;
ALTER TABLE temp_table2 RENAME TO temp_table;


#top TABLE TRUNCATE


Zobacz także TABLE TRUNCATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

Składnia:
TRUNCATE [TABLE] tbl_name

Example:
TRUNCATE TABLE temp_table;


#top TABLE DROP


Zobacz także TABLE DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/drop-table.html

Składnia:
DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

Example:
DROP TABLE temp_prikey;

DROP TABLE user_preferences;
DROP TABLE users;

DROP TABLE temp_index;

DROP TABLE words_cs;
DROP TABLE words_ci;

DROP TABLE words_fulltext;



#top sequences


MySQL nie posiada obsługi sekwencji/generatorów wykorzystywanych do tworzenia pól zawierających wartość domyślną inkrementowaną (używaną często jako klucz główny) przy każdym wstawiany rekordu do tabeli. Taka funkcjonalność w MySQL została zaimplementowana poprzez atrybut auto_increment dla kolumny typu numerycznego.
Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
(An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.)


#top SEQUENCE CREATE


Zobacz także SEQUENCE CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Brak obsługi tworzenia sekwencji/generatorów!!! Przeczytaj komentarz powyżej - Sekwencje.


#top SEQUENCE ALTER


Zobacz także SEQUENCE ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Brak obsługi tworzenia sekwencji/generatorów!!! Przeczytaj komentarz powyżej - Sekwencje.


#top SEQUENCE DROP


Zobacz także SEQUENCE DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Brak obsługi tworzenia sekwencji/generatorów!!! Przeczytaj komentarz powyżej - Sekwencje.



#top indexes


#top INDEX CREATE


Zobacz także INDEX CREATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/create-index.html

Składnia:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name

Example: alter table add INDEX
CREATE TABLE temp_index (
idtemp int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY  (idtemp)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE INDEX temp_index_nameidx USING HASH ON temp_index (name);


#top INDEX ALTER


Zobacz także INDEX ALTER dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Brak obsługi modyfikacji indeksów!!! Zobacz tworzenie i usuwanie indeksów: INDEX CREATE i INDEX DROP.


#top INDEX DROP


Zobacz także INDEX DROP dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/drop-index.html

Składnia:
DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name

Example: alter table drop INDEX
DROP INDEX temp_index_nameidx ON temp_index;



#top functions


#top Functions and Operators


Zobacz także Functions and Operators dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/functions.html

Example:
-- comment



#top DATA Manipulations


#top SELECT with LIMIT and OFFSET


Zobacz także SELECT with LIMIT and OFFSET dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/select.html#id757300

Składnia:
SELECT ...
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

Example:
CREATE TABLE temp_limit (
idtemp int(9) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);

INSERT INTO temp_limit(idtemp,name,value) VALUES(1,'name-01','val-01');
INSERT INTO temp_limit(idtemp,name,value) VALUES(2,'name-02','val-02');
INSERT INTO temp_limit(idtemp,name,value) VALUES(3,'name-03','val-03');
INSERT INTO temp_limit(idtemp,name,value) VALUES(4,'name-04','val-04');
INSERT INTO temp_limit(idtemp,name,value) VALUES(5,'name-05','val-05');
INSERT INTO temp_limit(idtemp,name,value) VALUES(6,'name-06','val-06');
INSERT INTO temp_limit(idtemp,name,value) VALUES(7,'name-07','val-07');
INSERT INTO temp_limit(idtemp,name,value) VALUES(8,'name-08','val-08');
INSERT INTO temp_limit(idtemp,name,value) VALUES(9,'name-09','val-09');


Po wykonaniu powyższych zapytań mających na celu utworzenie tabeli oraz dodaniu przykładowych danych można wykonać poniższe zapytania w celu sprawdzenia działania klauzul LIMIT przy wyświetlaniu danych z tabeli:
SELECT * FROM temp_limit LIMIT 3;
SELECT * FROM temp_limit LIMIT 5;
SELECT * FROM temp_limit LIMIT 7;

Uzyskany rezultat powinien być analogiczny do poniższego:
idtemp  name    value
1       name-01 val-01
2       name-02 val-02
3       name-03 val-03

idtemp  name    value
1       name-01 val-01
2       name-02 val-02
3       name-03 val-03
4       name-04 val-04
5       name-05 val-05

idtemp  name    value
1       name-01 val-01
2       name-02 val-02
3       name-03 val-03
4       name-04 val-04
5       name-05 val-05
6       name-06 val-06
7       name-07 val-07


Po wykonaniu powyższych zapytań mających na celu utworzenie tabeli oraz dodaniu przykładowych danych można wykonać poniższe zapytania w celu sprawdzenia działania klauzul LIMIT przy wyświetlaniu danych z tabeli:
SELECT * FROM temp_limit LIMIT 0, 5;
SELECT * FROM temp_limit LIMIT 3, 5;
SELECT * FROM temp_limit LIMIT 6, 5;

Uzyskany rezultat powinien być analogiczny do poniższego:
idtemp  name    value
2       name-02 val-02
3       name-03 val-03
4       name-04 val-04
5       name-05 val-05
6       name-06 val-06

idtemp  name    value
5       name-05 val-05
6       name-06 val-06
7       name-07 val-07
8       name-08 val-08
9       name-09 val-09

idtemp  name    value
8       name-08 val-08
9       name-09 val-09


Po wykonaniu powyższych zapytań mających na celu utworzenie tabeli oraz dodaniu przykładowych danych można wykonać poniższe zapytania w celu sprawdzenia działania klauzul LIMIT przy wyświetlaniu danych z tabeli:
SELECT * FROM temp_limit LIMIT 5 OFFSET 0;
SELECT * FROM temp_limit LIMIT 5 OFFSET 3;
SELECT * FROM temp_limit LIMIT 5 OFFSET 6;

Uzyskany rezultat powinien być analogiczny do poniższego:
idtemp  name    value
2       name-02 val-02
3       name-03 val-03
4       name-04 val-04
5       name-05 val-05
6       name-06 val-06

idtemp  name    value
5       name-05 val-05
6       name-06 val-06
7       name-07 val-07
8       name-08 val-08
9       name-09 val-09

idtemp  name    value
8       name-08 val-08
9       name-09 val-09



#top WHERE IN subselect with multiple columns


Zobacz także WHERE IN subselect with multiple columns dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase



#top Deleting duplicates rows


Zobacz także Deleting duplicates rows dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase



#top REPLACE INTO


Zobacz także REPLACE INTO dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
http://dev.mysql.com/doc/refman/5.1/en/replace.html
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

Składnia:
REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
Składnia:
INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

Example:
-- comment



#top GROUP with CONCAT


Zobacz także REPLACE INTO dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

Dokumentacja MySQL:
https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

Składnia:
-- comment









































Zmodyfikowany ostatnio: 2017/11/01 23:59:01 (6 lat temu), textsize: 86,4 kB, htmlsize: 155 kB
Zapraszam do komentowania, zgłaszania sugestii, propozycji, własnych przykładów, ...
Dodaj komentarzKomentarze użytkowników