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 available:
  • CHECK: Verify that values meet the defined condition
  • DEFAULT: Sets a default value for new rows in a column
  • FOREIGN KEY: Verify referential integrity of data in a table to match values in another
  • NOT NULL – Columns cannot store a NULL value (be empty)
  • PRIMARY KEY – Columns cannot store a NULL value AND values in rows must be unique
  • UNIQUE – Each row in a column must be unique
For example, the NOT NULL constraint would be defined as follows: CREATE TABLE testingnotnull ( telephonenumber int NOT NULL, ); If you have an app sitting in front of a database, then use these with caution, as if SQL just terminates an operation your app might have unexpected integrity issues.

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 GRANT statement along with ALL PRIVILEGES to give this new krypted user access to all of the databases running on MySQL: GRANT ALL PRIVILEGES ON * . * TO 'krypted'@'localhost'; Pretty easy so far. Just flush the permissions with the FLUSH PRIVILEGES statement and krypted’s now all good to access anything that exists in MySQL when the command was run. FLUSH PRIVILEGES; Once you’ve flushed, you can see what a user can access using the SHOW GRANTS statement: SHOW GRANTS FOR 'krypted'@'localhost'; If you create new databases, do so again. To login as the user, you can then just run mysql followed by the -u option to define the user: mysql -u krypted -p To remove permissions, use the REVOKE statement. Let’s remove the ALL PRIVILEGES from krypted: REVOKE ALL PRIVILEGES ON *.* FROM 'krypted'@'localhost'; To delete a user, use the DROP statement, and then USER, followed by who we’re deleting: DROP USER ‘krypted’@‘localhost’; If you were then going to create the user and provide a different level of privileges you could replace ALL PRIVILEGES with one of the following:
  • ALL PRIVILEGES: Gives the user full access to a database (or all included with a wildcard)
  • CREATE: Gives a user the ability to create new databases or tables within a database the access is provided
  • DROP: Gives a user the ability to use the DROP statement to remove tables or databases
  • DELETE: Gives a user the ability to delete rows from tables
  • GRANT OPTION: Gives a user the ability to add and remove privileges for other users
  • INSERT: Gives a user the ability to create rows into tables using the INSERT statement
  • SELECT: Gives a user the ability to use SELECT statements (similar to read in POSIX)
  • UPDATE: Gives a user the ability to update table rows only
We can also string some of this together in one statement, such as if we wanted the krypted password to expire in 60 days: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password' PASSWORD EXPIRE INTERVAL 60 DAY; A full list of options would include the following syntax, with options including maximum queries, max connections, ssl, auto-lock, etc: CREATE USER [IF NOT EXISTS] user_specification [, user_specification] ... [REQUIRE {NONE | tsl_option [[AND] tsl_option] ...}] [WITH resource_option [resource_option] ...] [password_option | lock_option] ... user_specification: user [ auth_option ] auth_option: { IDENTIFIED BY ‘auth_string’ | IDENTIFIED BY PASSWORD ‘hash_string’ | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin BY ‘auth_string’ | IDENTIFIED WITH auth_plugin AS ‘hash_string’ } tsl_option: { SSL | X509 | CIPHER ‘cipher’ | ISSUER ‘issuer’ | SUBJECT ‘subject’ } resource_option: { MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count } password_option: { PASSWORD EXPIRE | PASSWORD EXPIRE DEFAULT | PASSWORD EXPIRE NEVER | PASSWORD EXPIRE INTERVAL N DAY } lock_option: { ACCOUNT LOCK | ACCOUNT UNLOCK }

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 --all-databases > dumpfile.sql

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 a comma: CREATE TABLE nameoftable ( column datatype(size), column datatype(size), ); So to create the Customers table that we’ve been using through these articles, we’ll use the following SQL Statement: CREATE TABLE Customers ( ID integer(255), Site varchar(255), Contact varchar(255), Address varchar(255), City varchar(255), Zip integer(255), Country varchar(255), ); Columns are created with data types. In the previous example, we named columns of integers and varchars. The available data types include the following:
  • CHARACTER: A string of characters using a defined length
  • VARCHAR: A variable length string of characters using a maximum length
  • BINARY: A binary string
  • BOOLEAN: TRUE or FALSE
  • VARBINARY: Binary string in a variable length, with a defined maximum length
  • INTEGER: A number with no decimals
  • SMALLINT: A 5 digit or less number with no decimals
  • BIGINT: A 19 digit or less number with no decimals
  • DECIMAL: Number with decimals
  • FLOAT: Floating number in base 10
  • REAL: Number
  • DOUBLEPRECISION: Approximate number
  • DATE: Year, month, and day in separated values
  • TIME: Hour, minute, and second in separated values
  • TIMESTAMP: Time in the form of year, month, day, hour, minute, and second
  • INTERVAL: A period of time
  • ARRAY: Ordered collection of data
  • MULTISITE: Unordered collection of data
  • XML: Stores XML

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 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 Grain Exchange Minneapolis 55418 US Let’s add a column called “ExpDate” in the “Customers” table with a date type, which will oddly hold a date. To do so, use the following SQL statement: ALTER TABLE Customers ADD ExpDate date The new columns are empty. Once you’ve created a column, you can then change the data type of that column.Let’s just change this to year, assuming all the ExpDate fields have the same actual day for their expiration: ALTER TABLE Customers ALTER COLUMN ExpDate year Crap, I promised love. How about this, give all the things away for free. Now let’s delete the column: ALTER TABLE Customers DROP COLUMN ExpDate Obviously, this would delete all of the data in the column as well, so be careful!

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 data inside the table, rather than the table. This leaves the schema in tact so that you can start writing data to the database again: TRUNCATE TABLE nameoftable And viola, you’re deleting data like a fool! exploits_of_a_mom

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 Grain Exchange Minneapolis 55418 US The following SQL statement selects all Sites with an ID BETWEEN 2 and 4: SELECT * FROM Customers WHERE ID BETWEEN 2 AND 4; Or look for the ones that are not between those: SELECT * FROM Customers WHERE ID NOT BETWEEN 2 AND 4; One of the best aspects of BETWEEN is the ability to specify multiples. So you can look for ID between 2 and 4 and not  3 and 4: SELECT * FROM Customers WHERE (ID BETWEEN 2 AND 4) AND NOT ID IN (3,4); The following SQL statement selects all Sites with a Site beginning with any of the letter BETWEEN ‘M’ and ‘Z’: SELECT * FROM Customers WHERE Site BETWEEN 'M' AND 'Z'; Or the same, using NOT BETWEEN ‘A’ and ‘L’: SELECT * FROM Customers WHERE Site NOT BETWEEN 'A' AND 'L'; If there was a Date field, the following statement would select all Sites with a Date BETWEEN ’01-01-2010′ and ’01-01-2016′: SELECT * FROM Customers WHERE Date BETWEEN #01/01/2010# AND #01/01/2016#; At this point, we have lots of logic we can use to further find data in large databases. We’ll also need to eventually get to joining tables, so we can take use of multiple pieces of data.

Compound Searching Using The SQL WHERE Statement With The IN Operator

One of the most important aspects of searching for objects is to be able to define multiple values in a search. We looked at searching using text globbing. But the IN operator goes a step and allows you to search The IN operator allows you to specify multiple values in a WHERE clause. SELECT column FROM table WHERE column IN (value,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 Grain Exchange Minneapolis 55418 US The following outputs all customers with a Site of “Krypted” or “JAMF”: SELECT * FROM Customers WHERE Site IN ('Krypted','JAMF'); When looking to use the IN operator, note that strings are quoted and separated by commas (,) inside parenthesis.

Moar About SQL Wildcards

Previously we looked at using wildcards in conjunction with the SQL LIKE operator. Wildcards allow you to search for data in a defined table. Think of them as text globbing for SQL. The wildcards available include the following:
  1. [list]: Define a ranges of characters for pattern matching
  2. [!charlist]: Matches only a character NOT specified within the brackets
  3. %: Require a single character/object in a pattern
  4. _: Allow any single character in a pattern
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 Grain Exchange Minneapolis 55410 US The following SQL statement selects all customers with a City starting with “Minne”: SELECT * FROM Customers WHERE City LIKE 'Minne%'; The above SELECT would not locate an object that was just called Minne because the % indicates that something must be there. We could also look for something with nn but with something before and after the pattern: SELECT * FROM Customers WHERE City LIKE '%nn%'; Or to look for something that would contain Minneapoli: SELECT * FROM Customers WHERE City LIKE 'Minneapoli_'; Not that the _ is looking for a single character and that the % is looking for any string in that space. We could also look for a set of objects or omit a set of objects using brackets to define multiple items or a range. For example, let’s say we wanted to look for zip codes 55418 and 55410 SELECT * FROM Customers WHERE Zip LIKE '[55418 55410]%'; Or 55410 through 55419, use a dash to separate the two (in ascending order): SELECT * FROM Customers WHERE Zip LIKE '[55410-55418]%'; Or the ones that don’t match that pattern: SELECT * FROM Customers WHERE Zip LIKE '[!55410-55418]%'; Overall, the globbing/pattern matching options are very basic (think DOS-like syntax) unless you use more complicated functions. But, it’s amazing what kind of stuff you can string together with simple commands, joins, and other tricks, without having to get into functions.