Sql mutations: Difference between revisions
From wikinotes
No edit summary |
No edit summary |
||
Line 39: | Line 39: | ||
</blockquote><!-- DROP --> | </blockquote><!-- DROP --> | ||
</blockquote><!-- Tables --> | </blockquote><!-- Tables --> | ||
= Columns = | |||
<blockquote> | |||
<syntaxhighlight lang="MySQL"> | |||
ALTER TABLE pet ADD COLUMN alive INT; # Add Column | |||
ALTER TABLE pet ADD COLUMN alive INT AFTER dead; # Add Column, and choose placement | |||
ALTER TABLE pet MODIFY alive dead INT; # Rename Column/modify datatype | |||
ALTER TABLE pet DROP COLUMN alive; # Delete Column | |||
</syntaxhighlight> | |||
</blockquote><!-- Columns --> | |||
= Rows = | = Rows = |
Revision as of 18:09, 19 September 2021
Changing data in your database.
Databases
CREATE DATABASE [IF NOT EXISTS] pets_db # Create Database SHOW DATABASES; # Show All Databases USE pets_db; # Select database to work with DROP DATABASE pets_db; # Delete Database
Tables
CREATE
CREATE TABLE users ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(10), last_name VARCHAR(10), age TINYINT );ALTER
ALTER TABLE users RENAME TO employees;DROP
DROP TABLE users;
Columns
ALTER TABLE pet ADD COLUMN alive INT; # Add Column ALTER TABLE pet ADD COLUMN alive INT AFTER dead; # Add Column, and choose placement ALTER TABLE pet MODIFY alive dead INT; # Rename Column/modify datatype ALTER TABLE pet DROP COLUMN alive; # Delete Column
Rows
INSERT
Add rows to a table.
Insert multiple rows.
# table ( columns ) # | | INSERT INTO tasks(subject, start_date, end_date, description) VALUES ('Task 1','2010-01-01','2010-01-02','Description 1'), ('Task 2','2010-01-01','2010-01-02','Description 2'), ('Task 3','2010-01-01','2010-01-02','Description 3');Insert row if match does not exist.
# Add 'Rupert' to users if does not exist INSERT INTO users (name, address, tele) SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp WHERE NOT EXISTS (SELECT name FROM users WHERE name = 'Rupert') LIMIT 1;UPDATE
Update an existing row.
UPDATE users set email = "user@domain.com" WHERE id = 190;DELETE
Delete an existing row.
DELETE FROM users WHERE id = 100;