Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Top 10 MySQL queries every developer should know

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 548
    Comment on it

    As developers of any technology which uses MySQL as backend we always need to know some basic queries to work on. The list may vary as per the development need but a basic set still remains the same.

    1. The very first query that comes into use is to set the base for any database i.e creating a database itself its as simple as

      create database <yourdatabase>

      create database employee
    2. Want to check what other databases are there
      show databases
    3. The next command may seem too basic to know but is root of many errors that creep in due to wrongly selected database. Unless you are using a GUI, to work on any database the command that comes in handy is

      use <yourdatabase>

      use employee
    4. Next object that is always needed is the table. We wont go here into nitty gritty of the data types and keys as they are a topic in themselves.

      create table <tablename>(<fieldname1> <datatype>, <fieldname2> <datatype>, <fieldname3> <datatype>)

      create table employee_master(id int, name varchar(20), date_of_birth datetime)
    5. To view what all tables are there in the database
      show tables
    6. If we need to check the structure(fields) of any table the command that comes to our help is

      desc <tablename>

      desc employee_master
    7. The table is ready so we need to populate it with data. There are many command variations possible but the base of all of them is same

      insert into <tablename> values(<value1>, <value2>, <value3>)

      insert into employee_master values(1, 'abc','1967-5-20')
    8. The data is inserted and we need to view the content, the command that comes to rescue is select this command has to many variations that a complete blog can be written on it. But the most basic one to start is

      select * from <tablename>

      select * from employee
    9. The data needs to be updated as value was wrongly entered. The command to your rescue is update

      update >tablename< set <fieldname> = <new value>

      update employee_master set date_of_birth='1968-5-20'
      
      A catch here !! the above command will update all the records with the new value, so be careful and use where clause. So the corrected command will look like :
      update employee_master set date_of_birth='1968-5-20' where id=1
      
    10. Sometimes we might need to remove a erroneous record the command that comes to your rescue is delete

      delete from <tablename>

      delete from employee_master
      
      Again be cautious here, as the command in its basic format will delete all the records. Use it carefully with where clause
      delete from employee_master where id=1
      

    Will cover more query variations on Select, Update, Delete commands in coming posts.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: