As far as I can tell, both syntaxes are equivalent. The first is SQL standard, the second is MySQL’s extension. So they should be exactly equivalent performance wise. http://dev.mysql.com/doc/refman/5.6/en/insert.html says: INSERT inserts new rows into an existing table. The INSERT … VALUES and INSERT … SET forms of the statement insert rows based on explicitly … Read more
This will do what you want (list of towns, with the number of users in each): SELECT `town`, COUNT(`town`) FROM `user` GROUP BY `town`; You can use most aggregate functions when using a GROUP BY statement (COUNT, MAX, COUNT DISTINCT etc.) Update: You can declare a variable for the number of users and save the … Read more
HAVING: is used to check conditions after the aggregation takes place. WHERE: is used to check conditions before the aggregation takes place. This code: select City, CNT=Count(1) From Address Where State=”MA” Group By City Gives you a table of all cities in MA and the number of addresses in each city. This code: select City, … Read more
@Nicolai is correct about casting and why the condition is false for any data. i guess you prefer the first form because you want to avoid date manipulation on the input string, correct? you don’t need to be afraid: SELECT * FROM table WHERE update_date >= ‘2013-05-03’::date AND update_date < (‘2013-05-03’::date + ‘1 day’::interval);
Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back: BEGIN TRANSACTION [Tran1] BEGIN TRY INSERT INTO [Test].[dbo].[T1] ([Title], [AVG]) VALUES (‘Tidd130’, 130), (‘Tidd230′, 230) UPDATE [Test].[dbo].[T1] SET [Title] = N’az2′ ,[AVG] = 1 WHERE [dbo].[T1].[Title] = N’az’ COMMIT TRANSACTION [Tran1] END TRY … Read more
You can do this via the information_schema tables. For example: SELECT tc.table_schema, tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_schema AS foreign_table_schema, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema WHERE tc.constraint_type=”FOREIGN KEY” … Read more
When you create a database in Microsoft SQL Server you can have multiple file groups, where storage is created in multiple places, directories or disks. Each file group can be named. The PRIMARY file group is the default one, which is always created, and so the SQL you’ve given creates your table ON the PRIMARY … Read more
It will order first, then get the first 20. A database will also process anything in the WHERE clause before ORDER BY.
Try this: ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0; From the documentation that you linked to: ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name alter_specification [, alter_specification] … alter_specification: … ADD [COLUMN] (col_name column_definition,…) … To find the syntax for column_definition search a bit further down the page: column_definition clauses use the same syntax … Read more
If you want just a first selected row, you can: select fname from MyTbl where rownum = 1 You can also use analytic functions to order and take the top x: select max(fname) over (rank() order by some_factor) from MyTbl