Mysql Questions & Answers - 2
6). How to delete a table?
DROP TABLE command is used to delete a table.
Syntax:
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
[RESTRICT CASCADE]
DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed, so be careful with this statement! If any of the tables named in the argument list do not exist, MySQL returns an error indicating by name which non-existing tables it was unable to drop, but it also drops all of the tables in the list that do exist.
Important: When a table is dropped, user privileges on the table are not automatically dropped. See [HELP GRANT].
IF EXISTS is used to prevent an error from occurring if the table does not exist.
Example:
mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)
This deletes the table t1;
7). How to delete a database?
Syntax:
DROP {DATABASE SCHEMA} [IF EXISTS] db_name
DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database. DROP SCHEMA is a synonym for DROP DATABASE.
Important: When a database is dropped, user privileges on the database are not automatically dropped. See [HELP GRANT].
IF EXISTS is used to prevent an error from occurring if the database does not exist.
Example:
mysql> drop database sig;
Query OK, 0 rows affected (0.00 sec)
This deletes the database sig.
8). How to display all databases present?
The SHOW DATABASES is used to display all the databases in the system.
mysql> show databases;
+--------------------+
Database
+--------------------+
information_schema
mysql
test
+--------------------+
3 rows in set (0.16 sec)
9).How to create users in a system?
The CREATE USER command is used to create new users on mysql.
Syntax:
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new row in the mysql.user table that has no privileges. An error occurs if the account already exists. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you
specify only the username part of the account name, a hostname part of '%' is used. For additional information about specifying account names, see [HELP GRANT].
The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the PASSWORD keyword. See [HELP GRANT].
10). How to display all users in a system ?
All the users are stored as different rows in the user table in the mysql databse.
mysql> use mysql
Database changed
mysql> select user from user;
+--------+
user
+--------+
testuser1
testuser2
root
sig
+--------+
4 rows in set (0.00 sec)
11).How to Grant permissions to users?
All the permissions to users created on mysql are given using the grant commands. All the grants to a particular user can be viewed in the user table.
Syntax:
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name * *.* db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE
[{SSL
X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]
mysql> grant all on *.* to testuser1 identified by 'testuser1' with grant option;
Query OK, 0 rows affected (0.09 sec)
This will grant all rights to the user testuser1 having password testuser1. We can also grant the specific privileges by appropriately setting the different columns in the user table.
mysql> update user set Create_priv='Y' where user = 'testuser1';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
This grants the create privilege to the user testuser1.
ALSOLOOK http://www.devarticles.com/c/a/MySQL/Creating-Users-and-Setting-Permissions-in-MySQL/
12). How to get help in mysql?
The HELP command is used to get help on mysql.
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help- ', where
- is one of the following categories:
Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Plugins
Storage Engines
Stored Routines
Table Maintenance
Transactions
Triggers
DROP TABLE command is used to delete a table.
Syntax:
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
[RESTRICT CASCADE]
DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed, so be careful with this statement! If any of the tables named in the argument list do not exist, MySQL returns an error indicating by name which non-existing tables it was unable to drop, but it also drops all of the tables in the list that do exist.
Important: When a table is dropped, user privileges on the table are not automatically dropped. See [HELP GRANT].
IF EXISTS is used to prevent an error from occurring if the table does not exist.
Example:
mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)
This deletes the table t1;
7). How to delete a database?
Syntax:
DROP {DATABASE SCHEMA} [IF EXISTS] db_name
DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database. DROP SCHEMA is a synonym for DROP DATABASE.
Important: When a database is dropped, user privileges on the database are not automatically dropped. See [HELP GRANT].
IF EXISTS is used to prevent an error from occurring if the database does not exist.
Example:
mysql> drop database sig;
Query OK, 0 rows affected (0.00 sec)
This deletes the database sig.
8). How to display all databases present?
The SHOW DATABASES is used to display all the databases in the system.
mysql> show databases;
+--------------------+
Database
+--------------------+
information_schema
mysql
test
+--------------------+
3 rows in set (0.16 sec)
9).How to create users in a system?
The CREATE USER command is used to create new users on mysql.
Syntax:
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
The CREATE USER statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. For each account, CREATE USER creates a new row in the mysql.user table that has no privileges. An error occurs if the account already exists. Each account is named using the same format as for the GRANT statement; for example, 'jeffrey'@'localhost'. If you
specify only the username part of the account name, a hostname part of '%' is used. For additional information about specifying account names, see [HELP GRANT].
The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the PASSWORD keyword. See [HELP GRANT].
10). How to display all users in a system ?
All the users are stored as different rows in the user table in the mysql databse.
mysql> use mysql
Database changed
mysql> select user from user;
+--------+
user
+--------+
testuser1
testuser2
root
sig
+--------+
4 rows in set (0.00 sec)
11).How to Grant permissions to users?
All the permissions to users created on mysql are given using the grant commands. All the grants to a particular user can be viewed in the user table.
Syntax:
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name * *.* db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE
[{SSL
X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]
mysql> grant all on *.* to testuser1 identified by 'testuser1' with grant option;
Query OK, 0 rows affected (0.09 sec)
This will grant all rights to the user testuser1 having password testuser1. We can also grant the specific privileges by appropriately setting the different columns in the user table.
mysql> update user set Create_priv='Y' where user = 'testuser1';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
This grants the create privilege to the user testuser1.
ALSOLOOK http://www.devarticles.com/c/a/MySQL/Creating-Users-and-Setting-Permissions-in-MySQL/
12). How to get help in mysql?
The HELP command is used to get help on mysql.
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help
Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Plugins
Storage Engines
Stored Routines
Table Maintenance
Transactions
Triggers
This post describes many commands with tier syntax and examples. Everything is explained in detail and very simple language. I learned very easily. Thanks!
sap support costs