Found 4589 Questions for MySQL

How to specify options for MySQL programs?

AmitDiwan
Updated on 09-Mar-2021 13:39:00
Let us understand how to specify options in MySQL programs. There are many ways in which options can be specified for MySQL programs −Command LineThe options on the command line after entering the program name have to be listed. This is a common step for options which applies to a specific invocation of the program.Option fileThe options in an option file which the program reads when it starts also needs to be listed. This is a common step for options that the user needs the program to use every time it runs.Environment VariablesThe options in environment variables need to be ... Read More

Invoking MySQL Programs

AmitDiwan
Updated on 09-Mar-2021 13:37:30
A MySQL program can be invoked from the command line (i.e, from your shell or command prompt).How to invoke?This can be done by entering the program name followed by any options or arguments that would be needed to instruct the program to do what the user wants.The below commands show some sample program invocations. The ‘shell>’ represents the prompt for the command interpreter; which is not part of what the user types. The specific prompt seen by user depends on their command interpreter.Typical prompts are $ for sh, ksh, or bash, % for csh or tcsh, and C:\> for the ... Read More

Overview of MySQL Programs

AmitDiwan
Updated on 09-Mar-2021 13:37:03
There are many programs in a MySQL installation. Let us see an overview of some of the programs. Some programs are platform−specific, which means they are not present in all of MySQL distributions.Every MySQL program takes different options. There is a ‘- - help’ option that can be used to get a description of the program’s different options. The default option values can be overridden for MySQL program, by specifying options on command line or in an option file.Every MySQL program takes different options. There is a ‘- - help’ option that can be used to get a description of ... Read More

How to set initial value and auto increment in MySQL?

AmitDiwan
Updated on 09-Mar-2021 13:34:51
The AUTO_INCREMENT attribute is used to generate a unique identify for new rows. If a column is declared as ‘NOT NULL’, it is possible to assign NULL to that column to generate a sequence of numbers.When any value is inserted into an AUTO_INCREMENT column, the column gets set to that value, and the sequence also gets reset so that it generates values automatically, in the sequential range from largest column value.An existing ‘AUTO_INCREMENT’ column can be updated that will reset the ‘AUTO_INCREMENT’ sequence as well. The most recent auto-generated ‘AUTO_INCREMENT; value can be retrieved using the ‘LAST_INSERT_ID()’ function in SQL ... Read More

Working with AUTO_INCREMENT Columns in MySQL

AmitDiwan
Updated on 09-Mar-2021 13:34:27
Let us understand how to work with AUTO_INCREMENT columns in MySQL −The AUTO_INCREMENT attribute is used to generate a unique identify for new rows. Let us see how this statement works. Before that, consider the below query −QueryCREATE TABLE tableName (    id MEDIUMINT NOT NULL AUTO_INCREMENT,    name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO tableName (name) VALUES (‘val1’), ('val2'), ('val3'), ('val4'); SELECT * FROM tableName;Output+----+---------+ | id | name | +----+---------+ | 1 | val1 | | 2 | val2 | | ... Read More

How to Create a MySQL Sequence?

AmitDiwan
Updated on 09-Mar-2021 13:31:15
What is Sequence in MySQL?In MySQL, a sequence refers to a list of integers that is generated in the ascending order beginning from 1 or 0 if specified. Many applications require sequences that will be used to generate unique numbers especially for identification.ExampleExamples include customer ID in CRM, employee numbers in HR, and equipment numbers in the services management system.To create a sequence in MySQL automatically, the AUTO_INCREMENT attribute for a column needs to be set. This would typically be a primary key column.RulesThe following rules need to be followed while using the AUTO_INCREMENT attribute −Every table has only one ... Read More

MySQL AUTO_INCREMENT with Examples

AmitDiwan
Updated on 09-Mar-2021 13:30:30
Let us understand how ATUO_INCREMENT works −The AUTO_INCREMENT attribute is used to generate a unique identify for new rows. Let us see how this statement works. Before that, consider the below query −QueryCREATE TABLE tableName (    id MEDIUMINT NOT NULL AUTO_INCREMENT,    name CHAR(30) NOT NULL,    PRIMARY KEY (id) ); INSERT INTO tableName (name) VALUES (‘val1’), ('val2'), ('val3'), ('val4'); SELECT * FROM tableName;Output+----+---------+ | id | name | +----+---------+ | 1  | val1 | | 2  | val2 | | 3  | val3 | | ... Read More

SQL queries for counter web visits per day, month, year and totals

AmitDiwan
Updated on 09-Mar-2021 13:26:53
Let us understand how to form the query to find the number of web visits per day, per month, per year, and the total in MySQL:Note: We assume we have created a database named ‘DBNAME’ and a table named ‘tableName’.Let us see the MySQL query which can be used to get the web visits per day, month, year and totals −QuerySELECT COUNT(DISTINCT ip) FROM tableName WHERE create_at >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH AND create_at < LAST_DAY(NOW()) + INTERVAL 1 DAYThe above query searches through a range of DATETIME values by beginning from the present month ... Read More

How can I count visitors per page per day using MySQL?

AmitDiwan
Updated on 09-Mar-2021 13:26:14
Note: We assume we have created a database named ‘DBNAME’ and a table named ‘tableName’.Let us understand how the count of visitors per day per page can be queried using MySQL. This can be done using the bit group function −QuerySELECT DATE(date) Date, page_id, COUNT(*) colName FROM tableName GROUP BY DATE(date), page_idHere ‘colName’ refers to the ‘visits per day’ column, and ‘tableName’ refers to the table that contains details about visitors.It makes sure that the duplicate values in the table are removed when the above query is run.

Making slow queries fast using composite indexes in MySQL

AmitDiwan
Updated on 09-Mar-2021 13:24:41
Let us first see what is Composite Index −A composite index is an index that is used on multiple columns.It is also known as a multiple-column index.MySQL allows the user to create a composite index which can consist of up to 16 columns.The query optimizer uses the composite indexes for queries which will test all columns in the index.It can also be used for queries which will test the first columns, the first two columns, and so on.If the columns are specified in the right order in the index definition, a single composite index can be used that would speed ... Read More
Previous 1 ... 5 6 7 8 9 10 11 ... 459 Next
Advertisements