PK  - Primary Key

NN  - Not Null

BIN - Binary (stores data as binary strings.  There is no character set so sorting and comparison is based on the numeric values of the bytes in the values.)

UN  - Unsigned (non-negative numbers only.  so if the range is -500 to 500, instead its 0 - 1000, the range is the same but it starts at 0)

UQ  - Create/remove Unique Key

ZF  - Zero-Filled (if the length is 5 like INT(5) then every field is filled with 0’s to the 5th digit.   12 = 00012, 400 = 00400, etc. )

AI  - Auto Increment

G - Generated column. i.e. value generated by a formula based on the other columns

Here is the source of these column flags

http://dev.mysql.com/doc/workbench/en/wb-table-editor-columns-tab.html

This exact question is answered on [mySql workbench-faq][1]:

&gt; Hover over an acronym to view a description, and see the Section 8.1.11.2, “The Columns Tab” and MySQL CREATE TABLE documentation for additional details.

That means hover over an acronym in the mySql Workbench table editor.

[Section 8.1.11.2, “The Columns Tab”][2]


  [1]: https://dev.mysql.com/doc/workbench/en/workbench-faq.html#faq-workbench-column-acronyms
  [2]: https://dev.mysql.com/doc/workbench/en/wb-table-editor-columns-tab.html

When I want to put a login system in place, I always compare the MD5 of the given password with its value in the users table on the server side.

However, a friend of mine told me that a &quot;clear&quot; password could be sniffed by a network software.

So my question is: is it a good idea to hash the password on the client side? Is it better than hashing it on the server side?


Is it worth hashing passwords on the client side

I&#39;ve written a function in C# that does a numerical differentiation. It looks like this:

    public double Diff(double x)
    {
        double h = 0.0000001;
    
        return (Function(x + h) - Function(x)) / h;
    }

I would like to be able to pass in any function, as in:

    public double Diff(double x, function f)
    {
        double h = 0.0000001;
    
        return (f(x + h) - f(x)) / h;
    }

I think this is possible with delegates (maybe?) but I&#39;m not sure how to use them.


C# Passing Function as Argument

In MySQL Workbench table editor there are 7 column flags available: PK, NN, UQ, BIN, UN, ZF, AI.

PK obviously stands for Primary Key. What about others?

What do column flags mean in MySQL Workbench?

<p>In MySQL Workbench table editor there are 7 column flags available: PK, NN, UQ, BIN, UN, ZF, AI.</p>
<p>PK obviously stands for Primary Key. What about others?</p>


I have two tables, keywords and data. 

Table keywords have 2 columns (id, keyword), table data have 3 columns (id[foreign key of keywords.id], name, value).

I am using this query:

    SELECT k.id, d.value, d.name
    FROM keywords AS k
    INNER JOIN data as d ON k.id = d.id

it returns something like:

    1 123 name1
    1 456 name2
    2 943 name1
    3 542 name1
    3 532 name2
    3 682 name3

Each id can have values from 0 to 3 (maybe more in the future).

How can I retrieve all the rows with the same id in the same row?

Like

    1 123 456
    2 943
    3 542 532 682

I want to do this because I want to be able to sort the values.

How to group mysql rows with same column value into one row?

Many database connection pooling libraries provide the ability to test their SQL connections for idleness. For example, the JDBC pooling library [c3p0][1] has a property called [`preferredTestQuery`][2], which gets executed on the connection at configured intervals. Similarly, Apache Commons DBCP has [`validationQuery`][3].

Many [example][4] [queries][5] I&#39;ve seen are for MySQL and recommend using `SELECT 1;` as the value for the test query. However, this query doesn&#39;t work on some databases (e.g. HSQLDB, for which `SELECT 1` expects a `FROM` clause).

Is there a database-agnostic query that&#39;s equivalently efficient but will work for all SQL databases?

**Edit:**

If there&#39;s not (which seems to be the case), can somebody suggest a set of SQL queries that will work for various database providers? My intention would be to programmatically determine a statement I can use based on my database provider configuration.


  [1]: http://www.mchange.com/projects/c3p0/index.html
  [2]: http://www.mchange.com/projects/c3p0/index.html#preferredTestQuery
  [3]:
https://commons.apache.org/proper/commons-dbcp/api-1.4/org/apache/commons/dbcp/BasicDataSource.html

  [4]: https://forum.hibernate.org/viewtopic.php?t=943079&amp;highlight=c3p0+connection+timeout
  [5]: http://forums.mysql.com/read.php?39,139821,160175

Efficient SQL test query or validation query that will work across all (or most) databases

I&#39;m hoping there&#39;s a simple way to do this without using a sub-query:

Scenario:  You have &quot;TableA&quot; with columns &quot;Key&quot;, &quot;SubKey&quot;, and &quot;Value&quot;.  I need to get the &quot;Value&quot; of the MAX(&quot;SubKey&quot;) for a given &quot;Key&quot;.

So if the Table contained the rows:

    KEY SUBKEY VALUE
    1   1      100
    1   2      200
    1   3      300

For Key = 1, I need the value 300.  I was hoping to do something like this:

    SELECT
      VALUE
    FROM
      TableA
    WHERE
      Key = 1
    HAVING
      SubKey = MAX(SubKey)

But that&#39;s a no-go.  Is there a way to do this without doing a &#39;WHERE SubKey = (subselect for max subkey)&#39;?

T-SQL: Selecting Column Based on MAX(Other Column)

How I can delete all records from all tables of my database? Can I do it with one SQL command or I need for one SQL command per one table?

Delete all data in SQL Server database

I try to do this, but it&#39;s a syntax error, what am I doing wrong?

    declare myid := insert into oameni values(default,&#39;lol&#39;) returning id;

my table:

    create table oameni
    (
     id   serial primary key,
     name varchar(10)
    );

Variable value assignment using RETURNING clause

How to set multiple columns of a table using update query in mysql?

setting multiple column using one update

I&#39;d like to get dumps of each mysql table into separate files. The manual indicates that the syntax for this is 

    mysqldump [options] db_name [tbl_name ...]

Which indicates that you know the table names before hand. I could set up the script that knows each table name now, but say I add a new table down the road and forget to update the dump script. Then I&#39;m missing dumps for one or more table. 

Is there a way to automagically dump each existing table into a separate file? Or am I going to have to do some script-fu; query the database, get all the table names, and dump them by name.

If I go the script-fu route, what scripting langauges can access a mysql database?


dump all mysql tables into separate files automatically?

I keep a record of logins in a table. I have columns for id, ip, date and time. From that record of logins I wanna fetch logins made only in the last hour. 

I&#39;m sweeping through the MySQL docs on time and date functions, but I just can&#39;t seem to combine them correctly. 

Can somebody help me?

Fetching rows added last hour

I have three tables named

    **Student Table**
    -------------
    id    name
    -------------
    1     ali
    2     ahmed
    3     john
    4     king

    **Course Table**
    -------------
    id    name
    -------------
    1     physic
    2     maths
    3     computer
    4     chemistry

    **Bridge**
    -------------
    sid    cid
    -------------
    1     1
    1     2
    1     3
    1     4
    2     1
    2     2
    3     3
    3     4
    4     1
    4     2

Now to show the student name with the course name which he had studied like,

    **Result**
    ---------------------------
    Student        Course
    ---------------------------
    ahmed         physic
    ahmed         maths
    ahmed         computer
    ahmed         chemistry
    ali           physic
    ali           maths
    john          computer
    john          chemistry
    king          physic
    king          maths

I build following query

    select s.name as Student, c.name as Course from student s, course c join bridge b on c.id = b.cid order by s.name

But it does not return the required result...

And what would be for normalized form, if I want to find who is manager over other:

    **employee**
    -------------------
    id        name
    -------------------
    1         ali
    2         king
    3         mak
    4         sam
    5         jon

    **manage**
    --------------
    mid      eid
    --------------
    1         2
    1         3
    3         4
    4         5

And wants to get this result:

    **result**
    --------------------
    Manager      Staff
    --------------------
    ali          king
    ali          mak
    mak          sam
    sam          jon


Joining three tables using MySQL

For CakePHP application, I created MySQL database. 

Which tool to be used to create ER Diagram of database? Fields and relations between tables are created in a way cakePHP likes.


thank you in advance!

Generate ER Diagram from existing MySQL database, created for CakePHP

I&#39;m using MySQL Workbench CE 5.2.30 CE / Rev 6790 . When execute the following statement:

    SELECT OLD_PASSWORD(&quot;test&quot;)
I only get back a nice `BLOB` icon, I need to left-click to select the cell, right-click and choose &quot;Open Value in viewer&quot; and select the &quot;Text&quot; tab.

Using the same with phpMyAdmin, I get directly back the value of the `OLD_PASSWORD` call. It&#39;s just an example, but is there a way to directly see such results in the output?

How can I directly view blobs in MySQL Workbench

I&#180;ve got a mysql-server that I&#180;m administrate remotely with MySQL Workbench.

Now I&#180;ve got a new computer and I cant find any solution to move my connections and instances-profiles to my new computer. This can&#180;t be an unsolved question, huh? Not the first time this would happen for someone else.

Correction: It&#180;s not the server-instances that I want to move. I need to export/move/backup my many client-profiles/instances-connections in MySQL Workbench.

Moving connections and instances between two computers

I want to take the whole database. Where do I find the database file?

And is there a way to write the whole database with all data to a text file (like the one in SQL Server)?


How to generate the whole database script in MySQL Workbench?

I am working on a moderately complex schema in MySQL Workbench, and the single page of the EER diagram is now full up. Does anyone know how to enlarge it to two or more pages?

Content Type	Original Author	Original Content on Stackoverflow
Question	Ivan	View Question on Stackoverflow
Solution 1 - Sql	Vasiliy Kulakov	View Answer on Stackoverflow
Solution 2 - Sql	macio.Jun	View Answer on Stackoverflow
Solution 3 - Sql	cellepo	View Answer on Stackoverflow

What do column flags mean in MySQL Workbench?

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

C# Passing Function as Argument

Is it worth hashing passwords on the client side

Attributions