Confused with selecting data from db in mysql

Question

I have 2 tables: PLAYER and GROUP.

When I call:

SELECT * FROM PLAYER

Everything is OK but when I call:

SELECT * FROM GROUP

There is error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP' at line 1`

I don't understand why there is such error.

score 5 · Answer 1 · answered Dec 11 '12 at 15:30

5

GROUP is a reserved word and it must be escaped with back ticks.

SELECT * FROM `GROUP`

It's also better practice to avoid using table names or columns that are reserved words. Also, specify a column list and not use *.

answered Dec 11 '12 at 15:30

Kermit

33,827
13
85
121

oh really I forgot thx a lot – hudi Dec 11 '12 at 15:31

score 4 · Accepted Answer · answered Dec 11 '12 at 15:30

4

GROUP is a reserved keyword . You have to escape it, like so

  SELECT * FROM `GROUP`

Things to note:

It is highly recommended not to name the table group, it is better to use the plural Groups.
Also, try to avoid *.

answered Dec 11 '12 at 15:30

Mahmoud Gamal

78,257
17
139
164

+1 : and then change the table name so it isn't a reserved word. – Andrew Dec 11 '12 at 15:31
and can you advise me new name of table ? I have table where is group of player so I call it GROUP – hudi Dec 11 '12 at 15:32
@hudi - `Groups` or `PlayersGroups` will be fine. – Mahmoud Gamal Dec 11 '12 at 15:34

fuzzy · Answer 3 · 2012-12-11T19:21:40.523

Just as you use the reserved keywords SELECT and FROM for constructing your query, there are other reserved keywords like the ones in the following list that you may want to avoid when naming all your tables, views, constraints and columns.

List of (some) reserved keywords: WHERE, ORDER, GROUP, UPDATE, DELETE, CHECK, CHANGE, LIKE etc...

Therefore, in your case the DB Engine is complaining because a query needs 1 mandatory clause SELECT and optional clauses FROM, WHERE, ORDER BY, GROUP BY, HAVING, LIMIT etc... read more here

We might be tempted to use these keywords; especially if they've been modeled in our problem domain (or in the diagrams). For example, a client placing an order yields 2 Entities CLIENT and ORDER etc. or a mechanic performing a check giving MECHANIC and CHECK. or even a FACEBOOK_USER expressing a LIKE. or in your case there might be a GROUP of PEOPLE for example.

As a general rule, you can transform your entities like so to avoid problems:-

a) The Entity is always modeled (on paper) as singular as it represents a concept/asset/person in the real world or problem domain. eg. ORDER, LIKE, CHECK, STUDENT, CAR

b) the corresponding DB Table it is transformed into is always named using plural. The logic is that the table will contain lots of instances of that Entity. Therefore ORDERS, LIKES, CHECKS, STUDENTS, CARS

In the end, you decide because you really can use GROUP if you really want or need to make your table name like a reserved word. Just remember to give them a special treatment by putting them in quotes using the backtick (“`”) when querying. At the moment of creation the DB engine won't complain, trusting that you know what you're doing.

Confused with selecting data from db in mysql

3 Answers3