In this article we will analyze, perhaps, one of the most important SQL queries. This queries to add and delete records from a database table. Because, VERY often add new records to the table, and do it in automatic mode, then this material is required to be studied.
To start SQL query to add a new record to a table:
INSERT INTO users (login, pass) values("TestUser", "123456")
When adding a record, the first command is " INSERT INTO", then the name of the table in which we insert the record. Next comes the names of the fields that we want to fill in parentheses. And then in parentheses after the word " values"we begin to list the values of the fields that we have selected. After executing this query, a new record will appear in our table.
Sometimes required update table entry, for this there is the following SQL query:
UPDATE users SET login = "TestUser2", pass="1234560" WHERE login="TestUser"
This query is more complex, since it has the " WHERE", but about it a little lower. First comes the command" UPDATE", then the table name, and after " SET" we describe the values of all fields that we want to change. It would be simple, but the question arises: " Which entry should be updated?". For this there is " WHERE". IN this case we update record, field " login"which matters" TestUser". Please note that if there are several such records, then everything will be updated! This is very important to understand, otherwise you risk losing your spreadsheet.
Let's talk a little more about WHERE". In addition simple checks for equality, there are also inequalities, as well as logical operations: AND And OR.
UPDATE users SET login = "TestUser2", pass="1234560" WHERE id< 15 AND login="TestUser"
The SQL query will update those records id which are less 15 AND field " login" has the meaning " TestUser". I hope you figured out the design" WHERE"because it's very important. Precisely" WHERE" is used when fetching records from tables, and this is the most frequently used task when working with databases.
And finally, simple SQL query to delete records from a table:
DELETE FROM users WHERE login="TestUser2"
After command " DELETE FROM" goes the name of the table in which you want to delete records. Next, we describe the "WHERE" construction. If the record meets the described conditions, it will be deleted. Again, pay attention, depending on the number of records that satisfy the condition after " WHERE", any number of them can be deleted.
Comments moved from the blog
SERGEY
09/14/2016 at 01:25
Good afternoon!
Interested in this question: what is the easiest way to organize the storage of data and program settings without using a database? Don't want to be tied to MySQL or Access..
ADMIN
09/14/2016 at 22:14
Hello!
Properties.Settings
App.Config
XML file
serialization
Try picking one of these from the list.
NIKOLAY
09/16/2016 at 02:28
Hello, how can I delete the highlighted row in dataGridVIew from dataGridVIew and phpMyAdmin.
PhpMyAdmin? This is just a shell for working with the database, can you explain?
NIKOLAY
09/18/2016 at 02:24
it is necessary that the selected row be deleted from the DataGridView and from the database.
ADMIN
09/19/2016 at 07:00
How to delete a row in a database mysql data added an article.
NIKOLAY
09/20/2016 at 09:20
Thanks a lot.
DIMA
09/20/2016 at 10:24
Hello, can you this way to implement not through DataGridView, but through ComboBox? If so, how? Thank you.
ADMIN
09/22/2016 at 03:21
Hello. Example:
GENNADY
09/22/2016 at 18:25
why should I add such text to the database System.Windows.Forms.TextBox, Text: ge
By the way, this (ge) at the end it is written by the gene, even though the text is specified in the table settings. The word of the gene should have fit further, I display this table in my program and it turns out that it displays all this unnecessary text for me
ADMIN
09/24/2016 at 04:17
Most likely the SQL query is written incorrectly, for example:
In textBox1 we enter the name: Gene.
Sql query: "Insert into table name values(textBox1, ..)"; Result: System.Windows.Forms.TextBox
And you need to pass: "Insert into table name values(textBox1.Text, ..)";
Result: Gena
GENNADY
09/24/2016 at 18:41
That is how it is. Thank you
SERGEY
25.09.2016 at 11:51
Hello. And how to implement adding to the database through textBox?
ADMIN
09/26/2016 at 20:53
Everything is the same in principle. For example, let's take the most recent example, it needs:
//create parameters and add them to the collection cmd.Parameters.AddWithValue("@Name", textBox1.Text); cmd.Parameters.AddWithValue("@LastName", textBox2.Text);
now the parameters: Name and LastName receive the values entered in the textboxes and pass them to the database
LINARA
09/27/2016 at 17:45
Hello, how is the highlighted row in dataGridVIew and phpMyAdmin?
ADMIN
09/29/2016 at 02:06
I don't know how you can select a row in phpMyAdmin. And in the dataGridView, for example, this can be done using the SelectionChanged event.
PSH
09/30/2016 at 03:48
2Linara:
If you really want to edit rows, take a tool a la HediSQL, tweak and modify rows.
2admin
Good day! Thank you for the materials - everything is very coolly stated)
Question: I add data with the following request (it is a test one):
String sql = "INSERT INTO users (`FIO`, `Tour`, `Count`, `Cost`, `Date`, `Passport`, `Birth`) VALUES ("Kolyan", "Moscow", "1+1 ", 1100, "2011-11-11", "1111 1111", "11/9/1900");";
The data is entered all ok, but in the database (mysql) instead of Cyrillic they turn out to be “????”.
Visual studio says System.String is a unicode sequence.
Also tried:
ALTER DATABASE `test` COLLATE "koi8r_general_ci"; ALTER TABLE `users` COLLATE="koi8r_general_ci"; ALTER DATABASE `test` COLLATE "utf8_unicode_ci"; ALTER TABLE `users` COLLATE="utf8_unicode_ci";
But it doesn't help..
What can be wrong? Different VS and DB encodings? Or what?
Could send what to read / change.
Thank you
ADMIN
01.10.2016 at 09:49
Hello.
In the DB (and in the table) the utf_general_ci collation
Is there such a comparison? Perhaps utf8_general_ci?
Usually they create a Mysql database by choosing the utf8_general_ci comparison, so there are no problems with the Cyrillic alphabet, unless, of course, the server does not receive bugs from the client.
COLLATION is used for comparison, but in this case, the encoding (charset) is important. Therefore, first you need to make sure that it is set correctly on the server, for example, in utf8, and not latin1.
When connecting via the .net connector (by default), latin1 is used, so sometimes you need to explicitly specify the utf8 encoding in the connection string:
MySqlConnection mycon; mycon = new MySqlConnection("server=127.0.0.1;uid=vasya;pwd=123;database=test;Charset=utf8;"); //MySqlConnectionStringBuilder: mysqlCSB.CharacterSet = "utf8";
PSH
01.10.2016 at 11:34
You are right, described yourself, utf8_general_ci!
Yes it helped, ;Charset=utf8;
Thank you very much!
SERGIUS
02.10.2016 at 11:02
Thanks for the working example. Question
I have created a text field in which I would like to enter the IP address of the database, but I do not know how to substitute this data here
String conStr = " [email protected];user=test;" +
"database=test;password=test;";
Please tell me how to insert data from text fields in windows form into this design….
ADMIN
03.10.2016 at 11:50
"[email protected];user=...
In general, it is better to use properties instead of such a string, as in this article, or the String.Format() method
OLGA2203
05/15/2017 at 20:14
String Connect = “Server=127.0.0.1;Port=3306;Database=base;Data Source=localhost;user=root;”; MySqlConnection con = new MySqlConnection(Connect); con.Open(); //Establish a connection to the database. MySqlCommand cmd = new MySqlCommand(); cmd.CommandText = @”INSERT INTO tovar(ID,Category,Name,TradeMark,Price,Photo,Size,Color,Material,Count) VALUES (@pr, @Category, @Name, @TradeMark, @Price, @Photo, @Size, @Color, @Material, @Count)”; cmd.Parameters.AddWithValue("@pr",counter); cmd.Parameters.AddWithValue(“@Category”, comboBox1.SelectedItem.ToString()); cmd.Parameters.AddWithValue(“@Name”, textBox1.Text); cmd.Parameters.AddWithValue(“@TradeMark”, textBox2.Text); cmd.Parameters.AddWithValue(“@Price”, Convert.ToInt32(textBox4.Text)); cmd.Parameters.AddWithValue(“@Photo”, textBox3.Text); cmd.Parameters.AddWithValue(“@Size”, textBox6.Text); cmd.Parameters.AddWithValue(“@Color”, textBox5.Text); cmd.Parameters.AddWithValue(“@Material”, textBox8.Text); cmd.Parameters.AddWithValue(“@Count”, Convert.ToInt32(textBox7.Text)); cmd.Connection = con; cmd.ExecuteNonQuery(); MessageBox.Show(“Adding successful”, “Adding successful”, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
The error “Column 'ID' cannot be null” is thrown, I remove the addition to the ID column - the same is written about the next column, etc.
If I enter any constant values in brackets in VALUES, the line is added to the base.
Tell me, please, what is the problem? I need to write to the database exactly the data and values entered through the form
Last update: 1.11.2015
To add data, the "INSERT" expression is used:
$query ="INSERT INTO goods VALUES(NULL, " samsung galaxy III","Samsumg")";
The "INSERT" statement inserts one row into the table. After keyword INTO specifies the name of the table, and after VALUES the set of values for all columns is specified in parentheses. Since we have three columns in the table, we specify three values.
Since in the previous topic, when creating a table, we specified the following column order: id, name, company, in this case, NULL is passed for the id column, and "Samsung" for the name Galaxy III", and for company - "Samsumg".
Because the id column is defined as AUTO_INCREMENT, we don't have to specify a specific numeric value for it, and we can pass a NULL value and MySQL will assign the next available value to the column.
Now let's look at adding data using an example. Let's create a file create.php with the following content:
Data added"; ) // close connection mysqli_close($link); ) ?>
Here, the code for interacting with the database is combined with the functionality of the forms: using the form, we enter data to be added to the database.
Here we have used the mysqli_real_escape_string() function. It serves to escape characters in a string, which is then used in SQL query. It takes as parameters a connection object and a string to be escaped.
Thus, we actually use character escape twice: first for the sql expression using the mysqli_real_escape_string() function, and then for html using the htmlentities() function. This will allow us to protect ourselves from two types of attacks at once: XSS attacks and SQL injections.
From the author: oh, you can’t throw words out of a song! But they can be deleted, updated or inserted by others. The main thing is that the words are entered into the database. Today we will tell you how data is written to MySQL and how to do it right so that the song sounds!
The phpMyAdmin shell for administering the MySQL DBMS implements a “lightweight” functionality for adding new records to database tables. Because of its simplicity, it is ideal for both green "dummies" and "lazy" professionals.
To enter new information into the table, you should enter the program with administrator rights. Then select from the lists on the left desired base and table. Then in the top menu go through the item "Insert".
After that, in order to make a record in the MySQL database, fill in the next window for all columns of the "Value" field and click "OK" at the bottom.
The screenshot above shows that the modified table "Animals" consists of two columns (fields): id and name. The second section specifies the type of each of the columns. We only need to put in a value for the name field since the id column is primary key, and was set to auto-increment when the table was created. This means that the value of the id field will be generated automatically by MySQL, adding 1 to the previous integer value.
To find out which field of data records in MySQL is the primary key (primary key), in phpMyAdmin, go to the menu (with the selected table on the left in the list) to the top menu tab "Structure". Here is a description of all fields in the table, their type and additional characteristics.
But phpMyAdmin is just a wrapper, and the real administrators "talk" to the MySQL server using Structured Query Language. That is, they “talk” with him on SQL language. Since we are striving to become real professionals, we will “dive” a little into the study of SQL commands within the framework of the topic under consideration. Here is a query, entering which in the "SQL" field, you will create the same database:
CREATE TABLE Animal (id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id));
CREATE TABLE Animal (id MEDIUMINT NOT NULL AUTO_INCREMENT , name CHAR (30 ) NOT NULL , PRIMARY KEY (id ) ) ; |
After creating a table and writing data to MySQL database(via the "Insert" tab), the program will inform you that a line with the identifier value 1 has been added to animals. And a little lower in the editor window, the request code will be displayed, which the shell generated for us and sent to the database server.
Request code:
INSERT INTO `my_db1`.`animal` (`id`, `name`) VALUES (NULL, "Cat");
INSERT INTO ` my_db1 ` . ` animal ` (` id ` , ` name ` ) VALUES (NULL , "Cat" ) ; |
Let's study it in more detail. SQL uses the INSERT statement to insert a new row into a table. It tells the server that in the database table (my_db1 . animal) you need to insert the specified values \u200b\u200bin the id and name fields (VALUES (NULL, 'Cat').
Please note that we do not specify a numeric value for the id column, but NULL, since we “enabled” autocomplete (autoincrement) for this field.
Everything we have considered is only a “prelude” to the main action, in which “His Highness” enters the stage server language PHP programming. It is thanks to him that MySQL as a DBMS has become so widespread on the Web.
On a bunch of these two Internet technologies, most of the world wide web. Wherever you look, you will find them everywhere: in modern CMS, "self-written" engines and on the server.
Not surprisingly, to write data to MySQL PHP provides so many built-in functions. But we will focus on the most important of them. Here is the code that adds a new "animal" to the animals table. If you try hard, then in this way you can collect a whole menagerie :)
$con_str=mysql_connect("localhost", "root", "", "db1"); if(mysql_connect("localhost","root"))( echo "Hello!!"; ) mysql_select_db("db1",$con_str); $query_str="INSERT INTO `db1`.`animal` (`id`, `name`) VALUES (NULL, "dog")"; mysql_query($query_str); mysql_close();
$ con_str = mysql_connect ("localhost" , "root" , "" , "db1" ) ; if (mysql_connect("localhost" , "root" ) ) ( echo "Hello!!" ; mysql_select_db("db1" , $ con_str ) ; $query_str= "INSERT INTO `db1`.`animal` (`id`, `name`) VALUES (NULL, "dog")"; mysql_query($query_str) ; mysql_close(); |
This code must be inserted into PHP file and host it on the server side.
Using the mysql_connect() function, we connect to the MySQL database server. As arguments, the function takes the host, DBMS username, password, and the name of the database to connect to. We have an empty password because we are using a server installed on the local (client) machine.
To demonstrate all the described examples of writing to the MySQL database with using PHP we used Denver's "gentlemen's kit". It includes local server apache, MySQL server, phpMyAdmin, and a few other useful code creation and testing tools.
Then, in the logical if block, we checked for a connection to the database server. After that, in the mysql_select_db () function, we designated the base to which we will connect. Using the mysql_query() function, we launched the SQL query written to the $query_str variable. And at the end, the established connection was closed (mysql_close() function). Now, if we look into our menagerie (table animal), we will find a new “pet” there.
To write it in MySQL, PHP "kindly" provided all the necessary set of functions. The main thing that beginners “burn out” on when using SQL in program code is incorrect spelling of queries, violation of syntax and alternation of characters for escaping (quotes).
To avoid the appearance of extra "gray" hair on your head, it is better to check the correct spelling of the request with using phpMyAdmin. To do this, place the SQL code in the program editor and run it for execution. If something is wrong, the application will start to “swear”, display a red message and indicate the location of the error.
As you can see, with the help of MySQL, you can “collect” your menagerie and correctly change the words of any “song”. And for writing to the MySQL database, PHP is perfect, so we advise you to make a "close" friendship with this "great" programming language!
All modules of a site or web application where it is required to enter and record some data (for example, name, age, address, etc.) use a simple mysql function INSERT INTO `name_base` (name,value1,value2) VALUES ('Vasya ','1','2');
All variables are entered into the database according to the values that we set in the first brackets. It is important to consider the encoding of the handler script, database, and configuration file. It is advisable to use the most common encoding UTF-8.
Keep in mind that there are two ways to write to the database.
First way if we do not initially set the cell names of the database tables. Then we must list all variables for each cell, namely how many cells are in the database table, so many variables must be listed in brackets after the VALUE value.
For example:
The database table has four cells. This means that after the VALUE (..) element, all four variables must be listed in parentheses. And one more thing: if the variable does not exist, let's say this is an optional parameter. We just write empty value in quotes ‘’,
"INSERT INTO `name_base` VALUES (NULL, `".$name."`,``,`2`)"; // the third empty value is written in quotes
But this request has some minor drawbacks. If you add one cell or two cells to a database table, then given request will return an error. Because in this method, enumeration of all cells in the query is mandatory.
Second way if after query INSERT INTO `name_base` (...) list all cells after database name. An example has already been discussed above. If you forgot, write again:
"INSERT INTO `name_base`(`name`,`value`,`value2`) VALUES (NULL, `".$name."`,``,`2`)";
Here we have listed all cells (name,value1,value2) . And if you add an additional two more cells to the database table, then the query syntax will not have to be changed. But unless we need to add at once in one request those very additional variables that we need for those very new created cells.
Such an error crashes very often, after a small change on the site. Let's say the administrator added an additional cell to the database, let's say status. But the script handler did not have time to change the module, or simply forgot. But some sites have a very complex structure, and it can take a lot of time and effort to find an error. Therefore, it is desirable to use the second method of writing to the database. Although this kind of mistake is more often made by novice web programmers.
So, now we got to the heart of the matter when working with database queries. We will do everything on practical examples. Let's create a simple script for recording comments that site visitors will leave.
First, let's create a table msg in the database with four cells. In the first cell we write the id of the comment. The number of characters in the cell is up to ten characters with the auto-increment parameter. This automatic setting will change each time you add a comment to +1.
The next cell name is the username. Number of characters - up to two hundred - three hundred characters of your choice, parameter char. Then the coment cell - we will enter the comment text itself into this cell. If you want to record large comment texts, then you can set the text parameter - then you can simply enter huge texts, more than five hundred thousand characters, or set the tinytext parameter, then a little less characters will fit, but it will work a little faster.
But in our case, we will keep in mind that visitors will not write huge texts. And therefore, we will limit ourselves and will fix two thousand characters with the varchar parameter, to write string values.
In the last cell we will write the date the comment text entry was made. We will write in numerical format in seconds, using the current date and time function time(); For simplicity, we will set the function to the variable $time=time(); And create a cell in the database. Let's name the same name time with the int parameter (for numeric values). Let's write down the number of characters - eleven is better (with a small margin for the future :-).
The database dump is as follows:
`msg` table structure -- CREATE TABLE IF NOT EXISTS `msg` (`id` int(10) NOT NULL AUTO_INCREMENT, `name` char(250) NOT NULL, `coment` varchar(2000) NOT NULL, `time` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Everything, a table for comments has been created. Now we write a form in order to write a comment and the handler script itself. The HTML code for the comment form is as follows.
