MySQLi Articles - Page 378 of 422

The syntax of SUBSTRING() function using FROM and FOR keywords is the standard MySQL syntax.SyntaxSUBSTRING(str FROM pos FOR len)Here, str is the string from which substring would be returned.Pos is the starting position of substring.Len is the length of substring i.e. the total number of characters fetched from str.Examplemysql> Select SUBSTRING('foobarbar' FROM 4 FOR 5); +-------------------------------------+ | SUBSTRING('foobarbar' FROM 4 FOR 5) | +-------------------------------------+ | barba                               | +-------------------------------------+ 1 row in set (0.00 sec)The result set above, makes the use of FROM and FOR keywords very much clear in SUBSTRING() function.

If we omit the hostname part of the user account, MySQL will accept it and allow the user to connect from any host. Its syntax would be as follows −Use mysql; CREATE USER user_name IDENTIFIED BY password;Here, user_name is the name of the user we wish to take account of.Password is the password we wish to make for user_account. With the help of this password, MySQL server will identify this user.ExampleIn the given example we are creating a user ‘REMOTE’ by omitting the host name.mysql> CREATE USER remote identified by 'password123'; Query OK, 0 rows affected (0.00 sec)The user ‘Remote’ can ... Read More

To make it understand, we are using the following data from a table named ‘customerdetail’.mysql> Select * from Customerdetail; +----------------------+----------------------+----------+---------------------+ | Name                 | FName                | Address  | Emailid             | +----------------------+----------------------+----------+---------------------+ | Advik Jhamb          | Lovkesh Jhamb        | Mumbai   | Advik@gmail.com     | | Chirag Jai Patil     | Raman Jai Patil      | Gujrat   | chirahp@yahoo.com   | | Devansh Singh Rajput | Kishore Singh Rajput | ... Read More

It is quite possible to allow a user account to connect from any host. To do so we need to create the user with the help of ‘%’ wild card character after @ character. Its syntax would be as follows −Use mysql; CREATE USER user_name@’%’ IDENTIFIED BY password;Here user_name is the name of the user we wish to make an account for.Password is the password we wish to make for user_account. With the help of this password, MySQL server will identify this user.ExampleIn the given example we are creating a user ‘Gaurav’ by using ‘%’ character so that it can be ... Read More

As we know that, MySQL database server is having the user table in MySQL database which is used to store the user accounts so by using MySQL database we can create user accounts in MySQL database server. There must be two things while creating the new user account, one is the username and other is the hostname which is after @ character. The syntax for creating the user account is as follows − Syntax Use mysql; CREATE USER user_account IDENTIFIED BY password; Here user_account is the name of the user we wish to take account of. It can ... Read More

When we install MySQL server, a database named MySQL created automatically. This MySQL database contains five main grant tables with the help of which MySQL server can control the privileges of MySQL database server. These tables are as follows −user tableThis table contains user account and global privileges columns. MySQL uses the user table to either accept or reject a connection from a host. A privilege granted in the user table is effective to all databases on the MySQL server.db tableThis table contains database-level privileges. MySQL uses the db table to determine which database a user can access and from which host. ... Read More

To make it understand, we are using the following data from a table named ‘customerdetail’.mysql> Select * from Customerdetail; +----------------------+----------------------+-----------+---------------------+ | Name                 | FName                | Address   | Emailid             | +----------------------+----------------------+-----------+---------------------+ | Advik Jhamb          | Lovkesh Jhamb        | Mumbai    | Advik@gmail.com     | | Chirag Jai Patil     | Raman Jai Patil      | Gujrat    | chirahp@yahoo.com   | | Devansh Singh Rajput | Kishore Singh Rajput ... Read More

MySQL implements a sophisticated access control and privilege system that allows us to create comprehensive access rules for handling client operations and effectively preventing unauthorized clients from accessing the database system.The MySQL access control has two stages when a client connects to the server −Connection verification A client, which connects to the MySQL database server, needs to have a valid username and password. In addition, the host from that the client connects needs to match with the host within the MySQL grant table.Request verificationonce a connection is established successfully, for each statement issued by the client, MySQL checks whether the client ... Read More

We can also use ALTER USER statement along with IDENTIFIED BY clause to change MySQL user password. Its syntax would be as possible −SyntaxALTER USER user_name@host_name IDENTIFIED BY ‘new_password’Here, New_password would be new password we want to set for MySQL userUser_name is the name of a current user.Host_name is the name of the host of a current user.ExampleSuppose if we want to change the password user@localhost to ‘tutorials’ then it can be done as follows −ALTER USER user@localhost IDENTIFIED BY ‘tutorials’

We can use SET PASSWORD statement to change the password. Before using this command, we need to have at least UPDATE privileges. Its syntax would be as follows −SyntaxSET PASSWORD FOR ‘user_name@host_name’=new_password;Here, New_password would be new password we want to set for MySQL userUser_name is the name of the current user.Host_name is the name of the host of the current user.ExampleSuppose if we want to change the password user@localhost to ‘tutorials’ then it can be done as follows −SET PASSWORD FOR ‘user@localhost’= tutorials;