lining figures in tables and math, old style figures in text

The libertine package per default has the options lining and tabular activated. In normal text you usually want proportional and oldstyle, though.

\usepackage[oldstyle,proportional]{libertine}

You don't want to set them as package options, though, but only after newtxmath has been loaded to get lining figures in math. This could be done by defining \libertine@figurealign to an empty definition (with pdfLaTeX) or to Proportional (with XeLaTeX or LuaLaTeX) and call \libertineOsF afterwards:

\usepackage{libertine}
\usepackage[cmintegrals, cmbraces, libertine]{newtxmath}
\makeatletter
\AtBeginDocument{\def\libertine@figurealign{}\libertineOsF}
\makeatother

If you now want that mhchem has lining numbers in text mode, too, you have to set its font option:

\mhchemoptions{textfontcommand=\libertineLF}

In a table you usually want libertine's defaults back. That could be done by redefining \libertine@figurealign to T (it's definition is empty for proportional figures) when compiling with pdfLaTeX or to Monospaced (vs. Proportional) with XeLaTeX or LuaLaTeX, and calling the switch \libertineLF afterwards. For convenience one could define a command \libertineTabular:

\makeatletter
\newcommand\libertineTabular{\def\libertine@figurealign{T}\libertineLF}
\makeatother

This newly defined command should be only used inside the {table} environment or, to be more precise, locally if you don't want to switch to tabular figures for the whole document.

\documentclass[a4paper]{scrartcl}
\usepackage[utf8]{inputenc}
\usepackage{booktabs}
\usepackage{tabulary}
\usepackage{textcomp}
\usepackage{amsmath, amsthm}
\usepackage{libertine}
\usepackage[cmintegrals, cmbraces, libertine]{newtxmath}
\usepackage[version=4]{mhchem}
\mhchemoptions{textfontcommand=\libertineLF}

\makeatletter
\AtBeginDocument{\def\libertine@figurealign{}\libertineOsF}
\newcommand\libertineTabular{\def\libertine@figurealign{T}\libertineLF}
\makeatother

\begin{document}
Old style numbers belong in text 0123456789. \ce{C6H12O6 + 6O2 -> 6CO2 ^ + 6H2O ^ + \text{heat} ^}
Not in equations:
\begin{equation}
  \ce{C6H12O6 + 6O2 -> 6CO2 ^ + 6H2O ^ + \text{heat} ^}
\end{equation}
Nor in tables.

\begin{table}[!htb]
  \caption{mean, median and standard deviation}
  \label{tab:mean}\libertineTabular
  \begin{tabulary}{\textwidth} {LCCCCR}
    \toprule
      climate & species & mean &  median & standard deviation & number of experiments \\
    \midrule
      1 & \textit{Picea abies}  & 20,3699   & 20,0335   & 4,453 & 30 \\
      2 & \textit{Picea abies}  & 23,9864   & 12,6398   & 7,236 & 98 \\
    \bottomrule
  \end{tabulary}
\end{table}

\[ a^2 + b^2 = c^2 \qquad 1234567890 \]
Old style numbers belong in text 0123456789.

\end{document}

1. Use \usepackage{libertine}, and immediately after \begin{document}, use \libertineOsF.
2. Immediately after \begin{table}[..], use \libertineLF.

As noted by @cgnieder the default appearance of numbers for the main text should be set to proportional and osf (old style figures). The chemistry equations should get proportional lining figures. Finally, tables should contain tabular figures (equal width), but personally I think old style figures are fine in a table.

In the following code I define three font switching commands that can be used to set the desired style on certain places. Then I set the styles as described in the previous paragraph. The main difference to cgnieder's answer is that I set tabular figures for all tabulary tables, without the need to specify that on each table by hand.

\documentclass[a4paper]{scrartcl}
\usepackage[utf8]{inputenc}
\usepackage{booktabs}
\usepackage{tabulary}
\usepackage{textcomp}
% \usepackage{libertine}               %% to have tabular (lining) figures in chemistry equations
\usepackage[proportional]{libertine} %% to have proportional (lining) figures in chemistry equations
\usepackage{amsmath, amsthm}
\usepackage[cmintegrals, cmbraces, libertine]{newtxmath}
\usepackage[version=3]{mhchem}

\makeatletter
\newcommand\libertineTLF{\def\libertine@figurealign{T}\libertineLF}
% \newcommand\libertinePLF{\def\libertine@figurealign{}\libertineLF}    %% not needed
\newcommand\libertineTOsF{\def\libertine@figurealign{T}\libertineOsF}
\newcommand\libertinePOsF{\def\libertine@figurealign{}\libertineOsF}
\makeatother

%% to have proportional old style figures in the main text
\AtBeginDocument{\libertinePOsF}

%% to have lining figures in chemistry equations;
%% to switch between tabular and  proportional lining figures, use the `proportional` package option of libertine
\mhchemoptions{textfontcommand=\libertineTLF}

%% to have tabular figures in all tabulary environments
\usepackage{etoolbox}
\makeatletter
\pretocmd{\TY@tabular}{\libertineTOsF}{}{}  %% tabular old style figures
% \pretocmd{\TY@tabular}{\libertineTLF}{}{}   %% tabular lining figures
\makeatother


\begin{document}
Old style proportional width numbers belong in text 0123456789.
Not in equations (does not make sense chemically):
\begin{align}
     \cee{C6H12O6 + 6666O2 & -> 6666CO2 ^ + 6666H2O ^ + \text{heat} ^} \\
     \cee{C6H12O6 + 1111O2 & -> 1111CO2 ^ + 1111H2O ^ + \text{heat} ^}
\end{align}
Nor in tables:
\begin{table}[!htb]
     \begin{tabulary}{\textwidth} {LCCCCR}
      \toprule
      climate &
      species &
      mean &
      median &
      standard deviation &
      number of experiments \\
      \midrule
      1 & \textit{Picea abies}  & 20,3699   & 20,0335   & 4,453 & 30 \\
      1 & \textit{Picea abies}  & 20,3699   & 11,1111   & 4,453 & 30 \\
      9 & \textit{Picea abies}  & 20,3699   & 99,9999   & 4,453 & 30 \\
      \bottomrule
      \end{tabulary}
      \caption{mean, median and standard deviation}
      \label{tab:mean}
\end{table}
\end{document}