some people type commands in MySQL like this:  "select * from RegisterUser where pid = '".$uploadPid."'"  and some type it like this  "SELECT * FROM RegisterUser WHERE pid = '".$uploadPid."'"  what's the best? should i scream or not
5565
Is SQL case sensitive.  I've used MySQL and SQL Server which both seem to be case in-sensitive.  Is this always the case?  Does the standard define case-sensitivity?
45618
I was browsing through the questions and noticed this:  SELECT prodid, issue FROM Sales  WHERE custid = @custid  AND datesold = SELECT MAX(datesold)               FROM Sales s               WHERE s.prodid = Sales.prodid                   AND s.issue = Sales.issue                   AND s.custid = @custid   I was wondering what the "@" does in front of custID?  Is it just a way of referencing the custID from the table being selected?
111051
How would you write a prepared MySQL statement in PHP that takes a differing number of arguments each time? An example such query is:  SELECT `age`, `name` FROM `people` WHERE id IN (12, 45, 65, 33)   The IN clause will have a different number of ids each time it is run.  I have two possible solutions in my mind but want to see if there is a better way.  Possible Solution 1 Make the statement accept 100 variables and fill the rest with dummy values guaranteed not to be in the table; make multiple calls for more than 100 values.  Possible Solution 2 Don't use a prepared statement; build and run the query checking stringently for possible injection attacks.
110974
I am just wondering if MariaDB or MySQL implements the &lt;table value constructor&gt; in the SQL Spec. In  and ?  SELECT * FROM ( VALUES (1) ) AS t(x);  x  ---  1 (1 row)   (syntax from ).
62599
Possible Duplicate:          I'm using the mysqli extension in PHP and I'm wondering, is there possibly any way to see a prepared query as it will be executed on the server, e.g. The query is something like this  select * from table1 where id = ? and name = ?   but I want to see the query after the values are filled in, like this:  select * from table1 where id = 20 and name = "John"
39428
I'm looking to find records in a table that match a specific number that the user enters.  So, the user may enter 12345, but this could be 123zz4-5 in the database. I imagine something like this would work, if PHP functions worked in MySQL. SELECT * FROM foo WHERE preg_replace(&quot;/[^0-9]/&quot;,&quot;&quot;,bar) = '12345'  What's the equivalent function or way to do this with just MySQL? Speed is not important.
88467
We have a question related to a DB query :   CREATE TABLE `Action` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `emp_Id` int(10) unsigned NOT NULL,   `name` varchar(60) NOT NULL,   `updated_action_at` datetime(3) DEFAULT NULL,   `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   `notes` varchar(400) DEFAULT NULL,   PRIMARY KEY (`id`),    KEY `action_empId_fk` (`emp_Id`),    CONSTRAINT `action_empId_fk` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,  ) ENGINE=InnoDB AUTO_INCREMENT=502004 DEFAULT CHARSET=latin1   CREATE TABLE `ActionAssignedTo` (   `action_Id` int(10) unsigned DEFAULT NULL,   `emp_Id` int(10) unsigned DEFAULT NULL,   KEY `actionassignedto_emp_id_foreign` (`emp_Id`),   KEY `actionassignedto_action_id_foreign` (`action_Id`),   CONSTRAINT `ActionAssignedTo_ibfk_1` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,   CONSTRAINT `ActionAssignedTo_ibfk_2` FOREIGN KEY (`action_Id`) REFERENCES `Action` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1  CREATE TABLE `Employee` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `vendor_Id` int(10) unsigned DEFAULT NULL,   `name` varchar(40) NOT NULL,   `mobile_Number` varchar(15) NOT NULL,   `active` tinyint(1) DEFAULT '1',   `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`id`),   KEY `employee_vendor_id_foreign` (`vendor_Id`),   CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`) ) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=latin1   We want to find the action created by employee with Action.empId = 4 along with the list of Assignee (an action can be assigned to multiple people)  We have the following query :   Select     notes,             Action.id,             AssigneeName.name,             ActionAssignedTo.emp_Id  from       Employee inner join Action  on         (Action.emp_Id = Employee.id  and        Action.emp_Id in (select Employee.id                               from Employee                               where Employee.vendor_Id = 1)) inner      join ActionAssignedTo  on         Action.id =  ActionAssignedTo.action_Id inner join Employee as AssigneeName  on         ActionAssignedTo.emp_Id =  AssigneeName.id where      Action.emp_Id = 4   Now, lets say if Action with id = 10 was assigned to 2 people : John (empId : 2) And Jack (empId : 5) --> the above query returns following results :  (columns from the query)  notes                       | Action.id | AssigneeName.name | ActionAssignedTo.emp_Id 1. Finish Designing Queries | 10        | John              | 2 2. Finish Designing Queries | 10        | Jack              | 5   If we observe the above results the first 2 columns get repeated for the Assignees for SAME ACTION ID  --> is there a way we can merge them something like   1. Finish Designing Queries |10         | { John : 2  , Jack  : 5}   Basically records being merged into one.
116000
I wrote a simple shell script to dump a specific mysql database. problem is it is prompting me for a password even though I provide on. The mysql db is version 5.0.27 if that matters. Here is the specific line I am using.  $MYSQLDUMP -u backup -p$MyPass $DB &gt; $DEST/$FILE   I tried several variations, but to no avail.
105605
I frequently need to do the following things in MySQL:   Create a non-root user Set that user's password Grant that user all privileges Make a database with the same name as the new user Allow usage only for users from the localhost.   Previously I did this using PHPmyadmin, but I would prefer doing it directly from BASH. Is there a CLI way to execute these steps?
35802
When i run the following in my console:  [root@****~]# mysql -u root -p -e 'SELECT VERSION();' Enter password: +-----------+ | VERSION() | +-----------+ | 5.5.39    | +-----------+   Which should be the correct version.  However when i enter my PHPMYADMIN on my server it has the following specs:  Server: Localhost via UNIX socket Program: MySQL Programversion: 5.1.73 - Source distribution   So which version is the correct one and how do i make sure that the database is running on 5.5?  Note  I have already restarted sql several times without any changes
94051
I want to do something like this:    create table app_users (     app_user_id smallint(6) not null auto_increment primary key,     api_key     char(36) not null default uuid() );    However this results in a error, is there a way to call a function for a default value in mysql?  thanks.
26596
What MySQL query will do a text search and replace in one particular field in a table?  I.e. search for foo and replace with bar so a record with a field with the value hello foo becomes hello bar.
7196
Is there an easy way to run a MySQL query from the Linux command line and output the results in  format?  Here's what I'm doing now:  mysql -u uid -ppwd -D dbname &lt;&lt; EOQ | sed -e 's/        /,/g' | tee list.csv select id, concat("\"",name,"\"") as name from students EOQ   It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.
11491
I know that in MySQL at the command line I can reset a table's auto-increment field to 1 with this:  ALTER TABLE tablename AUTO_INCREMENT = 1   I am curious if there is a way to do this from within phpMyAdmin.  Something like a check box to reset the auto-increment or something else along those lines?  Not that there is anything wrong with the command line approach.  More one of those curiosity things I keep thinking on...  Thanks in advance!
111744
Say I have a table called User_FriendList, which has the following characteristics:  CREATE TABLE User_FriendList (     ID ...,     User_ID...,     FriendList_IDs...,     CONSTRAINT User_Friendlist_PK PRIMARY KEY (ID) );   And let us suppose that said table holds the following data:    +----+---------+---------------------------+  | ID | User_ID | Friendlist_IDs            |  +----+---------+---------------------------+  | 1  | 102     | 2:15:66:35:26:17:         |  +----+---------+---------------------------+  | 2  | 114     | 1:12:63:33:24:16:102      |  +----+---------+---------------------------+  | 3  | 117     | 6:24:52:61:23:90:97:118   |  +----+---------+---------------------------+   Note: The “:” (colon) is the delimiter when exploding in PHP into an array.  Questions  So:   Is this a convenient way to “store” the IDs of a FriendList? Or, instead, should I have individual rows with only one single FriendId value in each of them and, when I need to retrieve all the rows of a given list, simply perform a query like SELECT * FROM UserFriendList WHERE UserId = 1?
71212
When we use an argument length with numerical data types, as far as I know this specifies the display width. I tried the following:    mysql&gt; create table boolean_test (var1 boolean, var2 tinyint);     Query OK, 0 rows affected (0.10 sec)     mysql&gt; show create table boolean_test;    +--------------+-------------------------    | Table        | Create Table +--------------+-------------------------   | boolean_test | CREATE TABLE `boolean_test` (     `var1` tinyint(1) DEFAULT NULL,     `var2` tinyint(4) DEFAULT NULL   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |    +--------------+---------------------------   1 row in set (0.00 sec)     First of all tinyint is a 1 byte value. So what is the meaning of tinyint(4)? There can not be 4 digits.    mysql&gt; insert into boolean_test values(101,112);   Query OK, 1 row affected (0.03 sec)     mysql&gt; select * from boolean_test;   +------+------+   | var1 | var2 |   +------+------+   |   10 |  112 |   |  101 |  112 |   +------+------+   2 rows in set (0.00 sec)     I see that in tinyint I stored a 10 and 101 and I could get back these values despite that it is defined as tinyint(1). Shouldn't I see a 1 for var1? I.e. just 1 display digits?
49377
Where can I find a syntax or language reference for the query language used on ?