* TO 'user' 'localhost' IDENTIFIED BY 'password' REVOKE ALL PRIVILEGES ON base. * TO 'user' 'localhost' IDENTIFIED BY 'password' GRANT SELECT, INSERT, DELETE ON base. ALTER TABLE table ALTER field1 DROP DEFAULT ALTER TABLE table ADD new_name_field1 type1 ALTER TABLE table ADD new_name_field1 type1 FIRST ALTER TABLE table ADD new_name_field1 type1 AFTER another_field ALTER TABLE table DROP field1 ALTER TABLE table ADD INDEX ( field ) ALTER TABLE table ADD PRIMARY KEY ( field ) - Change field order: ALTER TABLE table MODIFY field1 type1 FIRST ALTER TABLE table MODIFY field1 type1 AFTER another_field ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER another_field ALTER TABLE old_name RENAME new_name KeysĬREATE USER 'user' 'localhost' IDENTIFIED BY 'password' GRANT ALL PRIVILEGES ON base. ALTER TABLE table ALTER field1 SET DEFAULT. ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL. DROP TEMPORARY TABLE table ALTER TABLE table MODIFY field1 type1 ALTER TABLE table MODIFY field1 type1 NOT NULL. , FOREIGN KEY ( fk_field1, fk_field2 ) REFERENCES table2 ( t2_fieldA, t2_fieldB )) CREATE TABLE table IF NOT EXISTS (.) CREATE TABLE new_tbl_name LIKE tbl_name CREATE TEMPORARY TABLE table (.) CREATE table new_table_name as SELECT FROM table_name DROP TABLE table DROP TABLE IF EXISTS table DROP TABLE table1, table2. , FOREIGN KEY ( fk_field1 ) REFERENCES table2 ( t2_fieldA ) ) CREATE TABLE table1 ( fk_field1 type1, fk_field2 type2. , PRIMARY KEY ( field1, field2 )) CREATE TABLE table1 ( fk_field1 type1, field2 type2. , PRIMARY KEY ( field1 )) CREATE TABLE table ( field1 type1, field2 type2. , INDEX ( field )) CREATE TABLE table ( field1 type1, field2 type2. ) CREATE TABLE table ( field1 type1, field2 type2. ) CREATE TABLE table ( field1 type1 unsigned not null auto_increment, field2 type2. Mysqldump -u -p | gzip > ĬREATE TABLE table ( field1 type1, field2 type2. SHOW VARIABLES WHERE Variable_name = 'hostname' ( source) Grant ALL access to user for * tables: GRANT ALL ON database.* TO Find out the IP Address of the Mysql Host List all users: SELECT User,Host FROM er Ĭreate new user: CREATE USER IDENTIFIED BY 'password' Rename column or table using an alias: SELECT. (The left table is the first table that appears in the statement.) Ĭombine rows from different tables but do not require the join condition: SELECT * FROM LEFT OUTER JOIN ON. FROM, Ĭombine rows from different tables: SELECT * FROM INNER JOIN ON. Get rounded average value and group by : SELECT, ROUND(AVG(), 2) FROM GROUP BY Multiple tables Get largest value in : SELECT MAX() FROM Import a database dump (more info here): mysql -u -p -h localhost = 00:00:00Ĭalculate total number of records: SELECT SUM() FROM Ĭount total number of and group by : SELECT, SUM() FROM GROUP BY Use -lock-tables=false option for locked tables (more info here). Removing table columns: ALTER TABLE DROP COLUMN ĭeleting databases: DROP DATABASE Ĭustom column output names: SELECT AS FROM Įxport a database dump (more info here): mysqldump -u -p > db_backup.sql Updating records: UPDATE SET = '' WHERE = ĭeleting records: DELETE FROM WHERE = ĭelete all records from a table (without dropping the table itself): DELETE FROM (This also resets the incrementing counter for auto generated columns like an id column.)ĭelete all records in a table: truncate table Select with custom order and only limit: SELECT * FROM WHERE ORDER BY ASC LIMIT (Order: DESC, ASC) Select a range: SELECT * FROM WHERE BETWEEN and Select records starting with val and ending with ue: SELECT * FROM WHERE LIKE '' Select records starting with : SELECT * FROM WHERE LIKE '%' Select records containing : SELECT * FROM WHERE LIKE '%%' Selecting specific records: SELECT * FROM WHERE = (Selectors:, != combine multiple selectors with AND, OR) Selecting parts of records: SELECT, FROM Ĭounting records: SELECT COUNT() FROM Ĭounting and selecting grouped records: SELECT *, (SELECT COUNT() FROM ) AS count FROM GROUP BY Selecting records: SELECT * FROM Įxplain records: EXPLAIN SELECT * FROM Inserting a record: INSERT INTO (, ) VALUES ('', ') List all indexes on a table: show index from Ĭreate new table with columns: CREATE TABLE ( VARCHAR(120), DATETIME) Īdding a column: ALTER TABLE ADD COLUMN VARCHAR(120) Īdding a column with an unique, auto-incrementing ID: ALTER TABLE ADD COLUMN int NOT NULL AUTO_INCREMENT PRIMARY KEY Access monitor: mysql -u -p (will prompt for password)Īccess database: mysql -u -p (will prompt for password)Ĭreate new database: create database ĭetermine what database is in use: select database()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |