SQL has a number of built-in functions that can be used to find common query results, such as averaging data, summing up a column of data, rounding information off, formatting data, etc. SQL also has a number of options for building your own custom functions (and triggering them to run). I usually like to use functions when I’m looking for data and reporting. I don’t like using them in code, as the language I’m performing a task in is typically better suited to manage data than is SQL, comparably. SQL functions come in three types. Aggregate, Scalar, and Custom. Aggregate functions (those that take multiple objects as an input): AVG()…
-
-
Reset A Lost MySQL Password
The first step to reset a password is to stop the MySQL daemon. This will cause mysqld to accept no new connections and terminate existing connections. But this can all be done in a matter of seconds, usually. To stop MySQL on Mac, use the System Preference pane or launchctl. To stop on Linux, use init.d: sudo /etc/init.d/mysql stop Or if it’s mysqld instead: sudo /etc/init.d/mysqld stop Then start the SQL daemon using the –skip-grant-tables option: sudo mysqld_safe --skip-grant-tables & Next, login to mysql, which won’t require a password running in this mode: mysql -u root And use the UPDATE USER statement to set a new password: UPDATE USER set…
-
SQL Constraints
SQL constraints the data that can be in a table. A violation of a constraint causes an action to be aborted. Constraints can be defined upon creation or using the ALTER TABLE statement once created. The general syntax of a CREATE (or use ALTER instead of CREATE) when defining constraints is as follows: CREATE TABLE tablename ( columnname datatype(size) constraintname, columnname datatype(size) constraintname, columnname datatype(size) constraintname, columnname datatype(size) constraint name, columnname datatype(size) constraint name, ); Obviously, replace columnname with the name of each of your column, datatype with the types of data your column contains and constraint name with the constraint you wish to use. You have the following constraints…
-
User And Permissions Management In MySQL
By default, MySQL comes with a root user configured. You can also create additional users, change passwords for users, and assign what databases and tables they have access to. From MySQL, you can can create a basic user using the CREATE USER statement, providing a user, a location, and then using IDENTIFIED BY followed by a password. In production, this would look similar to the following, using krypted as the user and mysecretpassword as the password: CREATE USER 'krypted'@'localhost' IDENTIFIED BY 'mysecretpassword'; Once you’ve created a user, you’ll want to assign what the user can access. Here, the * wildcard is pretty handy. In the following command, we’ll use the…
-
A Couple Of Ways To Backup SQL Data
Create a backup copy of a table called Customers into a table called Customers2 on a running database called Backup: SELECT * INTO Customers2 IN 'Backup.mdb' FROM Customers; You can also specify multiple tables to pull data from when bringing that data into a new table, effectively merging data into a backup database: SELECT Customers.Site, IPs.IP INTO CustomerIPsbackup FROM Customers LEFT JOIN IPs ON Customers.Site=IPs.IP; Since this is more like replication than backup, MySQL also has a mysqldump command, used to dump a sql database to a file or screen, or whatever. Here, we’ll export all of our databases in a running MySQL instance into a file called dumpfile.sql: mysqldump…
-
Use SELECT INTO To Copy Data Between Tables
You can use the SELECT INTO statement finds data and then copies that data between tables or databases. To do so, use the following syntax: SELECT * INTO newtablename FROM tablename; So to copy that Customers table to a new table called Customers2: SELECT * INTO Customers2 FROM Customers; Or to copy only certain columns into Customers2, we’d use the following: SELECT ID,Site INTO Customers2 FROM Customers;
-
Create A SQL Database
So you’re ready to write some software? Or test some cool stuff. Or build something awesome. You can use the CREATE DATABASE statement to get started, by creating a database. To do so is pretty easy, simply run that statement followed by a name for the database (called Customers): CREATE DATABASE Customers; Once you’ve created a database, it’s time to create tables, which can be done using the CREATE TABLE statement. The Syntax of that statement looks something like this, defining a set of columns, their data type and the size of the column (in the form of a maximum length), all wrapped in parenthesis with each column separated by…
-
Add Columns, Delete Columns, and Edit Columns, and Find Love Using SQL ALTER Statements
Previously, we covered creating tables in SQL. To create a column in a table, use the ALTER TABLE statement, along with the ADD and then define a column name followed by the data type: ALTER TABLE nameoftable ADD nameofcolumn datatype To delete a column in a table:: ALTER TABLE nameoftable DROP COLUMN nameofcolumn To change the type of data stored in a column, use MODIFY: ALTER TABLE nameoftable MODIFY COLUMN nameofcolumn datatype In this article, we’ll use the same “Customers” table from our first articles to add and edit columns: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook…
-
Remove Items From SQL Databases Using the DROP Statement
In SQL, the DROP Statement is used to remove databases, tables, and indexes. The syntax to remove a table is: DROP TABLE nameoftable Wow. That’s really, really easy. I mean, you can delete craploads of data that way! It can’t be! You can also delete a database. To do so, use the DROP statement again, but this time, instead of dropping a table, let’s remove the database: DROP DATABASE nameofdatabase Aaaaaand, you can drop an index, which on MySQL is done using an ALTER statement, followed by TABLE, then the table name that has an index needing to drop: ALTER TABLE nameoftable DROP INDEX nameofindex You can also delete the…
-
Query SQL Data In A Range Using The BETWEEN Operator
Sometimes you’re looking for IDs, prices, zip codes and the like in a range. You can use the BETWEEN operator to select integers, text, or dates within a range of items. The syntax when using the BETWEEN operator would look similar to the following: SELECT column(s) FROM table WHERE column_name BETWEEN value AND value; In this article, we’ll use the same “Customers” table from our first articles: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager…