Results 1 to 4 of 4
  1. #1
    dbblogger is offline Member
    Join Date
    Jun 2012
    Posts
    1
    Rep Power
    0

    Post Sample to create, insert, modify and delete MySQL Objects

    Found some good samples on how to Create, Insert, Modify and Delete schema, tables, views, indexes, constraints, stored procedures, functions, triggers.

    Hope it is helpful.


    From : DtSQL - MySQL Features

    Schema

    Create Schema
    MySQL allows to create a new schema. After entering the schema name, DtSQL can generate and execute the SQL to create the new schema.

    Sample : CREATE DATABASE TEST_SCHEMA

    Drop Schema
    MySQL allows to drop an existing schema. After entering the schema name, DtSQL can generate and execute the SQL to drop the schema.

    Sample : DROP DATABASE TEST_SCHEMA





    Table

    Create Table
    MySQL allows to create a new table. After entering the table name and column information, DtSQL can generate and execute the SQL to create the table.

    ■Column Name
    ■Column Type
    ■Length/Precesion
    ■Scale (decimal)
    ■Default Value
    ■Nullable
    ■Primary Key
    ■Unique
    Sample :
    CREATE TABLE TEST_SCHEMA.TEST_TABLE (
    a bigint NOT NULL DEFAULT 1,
    b char ( 12 ) NOT NULL DEFAULT ‘abc’,
    c decimal ( 12, 2 ) ,

    PRIMARY KEY ( a, b ),
    UNIQUE ( b,c )
    )

    Rename Table
    MySQL allows to rename an existing table. After entering the table name, DtSQL can generate and execute the SQL to rename the table.

    Sample : ALTER TABLE TEST_SCHEMA.TEST_TABLE RENAME TO TESTTABLE

    Truncate Table
    MySQL does not allows to truncate an table and deleting table is used instead. After entering the table name, DtSQL can delete all data in the table.

    Sample : DELETE FROM TEST_SCHEMA.TEST_TABLE

    Drop Table
    MySQL allows to drop an existing table. After entering the table name, DtSQL can generate and execute the SQL to drop the table.

    Sample : DROP TABLE TEST_SCHEMA.TEST_TABLE



    Column

    Add Column
    MySQL allows to add a new column to an existing table. After selecting the table and entering column information, DtSQL can generate and execute the SQL to add the new column to the table.

    ■Column Name
    ■Column Type
    ■Length/Precesion
    ■Scale (decimal)
    ■Default Value
    ■Nullable
    Sample :
    ALTER TABLE
    TEST_SCHEMA.TEST_TABLE
    ADD
    d varchar(20) NOT NULL DEFAULT ‘def’

    Change Data Type
    MySQL allows to change the column data. After selecting the new data type, DtSQL can generate and execute the SQL to change the column data type.

    Sample :
    ALTER TABLE TEST_SCHEMA.TEST_TABLE
    MODIFY COLUMN F BIGINT

    Drop Column
    MySQL allows to delete an existing column. After selecting the column, DtSQL can generate and execute the SQL to delete the column.

    Sample :
    ALTER TABLE TEST_SCHEMA.TEST_TABLE
    DROP COLUMN F



    View

    Create View
    MySQL allows to create a new view. After entering the view name and select SQL, DtSQL can generate and execute the SQL to create the view.

    Sample :
    CREATE VIEW TEST_SCHEMA.TEST_VIEW
    AS
    SELECT * FROM TEST_SCHEMA.TEST_TABLE

    Drop View
    MySQL allows to drop an existing view. After selecting the view name, DtSQL can generate and execute the SQL to delete the view.

    Sample : DROP VIEW TEST_SCHEMA.TEST_VIEW



    Index

    Create Index
    MySQL allows to create a new index. After selecting the table columns and entering the view name, DtSQL can generate and execute the SQL to create the index.

    Sample :
    CREATE UNIQUE INDEX TEST_SCHEMA.TEST_INDEX
    ON TEST_SCHEMA.TEST_TABLE ( A, B, C )

    Drop Index
    MySQL allows to drop an existing index. After selecting the index name, DtSQL can generate and execute the SQL to delete the index.

    Sample : DROP INDEX TEST_SCHEMA.TEST_INDEX



    Trigger

    Create Trigger
    MySQL allows to create a new trigger. After entering the trigger name and trigger actions, DtSQL can generate and execute the SQL to create the trigger.

    Sample :
    CREATE TRIGGER TEST_SCHEMA.TEST_TRIGGER
    AFTER INSERT
    ON TEST_SCHEMA.TEST_TABLE
    FOR EACH ROW
    BEGIN
    UPDATE TEST_SCHEMA.TEST_TB SET COL1 = COL1 + 1;
    END

    Drop Trigger
    MySQL allows to drop an existing trigger. After selecting the trigger name, DtSQL can generate and execute the SQL to delete the trigger.

    Sample : DROP TRIGGER TEST_SCHEMA.TEST_TRIGGER



    Constraint

    Create Primary Key
    MySQL allows to create a primary key for a table. After selecting the table columns, DtSQL can generate and execute the SQL to create the primary key.

    Sample :
    ALTER TABLE
    TEST_SCHEMA.TEST_TABLE
    ADD
    PRIMARY KEY ( B, C )

    Create Foreign Key
    MySQL allows to create a foreign key for a table to reference to another table. After selecting the table columns and referenced table columns, DtSQL can generate and execute the SQL to create the foreign key.

    Sample :
    ALTER TABLE
    TEST_SCHEMA.TEST_TABLE
    ADD
    FOREIGN KEY ( A, C )
    REFERENCES TEST_SCHEMA.TEST_TB (B, A )

    Create Unique Constraint
    MySQL allows to create an unique constraint for a table columns. After entering the unique constraint name and selecting table columns, DtSQL can generate and execute the SQL to create the unique constraint.

    Sample :
    ALTER TABLE
    TEST_SCHEMA.TEST_TABLE
    ADD CONSTRAINT TEST_UNIQUE
    UNIQUE ( B, C )

    Drop Primary Key
    MySQL allows to drop an existing primary key. After selecting the primary key, DtSQL can generate and execute the SQL to delete the primary key.

    Sample : ALTER TABLE TEST_SCHEMA.TEST_TABLE DROP PRIMARY KEY

    Drop Foreign Key
    MySQL allows to drop an existing foreign key. After selecting the foreign key, DtSQL can generate and execute the SQL to delete the foreign key.

    Sample :
    ALTER TABLE TEST_SCHEMA.TEST_TABLE
    DROP FOREIGN KEY TEST_TABLE_IBFK_1

    Drop Unique Constraint
    MySQL allows to drop an existing unique constraint. After selecting the unique constraint, DtSQL can generate and execute the SQL to delete the unique constraint.

    Sample :
    ALTER TABLE
    TEST_SCHEMA.TEST_TABLE
    DROP INDEX TEST_UNIQUE



    Procedure

    Create Procedure
    MySQL allows to create procedures. After entering the procedure name, parameter names and procedure body, DtSQL can generate and execute the SQL to create the procedure.

    Sample :
    CREATE PROCEDURE TEST_SCHEMA.TEST_PROC
    ( IN PARAM1 VARCHAR(20), INOUT PARAM2 INT, OUT PARAM3 INT)
    BEGIN
    SELECT COUNT(*) INTO PARAM3 FROM information_schema.TABLES;
    END

    Drop Procedure
    MySQL allows to drop an existing procedure. After selecting the procedure, DtSQL can generate and execute the SQL to delete the procedure.

    Sample : DROP PROCEDURE TEST_SCHEMA.TEST_PROC



    Function

    Create Function
    MySQL allows to create functions. After entering the procedure name, parameter names and function body, DtSQL can generate and execute the SQL to create the function.

    Sample :
    CREATE FUNCTION TEST_SCHEMA.TEST_FUNC
    ( PARAM1 VARCHAR(20), PARAM2 BIGINT, PARAM3 DATE )
    RETURNS VARCHAR(30)
    RETURN CONCAT(‘Hello, ‘,s,’!')

    Drop Function
    MySQL allows to drop an existing function. After selecting the function, DtSQL can generate and execute the SQL to delete the function.

    Sample : DROP FUNCTION TEST_SCHEMA.TEST_FUNC

  2. #2
    KevinWorkman's Avatar
    KevinWorkman is offline Crazy Cat Lady
    Join Date
    Oct 2010
    Location
    Washington, DC
    Posts
    4,013
    Rep Power
    10

    Default Re: Sample to create, insert, modify and delete MySQL Objects

    I don't know what the point of this post is other than to advertise that site. I deleted your duplicate post in the Advanced Java section.

    Crossposted: Samples to Create, Insert, Modify and Delete MySQL Database Objects
    How to Ask Questions the Smart Way
    Static Void Games - Play indie games, learn from game tutorials and source code, upload your own games!

  3. #3
    KevinWorkman's Avatar
    KevinWorkman is offline Crazy Cat Lady
    Join Date
    Oct 2010
    Location
    Washington, DC
    Posts
    4,013
    Rep Power
    10

    Default Re: Sample to create, insert, modify and delete MySQL Objects

    I've deleted yet another duplicate post in the New to Java section. Stop. You're just creating more work for the moderators. If you continue spam posting the same thing in multiple forums, I'm going to have to ban you.
    How to Ask Questions the Smart Way
    Static Void Games - Play indie games, learn from game tutorials and source code, upload your own games!

  4. #4
    DarrylBurke's Avatar
    DarrylBurke is offline Forum Police
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,453
    Rep Power
    20

    Default Re: Sample to create, insert, modify and delete MySQL Objects

    Advertising doesn't belong in any of the technical forums. Moved from JDBC.

    The content posted isn't specific to JDBC or Java either. Any more posts like this and the poster will be both banned and reported as a forum spammer.

    db
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

Similar Threads

  1. Java Excel insert/update/delete/view
    By bdtuhin007 in forum JDBC
    Replies: 1
    Last Post: 11-04-2011, 03:44 AM
  2. Modify and delete certain xml entries
    By scheilaad in forum XML
    Replies: 2
    Last Post: 03-01-2011, 12:18 AM
  3. Replies: 1
    Last Post: 01-22-2009, 05:25 PM
  4. How to Modify,Delete data in File Txt???
    By hungleon88 in forum Advanced Java
    Replies: 9
    Last Post: 09-24-2008, 04:19 AM
  5. Modify/create AVI's INFO BLOCK
    By Agus211 in forum New To Java
    Replies: 0
    Last Post: 02-11-2008, 04:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •