MySQL Quick Notes

mySQL!

Some commands: First thing to do on a new install is to change the root password:
mysqladmin -u root password pwd
where “pwd” is the new password that you want to use (please apply basic password rules like at least 8 characters, use numbers and letters, upper-case and lower-case and symbols).

To add users to the database
insert into user (host,user,password) values (‘%’,’USERID’,password(‘PASSWORD’));
OR
grant all on tblName.* to userName identified by ‘pwd’;
and don’t forget to:
flush privileges;

To re-load the configuration
mysqladmin -u root -p reload To add a database to the system
insert into db values (‘%’,’DATABASENAME’,’USERID’,’y’,’y’,’y’,’y’,’y’,’y’,’y’,’y’,’y’,’y’);

Common SQL queries

SELECT

 SELECT fieldName,  fieldName, fieldName, ... FROM table
 WHERE fieldName [ operator ] value

 operator:  = <> >= <= LIKE NOT etc.

UPDATE

 UPDATE [ table ]
 SET   fieldName = value  ( numeric ) 
       fieldName = 'value' (  string )
 WHERE fieldName [ operator ] value 

 operator:  = <> >= <= LIKE NOT etc.

INSERT

 INSERT INTO table ( fieldName,  fieldName, fieldName, ... )
 VALUES( value, value, value, ... )

 operator:  = <> >= <= LIKE NOT etc.

DELETE

 DELETE FROM table
 WHERE fieldName [ operator ]  value

 operator:  = <> >= <= LIKE NOT etc.

Playing with Table Structure

 
CREATE TABLE

 CREATE TABLE tablename(
    field1 type(size),
    field2 int,
    field3 varchar(30))

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
  or    ADD [COLUMN] (create_definition, create_definition,...)
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ADD FULLTEXT [index_name] (index_col_name,...)
  or	ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition [FIRST | AFTER column_name]
  or    MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    DISABLE KEYS
  or    ENABLE KEYS
  or    RENAME [TO] new_tbl_name
  or    ORDER BY col
  or    table_options