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 but if there is an error, ignore generating error and stopping the flow
(see more here https://www.geeksforgeeks.org/sql-insert-ignore-statement/):
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;