Difference between revisions of "MySQL"

From WhyAskWhy.org Wiki
Jump to: navigation, search
m (Added references)
m (Added cheat sheet entries for inserting a new row, updating a field value and cleaned up instructions for resetting root password.)
Line 39: Line 39:
 
<syntaxhighlight lang="mysql">
 
<syntaxhighlight lang="mysql">
 
source my-sql-file.sql;
 
source my-sql-file.sql;
 +
</syntaxhighlight>
 +
 +
 +
== Insert a new row ==
 +
 +
<ref name="mysql-wikibooks-cheatsheet" />
 +
 +
<syntaxhighlight lang="mysql">
 +
  INSERT INTO table_name (
 +
      `field_name1`,
 +
      `field_name2`,
 +
      `field_name3`
 +
  )
 +
  VALUES (
 +
      'Value1',
 +
      'Value2',
 +
      'Value3'
 +
  );
 +
</syntaxhighlight>
 +
 +
 +
== Update field ==
 +
 +
<ref name="mysql-wikibooks-cheatsheet" />
 +
 +
<syntaxhighlight lang="mysql">
 +
UPDATE table_name SET table_field = 'Value' WHERE id = '1';
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Line 60: Line 87:
 
<ref name="mysql-root-reset-1" />
 
<ref name="mysql-root-reset-1" />
 
<ref name="mysql-root-reset-2" />
 
<ref name="mysql-root-reset-2" />
The safest approach is to add the statement containing the password to a file and run
 
  
<syntaxhighlight lang="bash">
+
=== More Secure ===
 +
 
 +
<syntaxhighlight lang="bash" line="GESHI_FANCY_LINE_NUMBERS" highlight="5">
 +
nano /home/me/FILE
 +
# Add SQL statement from section below
 +
# Save & quit nano
 +
sudo /etc/init.d/mysql stop
 
mysqld_safe --init-file=/home/me/FILE &
 
mysqld_safe --init-file=/home/me/FILE &
 +
rm /home/me/FILE
 +
sudo /etc/init.d/mysql restart
 
</syntaxhighlight>
 
</syntaxhighlight>
  
followed by:
+
=== Less secure ===
  
<syntaxhighlight lang="bash">
+
<syntaxhighlight lang="bash" line="GESHI_FANCY_LINE_NUMBERS">
 
sudo /etc/init.d/mysql stop
 
sudo /etc/init.d/mysql stop
 
sudo mysqld --skip-grant-tables &
 
sudo mysqld --skip-grant-tables &
 
mysql -u root mysql
 
mysql -u root mysql
</syntaxhighlight>
 
 
<syntaxhighlight lang="mysql">
 
 
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
 
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
 
</syntaxhighlight>
 
</syntaxhighlight>
Line 104: Line 135:
 
<ref name="mysql-database-engine-type" />
 
<ref name="mysql-database-engine-type" />
  
<syntaxhighlight lang="mysql">
+
<syntaxhighlight lang="mysql" >
 
USE DATABASE database_name;
 
USE DATABASE database_name;
 
SHOW CREATE TABLE table_name;
 
SHOW CREATE TABLE table_name;
Line 126: Line 157:
 
<ref name="mysql-database-character-set">[http://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-database-table-column-is-in-mysql How do I see what character set a database / table / column is in MySQL?]</ref>
 
<ref name="mysql-database-character-set">[http://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-database-table-column-is-in-mysql How do I see what character set a database / table / column is in MySQL?]</ref>
  
 +
<ref name="mysql-wikibooks-cheatsheet">[http://en.wikibooks.org/wiki/MySQL/CheatSheet WikiBooks - MySQL CheatSheet]</ref>
 
</references>
 
</references>

Revision as of 20:04, 6 March 2014


Character length restrictions

[1] [2]

  • 64 characters for database name
  • 16 characters for user name
  •  ? characters for password length
    • Seems to be dependent on the version


Create a database

CREATE DATABASE my_test_db CHARACTER SET utf8;


Create a user for that database with full privileges to it

CREATE USER 'my_test_db_usr'@'localhost' IDENTIFIED BY 'INITIAL_PASSWORD';
GRANT ALL PRIVILEGES ON my_test_db.* TO 'my_test_db_usr'@'localhost';


Reset password for user account

SET PASSWORD for 'my_test_db_usr'@'localhost' = PASSWORD('REAL_PASSWORD_HERE');


Import or run SQL statements from file

source my-sql-file.sql;


Insert a new row

[3]

  INSERT INTO table_name (
      `field_name1`, 
      `field_name2`, 
      `field_name3`
  )
  VALUES (
      'Value1', 
      'Value2', 
      'Value3'
  );


Update field

[3]

UPDATE table_name SET table_field = 'Value' WHERE id = '1';


Delete user account

DROP USER 'my_test_db_usr'@'localhost';


Delete database

DROP DATABASE 'my_test_db';


Resetting the root user account

[4] [5]

More Secure

1 nano /home/me/FILE
2 # Add SQL statement from section below
3 # Save & quit nano
4 sudo /etc/init.d/mysql stop
5 mysqld_safe --init-file=/home/me/FILE &
6 rm /home/me/FILE
7 sudo /etc/init.d/mysql restart

Less secure

1 sudo /etc/init.d/mysql stop
2 sudo mysqld --skip-grant-tables &
3 mysql -u root mysql
4 UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;


Viewing privileges granted to a user

[6] [7]

  • select * from mysql.user;
    
    • Parse results by eye (not pretty)
  • SHOW GRANTS FOR username@ipaddress;
    
  • SHOW GRANTS;
    
    • For current user


Show the character set for a database

[8]

SHOW CREATE DATABASE database_name;


Show the database engine type

[9]

USE DATABASE database_name;
SHOW CREATE TABLE table_name;


References

  1. 6.3.1 User Names and Passwords
  2. 9.2 Schema Object Names
  3. 3.0 3.1 WikiBooks - MySQL CheatSheet
  4. Set / Change / Reset the MySQL root password on Ubuntu Linux
  5. MySQL :: MySQL 5.0 Reference Manual :: C.5.4.1 How to Reset the Root Password
  6. How can I show user's privileges in MySQL?
  7. How to show user privileges on mysql
  8. How do I see what character set a database / table / column is in MySQL?
  9. How can I check MySQL engine type for a specific table?