Notes while working with MySQL database

Last Updated on: (senast uppdaterad på:)

  • CDing into MySQL Server 8.0
cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
  • Logging into MySQL Server:
.\mysql -u root -p
  • Shutting down server after logging in:
shutdown;
  • Starting up the server again via DOS (given it is administrator account):
net start mysql80
  • Show databases available:
show databases;
  • Start working with one of the databases:
use <database name>
  • View all records in a given table:
select * from <table name>;
  • Delete a record from a given table:
delete from <table name> where <condition>;
  • Rename a table:
ALTER TABLE table_name RENAME TO new_table_name;
  • Create a table:
CREATE TABLE `student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `forename` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ìd` (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  • Insert into table:
INSERT INTO student (id, forename, lastname, email) VALUES (20,"Bob","L.","test@test.com");
INSERT IGNORE INTO student (id, forename, lastname, email) VALUES (20,"Bob","L.","test@test.com");
  • save the last insert Id:
SET @last_id = LAST_INSERT_ID();
  • get the last insert Id:
SELECT @last_id;
  • Alter a table, add a column:
ALTER TABLE table_name ADD column_name datatype;

eg:

 ALTER TABLE `resource` ADD `url` varchar(255);
  • Alter a table column, i.e.:
    ALTER TABLE table_name MODIFY COLUMN column_name datatype; 
ALTER TABLE `users` MODIFY COLUMN id INT(11) NOT NULL;
  • Alter a table, drop a column:
ALTER TABLE `tableName` DROP COLUMN `columnName`;
  • Update a table column, i.e.:
    UPDATE table_name SET column1 = value1, column2 = value2, …
    WHERE condition; 
UPDATE `users` SET id=6 WHERE username='user1';
  • Add a foreign key to an existing table:
ALTER TABLE`tablename` ADD CONSTRAINT `fk_namedescrip` FOREIGN KEY (`fieldinthistable`) REFERENCES referringtable(fieldinthereferringtable);
  • Update one or two tables that have foreign keys which make updating rise a foreign key constraint.
-- set foreign key checks to 0 first...
SET FOREIGN_KEY_CHECKS = 0;
-- do the update, eg:
update authorities set username='AdminBob' where username='bob1';
-- return the foreign key check to 1, i.e.:
SET FOREIGN_KEY_CHECKS = 1;

Lämna ett svar