First we’ll login to the MySQL server from the command line with the following command:
mysql -u root -p
In this case, I’ve specified the user root with the -u flag, and then used the -p flag so MySQL prompts for a password. Enter your current password to complete the login.
If you need to change your root (or any other) password in the database, then follow this tutorial onchanging a password for MySQL via the command line.
You should now be at a MySQL prompt that looks very similar to this:
mysql>
Follow this tutorial if you have not yet created a database, or you want a list of databases.
View Selected Database in MySQL
When executing commands via the MySQL command line, a database may be explicitly specified. However, by default all MySQL operations run via the command line are performed on the currently selected database. Which database is currently selected? To find out issue the following command:
SELECT database();
Your result may be similar to this:
mysql> SELECT database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
The result is null, meaning a database is not currently selected.
Select a Database in MySQL
To select a database for use with subsequent MySQL operations, use the following command:
USE tutorial_database;
That command should yield a result of Database changed similar to:
mysql> USE tutorial_database;
Database changed
To verify the database has been selected simply issue the following command (which we ran previously):
mysql> SELECT database();
+-------------------+
| database() |
+-------------------+
| tutorial_database |
+-------------------+
1 row in set (0.00 sec)
To switch databases:
\connect database_name
SELECT USER FROM mysql.user;DROP USER 'jeffrey'@'localhost';
In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is ‘authentication_string’.
How to import a .sql file in MySQL?
Export:
mysqldump -u username –-password=your_password database_name > file.sqlImport:
mysql -u username –-password=your_password database_name < file.sql There has to be no space between -p and password
First choose the database:
mysql>use mysql;And then show the tables:
mysql>show tables;You will find the user table, now let’s see its fields:
mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)Surprise！There is no field named ‘password’, the password field is named ‘ authentication_string’. So, just do this:
update user set authentication_string=password('1111') where user='root';Now, everything will be ok.
Compared to MySQL 5.6, the changes are quite extensive: What’s New in MySQL 5.7
- To specify a password for an account at account-creation time, use
IDENTIFIED BYwith the literal cleartext password value:CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
The server assigns an authentication plugin to the account implicitly, as described previously, and assigns the given password. Clients must provide the given password when they connect.
If the implicitly assigned plugin is
mysql_native_password, theold_passwordssystem variable must be set to 0. Otherwise,CREATE USERdoes not hash the password in the format required by the plugin and an error occurs:mysql>
SET old_passwords = 1;mysql>CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. mysql>SET old_passwords = 0;mysql>CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';Query OK, 0 rows affected (0.00 sec) - To avoid specifying the cleartext password if you know its hash value (the value that
PASSWORD()would return for the password), specify the hash value preceded by the keywordPASSWORD:CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';
The server assigns an authentication plugin to the account implicitly, as described previously, and assigns the given password. The password hash must be in the format required by the assigned plugin. Clients must provide the password when they connect.
- Example: This statement changes the password for
jeffreybut leaves that forjeanneunchanged. For both accounts, connections are required to use SSL and each account can be used for a maximum of two simultaneous connections:ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY '
new_password', 'jeanne'@'localhost' REQUIRE SSL WITH MAX_USER_CONNECTIONS 2;How to get a list of MySQL views?
-
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';MySQL: STR_TO_DATE Function
This MySQL tutorial explains how to use the MySQL STR_TO_DATE function with syntax and examples.
Description
The MySQL STR_TO_DATE function takes a string and returns a date specified by a format mask.
Syntax
The syntax for the STR_TO_DATE function in MySQL is:
STR_TO_DATE( string, format_mask )
Parameters or Arguments
- string
- The string value to format as a date.
- format_mask
- The format to apply to string. The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.
Value Description %a Weekday name abbreviated (Sun to Sat) %b Month name abbreviated (Jan to Dec) %c Month as a numeric value (0 to 12) %D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, …) %d Day of the month as a numeric value (01 to 31) %e Day of the month as a numeric value (0 to 31) %f Microseconds (000000 to 999999) %H Hour (00 to 23) %h Hour (00 to 12) %I Hour (00 to 12) %i Minutes (00 to 59) %j Day of the year (001 to 366) %k Hour (00 to 23) %l Hour (1 to 12) %M Month name in full (January to December) %m Month name as a numeric value (00 to 12) %p AM or PM %r Time in 12 hour AM or PM format (hh:mm:ss AM/PM) %S Seconds (00 to 59) %s Seconds (00 to 59) %T Time in 24 hour format (hh:mm:ss) %U Week where Sunday is the first day of the week (00 to 53) %u Week where Monday is the first day of the week (00 to 53) %V Week where Sunday is the first day of the week (01 to 53)
Available starting in MySQL 3.23.8 and used with %X%v Week where Monday is the first day of the week (01 to 53)
Available starting in MySQL 3.23.8 and used with %X%W Weekday name in full (Sunday to Saturday) %w Day of the week where Sunday=0 and Saturday=6 %X Year for the week where Sunday is the first day of the week
Available starting in MySQL 3.23.8 and used with %V%x Year for the week where Monday is the first day of the week
Available starting in MySQL 3.23.8 and used with %v%Y Year as a numeric, 4-digit value %y Year as a numeric, 2-digit value
Note
- The STR_TO_DATE function will return a datetime value, if the string contains both valid date and time parts.
- The STR_TO_DATE function will return a date value, if the string contains only valid date parts.
- The STR_TO_DATE function will return a time value, if the string contains only valid time parts.
- The STR_TO_DATE function will return a NULL value, if it is unable to extract valid date and time parts using the format_mask.
Applies To
The STR_TO_DATE function can be used in the following versions of MySQL:
- MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
Example
Let’s look at some MySQL STR_TO_DATE function examples and explore how to use the STR_TO_DATE function in MySQL.
For example:
mysql> SELECT STR_TO_DATE('February 01 2014', '%M %d %Y'); Result: '2014-02-01' mysql> SELECT STR_TO_DATE('March,8,2014', '%M,%e,%Y'); Result: '2014-03-08' mysql> SELECT STR_TO_DATE('Friday, February 28, 2014', '%W, %M %e, %Y'); Result: '2014-02-28' mysql> SELECT STR_TO_DATE('2014,2,28 09', '%Y,%m,%d %h'); Result: '2014-02-28 09:00:00' mysql> SELECT STR_TO_DATE('2014,2,28 09,30,05', '%Y,%m,%d %h,%i,%s'); Result: '2014-02-28 09:30:05' mysql> SELECT STR_TO_DATE('10,15,30', '%h,%i,%s'); Result: '10:15:30'How to set-up MySQL for Python on Ubuntu
Installation
Starting with a vanilla Lucid install1, install pip and upgrade to the latest version:
apt-get install python-pip pip install -U pipNext, install the required development packages:
apt-get install python-dev libmysqlclient-devthen
pip install MySQL-pythonshould complete successfully.
Symptoms of missing headers
Without
libmysqlclient-dev, you’ll see something like this:Downloading/unpacking MySQL-python Running setup.py egg_info for package MySQL-python sh: mysql_config: not found Traceback (most recent call last): File "<string>", line 14, in <module> File "/home/vagrant/build/MySQL-python/setup.py", line 15, in <module> metadata, options = get_config() File "setup_posix.py", line 43, in get_config libs = mysql_config("libs_r") File "setup_posix.py", line 24, in mysql_config raise EnvironmentError("%s not found" % (mysql_config.path,)) EnvironmentError: mysql_config not found Complete output from command python setup.py egg_info: sh: mysql_config: not found Traceback (most recent call last): File "<string>", line 14, in <module> File "/home/vagrant/build/MySQL-python/setup.py", line 15, in <module> metadata, options = get_config() File "setup_posix.py", line 43, in get_config libs = mysql_config("libs_r") File "setup_posix.py", line 24, in mysql_config raise EnvironmentError("%s not found" % (mysql_config.path,)) EnvironmentError: mysql_config not foundWithout
python-dev, you’ll see something that ends with the following:... _mysql.c:2620: error: expected '=', ',', ';', 'asm' or '__attribute__' before '_mysql_ResultObject_Type' _mysql.c:2706: error: expected '=', ',', ';', 'asm' or '__attribute__' before '_mysql_methods' _mysql.c:2778: error: expected '=', ',', ';', 'asm' or '__attribute__' before '*' token _mysql.c:2810: warning: return type defaults to 'int' _mysql.c: In function 'DL_EXPORT': _mysql.c:2810: error: expected declaration specifiers before 'init_mysql' _mysql.c:2888: error: expected '{' at end of input error: command 'gcc' failed with exit status 1 ---------------------------------------- Command /usr/bin/python -c "import setuptools;__file__='/home/vagrant/build/MySQL-python/setup.py';exec(compile(open(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --single-version-externally-managed --record /tmp/pip-dPF1DK-record/install-record.txt failed with error code 1 Storing complete log in /home/vagrant/.pip/pip.log
weicode 