Johnathan Kong

1

ALTER TABLE tablename AUTO_INCREMENT = 1

The easiest way that I’ve found to import a csv file into a table is to create a table that has the exact columns as the file, then run the following command:

1
2
3
4

LOAD DATA LOCAL INFILE '<full path with file name>' 
INTO TABLE import 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'

For some reason, after changing the system time in linux, the MySQL server did not take the time. I ended up trying to search for how to update the time, but could not get any of the instructions working, so I ended up doing the following hack

1

SET time_zone = '-5:00';

I wanted to set the time zone to EST, but could not import it into the database with the mysql_tzinfo_to_sql, so I ended up having to set the offset of my desired time zone.

Add User:

1

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password'

Give user ALL permissions

1

GRANT ALL PRIVILEGES ON DATABASE.* TO 'new_user'@'localhost'

Reset a user password

1

SET PASSWORD FOR 'new_user'@'localhost' = PASSWORD('newpass');

Example of creating a new table in MySQL. This table uses the usual things that I forget all the time, primary key, auto increment, foreign key, and the engine type.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE TABLE fixed_expenses(
   id INT NOT NULL AUTO_INCREMENT,
   user_id INT,
   category_id INT,
   account_number VARCHAR(32),
   name VARCHAR(64),
   amount DECIMAL(8,2),
   frequency TINYINT,
   first_payment DATE,
   end_date DATE,
   reminder BOOL,
   PRIMARY KEY(id),
   FOREIGN KEY(user_id) REFERENCES users(id),
   FOREIGN KEY(category_id) REFERENCES expense_categories(id)
)ENGINE=InnoDB