MySQL DDL Operations: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(12 intermediate revisions by the same user not shown)
Line 18: Line 18:
GRANT ALL PRIVILEGES ON some_database.* TO 'someuser'@'localhost';
GRANT ALL PRIVILEGES ON some_database.* TO 'someuser'@'localhost';
FLUSH PRIVILEGES;
FLUSH PRIVILEGES;
</syntaxhighlight>
==Show Privileges==
<syntaxhighlight lang='sql'>
SHOW GRANTS FOR 'someuser'@'localhost';
</syntaxhighlight>
</syntaxhighlight>


==Delete User==
==Delete User==
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
DROP USER 'someuser'@'localhost';
</syntaxhighlight>
</syntaxhighlight>
=Database=
=Database=
==Display Databases==
==Display Databases==
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
SHOW DATABASES;
SHOW DATABASES;
</syntaxhighlight>
=Check if a Database Exists=
<syntaxhighlight lang='sql'>
SHOW DATABASES LIKE 'some_database';
</syntaxhighlight>
<syntaxhighlight lang='sql'>
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='some_database'"
</syntaxhighlight>
</syntaxhighlight>


Line 36: Line 49:
==Delete Database==
==Delete Database==
<syntaxhighlight lang='sql'>
<syntaxhighlight lang='sql'>
DROP DATABASE some_database;
</syntaxhighlight>
=Tables=
==Show Tables==
Show the tables in the database.
<syntaxhighlight lang='sql'>
SHOW TABLES;
</syntaxhighlight>
</syntaxhighlight>
==Create Table==
{{Internal|SQL_CREATE_ALTER_DROP_TABLE#CREATE|Standard SQL Syntax}}
=Views=
==Create View==
{{Internal|SQL_CREATE_ALTER_DROP_VIEW#CREATE|Standard SQL Syntax}}

Latest revision as of 17:08, 23 May 2024

Internal

Server Version

SELECT VERSION();

Users

Display Users

SELECT USER FROM mysql.user;

Create User

CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';

Grant to User All Privileges on a Database

GRANT ALL PRIVILEGES ON some_database.* TO 'someuser'@'localhost';
FLUSH PRIVILEGES;

Show Privileges

SHOW GRANTS FOR 'someuser'@'localhost';

Delete User

DROP USER 'someuser'@'localhost';

Database

Display Databases

SHOW DATABASES;

Check if a Database Exists

SHOW DATABASES LIKE 'some_database';
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='some_database'"

Create Database

CREATE DATABASE some_database;

Delete Database

DROP DATABASE some_database;

Tables

Show Tables

Show the tables in the database.

SHOW TABLES;

Create Table

Standard SQL Syntax

Views

Create View

Standard SQL Syntax