Each command sent to the database engine is called a query. On this page, we will list the most used queries
The following command creates a new users
table by defining each of the
fields:
CREATE TABLE users (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
lastname VARCHAR(100),
firstname VARCHAR(100),
email VARCHAR(255)
);
The following query creates a new row in the table, specifying the
data contained in this line. Note that the id
field is not
defines because, this column was set as auto-increment.
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Lary', 'Page', 'lary.page@google.com');
When updating a row, you need of course to specify which line it is, here by its
identifier id
.
UPDATE `users` SET email = 'lary.page@gmail.com' WHERE id=1;
As for updating, you must specify which row to delete. The next command will delete all rows where the user's lastname is Page:
DELETE FROM `users` WHERE lastname='Page';
The following example:
users
;-- Create table
CREATE TABLE users (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
lastname VARCHAR(100),
firstname VARCHAR(100),
email VARCHAR(255)
);
-- Insert rows
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Lary', 'Page', 'lary.page@google.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Sergueï', 'Brin', 'serguei.brin@google.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Elon', 'Musk', 'elon.musk@tesla.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Steve', 'Jobs', 'steve.jobs@apple.com');
INSERT INTO `users` (`firstname`, `lastname`, `email`) VALUES ('Noël', 'Père', 'santa@clause.com');
-- Update row
UPDATE `users` SET email = 'steve.jobs@paradise.com' WHERE lastname="jobs";
-- Delete row
DELETE FROM `users` WHERE email='santa@clause.com';
-- Select the full table
SELECT * FROM `users`;
-- Select rows sorted by lastname
SELECT * FROM `users` ORDER BY lastname;
-- Select rows for a given last name
SELECT * FROM `users` WHERE lastname = 'Page';
Let's consider the following table creation:
CREATE TABLE products (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
price FLOAT
);
Nom | Prix |
---|---|
smartphone | 399€ |
laptop | 699€ |
keyboard | 29.99€ |
mouse | 19.90€ |
printer | 1650€ |
modem | 99€ |