How to set/reset MySQL root password?

I am using XAMPP to run Apache server with PHP and MySQL. As you install XAMPP it set the MySQL password to empty by default. To change the password you can navigate to http://localhost/phpmyadmin (remember to include your port if you had to use a different port number than 80). Then click on SQL and enter the following query to reset the password to ‘whatever’

UPDATE mysql.USER SET Password=PASSWORD('whatever') WHERE USER='root'; FLUSH PRIVILEGES;

Press Go and you should get this screen:

Continue reading

INFORMATION_SCHEMA.COLUMNS

Contains one row for each column accessible to the current user in the current database. The INFORMATION_SCHEMA.COLUMNS view is based on the sysobjectsspt_data type_infosystypessyscolumnssyscommentssysconfigures, and syscharsets system tables.

To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name.
Continue reading

SQL: If Exists Update Else Insert

This is a pretty common situation that comes up when performing database operations. A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not. If we refer to the Books Online documentation, it gives examples that are similar to:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

This approach does work, however it might not always be the best approach. This will do a table/index scan for both the SELECT statement and the UPDATE statement. In most standard approaches, the following statement will likely provide better performance. It will only perform one table/index scan instead of the two that are performed in the previous approach.

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

Ref: http://blogs.msdn.com/b/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx

The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS

The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table

The REFERENTIAL_CONSTRAINTS table provides information about foreign keys.

Using the ON DELETE CASCADE Option
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.If you specify this option, later when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.For example, the all_candy table contains the candy_num column as a primary key. The hard_candy table refers to the candy_num column as a foreign key. The following CREATE TABLE statement creates the hard_candy table with the cascading-delete option on the foreign key:
CREATE TABLE all_candy
(candy_num SERIAL PRIMARY KEY,
candy_maker CHAR(25));CREATE TABLE hard_candy
(candy_num INT,
candy_flavor CHAR(20),
FOREIGN KEY (candy_num) REFERENCES all_candy
ON DELETE CASCADE)

Because ON DELETE CASCADE is specified for the dependent table, when a row of the all_candy table is deleted, the corresponding rows of the hard_candy table are also deleted. For information about syntax restrictions and locking implications when you delete rows from tables that have cascading deletes, see Considerations When Tables Have Cascading Deletes.

INFORMATION_SCHEMA Name SHOW Name Remarks
CONSTRAINT_CATALOG NULL
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
UNIQUE_CONSTRAINT_CATALOG NULL
UNIQUE_CONSTRAINT_SCHEMA
UNIQUE_CONSTRAINT_NAME
MATCH_OPTION
UPDATE_RULE
DELETE_RULE
TABLE_NAME
REFERENCED_TABLE_NAME

Refs: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls292.htm

GO Transact-SQL

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

Users must follow the rules for batches. For example, any execution of a stored procedure after the first statement in a batch must include the EXECUTE keyword. The scope of local (user-defined) variables is limited to a batch, and cannot be referenced after a GO command.

Continue reading

@ManyToOne and @OneToMany Java Annotations

The Many-to-One link is the foundation of persistent relations. @ManyToOne relation links a source table to a destination with a database REFERENCES column.
Many-to-One adds two capabilities: SQL extensions for links and direct lookup of target beans through field references.

The @OneToMany relation adds collection extensions to the query language and provides a Java Collection containing the children.
@OneToMany represents a collection of children belonging to a parent, like students in Gryffindor house at Hogwarts school.

A Many-to-One relation links one source entity to another target entity. A one-to-many relation links the target entity back to the source entity.

Continue reading

SQL REFERENCES keyword

The REFERENCES keyword is used to create a foreign key when creating a table. The basic formula to use is:

ColumnName DataType REFERENCES ParentTableName(ForeignKeyCcolumn)

The REFERENCES keyword is required. In the ParentTableName placeholder, enter the name of the primary table that holds the information that will be accessed in the current table. In the parentheses of ParentTableName, enter the name of the primary column of the parent table. Here is an example:

CREATE TABLE Persons
(
    PersonID AUTOINCREMENT(1,1) NOT NULL,
    FirstName VARCHAR(20),
    LastName VARCHAR(20) NOT NULL,
    GenderID INTEGER REFERENCES Genders(GenderID),
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);