Most commonly used MySQL commands

common.sql

-- Standard select
SELECT id, price FROM table_name;

-- Check not NULL
SELECT id, price FROM table_name WHERE consumer IS NOT NULL;

-- Update
UPDATE table_name SET price = 1;

-- Delete data
DELETE FROM Store_Information WHERE Store_Name = 'Los Angeles';

-- Show table rows
DESCRIBE table_name;

-- Delete column
ALTER TABLE table_name DROP row_name;

-- Insert
INSERT INTO kunden (name, adresse) VALUES ('Hans Meier', 'Wuppernweg 19')

-- Nutzer erstellen und gleichzeitig Zugang zu einer Datenbank gewähren
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

-- Drop user
DROP USER 'monty'@'%';

-- Change db engine
alter table card ENGINE=INNODB;

-- Rename column
ALTER TABLE agent_groups CHANGE COLUMN agent_group_id group_id bigint(20) NOT NULL;

-- Reset auto increment counter
ALTER TABLE ticket AUTO_INCREMENT = 1;

-- Replace Strings
UPDATE tablename SET column = REPLACE(column, "aaa", "bbb");

-- Dump wieder einspielen
mysql -u -p --database=webapp < webapp.sql

-- Eine Datenbank exportieren:
mysqldump --opt -u -p webapp > webapp.sql