blog.x-e.ro / database: page 1 of 6
sql :: command line database clients
as a curious code monkey, i find myself poking around a lot of different types of databases. and as a terminal junkie, i've used a bunch of different commandline tools to connect and interact with them.
in this post i wanna hightlight the dbcli project. it's a suite of db clients each written in python with a similar interface. clients support autocompletion, syntax highlighting, history, the project is opensource, and cross-platform.
i find myself perpetually using them for postgres, mysql, and sqlite but these have clients for ms-sql, redis, and more....
sql :: quick and dirty sql example
sometimes you find the need to do mass string replacements in your mysql/maria databases. here's how i do it:
UPDATE your_table
SET your_field = REPLACE(your_field, 'old_value', 'new_value')
WHERE your_field LIKE '%old_value%'
sql :: copy and swap database tables in MSSQL
have you ever had a database table that you needed to clone? you can use any number of convoluted methods such as: manually coping and pasting rows in the database manager, running lots of queries/stored procedures, or writing a script to cycle though each row copying data from one table to the next. all of these methods are inferior and far more complex then the method i'm going to show you.
introducing the select into query...
this method should be used if the table you want to clone exists, but the new table douse not. the select into query will create the new table for you, copying all the column names, properties, and data. if you want to make an exact clone of the table you can select * into the new table. but if you only want a few specific columns, you can declare only the ones you want. the syntax is elegantly simple...
SELECT *
INTO [new-table]
FROM [old-table]
sql :: nested queries in MSSQL
the world of database programming can be a dark and treacherous journey. when you first set out, your quest seems manageable. but as time progresses your start to realize you need more queries, more tables, etc, etc, to achieve your goal. well today i’m going teach you a little trick that might help you need one less stored procedure then you thought...
sometimes it’s true, you actually do need two queries to get the job done. but other times you can circumvent this by using something like a "join" or "inner join" query to merge two tables together and pull your results from both.
but the optimizer in you says that still isn’t enough...
visual studio 2005 has a tool for connecting to a database called a "sqlDataSource". while this tool works, i find myself wanting to create the connection and build or execute stored procedures within my own C# code. this tutorial will show you how to connect to a sql database 3 different ways in asp.net
the first thing is creating a connection with your database. if you understand how connection strings are built, write your own other wise will can use the visual studio database tool to do that for us.
here's a simple SQL script that returns the last row created. this is great for when you add a new record that has an autogenerated id and you need that number.
mssql
CREATE PROCEDURE [dbo].[getLastLogin]
AS
SELECT TOP 1 *
FROM dbo.login
ORDER BY id DESC
GO
mysql
SELECT * FROM `login`
ORDER BY `id` DESC
LIMIT 1,1;
