#mysql MySQL Snippets

mysql-snippets.sql

---------
-- Random Notes
-- From http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html
-- #1 boolean is not a distinct type in MySQL; just a synonym for tinyint(1);
-- #2 the values TRUE and FALSE are merely aliases for 1 and 0
-----------

/* match a string at the beginning of string */
SELECT 'Test' REGEXP '^The'; -- 0
SELECT 'The Test' REGEXP '^The'; -- 1

/* if a name is not prefixed with 'The ' then add it */
UPDATE [table]
SET Name = CONCAT('The ', TRIM(Name))
WHERE Name NOT REGEXP '^The'

/* copy a column from one table to another */
INSERT INTO [table] ([column]) SELECT [column] FROM [table]

/* remove all whitespace */
UPDATE [table] SET [column] = REPLACE([column], ' ', '')

/* (this is BASH) drop all tables in a database */
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

/* change to collation of a table and all of its existing columns */
alter table [table] convert to character set utf8 collate utf8_general_ci;

/* change root password */
-- Straightforward MySQL 101
mysqladmin -u root password [Type in selected password]
-- Changing users ROOT password
mysqladmin -u root -p [type old password] newpass [hit enter and type new password. Press enter]
-- Use mysql sql command 
mysql -u root -p
-- prompt “mysql>” pops up. Enter:
use mysql;
-- Enter user name you want to change password for
update user set password=PASSWORD (Type new Password Here) where User = 'username';
-- Don't forget the previous semicolon, now reload the settings for the users privileges
flush privileges; 
quit