Styling points generated by table to have varying opacity?

One possibility would be to work with raw Graphics primitives:

Graphics[
 {PointSize[0.05],
  Table[{Opacity[(n + 1)/5], Point[{n, n + 1}]}, {n, 0, 4}]
 },
 Frame -> True, AspectRatio -> 1/GoldenRatio,
 PlotRangePadding -> Scaled[.05]
]

data = Transpose[{Range[6], Range[0, 5]}];
ListPlot[{#} & /@ data, PlotStyle -> (Opacity[#/5] & /@ Range[1, 6]), 
 Frame -> True]

Also:

data = Table[n, {n, 0, 5}];
styleddata = Style[#, Red, Opacity[ (# + 1)/5], PointSize[.1]] & /@ data;

ListPlot[styleddata, PlotStyle -> None, PlotRangePadding -> {{0, 1}, {1, 1}}, 
 Frame -> True, Axes -> False]