Log in to MySQL as administrator:
mysql -u root -p
mysql> show databases;
Show tables in the database
mysql> use mysql; mysql> show tables;
Administration program for the mysqld daemon:
Another GUI administration program:
mysql-admin (note the ‘
Create a database called “finance”:
$ mysqladmin -u root -p create finance
Create a user who can work on the previously created finance database:
mysql> GRANT select, insert, update, delete, create, drop, index, alter, create temporary tables, lock tables ON finance.* TO 'myuser'@'localhost' IDENTIFIED BY 'mypass';
Now we have a user called “myuser” with the password “mypass” who can work on the “finance” database from localhost only.
Log in with the newly created user:
$ mysql -u myuser -p mysql> use finance; mysql> show tables;
You want to insert multiple records in a MySQL table (in a loop, for instance). How to improve the performance?
One way is to regroup all the inserts in one SQL command:
INSERT INTO x (a,b) VALUES ('1', 'one'), ('2', 'two'), ('3', 'three')
Credits go here.
Another way is to regroup the inserts in a transaction:
mysql_query("START TRANSACTION"); mysql_query("INSERT ..."); mysql_query("INSERT ..."); mysql_query("INSERT ..."); mysql_query("COMMIT"); // or "ROLLBACK" if you changed your mind
Credits go here.
You are using MySQL and you want to set foreign keys with phpMyAdmin.
By default, phpMyAdmin uses the storage engine MyISAM. If you want foreign keys, you must use the engine InnoDB instead. Steps:
- choose the table you want to modify
- choose the tab Operations
- here, change the Storage Engine to “InnoDB” (do this with all the tables in the current database)
- choose the tab Structure, then follow the link Relation view
- now you can set the foreign keys and decide what to do on delete/update