Direct editing tables On SQL Management
For some time been wanting to create this article with some basic tips for managing the database in SQL language.
Although not an expert in SQL, I have run smoothly databases created in SQL language. During my visits I make to my clients, IT professionals these companies always want to ask questions of how to enter, change, or delete records in the database tables directly into it and from there I like the idea of putting these practices in a small emerged tutorial.
In order to include records in a particular database, you need to have knowledge of your data dictionary to know about the tables found in this database.
Here, I'll demonstrate how to use some basic command to change, add and delete records from a given table.
I will use as an example a database access software with which work.
First will have to have installed SQL Server 2005 or later and SQL Server Management matches this version of SQL. The Management is software that makes the management of databases. There are other options of management software, however advise using the Management that is more complete and robust. Recalling that the Express Edition version is distributed free.
As already created a previous article regarding the installation and configuration of SQL Server, I will not go into that.
Suppose that the database is already created and running, to manage it, we will open the SQL Server Management.
http://lucianogusso.files.wordpress.com/2014/09/sqlalt1.jpg?w=480&h=347
The database that I will use as an example is the RBACESSO_V100, which is already deployed and working as we see in the relationship database located in the Object Explorer located in the left column.
The first step I want to do is verify that the records are in this table I want to do the necessary changes and it'll clickNew Query .
https://lucianogusso.files.wordpress.com/2014/09/sqlalt2.jpg?w=480&h=286
Now, to make the consultation of records, I will type into the query screen use RBACESSO_V100 and then click ** <Enter> ** to go to the next line. Notice that when I start typing the name of the bank, as it appears between the existing options and then we can click on it. ** **
https://lucianogusso.files.wordpress.com/2014/09/sqlalt3.jpg?w=480&h=361
Now that I mentioned in the previous line database that will use the next line put the instruction to list the records of a particular table. The instruction is to use SELECT * FROM dbo.USUARIO . Notice that when you type dbo. opens a window with all corresponding to this database with the command mentioned tables USE . The select command will list the records of the table, the * (asterisk) will list all table records dbo.USUARIO . If you already know all the tables that are in the database can put USER instead of dbo.USUARIO . Put the dbo. poderermos to view that will show all existing tables. ** **
https://lucianogusso.files.wordpress.com/2014/09/sqlalt4.jpg?w=480&h=237
The instruction list is ready now we'll click on Run or you can click directly on the key F5 .
https://lucianogusso.files.wordpress.com/2014/09/sqlalt5.jpg?w=480&h=278
To have executed the instruction, notice that the existing records in this table appears at the bottom center just below the query statement.
https://lucianogusso.files.wordpress.com/2014/09/sqlalt6.jpg?w=480&h=233
Now we have an idea of how to list the records in this table will change as example the login name of the user reception to TEST.
Then in the query screen, we will enter the bank statement which we will be using and that will USE RBACESSO_V100 . On the next line put the statement UPDATE dbo.USUARIO (can also be just without the USER dbo) to change or update a record in the table USER . ** **
The next line will point to which field within the table USER want to make the change and it will use the command SET. Notice that the available fields will be listed in the table USER . How I want to change the login name of a particular user to TEST, this statement line looks like this: SET USU_LOGIN = 'TEST' . When the field refer to alphanumeric data field contents should be between the symbol '(apostrophe).
https://lucianogusso.files.wordpress.com/2014/09/sqlalt7.jpg?w=480&h=268
On the next line, I will point to which record I want to make the change the login name and in this case I want to change the name for the user number 2 Here in this database, the records of USU_NUMERO column primary keys and are therefore not changes can be made in this field. As the data dictionary I own this bank, the figures in this field are automatically generated and identify each user. So continuing, the statement of this line will look like this: **WHERE USU_NUMERO = 2 ** then we can click Run or directly press the key F5 .
Only pararesumir in USERS database table RBACESSO_V100, we will change the name of the user record to TEST 2.
https://lucianogusso.files.wordpress.com/2014/09/sqlalt8.jpg?w=480&h=235
Notice that the instructions to be executed, appear in many rows were affected. Signal that worked. If errors occurred, which appear on-line and / or field that has the error. An example error that can happen is to try to add or change data in a numeric field that is for alphanumeric data. Remembering that numeric data are not enclosed in apostrophes.
https://lucianogusso.files.wordpress.com/2014/09/sqlalt9.jpg?w=480&h=289
Just to visualize that there was this change successfully, we will use the instruction to view the records. If you want, you can put the third line with the statement **WHERE USU_NUMERO = 2, ** only to view this record.
https://lucianogusso.files.wordpress.com/2014/09/sqlalt10.jpg?w=480&h=239
Now to insert a record to the table USERS , we will enter USE RBACESSO_V100 to point the database that we will use on the next line put the INSERT INTO USERS (USU_LOGIN, USU_SENHA) indicating that we will insert data into the table USER only for fields USU_LOGIN, USU_SENHA . And in the next line coloremos **VALUES ('LUCIANO', 'L123')**referred to the values that will insert these fields and the case are LUCIANO and L123 .
As we only ask to insert new data to only these two fields, other tables will be populated as NULL or auto value related to this field and then we'll click on Run or F5 .
https://lucianogusso.files.wordpress.com/2014/09/sqlalt11.jpg?w=480&h=322
Only for us to verify, let's do a SELECT on this table to see if the data was included as is shown in the image below.
https://lucianogusso.files.wordpress.com/2014/09/sqlalt12-1024x556.jpg?w=480&h=260
To exclude a record is simple. Put the instruction USE RBACESSO_V100 for us to use this bank as mentioned earlier. On the next line put the statement DELETE FROM USER to exclude a record of the USER table and on the next line putWHERE USU_NUMERO = 12 which indicates that we exclude the number 12 record.
https://lucianogusso.files.wordpress.com/2014/09/sqlalt13.jpg?w=480&h=294
